ITPub博客

首页 > 数据库 > Oracle > [20200819]12c Global Temporary table 统计信息的收集的疑问.txt

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

原创 Oracle 作者:lfree 时间:2020-08-29 10:13:13 0 删除 编辑

[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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2715672/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2713
  • 访问量
    6543086