What is this issue ?

[svk]

Hello All,

Following is part of a Pro*C code :

    EXEC SQL
        SELECT   A.RecurringChargeAmt,
                B.AdvanceInd
        INTO     :hostMrcAmt,
                :hostAdvanceInd
        FROM     ChargePrice A,
                 Charge B
        WHERE   A.MarketCd   = :hostMarketCd
        AND   B.MarketCd     = :hostMarketCd
        AND   B.MarketCd     = A.MarketCd
        AND   A.ChargeCd     = :hostChargeCd
        AND   B.Chargecd     = :hostChargeCd
        AND   B.ChargeCd     = A.ChargeCd
        AND   A.ChargeTypeCd = :hostChargeTypeCd
        AND   B.ChargeTypeCd = :hostChargeTypeCd
        AND   B.ChargeTypeCd = A.ChargeTypeCd
        AND   A.PricePlanCd  = :hostPricePlanCd; 

All host variables get set in the program. Whn this code runs against a 9i(9205) database, everything is fine. No issues. Same code doesn't run against 10g(10202). The sessions just hangs. STATUS ni V$SESSION is ACTIVE and WAITEVENT is "SQL*Net mode data from client". That in itself is a little strange. But, that is minor - if the session does somethng.

In an attempt to resolve this, I asked the query to be changed to eliminate redundant WHERE clause conditions. So, the new query is :

    EXEC SQL
        SELECT   A.RecurringChargeAmt,
                B.AdvanceInd
        INTO     :hostMrcAmt,
                :hostAdvanceInd
        FROM     ChargePrice A,
                 Charge B
        WHERE
           B.MarketCd     = :hostMarketCd
        AND   B.MarketCd     = A.MarketCd
        AND   B.Chargecd     = :hostChargeCd
        AND   B.ChargeCd     = A.ChargeCd
        AND   B.ChargeTypeCd = :hostChargeTypeCd
        AND   B.ChargeTypeCd = A.ChargeTypeCd
        AND   A.PricePlanCd  = :hostPricePlanCd; 

Now, things run okay even on 9i as well as 10g.

What the heck is going on here !!??

I'll really appreciate if someone gives me some clue(s).

[pando]

Execution Plan changes?

[tamilselvan]

Same code doesn't run against 10g(10202).

Who said 10gR2 is bug free?

Tamil

[KenEwald]

Does it hang from sqlplus (10g client)?

[svk]

Pando : No change in query plan and both queries show same plan in 9i and 10g.
Tamil : I'm not counting on 10gR2 being bug-free. I'm interested in knowing if this is happening because of a bug. I have a tar open with oracle and they are also looking at it.
Ken : No, this doen't hang from SQL*Plus or any other similar tool (TOAD).

[pando]

hae you sql traced ad see what is waiting?

[svk]

Yes, it just shows "SQL*Net more data from client". The session status is shown as ACTIVE. As I mentioned initially, this in itself is a little strange...

[Mr.Hanky]

Tables the same size, indexes?

Stats up to date?

[KenEwald]

This could be a query rewrite issue.

[svk]

Tables and indexes are identical between 9i and 10g. Not that it proves anything but the 9i database was used as source for "exp" to pupulate the 10g database. Stats are up-to-date. The tables are tiny. @ 6000 rows in each of them.