!!! !!!! What happens during an update on a table with "on commit" MV created on it !!! Note the FULL table scan of the MV in this step when ORACLE is !!! removing old record from and insert new record into the MV DELETE FROM "JTEST"."MV_TEST1_J_TEST2" SNA$ WHERE "T1ROWID" IN (SELECT /*+ NO_MERGE NO_SEMIJOIN */ * FROM (SELECT /*+ CARDINALITY( 2) */ CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM "JTEST"."MLOG$_TEST1" "MAS$" WHERE "MAS$".SNAPTIME$$ > :1 )MAS$) INSERT INTO "JTEST"."MV_TEST1_J_TEST2" SELECT /*+ NO_MERGE("JV$") */ "JV$"."C1","JV$"."C2","JV$"."RID$","MAS$0"."C3","MAS$0".ROWID FROM ( SELECT "MAS$"."ROWID" "RID$" , "MAS$".* FROM "JTEST"."TEST1" "MAS$" WHERE ROWID IN (SELECT /*+ CARDINALITY( 2) NO_SEMIJOIN */ CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM "JTEST"."MLOG$_TEST1" "MAS$" WHERE "MAS$".SNAPTIME$$ > :1 )) "JV$", "JTEST"."TEST2" AS OF SNAPSHOT (:2) "MAS$0" WHERE "JV$"."C1"="MAS$0"."C1" TKPROF: Release 9.2.0.1.0 - Production on Tue Oct 8 16:21:40 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Trace file: athena_ora_3380.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 ******************************************************************************** The following statement encountered a error during parse: select test1.c1, test1.c2, test1.rowid as t1rowid, test2.c3, test2.rowid as t2rowid from test1, test2 where test1.c1=test2.c1 Error encountered: ORA-10980 ******************************************************************************** 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.03 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.03 0 0 0 0 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer goal: CHOOSE Parsing user id: 72 ******************************************************************************** update test1 set c3='row1' where c1=1 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 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: 72 Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE 1 INDEX UNIQUE SCAN PK_TEST1 (object id 31962) ******************************************************************************** INSERT /*+ IDX(0) */ INTO "JTEST"."MLOG$_TEST1" (dmltype$$,old_new$$, snaptime$$,change_vector$$,m_row$$,"C1") VALUES (:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:m,:1) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 2 0.00 0.00 0 1 4 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.00 0 1 4 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) ******************************************************************************** commit call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.09 0.09 0 18 5 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.09 0.09 0 18 5 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 72 ******************************************************************************** select s.sowner, s.vname from snap$ s, (select distinct sowner, vname from snap_reftime$ where instsite=0 start with masobj# in (31960) connect by prior sowner = mowner and prior vname = master) v where s.sowner=v.sowner and s.vname=v.vname and bitand(s.flag,32768)!=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 0 0 0 Fetch 2 0.00 0.00 0 10 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 10 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$ 3 NESTED LOOPS 1 VIEW 1 SORT UNIQUE 1 FILTER 1 CONNECT BY WITH FILTERING 1 NESTED LOOPS 1 TABLE ACCESS FULL SNAP_REFTIME$ 1 TABLE ACCESS BY USER ROWID SNAP_REFTIME$ 0 NESTED LOOPS 1 BUFFER SORT 1 CONNECT BY PUMP 0 TABLE ACCESS FULL SNAP_REFTIME$ 1 INDEX RANGE SCAN I_SNAP1 (object id 181) ******************************************************************************** update tab$ set spare3 = :1 where obj# = :2 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: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE 1 TABLE ACCESS CLUSTER TAB$ 1 INDEX UNIQUE SCAN I_OBJ# (object id 3) ******************************************************************************** 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 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 0 10 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 10 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) ******************************************************************************** SELECT DISTINCT s.sowner, s.vname, s.auto_fast FROM snap_reftime$ r, snap$ s WHERE r.vname=s.vname AND r.sowner=s.sowner and bitand(s.flag,32768)!=0 AND r.masobj# IN (31960) 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 0 6 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 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 UNIQUE 1 TABLE ACCESS BY INDEX ROWID SNAP_REFTIME$ 6 NESTED LOOPS 2 TABLE ACCESS FULL SNAP$ 3 INDEX RANGE SCAN I_SNAP_REFTIME1 (object id 185) ******************************************************************************** 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.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$ 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.01 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 2 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 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 ------- --------------------------------------------------- 2 SORT ORDER BY 2 TABLE ACCESS BY INDEX ROWID SNAP_REFTIME$ 2 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 3 0.00 0.00 0 0 0 0 Execute 3 0.00 0.00 0 3 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 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 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 10 0.00 0.00 0 0 0 0 Execute 10 0.00 0.00 0 0 0 0 Fetch 10 0.00 0.00 0 30 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 30 0.00 0.00 0 30 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 6 0.00 0.00 0 0 0 0 Fetch 6 0.00 0.00 0 18 0 6 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 13 0.00 0.00 0 18 0 6 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 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 12 0 6 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 18 0.00 0.00 0 12 0 6 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 4 0.00 0.00 0 0 0 0 Execute 4 0.00 0.00 0 14 4 0 Fetch 4 0.00 0.00 0 12 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.00 0.00 0 26 4 4 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 "JTEST"."MLOG$_TEST1" set snaptime$$ = :1 where snaptime$$ > to_date('2100-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.01 0.00 0 7 2 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.00 0 7 2 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE 2 TABLE ACCESS FULL MLOG$_TEST1 ******************************************************************************** update sys.mlog$ set youngest = :1, yscn = :2 where mowner = :3 and master = :4 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 8 2 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 8 2 2 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 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 4 2 0 Fetch 2 0.00 0.00 0 0 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 4 2 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 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 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 2 2 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 2 2 2 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 s.tableobj# = :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 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 2 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 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 UPDATE 0 INDEX RANGE SCAN I_SUMDELTA$ (object id 163) ******************************************************************************** update "JTEST"."MLOG$_TEST2" set snaptime$$ = :1 where snaptime$$ > to_date('2100-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 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 UPDATE 0 TABLE ACCESS FULL MLOG$_TEST2 ******************************************************************************** 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 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 2 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 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 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 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 2 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 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 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 "JTEST"."MLOG$_TEST2" 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$_TEST2 ******************************************************************************** select dmltype$$, max(snaptime$$) from "JTEST"."MLOG$_TEST1" 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 2 0.00 0.00 0 7 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 7 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 GROUP BY 2 TABLE ACCESS FULL MLOG$_TEST1 ******************************************************************************** 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 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 6 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 6 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 INDEX ROWID OBJ$ 1 INDEX RANGE SCAN I_OBJ2 (object id 37) ******************************************************************************** SELECT oldest FROM sys.snap_loadertime$ WHERE tableobj# = :1 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 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 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 ------- --------------------------------------------------- 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 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 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) ******************************************************************************** select 1 from sumpartlog$ a where a.bo# = :1 and bitand(a.flags,5) != 5 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 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 COUNT STOPKEY 0 TABLE ACCESS BY INDEX ROWID SUMPARTLOG$ 0 INDEX RANGE SCAN I_SUMPARTLOG$_BOPART$ (object id 161) ******************************************************************************** SELECT query_txt 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.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$ 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 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 2 6 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 2 6 2 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 OLD_NEW$$, COUNT(*) FROM "JTEST"."MLOG$_TEST1" WHERE SNAPTIME$$ > :1 AND SNAPTIME$$ <= :2 GROUP BY OLD_NEW$$ 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 7 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.00 0.00 0 7 0 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 72 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 2 SORT GROUP BY 2 TABLE ACCESS FULL MLOG$_TEST1 ******************************************************************************** DELETE FROM "JTEST"."MV_TEST1_J_TEST2" SNA$ WHERE "T1ROWID" IN (SELECT /*+ NO_MERGE NO_SEMIJOIN */ * FROM (SELECT /*+ CARDINALITY( 2) */ CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM "JTEST"."MLOG$_TEST1" "MAS$" WHERE "MAS$".SNAPTIME$$ > :1 )MAS$) 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 12 2 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 12 2 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 72 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE 1 HASH JOIN 1 VIEW 1 SORT UNIQUE 2 TABLE ACCESS FULL MLOG$_TEST1 1 TABLE ACCESS FULL MV_TEST1_J_TEST2 ******************************************************************************** INSERT INTO "JTEST"."MV_TEST1_J_TEST2" SELECT /*+ NO_MERGE("JV$") */ "JV$"."C1","JV$"."C2","JV$"."RID$","MAS$0"."C3","MAS$0".ROWID FROM ( SELECT "MAS$"."ROWID" "RID$" , "MAS$".* FROM "JTEST"."TEST1" "MAS$" WHERE ROWID IN (SELECT /*+ CARDINALITY( 2) NO_SEMIJOIN */ CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM "JTEST"."MLOG$_TEST1" "MAS$" WHERE "MAS$".SNAPTIME$$ > :1 )) "JV$", "JTEST"."TEST2" AS OF SNAPSHOT (:2) "MAS$0" WHERE "JV$"."C1"="MAS$0"."C1" 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 12 3 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.00 0 12 3 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer goal: CHOOSE Parsing user id: 72 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS 1 VIEW 1 NESTED LOOPS 1 VIEW 1 SORT UNIQUE 2 TABLE ACCESS FULL MLOG$_TEST1 1 TABLE ACCESS BY USER ROWID TEST1 1 TABLE ACCESS BY INDEX ROWID TEST2 1 INDEX UNIQUE SCAN PK_TEST2 (object id 31963) ******************************************************************************** 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 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 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 2 0.00 0.00 0 2 2 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 2 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 2 UPDATE 2 INDEX UNIQUE SCAN I_SNAP_REFTIME1 (object id 185) ******************************************************************************** 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 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: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 0 TABLE ACCESS BY INDEX ROWID SUMDELTA$ 3 NESTED LOOPS 2 TABLE ACCESS BY INDEX ROWID SUMDEP$ 2 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 7 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 7 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$ 3 NESTED LOOPS 2 TABLE ACCESS BY INDEX ROWID SUMDEP$ 2 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) ******************************************************************************** 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.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) ******************************************************************************** select OLDEST, flag 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.00 0.00 0 6 2 0 Fetch 2 0.00 0.00 0 6 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 12 2 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.slog$ set snaptime = :1 where snapid = :2 and mowner = :3 and master = :4 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 8 10 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 8 10 2 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 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 4 2 0 Fetch 2 0.00 0.00 0 0 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 4 2 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 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 2 0.00 0.00 0 0 0 0 Execute 2 0.01 0.00 0 2 2 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.00 0 2 2 2 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 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 6 2 0 Fetch 2 0.00 0.00 0 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 10 2 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) ******************************************************************************** select snaptime from sys.slog$ where master = :2 and mowner = :1 order by snaptime 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 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 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 ------- --------------------------------------------------- 1 SORT ORDER BY 1 TABLE ACCESS CLUSTER SLOG$ 1 INDEX UNIQUE SCAN I_MLOG# (object id 169) ******************************************************************************** delete from "JTEST"."MLOG$_TEST2" 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$_TEST2 ******************************************************************************** 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 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 8 2 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 8 2 2 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 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 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 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 ------- --------------------------------------------------- 1 SORT AGGREGATE 1 TABLE ACCESS BY INDEX ROWID SNAP_LOGDEP$ 1 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 2 0.00 0.00 0 0 0 0 Execute 2 0.01 0.00 0 2 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 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_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 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 2 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 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 TABLE ACCESS BY INDEX ROWID SUMPARTLOG$ 0 INDEX RANGE SCAN I_SUMPARTLOG$_BOPART$ (object id 161) ******************************************************************************** delete from "JTEST"."MLOG$_TEST1" 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 3 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 7 3 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 FULL MLOG$_TEST1 ******************************************************************************** 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 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 6 2 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 6 2 2 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 3 INDEX RANGE SCAN I_SUMDETAIL$_1 (object id 390) 1 INDEX RANGE SCAN I_SUMDETAIL$_2 (object id 391) ******************************************************************************** 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.10 0.13 0 19 7 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.10 0.13 0 19 7 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 91 0.01 0.04 0 0 0 0 Execute 98 0.07 0.04 0 148 59 29 Fetch 66 0.00 0.00 0 171 0 46 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 255 0.09 0.08 0 319 59 75 Misses in library cache during parse: 51 Misses in library cache during execute: 1 6 user SQL statements in session. 88 internal SQL statements in session. 94 SQL statements in session. ******************************************************************************** Trace file: athena_ora_3380.trc Trace file compatibility: 9.00.01 Sort options: default 1 session in tracefile. 6 user SQL statements in trace file. 88 internal SQL statements in trace file. 94 SQL statements in trace file. 54 unique SQL statements in trace file. 1140 lines in trace file.