查看oracle全局临时表,全局临时表不收集统计信息

网上看到一个结论，临时表的数据是不做分析的，因为它存放的数据是临时性的，可能很快就释放了，但是当一个查询关联到这样的临时表时，CBO要想获得临时表上的统计信息分析数据，就只能依赖于动态采样了。经我测试，

(1)事务级临时表确实不能收集统计信息，而且手动一收集，临时段就会被截断。测试如下：

SQL> create global temporary table tmp  (object_type varchar2(19));

Table created.

SQL> insert into tmp select distinct object_type from dba_objects;

41 rows created.

SQL> SELECT COUNT(*) from tmp;

COUNT(*)

----------

41

SQL> select

2      TABLE_NAME,

3      NUM_ROWS,

4      BLOCKS,

5      EMPTY_BLOCKS,

6      AVG_SPACE,

7      CHAIN_CNT,

8      AVG_ROW_LEN,

9      GLOBAL_STATS,

10      USER_STATS,

11      SAMPLE_SIZE,

12      to_char(t.last_analyzed,'MM-DD-YYYY')

13  from dba_tables t

14  where

15      owner = 'SYS'

16  and table_name = 'TMP';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE

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

CHAIN_CNT AVG_ROW_LEN GLO USE SAMPLE_SIZE TO_CHAR(T.

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

TMP

NO  NO

SQL> set pages 1000 lines 1000

SQL> select count(*) from tmp;

COUNT(*)

----------

41

SQL> SELECT count(*) from tmp;

COUNT(*)

----------

41

SQL>  exec dbms_stats.delete_table_stats(ownname=>'SYS',tabname=>'TMP');

PL/SQL procedure successfully completed.

SQL> select

2      TABLE_NAME,

3      NUM_ROWS,

4      BLOCKS,

5      EMPTY_BLOCKS,

6      AVG_SPACE,

7      CHAIN_CNT,

8      AVG_ROW_LEN,

9      GLOBAL_STATS,

10      USER_STATS,

11      SAMPLE_SIZE,

12      to_char(t.last_analyzed,'MM-DD-YYYY')

13  from dba_tables t

14  where

15      owner = 'SYS'

16  and table_name = 'TMP';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN GLO USE SAMPLE_SIZE TO_CHAR(T.

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

TMP                                                                                                 NO  NO

SQL> SELECT COUNT(*) FROM TMP;

COUNT(*)

----------

0

(2)创建一个会话级临时表，收集统计信息的时候是生效的，跟正常的表是一样的，没收集统计信息的时候，采用动态采样，收集完统计信息之后，不采用动态采样。具体过程如下：

SQL>  create global temporary table tmp  (object_type varchar2(19)) ON COMMIT preserve ROWS;

Table created.

SQL> insert into tmp select distinct object_type from dba_objects;

41 rows created.

SQL> select count(*) from tmp;

COUNT(*)

----------

41

SQL> commit;

Commit complete.

SQL> select count(*) from tmp;

COUNT(*)

----------

41

SQL> select

2      TABLE_NAME,

3      NUM_ROWS,

4      BLOCKS,

5      EMPTY_BLOCKS,

6      AVG_SPACE,

7      CHAIN_CNT,

8      AVG_ROW_LEN,

9      GLOBAL_STATS,

10      USER_STATS,

11      SAMPLE_SIZE,

12      to_char(t.last_analyzed,'MM-DD-YYYY')

13  from dba_tables t

14  where

15      owner = 'SYS'

16  and table_name = 'TMP';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN GLO USE SAMPLE_SIZE TO_CHAR(T.

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

TMP                                                                                                 NO  NO

SQL> set autot trace

SQL> select count(*) from tmp;

Execution Plan

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

Plan hash value: 3231217655

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

| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |            |          |

|   2 |   TABLE ACCESS FULL| TMP  |    41 |     2   (0)| 00:00:01 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

0  recursive calls

0  db block gets

3  consistent gets

0  physical reads

0  redo size

526  bytes sent via SQL*Net to client

523  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> set autot off

SQL> exec dbms_stats.gather_table_stats(ownname=>'sys',tabname=>'tmp');

PL/SQL procedure successfully completed.

SQL> select

2      TABLE_NAME,

3      NUM_ROWS,

4      BLOCKS,

5      EMPTY_BLOCKS,

6      AVG_SPACE,

7      CHAIN_CNT,

8      AVG_ROW_LEN,

9      GLOBAL_STATS,

10      USER_STATS,

11      SAMPLE_SIZE,

12      to_char(t.last_analyzed,'MM-DD-YYYY')

13  from dba_tables t

14  where

15      owner = 'SYS'

16  and table_name = 'TMP';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN GLO USE SAMPLE_SIZE TO_CHAR(T.

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

TMP                                    41          1            0          0          0          10 YES NO           41 05-25-2018

SQL> set autot trace

SQL> select count(*) from tmp;

Execution Plan

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

Plan hash value: 3231217655

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

| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |            |          |

|   2 |   TABLE ACCESS FULL| TMP  |    41 |     2   (0)| 00:00:01 |

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

Statistics

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

0  recursive calls

0  db block gets

3  consistent gets

0  physical reads

0  redo size

526  bytes sent via SQL*Net to client

523  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@oracledb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri May 25 03:13:05 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from tmp;

COUNT(*)

----------

0

环境为11.2.0.4.按此测试，网上说的临时表要采用动态采样了，此说法就不太准确了，因为会话级的临时表手动收集统计信息后，会包含统计信息的。求大声知道？？？