- SQL優化核心思想
- 羅炳森 黃超 鐘僥
- 2633字
- 2019-08-06 10:11:04
1.3 直方圖（HISTOGRAM）
前面提到，當某個列基數很低，該列數據分布就會不均衡。數據分布不均衡會導致在查詢該列的時候，要么走全表掃描，要么走索引掃描，這個時候很容易走錯執行計劃。
如果沒有對基數低的列收集直方圖統計信息，基于成本的優化器（CBO）會認為該列數據分布是均衡的。
下面我們還是以測試表test為例，用實驗講解直方圖。
首先我們對測試表test收集統計信息，在收集統計信息的時候，不收集列的直方圖，語句for all columns size 1表示對所有列都不收集直方圖。
SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', 3 tabname => 'TEST', 4 estimate_percent => 100, 5 method_opt => 'for all columns size 1', 6 no_invalidate => FALSE, 7 degree => 1, 8 cascade => TRUE); 9 END; 10 / PL/SQL procedure successfully completed.
Histogram為none表示沒有收集直方圖。
SQL> select a.column_name, 2 b.num_rows, 3 a.num_distinct Cardinality, 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity, 5 a.histogram, 6 a.num_buckets 7 from dba_tab_col_statistics a, dba_tables b 8 where a.owner = b.owner 9 and a.table_name = b.table_name 10 and a.owner = 'SCOTT' 11 and a.table_name = 'TEST'; COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS --------------- ---------- ----------- ----------- --------- ----------- OWNER 72462 29 .04 NONE 1 OBJECT_NAME 72462 44236 61.05 NONE 1 SUBOBJECT_NAME 72462 106 .15 NONE 1 OBJECT_ID 72462 72462 100 NONE 1 DATA_OBJECT_ID 72462 7608 10.5 NONE 1 OBJECT_TYPE 72462 44 .06 NONE 1 CREATED 72462 1366 1.89 NONE 1 LAST_DDL_TIME 72462 1412 1.95 NONE 1 TIMESTAMP 72462 1480 2.04 NONE 1 STATUS 72462 1 0 NONE 1 TEMPORARY 72462 2 0 NONE 1 GENERATED 72462 2 0 NONE 1 SECONDARY 72462 2 0 NONE 1 NAMESPACE 72462 21 .03 NONE 1 EDITION_NAME 72462 0 0 NONE 0 15 rows selected.
owner列基數很低，現在我們對owner列進行查詢。
SQL> set autot trace SQL> select * from test where owner='SCOTT'; 7 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2499 | 236K| 289 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| TEST | 2499 | 236K| 289 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OWNER"='SCOTT')
請注意看粗體字部分，查詢owner＝'SCOTT'返回了7條數據，但是CBO在計算Rows的時候認為owner='SCOTT'返回2 499條數據，Rows估算得不是特別準確。從72 462條數據里面查詢出7條數據，應該走索引，所以現在我們對owner列創建索引。
SQL> create index idx_owner on test(owner); Index created. ``` 我們再來查詢一下。 ``` SQL> select * from test where owner='SCOTT'; 7 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3932013684 ------------------------------------------------------------------------------------- | Id |Operation |Name | Rows | Bytes | Cost(%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2499 | 236K | 73 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID |TEST | 2499 | 236K | 73 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN |IDX_OWNER| 2499 | | 6 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='SCOTT')
``` 現在我們查詢`owner='SYS'`。
SQL> select * from test where owner='SYS'; 30808 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3932013684 ------------------------------------------------------------------------------------- | Id |Operation | Name | Rows | Bytes | Cost(%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2499 | 236K| 73 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2499 | 236K| 73 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_OWNER| 2499 | | 6 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='SYS')
注意粗字體部分，查詢owner='SYS'返回了30 808條數據。從72 462條數據里面返回30 808條數據能走索引嗎？很明顯應該走全表掃描。也就是說該執行計劃是錯誤的。
為什么查詢owner='SYS'的執行計劃會用錯呢？因為owner這個列基數很低，只有29，而表的總行數是72 462。前文著重強調過，當列沒有收集直方圖統計信息的時候，CBO會認為該列數據分布是均衡的。正是因為CBO認為owner列數據分布是均衡的，不管owner等于任何值，CBO估算的Rows永遠都是2 499。而這2 499是怎么來的呢？答案如下。
SQL> select round(72462/29) from dual;
round(72462/29)
--------------
2499
現在大家也知道了，執行計劃里面的Rows是假的。執行計劃中的Rows是根據統計信息以及一些數學公式計算出來的。很多DBA到現在還不知道執行計劃中Rows是假的這個真相，真是令人遺憾。
在做SQL優化的時候，經常需要做的工作就是幫助CBO計算出比較準確的Rows。注意：我們說的是比較準確的Rows。CBO是無法得到精確的Rows的，因為對表收集統計信息的時候，統計信息一般都不會按照100%的標準采樣收集，即使按照100%的標準采樣收集了表的統計信息，表中的數據也隨時在發生變更。另外計算Rows的數學公式目前也是有缺陷的，CBO永遠不可能計算得到精確的Rows。
如果CBO每次都能計算得到精確的Rows，那么相信我們這個時候只需要關心業務邏輯、表設計、SQL寫法以及如何建立索引了，再也不用擔心SQL會走錯執行計劃了。
Oracle12c的新功能SQL Plan Directives在一定程度上解決了Rows估算不準而引發的SQL性能問題。關于SQL Plan Directives，本書不做過多討論。
為了讓CBO選擇正確的執行計劃，我們需要對owner列收集直方圖信息，從而告知CBO該列數據分布不均衡，讓CBO在計算Rows的時候參考直方圖統計。現在我們對owner列收集直方圖。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'TEST',
4 estimate_percent => 100,
5 method_opt => 'for columns owner size skewonly',
6 no_invalidate => FALSE,
7 degree => 1,
8 cascade => TRUE);
9 END;
10 /
PL/SQL procedure successfully completed.
查看一下owner列的直方圖信息。
SQL> select a.column_name, 2 b.num_rows, 3 a.num_distinct Cardinality, 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity, 5 a.histogram, 6 a.num_buckets 7 from dba_tab_col_statistics a, dba_tables b 8 where a.owner = b.owner 9 and a.table_name = b.table_name 10 and a.owner = 'SCOTT' 11 and a.table_name = 'TEST'; COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS --------------- ---------- ----------- ----------- ---------- ----------- OWNER 72462 29 .04 FREQUENCY 29 OBJECT_NAME 72462 44236 61.05 NONE 1 SUBOBJECT_NAME 72462 106 .15 NONE 1 OBJECT_ID 72462 72462 100 NONE 1 DATA_OBJECT_ID 72462 7608 10.5 NONE 1 OBJECT_TYPE 72462 44 .06 NONE 1 CREATED 72462 1366 1.89 NONE 1 LAST_DDL_TIME 72462 1412 1.95 NONE 1 TIMESTAMP 72462 1480 2.04 NONE 1 STATUS 72462 1 0 NONE 1 TEMPORARY 72462 2 0 NONE 1 GENERATED 72462 2 0 NONE 1 SECONDARY 72462 2 0 NONE 1 NAMESPACE 72462 21 .03 NONE 1 EDITION_NAME 72462 0 0 NONE 0 15 rows selected.
現在我們再來查詢上面的SQL，看執行計劃是否還會走錯并且驗證Rows是否還會算錯。
SQL> select * from test where owner='SCOTT'; 7 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3932013684 ------------------------------------------------------------------------------------- | Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7 | 679 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 7 | 679 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_OWNER| 7 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='SCOTT') SQL> select * from test where owner='SYS'; 30808 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 30808 | 2918K| 290 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| TEST | 30808 | 2918K| 290 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OWNER"='SYS')
對owner列收集完直方圖之后，CBO估算的Rows就基本準確了，一旦Rows估算對了，那么執行計劃也就不會出錯了。
大家是不是很好奇，為什么收集完直方圖之后，Rows計算得那么精確，收集直方圖究竟完成了什么操作呢？對owner列收集直方圖其實就相當于運行了以下SQL。
select owner,count(*) from test group by owner;
直方圖信息就是以上SQL的查詢結果，這些查詢結果會保存在數據字典中。這樣當我們查詢owner為任意值的時候，CBO總會算出正確的Rows，因為直方圖已經知道每個值有多少行數據。
如果SQL使用了綁定變量，綁定變量的列收集了直方圖，那么該SQL就會引起綁定變量窺探。綁定變量窺探是一個老生常談的問題，這里不多做討論。Oracle11g引入了自適應游標共享（Adaptive Cursor Sharing），基本上解決了綁定變量窺探問題，但是自適應游標共享也會引起一些新問題，對此也不做過多討論。
當我們遇到一個SQL有綁定變量怎么辦？其實很簡單，我們只需要運行以下語句。
select 列, count(*) from test group by 列 order by 2 desc;
如果列數據分布均衡，基本上SQL不會出現問題；如果列數據分布不均衡，我們需要對列收集直方圖統計。
關于直方圖，其實還有非常多的話題，比如直方圖的種類、直方圖的桶數等，本書在此不做過多討論。在我們看來，讀者只需要知道直方圖是用來幫助CBO在對基數很低、數據分布不均衡的列進行Rows估算的時候，可以得到更精確的Rows就夠了。
什么樣的列需要收集直方圖呢？當列出現在where條件中，列的選擇性小于1%并且該列沒有收集過直方圖，這樣的列就應該收集直方圖。注意：千萬不能對沒有出現在where條件中的列收集直方圖。對沒有出現在where條件中的列收集直方圖完全是做無用功，浪費數據庫資源。
下面我們為大家分享本書第二個全自動化優化腳本。
抓出必須創建直方圖的列（大家可以對該腳本進行適當修改，以便用于生產環境）。
SQL> select a.owner, 2 a.table_name, 3 a.column_name, 4 b.num_rows, 5 a.num_distinct, 6 trunc(num_distinct / num_rows * 100,2) selectivity, 7 'Need Gather Histogram' notice 8 from dba_tab_col_statistics a, dba_tables b 9 where a.owner = 'SCOTT' 10 and a.table_name = 'TEST' 11 and a.owner = b.owner 12 and a.table_name = b.table_name 13 and num_distinct / num_rows<0.01 14 and (a.owner, a.table_name, a.column_name) in 15 (select r.name owner, o.name table_name, c.name column_name 16 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r 17 where o.obj# = u.obj# 18 and c.obj# = u.obj# 19 and c.col# = u.intcol# 20 and r.name = 'SCOTT' 21 and o.name = 'TEST') 22 and a.histogram ='NONE'; OWNER TABLE COLUM NUM_ROWS NUM_DISTINCT SELECTIVITY NOTICE ----- ----- ----- ---------- ------------ ----------- ---------------------- SCOTT TEST OWNER 72462 29 .04 Need Gather Histogram
- ASP.NET Core：Cloud-ready，Enterprise Web Application Development
- VMware vSphere 6.7虛擬化架構實戰指南
- Linux環境編程：從應用到內核
- Reactive Programming With Java 9
- Building Minecraft Server Modifications
- Python編程實戰
- Learning OpenStack Networking（Neutron）
- 詳解MATLAB圖形繪制技術
- Python Automation Cookbook
- JavaScript Unit Testing
- Developer，Advocate！
- 微信公眾平臺開發最佳實踐
- Daniel Arbuckle's Mastering Python
- Mathematica Data Visualization
- Real-time Analytics with Storm and Cassandra