Statistics on Column Groups
單個列統計資訊對於判斷where子句中的單個謂詞的選擇性是非常有用的。然而，當where子句中包含來自相同表的不同列的多個謂詞時，單個列統計資訊不能顯示列之間的關係。使用列組(column group)就是用來解決這個問題的。最佳化器單獨計算謂詞的選擇性，然後合併它們。然而，如果在單列之間存在關聯，那麼最佳化器當評估基數時不會考慮它，最佳化器會使用每個表謂詞的選擇性來乘以行數來評估基數。

下面的語句查詢dba_tab_col_statistics表來顯示關於sh.customers表中列cust_state_province與country_id列的統計資訊。

SQL> COL COLUMN_NAME FORMAT a20
SQL> COL NDV FORMAT 999
SQL> SELECT COLUMN_NAME, NUM_DISTINCT AS "NDV", HISTOGRAM
  2  FROM DBA_TAB_COL_STATISTICS
  3  WHERE OWNER = 'SH'
  4  AND TABLE_NAME = 'CUSTOMERS'
  5  AND COLUMN_NAME IN ('CUST_STATE_PROVINCE', 'COUNTRY_ID');
COLUMN_NAME           NDV HISTOGRAM
-------------------- ---- ---------------
CUST_STATE_PROVINCE   145 FREQUENCY
COUNTRY_ID             19 FREQUENCY

下面的語句查詢住在California的客戶人數3341人:

SQL> SELECT COUNT(*)
  2  FROM sh.customers
  3  WHERE cust_state_province = 'CA';
  COUNT(*)
----------
      3341

來顯示查詢state為CA，country_id為52790(USA)的客戶人數的查詢執行

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM sh.customers
  4  WHERE cust_state_province = 'CA'
  5  AND country_id=52790;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  1115 |   205K|   423   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |  1115 |   205K|   423   (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790)
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel thre
shold
17 rows selected.

基於單列country_id和cust_state_province列的統計資訊，最佳化器評估住在USA的California的客戶人數是1115,而實際上有3341人，但最佳化器不知道，所以透過所有謂詞減少了返回的行數因此大大降低了評估基數。可以透過收集列組統計資訊來讓最佳化器知曉列country_id與cust_state_province之間的真實關係。

自動與手動列組統計資訊
Oracle資料庫可以自動或手動建立列組統計資訊。最佳化器可以使用SQL執行計劃指令來生成更優的執行計劃。如果dbms_stats引用引數auto_stat_extensions被設定為ON(預設值為OFF)，那麼SQL執行計劃指令基於工作量中謂詞的使用情況可以自動觸發來建立列組統計資訊。可以透過set_table_prefs,set_global_prefs或set_schema_prefs過程來設定auto_stat_extensions。

當想要手動管理列組統計資訊時，可以使用dbms_stats來執行以下操作:
.探測列組
.建立以前探測到的列組
.手動建立列組並收集列組統計資訊

列組統計資訊使用者介面
有幾個dbms_stats程式單元有與列組相關的引用引數
seed_col_usage過程，迭代指定工作量中的SQL語句，編譯它們，然後檢視在這些語句謂詞中出現列的使用資訊。為了決定合適的列組，資料庫必須觀察一個有代表性的工作量。在監控期間不需要執行查詢本身。可以對在工作量中那些執行時間長的查詢執行explain plan來確保資料庫記錄這些查詢所使用的列組資訊。

report_col_usage函式，生成一個報告列出在工作量中所看到的過濾謂詞，連線謂詞與group by子句中的列。可以使用這個函式來檢查對於指定表所記錄的列使用資訊。

create_extended_stats函式，建立擴充套件，它可以是列組或表示式。當使用者手動或自動統計資訊收集任務對錶收集統計資訊時資料庫會對擴充套件收集統計資訊。

auto_stat_extensions引用引數，控制自動建立擴充套件，包括列組，當最佳化器統計資訊被收集時，使用set_table_prefs,set_schema_prefs或set_global_prefs來設定這個引用引數。當auto_stat_extensions被設定為off(預設值)時，資料庫不會自動建立列組統計資訊。為了建立擴充套件，你必須執行create_extended_stats函式或在dbms_stats API中的method_opt引數中顯性指定擴充套件統計資訊。當auto_stat_extensions設定為ON時，一個SQL執行計劃指令基於工作量中謂詞中列的使用資訊可以觸發自動建立列組統計資訊。

為特定的工作量檢測有用的列組
可以使用dbms_stats.seed_col_usage與report_col_usage來基於特定工作量來決定那個表需要列組。當你不知道需要建立什麼樣的擴充套件統計資訊時這種技術很有用。這種技術對於擴充套件統計資訊不會工作。

假設存在以下情況:
.查詢sh.customers_test表(用customers表來建立)並在謂詞中使用了country_id與cust_state_province列但基數評估不正確。

.想要資料庫監控工作量5分鐘(300秒)。

.想要資料庫自動判斷需要那些列組。

為了檢測列組需要執行以下操作:
1.啟動SQL*Plus或SQL Developer，並以使用者sh登入資料庫

2.建立表customers_test並收集統計資訊:

SQL> DROP TABLE customers_test;
Table dropped.
SQL> CREATE TABLE customers_test AS SELECT * FROM customers;
Table created.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');
PL/SQL procedure successfully completed.

3.啟用工作量監控
在不同的SQL*Plus會話中，以sys使用者登入並執行以下的PL/SQL程式來啟用監控300秒:

SQL> BEGIN
  2  DBMS_STATS.SEED_COL_USAGE(null,null,300);
  3  END;
  4  /
PL/SQL procedure successfully completed.

4.以使用者sh來在使用工作量的情況下對兩個查詢解析它們的執行計劃。

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM customers_test
  4  WHERE cust_city = 'Los Angeles'
  5  AND cust_state_province = 'CA'
  6  AND country_id = 52790;
Explained.
SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 2112738156
----------------------------------------------------
| Id  | Operation         | Name           | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |
|   1 |  TABLE ACCESS FULL| CUSTOMERS_TEST |     1 |
----------------------------------------------------
8 rows selected.
SQL> EXPLAIN PLAN FOR
  2  SELECT country_id, cust_state_province, count(cust_city)
  3  FROM customers_test
  4  GROUP BY country_id, cust_state_province;
Explained.
SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 1820398555
-----------------------------------------------------
| Id  | Operation          | Name           | Rows  |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |                |  1949 |
|   1 |  HASH GROUP BY     |                |  1949 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
-----------------------------------------------------
9 rows selected.

第一個執行計劃顯示基數為1而查詢返回932行記錄，第二個執行計劃顯示基數為1949而查詢返回145行記錄。

5.可選操作，檢查對錶customers_test所記錄的列使用資訊

SQL> SET LONG 100000
SQL> SET LINES 120
SQL> SET PAGES 0
SQL> SELECT DBMS_STATS.REPORT_COL_USAGE(user, 'customers_test')
  2  FROM DUAL;
LEGEND:
.......
EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
LIKE       : Used in single table LIKE predicate
NULL       : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER     : Used in single table FILTER predicate
JOIN       : Used in JOIN predicate
GROUP_BY   : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR SH.CUSTOMERS_TEST
.........................................
1. COUNTRY_ID                          : EQ
2. CUST_CITY                           : EQ
3. CUST_STATE_PROVINCE                 : EQ
4. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                        : FILTER
5. (CUST_STATE_PROVINCE, COUNTRY_ID)   : GROUP_BY
###############################################################################

在上面的報告中，前三個列是第一個監控查詢中等值謂詞中所使用的三個列：

...
WHERE cust_city = 'Los Angeles'
AND cust_state_province = 'CA'
AND country_id = 52790;

所有三個列出現在相同的where子句中，因此報告顯示他們作為一組。在第二個查詢中，兩個列出現在group by子句中，因此報告標記它們作為group_by。在filter與group_by中的列組就是列組的候選者。

在工作量監控下建立所檢測到的列組
可以使用dbms_stats.create_extended_stats函式來為執行dbms_stats.seed_col_usage所檢測到的列組來建立列組，具體操作如下:
1.基於在監控視窗期間所捕獲到的列使用資訊來為customers_test表建立列組，執行下面的查詢

SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, 'customers_test') FROM DUAL;
###############################################################################
EXTENSIONS FOR SH.CUSTOMERS_TEST
................................
1. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                        : SYS_STUMZ$C3AIHLPBROI#SKA58H_N created
2. (CUST_STATE_PROVINCE, COUNTRY_ID)   : SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ created
###############################################################################

資料庫將為customers_test表建立兩個列組:一個列組是過濾謂詞，一個列組是group by操作。

2.重新收集表統計資訊

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');
PL/SQL procedure successfully completed.

3.以使用者sh來查詢user_tab_col_statistics檢視來判斷資料庫建立了那些額外統計資訊:

SQL> SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
  2  FROM USER_TAB_COL_STATISTICS
  3  WHERE TABLE_NAME = 'CUSTOMERS_TEST'
  4  ORDER BY 1;
COUNTRY_ID                                                   19 FREQUENCY
CUST_CITY                                                   620 HYBRID
CUST_CITY_ID                                                620 NONE
CUST_CREDIT_LIMIT                                             8 NONE
CUST_EFF_FROM                                                 1 NONE
CUST_EFF_TO                                                   0 NONE
CUST_EMAIL                                                 1699 NONE
CUST_FIRST_NAME                                            1300 NONE
CUST_GENDER                                                   2 NONE
CUST_ID                                                   55500 NONE
CUST_INCOME_LEVEL                                            12 NONE
CUST_LAST_NAME                                              908 NONE
CUST_MAIN_PHONE_NUMBER                                    51344 NONE
CUST_MARITAL_STATUS                                          11 NONE
CUST_POSTAL_CODE                                            623 NONE
CUST_SRC_ID                                                   0 NONE
CUST_STATE_PROVINCE                                         145 FREQUENCY
CUST_STATE_PROVINCE_ID                                      145 NONE
CUST_STREET_ADDRESS                                       49900 NONE
CUST_TOTAL                                                    1 NONE
CUST_TOTAL_ID                                                 1 NONE
CUST_VALID                                                    2 NONE
CUST_YEAR_OF_BIRTH                                           75 NONE
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_                              145 NONE
SYS_STUMZ$C3AIHLPBROI#SKA58H_N                              620 HYBRID
25 rows selected.

上面的查詢顯示了由dbms_stats.create_extended_stats函式所返回的兩個列組名。為CUST_CITY, CUST_STATE_PROVINCE和COUNTRY_ID列所建立的列組有一個HYBRID型別的直方圖統計資訊。

4.再次解析之前的兩個查詢語句的執行計劃

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM customers_test
  4  WHERE cust_city = 'Los Angeles'
  5  AND cust_state_province = 'CA'
  6  AND country_id = 52790;
Explained.
SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 2112738156
----------------------------------------------------
| Id  | Operation         | Name           | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT  |                |   874 |
|   1 |  TABLE ACCESS FULL| CUSTOMERS_TEST |   874 |
----------------------------------------------------
8 rows selected.
SQL> EXPLAIN PLAN FOR
  2  SELECT country_id, cust_state_province, count(cust_city)
  3  FROM customers_test
  4  GROUP BY country_id, cust_state_province;
Explained.
SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 1820398555
-----------------------------------------------------
| Id  | Operation          | Name           | Rows  |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |                |   145 |
|   1 |  HASH GROUP BY     |                |   145 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
-----------------------------------------------------
9 rows selected.

第一個查詢評估的基數是874，要返回的記錄數是932，第二個查詢評估的基數是145，要返回的記錄數是145,這樣基數評估的記錄數與實際返回的記錄已經非常接近了，這就是列組統計資訊所帶來的好處。

手動建立與收集列組統計資訊
在有些情況下，可能知道想要建立的列組。dbms_stats.gather_table_stats函式的method_opt引數可以自動建立與收集列組統計資訊。可以透過使用for columns來指定列組從而來建立一個新的列組。

假設存在以下情況:
.想要對sh.customers表上的cust_state_province與country_id列建立列組。

.想要對sh.customers表與新的列組收集統計資訊。

手動建立與收集列組統計資訊執行以下操作:
1.啟動SQL*Plus並以sh使用者登入資料庫。

2.使用以下PL/SQL程式來建立列組並收集統計資訊:

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS( 'sh','customers',
  3  METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY ' ||
  4  'FOR COLUMNS SIZE SKEWONLY (cust_state_province,country_id)' );
  5  END;
  6  /
PL/SQL procedure successfully completed.

顯示列組資訊
為了獲得列組名，可以使用dbms_stats.show_extended_stats_name函式或資料庫檢視。也可以使用檢視來獲得資訊比如，distinct值的數量與列組是否有直方圖統計資訊。
1.啟動SQL*Plus並以sh使用者登入資料庫。

2.為了獲得列組名，執行以下PL/SQL程式

SQL> SELECT SYS.DBMS_STATS.SHOW_EXTENDED_STATS_NAME( 'sh','customers',
  2  '(cust_state_province,country_id)' ) col_group_name
  3  FROM DUAL;
COL_GROUP_NAME
------------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_

查詢user_stat_extensions檢視

SQL> SELECT EXTENSION_NAME, EXTENSION
  2  FROM USER_STAT_EXTENSIONS
  3  WHERE TABLE_NAME='CUSTOMERS';
EXTENSION_NAME                                                                                                                   EXTENSION
-----------------------------------------------------------------------                                                          ------------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_                                                                                                   ("CUST_STATE_PROVINCE","COUNTRY_ID")

3.查詢建立的列組的distinct值的數量並檢視是否建立了直方圖

SQL> SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM
  2  FROM USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
  3  WHERE e.EXTENSION_NAME=t.COLUMN_NAME
  4  AND e.TABLE_NAME=t.TABLE_NAME
  5  AND t.TABLE_NAME='CUSTOMERS';
COL_GROUP                                                                        NUM_DISTINCT HISTOGRAM
----------------------------------------------------------------------           ------------ ---------
("CUST_STATE_PROVINCE","COUNTRY_ID")                                                      145 FREQUENCY

刪除列組
可以使用dbms_stats.drop_extended_stats函式來從表中刪除列組

SQL> BEGIN
  2  DBMS_STATS.DROP_EXTENDED_STATS( 'sh', 'customers',
  3  '(cust_state_province, country_id)' );
  4  END;
  5  /
PL/SQL procedure successfully completed.

Oracle 12C Statistics on Column Groups

相關文章