« 东北大学、西财、兰州商学院-2013 ACOUG高校行琐记 | Blog首页 | Oracle Database 12c 正式发布-多租户特性嵌入云计算 »
SYS_TEMP_ 临时表的生成与Oracle内部操作
链接：https://www.eygle.com/archives/2013/06/sys_temp_oracle.html
今天有网友问道一个问题，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
shadow_core_dump string partial
user_dump_dest string /u01/app/oracle/diag/rdbms/mom
o/momo/trace
检查跟踪文件，可以看到详细的内部过程，以下引用了整个跟踪文件，供参考：
[oracle@enmodb12c trace]$ more momo_ora_8853.trc
Trace file /u01/app/oracle/diag/rdbms/momo/momo/trace/momo_ora_8853.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/db_2
System name: Linux
Node name: enmodb12c
Release: 2.6.39-200.24.1.el6uek.x86_64
Version: #1 SMP Sat Jun 23 02:39:07 EDT 2012
Machine: x86_64
Instance name: momo
Redo thread mounted by this instance: 1
Oracle process number: 32
Unix process pid: 8853, image: oracle@enmodb12c
*** 2013-06-21 10:12:44.590
*** SESSION ID:(17.19381) 2013-06-21 10:12:44.590
*** CLIENT ID:() 2013-06-21 10:12:44.590
*** SERVICE NAME:(enmo) 2013-06-21 10:12:44.590
*** MODULE NAME:(SQL*Plus) 2013-06-21 10:12:44.590
*** ACTION NAME:() 2013-06-21 10:12:44.590
*** CONTAINER ID:(3) 2013-06-21 10:12:44.590
WAIT #140228855137296: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=654 tim=81803158300
*** 2013-06-21 10:13:04.186
WAIT #140228855137296: nam='SQL*Net message from client' ela= 19595252 driver id=1413697536 #bytes=1 p3=0 obj#=654 tim=81822753857
CLOSE #140228855137296:c=0,e=21,dep=0,type=1,tim=81822753959
=====================
PARSING IN CURSOR #140228859013352 len=245 dep=1 uid=0 oct=3 lid=0 tim=81822754671 hv=1602468406 ad='bbd281c0' sqlid='1p5grz1gs7fjq'
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2, spare3, signature, spare7, spare8, spare9 from obj$ where owner#=:1 and name=:
2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
BINDS #140228859013352:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=80 off=0
kxsbbbfp=7f8992eb7fb0 bln=22 avl=01 flg=05
value=0
Bind#1
oacdty=01 mxl=32(25) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=178 siz=0 off=24
kxsbbbfp=7f8992eb7fc8 bln=32 avl=25 flg=01
value="SYS_TEMP_0FD9D6698_3729F2"
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=56
kxsbbbfp=7f8992eb7fe8 bln=22 avl=02 flg=01
value=1
EXEC #140228859013352:c=0,e=159,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=813480514,tim=81822754810
FETCH #140228859013352:c=0,e=17,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,plh=813480514,tim=81822754840
CLOSE #140228859013352:c=0,e=1,dep=1,type=3,tim=81822754858
=====================
PARSING IN CURSOR #140228855286880 len=97 dep=1 uid=0 oct=3 lid=0 tim=81822754938 hv=791757000 ad='bbd026b0' sqlid='87gaftwrm2h68'
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1
END OF STMT
BINDS #140228855286880:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f8992eb7fe8 bln=22 avl=06 flg=05
value=4254951064
EXEC #140228855286880:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1072382624,tim=81822754993
FETCH #140228855286880:c=0,e=7,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=1072382624,tim=81822755037
CLOSE #140228855286880:c=0,e=1,dep=1,type=3,tim=81822755053
=====================
PARSING IN CURSOR #140228852025424 len=174 dep=1 uid=0 oct=1 lid=0 tim=81822755362 hv=3089950909 ad='ba5a6038' sqlid='gu6tc8yw2tu5x'
CREATE GLOBAL TEMPORARY T
END OF STMT
PARSE #140228852025424:c=0,e=287,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=81822755361
BINDS #140228859013352:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=80 off=0
kxsbbbfp=7f8992eb7fb0 bln=22 avl=02 flg=05
value=1
Bind#1
oacdty=01 mxl=32(25) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=178 siz=0 off=24
kxsbbbfp=7f8992eb7fc8 bln=32 avl=25 flg=01
value="SYS_TEMP_0FD9D6698_3729F2"
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=56
kxsbbbfp=7f8992eb7fe8 bln=22 avl=02 flg=01
value=1
EXEC #140228859013352:c=0,e=107,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=813480514,tim=81822755553
FETCH #140228859013352:c=0,e=9,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=4,plh=813480514,tim=81822755572
CLOSE #140228859013352:c=0,e=1,dep=2,type=3,tim=81822755598
BINDS #140228859013352:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=80 off=0
kxsbbbfp=7f8992eb7fb0 bln=22 avl=01 flg=05
value=0
Bind#1
oacdty=01 mxl=32(25) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=178 siz=0 off=24
kxsbbbfp=7f8992eb7fc8 bln=32 avl=25 flg=01
value="SYS_TEMP_0FD9D6698_3729F2"
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=56
kxsbbbfp=7f8992eb7fe8 bln=22 avl=02 flg=01
value=1
EXEC #140228859013352:c=1000,e=1324,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=813480514,tim=81822756968
FETCH #140228859013352:c=0,e=13,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=4,plh=813480514,tim=81822757000
STAT #140228859013352 id=1 cnt=0 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ (cr=3 pr=0 pw=0 time=15 us cost=4 size=103 card=1)'
STAT #140228859013352 id=2 cnt=0 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=13 us cost=3 size=0 card=1)'
CLOSE #140228859013352:c=0,e=2,dep=2,type=3,tim=81822757081
EXEC #140228852025424:c=2000,e=1756,p=0,cr=6,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=81822757159
CLOSE #140228852025424:c=0,e=2,dep=1,type=0,tim=81822757204
=====================
PARSING IN CURSOR #140228855134600 len=64 dep=0 uid=104 oct=3 lid=104 tim=81822757894 hv=3881241907 ad='b675b9a8' sqlid='czdhykbmpf39m'
with t1 as (select count(*) from t) select * from t1 t11, t1 t12
END OF STMT
PARSE #140228855134600:c=3000,e=3902,p=0,cr=11,cu=0,mis=1,r=0,dep=0,og=1,plh=3178380639,tim=81822757893
WAIT #140228855134600: nam='direct path read' ela= 76 file number=10 first dba=163 block cnt=13 obj#=91727 tim=81822758343
WAIT #140228855134600: nam='direct path read' ela= 82 file number=10 first dba=177 block cnt=15 obj#=91727 tim=81822758474
WAIT #140228855134600: nam='direct path read' ela= 34 file number=10 first dba=193 block cnt=15 obj#=91727 tim=81822758545
WAIT #140228855134600: nam='direct path read' ela= 22 file number=10 first dba=209 block cnt=15 obj#=91727 tim=81822758595
WAIT #140228855134600: nam='direct path read' ela= 24 file number=10 first dba=225 block cnt=15 obj#=91727 tim=81822758647
WAIT #140228855134600: nam='direct path read' ela= 24 file number=10 first dba=241 block cnt=15 obj#=91727 tim=81822758698
WAIT #140228855134600: nam='direct path read' ela= 24 file number=10 first dba=257 block cnt=15 obj#=91727 tim=81822758750
WAIT #140228855134600: nam='direct path read' ela= 23 file number=10 first dba=273 block cnt=15 obj#=91727 tim=81822758801
WAIT #140228855134600: nam='direct path read' ela= 683 file number=10 first dba=386 block cnt=126 obj#=91727 tim=81822759512
WAIT #140228855134600: nam='direct path read' ela= 663 file number=10 first dba=514 block cnt=126 obj#=91727 tim=81822760224
WAIT #140228855134600: nam='direct path read' ela= 197 file number=10 first dba=642 block cnt=126 obj#=91727 tim=81822760652
WAIT #140228855134600: nam='direct path read' ela= 236 file number=10 first dba=770 block cnt=126 obj#=91727 tim=81822761097
WAIT #140228855134600: nam='direct path read' ela= 204 file number=10 first dba=898 block cnt=126 obj#=91727 tim=81822761520
WAIT #140228855134600: nam='direct path read' ela= 200 file number=10 first dba=1026 block cnt=126 obj#=91727 tim=81822761926
WAIT #140228855134600: nam='direct path read' ela= 199 file number=10 first dba=1154 block cnt=126 obj#=91727 tim=81822762370
WAIT #140228855134600: nam='direct path read' ela= 193 file number=10 first dba=1282 block cnt=126 obj#=91727 tim=81822762784
WAIT #140228855134600: nam='direct path read' ela= 218 file number=10 first dba=1410 block cnt=126 obj#=91727 tim=81822763245
WAIT #140228855134600: nam='direct path read' ela= 195 file number=10 first dba=1538 block cnt=126 obj#=91727 tim=81822763681
WAIT #140228855134600: nam='direct path read' ela= 259 file number=10 first dba=1666 block cnt=126 obj#=91727 tim=81822764153
WAIT #140228855134600: nam='direct path read' ela= 40 file number=10 first dba=1794 block cnt=21 obj#=91727 tim=81822764412
WAIT #140228855134600: nam='direct path write temp' ela= 22 file number=203 first dba=264 block cnt=1 obj#=91727 tim=81822764960
EXEC #140228855134600:c=7999,e=7177,p=1525,cr=1528,cu=8,mis=0,r=0,dep=0,og=1,plh=3178380639,tim=81822765116
WAIT #140228855134600: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=91727 tim=81822765161
WAIT #140228855134600: nam='db file sequential read' ela= 9 file#=203 block#=264 blocks=1 obj#=-40016232 tim=81822765233
FETCH #140228855134600:c=0,e=103,p=1,cr=8,cu=1,mis=0,r=1,dep=0,og=1,plh=3178380639,tim=81822765283
WAIT #140228855134600: nam='SQL*Net message from client' ela= 149 driver id=1413697536 #bytes=1 p3=0 obj#=-40016232 tim=81822765680
FETCH #140228855134600:c=0,e=8,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3178380639,tim=81822765706
STAT #140228855134600 id=1 cnt=1 pid=0 pos=1 obj=0 op='TEMP TABLE TRANSFORMATION (cr=1536 pr=1526 pw=1 time=7259 us)'
STAT #140228855134600 id=2 cnt=0 pid=1 pos=1 obj=0 op='LOAD AS SELECT (cr=1528 pr=1525 pw=1 time=7030 us)'
STAT #140228855134600 id=3 cnt=1 pid=2 pos=1 obj=0 op='SORT AGGREGATE (cr=1528 pr=1525 pw=0 time=6535 us)'
STAT #140228855134600 id=4 cnt=90776 pid=3 pos=1 obj=91727 op='TABLE ACCESS FULL T (cr=1528 pr=1525 pw=0 time=2913 us cost=425 size=0 card=90776)'
STAT #140228855134600 id=5 cnt=1 pid=1 pos=2 obj=0 op='MERGE JOIN CARTESIAN (cr=8 pr=1 pw=0 time=101 us cost=4 size=26 card=1)'
STAT #140228855134600 id=6 cnt=1 pid=5 pos=1 obj=0 op='VIEW (cr=5 pr=1 pw=0 time=74 us cost=2 size=13 card=1)'
STAT #140228855134600 id=7 cnt=1 pid=6 pos=1 obj=4254951064 op='TABLE ACCESS FULL SYS_TEMP_0FD9D6698_3729F2 (cr=5 pr=1 pw=0 time=74 us cost=2 size=13 card=1)'
STAT #140228855134600 id=8 cnt=1 pid=5 pos=2 obj=0 op='BUFFER SORT (cr=3 pr=0 pw=0 time=24 us cost=4 size=13 card=1)'
STAT #140228855134600 id=9 cnt=1 pid=8 pos=1 obj=0 op='VIEW (cr=3 pr=0 pw=0 time=13 us cost=2 size=13 card=1)'
STAT #140228855134600 id=10 cnt=1 pid=9 pos=1 obj=4254951064 op='TABLE ACCESS FULL SYS_TEMP_0FD9D6698_3729F2 (cr=3 pr=0 pw=0 time=12 us cost=2 size=13 card=1)'
WAIT #140228855134600: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-40016232 tim=81822765859
历史上的今天...
>> 2011-06-24文章:
>> 2004-06-24文章:
By eygle on 2013-06-24 08:27 | Comments (0) | SQL.PLSQL | 3103 |