RE: SQL run time changed after DB upgrade but execution plan did not change
- From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
- To: "mwf@xxxxxxxx" <mwf@xxxxxxxx>, 'Oracle-L Freelists' <Oracle-L@xxxxxxxxxxxxx>
- Date: Sun, 28 Apr 2013 13:30:01 +0000
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Hameed, Amir
Sent: Saturday, April 27, 2013 10:40 PM
To: mwf@xxxxxxxx; '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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Hameed, Amir
Sent: Saturday, April 27, 2013 5:50 PM
To: jonathan@xxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Saturday, April 27, 2013 11:46 AM
To: oracle-l@xxxxxxxxxxxxx
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@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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
