TKPROF: Release 10.2.0.1.0 - Production on Sal Tem 17 11:54:24 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Trace file: xe_ora_3384_test_1.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 8' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.01 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.01 0 0 0 0 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 38 (TEST) 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 0.00 0.00 ******************************************************************************** BEGIN populate_sql_trace_test(p_loops => 5); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.14 0 15 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.14 0 15 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 38 (TEST) 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 0.00 0.00 ******************************************************************************** INSERT INTO SQL_TRACE_TEST (ID, DESCRIPTION) VALUES (:B1 , 'Description for ' || :B1 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 5 0.00 0.20 0 1 12 5 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.20 0 1 12 5 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 38 (TEST) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT MODE: ALL_ROWS ******************************************************************************** select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled from obj$ o,user$ u,trigger$ t where t.baseobject=:1 and t.obj#=o.obj# and o.owner#=u.user# and bitand(property,16)=0 and bitand(property,8)=0 order by o.obj# 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.00 0.00 0 1 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY (cr=1 pr=0 pw=0 time=85 us) 0 NESTED LOOPS (cr=1 pr=0 pw=0 time=56 us) 0 NESTED LOOPS (cr=1 pr=0 pw=0 time=51 us) 0 TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=1 pr=0 pw=0 time=46 us) 0 INDEX RANGE SCAN I_TRIGGER1 (cr=1 pr=0 pw=0 time=31 us)(object id 125) 0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us) 0 INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)(object id 36) 0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us) 0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us)(object id 11) ******************************************************************************** SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("SQL_TRACE_TEST") FULL("SQL_TRACE_TEST") NO_PARALLEL_INDEX("SQL_TRACE_TEST") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "SQL_TRACE_TEST" "SQL_TRACE_TEST") SAMPLESUB call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.04 0 0 0 0 Fetch 1 0.00 0.01 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.05 0 3 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 38 (TEST) (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=14483 us) 5 TABLE ACCESS FULL SQL_TRACE_TEST (cr=3 pr=0 pw=0 time=14443 us) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: HINT: ALL_ROWS 1 SORT (AGGREGATE) 5 TABLE ACCESS (FULL) OF 'SQL_TRACE_TEST' (TABLE) ******************************************************************************** SELECT COUNT(*) FROM SQL_TRACE_TEST call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.19 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.19 0 4 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 38 (TEST) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 0 SORT (AGGREGATE) 0 TABLE ACCESS (FULL) OF 'SQL_TRACE_TEST' (TABLE) ******************************************************************************** COMMIT call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.01 0 0 1 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.01 0 0 1 0 Misses in library cache during parse: 0 Parsing user id: 38 (TEST) (recursive depth: 1) ******************************************************************************** select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 3 0.03 0.18 0 0 0 0 Fetch 5 0.00 0.00 0 12 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 11 0.03 0.18 0 12 0 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID IDL_SB4$ (cr=2 pr=0 pw=0 time=38 us) 0 INDEX RANGE SCAN I_IDL_SB41 (cr=2 pr=0 pw=0 time=31 us)(object id 117) ******************************************************************************** select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 133 0.01 0.00 0 0 0 0 Fetch 133 0.01 0.01 0 395 0 127 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 268 0.03 0.01 0 395 0 127 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: RULE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=3 pr=0 pw=0 time=59 us) 1 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=38 us)(object id 257) ******************************************************************************** select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 105 0.00 0.00 0 0 0 0 Fetch 105 0.03 0.04 0 315 0 1661 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 211 0.03 0.05 0 315 0 1661 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: RULE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 2 SORT ORDER BY (cr=3 pr=0 pw=0 time=125 us) 2 TABLE ACCESS CLUSTER HISTGRM$ (cr=3 pr=0 pw=0 time=77 us) 1 INDEX UNIQUE SCAN I_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=25 us)(object id 252) ******************************************************************************** select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 3 0.01 0.01 0 0 0 0 Fetch 5 0.00 0.02 1 16 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 11 0.01 0.03 1 16 0 4 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID IDL_UB1$ (cr=2 pr=0 pw=0 time=35 us) 0 INDEX RANGE SCAN I_IDL_UB11 (cr=2 pr=0 pw=0 time=28 us)(object id 114) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 1 0.01 0.01 ******************************************************************************** select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 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 9 0.00 0.00 0 6 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID IDL_CHAR$ (cr=2 pr=0 pw=0 time=35 us) 0 INDEX RANGE SCAN I_IDL_CHAR1 (cr=2 pr=0 pw=0 time=27 us)(object id 115) ******************************************************************************** select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 3 0.01 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 6 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.01 0.00 0 6 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID IDL_UB2$ (cr=2 pr=0 pw=0 time=35 us) 0 INDEX RANGE SCAN I_IDL_UB21 (cr=2 pr=0 pw=0 time=28 us)(object id 116) ******************************************************************************** select user#,type# from user$ where name=: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.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=36 us) 1 INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=17 us)(object id 44) ******************************************************************************** select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 4 0.00 0.00 0 0 0 0 Fetch 4 0.00 0.00 0 12 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.00 0.00 0 12 0 4 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=53 us) 1 INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=33 us)(object id 37) ******************************************************************************** select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols, nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans, t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln, t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1), nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0), nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit, ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 2 0.04 0.03 0 0 0 0 Fetch 2 0.00 0.00 0 8 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.04 0.03 0 8 0 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS OUTER (cr=4 pr=0 pw=0 time=68 us) 1 TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=49 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=20 us)(object id 3) 0 TABLE ACCESS BY INDEX ROWID TAB_STATS$ (cr=1 pr=0 pw=0 time=9 us) 0 INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# (cr=1 pr=0 pw=0 time=6 us)(object id 709) ******************************************************************************** select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property, i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey, i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256), i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0), nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null, null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols, min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 2 0.00 0.05 0 0 0 0 Fetch 4 0.00 0.00 0 16 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.00 0.06 0 16 0 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT ORDER BY (cr=8 pr=0 pw=0 time=866 us) 1 HASH JOIN OUTER (cr=8 pr=0 pw=0 time=813 us) 1 NESTED LOOPS OUTER (cr=5 pr=0 pw=0 time=130 us) 1 TABLE ACCESS CLUSTER IND$ (cr=4 pr=0 pw=0 time=107 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=24 us)(object id 3) 0 TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=1 pr=0 pw=0 time=14 us) 0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=1 pr=0 pw=0 time=9 us)(object id 711) 0 VIEW (cr=3 pr=0 pw=0 time=112 us) 0 SORT GROUP BY (cr=3 pr=0 pw=0 time=108 us) 0 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=3 pr=0 pw=0 time=48 us) 3 INDEX RANGE SCAN I_CDEF2 (cr=2 pr=0 pw=0 time=29 us)(object id 51) ******************************************************************************** select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where 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.01 0 0 0 0 Fetch 5 0.00 0.00 0 10 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 8 0.00 0.01 0 10 0 3 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 2 TABLE ACCESS BY INDEX ROWID ICOL$ (cr=6 pr=0 pw=0 time=47 us) 2 INDEX RANGE SCAN I_ICOL1 (cr=4 pr=0 pw=0 time=35 us)(object id 40) ******************************************************************************** select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2, nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182, scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$, rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2, nvl(spare3,0) from col$ where obj#=:1 order by intcol# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 2 0.01 0.00 0 0 0 0 Fetch 8 0.00 0.00 0 6 0 6 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 11 0.01 0.00 0 6 0 6 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 3 SORT ORDER BY (cr=3 pr=0 pw=0 time=102 us) 3 TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=50 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=20 us)(object id 3) ******************************************************************************** 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 6 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.00 0.00 0 6 0 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=42 us) 1 INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=19 us)(object id 36) ******************************************************************************** select obj# from oid$ where user#=:1 and oid$=:2 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.00 0.00 0 2 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID OID$ (cr=2 pr=0 pw=0 time=26 us) 0 INDEX UNIQUE SCAN I_OID1 (cr=2 pr=0 pw=0 time=18 us)(object id 179) ******************************************************************************** select metadata from kopm$ where name='DB_FDO' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.00 0 2 0 1 Misses in library cache during parse: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID KOPM$ (cr=2 pr=0 pw=0 time=34 us) 1 INDEX UNIQUE SCAN I_KOPM1 (cr=1 pr=0 pw=0 time=17 us)(object id 365) ******************************************************************************** select audit$,options from procedure$ where obj#=: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.00 0.00 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 3 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID PROCEDURE$ (cr=3 pr=0 pw=0 time=36 us) 1 INDEX UNIQUE SCAN I_PROCEDURE1 (cr=2 pr=0 pw=0 time=20 us)(object id 109) ******************************************************************************** select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order# 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 5 0.00 0.00 0 14 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.00 0.00 0 14 0 4 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 4 SORT ORDER BY (cr=14 pr=0 pw=0 time=198 us) 4 NESTED LOOPS OUTER (cr=14 pr=0 pw=0 time=176 us) 4 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=4 pr=0 pw=0 time=73 us) 4 INDEX RANGE SCAN I_DEPENDENCY1 (cr=2 pr=0 pw=0 time=35 us)(object id 122) 4 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=10 pr=0 pw=0 time=76 us) 4 INDEX UNIQUE SCAN I_OBJ1 (cr=6 pr=0 pw=0 time=40 us)(object id 36) ******************************************************************************** select order#,columns,types from access$ where d_obj#=: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 4 0.00 0.00 0 8 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 8 0 3 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 3 TABLE ACCESS BY INDEX ROWID ACCESS$ (cr=8 pr=0 pw=0 time=47 us) 3 INDEX RANGE SCAN I_ACCESS1 (cr=5 pr=0 pw=0 time=43 us)(object id 124) ******************************************************************************** select * from dual 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.01 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.01 0 3 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 38 (TEST) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=12529 us) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 1 TABLE ACCESS MODE: ANALYZED (FULL) OF 'DUAL' (TABLE) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 0.02 0.03 ******************************************************************************** alter session set events '10046 trace name context off' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.02 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 2 0.00 0.02 0 0 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 38 (TEST) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.02 0 0 0 0 Execute 4 0.00 0.15 0 15 0 1 Fetch 2 0.00 0.01 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.00 0.19 0 18 0 2 Misses in library cache during parse: 3 Misses in library cache during execute: 1 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 7 0.00 0.00 SQL*Net message from client 7 0.02 0.04 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 32 0.01 0.22 0 1 0 0 Execute 279 0.14 0.61 0 1 13 5 Fetch 295 0.04 0.09 1 846 0 1825 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 606 0.20 0.93 1 848 13 1830 Misses in library cache during parse: 22 Misses in library cache during execute: 20 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 1 0.01 0.01 8 user SQL statements in session. 271 internal SQL statements in session. 279 SQL statements in session. 4 statements EXPLAINed in this session. ******************************************************************************** Trace file: xe_ora_3384_test_1.trc Trace file compatibility: 10.01.00 Sort options: default 1 session in tracefile. 8 user SQL statements in trace file. 271 internal SQL statements in trace file. 279 SQL statements in trace file. 27 unique SQL statements in trace file. 4 SQL statements EXPLAINed using schema: TEST.prof$plan_table Default table was used. Table was created. Table was dropped. 2460 lines in trace file. 1 elapsed seconds in trace file.