We have a fairly simple selection statement that accesses data with unique index fields. However, the optimizer decides to use a bad index, and the selection takes about 4 seconds instead of 0.0x seconds.
This problem does not exist with oracle 11g, but with oracle 12c.
Table statistics updated.
It seems that evaluating a bad plan is wrong (see below), how can we avoid this?
I think by adding field statistics or source records, but I hope there is another solution.
Thanks in advance.
Table definition
create table PS_CS_AKT_PROD_TB(business_unit VARCHAR2(5) not null, ra_cmpgn_wave_id VARCHAR2(15) not null, product_id VARCHAR2(18) not null, cs_aboart_cd VARCHAR2(20) not null, cs_einweis_id VARCHAR2(20) not null, row_added_dttm TIMESTAMP(6), row_added_oprid VARCHAR2(30) not null, row_lastmant_dttm TIMESTAMP(6), row_lastmant_oprid VARCHAR2(30) not null, cs_recstat_xl VARCHAR2(4) not null, cs_kondition VARCHAR2(20) not null) tablespace CS_APP pctfree 10 initrans 1 maxtrans 255 storage(initial 40K next 104K minextents 1 maxextents unlimited);
Indices
create unique index PS_CS_AKT_PROD_TB on PS_CS_AKT_PROD_TB( BUSINESS_UNIT,RA_CMPGN_WAVE_ID,PRODUCT_ID,CS_ABOART_CD) tablespace PSINDEX pctfree 10 initrans 2 maxtrans 255 storage(initial 40K next 104K minextents 1 maxextents unlimited); create index PSBCS_AKT_PROD_TB on PS_CS_AKT_PROD_TB( BUSINESS_UNIT,PRODUCT_ID) tablespace PSINDEX pctfree 10 initrans 2 maxtrans 255 storage(initial 40K next 104K minextents 1 maxextents unlimited);
Table size
select count(*) from PS_CS_AKT_PROD_TB;
Select expression
All required fields of a unique index are given:
SELECT CS_STEUERUNG_XL, CS_EWF2EVT FROM PS_CS_AKT_PROD_TB WHERE BUSINESS_UNIT = :1 AND RA_CMPGN_WAVE_ID = :2 AND PRODUCT_ID = :3 AND CS_ABOART_CD = :4;
Implementation Details and Explain Plan
select v.CHILD_NUMBER, elapsed_time / 1000000 elapsed_time, executions, round((elapsed_time / decode(executions, 0, 1, executions)) / 1000000, 4) elapsed_time_per_exec, disk_reads, buffer_gets, rows_processed, cpu_time from v$sql v where v.SQL_ID = 'dqrktmcraprvp'; select plan_table_output from table(dbms_xplan.display_cursor('dqrktmcraprvp', 0)) t; select plan_table_output from table(dbms_xplan.display_cursor('dqrktmcraprvp', 1)) t;
Amendment (see comments)
PROCUCT_ID field values are not evenly distributed
OCCURENCE_OF_PRODUCT_ID TOTAL upto 10^1-1 1134 upto 10^2-1 1607 upto 10^3-1 1649 upto 10^4-1 455 upto 10^5-1 279
Output dbms_xplan.display_cursor (null, null, '+ OUTLINE')
up to 37 seconds:
SQL_ID ga79yhh54r5bu, child number 0 ------------------------------------- select a.cs_ewf2evt, a.cs_steuerung_xl from ps_cs_akt_prod_tb a where a.business_unit = :1 and a.ra_cmpgn_wave_id = :2 and a.product_id = :3 and a.cs_aboart_cd = :4 Plan hash value: 1118713352 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| PS_CS_AKT_PROD_TB | 1 | 46 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | PSBCS_AKT_PROD_TB | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.1.0.2') DB_VERSION('12.1.0.2') OPT_PARAM('_optimizer_max_permutations' 50) OPT_PARAM('_unnest_subquery' 'false') OPT_PARAM('optimizer_dynamic_sampling' 4) OPT_PARAM('_gby_hash_aggregation_enabled' 'false') OPT_PARAM('_optimizer_use_feedback' 'false') OPT_PARAM('_px_adaptive_dist_method' 'off') OPT_PARAM('_optimizer_dsdir_usage_control' 0) OPT_PARAM('_optimizer_adaptive_plans' 'false') OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false') OPT_PARAM('_optimizer_gather_feedback' 'false') OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false') OPT_PARAM('optimizer_index_cost_adj' 20) ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("PS_CS_AKT_PROD_TB"."BUSINESS_UNIT" "PS_CS_AKT_PROD_TB"."PRODUCT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "A"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("A"."RA_CMPGN_WAVE_ID"=:2 AND "A"."CS_ABOART_CD"=:4)) 2 - access("A"."BUSINESS_UNIT"=:1 AND "A"."PRODUCT_ID"=:3)
~ 0.06 seconds
SQL_ID ga79yhh54r5bu, child number 0 ------------------------------------- select a.cs_ewf2evt, a.cs_steuerung_xl from ps_cs_akt_prod_tb a where a.business_unit = :1 and a.ra_cmpgn_wave_id = :2 and a.product_id = :3 and a.cs_aboart_cd = :4 Plan hash value: 619225732 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| PS_CS_AKT_PROD_TB | 1 | 46 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PS_CS_AKT_PROD_TB | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.1.0.2') DB_VERSION('12.1.0.2') OPT_PARAM('_optimizer_max_permutations' 50) OPT_PARAM('_unnest_subquery' 'false') OPT_PARAM('optimizer_dynamic_sampling' 4) OPT_PARAM('_gby_hash_aggregation_enabled' 'false') OPT_PARAM('_optimizer_use_feedback' 'false') OPT_PARAM('_px_adaptive_dist_method' 'off') OPT_PARAM('_optimizer_dsdir_usage_control' 0) OPT_PARAM('_optimizer_adaptive_plans' 'false') OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false') OPT_PARAM('_optimizer_gather_feedback' 'false') OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false') OPT_PARAM('optimizer_index_cost_adj' 20) ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("PS_CS_AKT_PROD_TB"."BUSINESS_UNIT" "PS_CS_AKT_PROD_TB"."RA_CMPGN_WAVE_ID" "PS_CS_AKT_PROD_TB"."PRODUCT_ID" "PS_CS_AKT_PROD_TB"."CS_ABOART_CD")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."BUSINESS_UNIT"=:1 AND "A"."RA_CMPGN_WAVE_ID"=:2 AND "A"."PRODUCT_ID"=:3 AND "A"."CS_ABOART_CD"=:4)