[20200819]12c Global Temporary table 统计信息的收集的疑问.txt

来源：这里教程网时间：2026-03-03 16:08:00 作者：

[20200819]12c Global Temporary table 统计信息的收集的疑问.txt Prior to Oracle 12c the database don't maintain separate statistics for global temporary table (GTT), the database maintains one version of statistics shared by all session, even though the data across sessions could differ. Starting with 12c we can set the table-level preference GLOBAL_TEMP_TABLE_STATS to either shared or session-specific (global preference default GLOBAL_TEMP_TABLE_STATS to SESSION). Users can gather statistics on GTT and can have own version of session statistics. During optimization the optimizer first check if session statistics exists if yes, then make use of them. If not optimizer uses Shared statistics if they exist. --//我总感觉这项功能不大实用,应用中谁会分析临时表.除非数据仓库类的应用.我个人还是建议使用SHARED模式.当然给具体问题具体分 --//析.本文仅仅分析我的一些疑问. 1.测试环境： SCOTT@test01p> @ver BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 SCOTT@test01p> SELECT DBMS_STATS.get_param ('GLOBAL_TEMP_TABLE_STATS') GLOBAL_TEMP_TABLE_STATS FROM DUAL; GLOBAL_TEMP_TABLE_STATS ----------------------- SESSION --//缺省参数设置为SESSION。 2.测试: --//session 1: SCOTT@test01p> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- -------------------------------------------------- 97 48554 5428:6256 DEDICATED 4912 25 4 alter system kill session '97,48554' immediate; SCOTT@test01p> create global temporary table T1 (id number, name varchar2(20)) on commit DELETE rows; Table created. --//注意定义on commit DELETE rows; SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1'; TABLE_NAME BLOCKS NUM_ROWS SCOPE -------------------- ---------- ---------- -------------------- T1 SHARED SCOTT@test01p> insert into T1 select rownum id,'test' name from dual connect by level <=100; 100 rows created. SCOTT@test01p> select count(*) from t1; COUNT(*) ---------- 100 SCOTT@test01p> exec dbms_stats.gather_table_stats(user,'T1'); PL/SQL procedure successfully completed. SCOTT@test01p> select count(*) from t1; COUNT(*) ---------- 100 SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1'; TABLE_NAME BLOCKS NUM_ROWS SCOPE -------------------- ---------- ---------- -------------------- T1 SHARED T1 1 100 SESSION --//具有on commit DELETE rows的表以前是无法分析的.因为分析前必须隐含一个提交.而GLOBAL_TEMP_TABLE_STATS采用session方式, --//不会出现信息丢失的情况,也就是分析前后有不会导致信息消失. --//打开一个新的会话,session 2: SCOTT@test01p> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- -------------------------------------------------- 96 43472 6464:5472 DEDICATED 5412 41 6 alter system kill session '96,43472' immediate; SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1'; TABLE_NAME BLOCKS NUM_ROWS SCOPE -------------------- ---------- ---------- -------------------- T1 SHARED --//另外的会话没有scope=SESSION的信息,也就是分析仅仅对当前会话有效. --//session 1: SCOTT@test01p> commit ; Commit complete. SCOTT@test01p> select count(*) from t1; COUNT(*) ---------- 0 SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1'; TABLE_NAME BLOCKS NUM_ROWS SCOPE -------------------- ---------- ---------- -------------------- T1 SHARED T1 1 100 SESSION --//可以发现即使我提交了,记录数为0,session 的统计信息还在. 3.10046跟踪看看: SCOTT@test01p> insert into T1 select rownum id,'test' name from dual connect by level <=10; 10 rows created. SCOTT@test01p> @ 10046on 12 Session altered. SCOTT@test01p> exec dbms_stats.gather_table_stats(user,'T1'); PL/SQL procedure successfully completed. SCOTT@test01p> @ 10046off Session altered. SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1'; TABLE_NAME BLOCKS NUM_ROWS SCOPE -------------------- ---------- ---------- -------------------- T1 SHARED T1 1 10 SESSION SCOTT@test01p> select count(*) from t1; COUNT(*) ---------- 10 --//查看跟踪文件,我自己也没看出来门道.我估计采用自治事务的形式来实现提交.那位知道? 4.如果修改GLOBAL_TEMP_TABLE_STATS=SHARED: --//退出,重新登陆: SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1'; TABLE_NAME BLOCKS NUM_ROWS SCOPE -------------------- ---------- ---------- -------------------- T1 SHARED SCOTT@test01p> exec dbms_stats.set_table_prefs(user,'T1','GLOBAL_TEMP_TABLE_STATS','SHARED'); PL/SQL procedure successfully completed. SCOTT@test01p> SELECT DBMS_STATS.GET_PREFS ('GLOBAL_TEMP_TABLE_STATS','SCOTT','T1') GLOBAL_TEMP_TABLE_STATS FROM DUAL; GLOBAL_TEMP_TABLE_STATS ------------------------ SHARED SCOTT@test01p> insert into T1 select rownum id,'test' name from dual connect by level <=20; 20 rows created. SCOTT@test01p> exec dbms_stats.gather_table_stats(user,'T1'); PL/SQL procedure successfully completed. SCOTT@test01p> select count(*) from t1; COUNT(*) ---------- 0 --//在执行分析前暗含一个提交.这样记录数量0. SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1'; TABLE_NAME BLOCKS NUM_ROWS SCOPE -------------------- ---------- ---------- -------------------- T1 0 0 SHARED --//虽然有统计,但是这样可能致命的.因为统计记录是0.很有可能导致执行计划畸形. SCOTT@test01p> alter session set statistics_level = all; Session altered. SCOTT@test01p> select * from dept,t1 ; no rows selected SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 8w67v982tdp0c, child number 0 ------------------------------------- select * from dept,t1 Plan hash value: 3780723038 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 0 |00:00:00.01 | | 1 | MERGE JOIN CARTESIAN| | 1 | 1 | 45 | 5 (0)| 00:00:01 | 0 |00:00:00.01 | | 2 | TABLE ACCESS FULL | T1 | 1 | 1 | 25 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | | 3 | BUFFER SORT | | 0 | 4 | 80 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | | 4 | TABLE ACCESS FULL | DEPT | 0 | 4 | 80 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | ------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 4 - SEL$1 / DEPT@SEL$1 23 rows selected. --//可以发现优先选择T1表.像以前这种情况只能伪造统计信息.或者就是采用动态分析方式. SCOTT@test01p> exec dbms_stats.set_table_stats(user,'T1',numrows=>100, numblks=>1); PL/SQL procedure successfully completed. SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1'; TABLE_NAME BLOCKS NUM_ROWS SCOPE -------------------- ---------- ---------- -------------------- T1 1 100 SHARED SCOTT@test01p> select * from dept,t1; no rows selected SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID cd332qxynx6dd, child number 0 ------------------------------------- select * from dept,t1 Plan hash value: 3973748831 ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 0 |00:00:00.01 | 7 | | 1 | MERGE JOIN CARTESIAN| | 1 | 400 | 18000 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 7 | | 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 | | 3 | BUFFER SORT | | 4 | 100 | 2500 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | 4 | TABLE ACCESS FULL | T1 | 1 | 100 | 2500 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | ----------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / DEPT@SEL$1 4 - SEL$1 / T1@SEL$1 5.最后看看两个信息都存在的情况下会选择那个. SCOTT@test01p> exec dbms_stats.set_table_prefs(user,'T1','GLOBAL_TEMP_TABLE_STATS','SESSION'); PL/SQL procedure successfully completed. SCOTT@test01p> insert into T1 select rownum id,'test' name from dual connect by level <=200; 200 rows created. SCOTT@test01p> exec dbms_stats.gather_table_stats(user,'T1'); PL/SQL procedure successfully completed. SCOTT@test01p> SELECT table_name, blocks, num_rows, scope,last_analyzed FROM user_tab_statistics WHERE table_name = 'T1'; TABLE_NAME BLOCKS NUM_ROWS SCOPE LAST_ANALYZED -------------------- ---------- ---------- -------------------- ------------------- T1 1 100 SHARED 2020-08-27 22:16:24 T1 1 200 SESSION 2020-08-27 22:17:51 SCOTT@test01p> select count(*) from t1; COUNT(*) ---------- 200 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 5bc0v4my7dvr5, child number 0 ------------------------------------- select count(*) from t1 Plan hash value: 3724264953 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 2 (100)| | 1 |00:00:00.01 | 3 | | 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 3 | | 2 | TABLE ACCESS FULL| T1 | 1 | 200 | 2 (0)| 00:00:01 | 200 |00:00:00.01 | 3 | ------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 Note ----- - Global temporary table session private statistics used ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//优先使用session的统计.不过有点奇怪的是: SCOTT@test01p> select count(*) from t1 where id=1; COUNT(*) ---------- 1 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID fmfby2uppmz68, child number 0 ------------------------------------- select count(*) from t1 where id=1 Plan hash value: 3724264953 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 3 | | 1 | SORT AGGREGATE | | 1 | 1 | 4 | | | 1 |00:00:00.01 | 3 | |* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 4 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"=1) Note ----- - Global temporary table session private statistics used --//这里E-ROWS=1,搞不懂....估计其它信息比如字段信息没有导入. 6.总结: --//12c之后注意临时表的收集方式,我看了生产系统我一般选择定义on commit preserve rows;