RE: SQL run time changed after DB upgrade but execution plan did not change
Date: Sat, 27 Apr 2013 21:50:20 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED0AF65E06_at_USA7109MB012.na.xerox.net>
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
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Saturday, April 27, 2013 11:46 AM
To: oracle-l_at_freelists.org
Subject: RE: SQL run time changed after DB upgrade but execution plan did not change
Amir,
You're looking at the "Execution Plan" section from the trace file - which is the result from a call to EXPLAIN PLAN, and therefore not necessarily what actually happened - you've probably not got the "Rowsource Execution" version because the SQL is inside pl/sql and the cursor hadn't closed.
You said you're seeing a full tablescan of "T1" when running at level 8, and that it's common to both plans - but neither shows a full tablescan - so that's an indication of how untrustworthy the execution plans are. (and neither "Execution Plan" has a T1 ;)
Regards
Jonathan Lewis
From: Hameed, Amir [Amir.Hameed_at_xerox.com] Sent: 27 April 2013 01:12
To: Jonathan Lewis; oracle-l_at_freelists.org Subject: RE: SQL run time changed after DB upgrade but execution plan did not change
Thanks Jonathan.
When I run the statement with 10046, level 8, the only FTS I see is for T1, which is common in both 11.1.0.7 and 11.2.0.3. There is no other FTS. The plan from the 10046 looks like as shown below:
11.1.0.7
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT MODE: ALL_ROWS
11.2.0.3 for 10 rows:
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT MODE: ALL_ROWS-- http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Apr 27 2013 - 23:50:20 CEST