前言
之前和大家分享過(guò)Oracle 11g下的一個(gè)新特性——收集多列統(tǒng)計(jì)信息，今天和大家分享Oracle 12c的一個(gè)新特性——自動(dòng)檢測(cè)有用列組信息。二者相得益彰，大家可以具體情況酌情使用。
言歸正傳，我們可以針對(duì)一個(gè)表，基于特定的工作負(fù)荷，通過(guò)使用DBMS_STATS.SEED_COL_USAGE和REPORT_COL_USAGE來(lái)確定我們需要哪些列組。當(dāng)你不清除需要?jiǎng)?chuàng)建哪個(gè)擴(kuò)展統(tǒng)計(jì)信息時(shí)，這個(gè)技術(shù)是非常有用的。需要注意的是，這種技術(shù)不適用于包含表達(dá)式列的統(tǒng)計(jì)工作。
接下來(lái)，我們通過(guò)例子來(lái)學(xué)習(xí)這個(gè)的新特性。
一、環(huán)境準(zhǔn)備
首先，我們創(chuàng)建測(cè)試表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>
二、收集統(tǒng)計(jì)信息
SQL> SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');PL/SQL procedure successfully completed.SQL>
三、開(kāi)啟負(fù)載監(jiān)控
另外打開(kāi)一個(gè)會(huì)話，通過(guò)sys用戶登錄，開(kāi)啟負(fù)載監(jiān)控。其中，SEED_COL_USAGE的第三個(gè)參數(shù)表示監(jiān)控的時(shí)間，單位是秒，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查詢(xún)執(zhí)行計(jì)劃
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>從執(zhí)行計(jì)劃來(lái)看，查詢(xún)結(jié)果只有1列。我們暫且記下這個(gè)結(jié)果。
五、查看列使用信息
此時(shí)，我們可以通過(guò)REPORT_COL_USAGE來(lái)查看列的使用信息。
我們看到，Oracle幫我們檢測(cè)到了一個(gè)有用的列組信息，包括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>
六、創(chuàng)建擴(kuò)展統(tǒng)計(jì)信息
檢測(cè)工作完成后，我們可以通過(guò)CREATE_EXTENDED_STATS方法來(lái)創(chuàng)建擴(kuò)展統(tǒng)計(jì)信息。其中，黃色標(biāo)注部分就是創(chuàng)建對(duì)象的名稱(chēng)。
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>
七、重新收集統(tǒng)計(jì)信息
SQL> SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');PL/SQL procedure successfully completed.SQL>
八、查看USER_TAB_COL_STATISTICS，確認(rèn)列統(tǒng)計(jì)信息
通過(guò)查詢(xún)USER_TAB_COL_STATISTICS，我們可以獲取到剛剛創(chuàng)建的列組對(duì)象，和第6步的輸出結(jié)果是一致的。
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>
九、重新查詢(xún)執(zhí)行計(jì)劃
我們看到，在第4步中查詢(xún)執(zhí)行計(jì)劃中，Rows為1；現(xiàn)在呢，是867。這差距也忒大了點(diǎn)兒。
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>總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)VeVb武林網(wǎng)的支持。
新聞熱點(diǎn)
疑難解答
圖片精選
