RE: Difference in Execution Plan - Same Environment, Same SQL
Date: Wed, 1 May 2013 15:21:42 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E8879DEB22FF_at_NADCWPMSGCMS10.hca.corpad.net>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ross Lafferty
Sent: Wednesday, May 01, 2013 3:16 PM
To: oracle-l_at_freelists.org
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 |Predicate Information (identified by operation id):
| 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 | ------------------------------------------------------------------------------------------------------------------------
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 |Predicate Information (identified by operation id):
| 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 | ------------------------------------------------------------------------------------------------------------
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_at_gmail.com ============== -- B. Ross Lafferty ============== M: 412-608-7505 E: ross.lafferty_at_gmail.com ============== -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed May 01 2013 - 22:21:42 CEST