RE: Difference in Execution Plan - Same Environment, Same SQL
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <ross.lafferty@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 May 2013 15:21:42 -0500
SQL Plan baselines maybe?
Upgrading from Oracle Database 10g to 11g:
What to expect from the Optimizer
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-upgrading-10g-to-11g-what-to-ex-133707.pdf
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
In Oracle Database 11g a new feature called SQL Plan Management (SPM) has been
introduced
to guarantees any plan changes that do occur lead to better performance. When
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to TRUE (default FALSE) Oracle will
automatically capture a SQL plan baseline for every repeatable SQL statement on
the system.
The execution plan found at parse time will be added to the SQL plan baseline
as an accepted
plan.
Chris
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Ross Lafferty
Sent: Wednesday, May 01, 2013 3:16 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Difference in Execution Plan - Same Environment, Same SQL
Running into interesting behavior with execution plans in 11.2.0.3 (running on
Exadata x3 platform). The below query was executed at roughly the same time,
against the same environment, originating from different clients (different
machines all together). The first one listed below, using hash joins, returns
results in ~1 minute. The second one listed below, using nested loops, doesn't
return, even after hours. No alter sessions have been performed.
OPTIMIZER_MODE for the environment is ALL_ROWS (which should gravitate towards
hash joins).
Query:
SELECT DEL.AE_LINE_ID,
DEL.MD_CHANGE_NUMBER,
DEL.MD_SOURCE_SYSTEM,
A.DETAIL_KEY
FROM DEL_CST_AE_LINES_REF DEL, MFG_PERIODICACCTLINES_DET A
WHERE DEL.ODS_CHANGE_FLAG = 'Y'
AND DEL.MD_SOURCE_SYSTEM = 1.00000000000000
AND DEL.AE_LINE_ID = A.MD_LOOKUP_VALUE
AND DEL.MD_SOURCE_SYSTEM = A.MD_SOURCE_SYSTEM;
Table Sizes:
TABLE_NAME NUM_ROWS
------------------------------ ----------
MFG_PERIODICACCTLINES_DET 8991569
DEL_CST_AE_LINES_REF 155693
Statistics on both tables have been gathered within 1 hour of the execution,
and have not changed (as no rows appear in dba_tab_modifications) since
statistics were last gathered.
Result coming in under 1 minute:
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | | 58143 (100)| |
|* 1 | HASH JOIN | |
155K| 5171K| 4272K| 58143 (1)| 00:13:35 |
|* 2 | TABLE ACCESS STORAGE FULL | DEL_CST_AE_LINES_REF |
155K| 2432K| | 192 (2)| 00:00:03 |
| 3 | VIEW | index$_join$_002 |
8991K| 154M| | 46742 (1)| 00:10:55 |
|* 4 | HASH JOIN | |
| | | | |
| 5 | INDEX STORAGE FAST FULL SCAN| MFG_PERIODICACCTLINES_DET_PK |
8991K| 154M| | 12313 (1)| 00:02:53 |
|* 6 | INDEX STORAGE FAST FULL SCAN| MFG_PERIODICACCTLI_IX2 |
8991K| 154M| | 17640 (1)| 00:04:07 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEL"."AE_LINE_ID"=TO_NUMBER("A"."MD_LOOKUP_VALUE") AND
"DEL"."MD_SOURCE_SYSTEM"="A"."MD_SOURCE_SYSTEM")
2 - storage(("DEL"."ODS_CHANGE_FLAG"=:SYS_B_0 AND
"DEL"."MD_SOURCE_SYSTEM"=:SYS_B_1))
filter(("DEL"."ODS_CHANGE_FLAG"=:SYS_B_0 AND
"DEL"."MD_SOURCE_SYSTEM"=:SYS_B_1))
4 - access(ROWID=ROWID)
6 - storage("A"."MD_SOURCE_SYSTEM"=:SYS_B_1)
filter("A"."MD_SOURCE_SYSTEM"=:SYS_B_1)
Will not Return (killed after 2 hours):
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 3669 (100)| |
| 1 | NESTED LOOPS | | 1 |
33 | 3669 (1)| 00:00:52 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEL_CST_AE_HEADERS_REF | 1 |
16 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEL_CST_AE_HEADERS_REF_IX2 | 1 |
| 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| MFG_PERIODICACCTHEADERS_DET | 1 |
17 | 3667 (1)| 00:00:52 |
|* 5 | INDEX SKIP SCAN | MFG_PERIODICACCTHEAD_IX2 | 1 |
| 3666 (1)| 00:00:52 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEL"."ODS_CHANGE_FLAG"=:SYS_B_0 AND
"DEL"."MD_SOURCE_SYSTEM"=:SYS_B_1)
5 - access("A"."MD_SOURCE_SYSTEM"=:SYS_B_1)
filter(("A"."MD_SOURCE_SYSTEM"=:SYS_B_1 AND
"DEL"."AE_HEADER_ID"=TO_NUMBER("A"."MD_LOOKUP_VALUE")))
My question is, why the varying execution path for the same query, same
environment, being run at the same time - and any way to correct it?
Thanks in advance!
--
B. Ross Lafferty
==============
E: ross.lafferty@xxxxxxxxx
==============
--
B. Ross Lafferty
==============
M: 412-608-7505
E: ross.lafferty@xxxxxxxxx
==============
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
