RE: SQL run time changed after DB upgrade but execution plan did not change
Date: Sat, 27 Apr 2013 00:12:09 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED0AF6597C_at_USA7109MB012.na.xerox.net>
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
0 LOAD TABLE CONVENTIONAL OF 'T2'
1 SORT (AGGREGATE)
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'MTL_MATERIAL_TRANSACTIONS' (TABLE)
38 INDEX MODE: ANALYZED (RANGE SCAN) OF
'MTL_MATERIAL_TRANSACTIONS_N1' (INDEX)
1588081 FAST DUAL
11.2.0.3 for 10 rows:
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT MODE: ALL_ROWS
0 LOAD TABLE CONVENTIONAL OF 'T2'
10 SORT (AGGREGATE)
14 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'MTL_MATERIAL_TRANSACTIONS' (TABLE)
175251 INDEX MODE: ANALYZED (RANGE SCAN) OF
'MTL_MATERIAL_TRANSACTIONS_N1' (INDEX)
10 FAST DUAL
Oracle is scanning a lot more rows in 11.2.0.3 than in 11.1.0.7.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Friday, April 26, 2013 5:00 PM
To: oracle-l_at_freelists.org
Subject: RE: SQL run time changed after DB upgrade but execution plan did not change
It looks like the execution plan of the scalar subquery has changed to use a tablescan.
Your execution plan looks like the plan for just the scalar subquery run through explain plan, you need to pull the execution plans from memory using dbms_xplan.display_cursor()
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Hameed, Amir [Amir.Hameed_at_xerox.com] Sent: 26 April 2013 21:24
To: oracle-l_at_freelists.org
Subject: SQL run time changed after DB upgrade but execution plan did not change
Below are statistics from 11.1.0.7 when all 1.5 million rows from "T1" table were processed:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 67.22 67.20 1280 42104 6500059 1588081 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 67.22 67.20 1280 42104 6500059 1588081 In 11.2.0.3, the statistics look below when processing 100 rows from "T1": call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 89.16 106.70 1417957 1634828 311 100 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 89.16 106.70 1417957 1634828 311 100
I am not sure if this is due to a bug or something else is causing this much IOs.-- http://www.freelists.org/webpage/oracle-l
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Apr 27 2013 - 02:12:09 CEST