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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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字段，会告诉你优化器已经发现了这个估算不准确的基数，会标记它表示需要重新优化

1
2
3
4
5
6
7
8
9
10
11
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的提示。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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的相关信息以后，优化器会根据获得这些信息生成一个新的执行计划

1
2
3
4
5
6
7
8
9
10
11
12
13
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视图查到

1
2
3
4
5
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，那时就得重新进行硬解析的操作。

1
2
3
4
5
6
7
8
9
10
11
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部分

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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);

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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，表示优化器已经发现了这个错误的估算

1
2
3
4
5
6
7
8
9
10
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表空间，也可以手动操作

1
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来进行再优化。

Tuning
12c Adaptive Statistics
https://www.xbdba.com/2018/12/10/12c-adaptive-statistics/
作者
xbdba
发布于
2018年12月10日
许可协议