TKPROF: Release 10.2.0.1.0 - Production on Mon Oct 26 10:59:48 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Trace file: prodpzee_ora_2237.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 SQL_TRACE = TRUE 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: 54 (VLINK) ******************************************************************************** update object_usage set flags=flags+1 where obj#=:1 and bitand(flags, 1)=0 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 3 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 3 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 UPDATE OBJECT_USAGE (cr=3 pr=0 pw=0 time=41 us) 0 TABLE ACCESS FULL OBJECT_USAGE (cr=3 pr=0 pw=0 time=36 us) ******************************************************************************** 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 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 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=1 pr=0 pw=0 time=26 us) 0 INDEX RANGE SCAN I_CDEF3 (cr=1 pr=0 pw=0 time=22 us)(object id 52) ******************************************************************************** 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 1 0.00 0.00 0 0 0 0 Fetch 3 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 ------- --------------------------------------------------- 2 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=6 pr=0 pw=0 time=38 us) 2 INDEX RANGE SCAN I_CDEF2 (cr=4 pr=0 pw=0 time=34 us)(object id 51) ******************************************************************************** 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 5 0.00 0.00 0 0 0 0 Fetch 5 0.00 0.00 0 14 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 11 0.00 0.00 0 14 0 4 Misses in library cache during parse: 1 Misses in library cache during execute: 1 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=0 pw=0 time=49 us) 1 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=26 us)(object id 257) ******************************************************************************** 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 2 0.00 0.00 0 0 0 0 Fetch 4 0.00 0.00 1 8 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.00 0.00 1 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 TABLE ACCESS BY INDEX ROWID CCOL$ (cr=4 pr=1 pw=0 time=8270 us) 1 INDEX RANGE SCAN I_CCOL1 (cr=3 pr=1 pw=0 time=8247 us)(object id 54) ******************************************************************************** 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 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: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT GROUP BY (cr=2 pr=0 pw=0 time=51 us) 0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=0 pw=0 time=25 us) 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=23 us)(object id 103) ******************************************************************************** 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.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: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT GROUP BY (cr=2 pr=0 pw=0 time=16 us) 0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=0 pw=0 time=8 us) 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=7 us)(object id 103) ******************************************************************************** update pin_type set status='U' where amount=10 and status='N' and rownum=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 21 11 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 21 11 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 54 (VLINK) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE PIN_TYPE (cr=21 pr=0 pw=0 time=718 us) 1 COUNT STOPKEY (cr=11 pr=0 pw=0 time=169 us) 1 BITMAP CONVERSION TO ROWIDS (cr=11 pr=0 pw=0 time=165 us) 1 BITMAP INDEX SINGLE VALUE IDX_AMOUNT_STATUS (cr=11 pr=0 pw=0 time=146 us)(object id 51293) Rows Execution Plan ------- --------------------------------------------------- 0 UPDATE STATEMENT MODE: ALL_ROWS 1 UPDATE OF 'PIN_TYPE' 1 COUNT (STOPKEY) 1 BITMAP CONVERSION (TO ROWIDS) 1 BITMAP INDEX (SINGLE VALUE) OF 'IDX_AMOUNT_STATUS' (INDEX (BITMAP)) ******************************************************************************** ALTER SESSION SET SQL_TRACE = FALSE 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 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 54 (VLINK) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 3 0.00 0.01 0 21 11 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.00 0.01 0 21 11 1 Misses in library cache during parse: 2 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 7 0.00 0.00 0 0 0 0 Execute 12 0.00 0.00 0 3 0 0 Fetch 15 0.00 0.00 1 33 0 8 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 34 0.01 0.01 1 36 0 8 Misses in library cache during parse: 7 Misses in library cache during execute: 7 3 user SQL statements in session. 12 internal SQL statements in session. 15 SQL statements in session. 1 statement EXPLAINed in this session. ******************************************************************************** Trace file: prodpzee_ora_2237.trc Trace file compatibility: 10.01.00 Sort options: default 1 session in tracefile. 3 user SQL statements in trace file. 12 internal SQL statements in trace file. 15 SQL statements in trace file. 10 unique SQL statements in trace file. 1 SQL statements EXPLAINed using schema: VLINK.prof$plan_table Default table was used. Table was created. Table was dropped. 149 lines in trace file. 25 elapsed seconds in trace file.