eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 东北大学、西财、兰州商学院-2013 ACOUG高校行琐记 | Blog首页 | Oracle Database 12c 正式发布-多租户特性嵌入云计算 »

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
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文章:
           Statspack之三-安装statspack
           如何在Oracle中发送Email
           Statspack之一-Statspack简介

无觅

By eygle on 2013-06-24 08:27 | Comments (0) | SQL.PLSQL | 3103 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com