前言
之前和大家分享過Oracle 11g下的一個新特性——收集多列統計信息，今天和大家分享Oracle 12c的一個新特性——自動檢測有用列組信息。二者相得益彰，大家可以具體情況酌情使用。
言歸正傳，我們可以針對一個表，基于特定的工作負荷，通過使用DBMS_STATS.SEED_COL_USAGE和REPORT_COL_USAGE來確定我們需要哪些列組。當你不清除需要創建哪個擴展統計信息時，這個技術是非常有用的。需要注意的是，這種技術不適用于包含表達式列的統計工作。
接下來，我們通過例子來學習這個的新特性。
一、環境準備
首先，我們創建測試表customers_test，基于sh示例用戶下的customers表。
SQL> select banner from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionPL/SQL Release 12.1.0.2.0 - ProductionCORE 12.1.0.2.0 ProductionTNS for Linux: Version 12.1.0.2.0 - ProductionNLSRTL Version 12.1.0.2.0 - ProductionSQL> SQL> conn sh/sh@HOEGHConnected.SQL> SQL> DROP TABLE customers_test;DROP TABLE customers_test *ERROR at line 1:ORA-00942: table or view does not existSQL> CREATE TABLE customers_test AS SELECT * FROM customers;Table created.SQL> select count(*) from customers_test; COUNT(*)---------- 55500SQL>
二、收集統計信息
SQL> SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');PL/SQL procedure successfully completed.SQL>
三、開啟負載監控
另外打開一個會話，通過sys用戶登錄，開啟負載監控。其中，SEED_COL_USAGE的第三個參數表示監控的時間，單位是秒，300表示5分鐘。
SQL> show userUSER is “SYS”SQL> BEGIN DBMS_STATS.SEED_COL_USAGE(null,null,300);END;/ 2 3 4PL/SQL procedure successfully completed.SQL>
四、使用explain plan for查詢執行計劃
SQL> SQL> EXPLAIN PLAN FOR SELECT * FROM customers_test WHERE cust_city = 'Los Angeles' AND cust_state_province = 'CA' AND country_id = 52790; 2 3 4 5 6 Explained.SQL> SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows')); 2 PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2112738156----------------------------------------------------| Id | Operation | Name | Rows |----------------------------------------------------| 0 | SELECT STATEMENT | | 1 || 1 | TABLE ACCESS FULL| CUSTOMERS_TEST | 1 |----------------------------------------------------8 rows selected.SQL>從執行計劃來看，查詢結果只有1列。我們暫且記下這個結果。
五、查看列使用信息
此時，我們可以通過REPORT_COL_USAGE來查看列的使用信息。
我們看到，Oracle幫我們檢測到了一個有用的列組信息，包括customers_test、cust_city和cust_state_province三列。
SQL> SQL> SET LONG 100000SQL> SET LINES 120SQL> SET PAGES 0SQL> SELECT DBMS_STATS.REPORT_COL_USAGE(user, 'customers_test') 2 FROM DUAL;LEGEND:.......EQ : Used in single table EQuality predicateRANGE : Used in single table RANGE predicateLIKE : Used in single table LIKE predicateNULL : Used in single table is (not) NULL predicateEQ_JOIN : Used in EQuality JOIN predicateNONEQ_JOIN : Used in NON EQuality JOIN predicateFILTER : Used in single table FILTER predicateJOIN : Used in JOIN predicateGROUP_BY : Used in GROUP BY expression...............................................................................###############################################################################COLUMN USAGE REPORT FOR SH.CUSTOMERS_TEST.........................................1. COUNTRY_ID : EQ2. CUST_CITY : EQ3. CUST_STATE_PROVINCE : EQ4. (CUST_CITY, CUST_STATE_PROVINCE, COUNTRY_ID) : FILTER###############################################################################SQL>
六、創建擴展統計信息
檢測工作完成后，我們可以通過CREATE_EXTENDED_STATS方法來創建擴展統計信息。其中，黃色標注部分就是創建對象的名稱。
SQL> 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###############################################################################SQL>
七、重新收集統計信息
SQL> SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');PL/SQL procedure successfully completed.SQL>
八、查看USER_TAB_COL_STATISTICS，確認列統計信息
通過查詢USER_TAB_COL_STATISTICS，我們可以獲取到剛剛創建的列組對象，和第6步的輸出結果是一致的。
SQL> SQL> COL COLUMN_NAME FOR A30SQL> SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAMFROM USER_TAB_COL_STATISTICSWHERE TABLE_NAME = 'CUSTOMERS_TEST'ORDER BY 1; 2 3 4 COUNTRY_ID 19 FREQUENCYCUST_CITY 620 HYBRIDCUST_CITY_ID 620 NONECUST_CREDIT_LIMIT 8 NONECUST_EFF_FROM 1 NONECUST_EFF_TO 0 NONECUST_EMAIL 1699 NONECUST_FIRST_NAME 1300 NONECUST_GENDER 2 NONECUST_ID 55500 NONECUST_INCOME_LEVEL 12 NONECUST_LAST_NAME 908 NONECUST_MAIN_PHONE_NUMBER 51344 NONECUST_MARITAL_STATUS 11 NONECUST_POSTAL_CODE 623 NONECUST_SRC_ID 0 NONECUST_STATE_PROVINCE 145 FREQUENCYCUST_STATE_PROVINCE_ID 145 NONECUST_STREET_ADDRESS 49900 NONECUST_TOTAL 1 NONECUST_TOTAL_ID 1 NONECUST_VALID 2 NONECUST_YEAR_OF_BIRTH 75 NONESYS_STUMZ$C3AIHLPBROI#SKA58H_N 620 HYBRID24 rows selected.SQL>
九、重新查詢執行計劃
我們看到，在第4步中查詢執行計劃中，Rows為1；現在呢，是867。這差距也忒大了點兒。
SQL> SQL> EXPLAIN PLAN FOR SELECT * FROM customers_test WHERE cust_city = 'Los Angeles' AND cust_state_province = 'CA' AND country_id = 52790; 2 3 4 5 6 Explained.SQL> SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows')); 2 Plan hash value: 2112738156----------------------------------------------------| Id | Operation | Name | Rows |----------------------------------------------------| 0 | SELECT STATEMENT | | 867 || 1 | TABLE ACCESS FULL| CUSTOMERS_TEST | 867 |----------------------------------------------------8 rows selected.SQL>總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，謝謝大家對VeVb武林網的支持。
新聞熱點
疑難解答
