[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');
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2222415/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2222415/