溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“Oracle 12C中的Statistics on Column Groups分析”，在日常操作中，相信很多人在Oracle 12C中的Statistics on Column Groups分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”Oracle 12C中的Statistics on Column Groups分析”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
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分析”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
