Re: Optimizer path
- From: Gaja Krishna Vaidyanatha <gajav@xxxxxxxxx>
- To: Oracle-L List <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 6 Mar 2012 13:31:56 -0800 (PST)
Hi Amir,
Would you mind posting a 10053 trace at level 1 for the 2 SQL statements in
question? This will eliminate the "guesswork" as to why the optimizer is doing
something. Bottom line - it is dealing with some condition driven by the
statistics (i.e. cardinality) and/or could just be getting something wrong due
to a bug. Won't know which one it is until one does the trace.
Cheers,
Gaja
Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC
http://www.dbperfman.com
http://www.dbcloudman.com
Phone - +1-650-743-6060
http://www.linkedin.com/in/gajakrishnavaidyanathaCo-author:Oracle
Insights:Tales of the Oak Table
- http://www.apress.com/book/bookDisplay.html?bID14
Co-author:Oracle Performance Tuning 101
- http://www.amazon.com/gp/reader/0072131454/ref=sib_dp_pt/102-6130796-4625766
Enabling Cloud Deployment & Management for Oracle Databases
________________________________
From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Monday, March 5, 2012 12:44 PM
Subject: Optimizer path
Folks,
I have a statement, which produces the following plan, which is based on
FTS:
SELECT *
FROM <TABLE_NAME>
WHERE reprocess_flag = 'Y'
AND processing_phase = 1
AND processing_status_code2 = 'Running'
AND processing_mode_code2 = 'Normal'
;
------------------------------------------------------------------------
------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
------------------------------------------------------------------------
------------------
| 0 | SELECT STATEMENT | | 139K| 52M| 5158
(1)| 00:01:02 |
|* 1 | TABLE ACCESS FULL| TXRWI0_WIP_CMPL_INTF | 139K| 52M| 5158
(1)| 00:01:02 |
------------------------------------------------------------------------
------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PROCESSING_PHASE"=1 AND "REPROCESS_FLAG"='Y' AND
"PROCESSING_STATUS_CODE2"='Running' AND
"PROCESSING_MODE_CODE2"='Normal')
When the same statement is run with a little modification in the WHERE
clause, it produces a different and more efficient execution plan:
SELECT *
FROM <TABLE_NAME>
WHERE reprocess_flag = 'Y'
AND processing_phase = 1
AND upper(processing_status_code2) = 'RUNNING'
AND upper(processing_mode_code2 )= 'NORMAL'
;
------------------------------------------------------------------------
----------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------
----------------------------
| 0 | SELECT STATEMENT | | 1 |
397 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TXRWI0_WIP_CMPL_INTF | 1 |
397 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IXRWI0_WIP_CMPL_IDX5 | 1 |
| 3 (0)| 00:00:01 |
------------------------------------------------------------------------
----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("REPROCESS_FLAG"='Y')
2 - access("PROCESSING_PHASE"=1)
filter(UPPER("PROCESSING_STATUS_CODE2")='RUNNING' AND
UPPER("PROCESSING_MODE_CODE2")='NORMAL')
Below is the what the index IXRWI0_WIP_CMPL_IDX5 looks like:
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------- ---------------
IXRWI0_WIP_CMPL_IDX5 PROCESSING_PHASE 1
PROCESSING_STATUS_CODE2 2
PROCESSING_MODE_CODE2 3
INDEX_NAME IND_ROWS DISTINCT_KEYS
CLUSTERING_FACTOR TAB_ROWS TAB_BLOCKS
------------------------------ ---------- -------------
----------------- ---------- ----------
IXRWI0_WIP_CMPL_IDX5 281189 1
11220 282400 18882
All of the columns of the index have bad selectivity, individually, as
well as combined:
select processing_phase, processing_status_code2, processing_mode_code2
, count(*) from txrwi0_wip_cmpl_intf group by
processing_phase, processing_status_code2, processing_mode_code2
;
PROCESSING_PHASE PROCESSING_STATUS_CODE2 PROCESSING_MODE_CODE2
COUNT(*)
---------------- ------------------------------
------------------------------ ----------
4 Completed Normal
283934
1 Completed Error
2
I have the following question:
- When the statement is run with functions in the WHERE clause,
the optimizer decided to use the leading column of the index to fetch
rows, even though the selectivity of that column was bad. What made the
optimizer go that route and why it did not choose it with the first run?
The eventual solution of the problem is to use histograms on column
PROCESSING_PHASE, which worked fine.
Thanks
Amir
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
