這篇文章主要介紹“Oracle 12C中的Statistics on Column Groups分析”，在日常操作中，相信很多人在Oracle 12C中的Statistics on Column Groups分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”O(jiān)racle 12C中的Statistics on Column Groups分析”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
10年積累的成都網(wǎng)站制作、成都網(wǎng)站設(shè)計、外貿(mào)營銷網(wǎng)站建設(shè)經(jīng)驗，可以快速應(yīng)對客戶對網(wǎng)站的新想法和需求。提供各種問題對應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認識你，你也不認識我。但先網(wǎng)站設(shè)計制作后付款的網(wǎng)站建設(shè)流程，更有觀山湖免費網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。
Statistics on Column Groups
單個列統(tǒng)計信息對于判斷where子句中的單個謂詞的選擇性是非常有用的。然而，當where子句中包含來自相同表的不同列的多個謂詞時，單個列統(tǒng)計信息不能顯示列之間的關(guān)系。使用列組(column group)就是用來解決這個問題的。優(yōu)化器單獨計算謂詞的選擇性，然后合并它們。然而，如果在單列之間存在關(guān)聯(lián)，那么優(yōu)化器當評估基數(shù)時不會考慮它，優(yōu)化器會使用每個表謂詞的選擇性來乘以行數(shù)來評估基數(shù)。
下面的語句查詢dba_tab_col_statistics表來顯示關(guān)于sh.customers表中列cust_state_province與country_id列的統(tǒng)計信息。
重慶分公司
重慶分公司
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的客戶人數(shù)3341人:
SQL> SELECT COUNT(*) 2 FROM sh.customers 3 WHERE cust_state_province = 'CA'; COUNT(*) ---------- 3341
來顯示查詢state為CA，country_id為52790(USA)的客戶人數(shù)的查詢執(zhí)行
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列的統(tǒng)計信息，優(yōu)化器評估住在USA的California的客戶人數(shù)是1115,而實際上有3341人，但優(yōu)化器不知道，所以通過所有謂詞減少了返回的行數(shù)因此大大降低了評估基數(shù)?？梢酝ㄟ^收集列組統(tǒng)計信息來讓優(yōu)化器知曉列country_id與cust_state_province之間的真實關(guān)系。
自動與手動列組統(tǒng)計信息
Oracle數(shù)據(jù)庫可以自動或手動創(chuàng)建列組統(tǒng)計信息。優(yōu)化器可以使用SQL執(zhí)行計劃指令來生成更優(yōu)的執(zhí)行計劃。如果dbms_stats引用參數(shù)auto_stat_extensions被設(shè)置為ON(缺省值為OFF)，那么SQL執(zhí)行計劃指令基于工作量中謂詞的使用情況可以自動觸發(fā)來創(chuàng)建列組統(tǒng)計信息。可以通過set_table_prefs,set_global_prefs或set_schema_prefs過程來設(shè)置auto_stat_extensions。
當想要手動管理列組統(tǒng)計信息時，可以使用dbms_stats來執(zhí)行以下操作:
.探測列組
.創(chuàng)建以前探測到的列組
.手動創(chuàng)建列組并收集列組統(tǒng)計信息
列組統(tǒng)計信息用戶接口
有幾個dbms_stats程序單元有與列組相關(guān)的引用參數(shù)
seed_col_usage過程，迭代指定工作量中的SQL語句，編譯它們，然后查看在這些語句謂詞中出現(xiàn)列的使用信息。為了決定合適的列組，數(shù)據(jù)庫必須觀察一個有代表性的工作量。在監(jiān)控期間不需要運行查詢本身?？梢詫υ诠ぷ髁恐心切┻\行時間長的查詢執(zhí)行explain plan來確保數(shù)據(jù)庫記錄這些查詢所使用的列組信息。
report_col_usage函數(shù)，生成一個報告列出在工作量中所看到的過濾謂詞，連接謂詞與group by子句中的列?？梢允褂眠@個函數(shù)來檢查對于指定表所記錄的列使用信息。
create_extended_stats函數(shù)，創(chuàng)建擴展，它可以是列組或表達式。當用戶手動或自動統(tǒng)計信息收集任務(wù)對表收集統(tǒng)計信息時數(shù)據(jù)庫會對擴展收集統(tǒng)計信息。
auto_stat_extensions引用參數(shù)，控制自動創(chuàng)建擴展，包括列組，當優(yōu)化器統(tǒng)計信息被收集時，使用set_table_prefs,set_schema_prefs或set_global_prefs來設(shè)置這個引用參數(shù)。當auto_stat_extensions被設(shè)置為off(缺省值)時，數(shù)據(jù)庫不會自動創(chuàng)建列組統(tǒng)計信息。為了創(chuàng)建擴展，你必須執(zhí)行create_extended_stats函數(shù)或在dbms_stats API中的method_opt參數(shù)中顯性指定擴展統(tǒng)計信息。當auto_stat_extensions設(shè)置為ON時，一個SQL執(zhí)行計劃指令基于工作量中謂詞中列的使用信息可以觸發(fā)自動創(chuàng)建列組統(tǒng)計信息。
為特定的工作量檢測有用的列組
可以使用dbms_stats.seed_col_usage與report_col_usage來基于特定工作量來決定那個表需要列組。當你不知道需要創(chuàng)建什么樣的擴展統(tǒng)計信息時這種技術(shù)很有用。這種技術(shù)對于擴展統(tǒng)計信息不會工作。
假設(shè)存在以下情況:
.查詢sh.customers_test表(用customers表來創(chuàng)建)并在謂詞中使用了country_id與cust_state_province列但基數(shù)評估不正確。
.想要數(shù)據(jù)庫監(jiān)控工作量5分鐘(300秒)。
.想要數(shù)據(jù)庫自動判斷需要那些列組。
為了檢測列組需要執(zhí)行以下操作:
1.啟動SQL*Plus或SQL Developer，并以用戶sh登錄數(shù)據(jù)庫
2.創(chuàng)建表customers_test并收集統(tǒng)計信息:
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.啟用工作量監(jiān)控
在不同的SQL*Plus會話中，以sys用戶登錄并執(zhí)行以下的PL/SQL程序來啟用監(jiān)控300秒:
SQL> BEGIN 2 DBMS_STATS.SEED_COL_USAGE(null,null,300); 3 END; 4 / PL/SQL procedure successfully completed.
4.以用戶sh來在使用工作量的情況下對兩個查詢解析它們的執(zhí)行計劃。
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.第一個執(zhí)行計劃顯示基數(shù)為1而查詢返回932行記錄，第二個執(zhí)行計劃顯示基數(shù)為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 ###############################################################################
在上面的報告中，前三個列是第一個監(jiān)控查詢中等值謂詞中所使用的三個列：
... WHERE cust_city = 'Los Angeles' AND cust_state_province = 'CA' AND country_id = 52790;
所有三個列出現(xiàn)在相同的where子句中，因此報告顯示他們作為一組。在第二個查詢中，兩個列出現(xiàn)在group by子句中，因此報告標記它們作為group_by。在filter與group_by中的列組就是列組的候選者。
在工作量監(jiān)控下創(chuàng)建所檢測到的列組
可以使用dbms_stats.create_extended_stats函數(shù)來為執(zhí)行dbms_stats.seed_col_usage所檢測到的列組來創(chuàng)建列組，具體操作如下:
1.基于在監(jiān)控窗口期間所捕獲到的列使用信息來為customers_test表創(chuàng)建列組，執(zhí)行下面的查詢
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 ###############################################################################
數(shù)據(jù)庫將為customers_test表創(chuàng)建兩個列組:一個列組是過濾謂詞，一個列組是group by操作。
2.重新收集表統(tǒng)計信息
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test'); PL/SQL procedure successfully completed.
3.以用戶sh來查詢user_tab_col_statistics視圖來判斷數(shù)據(jù)庫創(chuàng)建了那些額外統(tǒng)計信息:
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函數(shù)所返回的兩個列組名。為CUST_CITY, CUST_STATE_PROVINCE和COUNTRY_ID列所創(chuàng)建的列組有一個HYBRID類型的直方圖統(tǒng)計信息。
4.再次解析之前的兩個查詢語句的執(zhí)行計劃
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.第一個查詢評估的基數(shù)是874，要返回的記錄數(shù)是932，第二個查詢評估的基數(shù)是145，要返回的記錄數(shù)是145,這樣基數(shù)評估的記錄數(shù)與實際返回的記錄已經(jīng)非常接近了，這就是列組統(tǒng)計信息所帶來的好處。
手動創(chuàng)建與收集列組統(tǒng)計信息
在有些情況下，可能知道想要創(chuàng)建的列組。dbms_stats.gather_table_stats函數(shù)的method_opt參數(shù)可以自動創(chuàng)建與收集列組統(tǒng)計信息。可以通過使用for columns來指定列組從而來創(chuàng)建一個新的列組。
假設(shè)存在以下情況:
.想要對sh.customers表上的cust_state_province與country_id列創(chuàng)建列組。
.想要對sh.customers表與新的列組收集統(tǒng)計信息。
手動創(chuàng)建與收集列組統(tǒng)計信息執(zhí)行以下操作:
1.啟動SQL*Plus并以sh用戶登錄數(shù)據(jù)庫。
2.使用以下PL/SQL程序來創(chuàng)建列組并收集統(tǒng)計信息:
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函數(shù)或數(shù)據(jù)庫視圖。也可以使用視圖來獲得信息比如，distinct值的數(shù)量與列組是否有直方圖統(tǒng)計信息。
1.啟動SQL*Plus并以sh用戶登錄數(shù)據(jù)庫。
2.為了獲得列組名，執(zhí)行以下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.查詢創(chuàng)建的列組的distinct值的數(shù)量并查看是否創(chuàng)建了直方圖
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函數(shù)來從表中刪除列組
SQL> BEGIN 2 DBMS_STATS.DROP_EXTENDED_STATS( 'sh', 'customers', 3 '(cust_state_province, country_id)' ); 4 END; 5 / PL/SQL procedure successfully completed.
到此，關(guān)于“Oracle 12C中的Statistics on Column Groups分析”的學習就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續(xù)學習更多相關(guān)知識，請繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
文章題目：Oracle12C中的StatisticsonColumnGroups分析
當前地址：http://weahome.cn/article/gjceis.html
