溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容主要講解“oracle中SQL全表掃描過程分析”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“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
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全表掃描過程分析”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
