• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    Oracle 12c新特性之如何检测有用的多列统计信息详解

    前言

    之前和大家分享过Oracle 11g下的一个新特性——收集多列统计信息(https://www.jb51.net/article/109514.htm),今天和大家分享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 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>

    二、收集统计信息

    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 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查询执行计划

    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 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>

    六、创建扩展统计信息

    检测工作完成后,我们可以通过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 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>

    九、重新查询执行计划

    我们看到,在第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>

    总结

    以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。

    您可能感兴趣的文章:
    • PDO取Oracle lob大字段,当数据量太大无法取出的问题的解决办法
    • Oracle 11g收集多列统计信息详解
    • Oracle Translate 统计字符出现的次数示例代码
    • Oracle数据库按时间进行分组统计数据的方法
    • oracle表空间中空表统计方法示例介绍
    • oracle数据库下统计专营店的男女数量的语句
    • Oracle 统计用户下表的数据量实现脚本
    上一篇:Oracle 11g收集多列统计信息详解
    下一篇:Oracle计算时间差为毫秒的实现代码
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    Oracle 12c新特性之如何检测有用的多列统计信息详解 Oracle,12c,新特性,新,特性,