aix
oracle 10.2.0.4
drop一张表大概需要5s左右
生成10046trace文件的问题sql
PARSING IN CURSOR #6 len=577 dep=1 uid=0 oct=3 lid=0 tim=39332565415208 hv=3360872067 ad='aaf1cad8'
select u2.name, o2.name from ind$ i, obj$ o1, obj$ o2, user$ u1, user$ u2 where i.bo# = o1.obj# and o1.owner#=u1.user# and i.obj#=o2.obj# and o2.owner#=u2.user# and u1.name=:1 and o1.name=:2 and i.type#=9 union select u4.name, o4.name from ind$ i1, ntab$ n, obj$ o3, obj$ o4, user$ u3, user$ u4 where i1.bo#=n.ntab# and n.ntab# in (select n1.ntab# from ntab$ n1 start with n1.obj#=o3.obj# connect by prior n1.ntab# = n1.obj#) and o3.owner#=u3.user# and i1.obj#=o4.obj# and o4.owner#=u4.user# and u3.name=:1 and o3.name=:2 and i1.type#=9
END OF STMT
PARSE #6:c=0,e=2477,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=39332565415206
EXEC #6:c=0,e=3079,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=39332565418352
FETCH #6:c=4840000,e=7178892,p=0,cr=929355,cu=0,mis=0,r=0,dep=1,og=4,tim=39332572597290
STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT UNIQUE (cr=929355 pr=0 pw=0 time=7178902 us)'
STAT #6 id=2 cnt=0 pid=1 pos=1 obj=0 op='UNION-ALL (cr=929355 pr=0 pw=0 time=7178883 us)'
省略。。。
执行计划如下:
PLAN_TABLE_OUTPUT
1 SQL_ID 7ayfw9g455pn3
2 --------------------
3 select u2.name, o2.name from ind$ i, obj$ o1, obj$ o2, user$ u1,
4 user$ u2 where i.bo# = o1.obj# and o1.owner#=u1.user# and
5 i.obj#=o2.obj# and o2.owner#=u2.user# and u1.name=:1 and o1.name=:2
6 and i.type#=9 union select u4.name, o4.name from ind$ i1, ntab$ n,
7 obj$ o3, obj$ o4, user$ u3, user$ u4 where i1.bo#=n.ntab# and
8 n.ntab# in (select n1.ntab# from ntab$ n1 start with n1.obj#=o3.obj#
9 connect by prior n1.ntab