Oracle 12c New Feature: 临时表(global temporary tables) session专有的统计信息

有时我们会在应用程序中用到数据库全局临时表(global temporary tables)用于存放临时的数据集, 在12c之前对于全局临时表上的统计信息处理非常的棘手, 就像我之前的一篇案例(临时表不要收统计信息)中遇到的, 当时建议是不对临时表收集统计信息解决方案, 让数据库使用动态采样来估算实际表中的数据, 因为临时表对所有用户可见,但数据只对当前的用户可见,数据保留分事务级和会话级, 除了动态采样外也可以使用hint固定或者使用dbms_stats表set一个更佳接近实际数据的值以生成正确执行计划.  以前就想过CBO应该改进对于GTT的统计信息收集的方法,后来发现在12c中ORACLE已经解决了这个问题.
在12C以前oracle不会主动维护全局临时表(global temporary tables)的统计信息, 并且如果手动收集也只是存在一份统计信息,即使不同的会话级临时表的数据量不一致,一旦存在对所有的会话可见,从12.1起可以使用GLOBAL_TEMP_TABLE_STATS 控制GTT表的统计信息是所有会话共享还是session级私有,默认session级统计信息是启用的. CBO 在查看表的统计信息时顺序是先看当前session级是否存在统计信息,如果没有再使用共享的统计信息(如果存在), 这样在dba_tab_statistics表中一个临时表就可能存在一个共享统计信息和一个session级统计信息记录, 以scope字段做区分.
另外注意在12c前如果临时表数据是事务级(on commit delete rows)在做dbms_stats收集表统计信息时会先隐性的发起一个commit, 最终临时表的记录为0, 而在12C中则不会删除记录.与该特性相关的隐藏参数是_optimizer_use_gtt_session_stats, default值为true. 该特性对SYS无效,测试请使用其他用户,下面演示一下这个特性.
12C以前的版本的不再演示, 如果收集了临时表统计信息, 所有会话使用相同的统计信息,可能产生错误的执行计划.

SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production                 0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                        0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

验证当前默认临时表stat scope
SQL> SELECT DBMS_STATS.get_prefs('GLOBAL_TEMP_TABLE_STATS') FROM dual;
DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS')
-------------------------------------
SESSION
如果想修改为共享方法
BEGIN
  DBMS_STATS.set_global_prefs (
    pname   => 'GLOBAL_TEMP_TABLE_STATS',
    pvalue  => 'SHARED');
END;
/
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBANBOB                       READ WRITE NO
		 
		 
oracle@anbob ~]$ export TWO_TASK=pdbanbob
[oracle@anbob ~]$ sqlplus anbob/anbob     
SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 21 16:45:47 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Last Successful login time: Mon Feb 20 2017 21:32:52 +08:00
Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
SQL> create global temporary table GTT(id int, name varchar2(20)) on commit preserve rows;
Table created.
SQL> select DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS','ANBOB','GTT') FROM DUAL;
DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS','ANBOB','GTT')
--------------------------------------------------------------------------------
SESSION
SQL> insert into gtt 
    select rownum,'anbob'||rownum from dual   connect by rownum<=1e6; 
1000000 rows created. 
SQL> commit;
Commit complete.
SQL> select  TABLE_NAME, BLOCKS,NUM_ROWS, SCOPE from USER_TAB_STATISTICS where TABLE_NAME = 'GTT';
TABLE_NAME               BLOCKS   NUM_ROWS SCOPE
-------------------- ---------- ---------- -------
GTT                                        SHARED
SQL> @gts gtt
Gather Table Statistics for table gtt...
PL/SQL procedure successfully completed.
SQL> select  TABLE_NAME, BLOCKS,NUM_ROWS, SCOPE from USER_TAB_STATISTICS where TABLE_NAME = 'GTT';
TABLE_NAME               BLOCKS   NUM_ROWS SCOPE
-------------------- ---------- ---------- -------
GTT                                        SHARED
GTT                        3018    1000000 SESSION
收集共享统计信息的方法
SQL> BEGIN
  2    DBMS_STATS.set_global_prefs (
  3      pname   => 'GLOBAL_TEMP_TABLE_STATS',
  4      pvalue  => 'SHARED');
  5  END;
  6  /
PL/SQL procedure successfully completed.
SQL> @gts gtt
Gather Table Statistics for table gtt...
PL/SQL procedure successfully completed.
SQL> select  TABLE_NAME, BLOCKS,NUM_ROWS, SCOPE from USER_TAB_STATISTICS where TABLE_NAME = 'GTT';
TABLE_NAME               BLOCKS   NUM_ROWS SCOPE
-------------------- ---------- ---------- -------
GTT                        3018    1000000 SHARED
GTT                        3018    1000000 SESSION
SQL> set autot trace exp
SQL> select /*+ gather_plan_statistics */ * from gtt;
Execution Plan
----------------------------------------------------------
Plan hash value: 917624683
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000K|    16M|   824   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| GTT  |  1000K|    16M|   824   (1)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - Global temporary table session private statistics used
   
复制

# SESSION 2

SQL> BEGIN
  2    DBMS_STATS.set_global_prefs (
  3      pname   => 'GLOBAL_TEMP_TABLE_STATS',
  4      pvalue  => 'SESSION');
  5  END;
  6  /
PL/SQL procedure successfully completed.
[oracle@anbob ~]$ sqlplus anbob/anbob
SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 21 16:56:23 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Last Successful login time: Tue Feb 21 2017 16:45:47 +08:00
Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
SQL> select * from gtt;
no rows selected
SQL> insert into gtt 
  2      select rownum,'anbob'||rownum from dual   connect by rownum<=10; 
10 rows created. 
SQL> @gts gtt
Gather Table Statistics for table gtt...
PL/SQL procedure successfully completed.
SQL> select  TABLE_NAME, BLOCKS,NUM_ROWS, SCOPE from USER_TAB_STATISTICS where TABLE_NAME = 'GTT';
TABLE_NAME                         BLOCKS   NUM_ROWS SCOPE
------------------------------ ---------- ---------- -------
GTT                                  3018    1000000 SHARED
GTT                                     1         10 SESSION
SQL> set autot trace exp
SQL> select /*+ gather_plan_statistics */ * from gtt;
Execution Plan
----------------------------------------------------------
Plan hash value: 917624683
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |   100 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| GTT  |    10 |   100 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - Global temporary table session private statistics used
复制

Note:
现在可以看到在不同的session级, 查看执行计划使用了session级专有的统计信息, 这样在与其它表关连时不会再像12c以前的版本那样使用错误的cardinality而造成产生错误的执行计划. 并且在使用auto trace或使用dbmt_xplan查看执行计划时会有"Global temporary table session private statistics used"的提示,  同时会注意到在session之间相同的sql使用自己新生成的SQL child cursor. 查看没有共享的原因是"Session Specific Cursor Session Mismatch"

SQL> @sqlt gtt
HASH_VALUE SQL_ID             CHLD# OPT_MODE   SQL_TEXT
---------- ------------- ---------- ---------- --------------------------------------------------------
 496979998 cswbnusftyn0y          0 ALL_ROWS   select /*+ gather_plan_statistics */ * from gtt
 496979998 cswbnusftyn0y          1 ALL_ROWS   select /*+ gather_plan_statistics */ * from gtt
9 rows selected.

SQL> @nonshared2 print cswbnusftyn0y
SQL_ID        CHILD#     REASON                                          REASON_XML
------------- ---------- ----------------------------------------------- --------------------------------------------------------------
cswbnusftyn0y 0          Session Specific Cursor Session Mismatch(1):    
                                                                         0
                                                                         46
                                                                         Session Specific Cursor Session Mismatch(1)
                                                                         7x2
                                                                         1
                                                                         66
                                                                         17003
                                                                         1
                                                                         80
                                                                         1089
                                                                         1
                                                                         
              1          Session Specific Cursor Session Mismatch(1):    
                                                                         1
                                                                         46
                                                                         Session Specific Cursor Session Mismatch(1)
                                                                         7x2
                                                                         1
                                                                         80
                                                                         1089
                                                                         1
                                                                         66
                                                                         17003
                                                                         1
                                                                         
复制

Summary:
在12c以前如果临时表收集的统计信息可能与实际数据不一致时可能因为错误的CARD值CBO产生了错误的执行计划, 从12C引入了session specific statistics, 这样就解决了不同的会话之间数据差异而使用不同的统计信息, 避免cardinality的错误,  该特性从12C中默认是启动的.