creatdep oracle_SYS_TEMP_ 临时表的生成与Oracle内部操作

SYS_TEMP_ 临时表的生成与Oracle内部操作

今天有网友问道一个问题，Oracle数据库里发现有一个莫名其妙的临时表，以SYS_TEMP_ 开头，但是不知道是做什么的，也找不到具体的SQL。

我提示：可以通过执行计划去反查SQL，这样就可以从视图中找到相关的SQL语句。

果然，SQL被如此找到。那么这些临时对象是如何产生，其用途又何在呢？

在Oracle数据库中，很多SQL在执行的过程中，都可能产生中间的过程对象，在执行之后会被清理删除，如果你不了解这些内部操作，可能就会发现比较奇怪的对象。看如下测试。

建立一个测试表，执行一个WITH语句查询，中间实体化的过程就引入临时表，这些可以从执行计划中看到：

SQL> connect eygle/eygle@enmo

Connected.

SQL> create table t as select * from dba_objects;

Table created.

SQL> set linesize 150

SQL> set autotrace traceonly

SQL> with t1 as (select count(*) from t) select * from t1 t11, t1 t12;

Execution Plan

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

Plan hash value: 3178380639

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

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

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

|   0 | SELECT STATEMENT       |                   |     1 |    26 |   429     (1)| 00:00:01 |

|   1 |  TEMP TABLE TRANSFORMATION |                   |       |       |        |           |

|   2 |   LOAD AS SELECT       | SYS_TEMP_0FD9D6697_3729F2 |       |       |        |           |

|   3 |    SORT AGGREGATE       |                   |     1 |       |        |           |

|   4 |     TABLE ACCESS FULL       | T                   | 90776 |       |   425     (1)| 00:00:01 |

|   5 |   MERGE JOIN CARTESIAN       |                   |     1 |    26 |     4     (0)| 00:00:01 |

|   6 |    VIEW            |                   |     1 |    13 |     2     (0)| 00:00:01 |

|   7 |     TABLE ACCESS FULL       | SYS_TEMP_0FD9D6697_3729F2 |     1 |    13 |     2     (0)| 00:00:01 |

|   8 |    BUFFER SORT           |                   |     1 |    13 |     4     (0)| 00:00:01 |

|   9 |     VIEW           |                   |     1 |    13 |     2     (0)| 00:00:01 |

|  10 |      TABLE ACCESS FULL       | SYS_TEMP_0FD9D6697_3729F2 |     1 |    13 |     2     (0)| 00:00:01 |

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

Statistics

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

42  recursive calls

8  db block gets

1549  consistent gets

1526  physical reads

668  redo size

619  bytes sent via SQL*Net to client

543  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

1  rows processed

通过10046事件，可以跟踪一下SQL的执行过程：

SQL> set autotrace off

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> with t1 as (select count(*) from t) select * from t1 t11, t1 t12;

COUNT(*)   COUNT(*)

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

90776    90776

SQL> show parameter dump

NAME                     TYPE     VALUE

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

background_core_dump             string     partial

background_dump_dest             string     /u01/app/oracle/diag/rdbms/mom

o/momo/trace

core_dump_dest                 string     /u01/app/oracle/diag/rdbms/mom

o/momo/cdump

max_dump_file_size             string     unlimited

sh