Hi,
I will be as short as I can:
- DB 10.2.0.3,
- statistics gathered automatically in mainteinance windows timeframe ('FOR ALL COLUMNS SIZE AUTO', AUTO_SAMPLE_SIZE (5-20%), AUTO_CASCADE)
- program table with these statistics (good times) for column program_id: 382.680 rows, density: 0.00000261314936..., null values=0 - an unique index is created on this column
- index on UPPER(program_id) with the following statistics (good times): 382.680 rows, 5019 blocks, distinct keys 382.680, clustering factor = 365.665, index level 2
- the following select that causes trouble (with literal as it is implemented right now in code... :( ):
SELECT DISTINCT T1.PROGRAM_ID,
T1.PARENT_ID,
T1.PROGRAM_TITLE,
T1.DURATION_QTY,
T1.DUE_DT,
T1.PURGE_DT,
T1.PROGRAM_STATUS_ID,
T1.CREATE_DTTM,
T1.LAST_UPDT_DTTM,
T1.ORIGIN,
T1.ASSET_CATEGORY,
T1.DISPLAY_NAME,
T1.PROGRAM_ALIAS
FROM PROGRAM_V T1
WHERE UPPER (T1.PROGRAM_ID) = '0X060A2B340101010001010D1113FFFFFF0604060B635305063A18AABBCCDDEEFF';
**************************
CREATE OR REPLACE VIEW MS_V2_1_0.PROGRAM_V
(
PROGRAM_ID,
PARENT_ID,
PROGRAM_TITLE,
PROGRAM_STATUS_ID,
DURATION_QTY,
DUE_DT,
PURGE_DT,
CREATE_DTTM,
LAST_UPDT_DTTM,
ORIGIN,
DISPLAY_NAME,
PROGRAM_ALIAS,
ASSET_CATEGORY
)
AS
SELECT program.program_id,
parent_program.program_id,
program.program_title,
program_status.program_status_id,
program.duration_qty,
program.due_dt,
program.purge_dt,
program.create_dttm,
program.last_updt_dttm,
program.origin,
program.display_name,
NVL (program.display_name, program.program_id) program_alias,
program.asset_category
FROM program, program_status, program parent_program
WHERE program_status.program_status_num = program.program_status_num
AND program.parent_num = parent_program.program_num(+)
AND program_status.program_status_id <> 'DELETED'
WITH READ ONLY;
The following 10053 traces were caught in bad times and in good times. The difference are as follows, if we need more, I will post them:
GOOD TIMES
============
Plan Table
============
-----------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 8 | |
| 1 | HASH UNIQUE | | 1 | 240 | 8 | 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 240 | 7 | 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 169 | 5 | 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | PROGRAM | 1 | 159 | 4 | 00:00:01 |
| 5 | INDEX RANGE SCAN | PROGRAMS_UPP | 1 | | 3 | 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | PROGRAM_STATUS | 1 | 10 | 1 | 00:00:01 |
| 7 | INDEX UNIQUE SCAN | PROGRAM_STATUS_PK| 1 | | 0 | |
| 8 | TABLE ACCESS BY INDEX ROWID | PROGRAM | 1 | 71 | 2 | 00:00:01 |
| 9 | INDEX UNIQUE SCAN | PROGRAMS_PK | 1 | | 1 | 00:00:01 |
-----------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
5 - access("PROGRAM"."SYS_NC00019$"='0X060A2B340101010001010D1113FFFFFF0604060B635305063A18AABBCCDDEEFF')
6 - filter("PROGRAM_STATUS"."PROGRAM_STATUS_ID"<>'DELETED')
7 - access("PROGRAM_STATUS"."PROGRAM_STATUS_NUM"="PROGRAM"."PROGRAM_STATUS_NUM")
9 - access("PROGRAM"."PARENT_NUM"="PARENT_PROGRAM"."PROGRAM_NUM")
***************************************
SINGLE TABLE ACCESS PATH
Column (#19): SYS_NC00019$(VARCHAR2)
AvgLen: 66.00 NDV: 382673 Nulls: 0 Density: 2.6132e-006
Table: PROGRAM Alias: PROGRAM
Card: Original: 382673 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: TableScan
Cost: 2222.10 Resp: 2222.10 Degree: 0
Cost_io: 2210.00 Cost_cpu: 270895180
Resp_io: 2210.00 Resp_cpu: 270895180
Access Path: index (AllEqRange)
Index: PROGRAMS_UPP
resc_io: 4.00 resc_cpu: 29706
*********** ix_sel: 2.6132e-006 ix_sel_with_filters: 2.6132e-006
Cost: 4.00 Resp: 4.00 Degree: 1
*********** Best:: AccessPath: IndexRange Index: PROGRAMS_UPP
Cost: 4.00 Degree: 1 Resp: 4.00 Card: 1.00 Bytes: 0
***************************************
BAD TIMES
============
Plan Table
============
-------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 19K | |
| 1 | HASH UNIQUE | | 221K | 52M | 19K | 00:04:54 |
| 2 | HASH JOIN | | 221K | 52M | 7775 | 00:02:34 |
| 3 | VIEW | index$_join$_003 | 2 | 20 | 3 | 00:00:01 |
| 4 | HASH JOIN | | | | | |
| 5 | INDEX FAST FULL SCAN | PROGRAM_STATUS_PK | 2 | 20 | 1 | 00:00:01 |
| 6 | INDEX FAST FULL SCAN | PROGRAM_STATUS_UNIQ| 2 | 20 | 1 | 00:00:01 |
| 7 | HASH JOIN RIGHT OUTER | | 221K | 50M | 7770 | 00:02:34 |
| 8 | TABLE ACCESS FULL | PROGRAM | 373K | 26M | 2216 | 00:00:27 |
| 9 | TABLE ACCESS FULL | PROGRAM | 221K | 34M | 2222 | 00:00:27 |
-------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("PROGRAM_STATUS"."PROGRAM_STATUS_NUM"="PROGRAM"."PROGRAM_STATUS_NUM")
3 - filter("PROGRAM_STATUS"."PROGRAM_STATUS_ID"<>'DELETED')
4 - access(ROWID=ROWID)
6 - filter("PROGRAM_STATUS"."PROGRAM_STATUS_ID"<>'DELETED')
7 - access("PROGRAM"."PARENT_NUM"="PARENT_PROGRAM"."PROGRAM_NUM")
9 - filter(UPPER("PROGRAM"."PROGRAM_ID")='0X060A2B3401010101010D1113dFFFFFF0604060B63530s5063A18AABB1CCDDEEFF')
***************************************
SINGLE TABLE ACCESS PATH
Column (#19): SYS_NC00019$(VARCHAR2)
AvgLen: 66.00 NDV: 13366 Nulls: 0 Density: 2.6940e-006
Histogram: HtBal #Bkts: 135 UncompBkts: 135 EndPtVals: 8
Table: PROGRAM Alias: PROGRAM
Card: Original: 381651 Rounded: 226164 Computed: 226163.56 Non Adjusted: 226163.56
Access Path: TableScan
Cost: 2222.48 Resp: 2222.48 Degree: 0
Cost_io: 2210.00 Cost_cpu: 279410260
Resp_io: 2210.00 Resp_cpu: 279410260
Access Path: index (AllEqRange)
Index: PROGRAMS_UPP
resc_io: 219132.00 resc_cpu: 1698286810
*********** ix_sel: 0.59259 ix_sel_with_filters: 0.59259
Cost: 219207.88 Resp: 219207.88 Degree: 1
*********** Best:: AccessPath: TableScan
Cost: 2222.48 Degree: 1 Resp: 2222.48 Card: 226163.56 Bytes: 0
***************************************
Bad plan comes after gathering statistics automatically with the options stated at the beginning of the thread, good plan comes after gathering statistics with SIZE 1 (no histograms) and mostly with estimated_percent = 100 (at least 20).
No rows inserted / updated / deleted meantime. Just gather schema statistics with histograms and auto_sample and bad execution plan kicks in.
Please advise.
Thank you,
Edited by: user11312555 on 23.06.2010 05:55
Edited by: user11312555 on Jun 23, 2010 1:50 PM