前言
之前和大家分享過Oracle 11g下的一個(gè)新特性——收集多列統(tǒng)計(jì)信息(https://www.jb51.net/article/109514.htm)，今天和大家分享Oracle 12c的一個(gè)新特性——自動(dòng)檢測(cè)有用列組信息。二者相得益彰，大家可以具體情況酌情使用。
言歸正傳，我們可以針對(duì)一個(gè)表，基于特定的工作負(fù)荷，通過使用DBMS_STATS.SEED_COL_USAGE和REPORT_COL_USAGE來確定我們需要哪些列組。當(dāng)你不清除需要?jiǎng)?chuàng)建哪個(gè)擴(kuò)展統(tǒng)計(jì)信息時(shí)，這個(gè)技術(shù)是非常有用的。需要注意的是，這種技術(shù)不適用于包含表達(dá)式列的統(tǒng)計(jì)工作。
接下來，我們通過例子來學(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 Production PL/SQL Release 12.1.0.2.0 - Production CORE 12.1.0.2.0 Production TNS for Linux: Version 12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production SQL> SQL> conn sh/sh@HOEGH Connected. SQL> SQL> DROP TABLE customers_test; DROP TABLE customers_test * ERROR at line 1: ORA-00942: table or view does not exist SQL> CREATE TABLE customers_test AS SELECT * FROM customers; Table created. SQL> select count(*) from customers_test; COUNT(*) ---------- 55500 SQL>
二、收集統(tǒng)計(jì)信息
SQL> SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test'); PL/SQL procedure successfully completed. SQL>
三、開啟負(fù)載監(jiān)控
另外打開一個(gè)會(huì)話，通過sys用戶登錄，開啟負(fù)載監(jiān)控。其中，SEED_COL_USAGE的第三個(gè)參數(shù)表示監(jiān)控的時(shí)間，單位是秒，300表示5分鐘。
SQL> show user USER is “SYS” SQL> BEGIN DBMS_STATS.SEED_COL_USAGE(null,null,300); END; / 2 3 4 PL/SQL procedure successfully completed. SQL>
四、使用explain plan for查詢執(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ì)劃來看，查詢結(jié)果只有1列。我們暫且記下這個(gè)結(jié)果。
五、查看列使用信息
此時(shí)，我們可以通過REPORT_COL_USAGE來查看列的使用信息。
我們看到，Oracle幫我們檢測(cè)到了一個(gè)有用的列組信息，包括customers_test、cust_city和cust_state_province三列。
SQL> 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 ############################################################################### SQL>
六、創(chuàng)建擴(kuò)展統(tǒng)計(jì)信息
檢測(cè)工作完成后，我們可以通過CREATE_EXTENDED_STATS方法來創(chuàng)建擴(kuò)展統(tǒng)計(jì)信息。其中，黃色標(biāo)注部分就是創(chuàng)建對(duì)象的名稱。
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ì)信息
通過查詢USER_TAB_COL_STATISTICS，我們可以獲取到剛剛創(chuàng)建的列組對(duì)象，和第6步的輸出結(jié)果是一致的。
SQL> SQL> COL COLUMN_NAME FOR A30 SQL> SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = 'CUSTOMERS_TEST' ORDER BY 1; 2 3 4 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_STUMZ$C3AIHLPBROI#SKA58H_N 620 HYBRID 24 rows selected. SQL>
九、重新查詢執(zhí)行計(jì)劃
我們看到，在第4步中查詢執(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í)或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，謝謝大家對(duì)腳本之家的支持。
標(biāo)簽：遼源 紹興 百色 中衛(wèi) 寧波 自貢 昭通 海東
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Oracle 12c新特性之如何檢測(cè)有用的多列統(tǒng)計(jì)信息詳解》，本文關(guān)鍵詞 Oracle,12c,新特性,新,特性,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
