[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

------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

| 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(*)

-------------------- ----------

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

------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------

| 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

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博客

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

最新文章