RE: SQL run time changed after DB upgrade but execution plan did not change
Date: Sat, 27 Apr 2013 15:46:16 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90CFD26_at_exmbx06.thus.corp>
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 Received on Sat Apr 27 2013 - 17:46:16 CEST