Re: SQL run time changed after DB upgrade but execution plan did not change
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 29 Apr 2013 15:36:45 +0100
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@xxxxxxxxx>
To: <jonathan@xxxxxxxxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
