sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】oracle multi column statistics多列统计信息使用案例

时间:2017-02-07 14:01   来源:Oracle研究中心   作者:网络   点击:

oracle研究中心学习笔记:分享一篇关于oracle 11g新特性:oracle multi column statistics多列统计信息使用案例。当where子句后指定了一个表的多个列条件时,优化器通常会将多个列的选择性(selectivity)相乘得到where语句的选择性,导致优化器做出错误判断!
Oracle 11g引入了多列统计信息概念,如果上面情况列关联性很好,我们可以做多列统计信息收集,让优化器做出正确判断。

例如:
SQL> select count(*) from sh.customers where CUST_STATE_PROVINCE = 'CA';

  COUNT(*)
----------
      3341

SQL> select count(*) from sh.customers where CUST_STATE_PROVINCE = 'CA' and country_id=52790;

  COUNT(*)
----------
      3341

SQL> select count(*) from sh.customers where CUST_STATE_PROVINCE = 'CA' and country_id=52775;

  COUNT(*)
----------
          0
可以看出表customers的列CUST_STATE_PROVINCE、country_id是有关系的,只有多列统计信息才能让优化器知道他们的关系,从而有一个更精确的选择性(selectiveity)。


1、创建Column Group:
DECLARE
  cg_name varchar2(30);
BEGIN
  cg_name := dbms_stats.create_extended_stats(null,'customers', '(cust_state_province,country_id)');
END;
/

2、查看column group name:
select sys.dbms_stats.show_extended_stats_name('sh','customers','(cust_state_province,country_id)') col_group_name from dual;

COL_GROUP_NAME
--------------------------------------------------------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_

3、删除Column Group
exec dbms_stats.drop_extended_stats('sh','customers','(cust_state_province,country_id)');

4、监控Column Groups
--查询多列统计信息
Select extension_name, extension from user_stat_extensions where table_name='CUSTOMERS';

EXTENSION_NAME                EXTENSION
------------------------------ ----------------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_   ("CUST_STATE_PROVINCE","COUNTRY_ID")

--查看distinct数和柱状图使用情况
select e.extension col_group, t.num_distinct, t.histogram
  from user_stat_extensions e, user_tab_col_statistics t
where e.extension_name = t.column_name
   and e.table_name = t.table_name
   and t.table_name = 'CUSTOMERS';
COL_GROUP                              NUM_DISTINCT  HISTOGRAM
---------------------------------------- ------------ ---------------
("CUST_STATE_PROVINCE","COUNTRY_ID")           145  FREQUENCY

5、收集多列统计信息
1)收集已存在的列组统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',METHOD_OPT =>'FOR ALL COLUMNS SIZE AUTO');

2)收集新指定的列组统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',METHOD_OPT =>'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (CUST_STATE_PROVINCE,COUNTRY_ID) SIZE SKEWONLY');

实验:
1)当不使用多列统计信息时,真实结果是3341,执行计划是1107.

SQL> exec dbms_stats.drop_extended_stats('sh','customers','(cust_state_province,country_id)');
PL/SQL procedure successfully completed.

SQL> select count(*) from sh.customers where CUST_STATE_PROVINCE = 'CA' and country_id=52790;

  COUNT(*)
----------
      3341

Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    16 |   406      (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |               |     1 |    16 |      |              |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |  1107 | 17712 |   406 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790)

Statistics
----------------------------------------------------------
         120  recursive calls
           0  db block gets
       1588  consistent gets
       1454  physical reads
           0  redo size
         423  bytes sent via SQL*Net to client
         420  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           7  sorts (memory)
           0  sorts (disk)
           1  rows processed

2)当使用多列统计信息时,真实结果是3341,执行计划是3294.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',METHOD_OPT =>'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (CUST_STATE_PROVINCE,COUNTRY_ID) SIZE SKEWONLY');

PL/SQL procedure successfully completed.

SQL> select count(*) from sh.customers where CUST_STATE_PROVINCE = 'CA' and country_id=52790;

  COUNT(*)
----------
      3341


Execution Plan
----------------------------------------------------------
Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    16 |   406      (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |               |     1 |    16 |      |              |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |  3294 | 52704 |   406        (1)| 00:00:05 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790)

Statistics
----------------------------------------------------------
           8  recursive calls
           0  db block gets
       1458  consistent gets
       1454  physical reads
           0  redo size
         423  bytes sent via SQL*Net to client
         420  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
           1  rows processed
3)即以上情况,使用多列统计信息能让优化器得到更准确的判断!
 

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】oracle multi column statistics多列统计信息使用案例

原文唯一网址:http://www.oracleplus.net/arch/1463.html

Oracle研究中心

关键词:

Oracle 11G新特性

oracle multi column statistics

oracle多列统计信息详细教程