-
What is this issue ?
Hello All,
Following is part of a Pro*C code :
PHP 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 :
PHP Code:
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).
svk
-
-
Same code doesn't run against 10g(10202).
Who said 10gR2 is bug free?
Tamil
-
Does it hang from sqlplus (10g client)?
-
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).
svk
-
hae you sql traced ad see what is waiting?
-
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...
svk
-
Tables the same size, indexes?
Stats up to date?
I remember when this place was cool.
-
This could be a query rewrite issue.
-
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.
svk
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|