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

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 27 Apr 2013 15:46:16 +0000

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@xxxxxxxxx]
Sent: 27 April 2013 01:12
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
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--
//www.freelists.org/webpage/oracle-l


Other related posts: