RE: SQL run time changed after DB upgrade but execution plan did not change
Date: Sun, 28 Apr 2013 13:30:01 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED0AF66173_at_USA7109MB012.na.xerox.net>
The definition of MTL_MATERIAL_TRANSACTIONS_N1 has not changed between the DB versions. I have already verified it. I forgot to mask the organization_id in my last send. 999 was the masked value, 398 was the actual value. You should read 368 as 999.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Sunday, April 28, 2013 12:11 AM
To: 'Oracle-L Freelists'
Subject: RE: SQL run time changed after DB upgrade but execution plan did not change
I don't know what to make of organization_id=368 in the statement and organization_id=999 in the plan.
Also, has the definition of MTL_MATERIAL_TRANSACTIONS_N1 changed?
-----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 10:40 PM
To: mwf_at_rsiz.com; 'Oracle-L Freelists'
Subject: RE: SQL run time changed after DB upgrade but execution plan did
not change
- Plan from 11.1.0.7 when all 1.5M rows from T1 were processed:
select * from table
(DBMS_XPLAN.DISPLAY_CURSOR('1401r0su59mvh',null,'ALLSTATS LAST')) ;
PLAN_TABLE_OUTPUT
SQL_ID 1401r0su59mvh, child number 0
INSERT INTO T2 SELECT 999, :B2 , :B1 , (SELECT MAX(TRANSACTION_DATE) FROM MTL_MATERIAL_TRANSACTIONS MMT WHERE MMT.TRANSACTION_TYPE_ID IN (11, 22, 33, 44, 55) AND ORGANIZATION_ID = 368 AND INVENTORY_ITEM_ID = :B4 AND SUBINVENTORY_CODE = :B3 ) FROM DUAL Plan hash value: 916472238
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------
-----------------------------------------------------
| 0 | INSERT STATEMENT | | 1588K| | 0 |00:01:01.94 | 6572K| 835 | | 1 | LOAD TABLE CONVENTIONAL | | 1588K| | 0 |00:01:01.94 | 6572K| 835 | | 2 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 35 | 0 | |* 3 | TABLE ACCESS BY INDEX ROWID| MTL_MATERIAL_TRANSACTIONS | 1 | 1 | 1 |00:00:00.01 | 35 | 0 | |* 4 | INDEX RANGE SCAN | MTL_MATERIAL_TRANSACTIONS_N1 | 1 | 12 | 38 |00:00:00.01 | 4 | 0 | | 5 | FAST DUAL | | 1588K| 1 | 1588K|00:00:00.89 | 0 | 0 |
----------------------------------------------------------------------------
-----------------------------------------------------
Predicate Information (identified by operation id):
3 - filter(("SUBINVENTORY_CODE"=:B3 AND INTERNAL_FUNCTION("MMT"."TRANSACTION_TYPE_ID"))) 4 - access("INVENTORY_ITEM_ID"=:B4 AND "ORGANIZATION_ID"=999)
- Plan from 11.2.0.3 when 10000 rows from T1 were processed:
select * from table
(DBMS_XPLAN.DISPLAY_CURSOR('1401r0su59mvh',null,'ALLSTATS LAST')) ;
PLAN_TABLE_OUTPUT
SQL_ID 1401r0su59mvh, child number 0
INSERT INTO TXRMR0_SOF_MTL_RCPT_DATES SELECT 999, :B2 , :B1 , (SELECT MAX(TRANSACTION_DATE) FROM MTL_MATERIAL_TRANSACTIONS MMT WHERE MMT.TRANSACTION_TYPE_ID IN (11, 22, 33, 44, 55) AND ORGANIZATION_ID = 368 AND INVENTORY_ITEM_ID = :B4 AND SUBINVENTORY_CODE = :B3 ) FROM DUAL Plan hash value: 916472238
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------
-----------------------------------------------------
| 0 | INSERT STATEMENT | | 10000 | | 0 |01:01:36.17 | 167M| 28M| | 1 | LOAD TABLE CONVENTIONAL | | 10000 | | 0 |01:01:36.17 | 167M| 28M| | 2 | SORT AGGREGATE | | 10000 | 1 | 10000 |01:01:34.58 | 167M| 28M| |* 3 | TABLE ACCESS BY INDEX ROWID| MTL_MATERIAL_TRANSACTIONS | 10000 | 1 | 57202 |01:07:14.43 | 167M| 28M| |* 4 | INDEX RANGE SCAN | MTL_MATERIAL_TRANSACTIONS_N1 | 10000 | 13 | 293M|00:04:53.09 | 2712K| 346K| | 5 | FAST DUAL | | 10000 | 1 | 10000 |00:00:00.01 | 0 | 0 |
----------------------------------------------------------------------------
-----------------------------------------------------
Predicate Information (identified by operation id):
3 - filter(("SUBINVENTORY_CODE"=:B3 AND INTERNAL_FUNCTION("MMT"."TRANSACTION_TYPE_ID"))) 4 - access("INVENTORY_ITEM_ID"=:B4 AND "ORGANIZATION_ID"=999)
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Mark W. Farnham
Sent: Saturday, April 27, 2013 10:36 PM
To: 'Oracle-L Freelists'
Subject: RE: SQL run time changed after DB upgrade but execution plan did
not change
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 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Sun Apr 28 2013 - 15:30:01 CEST