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

摘要:twp-optimizer-with-oracledb-12c-1963236.pdf

Optimizer with Oracle Database 12c

Session level statistics on Global Temporary Tables P22

Global temporary tables are often used to store intermediate results in an application context. A global
temporary table shares (GTT) its definition system-wide with all users with the appropriate privileges,
but the data content is always session-private. It has always been possible to gather statistics on a global
temporary table (that persist rows on commit); however in previous releases the statistics gathered
would be used by all sessions accessing that table. This was less than ideal if the volume or nature of
the data stored in the GTT differed greatly between sessions.

It is now possible to have a separate set of statistics for every session using a GTT.  Statistics sharing
on a GTT is controlled using a new DBMS_STATS preference GLOBAL_TEMP_TABLE_STATS. By default
the preference is set to SESSION, meaning each session accessing the GTT will have it's own set of
statistics. The optimizer will try to use session statistics first but if session statistics do not exist, then
optimizer will use shared statistics. If you want to revert back to the prior behavior. of only one set of
statistics used by all session, set the GLOBAL_TEMP_TABLE_STATS preference to 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.GLOBAL_TEMP_TABLE_STATS=session

create global temporary table GTT_T1 (d number, name varchar2(20)) on commit DELETE rows;

insert into GTT_T1 select rownum id,'test' name from dual connect by level <=100;

SCOTT@test01p> select dbms_stats.get_prefs('GLOBAL_TEMP_TABLE_STATS',user,'GTT_T1') c20 from dual;

C20

--------------------

SESSION
--参数缺省是session。

SCOTT@test01p> select count(*) from gtt_t1 ;

  COUNT(*)

----------

       100

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------------------------------

SQL_ID  1b40b6ymz94px, child number 0

-------------------------------------

select count(*) from gtt_t1

Plan hash value: 3957256632

-----------------------------------------------------------

| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |        |        |     2 (100)|

|   1 |  SORT AGGREGATE    |        |      1 |            |

|   2 |   TABLE ACCESS FULL| GTT_T1 |      1 |     2   (0)|

-----------------------------------------------------------

SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'GTT_T1');

PL/SQL procedure successfully completed.

SCOTT@test01p> select count(*) from gtt_t1 ;

  COUNT(*)

----------

       100

--难道这里没有隐含commit吗？-难道分析临时表不是隐含commit吗？查询还有结果，为什么？

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------------------------------

SQL_ID  1b40b6ymz94px, child number 0

-------------------------------------

select count(*) from gtt_t1

Plan hash value: 3957256632

-----------------------------------------------------------

| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |        |        |     2 (100)|

|   1 |  SORT AGGREGATE    |        |      1 |            |

|   2 |   TABLE ACCESS FULL| GTT_T1 |    100 |     2   (0)|

-----------------------------------------------------------

--分析后统计信息正确！

SCOTT@test01p> select num_rows,last_analyzed from user_tables where table_name = 'GTT_T1';

  NUM_ROWS LAST_ANALYZED

---------- -------------------

--没有信息，说明前面的分析根本没有执行。

create global temporary table GTT_T2 (d number, name varchar2(20)) on commit PRESERVE rows;

insert into GTT_T2 select rownum id,'test' name from dual connect by level <=200;

SCOTT@test01p> select count(*) from gtt_T2 ;

  COUNT(*)

----------

       200

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------------------------------

SQL_ID  gxbfux75kpj3d, child number 0

-------------------------------------

select count(*) from gtt_T2

Plan hash value: 1338463290

-----------------------------------------------------------

| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |        |        |     2 (100)|

|   1 |  SORT AGGREGATE    |        |      1 |            |

|   2 |   TABLE ACCESS FULL| GTT_T2 |    200 |     2   (0)|

-----------------------------------------------------------

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'GTT_T2');

PL/SQL procedure successfully completed.

SCOTT@test01p> select count(*) from gtt_T2 ;

  COUNT(*)

----------

       200

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------

SQL_ID  gxbfux75kpj3d, child number 0

-------------------------------------

select count(*) from gtt_T2

Plan hash value: 1338463290

-----------------------------------------------------------

| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |        |        |     2 (100)|

|   1 |  SORT AGGREGATE    |        |      1 |            |

|   2 |   TABLE ACCESS FULL| GTT_T2 |    200 |     2   (0)|

-----------------------------------------------------------

--注意上下执行计划的区别是前者是使用dynamic sampling。后者没有

SCOTT@test01p> select num_rows,last_analyzed from user_tables where table_name = 'GTT_T2';

  NUM_ROWS LAST_ANALYZED

---------- -------------------

--奇怪统计信息放那里呢？真不懂12c如何做到的？难道仅仅在会话的某个内存中吗？

2.GLOBAL_TEMP_TABLE_STATS=share

exec dbms_stats.set_table_prefs(user,'GTT_T1','GLOBAL_TEMP_TABLE_STATS','SHARED') ;

exec dbms_stats.set_table_prefs(user,'GTT_T2','GLOBAL_TEMP_TABLE_STATS','SHARED') ;

SCOTT@test01p> select dbms_stats.get_prefs('GLOBAL_TEMP_TABLE_STATS',user,'GTT_T1') c20 from dual;

C20

--------------------

SHARED

退出进入重复测试：

insert into GTT_T1  select rownum id,'test' name from dual connect by level <=100;

SCOTT@test01p> select count(*) from gtt_T1 ;

  COUNT(*)

----------

       100

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

------------------------------------------------------------

SQL_ID  2bar3u3cr7t8r, child number 0

-------------------------------------

select count(*) from gtt_T1

Plan hash value: 3957256632

-----------------------------------------------------------

| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |        |        |     2 (100)|

|   1 |  SORT AGGREGATE    |        |      1 |            |

|   2 |   TABLE ACCESS FULL| GTT_T1 |    100 |     2   (0)|

-----------------------------------------------------------

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'GTT_T1');

PL/SQL procedure successfully completed.

SCOTT@test01p> select count(*) from gtt_T1 ;

  COUNT(*)

----------

         0

--这回结果是0，说明分析临时表完成后，隐含commit命令，导致临时表的信息清除。注gtt_t1: on commit DELETE rows.

SCOTT@test01p> select num_rows,last_analyzed from user_tables where table_name = 'GTT_T1';

  NUM_ROWS LAST_ANALYZED

---------- -------------------

         0 2013-10-18 22:18:28

--从这里也可以看出，有统计信息了。看来具有on commit DELETE rows特性的临时表的统计信息要伪造才可以。

SCOTT@test01p> insert into GTT_T2  select rownum id,'test' name from dual connect by level <=200;

200 rows created.

SCOTT@test01p> select count(*) from gtt_T2 ;

  COUNT(*)

----------

       200

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  gxbfux75kpj3d, child number 0

-------------------------------------

select count(*) from gtt_T2

Plan hash value: 1338463290

-----------------------------------------------------------

| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |        |        |     2 (100)|

|   1 |  SORT AGGREGATE    |        |      1 |            |

|   2 |   TABLE ACCESS FULL| GTT_T2 |    200 |     2   (0)|

-----------------------------------------------------------

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'GTT_T2');

PL/SQL procedure successfully completed.

SCOTT@test01p> select num_rows,last_analyzed from user_tables where table_name = 'GTT_T2';

  NUM_ROWS LAST_ANALYZED

---------- -------------------

       200 2013-10-18 22:24:16

SCOTT@test01p> select count(*) from gtt_T2 ;

  COUNT(*)

----------

       200

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  gxbfux75kpj3d, child number 0

-------------------------------------

select count(*) from gtt_T2

Plan hash value: 1338463290

-----------------------------------------------------------

| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |        |        |     2 (100)|

|   1 |  SORT AGGREGATE    |        |      1 |            |

|   2 |   TABLE ACCESS FULL| GTT_T2 |    200 |     2   (0)|

-----------------------------------------------------------

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

--奇怪执行计划依旧使用动态取样。

--修改1个字符看看？select => Select .

SCOTT@test01p> Select count(*) from gtt_T2 ;

  COUNT(*)

----------

       200

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

----------------------------------------------------------------

SQL_ID  7xj17ts3qm5mv, child number 0

-------------------------------------

Select count(*) from gtt_T2

Plan hash value: 1338463290

-----------------------------------------------------------

| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |        |        |     2 (100)|

|   1 |  SORT AGGREGATE    |        |      1 |            |

|   2 |   TABLE ACCESS FULL| GTT_T2 |    200 |     2   (0)|

-----------------------------------------------------------

--退出再进入：

SCOTT@test01p> select num_rows,last_analyzed from user_tables where table_name = 'GTT_T2';

  NUM_ROWS LAST_ANALYZED

---------- -------------------

       200 2013-10-18 22:24:16

--统计信息已经在数据库里面。退出再进入：

SCOTT@test01p> SElect count(*) from gtt_T2 ;

  COUNT(*)

----------

         0

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

--------------------------------------------------------------

SQL_ID  03vac2xzj0uyr, child number 0

-------------------------------------

SElect count(*) from gtt_T2

Plan hash value: 1338463290

-----------------------------------------------------------

| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |        |        |     2 (100)|

|   1 |  SORT AGGREGATE    |        |      1 |            |

|   2 |   TABLE ACCESS FULL| GTT_T2 |    200 |     2   (0)|

-----------------------------------------------------------

--虽然记数=0,但是执行计划的信息保持还是E-Rows=200.