oracle中SQL全表掃描過程分析

158次閱讀

共計 12011 個字符，預計需要花費 31 分鐘才能閱讀完成。

本篇內容主要講解“oracle 中 SQL 全表掃描過程分析”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“oracle 中 SQL 全表掃描過程分析”吧!

以下 SQL 走了全表掃描，效率下降，而 SQL 中謂詞字段選擇性非常低，通過直方圖，并從 btree 轉 bitmap 后性能提供，于是對此過程進行分析。

Select Count(*) From pmc.DesignXXXXX t Where 1=1 and OrganId= C00000220 And CategoryCode=2 and IsEnable=1 and isdelete=0 or (PublicStatus=1 and isdelete=0 );
 COUNT(*)
----------
 1845

較差的執行計劃：通過掃描表方式，邏輯讀需要 844525：

=====================================================

Execution Plan
----------------------------------------------------------
Plan hash value: 527126818
-----------------------------------------------------------------------------------
| Id | Operation  | Name  | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |  | 1| 19 | 229K (1)| 00:45:58 |
| 1 | SORT AGGREGATE |  | 1| 19 |  |   |
|* 2 | TABLE ACCESS FULL| DESIGNXXXXX | 4744K| 85M| 229K (1)| 00:45:58 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter( ISDELETE =0 AND ( PUBLICSTATUS =1 OR  ORGANID = C00000220 
  AND  CATEGORYCODE =2 AND  ISENABLE =1))
Statistics
----------------------------------------------------------
   1 recursive calls
   0 db block gets
 844525 consistent gets
 842418 physical reads
   0 redo size
  527 bytes sent via SQL*Net to client
  520 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
  1 rows processed

該 SQL 是如何選擇的執行計劃 (通過 10053 進行追蹤)：

oracle 進行了次以下幾種方式的 cost 比較：

1. 評估通過全表掃描需要的 cost 是 229760.92.

 Access Path: TableScan
 Cost: 229760.92 Resp: 229760.92 Degree: 0
 Cost_io: 229075.00 Cost_cpu: 25302994949
 Resp_io: 229075.00 Resp_cpu: 25302994949

2. 評估通過位圖索引的方式 cost 是 741028, 這里是已經同時用 bitmap 方式將 or 兩邊進行聯結的消耗。

****** trying bitmap/domain indexes ******

….

Bitmap nodes:

Used IND_DESIGNXXXXX_ISENABLE_ORG

Cost = 35.099036, sel = 0.000494

Used IND_DESIGNXXXXX_CATEGORYCODE

Cost = 1281.621955, sel = 0.034894

Bitmap nodes:

Used IND_PUBLICSTATUS

Cost = 17275.447942, sel = 0.471383

Used bitmap node

Bitmap nodes:

Used bitmap node

Access path: Bitmap index – accepted

Cost: 741028.481879 Cost_io: 740534.527080 Cost_cpu: 18221443693.247154 Sel: 0.471392

因為該語句中存在 or，即分別計算 or 左右的訪問路徑消耗，再來進行組合。

3.or 右邊通過 IND_PUBLICSTATUS 索引范圍掃描 cost 是 429957

Access Path: index (AllEqRange)

Index: IND_PUBLICSTATUS

resc_io: 429587.00 resc_cpu: 13681713060

ix_sel: 0.477347 ix_sel_with_filters: 0.477347

Cost: 429957.89 Resp: 429957.89 Degree: 1

4.or 左邊分別計算使用以下索引的的消耗

1)DESIGNXXXXX_TIME_ORGANID 的消耗是 88778。

Access Path: index (SkipScan)

Index: DESIGNXXXXX_TIME_ORGANID

resc_io: 88761.00 resc_cpu: 643271006

ix_sel: 0.000509 ix_sel_with_filters: 0.000509

Cost: 88778.44 Resp: 88778.44 Degree: 1

2)IND_DESIGNXXXXX_CATEGORYCODE 的消耗是 32961.

Access Path: index (AllEqRange)

Index: IND_DESIGNXXXXX_CATEGORYCODE

resc_io: 32934.00 resc_cpu: 1020893102

ix_sel: 0.036885 ix_sel_with_filters: 0.036885

Cost: 32961.67 Resp: 32961.67 Degree: 1

ColGroup Usage:: PredCnt: 2 Matches Full: #2 Partial: Sel: 0.0005

3)IND_DESIGNXXXXX_CATEGORYCODE 的消耗是 32961.

Access Path: index (AllEqRange)

Index: IND_DESIGNXXXXX_ISENABLE_ORG

resc_io: 6499.00 resc_cpu: 57845156

ix_sel: 0.000494 ix_sel_with_filters: 0.000494

Cost: 6500.57 Resp: 6500.57 Degree: 1

4) 單獨 IND_DESIGNXXXXX_ISENABLE_ORG 和 IND_DESIGNXXXXX_CATEGORYCODE 轉 bitmap 的消耗是 1406。

Bitmap nodes:

Used IND_DESIGNXXXXX_ISENABLE_ORG

Cost = 35.099036, sel = 0.000494

Used IND_DESIGNXXXXX_CATEGORYCODE

Cost = 1281.621955, sel = 0.034894

Access path: Bitmap index – accepted

Cost: 1406.374238 Cost_io: 1399.626467 Cost_cpu: 248917754.369408 Sel: 0.000017

這里需要注意的是將 or 左右兩邊分別拿出來計算，最終合并需要統計計算兩邊的消耗，因此以上的所有消耗評估是：

全表掃描 (Cost: 229760.92) IND_PUBLICSTATUS 索引（Cost: 429957.89）+ 任意左邊任意一種訪問路徑方式兩邊直接轉位圖聯結的方式（Cost: 741028）

于是自然而然選擇了全表掃描：

Final cost for query block SEL$1 (#0) – All Rows Plan:

Best join order: 1

Cost: 229760.9246 Degree: 1 Card: 1845.0000 Bytes: 35055

Resc: 229760.9246 Resc_io: 229075.0000 Resc_cpu: 25302994949

Resp: 229760.9246 Resp_io: 229075.0000 Resc_cpu: 25302994949

我們要知道以上都只是 oracle CBO 評估的結果，而在日常應用中 CBO 如果獲取的表信息不夠準確便為導致評估結果不一定是正確，而我們有時無法控制的是 SQL 每次硬解析時獲取信息是否足夠準確，這也是因此偶爾會出現執行計劃突變的狀況。

以上 SQL 通過收集直方圖后便可暫時得到解決。

這是收集直方圖后，較優的執行計劃：分別通過 btree 索引轉成 BITMAP 索引方式，邏輯讀需要 2196

================================================================

Execution Plan
----------------------------------------------------------
Plan hash value: 4067119963
--------------------------------------------------------------------------------------------------------------------
| Id | Operation  | Name  | Rows | Bytes | Cost (%CPU)| Time  |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |  |  1 | 19 | 647 (1)| 00:00:08 |
| 1 | SORT AGGREGATE   |  |  1 | 19 | |  |
|* 2 | TABLE ACCESS BY INDEX ROWID  | DESIGNXXXXX  | 1901 | 36119 | 647 (1)| 00:00:08 |
| 3 | BITMAP CONVERSION TO ROWIDS  |  |  |  | |  |
| 4 | BITMAP OR  |  |  |  | |  |
| 5 | BITMAP CONVERSION FROM ROWIDS |  |  |  | |  |
|* 6 | INDEX RANGE SCAN  | IND_PUBLICSTATUS  |  |  |  6 (0)| 00:00:01 |
| 7 | BITMAP AND   |  |  |  | |  |
| 8 | BITMAP CONVERSION FROM ROWIDS|  |  |  | |  |
|* 9 | INDEX RANGE SCAN   | IND_DESIGNXXXXx_ISENABLE_ORG |  |  |  3 (0)| 00:00:01 |
| 10 | BITMAP CONVERSION FROM ROWIDS|  |  |  | |  |
|* 11 | INDEX RANGE SCAN   | IND_DESIGNXXXXXX_CATEGORYCODE |  |  | 102 (0)| 00:00:02 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter(ISDELETE =0)
 6 - access(PUBLICSTATUS =1)
 9 - access(ISENABLE =1 AND  ORGANID = C00000220)
 11 - access(CATEGORYCODE =2)
Statistics
----------------------------------------------------------
  1 recursive calls
  0 db block gets
 2196 consistent gets
  0 physical reads
  0 redo size
 527 bytes sent via SQL*Net to client
 520 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

以上 BITMAP CONVERSION 的順序過程：

步驟 1.sql 通過 IND_PUBLICSTATUS 索引到表中獲取符合條件的行，然后從獲取的行中的 rowid 轉換成 bitmap，這一步是 BITMAP CONVERSION FROM ROWIDS。

步驟 2.sql 通過 IND_DESIGNXXXXX_CATEGORYCODE 索引到表中獲取符合條件的行，然后同樣從獲取的行中的 rowid 轉換成 bitmap，這一步是 BITMAP CONVERSION FROM ROWIDS。

步驟 3.sql 通過 IND_DESIGNXXXXX_ISENABLE_ORG 索引到表中獲取符合條件的行，然后同樣從獲取的行中的 rowid 轉換成 bitmap，這一步是 BITMAP CONVERSION FROM ROWIDS。

步驟 4.sql 將步驟 2 和步驟 3 所得 bitmap 數據通過 BITMAP AND 方式取交集。

步驟 5.sql 將步驟 1 所得 bitmaps 數據與步驟 4 通過 BITMAP OR 方式取并集。

步驟 6.sql 將步驟 5 最終獲取的并集 bitmap 數據轉換成 ROWIDS，這一步是 BITMAP CONVERSION TO ROWIDS。

步驟 7.sql 將步驟 6 獲取的 rowid 通過回表方式到表中獲取所需要的字段數據，這一步是 ABLE ACCESS BY INDEX ROWID。

為什么會這樣:

當對表中的唯一度不高的列建立了 index，oracle 就有可能選擇轉為 bitmap 來執行。查看 sql 中 where 條件后字段都是選擇性非常的低。

相應字段選擇性：

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY
------------------------------ ---------- ----------- -----------
ORGANID 21095783 2070 .01
CATEGORYCODE 21095783 29 0
ISENABLE 21095783 2 0
ISDELETE 21095783 2 0
PUBLISHSTATE 21095783 1 0
對應索引：INDEX_NAME INDEX_COL INDEX_TYPE 
-------------------------------- ---------------------- ----------------------
PMC.IND_DESIGNXXXXX_CATEGORYCODE CATEGORYCODE NORMAL-NONUNIQUE 
PMC.IND_DESIGNXXXXX_ISENABLE_ORG ISENABLE,ORGANID NORMAL-NONUNIQUE 
PMC.IND_PUBLICSTATUS PUBLICSTATUS NORMAL-NONUNIQUE

同樣使用 10053 追蹤增加直方圖后 SQL 執行，此時 CBO 為什么可以選擇到轉位圖的執行計劃，發現增加直方圖之后評估消耗只需要 647，而在此之前所需消耗要高達 741028。

增加直方圖后的評估：

Access path: Bitmap index – accepted

Cost: 647.047103 Cost_io: 646.348285 Cost_cpu: 25778603.541021 Sel: 0.000103

對比未增加直方圖之前的評估：

Access path: Bitmap index – accepted

Cost: 741028.481879 Cost_io: 740534.527080 Cost_cpu: 18221443693.247154 Sel: 0.471392

為什么收集直方圖后評估的消耗可以這么低？

在 oracle CBO 計算 cost 主要是 IO 成本 +CPU 成本，在計算成本之前,CBO 會收集以下統計信息:

列中不同值的數量也就是 NDV

列中的最小值 / 最大值

列中 null 值的數量

數據分布

直方圖信息（前提是收集直方圖）

對比收集直方圖前后的字段信息：

收集直方圖之前的字段信息：

Column (#4): ORGANID(

AvgLen: 10 NDV: 2023 Nulls: 4717 Density: 0.000494

Column (#29): CATEGORYCODE(

AvgLen: 2 NDV: 27 Nulls: 1164044 Density: 0.037037 Min: 0 Max: 66

Column (#38): ISENABLE(

AvgLen: 2 NDV: 2 Nulls: 1151627 Density: 0.500000 Min: 0 Max: 1

Column (#14): ISDELETE(

AvgLen: 3 NDV: 2 Nulls: 0 Density: 0.500000 Min: 0 Max: 1

Column (#32): PUBLICSTATUS(

AvgLen: 2 NDV: 2 Nulls: 1151554 Density: 0.500000 Min: 0 Max: 1

收集直方圖之后的字段信息：

Single Table Cardinality Estimation for DESIGNXXXXX[T]

Column (#14):

NewDensity:0.041803, OldDensity:0.000000 BktCnt:6033548, PopBktCnt:6033548, PopValCnt:2, NDV:2

Column (#14): ISDELETE(

AvgLen: 3 NDV: 2 Nulls: 0 Density: 0.041803 Min: 0 Max: 1

Histogram: Freq #Bkts: 2 UncompBkts: 6033548 EndPtVals: 2

Column (#4):

NewDensity:0.000185, OldDensity:0.001779 BktCnt:254, PopBktCnt:160, PopValCnt:25, NDV:2027

Column (#4): ORGANID(

AvgLen: 10 NDV: 2027 Nulls: 4830 Density: 0.000185

Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 120

Column (#29):

NewDensity:0.000000, OldDensity:0.000000 BktCnt:5680066, PopBktCnt:5680055, PopValCnt:16, NDV:27

Column (#29): CATEGORYCODE(

AvgLen: 2 NDV: 27 Nulls: 1162620 Density: 0.000000 Min: 0 Max: 66

Histogram: Freq #Bkts: 27 UncompBkts: 5680066 EndPtVals: 27

Column (#38):

NewDensity:0.000943, OldDensity:0.000000 BktCnt:5687407, PopBktCnt:5687407, PopValCnt:2, NDV:2

Column (#38): ISENABLE(

AvgLen: 2 NDV: 2 Nulls: 1150490 Density: 0.000943 Min: 0 Max: 1

Histogram: Freq #Bkts: 2 UncompBkts: 5687407 EndPtVals: 2

ColGroup (#2, Index) IND_DESIGNXXXXX_ISENABLE_ORG

Col#: 4 38 CorStregth: 2.00

ColGroup (#3, Index) IND_DESIGNXXXXX_AUTHOR_TIME

Col#: 6 7 CorStregth: -1.00

ColGroup (#1, Index) DESIGNXXXXX_TIME_ORGANID

Col#: 4 7 CorStregth: -1.00

ColGroup Usage:: PredCnt: 3 Matches Full: Partial:

Column (#32):

NewDensity:0.000055, OldDensity:0.000000 BktCnt:5688611, PopBktCnt:5688611, PopValCnt:2, NDV:2

Column (#32): PUBLICSTATUS(

AvgLen: 2 NDV: 2 Nulls: 1150387 Density: 0.000055 Min: 0 Max: 1

Histogram: Freq #Bkts: 2 UncompBkts: 5688611 EndPtVals: 2

在沒有收集直方圖之前，發現有部分字段的 Density 都是 0.5，這個值是從 1 /NDV(基數) 得到的，這是因為 CBO 有時無法正確的統計到表的數據分布，但當收集直方圖后該值就改變了，因為在一個表中，不一定所有的數據都能分配平均，直方圖的作用就是能找出這種不平均，

那 PUBLICSTATUS 字段來說，我們看到 NDV 是 2，即是說全表之后兩個值，這兩個值是 0 或 1，在沒有收集直方圖之前 CBO 可能會認為 0 和 1 的分布是各一半，此時他去評估訪問該字段的路徑可能是全表掃描比較好，

而實際上，表中 PUBLICSTATUS=1 的數據量非常少。

sys@LVDB SQL Select Count(*) From pmc.DesignXXXXX t where PublicStatus=1 and isdelete=0 ;

COUNT(*)

———-

1845

但直到 PUBLICSTATUS 的數據分布后，CBO 評估通過 IND_PUBLICSTATUS 索引訪問 cost 只需要 6。這也是為什么收集直方圖后能更加準確的評估訪問表的消耗了。

Access Path: index (AllEqRange)

Index: IND_PUBLICSTATUS

resc_io: 6.00 resc_cpu: 457729

ix_sel: 0.000112 ix_sel_with_filters: 0.000112

Cost: 6.01 Resp: 6.01 Degree: 0

然后該種 0 或 1 的情況選擇了轉換成 bitmap 索引的模式。

其實如果不選擇 btree 轉換 bitmap 方式，直接使用 btree 索引回表效率也是沒問題的，只是需要將 sql 中的 or 拆成 union 語句

Execution Plan
----------------------------------------------------------
Plan hash value: 3766559296
------------------------------------------------------------------------------------------------------------------
| Id | Operation  | Name   | Rows | Bytes | Cost (%CPU)| Time  |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |  | 1 | 13 | 105 (2)| 00:00:02 |
| 1 | SORT AGGREGATE   |  | 1 | 13 |  |   |
| 2 | VIEW  |  | 2 | 26 | 105 (2)| 00:00:02 |
| 3 | SORT UNIQUE  |  | 2 | 22 | 105 (2)| 00:00:02 |
| 4 | UNION-ALL  |  |  |  |  |   |
| 5 | SORT AGGREGATE  |  | 1 | 17 | 9 (12)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| DESIGXXXXXXX   | 1 | 17 | 8 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN   | IND_DESIGNXXXXXX_ISENABLE_ORG | 6 |  | 3 (0)| 00:00:01 |
| 8 | SORT AGGREGATE  |  | 1 | 5 | 96 (2)| 00:00:02 |
|* 9 | TABLE ACCESS BY INDEX ROWID| DESIGNXXXXXXX   | 1874 | 9370 | 95 (0)| 00:00:02 |
|* 10 | INDEX RANGE SCAN   | IND_PUBLICSTATUS  | 2046 |  | 6 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 6 - filter(CATEGORYCODE =2 AND  ISDELETE =0)
 7 - access(ISENABLE =1 AND  ORGANID = C00000281)
 9 - filter(ISDELETE =0)
 10 - access(PUBLICSTATUS =1)
Statistics
----------------------------------------------------------
  1 recursive calls
  0 db block gets
 2114 consistent gets
  0 physical reads
  0 redo size
 527 bytes sent via SQL*Net to client
 520 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  1 sorts (memory)
  0 sorts (disk)
  1 rows processed

對于開啟直方圖和 btree 轉 Bitma 都各自存在某些 bug, 有時甚至可能引發異常的性能問題，這點是需要重點注意的。

到此，相信大家對“oracle 中 SQL 全表掃描過程分析”有了更深的了解，不妨來實際操作一番吧！這里是丸趣 TV 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！

正文完