RE: SQL run time changed after DB upgrade but execution plan did not change
Date: Mon, 29 Apr 2013 16:59:17 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED0AF66BC3_at_USA7109MB012.na.xerox.net>
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
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Apr 29 2013 - 18:59:17 CEST