What happens behind the scene during a complete refresh ----------------------------------------------------------------- A lot of codes to go through. Note the followings: 1. truncate table "JYANG"."MV_MVTEST2" purge snapshot log 2. INSERT /*+ APPEND */ INTO "JYANG"."MV_MVTEST2"("COUNT(*)","C1","COUNT(C2)", "SUM(C2)") SELECT COUNT(*),"MVTEST2"."C1",COUNT("MVTEST2"."C2"), SUM("MVTEST2"."C2") FROM "MVTEST2" "MVTEST2" GROUP BY "MVTEST2"."C1" 3. ALTER SUMMARY "JYANG"."MV_MVTEST2" COMPILE 4. delete from "JYANG"."MLOG$_MVTEST2" where snaptime$$ <= :1 TKPROF: Release 9.2.0.1.0 - Production on Sat Jun 7 11:05:04 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Trace file: athena_ora_268.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.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: 61 ******************************************************************************** BEGIN dbms_mview.refresh('MV_MVTEST2','c'); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.04 0.30 1 24 25 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.06 0.31 1 24 25 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 ******************************************************************************** SELECT user from sys.dual 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 1 0.00 0.00 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 3 0 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) ******************************************************************************** SELECT 1 FROM SYS.SNAP$ WHERE SOWNER = :b1 AND VNAME = :b2 AND INSTSITE = 0 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.01 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 1 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.00 0 1 0 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) ******************************************************************************** SELECT SUBSTR(MLINK,2),TNAME,USLOG,SUBSTR(AUTO_FAST,1,1),BITAND(FLAG,:b1) FROM SYS.SNAP$ WHERE SOWNER = :b2 AND VNAME = :b3 AND INSTSITE = 0 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 1 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 2 0 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) ******************************************************************************** SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :b1 FOR UPDATE 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 2 2 0 Fetch 1 0.00 0.00 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 2 2 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) ******************************************************************************** UPDATE DBMS_LOCK_ALLOCATED SET EXPIRATION=SYSDATE + (:b1 / 86400 ) WHERE ROWID = :b2 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 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.00 0 0 1 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) ******************************************************************************** SELECT NVL(DISABLED,'F'),FLAG FROM SYSTEM.DEF$_DESTINATION WHERE DBLINK = NLS_UPPER(:b1) AND CATCHUP = :b2 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 1 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 2 0 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) ******************************************************************************** select last_delivered, last_enq_tid, last_seq, disabled, rowid, decode(apply_init, NULL, 1, 0), NVL(flag, '00000000') from system.def$_destination where dblink = :1 and catchup = :2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 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 goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID DEF$_DESTINATION 1 INDEX UNIQUE SCAN DEF$_DESTINATION_PRIMARY (object id 3883) ******************************************************************************** update system.def$_destination set last_delivered = :1, last_enq_tid = :2, last_seq = :3, last_txn_count = :4, total_txn_count = total_txn_count + :5, total_prop_time_throughput = total_prop_time_throughput + :6, total_prop_time_latency = total_prop_time_latency + :7, to_communication_size = to_communication_size + :8, from_communication_size = from_communication_size + :9, spare1 = spare1 + :10, spare2 = spare2 + :11, spare3 = spare3 + :12, spare4 = spare4 + :13, last_error_number = :14, last_error_message = :15 where rowid = :16 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.14 0 0 0 0 Execute 1 0.00 0.00 0 1 2 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.15 0 1 2 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 TABLE ACCESS BY USER ROWID DEF$_DESTINATION ******************************************************************************** select disabled, NVL(flag, '00000000') from system.def$_destination where rowid = :1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.01 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 2 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.01 0.00 0 2 0 2 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 USER ROWID DEF$_DESTINATION ******************************************************************************** select last_delivered, last_enq_tid, rowid from system.def$_destination dd where (dd.dblink != :1) and ( exists ( select 1 from system.def$_calldest cd where cd.dblink = dd.dblink and cd.catchup = dd.catchup) or exists ( select 1 from system.repcat$_repprop rp where rp.dblink = dd.dblink and rp.how = 1 and rp.extension_id = dd.catchup)) 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 10 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.00 0.00 0 10 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 FILTER 2 TABLE ACCESS FULL DEF$_DESTINATION 0 INDEX RANGE SCAN DEF$_CALLDEST_N2 (object id 3886) 0 INDEX RANGE SCAN REPCAT$_REPPROP_DBLINK_HOW (object id 5162) ******************************************************************************** select last_delivered, last_enq_tid from system.def$_destination 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 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.00 0.00 0 4 0 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 2 TABLE ACCESS FULL DEF$_DESTINATION ******************************************************************************** select enq_tid from system.def$_aqcall where (cscn < :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 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 INDEX RANGE SCAN DEF$_TRANORDER (object id 3870) ******************************************************************************** delete from system.def$_lob where (enq_tid = :1) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 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 1 0.00 0.00 0 0 0 0 Misses in library cache during parse: 1 Parsing user id: SYS (recursive depth: 1) ******************************************************************************** delete from system.def$_calldest where (enq_tid = :1) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.17 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 1 0.00 0.17 0 0 0 0 Misses in library cache during parse: 1 Parsing user id: SYS (recursive depth: 1) ******************************************************************************** delete from system.def$_aqcall where (enq_tid = :1) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 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 1 0.00 0.00 0 0 0 0 Misses in library cache during parse: 1 Parsing user id: SYS (recursive depth: 1) ******************************************************************************** update system.def$_destination set last_delivered = :1, last_enq_tid = :2, last_seq = :3, last_txn_count = :4, total_txn_count = total_txn_count + :5, total_prop_time_throughput = total_prop_time_throughput + :6, total_prop_time_latency = total_prop_time_latency + :7, to_communication_size = to_communication_size + :8, from_communication_size = from_communication_size + :9, spare1 = spare1 + :10, spare2 = spare2 + :11, spare3 = spare3 + :12, spare4 = spare4 + :13 where rowid = :14 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 1 2 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.00 0 1 2 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 TABLE ACCESS BY USER ROWID DEF$_DESTINATION ******************************************************************************** SET CONSTRAINTS ALL DEFERRED 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 goal: CHOOSE Parsing user id: 61 (recursive depth: 1) ******************************************************************************** SELECT tname, snapid, flag, flag2, status, master_version, tables, lobmaskvec, query_len, mas_roll_seg, mlink, snapshot, auto_fun, uslog, NVL(rscn, 0), refhnt, objflag, auto_fast, sna_type_oid, sna_type_hashcode, sna_type_owner, sna_type_name, mas_type_oid, mas_type_hashcode, mas_type_owner, mas_type_name FROM sys.snap$ WHERE sowner = :1 and vname = :2 and instsite = :3 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.06 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.06 0 2 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 SNAP$ 1 INDEX UNIQUE SCAN I_SNAP1 (object id 181) ******************************************************************************** SELECT masobj#, masflag, mowner, master, lastsuccess, snaptime, sysdate, loadertime, sysdate, NVL(refscn, 0), fcmaskvec, ejmaskvec, sub_handle, change_view FROM sys.snap_reftime$ WHERE sowner = :1 AND vname = :2 AND instsite = :3 ORDER BY tablenum call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.09 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.09 0 2 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT ORDER BY 1 TABLE ACCESS BY INDEX ROWID SNAP_REFTIME$ 1 INDEX RANGE SCAN I_SNAP_REFTIME1 (object id 185) ******************************************************************************** update sys.sumpartlog$ s set s.timestamp = :1, s.scn = :2 where s.bo# = :3 and s.timestamp >= to_date('4000-01-01:00:00:00', 'YYYY-MM-DD:HH24:MI:SS') 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 1 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE 0 TABLE ACCESS BY INDEX ROWID SUMPARTLOG$ 0 INDEX RANGE SCAN I_SUMPARTLOG$_BOPART$ (object id 161) ******************************************************************************** select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 6 0.00 0.00 0 0 0 0 Execute 6 0.00 0.00 0 0 0 0 Fetch 6 0.00 0.00 0 18 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 18 0.00 0.00 0 18 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 FULL CDC_CHANGE_TABLES$ ******************************************************************************** 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 13 0.00 0.00 0 0 0 0 Fetch 13 0.00 0.00 0 39 0 13 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 27 0.00 0.00 0 39 0 13 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) ******************************************************************************** select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn from sys.mlog$ where mowner = :1 and master = :2 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 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.00 0.00 0 6 0 3 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 MLOG$ 1 INDEX UNIQUE SCAN I_MLOG# (object id 169) ******************************************************************************** select log, oldest, oldest_pk, oldest_oid, oldest_new, youngest+1/86400, flag, yscn from sys.mlog$ where mowner = :1 and master = :2 for update call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.01 0.07 0 7 3 0 Fetch 2 0.00 0.00 0 6 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.01 0.07 0 13 3 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 FOR UPDATE 2 TABLE ACCESS CLUSTER MLOG$ 2 INDEX UNIQUE SCAN I_MLOG# (object id 169) ******************************************************************************** update sys.mlog$ set youngest = :1, yscn = :2 where mowner = :3 and master = :4 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 4 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 4 1 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE 1 TABLE ACCESS CLUSTER MLOG$ 1 INDEX UNIQUE SCAN I_MLOG# (object id 169) ******************************************************************************** SELECT oldest, youngest+1/86400 FROM sys.snap_loadertime$ WHERE tableobj# = :1 FOR UPDATE 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 2 1 0 Fetch 1 0.00 0.00 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 1 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 FOR UPDATE 1 TABLE ACCESS BY INDEX ROWID SNAP_LOADERTIME$ 1 INDEX UNIQUE SCAN I_SNAP_LOADERTIME1 (object id 167) ******************************************************************************** UPDATE sys.snap_loadertime$ SET youngest = :1 WHERE tableobj# = :2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.15 0 0 0 0 Execute 1 0.00 0.00 0 1 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.15 0 1 1 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 INDEX UNIQUE SCAN I_SNAP_LOADERTIME1 (object id 167) ******************************************************************************** update sys.sumdelta$ s set s.timestamp = :1, s.scn = :2 where rowid in (select rowid from sys.sumdelta$ AS OF SNAPSHOT (:3) s1 where s1.tableobj# = :4 and s1.timestamp >= to_date('4000-01-01:00:00:00', 'YYYY-MM-DD:HH24:MI:SS')) 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 1 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE 0 NESTED LOOPS 0 INDEX RANGE SCAN I_SUMDELTA$ (object id 163) 0 TABLE ACCESS BY USER ROWID SUMDELTA$ ******************************************************************************** update sys.sumpartlog$ s set s.timestamp = :1, s.scn = :2 where rowid in (select rowid from sumpartlog$ AS OF SNAPSHOT(:3) s1 where s1.bo# = :4 and s1.timestamp >= to_date('4000-01-01:00:00:00', 'YYYY-MM-DD:HH24:MI:SS')) 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 1 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE 0 NESTED LOOPS 0 TABLE ACCESS BY INDEX ROWID SUMPARTLOG$ 0 INDEX RANGE SCAN I_SUMPARTLOG$ (object id 160) 0 TABLE ACCESS BY USER ROWID SUMPARTLOG$ ******************************************************************************** select NVL(max(s.scn), 0) from sumdelta$ s where s.tableobj# = :1 and NVL(s.scn,0) <= :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 1 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 1 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 0 TABLE ACCESS BY INDEX ROWID SUMDELTA$ 0 INDEX RANGE SCAN I_SUMDELTA$ (object id 163) ******************************************************************************** select decode(bitand(s.flags,1),0,0,1), NVL(max(s.scn), 0) from sumpartlog$ s where s.bo# = :1 and nvl(s.scn,0) <= :2 group by decode(bitand(s.flags,1),0,0,1) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 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.01 0.01 0 1 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT GROUP BY 0 TABLE ACCESS BY INDEX ROWID SUMPARTLOG$ 0 INDEX RANGE SCAN I_SUMPARTLOG$_BOPART$ (object id 161) ******************************************************************************** select dmltype$$, max(snaptime$$) from "JYANG"."MLOG$_MVTEST2" where snaptime$$ <= :1 group by dmltype$$ 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 7 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 7 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT GROUP BY 0 TABLE ACCESS FULL MLOG$_MVTEST2 ******************************************************************************** SELECT o.obj# FROM sys.obj$ o WHERE o.owner# = :1 AND o.name = :2 AND o.type# = 2 AND o.namespace = 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 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID OBJ$ 1 INDEX RANGE SCAN I_OBJ2 (object id 37) ******************************************************************************** select obj# from sys.obj$ o where owner# = :1 and name = :2 and (type# in (3,4) or (type# = 2 and not exists (select null from sys.tab$ t where t.obj# = o.obj# and (bitand(t.property, 512) = 512)))) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 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 12 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 12 0 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 FILTER 1 TABLE ACCESS BY INDEX ROWID OBJ$ 1 INDEX RANGE SCAN I_OBJ2 (object id 37) 0 TABLE ACCESS CLUSTER TAB$ 1 INDEX UNIQUE SCAN I_OBJ# (object id 3) ******************************************************************************** select m.intcol#, f.oldest from sys.col$ m, sys.mlog_refcol$ f where m.obj# = :1 and exists (select 1 from sys.col$ l where l.obj# = :2 and m.name = l.name) and f.mowner = :3 and f.master = :4 and f.colname = m.name order by f.oldest desc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 13 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.01 0.01 0 13 0 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 2 SORT ORDER BY 2 FILTER 2 NESTED LOOPS 2 TABLE ACCESS CLUSTER COL$ 1 INDEX UNIQUE SCAN I_OBJ# (object id 3) 2 TABLE ACCESS BY INDEX ROWID MLOG_REFCOL$ 2 INDEX UNIQUE SCAN I_MLOG_REFCOL1 (object id 187) 2 INDEX UNIQUE SCAN I_COL1 (object id 45) ******************************************************************************** SELECT oldest FROM sys.snap_loadertime$ WHERE tableobj# = :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 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID SNAP_LOADERTIME$ 1 INDEX UNIQUE SCAN I_SNAP_LOADERTIME1 (object id 167) ******************************************************************************** UPDATE sys.snap$ set status = :1 WHERE vname = :2 AND sowner = :3 AND instsite = :4 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 1 2 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 2 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 INDEX UNIQUE SCAN I_SNAP1 (object id 181) ******************************************************************************** update sys.snap$ set snapshot = :1, snaptime = :2, auto_date = :3, snapid = :4, error# = 0 where vname = :6 and sowner = :5 and instsite = :7 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 1 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 1 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 INDEX UNIQUE SCAN I_SNAP1 (object id 181) ******************************************************************************** update sum$ set containerobj#=:2,containertype=:3,containernam=:4, fullrefreshtim=:5,increfreshtim=:6,lastrefreshscn=:7,lastrefreshdate=:8, refreshmode=:9,pflags=:10,mflags=:11,numdetailtab=:12,numaggregates=:13, numkeycolumns=:14,numjoins=:15,numinlines=:16,sumtextlen=:17,sumtext=:18, fromoffset=:19,fromlen=:20,objcount=:21,metaversion=:22, xpflags=:23, numwhrnodes=:24, numhavnodes=:25where obj#=:1 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 3 9 3 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 3 9 3 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 INDEX UNIQUE SCAN I_SUM$_1 (object id 388) ******************************************************************************** select o.owner#, u.name, o.name, o.namespace, o.obj#, d.d_timestamp, nvl(d.property,0), o.type#, o.subname from dependency$ d, obj$ o, user$ u where d.p_obj#=:1 and (d.p_timestamp=:2 or d.property=2) and d.d_obj#=o.obj# and o.owner#=u.user# and decode(:3,0,0,o.type#)=:3 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.01 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 12 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.00 0.02 0 12 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS 1 NESTED LOOPS 1 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ 1 INDEX RANGE SCAN I_DEPENDENCY2 (object id 128) 1 TABLE ACCESS BY INDEX ROWID OBJ$ 1 INDEX UNIQUE SCAN I_OBJ1 (object id 36) 1 TABLE ACCESS CLUSTER USER$ 1 INDEX UNIQUE SCAN I_USER# (object id 11) ******************************************************************************** update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11, dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null) and(subname=:12 or subname is null and :12 is null) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 0 0 Execute 4 0.00 0.00 0 8 5 4 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 8 0.00 0.00 0 8 5 4 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE 1 INDEX RANGE SCAN I_OBJ2 (object id 37) ******************************************************************************** SELECT operation#, cols, sql_txt FROM sys.snap_refop$ WHERE operation# = 7 AND sowner = :1 AND vname = :2 AND instsite = :3 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 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID SNAP_REFOP$ 1 INDEX RANGE SCAN I_SNAP_REFOP1 (object id 189) ******************************************************************************** select null from ind$ i, obj$ o, user$ u where i.bo#=o.obj# and i.type#=9 and o.name=:1 and o.owner# = u.user# and u.name= :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 10 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 10 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 NESTED LOOPS 2 NESTED LOOPS 1 TABLE ACCESS BY INDEX ROWID USER$ 1 INDEX UNIQUE SCAN I_USER1 (object id 44) 2 TABLE ACCESS BY INDEX ROWID OBJ$ 2 INDEX RANGE SCAN I_OBJ2 (object id 37) 0 TABLE ACCESS CLUSTER IND$ 1 INDEX UNIQUE SCAN I_OBJ# (object id 3) ******************************************************************************** update snap$ set status = :1, refhnt = :2 where sowner = :3 and vname = :4 and instsite = :5 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 1 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.02 0 1 1 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 INDEX UNIQUE SCAN I_SNAP1 (object id 181) ******************************************************************************** truncate table "JYANG"."MV_MVTEST2" purge snapshot log call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.12 0 0 1 0 Execute 1 0.06 0.70 8 1 56 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.06 0.83 8 1 57 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 (recursive depth: 1) ******************************************************************************** BEGIN IF (xdb.DBMS_XDBZ.is_hierarchy_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name)) THEN xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name); END IF; EXCEPTION WHEN OTHERS THEN null; END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.03 0.39 0 0 0 0 Execute 1 0.00 0.00 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.03 0.39 0 0 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 35 (recursive depth: 2) ******************************************************************************** SELECT COUNT(*) FROM ALL_POLICIES V WHERE V.OBJECT_OWNER = :b1 AND V.OBJECT_NAME = :b2 AND POLICY_NAME LIKE '%xdbrls%' 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 1 0.00 0.00 0 36 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 36 0 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 35 (recursive depth: 3) ******************************************************************************** SELECT COUNT(*) FROM USER_POLICIES V WHERE V.OBJECT_NAME = :b1 AND POLICY_NAME LIKE '%xdbrls%' 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 1 0.00 0.00 0 9 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 9 0 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 35 (recursive depth: 3) ******************************************************************************** select log, sysdate, youngest, youngest+1/86400, oldest, oldest_pk, oldest_oid, oldest_new from sys.mlog$ where master = :2 and mowner = :1 for update call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 5 2 0 Fetch 2 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 7 2 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 FOR UPDATE 0 TABLE ACCESS CLUSTER MLOG$ 0 INDEX UNIQUE SCAN I_MLOG# (object id 169) ******************************************************************************** select n.intcol# from ntab$ n, col$ c where n.obj#=:1 and c.obj#=:1 and c.intcol#=n.intcol# and bitand(c.property, 32768)!=32768 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 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 20 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 20 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 NESTED LOOPS 5 TABLE ACCESS CLUSTER COL$ 1 INDEX UNIQUE SCAN I_OBJ# (object id 3) 0 INDEX UNIQUE SCAN I_NTAB2 (object id 354) ******************************************************************************** delete from superobj$ where subobj# = :1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.01 0 0 0 0 Execute 1 0.00 0.00 0 1 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.01 0 1 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE 0 TABLE ACCESS BY INDEX ROWID SUPEROBJ$ 0 INDEX UNIQUE SCAN I_SUPEROBJ1 (object id 65) ******************************************************************************** update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#= decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9), audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15, rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21, analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1, null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29, flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34, spare6=:35 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 5 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 5 1 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE 1 TABLE ACCESS CLUSTER TAB$ 1 INDEX UNIQUE SCAN I_OBJ# (object id 3) ******************************************************************************** update ind$ set ts#=:2,file#=:3,block#=:4,intcols=:5,type#=:6,flags=:7, property=:8,pctfree$=:9,initrans=:10,maxtrans=:11,blevel=:12,leafcnt=:13, distkey=:14,lblkkey=:15,dblkkey=:16,clufac=:17,cols=:18,analyzetime=:19, samplesize=:20,dataobj#=:21,degree=decode(:22,1,null,:22),instances= decode(:23,1,null,:23),rowcnt=:24,pctthres$=:31*256+:25, indmethod#=:26, trunccnt=:27,spare1=:28,spare4=:29,spare2=:30,spare6=:32where 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 2 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 2 1 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 INDEX UNIQUE SCAN I_IND1 (object id 39) ******************************************************************************** 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 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 10 2 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 10 2 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE 1 TABLE ACCESS CLUSTER SEG$ 1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (object id 9) ******************************************************************************** select 1 from sys.snap$ where vname = :1 and sowner = :2 and instsite = :3 for update 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 1 2 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 2 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 FOR UPDATE 1 INDEX UNIQUE SCAN I_SNAP1 (object id 181) ******************************************************************************** update /*+ BYPASS_UJVC */ ( select s.status status from snap$ s, snap_reftime$ r where s.sowner = r.sowner and s.vname = r.vname and r.mowner = :1 and r.master = :2 and s.mlink IS NULL and r.instsite =0 and s.instsite = 0) v set status = status + 16 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 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE 0 NESTED LOOPS 0 TABLE ACCESS FULL SNAP_REFTIME$ 0 TABLE ACCESS BY INDEX ROWID SNAP$ 0 INDEX UNIQUE SCAN I_SNAP1 (object id 181) ******************************************************************************** INSERT /*+ APPEND */ INTO "JYANG"."MV_MVTEST2"("COUNT(*)","C1","COUNT(C2)", "SUM(C2)") SELECT COUNT(*),"MVTEST2"."C1",COUNT("MVTEST2"."C2"), SUM("MVTEST2"."C2") FROM "MVTEST2" "MVTEST2" GROUP BY "MVTEST2"."C1" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.50 2 8 6 7 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.50 2 8 6 7 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 LOAD AS SELECT 7 SORT GROUP BY 7 TABLE ACCESS FULL MVTEST2 ******************************************************************************** UPDATE sys.snap$ SET status = :1, rscn = :2, refhnt = :3, flag = :4 WHERE vname = :5 AND sowner = :6 AND instsite = :7 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 1 2 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 2 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 INDEX UNIQUE SCAN I_SNAP1 (object id 181) ******************************************************************************** UPDATE sys.snap_reftime$ SET snaptime = :1, loadertime = :2, refscn = :3, fcmaskvec = :4, ejmaskvec = :5, masobj# = :6, sub_handle = :7, change_view = :8 WHERE sowner = :9 AND vname = :10 AND tablenum = :11 AND instsite = :12 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 1 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 1 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 INDEX UNIQUE SCAN I_SNAP_REFTIME1 (object id 185) ******************************************************************************** select obj# from obj$ o,user$ u where o.name=:1 and o.owner#=u.user# and u.name=:2 and o.type#=:3 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 5 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 5 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS 1 TABLE ACCESS BY INDEX ROWID USER$ 1 INDEX UNIQUE SCAN I_USER1 (object id 44) 1 TABLE ACCESS BY INDEX ROWID OBJ$ 2 INDEX RANGE SCAN I_OBJ2 (object id 37) ******************************************************************************** select sumtextlen,containertype,lengthb(containernam),refreshmode,pflags from sum$ 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 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID SUM$ 1 INDEX UNIQUE SCAN I_SUM$_1 (object id 388) ******************************************************************************** select query_len from snap$ where sowner = :1 and vname = :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 4 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 4 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID SNAP$ 1 INDEX RANGE SCAN I_SNAP1 (object id 181) ******************************************************************************** select query_txt from snap$ where sowner = :1 and vname = :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 5 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 5 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID SNAP$ 1 INDEX RANGE SCAN I_SNAP1 (object id 181) ******************************************************************************** select tname, lengthb(tname) from snap$ where sowner = :1 and vname = :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 4 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 4 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID SNAP$ 1 INDEX RANGE SCAN I_SNAP1 (object id 181) ******************************************************************************** select text from view$ where rowid=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.03 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.03 0 4 0 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 3) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY USER ROWID VIEW$ ******************************************************************************** select count(*) from all_snapshots where owner = :1 and name = :2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.04 0 5 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 1 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.04 0 6 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 0 FILTER 0 FILTER 0 NESTED LOOPS OUTER 0 NESTED LOOPS 0 NESTED LOOPS 0 NESTED LOOPS 0 TABLE ACCESS BY INDEX ROWID SNAP$ 0 INDEX UNIQUE SCAN I_SNAP1 (object id 181) 0 TABLE ACCESS BY INDEX ROWID USER$ 0 INDEX UNIQUE SCAN I_USER1 (object id 44) 0 INDEX UNIQUE SCAN I_SNAP_REFTIME1 (object id 185) 0 TABLE ACCESS BY INDEX ROWID OBJ$ 0 INDEX RANGE SCAN I_OBJ2 (object id 37) 0 INDEX RANGE SCAN I_RGCHILD (object id 212) 0 NESTED LOOPS 0 FIXED TABLE FULL X$KZSRO 0 INDEX RANGE SCAN I_OBJAUTH2 (object id 109) 0 FIXED TABLE FULL X$KZSPR ******************************************************************************** select decode(bitand(s.flag2,64), 0, 0, 1) from snap$ s where s.sowner= :1 and s.vname= :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 4 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 4 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID SNAP$ 1 INDEX RANGE SCAN I_SNAP1 (object id 181) ******************************************************************************** ALTER SUMMARY "JYANG"."MV_MVTEST2" COMPILE call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 35 0 0 Execute 1 0.04 0.55 0 0 2 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.06 0.56 0 35 2 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 (recursive depth: 1) ******************************************************************************** BEGIN /* NOP UNLESS A TABLE OBJECT */ IF dictionary_obj_type = 'TABLE' AND sys.dbms_cdc_publish.active > 0 THEN sys.dbms_cdc_publish.change_table_trigger(dictionary_obj_owner, dictionary_obj_name,sysevent); END IF; END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.04 0.54 0 0 0 0 Execute 1 0.00 0.00 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.04 0.54 0 0 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) ******************************************************************************** select line,position#,text from error$ where obj# = :1 order by sequence# 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 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY 0 TABLE ACCESS BY INDEX ROWID ERROR$ 0 INDEX RANGE SCAN I_ERROR1 (object id 125) ******************************************************************************** delete from dependency$ 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 3 10 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 3 10 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE 2 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ 2 INDEX RANGE SCAN I_DEPENDENCY1 (object id 127) ******************************************************************************** delete 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 2 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 2 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE 0 INDEX RANGE SCAN I_ACCESS1 (object id 129) ******************************************************************************** insert into dependency$(d_obj#,d_timestamp,order#,p_obj#,p_timestamp,d_owner#, property) values (:1,:2,:3,:4,:5,:6, :7) 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 1 10 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 1 10 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) ******************************************************************************** insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 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 1 0.00 0.00 0 0 0 0 Misses in library cache during parse: 1 Parsing user id: SYS (recursive depth: 1) ******************************************************************************** select count(*), max(scn) from sumdelta$, sumdep$ where sumobj# = :1 and p_obj# = tableobj# and scn > :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 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 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 SORT AGGREGATE 0 TABLE ACCESS BY INDEX ROWID SUMDELTA$ 2 NESTED LOOPS 1 TABLE ACCESS BY INDEX ROWID SUMDEP$ 1 INDEX RANGE SCAN I_SUMDEP$_1 (object id 403) 0 INDEX RANGE SCAN I_SUMDELTA$ (object id 163) ******************************************************************************** select count(*), max(scn) from (select sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags from sumpartlog$ sp, sumdep$ sd where sd.sumobj# = :1 and sd.p_obj# = sp.bo# group by sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags minus select sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags from sumpartlog$ sp where sp.bo# not in (select sk.DETAILOBJ# from sumkey$ sk where sk.sumobj# = :1 and sk.DETAILCOLFUNCTION in (2,3) ) and bitand(sp.flags, 2) != 2 and sp.PMOPTYPE in (2,3,5,7) group by sp.BO#, sp.PMOPTYPE, sp.scn, sp.flags) where scn > : 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 6 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 6 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 0 VIEW 0 MINUS 0 SORT UNIQUE 0 SORT GROUP BY 0 TABLE ACCESS BY INDEX ROWID SUMPARTLOG$ 2 NESTED LOOPS 1 TABLE ACCESS BY INDEX ROWID SUMDEP$ 1 INDEX RANGE SCAN I_SUMDEP$_1 (object id 403) 0 INDEX RANGE SCAN I_SUMPARTLOG$_BOPART$ (object id 161) 0 SORT UNIQUE 0 SORT GROUP BY 0 FILTER 0 TABLE ACCESS FULL SUMPARTLOG$ 0 TABLE ACCESS BY INDEX ROWID SUMKEY$ 0 INDEX RANGE SCAN I_SUMKEY$_1 (object id 397) ******************************************************************************** update sumdetail$ set detailobjtype=:3, detailalias=:4, refreshscn=:5, detaileut=:6, spare4=:7, inline# =:8, instance# =:9, dataless =:10 where sumobj#=:1 and detailobj#=: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 3 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 3 1 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE 1 AND-EQUAL 2 INDEX RANGE SCAN I_SUMDETAIL$_1 (object id 390) 1 INDEX RANGE SCAN I_SUMDETAIL$_2 (object id 391) ******************************************************************************** select min(s1.lastrefreshscn) from sum$ s1, sumdetail$ s2 where s1.obj#= s2.sumobj# and s2.detailobj# = :1 and s1.obj#!= :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 1 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 0 NESTED LOOPS 0 TABLE ACCESS BY INDEX ROWID SUMDETAIL$ 0 INDEX RANGE SCAN I_SUMDETAIL$_2 (object id 391) 0 TABLE ACCESS BY INDEX ROWID SUM$ 0 INDEX UNIQUE SCAN I_SUM$_1 (object id 388) ******************************************************************************** delete from sumpartlog$ where bo#=:1 and scn<:2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.00 0 1 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.00 0 1 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE 0 TABLE ACCESS BY INDEX ROWID SUMPARTLOG$ 0 INDEX RANGE SCAN I_SUMPARTLOG$_BOPART$ (object id 161) ******************************************************************************** select OLDEST, flag from sys.mlog$ where mowner = :1 and master = :2 for update 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 4 2 0 Fetch 1 0.00 0.00 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 7 2 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 FOR UPDATE 2 TABLE ACCESS CLUSTER MLOG$ 2 INDEX UNIQUE SCAN I_MLOG# (object id 169) ******************************************************************************** update sys.slog$ set snaptime = :1 where snapid = :2 and mowner = :3 and master = :4 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 4 5 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 4 5 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE 1 TABLE ACCESS CLUSTER SLOG$ 1 INDEX UNIQUE SCAN I_MLOG# (object id 169) ******************************************************************************** SELECT oldest FROM sys.snap_loadertime$ WHERE tableobj# = :1 FOR UPDATE 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 2 2 0 Fetch 1 0.00 0.00 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 2 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 FOR UPDATE 1 TABLE ACCESS BY INDEX ROWID SNAP_LOADERTIME$ 1 INDEX UNIQUE SCAN I_SNAP_LOADERTIME1 (object id 167) ******************************************************************************** UPDATE sys.snap_logdep$ SET snaptime = :1 WHERE snapid = :2 AND tableobj# = :3 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 1 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 1 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 INDEX UNIQUE SCAN I_SNAP_LOGDEP1 (object id 165) ******************************************************************************** select snaptime from sys.slog$ where master = :2 and mowner = :1 order by snaptime 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 goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT ORDER BY 3 TABLE ACCESS CLUSTER SLOG$ 1 INDEX UNIQUE SCAN I_MLOG# (object id 169) ******************************************************************************** delete from "JYANG"."MLOG$_MVTEST2" where snaptime$$ <= :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 7 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 7 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE 0 TABLE ACCESS FULL MLOG$_MVTEST2 ******************************************************************************** update sys.mlog$ set oldest = :1, oldest_pk = :2, oldest_oid = :3, oldest_new = :4, youngest = :5 where master = :6 and mowner = :7 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 4 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 4 1 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE 1 TABLE ACCESS CLUSTER MLOG$ 1 INDEX UNIQUE SCAN I_MLOG# (object id 169) ******************************************************************************** SELECT NVL(MIN(snaptime),sysdate) FROM sys.snap_logdep$ WHERE tableobj# = :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 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 3 TABLE ACCESS BY INDEX ROWID SNAP_LOGDEP$ 3 INDEX RANGE SCAN I_SNAP_LOGDEP1 (object id 165) ******************************************************************************** DELETE FROM sys.sumdelta$ sd WHERE sd.tableobj# = :1 AND sd.timestamp <= :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 1 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE 0 INDEX RANGE SCAN I_SUMDELTA$ (object id 163) ******************************************************************************** DELETE FROM sys.sumpartlog$ sp WHERE sp.bo# = :1 AND sp.timestamp <= :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 1 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE 0 TABLE ACCESS BY INDEX ROWID SUMPARTLOG$ 0 INDEX RANGE SCAN I_SUMPARTLOG$_BOPART$ (object id 161) ******************************************************************************** BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.01 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.01 0 0 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 ******************************************************************************** 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 goal: CHOOSE Parsing user id: 61 ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.01 0.00 0 0 0 0 Execute 4 0.06 0.31 1 24 25 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.07 0.32 1 24 25 2 Misses in library cache during parse: 3 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 101 0.18 1.85 0 40 1 0 Execute 119 0.17 1.98 10 107 138 38 Fetch 79 0.00 0.00 0 278 0 58 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 299 0.35 3.84 10 425 139 96 Misses in library cache during parse: 81 11 user SQL statements in session. 102 internal SQL statements in session. 113 SQL statements in session. ******************************************************************************** Trace file: athena_ora_268.trc Trace file compatibility: 9.00.01 Sort options: default 1 session in tracefile. 11 user SQL statements in trace file. 102 internal SQL statements in trace file. 113 SQL statements in trace file. 93 unique SQL statements in trace file. 1086 lines in trace file.