oracle – 优化器使用错误的索引
发布时间:2020-12-12 13:15:03 所属栏目:百科 来源:网络整理
导读:我们有一个非常简单的select语句访问具有唯一索引字段的数据.然而,优化器决定使用坏索引,选择大约需要4秒而不是0.0x秒. oracle 11g不存在这个问题,但是使用oracle 12c. 表统计信息是最新的. 看来,糟糕计划的估计是错误的(见下文),我们如何避免这种情况? 我
我们有一个非常简单的select语句访问具有唯一索引字段的数据.然而,优化器决定使用坏索引,选择大约需要4秒而不是0.0x秒.
oracle 11g不存在这个问题,但是使用oracle 12c. 表统计信息是最新的. 看来,糟糕计划的估计是错误的(见下文),我们如何避免这种情况? 我想通过添加字段统计或基线条目,但我希望有另一种解决方案. 提前致谢. 表定义 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,/* ... further fields ... */ cs_kondition VARCHAR2(20) not null) tablespace CS_APP pctfree 10 initrans 1 maxtrans 255 storage(initial 40K next 104K minextents 1 maxextents unlimited); 索引 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); 表大小 select count(*) from PS_CS_AKT_PROD_TB; --> 6372395 选择声明 给出了唯一索引的所有必填字段: 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; 执行细节和解释计划 select v.CHILD_NUMBER,elapsed_time / 1000000 elapsed_time,executions,round((elapsed_time / decode(executions,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'; /* CHILD_NUMBER ELAPSED_TIME EXECUTIONS ELAPSED_TIME_PER_EXEC DISK_READS BUFFER_GETS ROWS_PROCESSED CPU_TIME 0 400,874709 100 4,0087 98457 495295 86 5929096 1 0,017217 8 0,0022 2 36 4 2108 2 0,002038 2 0,001 0 9 1 0 */ select plan_table_output from table(dbms_xplan.display_cursor('dqrktmcraprvp',0)) t; /* SQL_ID dqrktmcraprvp,child number 0 ------------------------------------- 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 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 | --------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("RA_CMPGN_WAVE_ID"=:2 AND "CS_ABOART_CD"=:4)) 2 - access("BUSINESS_UNIT"=:1 AND "PRODUCT_ID"=:3) */ select plan_table_output from table(dbms_xplan.display_cursor('dqrktmcraprvp',1)) t; /* SQL_ID dqrktmcraprvp,child number 1 ------------------------------------- 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 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 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("BUSINESS_UNIT"=:1 AND "RA_CMPGN_WAVE_ID"=:2 AND "PRODUCT_ID"=:3 AND "CS_ABOART_CD"=:4) */ 修正案(见评论) PROCUCT_ID的字段值不均匀分布 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 dbms_xplan.display_cursor的输出(null,null,’OUTLINE’) 最多37秒: 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秒 SQL_ID ga79yhh54r5bu,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) 解决方法PRODUCT_ID字段的值不均匀分布(请参阅上面问题中的分发列表).因此,在极少数情况下,两个计划几乎相同.因为使用绑定变量调用语句,所以仅在第一次执行时处理估计. 同 alter session set "_optim_peek_user_binds"=false; 优化器被迫在第二次执行时重新评估绑定值. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |