Oracle 12c新特性之檢測有用的多列統計資訊

Hoegh發表於2016-09-07
Oracle
    之前和大家分享過Oracle 11g下的一個新特性——收集多列統計資訊(http://blog.itpub.net/30162081/viewspace-1637387/)，今天和大家分享Oracle 12c的一個新特性——自動檢測有用列組資訊。二者相得益彰，大家可以具體情況酌情使用。
   言歸正傳，我們可以針對一個表，基於特定的工作負荷，透過使用DBMS_STATS.SEED_COL_USAGE和REPORT_COL_USAGE來確定我們需要哪些列組。當你不清除需要建立哪個擴充套件統計資訊時，這個技術是非常有用的。需要注意的是，這種技術不適用於包含表示式列的統計工作。
   接下來，我們透過例子來學習這個的新特性。

1.環境準備

首先，我們建立測試表customers_test，基於sh示例使用者下的customers表。

點選(此處)摺疊或開啟

  1. SQL> select banner from v$version;

  3. BANNER
  4. --------------------------------------------------------------------------------
  5. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  6. PL/SQL Release 12.1.0.2.0 - Production
  7. CORE    12.1.0.2.0    Production
  8. TNS for Linux: Version 12.1.0.2.0 - Production
  9. NLSRTL Version 12.1.0.2.0 - Production

  11. SQL>
  12. SQL> conn sh/sh@HOEGH
  13. Connected.
  14. SQL>
  15. SQL> DROP TABLE customers_test;
  16. DROP TABLE customers_test
  17.            *
  18. ERROR at line 1:
  19. ORA-00942: table or view does not exist


  22. SQL> CREATE TABLE customers_test AS SELECT * FROM customers;

  24. Table created.

  26. SQL> select count(*) from customers_test;

  28.   COUNT(*)
  29. ----------
  30.      55500

  32. SQL>

2.收集統計資訊

點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');

  4. PL/SQL procedure successfully completed.

  6. SQL>

3.開啟負載監控

另外開啟一個會話，透過sys使用者登入，開啟負載監控。其中，SEED_COL_USAGE的第三個參數列示監控的時間，單位是秒，300表示5分鐘。

點選(此處)摺疊或開啟

  1. SQL> show user
  2. USER is “SYS”
  3. SQL> BEGIN
  4.   DBMS_STATS.SEED_COL_USAGE(null,null,300);
  5. END;
  6. / 2 3 4

  8. PL/SQL procedure successfully completed.
  9. SQL>

4.使用explain plan for查詢執行計劃

點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> EXPLAIN PLAN FOR
  3.   SELECT *
  4.   FROM customers_test
  5.   WHERE cust_city = 'Los Angeles'
  6.   AND cust_state_province = 'CA'
  7.   AND country_id = 52790; 2 3 4 5 6

  9. Explained.

  11. SQL>
  12. SQL> SELECT PLAN_TABLE_OUTPUT
  13. FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows')); 2

  15. PLAN_TABLE_OUTPUT
  16. --------------------------------------------------------------------------------
  17. Plan hash value: 2112738156

  19. ----------------------------------------------------
  20. | Id | Operation     | Name     | Rows |
  21. ----------------------------------------------------
  22. | 0 | SELECT STATEMENT |         |     1 |
  23. | 1 | TABLE ACCESS FULL| CUSTOMERS_TEST |     1 |
  24. ----------------------------------------------------

  26. 8 rows selected.

  28. SQL>
從執行計劃來看，查詢結果只有1列。我們暫且記下這個結果。

5.檢視列使用資訊

此時，我們可以透過REPORT_COL_USAGE來檢視列的使用資訊。
我們看到，Oracle幫我們檢測到了一個有用的列組資訊，包括customers_test、cust_city和cust_state_province三列。

點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> SET LONG 100000
  3. SQL> SET LINES 120
  4. SQL> SET PAGES 0
  5. SQL> SELECT DBMS_STATS.REPORT_COL_USAGE(user, 'customers_test')
  6.   2 FROM DUAL;
  7. LEGEND:
  8. .......

  10. EQ     : Used in single table EQuality predicate
  11. RANGE     : Used in single table RANGE predicate
  12. LIKE     : Used in single table LIKE predicate
  13. NULL     : Used in single table is (not) NULL predicate
  14. EQ_JOIN : Used in EQuality JOIN predicate
  15. NONEQ_JOIN : Used in NON EQuality JOIN predicate
  16. FILTER     : Used in single table FILTER predicate
  17. JOIN     : Used in JOIN predicate
  18. GROUP_BY : Used in GROUP BY expression
  19. ...............................................................................

  21. ###############################################################################

  23. COLUMN USAGE REPORT FOR SH.CUSTOMERS_TEST
  24. .........................................

  26. 1. COUNTRY_ID             : EQ
  27. 2. CUST_CITY             : EQ
  28. 3. CUST_STATE_PROVINCE         : EQ
  29. 4. (CUST_CITY, CUST_STATE_PROVINCE,
  30.     COUNTRY_ID)          : FILTER
  31. ###############################################################################



  35. SQL>

6.建立擴充套件統計資訊

檢測工作完成後，我們可以透過CREATE_EXTENDED_STATS方法來建立擴充套件統計資訊。其中，黃色標註部分就是建立物件的名稱。

點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, 'customers_test') FROM DUAL;
  3. ###############################################################################

  5. EXTENSIONS FOR SH.CUSTOMERS_TEST
  6. ................................

  8. 1. (CUST_CITY, CUST_STATE_PROVINCE,
  9.     COUNTRY_ID)          : SYS_STUMZ$C3AIHLPBROI#SKA58H_N created
  10. ###############################################################################



  14. SQL>

7.重新收集統計資訊

點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');

  4. PL/SQL procedure successfully completed.

  6. SQL>

8.檢視USER_TAB_COL_STATISTICS，確認列統計資訊

透過查詢USER_TAB_COL_STATISTICS，我們可以獲取到剛剛建立的列組物件，和第6步的輸出結果是一致的。

點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> COL COLUMN_NAME FOR A30
  3. SQL> SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
  4. FROM USER_TAB_COL_STATISTICS
  5. WHERE TABLE_NAME = 'CUSTOMERS_TEST'
  6. ORDER BY 1; 2 3 4
  7. COUNTRY_ID                 19 FREQUENCY
  8. CUST_CITY                620 HYBRID
  9. CUST_CITY_ID                620 NONE
  10. CUST_CREDIT_LIMIT             8 NONE
  11. CUST_EFF_FROM                 1 NONE
  12. CUST_EFF_TO                 0 NONE
  13. CUST_EMAIL             1699 NONE
  14. CUST_FIRST_NAME          1300 NONE
  15. CUST_GENDER                 2 NONE
  16. CUST_ID              55500 NONE
  17. CUST_INCOME_LEVEL             12 NONE
  18. CUST_LAST_NAME                908 NONE
  19. CUST_MAIN_PHONE_NUMBER         51344 NONE
  20. CUST_MARITAL_STATUS             11 NONE
  21. CUST_POSTAL_CODE            623 NONE
  22. CUST_SRC_ID                 0 NONE
  23. CUST_STATE_PROVINCE            145 FREQUENCY
  24. CUST_STATE_PROVINCE_ID            145 NONE
  25. CUST_STREET_ADDRESS         49900 NONE
  26. CUST_TOTAL                 1 NONE
  27. CUST_TOTAL_ID                 1 NONE
  28. CUST_VALID                 2 NONE
  29. CUST_YEAR_OF_BIRTH             75 NONE
  30. SYS_STUMZ$C3AIHLPBROI#SKA58H_N        620 HYBRID

  32. 24 rows selected.

  34. SQL>

9.重新查詢執行計劃

我們看到，在第4步中查詢執行計劃中，Rows為1；現在呢，是867。這差距也忒大了點兒。

點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> EXPLAIN PLAN FOR
  3.   SELECT *
  4.   FROM customers_test
  5.   WHERE cust_city = 'Los Angeles'
  6.   AND cust_state_province = 'CA'
  7.   AND country_id = 52790; 2 3 4 5 6

  9. Explained.

  11. SQL>
  12. SQL> SELECT PLAN_TABLE_OUTPUT
  13. FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows')); 2
  14. Plan hash value: 2112738156

  16. ----------------------------------------------------
  17. | Id | Operation     | Name     | Rows |
  18. ----------------------------------------------------
  19. | 0 | SELECT STATEMENT |         | 867 |
  20. | 1 | TABLE ACCESS FULL| CUSTOMERS_TEST | 867 |
  21. ----------------------------------------------------

  23. 8 rows selected.

  25. SQL>

~~~~~~~ the end~~~~~~~~~
hoegh
2016.09.07

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30162081/viewspace-2124652/，如需轉載，請註明出處，否則將追究法律責任。

相關文章