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中默认是启动的.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。