TKPROF: Release 10.2.0.3.0 - Production on Thu May 27 15:41:56 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. Trace file: rcc1_ora_10207954.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 ******************************************************************************** SELECT CCIT_CODE FROM GS_MAP_RCC_CCIT WHERE RCC_NUM=:B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 152132 17.10 139.27 0 0 0 0 Fetch 152132 0.61 3.03 0 304264 0 152132 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 304264 17.71 142.30 0 304264 0 152132 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 58 (recursive depth: 2) ******************************************************************************** SELECT USER FROM SYS.DUAL call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 152132 19.19 101.36 0 0 0 0 Fetch 152132 0.34 0.68 0 0 0 152132 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 304264 19.53 102.05 0 0 0 152132 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 2) ******************************************************************************** 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 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 2 9 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 2 9 0 1 Misses in library cache during parse: 0 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 2 0.00 0.00 gc current block 2-way 1 0.00 0.00 ******************************************************************************** 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 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 1 4 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 1 4 0 1 Misses in library cache during parse: 0 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID ICOL$ (cr=4 pr=1 pw=0 time=393 us) 1 INDEX RANGE SCAN I_ICOL1 (cr=3 pr=1 pw=0 time=388 us)(object id 40) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ library cache lock 1 0.00 0.00 db file sequential read 1 0.00 0.00 ******************************************************************************** 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 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 11 0.00 0.00 0 3 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.00 0.00 0 3 0 10 Misses in library cache during parse: 0 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) ******************************************************************************** select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 1 4 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.01 1 4 0 0 Misses in library cache during parse: 0 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 1 0.00 0.00 library cache lock 1 0.00 0.00 ******************************************************************************** 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 0 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 25 0.00 0.00 0 30 0 23 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 27 0.00 0.00 0 30 0 23 Misses in library cache during parse: 0 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) ******************************************************************************** select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 23 0.00 0.00 0 0 0 0 Fetch 46 0.00 0.00 1 115 0 23 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 70 0.00 0.00 1 115 0 23 Misses in library cache during parse: 0 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID CCOL$ (cr=5 pr=1 pw=0 time=6027 us) 1 INDEX RANGE SCAN I_CCOL1 (cr=4 pr=1 pw=0 time=6019 us)(object id 54) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 1 0.00 0.00 ******************************************************************************** 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 0 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 1 4 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 1 4 0 0 Misses in library cache during parse: 0 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 1 0.00 0.00 ******************************************************************************** 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 0 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 4 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 4 0 0 Misses in library cache during parse: 0 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) ******************************************************************************** update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6, cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 304 0.01 0.01 0 0 0 0 Execute 304 0.05 0.21 0 304 608 304 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 608 0.06 0.23 0 304 608 304 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 UPDATE SEQ$ (cr=1 pr=0 pw=0 time=162 us) 1 INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=11 us)(object id 102) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ library cache lock 3 0.00 0.00 row cache lock 10 0.00 0.00 gc current block 2-way 1 0.00 0.00 ******************************************************************************** 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 1 0.00 0.00 0 0 0 0 Execute 22 0.00 0.00 0 0 0 0 Fetch 22 0.00 0.01 2 66 0 22 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 45 0.00 0.01 2 66 0 22 Misses in library cache during parse: 0 Optimizer mode: RULE Parsing user id: SYS (recursive depth: 3) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=3 pr=2 pw=0 time=15583 us) 1 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=1 pw=0 time=9219 us)(object id 257) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 2 0.00 0.01 ******************************************************************************** select file# from file$ where ts#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 9 0.00 0.00 0 0 0 0 Execute 9 0.00 0.00 0 0 0 0 Fetch 90 0.00 0.00 0 171 0 81 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 108 0.00 0.00 0 171 0 81 Misses in library cache during parse: 0 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 8 TABLE ACCESS BY INDEX ROWID FILE$ (cr=17 pr=0 pw=0 time=35 us) 8 INDEX RANGE SCAN I_FILE2 (cr=9 pr=0 pw=0 time=47 us)(object id 42) ******************************************************************************** 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),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 5 0.02 0.00 0 0 0 0 Execute 5 0.00 0.03 4 66 5 5 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 10 0.02 0.04 4 66 5 5 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 UPDATE SEG$ (cr=6 pr=2 pw=0 time=13276 us) 1 TABLE ACCESS CLUSTER SEG$ (cr=6 pr=2 pw=0 time=13135 us) 1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=3 pr=1 pw=0 time=5688 us)(object id 9) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ row cache lock 12 0.00 0.00 gc cr grant 2-way 2 0.00 0.00 db file sequential read 4 0.00 0.01 gc current block 2-way 1 0.00 0.00 gc current grant busy 1 0.00 0.00 ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 0 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 635909 0.57 3546.72 gc current grant 2-way 337544 0.10 118.41 gc cr multi block request 166 0.00 0.07 db file scattered read 321 0.01 0.82 row cache lock 368 0.00 0.03 library cache lock 2 0.00 0.00 gc current grant busy 1 0.00 0.00 gc current multi block request 1164 0.00 0.21 enq: FB - contention 117 0.00 0.04 gc cr grant 2-way 7 0.00 0.00 log file switch completion 8 0.39 0.53 enq: HW - contention 14 0.04 0.05 enq: TT - contention 1 0.00 0.00 latch: KCL gc element parent latch 1 0.00 0.00 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 321 0.03 0.02 0 0 0 0 Execute 304638 36.34 240.89 4 370 613 309 Fetch 304468 0.95 3.75 8 304674 0 304425 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 609427 37.32 244.68 12 305044 613 304734 Misses in library cache during parse: 2 Misses in library cache during execute: 2 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 12 0.00 0.05 row cache lock 22 0.00 0.00 library cache lock 3 0.00 0.00 gc current block 2-way 3 0.00 0.00 gc current grant busy 1 0.00 0.00 gc cr grant 2-way 2 0.00 0.00 2 user SQL statements in session. 374 internal SQL statements in session. 376 SQL statements in session. ******************************************************************************** Trace file: rcc1_ora_10207954.trc Trace file compatibility: 10.01.00 Sort options: default 1 session in tracefile. 2 user SQL statements in trace file. 374 internal SQL statements in trace file. 376 SQL statements in trace file. 14 unique SQL statements in trace file. 2821692 lines in trace file. 4087 elapsed seconds in trace file.