RE: SQL run time changed after DB upgrade but execution plan did not change
Date: Sat, 27 Apr 2013 22:35:38 -0400
Message-ID: <004501ce43b9$128af370$37a0da50$_at_rsiz.com>
I don't see any attachments. Probably you need to paste the plans in-line for them to reach oracle-l. After starting out accepting your claim the plans had not changed, we probably do need to see the plans to help.
I'm not sure what you mean by the optimizer pulling actual rows. Running the query is required to get actual rows (along with the appropriate hint.)
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Hameed, Amir
Sent: Saturday, April 27, 2013 5:50 PM
To: jonathan_at_jlcomp.demon.co.uk; oracle-l_at_freelists.org
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
-----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
--
http://www.freelists.org/webpage/oracle-l
Received on Sun Apr 28 2013 - 04:35:38 CEST