[20181201]奇怪的INDEX SKIP SCAN执行计划.txt

来源：这里教程网时间：2026-03-03 12:15:40 作者：

[20181201]奇怪的INDEX SKIP SCAN执行计划.txt --//工作中遇到的问题,有时候希望出现INDEX SKIP SCAN,有时候希望它不出现,总之对于这个不好控制. --//比如链接:http://blog.itpub.net/267265/viewspace-2213256/,并没有选择INDEX SKIP SCAN. --//一般我认为仅仅前缀选择率很低,查询谓词不包括前缀,走index skip scan也许合适或者出现. --//我前一段时间遇到问题,实际上在第2,3列也可能出现INDEX SKIP SCAN, --//链接 :http://blog.itpub.net/267265/viewspace-2168467/ --//实际工作中遇到的一些情况非常让自己困惑: 1.环境: SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 SCOTT@test01p> create table t as select * from dba_objects; Table created. SCOTT@test01p> create index i_t_object_id_object_type on t(OBJECT_ID,OBJECT_TYPE); Index created. --//分析略. 2.测试: SCOTT@test01p> alter session set statistics_level=all; Session altered. SCOTT@test01p> select count(object_name) from t where object_TYPE='TABLE'; COUNT(OBJECT_NAME) ------------------ 1795 --//注意:object_name 定义包含可以输入null,执行时一定会回表. SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 5x9kf84fsnz36, child number 0 ------------------------------------- select count(object_name) from t where object_TYPE='TABLE' Plan hash value: 1271557081 ------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 84 (100)| | 1 |00:00:00.01 | 220 | | 1 | SORT AGGREGATE | | 1 | 1 | 27 | | | 1 |00:00:00.01 | 220 | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 531 | 14337 | 84 (0)| 00:00:01 | 1795 |00:00:00.01 | 220 | |* 3 | INDEX SKIP SCAN | I_T_OBJECT_ID_OBJECT_TYPE | 1 | 531 | | 71 (0)| 00:00:01 | 1795 |00:00:00.01 | 71 | ------------------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T@SEL$1 3 - SEL$1 / T@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OBJECT_TYPE"='TABLE') filter("OBJECT_TYPE"='TABLE') SCOTT@test01p> select count(distinct object_type),count(distinct object_id),count(*) from t; COUNT(DISTINCTOBJECT_TYPE) COUNT(DISTINCTOBJECT_ID) COUNT(*) -------------------------- ------------------------ ---------- 42 22298 22300 --//object_id 字段选择性很好,按照道理不应该选择INDEX SKIP SCAN. SCOTT@test01p> select /*+ full(t) */ count(object_name) from t where object_TYPE='TABLE'; COUNT(OBJECT_NAME) ------------------ 1795 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID auqna0b3c02dd, child number 0 ------------------------------------- select /*+ full(t) */ count(object_name) from t where object_TYPE='TABLE' Plan hash value: 2966233522 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 113 (100)| | 1 |00:00:00.01 | 409 | | 1 | SORT AGGREGATE | | 1 | 1 | 27 | | | 1 |00:00:00.01 | 409 | |* 2 | TABLE ACCESS FULL| T | 1 | 531 | 14337 | 113 (0)| 00:00:01 | 1795 |00:00:00.01 | 409 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_TYPE"='TABLE') --//视乎走跳跃索引扫描要好一点. 3.继续测试: SCOTT@test01p> select * from (select /*+ full(t) */ object_type,count(*) from t group by object_type order by 2 desc) where rownum<=3; OBJECT_TYPE COUNT(*) -------------------- ---------- VIEW 6514 SYNONYM 5856 INDEX 2329 --//OBJECT_TYPE='VIEW'的记录最多,换成它查询看看. SCOTT@test01p> select count(object_name) from t where object_TYPE='VIEW'; COUNT(OBJECT_NAME) ------------------ 6514 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID dmh6d16acm77n, child number 0 ------------------------------------- select count(object_name) from t where object_TYPE='VIEW' Plan hash value: 1271557081 ------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 84 (100)| | 1 |00:00:00.03 | 348 | | 1 | SORT AGGREGATE | | 1 | 1 | 27 | | | 1 |00:00:00.03 | 348 | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 531 | 14337 | 84 (0)| 00:00:01 | 6514 |00:00:00.02 | 348 | |* 3 | INDEX SKIP SCAN | I_T_OBJECT_ID_OBJECT_TYPE | 1 | 531 | | 71 (0)| 00:00:01 | 6514 |00:00:00.02 | 71 | ------------------------------------------------------------------------------------------------------------------------------------------------------------ --//加大object_type='VIEW'的记录. SCOTT@test01p> update t set object_type='VIEW' where object_type='SYNONYM' and rownum<=5855; 5855 rows updated. SCOTT@test01p> commit ; Commit complete. SCOTT@test01p> select count(object_name) from t where object_TYPE='VIEW'; COUNT(OBJECT_NAME) ------------------ 12369 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID dmh6d16acm77n, child number 0 ------------------------------------- select count(object_name) from t where object_TYPE='VIEW' Plan hash value: 1271557081 ------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 84 (100)| | 1 |00:00:00.03 | 455 | | 1 | SORT AGGREGATE | | 1 | 1 | 27 | | | 1 |00:00:00.03 | 455 | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 531 | 14337 | 84 (0)| 00:00:01 | 12369 |00:00:00.03 | 455 | |* 3 | INDEX SKIP SCAN | I_T_OBJECT_ID_OBJECT_TYPE | 1 | 531 | | 71 (0)| 00:00:01 | 12369 |00:00:00.02 | 132 | ------------------------------------------------------------------------------------------------------------------------------------------------------------ --//重新rebuild 索引看看. SCOTT@test01p> alter index I_T_OBJECT_ID_OBJECT_TYPE rebuild; Index altered. SCOTT@test01p> select count(object_name) from t where object_TYPE='VIEW'; COUNT(OBJECT_NAME) ------------------ 12369 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID dmh6d16acm77n, child number 0 ------------------------------------- select count(object_name) from t where object_TYPE='VIEW' Plan hash value: 1271557081 ------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 81 (100)| | 1 |00:00:00.01 | 391 | | 1 | SORT AGGREGATE | | 1 | 1 | 27 | | | 1 |00:00:00.01 | 391 | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 531 | 14337 | 81 (0)| 00:00:01 | 12369 |00:00:00.01 | 391 | |* 3 | INDEX SKIP SCAN | I_T_OBJECT_ID_OBJECT_TYPE | 1 | 531 | | 68 (0)| 00:00:01 | 12369 |00:00:00.01 | 68 | ------------------------------------------------------------------------------------------------------------------------------------------------------------ 4.分析执行计划看看: SCOTT@test01p> @ 10053x dmh6d16acm77n 0 PL/SQL procedure successfully completed. --//查看转储文件部分内容: *************************************** SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for T[T] SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE kkecdn: Single Table Predicate:"T"."OBJECT_TYPE"='VIEW' Column (#6): OBJECT_TYPE(VARCHAR2) AvgLen: 7 NDV: 42 Nulls: 0 Density: 0.023810 Estimated selectivity: 0.023810 , col: #6 Table: T Alias: T Card: Original: 22300.000000 Rounded: 531 Computed: 530.952381 Non Adjusted: 530.952381 Scan IO Cost (Disk) = 113.000000 Scan CPU Cost (Disk) = 8487668.960000 Cost of predicates: io = NOCOST, cpu = 50.000000, sel = 0.023810 flag = 2048 ("T"."OBJECT_TYPE"='VIEW') Total Scan IO Cost = 113.000000 (scan (Disk)) + 0.000000 (io filter eval) (= 0.000000 (per row) * 22300.000000 (#rows)) = 113.000000 Total Scan CPU Cost = 8487668.960000 (scan (Disk)) + 1115000.000000 (cpu filter eval) (= 50.000000 (per row) * 22300.000000 (#rows)) = 9602668.960000 Access Path: TableScan Cost: 113.425738 Resp: 113.425738 Degree: 0 Cost_io: 113.000000 Cost_cpu: 9602669 Resp_io: 113.000000 Resp_cpu: 9602669 kkofmx: index filter:"T"."OBJECT_TYPE"='VIEW' ****** Costing Index I_T_OBJECT_ID_OBJECT_TYPE SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SKIP_SCAN Estimated selectivity: 0.023810 , col: #6 Estimated selectivity: 0.023810 , col: #6 Access Path: index (skip-scan) SS scan sel: 0.023810 SS filter sel: 0.023810 ANDV (#skips): 22298.000000 SS io: 67.000000 vs. table scan io: 113.000000 Skip Scan chosen Access Path: index (SkipScan) Index: I_T_OBJECT_ID_OBJECT_TYPE resc_io: 81.000000 resc_cpu: 815787 ix_sel: 0.023810 ix_sel_with_filters: 0.023810 Cost: 81.036168 Resp: 81.036168 Degree: 1 Best:: AccessPath: IndexRange Index: I_T_OBJECT_ID_OBJECT_TYPE Cost: 81.036168 Degree: 1 Resp: 81.036168 Card: 530.952381 Bytes: 0.000000 check parallelism for statement[<unnamed>] kkfdPaPrm.1:curInst:4, curpxEnabled=1, curCPUCount=1 kkfdPaPrm.2:sessInst:4, sesspxEnabled=1, sesCPUCount=1 kkfdPaForcePrm: dop:1 () use dictionary DOP(1) on table kkfdPaPrm:- The table : 22989 kkfdPaPrm:DOP = 1 (computed from hint/dictionary/autodop) kkfdiPaPrm: dop:1 serial(?) flags: 1 *************************************** --//太复杂,放弃!! 5.总结: --//总之感觉对INDEX SKIP SCAN很难控制. --//另外,exadata我感觉更佳趋向于smart scan,或者采用布隆过滤.参考链接http://blog.itpub.net/267265/viewspace-2213256/ --//附上10053x.sql脚本 execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1');