Oracle 12c新特性之怎么檢測(cè)有用的多列統(tǒng)計(jì)信息

134次閱讀

共計(jì) 5056 個(gè)字符，預(yù)計(jì)需要花費(fèi) 13 分鐘才能閱讀完成。

自動(dòng)寫(xiě)代碼機(jī)器人，免費(fèi)開(kāi)通

這篇文章給大家分享的是有關(guān) Oracle 12c 新特性之怎么檢測(cè)有用的多列統(tǒng)計(jì)信息的內(nèi)容。丸趣 TV 小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨丸趣 TV 小編過(guò)來(lái)看看吧。

一、環(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

三、開(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 user
USER is “SYS”SQL  BEGIN
 DBMS_STATS.SEED_COL_USAGE(null,null,300);
/ 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ì)劃來(lái)看，查詢結(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 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è)工作完成后，我們可以通過(guò) CREATE_EXTENDED_STATS 方法來(lái)創(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ì)信息

通過(guò)查詢 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

感謝各位的閱讀！關(guān)于“Oracle 12c 新特性之怎么檢測(cè)有用的多列統(tǒng)計(jì)信息”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！

向 AI 問(wèn)一下細(xì)節(jié)

正文完