Re: SQL run time changed after DB upgrade but execution plan did not change
Date: Mon, 29 Apr 2013 19:35:48 +0100
Message-ID: <95C354C06CB54A55AD285A5288EF4E5D_at_Primary>
A few things that I'd look at:
- Do a count distinct on t1 for the number of distinct calls to the inline scalar subquery that you would get if the subquery results were being cached (as in scalar subquery caching, NOT data caching).
- Check memory to see if there are two cursors for the subquery - on the basis that maybe we're seeing an unusual appearance of cardinality feedback
- copy ALL the tables to 11.1.0.7 and test
- copy ALL the tables to 11.2.0.3 and test
- check that the results on 11.1.0.7 matched the results on 11.2.0.3 in both cases.
The most unusual anomaly is that apparent single execution on 11.1.0.7 - and since the final buffer count matches the total "consistent gets" count on the tkprof output, it looks as if that single execution is correct. (But how many indexes are there on the table you're inserting into ? )
In the absence of further access to details, my thoughts are turning to a PL/SQL bug where the same values from the input arrays are being passed to the inline scalar subquery every time (which would explain the single execution) and no-one has noticed that the results are wrong.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: "Hameed, Amir" <Amir.Hameed_at_xerox.com> To: <jonathan_at_jlcomp.demon.co.uk>; <oracle-l_at_freelists.org> Sent: Monday, April 29, 2013 5:59 PM Subject: RE: SQL run time changed after DB upgrade but execution plan did not change
Hi Jonathan,
Thank you for the feedback. I have checked with development on T1 and T2.
T1 gets loaded every day as part of the processing and once T2 is populated
and all processing is done, these tables are purged. T1 in 11.1.0.7 contain
slightly different data but the difference is not huge.
As a test, I populated the T1 table in 11.1.0.7 DB with data from 11.2.0.3
but the plan remained the same.
At this point, we are going to open an SR with Oracle and see if this is somehow related to an optimizer bug.
I do appreciate all the feedback that I have received on this issue.
Thanks
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jonathan Lewis
Sent: Monday, April 29, 2013 10:37 AM
To: oracle-l_at_freelists.org
Subject: Re: SQL run time changed after DB upgrade but execution plan did
not change
Amir,
Looking at the plans with rowsource execution statistics, the most significant thing is that the inline scalar subquery seems to have executed just once in 11.1.0.7 and 10,000 times in the 11.2.0.3 database. On top of that, the 10,000 executions in the 11.2.0.3 database haven't been reusing cached data.
In combination these observations suggest that the two databases hold remarkably different data sets in both the "T1" and "T2" tables - and the t1 table in the 11..1.0.7 instance looks as if every row holds the same values ! (Alternatively, every row in t1 in 11.1.0.7 holds a value that puts the index above the high value for EVERY row, and Oracle is smart enough to not re-execute the query as a consequence - but if that's the case it's a clever trick that I didn't know about).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: "Hameed, Amir" <Amir.Hameed_at_xerox.com> To: <jonathan_at_jlcomp.demon.co.uk>; <oracle-l_at_freelists.org> Sent: Saturday, April 27, 2013 10:50 PM Subject: RE: SQL run time changed after DB upgrade but execution plan did not change
Hi Jonathan,
I pulled the execution plan by using DBMS_XPLAN.DISPLAY_CURSOR and is
attached for both DB versions. The difference seems to be the way the
optimizer is pulling the estimated and actual rows. SORT AGGREGATE is also
different.
Thanks,
Amir
--
http://www.freelists.org/webpage/oracle-l
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.2904 / Virus Database: 3162/6280 - Release Date: 04/28/13
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Apr 29 2013 - 20:35:48 CEST