Re: SQL run time changed after DB upgrade but execution plan did not change

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 Received on Mon Apr 29 2013 - 16:36:45 CEST