[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');

[20181201]奇怪的INDEX SKIP SCAN執行計劃.txt

相關文章