分类目录归档:Tuning

12.2统计信息之管理统计信息

除了收集合适的统计信息,提供一个管理这些统计信息的综合架构同样重要。oracle提供了多个方法去做这些工作,包括恢复统计信息到以前的版本,提供从一个系统迁移统计信息到另外一个的选项,甚至你可以手动设置统计信息。这些选项在某些特定情形下都非常有用,但是仍然建议都通过DBMS_STATS包来收集统计信息。

恢复统计信息

当你用DBMS_STATS包来收集统计信息时,原来的统计信息会自动备份存储在字典表里,如果新生成的统计信息有问题则能很容易的通过DBMS_STATS.RESTORE_TABLE_STATS恢复之前的统计信息。视图DBA_TAB_STATS_HISTORY包含多个时间点保存的统计信息。

下面这个例子恢复了表t的统计信息至昨天,并自动使shared pool里所有涉及到T表的游标都失效。因为恢复了昨天的统计信息,所以想立刻想使用昨天的统计信息来影响新的游标。参数NO_INVALIDATE的值决定了表T相关的游标是否要失效。

BEGIN 
    DBMS_STATS.RESTORE_TABLE_STATS(ownname => 'SYS', 
                                   tabname => 'T', 
                                   as_of_timestamp => SYSTIMESTAMP-1,
                                   force => FALSE, 
                                   no_invalidate => FALSE); 
END; 
/

挂起统计信息

默认情况下,当统计信息收集以后,它们将会立刻被写入到数据字典表然后立刻被优化器所使用。从11g开始,oracle支持收集完统计信息以后不立即写入字典表,而是先存储在挂起表中,这样就可以在正式使用之前先进行测试。这些挂起统计信息可以对单个会话进行启用,以一种可控的方式让你在应用这些统计信息之前先进行验证。为了激活挂起统计信息的收集,需要使用DBMS_STATS.SET_*_PREFS其中的过程来改变PUBLISH参数的值从TRUE(默认值)变成FALSE

exec DBMS_STATS.SET_TABLE_PREFS('SYS', 'T', 'PUBLISH', 'FALSE');

跟之前一样正常收集统计信息

EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T');

这时收集的统计信息都会储存在USER_*_PENDING_STATS数据字典中,可以指定优化器去使用这些挂起统计信息,通过ALTER SESSION命令去设置初始化参数OPTIMIZER_USE_PENDING_STATSTRUE然后运行sql。对于sql中那些没有挂起统计信息的表来说,优化器会自动选择它们当前存储在字典表的信息。当你启用了这些挂起统计信息,也可以通过DBMS_STATS.PUBLISH_PENDING_STATS去发布它们,将它们写入正式的数据字典当中。

sys@ORA12C> INSERT INTO T select * from t;                       <<<<==== 手动插入一些数据

55 rows created.

sys@ORA12C> commit;

Commit complete.

sys@ORA12C> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T');

PL/SQL procedure successfully completed.

sys@ORA12C> select table_name,NUM_ROWS ,BLOCKS ,LAST_ANALYZED from user_tables  where table_name='T';

TABLE_NAME             NUM_ROWS     BLOCKS LAST_ANALYZED
-------------------- ---------- ---------- -------------------
T                            55          1 2018-12-13 16:58:16                      <<<<====旧的统计信息

sys@ORA12C> select table_name,NUM_ROWS ,BLOCKS ,LAST_ANALYZED from user_tab_pending_stats  where table_name='T';

TABLE_NAME             NUM_ROWS     BLOCKS LAST_ANALYZED
-------------------- ---------- ---------- -------------------
T                           110          1 2018-12-18 15:13:14                      <<<<===挂起统计信息里显示正确的110行

更新挂起统计信息至正式表

sys@ORA12C> Exec dbms_stats.publish_pending_stats('SYS', 'T');

PL/SQL procedure successfully completed.

sys@ORA12C> select table_name,NUM_ROWS ,BLOCKS ,LAST_ANALYZED from user_tables  where table_name='T';

TABLE_NAME             NUM_ROWS     BLOCKS LAST_ANALYZED
-------------------- ---------- ---------- -------------------
T                           110          1 2018-12-18 15:13:14              <<<<====数据正确

sys@ORA12C> select table_name,NUM_ROWS ,BLOCKS ,LAST_ANALYZED from user_tab_pending_stats  where table_name='T';

no rows selected

如果不想使用新的统计信息,可以直接删掉

Exec dbms_stats.delete_pending_stats('SYS', 'T');

导入导出统计信息

统计信息可以从一个库复制到另一个,比如在准生产环境通过从别的库复制过来的统计信息进行性能测试是很有用的。复制的时候是通过DBMS_STATS.EXPORT_*_STATSDBMS_STATS.IMPORT_*_STATS过程来实现。

在导出统计信息之前,你需要先通过DBMS_STATS.CREATE_STAT_TABLE创建一张表来储存这些信息。当表创建完毕以后,你就可以通过DBMS_STATS.EXPORT_*_STATS过程来将统计信息导出,当这些统计信息打包到表里以后,你就可以通过数据泵的方式将表的数据从生产环境导入到测试环境。当表导入测试数据库以后,就可以通过DBMS_STATS.IMPORT_*_STATS将统计信息导入字典当中。

exec DBMS_STATS.CREATE_STAT_TABLE('SYS','T','USERS');

exec dbms_stats.EXPORT_DATABASE_STATS('T','ST_TEST','SYS');

exec dbms_stats.IMPORT_DATABASE_STATS('T','ST_TEST','SYS');

复制分区统计信息

当处理分区表时,优化器需要同时依赖整表和单独分区的统计信息用于得到一个更优的执行计划。如果sql只需要访问单独的分区,优化器则只采用单独分区的统计信息。如果需要访问多个分区,则优化器会采用全局统计信息。

经常会有这样的场景,分区表增加分区,数据只会插入这个新分区。如果用户在这个分区收集统计信息之前去查询这些数据,那很可能就会得到一个比较差的执行计划。又一个很常见的场景就是传入谓词条件的值超过了列统计信息的最大值和最小值之间的范围,这就是被熟知的超出范围错误。在这种情况下,优化器根据谓词与最大值之间的距离来分配选择性(假设传参超过了最大值),谓词值与最大值或最小值越远,那么值的选择性就越低。

超出范围情况能通过DBMS_STATS.COPY_TABLE_STATS过程来避免,这个过程能将源分区的统计信息复制到新建的统计信息为空的分区中去。同时复制了依赖对象的统计信息:字段、本地索引等。分区字段的最小值和最大值按照如下进行调整:

  • 如果分区类型是HASH则目标分区的最大值和最小值和源分区一致
  • 如果分区类型是LIST并且目标分区不是一个默认分区,则目标分区的最小值设成用来定义分区的LIST值中的最小值,最大值就是LIST值中的最大值
  • 如果分区类型是LIST并且目标分区是一个默认分区,则目标分区的最小值设成源分区的最小值,目标分区的最大值设成源分区的最大值
  • 如果分区类型是RANGE,则目标分区的最小值设成前一个分区的上限值,目标分区的最大值设成定义目标分区的RANGE最大值,而如果RANGE最大值是MAXVALUE时,目标分区的最大值则设成前一个分区的上限值

可以根据给定的scale_factor参数来缩放统计信息(比如块的数量、行数等)。统计信息中像行的平均长度和唯一值数量并未进行调整,而是认为在目标分区中是相同的。

SALES_Q3_2011范围分区当中的统计信息复制到SALES_Q4_2011,设置缩放因子为2来缩放基础统计信息

EXEC DBMS_STATS.COPY_TABLE_STATS('SH','SALES','SALES_Q3_2002','SALES_Q4_2002', 2);

只有在索引分区名称与表分区名称一样时才会复制索引信息(默认值),全局统计信息默认情况下并不会更新。只有在全局统计信息不存在并且通过聚合生成了全局统计信息时,才能通过DBMS_STATS.COPY_TABLE_STATS过程影响全局统计信息。

统计信息比较

一个系统的执行计划于另一个系统不一样的一个重要原因就是优化器统计信息不一样。比如如果数据没有同步,测试环境的统计信息很可能就与生产环境的不一样。为了确定统计信息之间的差异,可以通过DBMS_STATS.DIFF_TABLE_STATS_*函数来比较两个源。A下面的表可以和B下面的表进行比较,同样可以对一个表的不同时间点的统计信息比较,或者当前统计信息与挂起统计信息比较。比如比较当前时间和昨天

sys@ORA12C> select report, maxdiffpct from dbms_stats.diff_table_stats_in_history(user, 'T', SYSDATE-1, SYSDATE, 2);

REPORT
---------------------------------------------------------------------------------------------
MAXDIFFPCT
----------
###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : T
OWNER         : SYS
SOURCE A      : Statistics as of 18-DEC-18 11.03.39.000000 AM +08:00
SOURCE B      : Statistics as of 19-DEC-18 11.03.39.000000 AM +08:00
PCTTHRESHOLD  : 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
...............................................................................

T                           T   A   55         1          3          55
                                B   110        1          3          110
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

ID              A   10      .009090909 YES  0       3    80    C10A  55
                B   10      .004545454 YES  0       3    80    C10A  110
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################

比较不同库的两张表统计信息其实跟导入导出统计信息类似,也是将其中一个系统的表统计信息导出表然后导入第二个系统进行比较
最后通过dbms_stats.diff_table_stats_in_stattab完成

select report from dbms_stat.diff_table_stats_in_stattab( 'SCOTT', 'EMP', 'STAT_TAB_OLD', 'STAT_TAB_NEW');

DIFF函数同时比较依赖对象(索引、分区、列)的统计信息,如果统计信息之间的差异超过了阀值则会列出源端目标端对象的所有的统计信息。这个阀值可以作为入参传入,默认值是10%。计算的时候以源端统计信息作为基数。

锁定统计信息

有些情况下,你希望通过锁定表或者方案的统计信息来避免重新收集时产生影响。当统计信息被锁定后,任何操作都无法修改这些信息除非你重新解锁或则收集统计信息时设置FORCE参数为TRUE

sys@ORA12C> exec DBMS_STATS.LOCK_TABLE_STATS('SYS', 'T');

PL/SQL procedure successfully completed.

sys@ORA12C> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T');
BEGIN DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 36873
ORA-06512: at "SYS.DBMS_STATS", line 36507
ORA-06512: at "SYS.DBMS_STATS", line 8582
ORA-06512: at "SYS.DBMS_STATS", line 9461
ORA-06512: at "SYS.DBMS_STATS", line 35836
ORA-06512: at "SYS.DBMS_STATS", line 36716
ORA-06512: at line 1


sys@ORA12C> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T',force=>TRUE);

PL/SQL procedure successfully completed.

也可以锁定分区级别的统计信息

EXEC DBMS_STATS.LOCK_PARTITION_STATS('SH', 'SALES', 'SALES_Q3_2000');

需要注意锁定统计信息的层次问题,比如如果锁定了一个分区表统计信息,然后为了单独搜集一个分区的统计信息而将这个分区统计信息进行解锁,则会报错ORA-20005。因为即使分区锁被解锁了,表级锁仍然存在。所以为了统计单独分区只能通过设置FORCE为TRUE。

手动设置统计信息

在罕见的情况下,手动设置数据字典里的统计信息可能会很有帮助。比如全局临时表的统计信息。可以手动通过DBMS_STATS.SET_*_STATS来收集。

12.2统计信息之分区表

分区表上的统计信息要同时计算表级别和分区级别,11g以前新增一个分区或者修改少数分区的数据需要扫描整张表去刷新表的统计信息。如果跳过收集全局统计信息,那么优化器则会根据分区级别的统计信息去推断全局级别的统计信息。这种方法对于简单的表统计信息(例如行数)是准确的——通过聚合所有分区的行数,但是其他的统计信息就无法准确的推断。比如无法从每个分区独立的信息来准确推断出一个字段的唯一值记录(优化器使用的最重要统计信息之一)。

oracle 11g开始加强了这部分的统计信息收集手段,引入了增量全局统计信息。如果分区表的INCREMENTAL选项设置为true,DBMS_STATS.GATHER_*_STATSGRANULARITY参数包含GLOBAL,并且ESTIMATE_PERCENT设置成AUTO_SAMPLE_SIZE,Oracle 会对新的分区收集信息,并且通过只扫描那些新增或进行修改了的分区从而准确的更新全局统计信息,这时就不需要扫描全表。

增量全局统计信息会对每个分区储存概要概要表示分区和分区字段的元数据。每个概要都是储存在SYSAUX表空间。然后通过聚合每个分区级别的统计信息和分区概要来收集全局统计信息,因此就不需要扫描整张表来收集表级别统计信息。当一个新分区添加到表中,你只需要收集单个分区的统计信息,全局统计信息会自动维护和使用新分区的概要来准确更新数据。

注意INCREMENTAL统计信息并不适用子分区,子分区和分区上的信息收集就跟正常的一样。只有分区上的统计信息用于更新全局或表级别的信息时,增量才有用。下面是如何使用增量统计信息

开启增量统计信息

EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'INCREMENTAL', 'TRUE');

跟平常一样收集,ESTIMATE_PERCENTGRANULARITY都是默认值

exec DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES');

检查当前INCREMENTAL

SELECT DBMS_STATS.GET_PREFS('INCREMENTAL', 'SH', 'SALES') FROM dual;

增量统计信息和过期

11g时,如果一个表上启用了增量统计信息,当一个分区的一行更新后,那么这个分区上的统计信息就被认为是过期的,如果要生成全局信息则必须要重新收集这个分区的统计信息。

12c有一个新的选项INCREMENTAL_STALENESS,能让你去控制什么时候分区统计信息被认为是过期的或者已经不适合用来生成全局统计信息了。默认这个值是NULL,表示分区有值变更时就立刻认为是过期(跟11g一样)。

它也可以设成USE_STALE_PERCENT或者USE_LOCKED_STATS,USE_STALE_PERCENT,表示变化的行数所占百分比小于设置的STALE_PRECENTAGE值时(默认10%),分区级别的统计信息一直被认为可用。USE_LOCKED_STATS表示如果一个分区的统计信息被锁定了,那么它就可以被用来生成全局统计信息而不用考虑这个分区从上次收集统计信息到现在有多少行记录做了变更。

给表ORDER_DEMO设置USE_STALE_PERCENT

BEGIN
    DBMS_STATS.SET_TABLE_PREFS (
        ownname  => 'OE', 
        tabname  => 'ORDERS_DEMO', 
        pname    => 'INCREMENTAL_STALENESS', 
        pvalue   => 'USE_STALE_PERCENT');
END;
/

修改默认的10%到20%

BEGIN
    DBMS_STATS.SET_TABLE_PREFS (
        ownname  => 'OE', 
        tabname  => 'ORDERS_DEMO', 
        pname    => 'STALE_PERCENT', 
        pvalue   => 20);
END;
/

分区表统计信息锁定的情况

给表ORDER_DEMO设置USE_LOCKED_STATS

BEGIN
    DBMS_STATS.SET_TABLE_PREFS (
        ownname  => 'OE', 
        tabname  => 'ORDERS_DEMO', 
        pname    => 'INCREMENTAL_STALENESS', 
        pvalue   => 'USE_LOCKED_STATS');
END;
/

给表ORDER_DEMO同时设置USE_STALE_PERCENT和USE_LOCKED_STATS

BEGIN
    DBMS_STATS.SET_TABLE_PREFS (
        ownname  => 'OE', 
        tabname  => 'ORDERS_DEMO', 
        pname    => 'INCREMENTAL_STALENESS', 
        pvalue   => 'USE_STALE_PERCENT,USE_LOCKED_STATS');
END;
/

增量统计和分区加载交换

分区的一个好处就是能快速而方便的加载数据,通过exchange partition命令能最小化对用户的影响。exchange partition命令可以让非分区表的数据交换到分区表中的指定分区。这个命令不是物理移动数据,而是更新数据字典交换指针从分区到表,反之亦然。

在之前的版本当中,是无法在分区交换的过程中对非分区表生成必要的统计信息用于支持增量统计信息。而是必须在分区交换动作完成以后才能收集,为了确保全家统计信息能被增量维护。

在12c中,必要的统计信息(概要)可以在交换之前就创建在非分区表上,所以在分区交换的过程中统计信息也被交换用于自动维护增量的全家统计信息。新的DBMS_STATS表选项INCREMENTAL_LEVEL可以被用来去确定一个将要做加载交换的非分区表。INCREMENTAL_LEVEL设成TABLE时(默认PARTITION),oracle将在收集统计信息时自动为表创建概要。这个表级别的概要将在分区交换完毕后变成分区的概要。

exec dbms_stats.set_table_prefs ('SH','EXCHANGETAB','INCREMENTAL','TRUE');
exec dbms_stats.set_table_prefs ('SH','EXCHANGETAB','INCREMENTAL_LEVEL','TABLE');

概要增强

对于那些含有大量分区表的数据仓库应用来说,增量维护节省了大量的时间。然而带来了性能上的收益却也伴随这磁盘费用的增加:概要信息都是储存在SYSAUX表空间,需要越来越多的磁盘用来存储那些超多分区、超多字段、特别是那些字段唯一性特别高的表的概要。除了磁盘的消耗,你必须也要考虑到维护大量概要信息所带来的资源消耗。

从12.2开始,DBMS_STATS包提供了对于收集唯一值信息的新算法,在精确度与以前相似的条件下产生少的多的概要。

DBMS_STATS的新选项APPROXIMATE_NDV_ALGORITHM用来控制创建哪种概要。默认值是REPEAT OR HYPERLOGLOG,表示那些已存在的自适应采用算法将适用于已存在的概要,但是新概要都通过新的HYPERLOGLOG算法。在同一张表中可以混合新旧算法同时使用。

当将一个12.2之前的版本升级到12.2(使用增量信息)时,有三个选项:

1.继续使用12.2以前的格式
调整DBMS_STATS参数为adaptive sampling

EXEC dbms_stats.set_database_prefs ('approximate_ndv_algorithm', 'adaptive sampling');

2.立刻调整旧算法为新的
所有分区的统计信息会被重新收集

EXEC dbms_stats.set_database_prefs ('approximate_ndv_algorithm', 'hyperloglog');
EXEC dbms_stats.set_database_prefs ('incremental_staleness', NULL);

3.逐渐用新算法替换旧算法
旧的概要不会被立即删掉,新的分区会采用新算法格式的概要。混合模式可能会产生不太准确的统计信息,但是这样就不需要在前台重新收集所有统计信息,因为自动统计信息收集任务将重新收集具有旧概要的分区信息,并使用新格式。到最后所有的概要都会处于新的格式,统计信息也都会精确。

EXEC dbms_stats.set_database_prefs ('approximate_ndv_algorithm', 'hyperloglog');
EXEC dbms_stats.set_database_prefs ('incremental_staleness', 'allow_mixed_format');

12.2统计信息之扩展统计信息

从11g开始引入了对列收集扩展统计信息,扩展统计信息包含了两种额外类型的统计信息:组合列和表达式统计信息。

组合列

对于真实数据来说,一个表中多个字段直接的数据通常具有关联性。比如在CUSTOMERS表中,CUST_STATE_PROVINCE字段会受到COUNTRY_ID字段值的影响,比如湖北只会出现在中国下面,如果只有基本的统计信息,优化器是没有办法知道这些关系的,如果在一个语句中这几个字段条件同时存在的话,优化器就会估算错误。这时如果有收集扩展统计信息的话,优化器就能知道它们之间的关系。

对组合列收集信息后,优化器能更好的计算出基数。可以通过函数DBMS_STATS.CREATE_EXTENDED_STATS去实现多个字段的组合收集。当组合字段的统计信息被收集以后,当这张表收集统计信息时,oracle会自动维护这个组合字段的统计信息,跟普通字段一样。

ALTER SYSTEM FLUSH SHARED_POOL;

conn xb/xb;

drop table t1 purge;

create table t1 as 
select 1 col1,2 col2
 from dual 
 connect by level<=5000
union all
select 2 col1,1 col2
 from dual 
 connect by level<=5000;

select dbms_stats.create_extended_stats(user,'t1','(COL1,COL2)') name from dual; 

NAME
--------------------------------------------------------------------------
SYS_STUFLHATC5RBD6JHJZWT$X2AAH

exec dbms_stats.gather_table_stats(user,'t1');

建完组合列和重新收集统计信息后,会看到一个额外由系统生成名称的列

col column_name for a60
select column_name,num_distinct,num_nulls,histogram 
  from user_tab_col_statistics
 where table_name='T1';

COLUMN_NAME                                                  NUM_DISTINCT  NUM_NULLS HISTOGRAM
------------------------------------------------------------ ------------ ---------- ---------------------------------------------
COL1                                                                    2          0 NONE
COL2                                                                    2          0 NONE
SYS_STUFLHATC5RBD6JHJZWT$X2AAH                                          2          0 NONE         <<<<====新生成的字段名

如果要查用户有哪些其他的扩展统计信息,可以查询视图USER_STAT_EXTENSIONS

col table_name for a30
col extension_name for a60
col extension for a60
select table_name,extension_name,extension
 from USER_STAT_EXTENSIONS
 where creator='USER';

TABLE_NAME                     EXTENSION_NAME                                               EXTENSION
------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
T1                             SYS_STUFLHATC5RBD6JHJZWT$X2AAH                               ("COL1","COL2")

自动组合列检测

尽管组合列统计信息非常有用,能很方便的生成好的执行计划,但是一般来说不容易知道在一个特定的负载下该收集哪些组合列的信息。

自动组合列检测决定了在一个特定的负载状态下,表的哪些组合列的统计信息应该被收集。注意此功能不对函数字段生成扩展信息,只对组合列适用。自动组合列检测由以下三个步骤完成:

种子列使用

为了决定适当的组合列,oracle必须了解具有代表性的负载情况。负载情况可以通过SQL Tuning Set查到或者通过负载监控工具。通过过程DBMS_STATS.SEED_COL_USAGE来显示负载,并告诉oracle这个负载要观察多久。下面这个例子表示对当前系统监控5分钟。

begin 
   dbms_stats.seed_col_usage(null,null,300); 
end; 
/

这个监控过程会收集储存在sys.col_usage$当中的信息并储存到sys.col_group_usage$去。包含在这个监控窗口范围内所有执行或解析的sql,当这个监控窗口结束,可以通过一个新的函数DBMS_STATS.REPORT_COL_USAGE去查看到所有表的特定列的使用信息。这个函数会生成一个报告,列出了表所有谓词条件里过滤、关联、group by等操作涉及到的列。

xb@ORA12C> select count(1) from t1 where col1=1 and col2=2;

  COUNT(1)
----------
      5000

xb@ORA12C> select dbms_stats.report_col_usage(user,'T1') from dual;

DBMS_STATS.REPORT_COL_USAGE(USER,'T1')
-----------------------------------------------------------------------
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 XB.T1
.............................

1. COL1                                : EQ                    <<<<====
2. COL2                                : EQ                    <<<<====
3. SYS_STUFLHATC5RBD6JHJZWT$X2AAH      : EQ                    <<<<==== 
4. (COL1, COL2)                        : FILTER                <<<<====
###############################################################################

创建组合列

对每张表调用函数DBMS_STATS.CREATE_EXTENDED_STATS,会自动根据之前监控窗口中得到的有用信息创建必要的组合列。当扩展统计信息创建以后,每当表统计信息被收集时,组合列的统计信息也会自动维护。

xb@ORA12C> select dbms_stats.create_extended_stats(user,'T1') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(USER,'T1')
-------------------------------------------------------------------------------------
###############################################################################

EXTENSIONS FOR XB.T1
....................

1. (COL1, COL2)                        : SYS_STUFLHATC5RBD6JHJZWT$X2AAH exists       <<<<====我这里提示已存在
###############################################################################

也可以手动指定要组合的列

xb@ORA12C> select dbms_stats.create_extended_stats(user,'T1','(COL1,COL2)') from dual;

重新收集统计信息

最后一步就是重新收集表的统计信息,这样新创建的组合列就会拥有统计信息。

exec dbms_stats.gather_table_stats(null,'T1')

SQL计划指示和组合列

sql计划指示不仅仅指示用来优化sql执行计划,可以参考12c Adaptive Statistics,同样可以用来决定组合列是否可以有效解决基数估算不准的问题。如果一个sql计划指示创建了,同时优化器认为基数估算不准的问题可以被这个组合列解决,12cR1中oracle会在下次表分析的时候自动创建组合列。

但是到了12cR2的时候这个特性有了变化,自动创建组合列的功能默认是被关闭的,可以通过包DBMS_STATS包来开启或关闭。

exec dbms_stats.set_global_prefs ('AUTO_STAT_EXTENSIONS', 'OFF');  <<<<====表示不自动创建

exec dbms_stats.set_global_prefs ('AUTO_STAT_EXTENSIONS', 'ON');  <<<<====表示会根据SQL计划指示自动创建

如果你的版本是12.1的话,想关闭这个特性可以通过打补丁解决21171382

表达式统计信息

可以对表达式或函数收集扩展统计信息,去帮助优化器去估算那些谓词条件中包含表达式或者函数的情况。比如条件中含有upper(name)=,那么这时候收集upper(name)的扩展统计信息就会非常有帮助。

drop table t1 purge;

create table t1 (name varchar2(10));

insert into t1 values('xb');
insert into t1 values('XB');
insert into t1 values('Xb');
commit;

select dbms_stats.create_extended_stats(NULL,'T1','(UPPER(NAME))') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'T1','(UPPER(NAME))')
------------------------------------------------------------------------
SYS_STUMMY#LUDFVUOI56L724JL4ZO

跟组合列一样,定义表达式统计信息以后需要重新收集统计信息。收集完毕后,可以通过USER_TAB_COL_STATISTICS视图查询,同样详细信息也可以通过USER_STAT_EXTENSIONS视图查询

col column_name for a60
select column_name,num_distinct,num_nulls,histogram 
  from user_tab_col_statistics
  where table_name='T1';

COLUMN_NAME                                                  NUM_DISTINCT  NUM_NULLS HISTOGRAM
------------------------------------------------------------ ------------ ---------- ---------------------------------------------
NAME                                                                    3          0 NONE
SYS_STUMMY#LUDFVUOI56L724JL4ZO                                          1          0 NONE            <<<<====可以看到经过函数以后的估算值是1


col table_name for a30
col extension_name for a60
col extension for a60
select table_name,extension_name,extension
 from USER_STAT_EXTENSIONS
 where creator='USER';

TABLE_NAME                     EXTENSION_NAME                                               EXTENSION
------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
T1                             SYS_STUMMY#LUDFVUOI56L724JL4ZO                               (UPPER("NAME"))

扩展统计信息的限制

扩展统计信息只适用于那些谓词条件为等于或者IN-LIST。如果列上有直方图信息但是组合列没有,则扩展统计信息不被使用。

12.2统计信息之直方图

介绍

很早之前的版本中,如何执行一个sql语句是由RBO(Rule Based Optimizer)决定的,根据一些设定好的规则来生成执行计划。

后来的版本中CBO(Cost Based Optimizer)被引入进来。CBO检查sql所有可能的执行计划然后选择其中cost最低的一个,cost其实就是反应了执行计划估算的资源消耗。cost越低,这个执行计划也就被认为效率越高。为了能让CBO计算出更准确的cost值,就必须要有sql所有的访问对象的相关信息,包括表、索引等。

这些必要的信息统称为统计信息,理解和管理统计信息对于获得一个好的执行计划是非常重要的。

什么是统计信息

统计信息是用来描述数据库和它其中的对象的数据,这些信息能被优化器使用用于对每个sql生成最好的执行计划。统计信息储存在数据字典当中,能直接通过字典视图来访问,例如USER_TAB_STATISTICS

大部分的统计信息需要定期收集和更新,用来确保当前的信息能真实反映实际储存在数据库中的数据情况。举个例子:部分交易表非常活跃,经常有大量数据的操作,那么这些表就需要经常更新统计信息。而另外一些历史归档表由于历史数据并没有被经常使用,只是时而被查询,那么这些表就不需要定期更新。所以是否需要收集统计信息完全是根据需要来的。

表和列的统计信息

表的统计信息包含有多少行数据,使用了多少个数据块,平均每一行数据使用了多少个byte等等。优化器使用这些信息并且结合其他的一些相关信息,计算出不同执行计划所需的cost,并且估算出各个操作所返回的记录数。比如访问某张表的cost则是用表的数据块数结合参数DB_FILE_MULTIBLOCK_READ_COUNT计算得出。你可以直接通过视图USER_TAB_STATISTICS来查询。

列的统计信息则包括列的唯一值数量(NDV),和列的最大值与最小值,可以通过USER_TAB_COL_STATISTICS视图查询。优化器通过这些信息结合表的相关信息来估算出sql每一步操作会返回的记录数。如果一张表有100行,查询的列上有10个唯一值,那么优化器估算的返回值也为100/10=10。

drop table t purge;

create table t as
select mod(level,10) id from dual
connect by level<=100;

sys@ORA12C> @grp id t
count id in table t...

        ID   COUNT(*)
---------- ----------
         0         10
         1         10
         2         10
         3         10
         4         10
         5         10
         6         10
         7         10
         8         10
         9         10

sys@ORA12C> select count(1) from t where id=1;


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |    10 |   130 |     2   (0)| 00:00:01 |         <<<<==== ROWs估算为10
---------------------------------------------------------------------------

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

   2 - filter("ID"=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          6  recursive calls
          5  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        607  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

额外列统计信息

表和列的统计信息给了优化器很多信息,但是其无法提供一个机制来告诉优化器表或列当中数据的性质。比如这些统计信息无法提供列的数据是否分布均匀,或者多个列的数据是否有关联关系。这种类型的信息只能通过扩展统计信息来获得。其中包括直方图,组合列和表达式统计信息。如果没有这些,那么优化器会默认数据是分布均匀的,然后所有列之间的数据并无关联关系。

直方图

直方图给优化器提供了列数据的分布情况,根据之前的例子,优化器是默认用总行数/唯一值来估算返回记录,如果列的数据分布不均匀,那么得到的结果就区别很大。为了准确反映一个不均匀的数据分布,就需要对列收集直方图。

oracle会自动决定列是否要收集直方图,取决于列的使用情况(SYS.COL_USAGE$),和数据倾斜的状况。比如当一个唯一列总是以等式出现在谓词条件中时,oracle不会对其自动创建直方图。

目前总共有4种直方图:frequency, top-frequency, height-balanced 和 hybrid(top-frequency和hybrid是12c新引入),oracle自动选择合适的类型。一般取决于列上的唯一值数量,可以通过user_tab_col_statistics视图的histogram字段来查询每一列的直方图类型。

frequency Histograms

当列的唯一值小于最大的桶的数量阀值时,默认创建frequency直方图。桶的最大值默认是254,但是可以通过DBMS_STATS来修改成最高至2048(从12c开始)

将之前的数据做下处理,然后收集直方图

delete from t where id=1 and rownum <=9;
delete from t where id=2 and rownum <=8;
delete from t where id=3 and rownum <=7;
delete from t where id=4 and rownum <=6;
delete from t where id=5 and rownum <=5;
delete from t where id=6 and rownum <=4;
delete from t where id=7 and rownum <=3;
delete from t where id=8 and rownum <=2;
delete from t where id=9 and rownum <=1;
commit;

sys@ORA12C> @grp id t
count id in table t...

        ID   COUNT(*)
---------- ----------
         0         10
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9

exec dbms_stats.gather_table_stats(USER,'T',method_opt=>'for all columns size auto');

现在来看一下直方图的情况

select endpoint_value,endpoint_number,endpoint_number-lag(endpoint_number,1,0) over(order by endpoint_number) as frequency
  from user_tab_histograms
 where table_name='T'
   and column_name='ID'
  order by 2;

ENDPOINT_VALUE ENDPOINT_NUMBER  FREQUENCY
-------------- --------------- ----------
             0              10         10
             1              11          1
             2              13          2
             3              16          3
             4              20          4
             5              25          5
             6              31          6
             7              38          7
             8              46          8
             9              55          9

FREQUENCY表示每一个桶值,也就是这个值有多少条记录数。oracle频度直方图将桶称为”endpoint values”,上面的结果就是表示有0-9总共10个桶,ENDPOINT_NUMBER这代表着累计频度,比如endpoint values为1的累计频度就是前后的FREQUENCY的累加值,10+1=11。

注意到ENDPOINT_VALUE为number型,所以对于非number类型的字段收集直方图以后,存储在ENDPOINT_VALUE字段的值也会转码成number型。

sys@ORA12C> @desc user_tab_histograms
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      TABLE_NAME                               VARCHAR2(128)
    2      COLUMN_NAME                              VARCHAR2(4000)
    3      ENDPOINT_NUMBER                          NUMBER
    4      ENDPOINT_VALUE                           NUMBER                          <<<<====这里是number类型
    5      ENDPOINT_ACTUAL_VALUE                    VARCHAR2(4000)
    6      ENDPOINT_ACTUAL_VALUE_RAW                RAW(2000)
    7      ENDPOINT_REPEAT_COUNT                    NUMBER
    8      SCOPE                                    VARCHAR2(7)

当直方图创建完毕以后,优化器就能估算的更加准确。比如id=1时,优化器立刻能知道记录只有1条,就会走索引之类。

高度平衡直方图

与频度直方图相反的是,当列的唯一值大于最大的桶的数量阀值时,默认创建高度平衡直方图。桶的最大值默认是254,但是可以通过DBMS_STATS来修改成最高至2048(从12c开始)。
频度直方图在面对字段有大量唯一值时就比较乏力,因为它每个桶中只存放了一个值,所以随着直方图数量的不断增加,系统在存储、使用和维护这些直方图时就会需要大量的资源。12c开始,在多数情况下oracle采用了新的混合直方图来取代高度平衡直方图,稍后会介绍到混合直方图。

假设一个表含有字段COL1,值从1到1000,下图展示出将数据切分成数个范围。值从1到100的记录数有1000行,值从201到300直接的值突然增高到3000

如果将同样的数据换成高度平衡直方图的话,通过平衡每个范围的大小,可以将每个范围的值调整成近似相等。换句话说,每个垂直条都有近似的高度。比如值1-200有2000行,下一个范围201-267也保持在2000行左右,因为201-300范围的值比较多。

Top-Frequency直方图

传统而言当列的唯一值大于最大的桶的数量阀值时,就会创建高度平衡直方图或者混合直方图。然而有些情况下列中大多数数据都是重复的,其余的相对少量数据却含有大量的唯一值,也就是说数据分布极不均匀。在这种情况下,更适合对表的大多数记录创建频度直方图,忽略对统计信息不重要的一组记录(基数小但是唯一值多)。当选择频度直方图时,数据库必须决定最大的桶数阀值是否足够去准确的计算基数,即使行中的唯一值数量已经超过了这个阀值。它计算列中99.6%的行中有多少个不同的值(99.6%是默认值,但是这个值会根据直方图的数量进行调整)。如果有足够的直方图桶可以用来容纳前N个不同的值,那么将为这些活跃的值创建频率直方图。

在收集统计信息时,只有参数ESTIMATE_PERCENT被设成AUTO_SAMPLE_SIZE(默认值)时,才会创建Top-Frequency直方图,因为必须查看列中的所有值,才能判断是否满足必要的条件(行中的99.6%有254或者更少的唯一值)。

构建一张表包含10000条数据,其中9000条随机1-9,大致上每个值含有1000条数据,其余的值都是唯一值。故总的唯一值数为1009,远超254的阀值。参数METHOD_OPT限制每个桶不超过10

DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT  CASE
         WHEN level <= 9000 THEN TRUNC(DBMS_RANDOM.value(1,10))
         ELSE level
       END AS id
FROM   dual
CONNECT BY level <= 10000;

EXEC DBMS_STATS.gather_table_stats(USER, 't1', method_opt => 'FOR COLUMNS ID SIZE 10');

通过USER_TAB_COLUMNS视图可以查到直方图类型

SELECT column_id,
       column_name,
       histogram
FROM   user_tab_columns
WHERE  table_name = 'T1';

 COLUMN_ID COLUMN_NAME          HISTOGRAM
---------- -------------------- ---------------------------------------------
         1 ID                   TOP-FREQUENCY

下面这个查询展示了Top-Frequency直方图中每个值以及其结束点值和频度,注意到重复度高的值展示的是一个正常的频度直方图,而重复度低的值则被分组成一个低频度的组。

select endpoint_value,endpoint_number, endpoint_number-lag(endpoint_number,1,0) over(order by endpoint_number) as frequency
  from user_tab_histograms
 where table_name='T1'
   and column_name='ID'
     order by 2;

ENDPOINT_VALUE ENDPOINT_NUMBER  FREQUENCY
-------------- --------------- ----------
             1             995        995
             2            1981        986
             3            2971        990
             4            3950        979
             5            4960       1010
             6            5950        990
             7            6932        982
             8            7952       1020
             9            9000       1048
         10000            9001          1
混合直方图

混合直方图类似频度和高度平衡直方图的一个结合,大多数情况下,12c采用混合直方图来取代传统的高度直方图。跟高度平衡直方图不一样的是,混合直方图中结束点值不能跨越桶,除了桶中的最高值外,混合直方图还储存最高值的次数,从而准确的了解其活跃程度,同时也能了解其他结束点值的活跃程度。那么混合直方图如何表示一个活跃的值呢?记录每个结束点值的频率(记录在新字段endpoint_repeat_count),从而提供每个结束点值的活跃程度。

下面这张表有10000行,有5000行随机1-99,其余5000行值唯一。

DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT  CASE
         WHEN MOD(level,2) = 0 THEN TRUNC(DBMS_RANDOM.value(1,100))
         ELSE level
       END AS id
FROM   dual
CONNECT BY level <= 10000;

这里为了触发在正常收集统计信息的时候收集直方图,先用重复度的高的条件查询一遍做硬解析。

sys@ORA12C> select count(1) from t1 where id=1;

  COUNT(1)
----------
        48

sys@ORA12C> EXEC DBMS_STATS.gather_table_stats(USER, 't1');

PL/SQL procedure successfully completed.

通过USER_TAB_COLUMNS视图可以查到直方图类型

SELECT column_id,
       column_name,
       histogram
FROM   user_tab_columns
  WHERE  table_name = 'T1';

 COLUMN_ID COLUMN_NAME          HISTOGRAM
---------- -------------------- ---------------------------------------------
         1 ID                   HYBRID

下面的结果可以看到混合直方图同时具有频度和高度平衡直方图的特性,桶可以包含多个值,终点值存储累计频度。

select histogram,num_buckets
  from user_tab_col_statistics
 where table_name='T1'
   and column_name='ID';

HISTOGRAM                                     NUM_BUCKETS
--------------------------------------------- -----------
HYBRID                                                254

select endpoint_value,endpoint_number,endpoint_repeat_count, endpoint_number-lag(endpoint_number,1,0) over(order by endpoint_number) as frequency
  from user_tab_histograms
 where table_name='T1'
   and column_name='ID'
  order by 2;

ENDPOINT_VALUE ENDPOINT_NUMBER ENDPOINT_REPEAT_COUNT  FREQUENCY
-------------- --------------- --------------------- ----------
             1              48                    48         48
             2              92                    44         44
             3             146                    54         54
             4             195                    49         49
             5             245                    50         50
             6             301                    56         56
             7             351                    50         50
             8             397                    46         46
             9             440                    43         43
            10             478                    38         38
            11             517                    39         39
            12             565                    48         48
            13             602                    37         37
            14             649                    47         47
            15             704                    55         55
            16             744                    40         40
            17             799                    55         55
            18             856                    57         57
            19             912                    56         56
            20             962                    50         50
            21            1008                    46         46
            22            1068                    60         60
            23            1127                    59         59
            24            1185                    58         58
            25            1231                    46         46
            26            1282                    51         51
            27            1333                    51         51
            28            1385                    52         52
            29            1434                    49         49
            30            1481                    47         47
            31            1530                    49         49
            32            1579                    49         49
            33            1630                    51         51
            34            1681                    51         51
            35            1736                    55         55
            36            1799                    63         63
            37            1852                    53         53
            38            1908                    56         56
            39            1966                    58         58
            40            2009                    43         43
            41            2061                    52         52
            42            2109                    48         48
            43            2158                    49         49
            44            2213                    55         55
            45            2253                    40         40
            46            2307                    54         54
            47            2362                    55         55
            48            2415                    53         53
            49            2460                    45         45
            50            2516                    56         56
            51            2557                    41         41
            52            2618                    61         61
            53            2665                    47         47
            54            2733                    68         68
            55            2787                    54         54
            56            2838                    51         51
            57            2895                    57         57
            58            2951                    56         56
            59            3023                    72         72
            60            3068                    45         45
            61            3123                    55         55
            62            3179                    56         56
            63            3235                    56         56
            64            3272                    37         37
            65            3342                    70         70
            66            3392                    50         50
            67            3439                    47         47
            68            3498                    59         59
            69            3557                    59         59
            70            3596                    39         39
            71            3645                    49         49
            72            3695                    50         50
            73            3745                    50         50
            74            3782                    37         37
            75            3842                    60         60
            76            3891                    49         49
            77            3948                    57         57
            78            3989                    41         41
            79            4048                    59         59
            80            4095                    47         47
            81            4153                    58         58
            82            4198                    45         45
            83            4250                    52         52
            84            4308                    58         58
            85            4346                    38         38
            86            4395                    49         49
            87            4448                    53         53
            88            4505                    57         57
            89            4554                    49         49
            90            4604                    50         50
            91            4644                    40         40
            92            4689                    45         45
            93            4743                    54         54
            94            4797                    54         54
            95            4851                    54         54
            96            4894                    43         43
            97            4953                    59         59
            98            5003                    50         50
            99            5050                    47         47
           163            5082                     1         32
           227            5114                     1         32
           291            5146                     1         32
           355            5178                     1         32
           419            5210                     1         32
           483            5242                     1         32
           547            5274                     1         32
           611            5306                     1         32
           677            5339                     1         33
           741            5371                     1         32
           805            5403                     1         32
           869            5435                     1         32
           933            5467                     1         32
           997            5499                     1         32
          1061            5531                     1         32
          1125            5563                     1         32
          1189            5595                     1         32
          1253            5627                     1         32
          1317            5659                     1         32
          1383            5692                     1         33
          1447            5724                     1         32
          1511            5756                     1         32
          1575            5788                     1         32
          1639            5820                     1         32
          1703            5852                     1         32
          1767            5884                     1         32
          1831            5916                     1         32
          1895            5948                     1         32
          1959            5980                     1         32
          2025            6013                     1         33
          2089            6045                     1         32
          2153            6077                     1         32
          2217            6109                     1         32
          2281            6141                     1         32
          2345            6173                     1         32
          2409            6205                     1         32
          2473            6237                     1         32
          2537            6269                     1         32
          2601            6301                     1         32
          2665            6333                     1         32
          2731            6366                     1         33
          2795            6398                     1         32
          2859            6430                     1         32
          2923            6462                     1         32
          2987            6494                     1         32
          3051            6526                     1         32
          3115            6558                     1         32
          3179            6590                     1         32
          3243            6622                     1         32
          3307            6654                     1         32
          3371            6686                     1         32
          3437            6719                     1         33
          3501            6751                     1         32
          3565            6783                     1         32
          3629            6815                     1         32
          3693            6847                     1         32
          3757            6879                     1         32
          3821            6911                     1         32
          3885            6943                     1         32
          3949            6975                     1         32
          4013            7007                     1         32
          4077            7039                     1         32
          4143            7072                     1         33
          4207            7104                     1         32
          4271            7136                     1         32
          4335            7168                     1         32
          4399            7200                     1         32
          4463            7232                     1         32
          4527            7264                     1         32
          4591            7296                     1         32
          4655            7328                     1         32
          4719            7360                     1         32
          4785            7393                     1         33
          4849            7425                     1         32
          4913            7457                     1         32
          4977            7489                     1         32
          5041            7521                     1         32
          5105            7553                     1         32
          5169            7585                     1         32
          5233            7617                     1         32
          5297            7649                     1         32
          5361            7681                     1         32
          5425            7713                     1         32
          5491            7746                     1         33
          5555            7778                     1         32
          5619            7810                     1         32
          5683            7842                     1         32
          5747            7874                     1         32
          5811            7906                     1         32
          5875            7938                     1         32
          5939            7970                     1         32
          6003            8002                     1         32
          6067            8034                     1         32
          6131            8066                     1         32
          6197            8099                     1         33
          6261            8131                     1         32
          6325            8163                     1         32
          6389            8195                     1         32
          6453            8227                     1         32
          6517            8259                     1         32
          6581            8291                     1         32
          6645            8323                     1         32
          6709            8355                     1         32
          6773            8387                     1         32
          6839            8420                     1         33
          6903            8452                     1         32
          6967            8484                     1         32
          7031            8516                     1         32
          7095            8548                     1         32
          7159            8580                     1         32
          7223            8612                     1         32
          7287            8644                     1         32
          7351            8676                     1         32
          7415            8708                     1         32
          7479            8740                     1         32
          7545            8773                     1         33
          7609            8805                     1         32
          7673            8837                     1         32
          7737            8869                     1         32
          7801            8901                     1         32
          7865            8933                     1         32
          7929            8965                     1         32
          7993            8997                     1         32
          8057            9029                     1         32
          8121            9061                     1         32
          8185            9093                     1         32
          8251            9126                     1         33
          8315            9158                     1         32
          8379            9190                     1         32
          8443            9222                     1         32
          8507            9254                     1         32
          8571            9286                     1         32
          8635            9318                     1         32
          8699            9350                     1         32
          8763            9382                     1         32
          8827            9414                     1         32
          8891            9446                     1         32
          8957            9479                     1         33
          9021            9511                     1         32
          9085            9543                     1         32
          9149            9575                     1         32
          9213            9607                     1         32
          9277            9639                     1         32
          9341            9671                     1         32
          9405            9703                     1         32
          9469            9735                     1         32
          9533            9767                     1         32
          9599            9800                     1         33
          9663            9832                     1         32
          9727            9864                     1         32
          9791            9896                     1         32
          9855            9928                     1         32
          9919            9960                     1         32
          9983            9992                     1         32
          9999           10000                     1          8

12c Adaptive Statistics

之前写过一篇12c Adaptive Plans,现在来说一下Adaptive Query Optimization的另外一部分:Adaptive Statistics

sql执行的计划的好坏很大程度上取决于统计信息的质量,现在的sql语句越来越复杂,单纯的依赖单独某个表的统计信息已经无法满足要求,为了使统计信息更加丰富完善,引入了新的帮手Adaptive Statistics

Dynamic Statistics

在一个sql语句编译的过程中,优化器会判断现有的统计信息是否足够用来生成一个好的执行计划,以此来决定是否采用动态采样(dynamic sampling)技术。通常由于统计信息的缺失或不全面而导致生成错误执行计划的情况,dynamic sampling就会派上用场。比如一个sql中,某张表缺失统计信息,优化器就会使用动态采样来收集这张表的一些基本统计信息。但是这种动态采样收集到的信息肯定是不能和DBMS_STATS包收集的数据相提并论的。

从12.1开始,dynamic sampling则被更为先进的Dynamic Statistics所取代。Dynamic Statistics能增加现有的统计信息内容,不仅对单表而言,对那些表的关联和group by之类的合并操作也同样有效。从12.1开始,增加了OPTIMIZER_DYNAMIC_SAMPLING参数新的动态采样级别level 11,这个level 11参数能让优化器自动对所有sql都执行动态统计信息收集,即使所有基表的统计信息都已经存在。Dynamic Statistics能适用于那些之前优化器只能靠猜来判断统计信息的场景,比如谓词里含有LIKE之类的条件。

默认的动态采样级别是2,如果设成11,那么很明显的会有更多的sql收到动态采样的影响。这样会增加sql的解析时间,为了减少对系统产生的影响,在12.1中动态采样的结果会存在于数据库Server Result Cache里,在12.2中变成了存放于SQL plan directive库中。这样的好处是可以允许多个sql共享动态采样收集到的统计信息。

Automatic Re-optimization

sql第一次执行时,会自动生成一个执行计划。在优化的过程中,某些由于缺乏统计信息或谓词条件太复杂而导致错误估算生成的执行计划则会被标记,这时会监控生成的游标。如果系统启用了对游标的监控,那么执行计划估算的基数会用来与sql执行过程中实际得到的基数进行比较,如果两者之间有明显的区别,优化器则会在下一次执行时生成替代的执行计划。优化器能多次re-optimize一个sql,每次都会从真实的执行过程中得到更多信息,帮助一下次得到更好的执行计划。12c支持多种形式的重优化(re-optimization)

Statistics Feedback

Statistics Feedback(替代11g的cardinality Feedback)是重优化的一种形式,能自动的对那些重复执行的sql但又缺乏相关统计信息的执行计划进行优化。在sql第一次执行的过程中,优化器自动生成一个执行计划然后决定是否对游标启用统计信息反馈监控。Statistics Feedback会在几个场景中启用:表没有统计信息;一个表有多个连接或反向的谓词条件,同时过滤条件比较复杂导致优化器不能准确的估算出基数。

在sql执行的最后阶段,优化器会比较原来估算的基数和执行过程中实际发现的基数,如果二者有明显区别,它会储存正确的值用于之后的操作。同时会创建一个SQL plan directive,其他的sql也能直接从这个SQL plan directive中获取想要的信息。如果这个sql再次执行时,优化器则会使用之前储存的正确的统计信息来生成执行计划。当原来估算的统计信息是准确的,则后续的动作都不需要。

下面来演示一下Statistics Feedback

我们知道当查询pipelined table function时,估算的记录通常都不准,刚好适合用来举这个例子。参考Pipelined Table Functions

DROP TYPE t_tf_type;
DROP TYPE t_tf_obj;

CREATE TYPE t_tf_obj AS OBJECT (
  id           NUMBER,
  name  VARCHAR2(50)
);
/

CREATE TYPE t_tf_type IS TABLE OF t_tf_obj;
/

CREATE OR REPLACE FUNCTION get_tab_ptf (cnt IN NUMBER) RETURN t_tf_type PIPELINED AS
BEGIN
  FOR i IN 1 .. cnt LOOP
    PIPE ROW(t_tf_obj(i, 'Name is ' || i));   
  END LOOP;

  RETURN;
END;
/

由于优化器在估算pipelined table function返回的记录数,是由block size来决定,对于默认的8k size来说估算的结果总是8168行。

xb@ORA12C> SELECT /*+ GATHER_PLAN_STATISTICS */* FROM   get_tab_ptf(10);

        ID NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
         1 Name is 1
         2 Name is 2
         3 Name is 3
         4 Name is 4
         5 Name is 5
         6 Name is 6
         7 Name is 7
         8 Name is 8
         9 Name is 9
        10 Name is 10

10 rows selected.

xb@ORA12C> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5znpk47jz9vj1, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */* FROM   get_tab_ptf(10)

Plan hash value: 822655197

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |             |      1 |        |     10 |00:00:00.01 |     470 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| GET_TAB_PTF |      1 |   8168 |     10 |00:00:00.01 |     470 |      <<<<====总是估算为8168行
-----------------------------------------------------------------------------------------------------------

检查V$SQL视图的IS_REOPTIMIZABLE字段,会告诉你优化器已经发现了这个估算不准确的基数,会标记它表示需要重新优化

col sql_text for a70
col is_reoptimizable for a20
xb@ORA12C> 
SELECT sql_text, is_reoptimizable
FROM   v$sql
WHERE  sql_text LIKE '%get_tab_ptf%'
  4      AND    sql_text NOT LIKE '%v$sql%';

SQL_TEXT                                                               IS_REOPTIMIZABLE
---------------------------------------------------------------------- --------------------
SELECT /*+ GATHER_PLAN_STATISTICS */* FROM   get_tab_ptf(10)           Y

这时如果重新执行这个语句,将会得到一个更为准确的估算值,同时会有一个statistics feedback的提示。

xb@ORA12C> SELECT /*+ GATHER_PLAN_STATISTICS */* FROM   get_tab_ptf(10);

        ID NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
         1 Name is 1
         2 Name is 2
         3 Name is 3
         4 Name is 4
         5 Name is 5
         6 Name is 6
         7 Name is 7
         8 Name is 8
         9 Name is 9
        10 Name is 10

10 rows selected.

xb@ORA12C> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5znpk47jz9vj1, child number 1                             <<<<==== child number为新生成
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */* FROM   get_tab_ptf(10)

Plan hash value: 822655197

-------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name        | Starts | E-Rows | A-Rows |   A-Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |             |      1 |        |     10 |00:00:00.01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| GET_TAB_PTF |      1 |     20 |     10 |00:00:00.01 |        <<<<==== 这次估算为20
-------------------------------------------------------------------------------------------------

Note
-----
   - statistics feedback used for this statement                 <<<<==== 表示新的执行计划采用了statistics feedback

采用了statistics feedback的相关信息以后,优化器会根据获得这些信息生成一个新的执行计划

COLUMN sql_text FORMAT A100
COLUMN is_reoptimizable FORMAT A16
xb@ORA12C> 
SELECT sql_id,child_number,sql_text, is_reoptimizable
FROM   v$sql
WHERE  sql_text LIKE '%get_tab_ptf%'
AND    sql_text NOT LIKE '%v$sql%'
   order by 1,2;

SQL_ID                                  CHILD_NUMBER SQL_TEXT                                                                                             IS_REOPTIMIZABLE
--------------------------------------- ------------ ---------------------------------------------------------------------------------------------------- ----------------
5znpk47jz9vj1                                      0 SELECT /*+ GATHER_PLAN_STATISTICS */* FROM   get_tab_ptf(10)                                         Y
5znpk47jz9vj1                                      1 SELECT /*+ GATHER_PLAN_STATISTICS */* FROM   get_tab_ptf(10)                                         N

其实statistics feedback实现的方式就是对sql做了一个OPT_ESTIMATE的hint,这部分信息可以通过V$SQL_REOPTIMIZATION_HINTS视图查到

xb@ORA12C> select sql_id,child_number,hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id='5znpk47jz9vj1' ;

SQL_ID                                  CHILD_NUMBER HINT_TEXT
--------------------------------------- ------------ ----------------------------------------------------------------------------------------------------
5znpk47jz9vj1                                      0 OPT_ESTIMATE (@"SEL$2" TABLE "KOKBF$0"@"SEL$2" ROWS=20.000000 )

由于statistics feedback得到的信息是储存在shared pool里,所以会随着sql cursor被一起被age out,那时就得重新进行硬解析的操作。

sys@ORA12C> alter system flush shared_pool;

System altered.

SELECT sql_id,child_number,sql_text, is_reoptimizable
FROM   v$sql
WHERE  sql_text LIKE '%get_tab_ptf%'
AND    sql_text NOT LIKE '%v$sql%'
  5  order by 1,2;

no rows selected

Performance Feedback

另一种再优化的形式是性能反馈,当在自适应模式下开启自动并行时会帮助改进sql的并发度。

当自适应模式下开启自动并行度时,优化器会决定sql是否并行执行和该采用多少个并发。采用多少个并发通常取决于对语句性能的估算。额外的性能监控会在第一次sql执行时被启用,用于帮助优化器决定是否开启并发。

在初始化执行的最后,优化器选择的并发度会跟实际执行的时候根据实际性能消耗所决定的并发度进行比较,如果二者有明显的区别则sql会被标记需要再优化,然后初始执行所搜集到的相关性能统计数据则会被存储在shared pool中,用于给之后的重复执行提供更准确的信息。

alter session set OPTIMIZER_ADAPTIVE_STATISTICS=true;
alter session set PARALLEL_DEGREE_POLICY=adaptive;

SQL plan directives

SQL plan directives(sql执行计划指示)会根据自动再优化学习到的信息进行自动创建。SQL plan directives是一个优化器用于生成更好执行计划的辅助信息。例如当两表进行关联时,关联的字段数据分布不均匀,SQL plan directives能指示优化器去采取动态统计信息去获取一个更加准确的估算基数。

SQL plan directives创建的时候是基于查询表达式,而不是对语句或某个对象,这能让它可以适用于多个语句。同时对于一个语句也可以使用多个SQL plan directives,一个语句用了多少个会显示在执行计划的下方note部分

构建一张数据分布不均匀的测试表

create table t1 (id number,name varchar2(10),sex varchar2(10));

insert into t1
select level ,'XB','Man'
from dual
connect by level<=10;
commit;

insert into t1
select level+10,'TING','Woman'
from dual
connect by level<=100;
commit;

create index idx_t1 on t1(name);
create index idx2_t1 on t1(sex);

收集统计信息,不搜集直方图,为了让优化器不清楚数据分布情况

xb@ORA12C> @gts t1
Gather Table Statistics for table t1...

PL/SQL procedure successfully completed.

xb@ORA12C> col column_name for a20
SELECT column_id,
       column_name,
       histogram
FROM   user_tab_columns
WHERE  table_name = 'T1'
  6  ORDER BY column_id;

 COLUMN_ID COLUMN_NAME          HISTOGRAM
---------- -------------------- ---------------------------------------------
         1 ID                   NONE
         2 NAME                 NONE
         3 SEX                  NONE

可以查到v$sql的IS_REOPTIMIZABLE字段为Y,表示优化器已经发现了这个错误的估算

COLUMN sql_text FORMAT a100
COLUMN is_reoptimizable FORMAT a20

SELECT sql_text, is_reoptimizable
FROM   v$sql
WHERE  sql_id = '9vgr7qmmxh0vn';

SQL_TEXT                                                                                             IS_REOPTIMIZABLE
---------------------------------------------------------------------------------------------------- --------------------
select /*+ GATHER_PLAN_STATISTICS*/* from t1 where name='XB' and sex='Man'                           Y

这就表示SQL plan directives已经被创建了。

一开始SQL plan directives是存放在sga里的,过一段时间oracle会自动将其持久化到SYSAUX表空间,也可以手动操作

EXEC DBMS_SPD.flush_sql_plan_directive

Statistics feedback是一个迹象表示优化器做了一个错误的选择,一般是由于缺失或者统计信息错误,这时Statistics feedback被用来作为解决方案进行再优化,但是仍然不能解决初始执行错误的问题,因为初始化执行的时候并没有基本的信息作为依据。

SQL plan directives同样也是用来作为避免未来的执行计划错误的办法,在某些情形下,自动的再优化会生产出一些SQL plan directives,但是这些directives并不包含Statistics/performance的反馈,而是通过一些比如动态采样的办法来获取一些信息,这种情况下也就不一定需要Statistics feedback。

所以在语句中有可能是通过Statistics feedback,也有可能是通过SQL plan directives来进行再优化。

12c Adaptive Plans

12c在优化器方面引入了不少的新特性,其中一个就是Adaptive Plans

在特定的条件下优化器会使用这个新特性,比如sql语句包含了表关联、复杂的谓词条件导致很难精准的估算cardinality,Adaptive Plans使优化器可以等到真正在执行sql语句的时候才决定采用哪种执行计划。
优化器会优先选择一个默认执行计划,同时搭配一个statistics collectors,这样就能发现实际的cardinality和估算的cardinality之间是否有比较大的区别,如果有明显区别,那么Adaptive Plans这个新特性就能自动的去选择更优的执行计划。

Adaptive Join Methods

优化器能动态调整连接方法,为整个plan的多个部分预先设定多个子plan。

例如上图,优化器默认选择的计划是PRODUCTS表走索引扫描,然后与ORDERS全表扫描的结果集做嵌套循环,但同时优化器也提供了另一种关联方式,就是两个表都走全表扫描,然后做hash join,然后具体执行的时候该选择那一种就是一个比较复杂的判断了。

sql语句在开始执行之前,statistics collectors会收集必要的信息来提供给下一步的执行计划,而具体要收集哪些信息则是有优化器来决定。首先优化器会计算出一个特定的拐点,作为各个子plan的判断依据。比如假设ORDERS表返回的结果集小于10,这个时候nested loop的COST更优,而结果集大于10时,hash join的COST更好,那么优化器就计算出10这个值作为拐点。优化器之后就会让statistics collectors去统计返回值,如果扫描到10行以上,则从NESTED LOOPS切换到HASH JOIN,否则则维持NESTED LOOPS。statistics collectors主要的作用就是在ORDERS表做全表扫描的过程中进行监控和缓存行,优化器根据statistics collectors得到的信息来决定使用哪个执行计划。

先建两张表

drop table t1 purge;
drop table t2 purge;

create table t1 as select object_id ,object_name,owner from dba_objects where object_id is not null;
alter table t1 add primary key(object_id);
create index idx_t1 on t1(object_name);

create table t2 as select object_id ,object_name,owner from dba_objects where object_id is not null;
alter table t2 add primary key(object_id);


EXEC DBMS_STATS.gather_table_stats(USER, 'T1');
EXEC DBMS_STATS.gather_table_stats(USER, 'T2');

做一个两表关联的sql,看执行计划会是什么样

select t1.owner ,t2.owner 
 from t1,t2 
where t1.object_id=t2.object_id 
  and t1.object_name='TS$';

-------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | E-Rows |E-Bytes| Cost (%CPU)|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |        |       |     5 (100)|
|   1 |  NESTED LOOPS                         |             |      1 |    55 |     5   (0)|
|   2 |   NESTED LOOPS                        |             |      1 |    55 |     5   (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1          |      1 |    45 |     4   (0)|
|*  4 |     INDEX RANGE SCAN                  | IDX_T1      |      1 |       |     3   (0)|
|*  5 |    INDEX UNIQUE SCAN                  | SYS_C007364 |      1 |       |     0   (0)|
|   6 |   TABLE ACCESS BY INDEX ROWID         | T2          |      1 |    10 |     1   (0)|
-------------------------------------------------------------------------------------------

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

   4 - access("T1"."OBJECT_NAME"='TS$')
   5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

   - this is an adaptive plan                             <<<<====表示自适应执行计划

可以通过DBMS_XPLAN.DISPLAY_CURSOR的新参数看到一个自适应计划的每一个步骤

SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive'));

Plan hash value: 970098525

---------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |             |       |       |     5 (100)|          |
|- *  1 |  HASH JOIN                              |             |     1 |    55 |     5   (0)| 00:00:01 |
|     2 |   NESTED LOOPS                          |             |     1 |    55 |     5   (0)| 00:00:01 |
|     3 |    NESTED LOOPS                         |             |     1 |    55 |     5   (0)| 00:00:01 |
|-    4 |     STATISTICS COLLECTOR                |             |       |       |            |          |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1          |     1 |    45 |     4   (0)| 00:00:01 |
|  *  6 |       INDEX RANGE SCAN                  | IDX_T1      |     1 |       |     3   (0)| 00:00:01 |
|  *  7 |     INDEX UNIQUE SCAN                   | SYS_C007364 |     1 |       |     0   (0)|          |
|     8 |    TABLE ACCESS BY INDEX ROWID          | T2          |     1 |    10 |     1   (0)| 00:00:01 |
|-    9 |   TABLE ACCESS FULL                     | T2          |     1 |    10 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   6 - access("T1"."OBJECT_NAME"='TS$')
   7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

每个ID前面带有’-‘符号的表示这个操作并没有被执行计划所采用。

为了得到真实的执行计划,通常建议采用/*+ GATHER_PLAN_STATISTICS */ hint的方式来执行语句,然后通过allstats last的方式来查询结果

select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner 
 from t1,t2 
where t1.object_id=t2.object_id 
  and t1.object_name='TS$';

SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  49n5jxqsqksss, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner  from t1,t2
where t1.object_id=t2.object_id   and t1.object_name='TS$'

Plan hash value: 970098525

-------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |             |      1 |        |      1 |00:00:00.01 |       7 |
|- *  1 |  HASH JOIN                              |             |      1 |      1 |      1 |00:00:00.01 |       7 |
|     2 |   NESTED LOOPS                          |             |      1 |      1 |      1 |00:00:00.01 |       7 |
|     3 |    NESTED LOOPS                         |             |      1 |      1 |      1 |00:00:00.01 |       6 |
|-    4 |     STATISTICS COLLECTOR                |             |      1 |        |      1 |00:00:00.01 |       4 |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1          |      1 |      1 |      1 |00:00:00.01 |       4 |
|  *  6 |       INDEX RANGE SCAN                  | IDX_T1      |      1 |      1 |      1 |00:00:00.01 |       3 |
|  *  7 |     INDEX UNIQUE SCAN                   | SYS_C007364 |      1 |      1 |      1 |00:00:00.01 |       2 |
|     8 |    TABLE ACCESS BY INDEX ROWID          | T2          |      1 |      1 |      1 |00:00:00.01 |       1 |
|-    9 |   TABLE ACCESS FULL                     | T2          |      0 |      1 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   6 - access("T1"."OBJECT_NAME"='TS$')
   7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)


33 rows selected.

现在对表中的数据做一些处理,看执行计划是否能自适应变更

update t1
set object_name='TS$'
where rownum<60000;
commit;

59999 rows updated.

SYS@ora12c> 
Commit complete.

现在T1表的查询条件满足的值不再是一条,而是60000条,这里我并没有重新收集统计信息,看最新的执行计划会是怎样

SQL_ID  49n5jxqsqksss, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner  from t1,t2
where t1.object_id=t2.object_id   and t1.object_name='TS$'

Plan hash value: 970098525

-------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |             |      1 |        |  59999 |00:00:00.15 |   62438 |
|- *  1 |  HASH JOIN                              |             |      1 |      1 |  59999 |00:00:00.15 |   62438 |
|     2 |   NESTED LOOPS                          |             |      1 |      1 |  59999 |00:00:00.13 |   62438 |
|     3 |    NESTED LOOPS                         |             |      1 |      1 |  59999 |00:00:00.13 |    2439 |
|-    4 |     STATISTICS COLLECTOR                |             |      1 |        |  59999 |00:00:00.08 |    1094 |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1          |      1 |      1 |  59999 |00:00:00.03 |    1094 |
|  *  6 |       INDEX RANGE SCAN                  | IDX_T1      |      1 |      1 |  59999 |00:00:00.06 |     565 |
|  *  7 |     INDEX UNIQUE SCAN                   | SYS_C007364 |  59999 |      1 |  59999 |00:00:00.02 |    1345 |
|     8 |    TABLE ACCESS BY INDEX ROWID          | T2          |  59999 |      1 |  59999 |00:00:00.03 |   59999 |
|-    9 |   TABLE ACCESS FULL                     | T2          |      0 |      1 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   6 - access("T1"."OBJECT_NAME"='TS$')
   7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

可以看到重新执行以后,执行计划并未有什么改变,这是由于最终的执行计划是由第一次运行时所决定了

清除shared pool,强制硬解析

SYS@ora12c> alter system flush shared_pool
  2  ;

System altered.

SYS@ora12c> @1
SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  49n5jxqsqksss, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner  from t1,t2
where t1.object_id=t2.object_id   and t1.object_name='TS$'

Plan hash value: 925498821

----------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |             |      1 |        |  59999 |00:00:00.08 |    1268 |       |       |          |
|  *  1 |  HASH JOIN                              |             |      1 |      1 |  59999 |00:00:00.08 |    1268 |  4515K|  2259K| 4429K (0)|
|-    2 |   NESTED LOOPS                          |             |      1 |      1 |  59999 |00:00:00.04 |     635 |       |       |          |
|-    3 |    NESTED LOOPS                         |             |      1 |      1 |  59999 |00:00:00.04 |     635 |       |       |          |
|-    4 |     STATISTICS COLLECTOR                |             |      1 |        |  59999 |00:00:00.03 |     635 |       |       |          |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1          |      1 |      1 |  59999 |00:00:00.02 |     635 |       |       |          |
|  *  6 |       INDEX RANGE SCAN                  | IDX_T1      |      1 |      1 |  59999 |00:00:00.01 |     224 |       |       |          |
|- *  7 |     INDEX UNIQUE SCAN                   | SYS_C007364 |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|-    8 |    TABLE ACCESS BY INDEX ROWID          | T2          |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|     9 |   TABLE ACCESS FULL                     | T2          |      1 |      1 |  72661 |00:00:00.01 |     633 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   6 - access("T1"."OBJECT_NAME"='TS$')
   7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)


33 rows selected.

最终的结果显示完整的执行计划并没有区别,由于硬解析过程中STATISTICS COLLECTOR重新收集了数据,所以根据新的统计信息优化器选择了更优的子计划,用hash join代替了之前的nested loop。

Adaptive Parallel Distribution Method

当一个sql语句并发执行时,例如排序、分组或者关联时需要数据分布在多个不同的并发子进程中。优化器选择哪种分布操作取决于有多少并发子进程,多少行数据。如果优化器无法准确的估算出有多少数据,那就不能得到最优的分布操作。

由于新的自适应分布操作特性(HYBRID HASH),优化器可以直到在最终执行sql时才决定采用哪种分布操作,同理会先采用STATISTICS COLLECTOR收集一波需要缓存的数据信息,根据得到的rows信息来决定分布操作。同样会计算出一个拐点,根据收集到的值与拐点最比较,最终确定分布操作是用HASH还是广播。跟Adaptive JOIN METHOD只影响第一次执行不同的是,Adaptive Parallel Distribution Method影响sql的每一次执行。

EXEC DBMS_STATS.gather_table_stats(USER, 'T1');
EXEC DBMS_STATS.gather_table_stats(USER, 'T2');

SYS@ora12c> alter system flush shared_pool;

System altered.

select /*+ GATHER_PLAN_STATISTICS PARALLEL(8)*/t1.owner ,t2.owner 
 from t1,t2 
where t1.object_id=t2.object_id 
  and t1.object_name='TS$';

SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

SQL_ID  gjm4v4t32wamn, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS PARALLEL(8)*/t1.owner ,t2.owner  from
t1,t2 where t1.object_id=t2.object_id   and t1.object_name='TS$'

Plan hash value: 435755347

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |      1 |        |  59999 |00:00:00.44 |      40 |       |       |          |         |
|   1 |  PX COORDINATOR            |          |      1 |        |  59999 |00:00:00.44 |      40 | 73728 | 73728 |          |         |
|   2 |   PX SEND QC (RANDOM)      | :TQ10002 |      0 |  60537 |      0 |00:00:00.01 |       0 |       |       |          |         |
|*  3 |    HASH JOIN BUFFERED      |          |      0 |  60537 |      0 |00:00:00.01 |       0 |  7106K|  3091K| 4581K (1)|    1024 |
|   4 |     PX RECEIVE             |          |      0 |  72661 |      0 |00:00:00.01 |       0 |       |       |          |         |
|   5 |      PX SEND HYBRID HASH   | :TQ10000 |      0 |  72661 |      0 |00:00:00.01 |       0 |       |       |          |         |        <<<<====新的HYBRID HASH分布操作
|   6 |       STATISTICS COLLECTOR |          |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |         |
|   7 |        PX BLOCK ITERATOR   |          |      0 |  72661 |      0 |00:00:00.01 |       0 |       |       |          |         |
|*  8 |         TABLE ACCESS FULL  | T2       |      0 |  72661 |      0 |00:00:00.01 |       0 |       |       |          |         |
|   9 |     PX RECEIVE             |          |      0 |  60537 |      0 |00:00:00.01 |       0 |       |       |          |         |
|  10 |      PX SEND HYBRID HASH   | :TQ10001 |      0 |  60537 |      0 |00:00:00.01 |       0 |       |       |          |         |        <<<<====新的HYBRID HASH分布操作
|  11 |       PX BLOCK ITERATOR    |          |      0 |  60537 |      0 |00:00:00.01 |       0 |       |       |          |         |
|* 12 |        TABLE ACCESS FULL   | T1       |      0 |  60537 |      0 |00:00:00.01 |       0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   8 - access(:Z>=:Z AND :Z<=:Z)
  12 - access(:Z>=:Z AND :Z<=:Z)
       filter("T1"."OBJECT_NAME"='TS$')

Note
-----
   - Degree of Parallelism is 8 because of hint

Adaptive Bitmap Index Pruning

当优化器生成一个星型转换的执行计划时,必须要选择正确的位图索引结合方式,用以减少访问相关的ROWIDS。如果访问了太多索引,而部分索引又不能很好的过滤数据,那么减少访问的索引量无疑能增加执行效率。Adaptive Bitmap Index Pruning这个特性就是用来自动做这些索引的裁剪工作。

先构建一个星型转换的场景

新建一张事实表和三张维度表

create table DIM1 (dim1_id number,DIM1_COD varchar2(10),DIM1_TXT varchar2(10));
insert into DIM1 values(1,'One','One');
insert into DIM1 values(2,'Two','Two');
insert into DIM1 values(3,'Three','Three');
insert into DIM1 values(4,'Four','Four');
insert into DIM1 values(5,'Five','Five');
insert into DIM1 values(6,'Six','Six');
insert into DIM1 values(7,'Seven','Seven');
insert into DIM1 values(8,'Eight','Eight');
insert into DIM1 values(9,'Nine','Nine');
insert into DIM1 values(10,'Ten','Ten');
commit;

create table DIM2 as select DIM1_ID DIM2_ID,DIM1_COD DIM2_COD,DIM1_TXT DIM2_TXT from DIM1 where rownum<=10;
create table DIM3 as select DIM1_ID DIM3_ID,DIM1_COD DIM3_COD,DIM1_TXT DIM3_TXT from DIM1 where rownum<=10;

create table FACT as select rownum FACT_ID,DIM1_ID,DIM2_ID,DIM3_ID,mod(rownum,1000)/10 FACT_MESURE from
DIM1,DIM2,DIM3,(select * from dual connect by level<=1000);

alter table DIM1 add constraint DIM1PK primary key(DIM1_ID);
alter table FACT add constraint DIM1FK foreign key (DIM1_ID) references DIM1;
create index DIM1BX on FACT(DIM1_ID);
alter table DIM2 add constraint DIM2PK primary key(DIM2_ID);
alter table FACT add constraint DIM2FK foreign key (DIM2_ID) references DIM2;
create index DIM2BX on FACT(DIM2_ID);
alter table DIM3 add constraint DIM3PK primary key(DIM3_ID);
alter table FACT add constraint DIM3FK foreign key (DIM3_ID) references DIM3;
create index DIM3BX on FACT(DIM3_ID);

SYS@ora12c> alter session set star_transformation_enabled=true;

Session altered.

收集相关统计信息

exec DBMS_STATS.gather_table_stats(USER, 'DIM1');
exec DBMS_STATS.gather_table_stats(USER, 'DIM2');
exec DBMS_STATS.gather_table_stats(USER, 'DIM3');
exec DBMS_STATS.gather_table_stats(USER, 'FACT');

当维度表的数据很少时,如果索引的选择性又很好,那么星型转换的效率很高。如下结果:首先对3张维度表进行了扫描(每张表都应用了对应的谓词条件),对三个结果集进行了笛卡尔积关联得到满足所有谓词的数据,得到的结果集包含用于访问fact表的维度键以及得到最后结果所需要的相关信息。对于每一个维度键扫描bitmap index得到相应的行,这些bitmaps随后合并到一起,做完and运算后转换成ROWID后找到对应fact表。这里statistics collectors的目的主要是提供缓存的数据信息,来确定之后是用nested loop还是其他。

SQL_ID  4dk2udqhwz501, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */* from FACT join DIM1
using(DIM1_ID) join DIM2 using(DIM2_ID) join DIM3 using(DIM3_ID) where
DIM1_COD='One' and DIM2_COD='One' and DIM3_COD='One'

Plan hash value: 246786650

-----------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                      |                |      1 |        |   1000 |00:00:00.04 |    1636 |       |       |          |
|  *  1 |  HASH JOIN                            |                |      1 |   1000 |   1000 |00:00:00.04 |    1636 |  1123K|  1123K|  414K (0)|
|     2 |   MERGE JOIN CARTESIAN                |                |      1 |      1 |      1 |00:00:00.01 |      21 |       |       |          |
|     3 |    MERGE JOIN CARTESIAN               |                |      1 |      1 |      1 |00:00:00.01 |      12 |       |       |          |
|  *  4 |     TABLE ACCESS FULL                 | DIM1           |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|     5 |     BUFFER SORT                       |                |      1 |      1 |      1 |00:00:00.01 |       9 |  2048 |  2048 | 2048  (0)|
|  *  6 |      TABLE ACCESS FULL                | DIM2           |      1 |      1 |      1 |00:00:00.01 |       9 |       |       |          |
|     7 |    BUFFER SORT                        |                |      1 |      1 |      1 |00:00:00.01 |       9 |  2048 |  2048 | 2048  (0)|
|  *  8 |     TABLE ACCESS FULL                 | DIM3           |      1 |      1 |      1 |00:00:00.01 |       9 |       |       |          |
|     9 |   VIEW                                | VW_ST_A500F760 |      1 |   1000 |   1000 |00:00:00.04 |    1615 |       |       |          |
|    10 |    NESTED LOOPS                       |                |      1 |   1000 |   1000 |00:00:00.04 |    1615 |       |       |          |
|    11 |     BITMAP CONVERSION TO ROWIDS       |                |      1 |   1000 |   1000 |00:00:00.04 |     615 |       |       |          |
|    12 |      BITMAP AND                       |                |      1 |        |      1 |00:00:00.04 |     615 |       |       |          |
|    13 |       BITMAP MERGE                    |                |      1 |        |      1 |00:00:00.01 |     201 |  1024K|   512K|20480  (0)|
|    14 |        BITMAP KEY ITERATION           |                |      1 |        |      1 |00:00:00.01 |     201 |       |       |          |
|  * 15 |         TABLE ACCESS FULL             | DIM1           |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|    16 |         BITMAP CONVERSION FROM ROWIDS |                |      1 |        |      1 |00:00:00.01 |     198 |       |       |          |
|  * 17 |          INDEX RANGE SCAN             | DIM1BX         |      1 |        |    100K|00:00:00.07 |     198 |       |       |          |
|-   18 |       STATISTICS COLLECTOR            |                |      1 |        |      2 |00:00:00.01 |     207 |       |       |          |
|    19 |        BITMAP MERGE                   |                |      1 |        |      2 |00:00:00.01 |     207 |  1024K|   512K|47104  (0)|
|    20 |         BITMAP KEY ITERATION          |                |      1 |        |      2 |00:00:00.01 |     207 |       |       |          |
|  * 21 |          TABLE ACCESS FULL            | DIM2           |      1 |      1 |      1 |00:00:00.01 |       9 |       |       |          |
|    22 |          BITMAP CONVERSION FROM ROWIDS|                |      1 |        |      2 |00:00:00.01 |     198 |       |       |          |
|  * 23 |           INDEX RANGE SCAN            | DIM2BX         |      1 |        |    100K|00:00:00.06 |     198 |       |       |          |
|-   24 |       STATISTICS COLLECTOR            |                |      1 |        |      5 |00:00:00.01 |     207 |       |       |          |
|    25 |        BITMAP MERGE                   |                |      1 |        |      5 |00:00:00.01 |     207 |  1024K|   512K|  146K (0)|
|    26 |         BITMAP KEY ITERATION          |                |      1 |        |      5 |00:00:00.01 |     207 |       |       |          |
|  * 27 |          TABLE ACCESS FULL            | DIM3           |      1 |      1 |      1 |00:00:00.01 |       9 |       |       |          |
|    28 |          BITMAP CONVERSION FROM ROWIDS|                |      1 |        |      5 |00:00:00.01 |     198 |       |       |          |
|  * 29 |           INDEX RANGE SCAN            | DIM3BX         |      1 |        |    100K|00:00:00.06 |     198 |       |       |          |
|    30 |     TABLE ACCESS BY USER ROWID        | FACT           |   1000 |      1 |   1000 |00:00:00.01 |    1000 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("ITEM_3"="DIM3"."DIM3_ID" AND "ITEM_2"="DIM2"."DIM2_ID" AND "ITEM_1"="DIM1"."DIM1_ID")
   4 - filter("DIM1"."DIM1_COD"='One')
   6 - filter("DIM2"."DIM2_COD"='One')
   8 - filter("DIM3"."DIM3_COD"='One')
  15 - filter("DIM1"."DIM1_COD"='One')
  17 - access("FACT"."DIM1_ID"="DIM1"."DIM1_ID")
  21 - filter("DIM2"."DIM2_COD"='One')
  23 - access("FACT"."DIM2_ID"="DIM2"."DIM2_ID")
  27 - filter("DIM3"."DIM3_COD"='One')
  29 - access("FACT"."DIM3_ID"="DIM3"."DIM3_ID")

Note
-----
   - star transformation used for this statement
   - this is an adaptive plan (rows marked '-' are inactive)

假设表记录估算错误,导致维度表不能很好的过滤掉数据,fact表中大部分的数据都满足维度表的要求,那么优化器会直接先忽略掉那个维度表的条件,先做其他纬度表的bitmap合并操作,等到最后再与这个表做必要的关联。

现在对数据做下处理,更改一下DIM3表的数据,使其所有的数据都满足谓词条件

update DIM3
set DIM3_COD='One';


10 rows updated.

SYS@ora12c> SYS@ora12c> commit;

Commit complete.

重新查看新的执行计划

SYS@ora12c> alter system flush shared_pool;

System altered.

SYS@ora12c> @1
SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4dk2udqhwz501, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */* from FACT join DIM1
using(DIM1_ID) join DIM2 using(DIM2_ID) join DIM3 using(DIM3_ID) where
DIM1_COD='One' and DIM2_COD='One' and DIM3_COD='One'

Plan hash value: 788957811

--------------------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                      |                |      1 |        |  10000 |00:00:00.18 |    3457 |      3 |       |       |          |
|  *  1 |  HASH JOIN                            |                |      1 |   1000 |  10000 |00:00:00.18 |    3457 |      3 |  1123K|  1123K|  921K (0)|
|     2 |   MERGE JOIN CARTESIAN                |                |      1 |      1 |     10 |00:00:00.01 |      21 |      0 |       |       |          |
|     3 |    MERGE JOIN CARTESIAN               |                |      1 |      1 |      1 |00:00:00.01 |      12 |      0 |       |       |          |
|  *  4 |     TABLE ACCESS FULL                 | DIM1           |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |       |       |          |
|     5 |     BUFFER SORT                       |                |      1 |      1 |      1 |00:00:00.01 |       9 |      0 |  2048 |  2048 | 2048  (0)|
|  *  6 |      TABLE ACCESS FULL                | DIM2           |      1 |      1 |      1 |00:00:00.01 |       9 |      0 |       |       |          |
|     7 |    BUFFER SORT                        |                |      1 |      1 |     10 |00:00:00.01 |       9 |      0 |  2048 |  2048 | 2048  (0)|
|  *  8 |     TABLE ACCESS FULL                 | DIM3           |      1 |      1 |     10 |00:00:00.01 |       9 |      0 |       |       |          |
|     9 |   VIEW                                | VW_ST_A500F760 |      1 |   1000 |  10000 |00:00:00.18 |    3436 |      3 |       |       |          |
|    10 |    NESTED LOOPS                       |                |      1 |   1000 |  10000 |00:00:00.18 |    3436 |      3 |       |       |          |
|    11 |     BITMAP CONVERSION TO ROWIDS       |                |      1 |   1000 |  10000 |00:00:00.15 |    2392 |      3 |       |       |          |
|    12 |      BITMAP AND                       |                |      1 |        |      1 |00:00:00.15 |    2392 |      3 |       |       |          |
|    13 |       BITMAP MERGE                    |                |      1 |        |      1 |00:00:00.01 |     201 |      0 |  1024K|   512K|20480  (0)|
|    14 |        BITMAP KEY ITERATION           |                |      1 |        |      1 |00:00:00.01 |     201 |      0 |       |       |          |
|  * 15 |         TABLE ACCESS FULL             | DIM1           |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |       |       |          |
|    16 |         BITMAP CONVERSION FROM ROWIDS |                |      1 |        |      1 |00:00:00.01 |     198 |      0 |       |       |          |
|  * 17 |          INDEX RANGE SCAN             | DIM1BX         |      1 |        |    100K|00:00:00.14 |     198 |      0 |       |       |          |
|-   18 |       STATISTICS COLLECTOR            |                |      1 |        |      2 |00:00:00.01 |     207 |      0 |       |       |          |
|    19 |        BITMAP MERGE                   |                |      1 |        |      2 |00:00:00.01 |     207 |      0 |  1024K|   512K|47104  (0)|
|    20 |         BITMAP KEY ITERATION          |                |      1 |        |      2 |00:00:00.01 |     207 |      0 |       |       |          |
|  * 21 |          TABLE ACCESS FULL            | DIM2           |      1 |      1 |      1 |00:00:00.01 |       9 |      0 |       |       |          |
|    22 |          BITMAP CONVERSION FROM ROWIDS|                |      1 |        |      2 |00:00:00.01 |     198 |      0 |       |       |          |
|  * 23 |           INDEX RANGE SCAN            | DIM2BX         |      1 |        |    100K|00:00:00.12 |     198 |      0 |       |       |          |
|-   24 |       STATISTICS COLLECTOR            |                |      1 |        |      5 |00:00:00.12 |    1984 |      3 |       |       |          |    <<<<====
|-   25 |        BITMAP MERGE                   |                |      1 |        |      5 |00:00:00.12 |    1984 |      3 |  1024K|   512K| 1454K (0)|    <<<<====
|-   26 |         BITMAP KEY ITERATION          |                |      1 |        |     50 |00:00:00.01 |    1984 |      3 |       |       |          |    <<<<====
|- * 27 |          TABLE ACCESS FULL            | DIM3           |      1 |      1 |     10 |00:00:00.01 |       9 |      0 |       |       |          |    <<<<====
|-   28 |          BITMAP CONVERSION FROM ROWIDS|                |     10 |        |     50 |00:00:00.03 |    1975 |      3 |       |       |          |    <<<<====
|- * 29 |           INDEX RANGE SCAN            | DIM3BX         |     10 |        |   1000K|00:00:01.65 |    1975 |      3 |       |       |          |    <<<<====
|    30 |     TABLE ACCESS BY USER ROWID        | FACT           |  10000 |      1 |  10000 |00:00:00.01 |    1044 |      0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("ITEM_3"="DIM3"."DIM3_ID" AND "ITEM_2"="DIM2"."DIM2_ID" AND "ITEM_1"="DIM1"."DIM1_ID")
   4 - filter("DIM1"."DIM1_COD"='One')
   6 - filter("DIM2"."DIM2_COD"='One')
   8 - filter("DIM3"."DIM3_COD"='One')
  15 - filter("DIM1"."DIM1_COD"='One')
  17 - access("FACT"."DIM1_ID"="DIM1"."DIM1_ID")
  21 - filter("DIM2"."DIM2_COD"='One')
  23 - access("FACT"."DIM2_ID"="DIM2"."DIM2_ID")
  27 - filter("DIM3"."DIM3_COD"='One')
  29 - access("FACT"."DIM3_ID"="DIM3"."DIM3_ID")

Note
-----
   - star transformation used for this statement
   - this is an adaptive plan (rows marked '-' are inactive)

可以看到我标注的部分全部都被优化器忽略掉了,并没有走DIM3表的位图扫描、合并等操作,只是最后过滤的时候用到了DIM3进行了hash join。