大佬教程收集整理的这篇文章主要介绍了oracle – 优化器使用错误的索引，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。
Oracle 11g不存在这个问题,但是使用Oracle 12c.
表统计信息是最新的.
看来,糟糕计划的估计是错误的(见下文),我们如何避免这种情况？
我想通过添加字段统计或基线条目,但我希望有另一种解决方案.
提前致谢.
表定义
@R_772_2102@ 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 @R_812_10586@L 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)
以上是大佬教程为你收集整理的oracle – 优化器使用错误的索引全部内容，希望文章能够帮你解决oracle – 优化器使用错误的索引所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。