今天做一个数据库的优化项目，主要是清理业务产生的垃圾数据，在优化的过程中，发现很多要清理的表都落在了DMT上了，应此在truncate 一些业务表的时候，导致了enqueue ST的所，于是就想到了：使用dbms_space_admin.tablespace_migrate_to_local这个存储过程了，由于闲着没事情，就想搞清楚ORACLE 在这个过程中到底做了哪些操作，对其做了10046 event的跟踪：
$ cat orcl_ora_27596.txt
TKPROF: Release 9.2.0.4.0 - Production on Sat Jul 12 22:55:07 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: orcl_ora_27596.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set events '10046 trace name context forever,level 12'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 205.69 205.69
********************************************************************************
BEGIN sys.dbms_space_admin.tablespace_migrate_to_local('TOOLS'); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 2.31 3.56 208 228 1004 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.33 3.58 208 228 1004 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
local write wait 16 0.03 0.15
db file sequential read 208 0.02 0.69
SQL*Net message to client 1 0.00 0.00
********************************************************************************
查出在该表空间上的cluster 已经使用的文件及相关的块数。
select file#, block#
from
seg$ where type# = 3 and ts# = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.00 0 225 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 225 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS CLUSTER SEG$ (cr=225 r=0 w=0 time=2983 us)
210 INDEX RANGE SCAN I_FILE#_BLOCK# (cr=5 r=0 w=0 time=481 us)(object id 9)
********************************************************************************
tools 表空间中已经能够被回收的extent的起点块号码。
select file#, block#
from
fet$ where ts#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 966 0.01 0.01 0 976 0 965
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 968 0.01 0.01 0 976 0 965
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
965 TABLE ACCESS CLUSTER FET$ (cr=976 r=0 w=0 time=10763 us)
1 INDEX UNIQUE SCAN I_TS# (cr=1 r=0 w=0 time=41 us)(object id 7)
********************************************************************************
查找能够合并的extent
select length
from
fet$ where file#=:1 and block#=:2 and ts#=:3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 978 0.01 0.03 0 0 0 0
Execute 978 0.55 0.56 0 0 0 0
Fetch 978 0.48 0.43 0 8956 0 965
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2934 1.04 1.04 0 8956 0 965
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS CLUSTER FET$ (cr=2 r=0 w=0 time=49 us)
1 INDEX UNIQUE SCAN I_TS# (cr=1 r=0 w=0 time=19 us)(object id 7)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch free 3 0.00 0.00
********************************************************************************
合并TOOLS 表空间的碎片，首先在fet$删除一行，将其和临近的碎片进行合并（后面的update操作）
delete from fet$
where
file#=:1 and block#=:2 and ts#=:3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 954 0.07 0.03 0 0 0 0
Execute 954 1.38 1.40 0 12408 1023 954
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1908 1.45 1.44 0 12408 1023 954
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE (cr=14 r=0 w=0 time=2518 us)
1 TABLE ACCESS CLUSTER FET$ (cr=14 r=0 w=0 time=1635 us)
1 INDEX UNIQUE SCAN I_TS# (cr=1 r=0 w=0 time=24 us)(object id 7)
********************************************************************************
update fet$ set length=:4
where
file#=:1 and block#=:2 and ts# = :3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 7 0.03 0.01 0 91 7 7
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.03 0.01 0 91 7 7
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE (cr=13 r=0 w=0 time=1669 us)
1 TABLE ACCESS CLUSTER FET$ (cr=13 r=0 w=0 time=1085 us)
1 INDEX UNIQUE SCAN I_TS# (cr=1 r=0 w=0 time=27 us)(object id 7)
********************************************************************************
查看个TOOLS表空间上的所有所有extent大大小（包括已经使用的和未被使用的extent)
select distinct(length)
from
fet$ f1 where ts# = :1 and block# f1.file# = f2.file# and f1.ts# = :1) union all select distinct(length)
from uet$ where ts#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 17 0.02 0.01 0 656 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 0.02 0.01 0 656 0 16
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
16 UNION-ALL (cr=656 r=0 w=0 time=16286 us)
11 SORT UNIQUE (cr=431 r=0 w=0 time=13312 us)
11 FILTER (cr=431 r=0 w=0 time=13137 us)
12 TABLE ACCESS CLUSTER FET$ (cr=23 r=0 w=0 time=354 us)
1 INDEX UNIQUE SCAN I_TS# (cr=1 r=0 w=0 time=27 us)(object id 7)
1 SORT AGGREGATE (cr=408 r=0 w=0 time=12583 us)
12 FILTER (cr=408 r=0 w=0 time=12537 us)
12 TABLE ACCESS FULL FET$ (cr=408 r=0 w=0 time=12527 us)
5 SORT UNIQUE (cr=225 r=0 w=0 time=2815 us)
281 TABLE ACCESS CLUSTER UET$ (cr=225 r=0 w=0 time=2528 us)
210 INDEX RANGE SCAN I_FILE#_BLOCK# (cr=5 r=0 w=0 time=301 us)(object id 9)
********************************************************************************
select f.relfile# header_file, s.block# header_block, o.dataobj#
segment_objd
from
sys.user$ u, sys.obj$ o, sys.ts$ ts, ( select 2 object_type_id ,
5 segment_type_id , t.obj# object_id , t.file# header_file,
t.block# header_block, t.ts# ts_number from sys.tab$
t where bitand(t.property, 1024) = 0 union
all select 19, 5, tp.obj#, tp.file#, tp.block#, tp.ts#
from sys.tabpart$ tp union all select 3, 5, c.obj#, c.file#,
c.block#, c.ts# from sys.clu$ c union all
select 1, 6, i.obj#, i.file#, i.block#, i.ts# from sys.ind$ i
where i.type# in (1, 2, 3, 4, 6, 7, 8, 9) union all
select 20, 6, ip.obj#, ip.file#, ip.block#, ip.ts# from
sys.indpart$ ip union all select 21, 8, l.lobj#, l.file#,
l.block#, l.ts# from sys.lob$ l union all select
34, 5, tsp.obj#, tsp.file#, tsp.block#, tsp.ts# from
sys.tabsubpart$ tsp union all select 35, 6,
isp.obj#, isp.file#, isp.block#, isp.ts# from sys.indsubpart$
isp union all select decode(lf.fragtype$, 'P', 40, 41), 8,
lf.fragobj#, lf.file#, lf.block#, lf.ts# from
sys.lobfrag$ lf ) so, sys.seg$ s, sys.file$ f where s.file# =
so.header_file and s.block# = so.header_block and s.ts# = :1
and s.ts# = so.ts_number and s.ts# = ts.ts# and o.obj# =
so.object_id and o.owner# = u.user# and s.type# =
so.segment_type_id and o.type# = so.object_type_id and s.ts# =
f.ts# and s.file# = f.relfile# union all select f.relfile#,
s.block#, un.us# from sys.user$ u, sys.ts$ ts, sys.undo$ un, sys.seg$ s,
sys.file$ f where s.file# = un.file# and s.block# = un.block#
and s.ts# = :1 and s.ts# = un.ts# and s.ts# = ts.ts#
and s.user# = u.user# and s.type# = 1 and un.status$ != 1
and un.ts# = f.ts# and un.file# = f.relfile# union all select
f.relfile#, s.block#, s.hwmincr from sys.user$ u, sys.ts$ ts, sys.seg$
s, sys.file$ f where s.ts# = :1 and s.ts# = ts.ts# and
s.user# = u.user# and s.type# not in (1, 5, 6, 8, 9) and s.ts# =
f.ts# and s.file# = f.relfile#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.02 0.01 0 0 0 0
Execute 2 0.02 0.02 0 0 0 0
Fetch 422 1.84 1.77 0 256320 0 420
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 426 1.88 1.81 0 256320 0 420
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
210 UNION-ALL (cr=128574 r=0 w=0 time=909666 us)
210 NESTED LOOPS (cr=128290 r=0 w=0 time=905018 us)
210 NESTED LOOPS (cr=127660 r=0 w=0 time=894424 us)
210 NESTED LOOPS (cr=125140 r=0 w=0 time=872640 us)
210 NESTED LOOPS (cr=124930 r=0 w=0 time=868999 us)
22426 NESTED LOOPS (cr=79436 r=0 w=0 time=627790 us)
22426 VIEW (cr=11878 r=0 w=0 time=213438 us)
22426 UNION-ALL (cr=11878 r=0 w=0 time=193929 us)
5416 TABLE ACCESS FULL TAB$ (cr=2715 r=0 w=0 time=49676 us)
3848 TABLE ACCESS FULL TABPART$ (cr=378 r=0 w=0 time=12824 us)
10 TABLE ACCESS FULL CLU$ (cr=2655 r=0 w=0 time=15405 us)
5872 TABLE ACCESS FULL IND$ (cr=2725 r=0 w=0 time=35463 us)
7193 TABLE ACCESS FULL INDPART$ (cr=739 r=0 w=0 time=24055 us)
87 TABLE ACCESS FULL LOB$ (cr=2657 r=0 w=0 time=15878 us)
0 TABLE ACCESS FULL TABSUBPART$ (cr=3 r=0 w=0 time=55 us)
0 TABLE ACCESS FULL INDSUBPART$ (cr=3 r=0 w=0 time=39 us)
0 TABLE ACCESS FULL LOBFRAG$ (cr=3 r=0 w=0 time=30 us)
22426 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=67558 r=0 w=0 time=348201 us)
22426 INDEX UNIQUE SCAN I_OBJ1 (cr=45084 r=0 w=0 time=187917 us)(object id 33)
210 TABLE ACCESS CLUSTER SEG$ (cr=45494 r=0 w=0 time=173972 us)
210 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=45064 r=0 w=0 time=145055 us)(object id 9)
210 INDEX UNIQUE SCAN I_FILE2 (cr=210 r=0 w=0 time=2097 us)(object id 39)
210 TABLE ACCESS CLUSTER TS$ (cr=2520 r=0 w=0 time=20459 us)
210 INDEX UNIQUE SCAN I_TS# (cr=210 r=0 w=0 time=2127 us)(object id 7)
210 TABLE ACCESS CLUSTER USER$ (cr=630 r=0 w=0 time=8722 us)
210 INDEX UNIQUE SCAN I_USER# (cr=210 r=0 w=0 time=2121 us)(object id 11)
0 NESTED LOOPS (cr=59 r=0 w=0 time=527 us)
0 NESTED LOOPS (cr=59 r=0 w=0 time=524 us)
0 NESTED LOOPS (cr=59 r=0 w=0 time=523 us)
26 NESTED LOOPS (cr=5 r=0 w=0 time=253 us)
26 TABLE ACCESS FULL UNDO$ (cr=3 r=0 w=0 time=102 us)
26 INDEX UNIQUE SCAN I_FILE2 (cr=2 r=0 w=0 time=78 us)(object id 39)
0 TABLE ACCESS CLUSTER SEG$ (cr=54 r=0 w=0 time=193 us)
0 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=54 r=0 w=0 time=168 us)(object id 9)
0 TABLE ACCESS CLUSTER TS$ (cr=0 r=0 w=0 time=0 us)
0 INDEX UNIQUE SCAN I_TS# (cr=0 r=0 w=0 time=0 us)(object id 7)
0 TABLE ACCESS CLUSTER USER$ (cr=0 r=0 w=0 time=0 us)
0 INDEX UNIQUE SCAN I_USER# (cr=0 r=0 w=0 time=0 us)(object id 11)
0 NESTED LOOPS (cr=225 r=0 w=0 time=2354 us)
0 NESTED LOOPS (cr=225 r=0 w=0 time=2352 us)
0 NESTED LOOPS (cr=225 r=0 w=0 time=2351 us)
0 TABLE ACCESS CLUSTER SEG$ (cr=225 r=0 w=0 time=2350 us)
210 INDEX RANGE SCAN I_FILE#_BLOCK# (cr=5 r=0 w=0 time=249 us)(object id 9)
0 INDEX UNIQUE SCAN I_FILE2 (cr=0 r=0 w=0 time=0 us)(object id 39)
0 TABLE ACCESS CLUSTER TS$ (cr=0 r=0 w=0 time=0 us)
0 INDEX UNIQUE SCAN I_TS# (cr=0 r=0 w=0 time=0 us)(object id 7)
0 TABLE ACCESS CLUSTER USER$ (cr=0 r=0 w=0 time=0 us)
0 INDEX UNIQUE SCAN I_USER# (cr=0 r=0 w=0 time=0 us)(object id 11)
********************************************************************************
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,
NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0)
from
seg$ where ts#=:1 and file#=:2 and block#=:3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 197 0.00 0.01 0 0 0 0
Execute 197 0.13 0.13 0 0 0 0
Fetch 197 0.02 0.01 0 793 0 196
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 591 0.15 0.15 0 793 0 196
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS CLUSTER SEG$ (cr=4 r=0 w=0 time=68 us)
1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=3 r=0 w=0 time=30 us)(object id 9)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch free 4 0.00 0.00
********************************************************************************
select con#,obj#,rcon#,enabled,nvl(defer,0)
from
cdef$ where robj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 6 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 6 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
********************************************************************************
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
rowid,cols,nvl(defer,0),mtime,nvl(spare1,0)
from
cdef$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 41 0.01 0.00 0 47 0 38
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 45 0.01 0.00 0 47 0 38
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
********************************************************************************
select intcol#,nvl(pos#,0),col#
from
ccol$ where con#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 38 0.01 0.01 0 0 0 0
Fetch 76 0.00 0.00 0 152 0 38
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 115 0.01 0.01 0 152 0 38
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
********************************************************************************
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 6 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 6 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
********************************************************************************
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by
grantee#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 8 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.01 0.00 0 8 0 2
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
********************************************************************************
update ts$ set name=:2,online$=:3,contents$=:4,undofile#=:5,undoblock#=:6,
blocksize=:7,dflmaxext=:8,dflinit=:9,dflincr=:10,dflextpct=:11,dflminext=
:12,dflminlen=:13,owner#=:14,scnwrp=:15,scnbas=:16,pitrscnwrp=:17,
pitrscnbas=:18,dflogging=:19,bitmapped=:20,inc#=:21,flags=:22,plugged=:23,
spare1=:24,spare2=:25
where
ts#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.02 0.02 0 0 0 0
Execute 2 0.00 0.00 0 26 5 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.03 0 26 5 2
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE (cr=13 r=0 w=0 time=778 us)
1 TABLE ACCESS CLUSTER TS$ (cr=13 r=0 w=0 time=340 us)
1 INDEX UNIQUE SCAN I_TS# (cr=1 r=0 w=0 time=26 us)(object id 7)
********************************************************************************
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=
:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),
groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=
DECODE(:17,0,NULL,:17)
where
ts#=:1 and file#=:2 and block#=:3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 421 0.06 0.03 0 0 0 0
Execute 421 1.24 1.37 0 2554 872 421
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 842 1.30 1.40 0 2554 872 421
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE (cr=6 r=0 w=0 time=939 us)
1 TABLE ACCESS CLUSTER SEG$ (cr=6 r=0 w=0 time=279 us)
1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=3 r=0 w=0 time=35 us)(object id 9)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch free 3 0.00 0.00
buffer busy waits 1 0.00 0.00
********************************************************************************
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,
o.dataobj#,o.flags
from
obj$ o where o.obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 8 0 2
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
********************************************************************************
select file#
from
file$ where ts#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID FILE$ (cr=3 r=0 w=0 time=70 us)
1 INDEX RANGE SCAN I_FILE2 (cr=2 r=0 w=0 time=44 us)(object id 39)
********************************************************************************
select blocks,NVL(ts#,-1),status$,NVL(relfile#,0),maxextend,inc, crscnwrp,
crscnbas,NVL(spare1,0)
from
file$ where file#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1&n
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/48620/viewspace-399607/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/48620/viewspace-399607/
114
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
