oracle 的truncate 内部执行过程-凯发app官方网站

执行truncate table命令，内部过程如下：

 

tkprof: release 11.2.0.1.0 - development on 星期五 3月 25 10:25:47 2011

凯发app官方网站 copyright (c) 1982, 2009, oracle and/or its affiliates.  all rights reserved.

trace file: c:\app\administrator\diag\rdbms\xyz\xyz\trace\xyz_ora_7816.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
********************************************************************************

sql id: 3nkd3g3ju5ph1
plan hash: 2853959010
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1,
  spare2
from
 obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
  and linkname is null and subname is null

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
parse        1      0.00       0.00          0          0          0           0
execute      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: 0
optimizer mode: choose
parsing user id: sys   (recursive depth: 1)

rows     row source operation
-------  ---------------------------------------------------
      1  table access by index rowid obj$ (cr=4 pr=0 pw=0 time=0 us cost=4 size=84 card=1)
      1   index range scan i_obj2 (cr=3 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 37)

elapsed times include waiting on following events:
  event waited on                             times   max. wait  total waited
  ----------------------------------------   waited  ----------  ------------
  sql*net message to client                       1        0.00          0.00
  sql*net message from client                     1        7.99          7.99
********************************************************************************

sql id: 76yafjccvr01u
plan hash: 1537128132
select count(fa#)
from
 sys_fba_trackedtables where obj# = 73680 and dropscn = 0

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: 0
optimizer mode: choose
parsing user id: sys   (recursive depth: 1)

rows     row source operation
-------  ---------------------------------------------------
      1  sort aggregate (cr=1 pr=0 pw=0 time=0 us)
      0   table access by index rowid sys_fba_trackedtables (cr=1 pr=0 pw=0 time=0 us cost=1 size=6 card=1)
      0    index unique scan sys_c001432 (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 1214)

********************************************************************************

sql id: 0ydz0ub16zr53
plan hash: 0
lock table "bak" in exclusive mode  nowait

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
parse        1      0.00       0.05          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.05          0          0          0           0

misses in library cache during parse: 1
optimizer mode: all_rows
parsing user id: 85     (recursive depth: 1)
********************************************************************************

sql id: b5w6yz14ry3td
plan hash: 3881973715
truncate table bak

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
parse        1      0.01       0.05          0          6          0           0
execute      1      0.04       0.00          0         45          9           0
fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.06       0.06          0         51          9           0

misses in library cache during parse: 1
optimizer mode: all_rows
parsing user id: 85 

elapsed times include waiting on following events:
  event waited on                             times   max. wait  total waited
  ----------------------------------------   waited  ----------  ------------
  sql*net message to client                       1        0.00          0.00
  sql*net message from client                     1        5.14          5.14
********************************************************************************

sql id: ct6c4h224pxgz
plan hash: 0
begin
  begin
    if (sys.is_vpd_enabled(sys.dictionary_obj_owner,
  sys.dictionary_obj_name, xdb.dbms_xdbz.is_enabled_contents)) then
     
  xdb.xdb_pitrig_pkg.pitrig_truncate(sys.dictionary_obj_owner,
  sys.dictionary_obj_name);
    elsif
  (sys.is_vpd_enabled(sys.dictionary_obj_owner, sys.dictionary_obj_name,
  xdb.dbms_xdbz.is_enabled_resmetadata)) then
     
  xdb.xdb_pitrig_pkg.pitrig_dropmetadata(sys.dictionary_obj_owner,
  sys.dictionary_obj_name);
    end if;
  exception
    when others then
    
  null;
  end;
end;

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           1
fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           1

misses in library cache during parse: 0
optimizer mode: choose
parsing user id: sys   (recursive depth: 1)
********************************************************************************

sql id: 86kwhy1f0bttn
plan hash: 2389657030
select /* all_rows */ count(*)
from
 dba_policies v where v.object_owner = :b3 and v.object_name = :b2 and
  (v.policy_name like '%xdbrls%' or v.policy_name like '%$xd_%') and
  v.function = :b1

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         12          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0         12          0           2

misses in library cache during parse: 0
optimizer mode: all_rows
parsing user id: sys   (recursive depth: 2)

rows     row source operation
-------  ---------------------------------------------------
      1  sort aggregate (cr=6 pr=0 pw=0 time=0 us)
      0   filter  (cr=6 pr=0 pw=0 time=0 us)
      0    nested loops  (cr=6 pr=0 pw=0 time=0 us cost=4 size=127 card=1)
      0     nested loops  (cr=6 pr=0 pw=0 time=0 us cost=3 size=105 card=1)
      1      nested loops  (cr=5 pr=0 pw=0 time=0 us cost=3 size=58 card=1)
      1       table access by index rowid user$ (cr=2 pr=0 pw=0 time=0 us cost=1 size=18 card=1)
      1        index unique scan i_user1 (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 46)
      1       index range scan i_obj5 (cr=3 pr=0 pw=0 time=0 us cost=2 size=40 card=1)(object id 40)
      0      table access by index rowid rls$ (cr=1 pr=0 pw=0 time=0 us cost=0 size=47 card=1)
      0       index range scan i_rls2 (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 376)
      0     index range scan i_user2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=22 card=1)(object id 47)
      0    nested loops  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
      0     index full scan i_user2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)
      0     index range scan i_obj4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=9 card=1)(object id 39)

********************************************************************************

declare
 type attrs_cur is ref cursor;
 m_cur       attrs_cur;
 m_event varchar2(512);
 m_user  varchar2(512);
 m_owner varchar2(512);
 m_user1 varchar2(512);
 m_type  varchar2(512);
 m_stmt  varchar2(512);
 m_name  varchar2(5120);
 m_column varchar2(5120);
 m_cnt   number;
 m_stmt1 varchar2(512);
 m_var   varchar2(512);
 m_o_stmt varchar2(5120);
pragma autonomous_transaction;
begin
 m_stmt:='select sys.dbms_standard.dictionary_obj_type from dual';
 execute immediate m_stmt into m_type;
 if(not (m_type='table' or m_type='trigger' or m_type='user'))
 then
   return;
 end if;
 m_stmt:='select sys.dbms_standard.sysevent from dual';
 execute immediate m_stmt into m_event;
 m_stmt:='select sys_context(''userenv'',''session_user'') from dual';
 execute immediate m_stmt into m_user;
 m_stmt:='select sys_context(''userenv'',''current_user'') from dual';
 execute immediate m_stmt into m_user1;
 m_stmt:='select sys.dbms_standard.dictionary_obj_owner from dual';
 execute immediate m_stmt into m_owner;
 m_stmt:='select sys.dbms_standard.dictionary_obj_name from dual';
 execute immediate m_stmt into m_name;
 m_stmt:='select sdo_geor_def.getsqltext from dual';
 execute immediate m_stmt into m_o_stmt;
  if (m_owner!='mdsys' and m_owner!='sys' and m_type='table' and m_event='alter')
  then
    m_stmt:='select column_name from dba_tab_columns where owner=:1 and table_name=:2';
    open m_cur for m_stmt using m_owner,m_name;
    loop
      fetch m_cur into m_column;
      exit when m_cur%notfound;
      m_stmt:='select sdo_geor_def.isdropcolumn(:1) from dual';
      execute immediate m_stmt into m_stmt1 using sys.dbms_assert.simple_sql_name(m_column);
      if (trim(m_stmt1)='true')
      then
        m_stmt:='begin sdo_geor_def.doalterdropcolumn(:1,:2,:3); end;';
        execute immediate m_stmt using sys.dbms_assert.schema_name(m_owner),sys.dbms_assert.simple_sql_name(m_name),sys.dbms_assert.simple_sql_name(m_column);
      end if;
    end loop;
  end if;
 if (m_event='drop' and m_type='user')
 then
   m_stmt:='insert into sdo_geor_ddl__table$$ values (1)';
   execute immediate m_stmt;
   commit;
 end if;
 if (m_event='drop' and m_type='table')
 then
   m_stmt:='select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and rdt_table_name=:2';
   execute immediate m_stmt into m_cnt using m_owner,m_name;
   if(m_cnt!=0)
   then
     m_stmt:='select count(*) from sdo_geor_ddl__table$$';
     execute immediate m_stmt into m_cnt;
     if((m_cnt=0)and (m_user!='sys' and m_user!='system' and m_user!='mdsys'and m_owner!='mdsys' and m_owner!='sys'))
     then
        m_stmt:='call mderr.raise_md_error(''md'', ''sdo'', -13391, ''the referenced raster data table(rdt) cannot be dropped.'')';
        execute immediate m_stmt;
     end if;
   end if;
   m_stmt:='insert into sdo_geor_ddl__table$$ values (2)';
   execute immediate m_stmt;
   commit;
 end if;
 if(m_user='sys' or m_user='system' or m_user='mdsys'
   or m_owner='mdsys' or m_owner='sys')
 then
   return;
 end if;
 if (m_event='rename' and m_type='table')
 then
   m_stmt:='select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and rdt_table_name=:2';
   execute immediate m_stmt into m_cnt using m_owner,m_name;
   if(m_cnt!=0)
   then
     m_stmt:='select count(*) from sdo_geor_ddl__table$$';
     execute immediate m_stmt into m_cnt;
     if(m_cnt=0)
     then
        m_stmt:='call mderr.raise_md_error(''md'', ''sdo'', -13391, ''the referenced raster data table(rdt) cannot be renamed directly.'')';
        execute immediate m_stmt;
     end if;
   end if;
 end if;
 if (m_type='trigger' and m_event='drop')
 then
   m_stmt:='select regexp_substr(:1,''grdmltr_. '',1,1,''i'') from dual';
   execute immediate m_stmt into m_var using m_name;
   if(m_var is null)
   then
     return;
   end if;
   m_stmt:='select count(*) from sdo_geor_ddl__table$$';
   execute immediate m_stmt into m_cnt;
   if(m_cnt=0)
   then
     m_stmt:='call mderr.raise_md_error(''md'', ''sdo'', -13391, ''georaster dml triggers cannot be dropped.'')';
     execute immediate m_stmt;
   end if;
 end if;
 if (m_type='trigger' and m_event='alter')
 then
   m_o_stmt:=upper(trim(m_o_stmt));
   if(instr(m_o_stmt,' compile ')>0 or instr(m_o_stmt,' enable ')>0
     or substr(m_o_stmt,length(m_o_stmt)-8,8)=' compile' or substr(m_o_stmt,length(m_o_stmt)-7,7)=' enable' )
   then
     return;
   end if;
   m_stmt:='select regexp_substr(:1,''grdmltr_. '',1,1,''i'') from dual';
   execute immediate m_stmt into m_var using m_name;
   if(m_var is null)
   then
     return;
   end if;
   m_stmt:='select count(*) from sdo_geor_ddl__table$$';
   execute immediate m_stmt into m_cnt;
   if(m_cnt=0)
   then
     m_stmt:='call mderr.raise_md_error(''md'', ''sdo'', -13391, ''georaster dml triggers cannot be altered.'')';
     execute immediate m_stmt;
   end if;
 end if;
 if (m_type='trigger' and m_event='create')
 then
   m_stmt:='select regexp_substr(:1,''grdmltr_. '',1,1,''i'') from dual';
   execute immediate m_stmt into m_var using m_name;
   if(m_var is null)
   then
     return;
   end if;
   m_stmt:='select count(*) from sdo_geor_ddl__table$$';
   execute immediate m_stmt into m_cnt;
   if(m_cnt=0)
   then
     m_stmt:='call mderr.raise_md_error(''md'', ''sdo'', -13391, ''georaster reserved names cannot be used to create regular triggers.'')';
     execute immediate m_stmt;
   end if;
 end if;
 exception
   when others then
     if(sqlcode=-13391)
     then
       m_stmt:=sqlerrm;
       m_stmt:=substr(m_stmt,11);
       m_stmt:='call mderr.raise_md_error(''md'', ''sdo'', -13391,'''||m_stmt||''')';
       execute immediate m_stmt;
     end if;
end;

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           1
fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           1

misses in library cache during parse: 0
optimizer mode: all_rows
parsing user id: 57     (recursive depth: 1)
********************************************************************************

sql id: bcv9qynmu1nv9
plan hash: 1388734953
select sys.dbms_standard.dictionary_obj_type
from
 dual

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          0          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          0          0           2

misses in library cache during parse: 0
optimizer mode: all_rows
parsing user id: 57     (recursive depth: 2)

rows     row source operation
-------  ---------------------------------------------------
      1  fast dual  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

sql id: 12kw3xcxv1qpg
plan hash: 1388734953
select sys.dbms_standard.sysevent
from
 dual

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          0          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          0          0           2

misses in library cache during parse: 0
optimizer mode: all_rows
parsing user id: 57     (recursive depth: 2)

rows     row source operation
-------  ---------------------------------------------------
      1  fast dual  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

sql id: d7y4tdacc7f3j
plan hash: 1388734953
select sys_context('userenv','session_user')
from
 dual

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          0          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          0          0           2

misses in library cache during parse: 0
optimizer mode: all_rows
parsing user id: 57     (recursive depth: 2)

rows     row source operation
-------  ---------------------------------------------------
      1  fast dual  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

sql id: 721d7993vjur9
plan hash: 1388734953
select sys_context('userenv','current_user')
from
 dual

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
parse        1      0.00       0.00          0          0          0           0
execute      1      0.00       0.00          0          0          0           0
fetch        1      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          0          0           1

misses in library cache during parse: 0
optimizer mode: all_rows
parsing user id: 57     (recursive depth: 2)

rows     row source operation
-------  ---------------------------------------------------
      1  fast dual  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

sql id: ar9nmtmd28460
plan hash: 1388734953
select sys.dbms_standard.dictionary_obj_owner
from
 dual

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          0          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          0          0           2

misses in library cache during parse: 0
optimizer mode: all_rows
parsing user id: 57     (recursive depth: 2)

rows     row source operation
-------  ---------------------------------------------------
      1  fast dual  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

sql id: 4a3ccstvk0ssw
plan hash: 1388734953
select sys.dbms_standard.dictionary_obj_name
from
 dual

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          0          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          0          0           2

misses in library cache during parse: 0
optimizer mode: all_rows
parsing user id: 57     (recursive depth: 2)

rows     row source operation
-------  ---------------------------------------------------
      1  fast dual  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

sql id: bcg7084jc4um6
plan hash: 1388734953
select sdo_geor_def.getsqltext
from
 dual

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
parse        1      0.00       0.00          0          0          0           0
execute      1      0.00       0.00          0          0          0           0
fetch        1      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          0          0           1

misses in library cache during parse: 0
optimizer mode: all_rows
parsing user id: 57     (recursive depth: 2)

rows     row source operation
-------  ---------------------------------------------------
      1  fast dual  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

sql id: cjaa80k1hvpc1
plan hash: 626680409
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        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           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           0

misses in library cache during parse: 0
optimizer mode: choose
parsing user id: sys   (recursive depth: 1)

rows     row source operation
-------  ---------------------------------------------------
      0  table access full cdc_change_tables$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=47 card=1)

********************************************************************************

sql id: czybksw5t3gvy
plan hash: 1551139906
select log, sysdate, sysdate 1/86400, 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          1          0           0
fetch        1      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          1          0           0

misses in library cache during parse: 0
optimizer mode: choose
parsing user id: sys   (recursive depth: 1)

rows     row source operation
-------  ---------------------------------------------------
      0  for update  (cr=1 pr=0 pw=0 time=0 us)
      0   buffer sort (cr=1 pr=0 pw=0 time=0 us)
      0    table access cluster mlog$ (cr=1 pr=0 pw=0 time=0 us cost=0 size=64 card=1)
      0     index unique scan i_mlog# (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 634)

********************************************************************************

sql id: drp1rmhz62fcy
plan hash: 3381929193
select inserts, deletes 
from
   sys.mon_mods$  where  obj# = :objn

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: 0
optimizer mode: choose
parsing user id: sys   (recursive depth: 1)

rows     row source operation
-------  ---------------------------------------------------
      1  table access by index rowid mon_mods$ (cr=2 pr=0 pw=0 time=0 us cost=0 size=39 card=1)
      1   index unique scan i_mon_mods$_obj (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 459)

********************************************************************************

sql id: 2j22nc38tjtzs
plan hash: 1245710492
select inserts, deletes 
from
   sys.mon_mods_all$  where  obj# = :objn

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: 0
optimizer mode: choose
parsing user id: sys   (recursive depth: 1)

rows     row source operation
-------  ---------------------------------------------------
      0  table access by index rowid mon_mods_all$ (cr=1 pr=0 pw=0 time=0 us cost=1 size=12 card=1)
      0   index unique scan i_mon_mods_all$_obj (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 461)

********************************************************************************

sql id: 350f5yrnnmshs
plan hash: 0
lock table sys.mon_mods$ in exclusive mode nowait

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: 0
optimizer mode: choose
parsing user id: sys   (recursive depth: 1)
********************************************************************************

sql id: g00cj285jmgsw
plan hash: 315182377
update sys.mon_mods$ set inserts = inserts :ins, updates = updates :upd,
  deletes = deletes :del, flags = (decode(bitand(flags, :flag), :flag,
  flags, flags :flag)), drop_segments = drop_segments :dropseg, timestamp
  = :time
where
 obj# = :objn

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
parse        1      0.00       0.00          0          0          0           0
execute      1      0.00       0.08          0          1          2           1
fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.08          0          1          2           1

misses in library cache during parse: 0
optimizer mode: choose
parsing user id: sys   (recursive depth: 1)

rows     row source operation
-------  ---------------------------------------------------
      0  update  mon_mods$ (cr=1 pr=0 pw=0 time=0 us)
      1   index unique scan i_mon_mods$_obj (cr=1 pr=0 pw=0 time=0 us cost=0 size=87 card=1)(object id 459)

********************************************************************************

sql id: 74anujtt8zw4h
plan hash: 3014793611
select o.owner#, o.name,            o.namespace,    o.obj#,   d.d_timestamp,
  nvl(d.property,0), o.type#, o.subname, d.d_attrs 
from
 dependency$ d, obj$ o   where d.p_obj#=:1  and   (d.p_timestamp=nvl(:2,
  d.p_timestamp) or d.property=2)  and   o.owner#=nvl(:3,o.owner#)  and  
  d.d_obj#=o.obj#  order by o.obj#

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        4      0.00       0.00          0         14          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.00       0.00          0         14          0           2

misses in library cache during parse: 0
optimizer mode: choose
parsing user id: sys   (recursive depth: 1)

rows     row source operation
-------  ---------------------------------------------------
      1  sort order by (cr=7 pr=0 pw=0 time=0 us cost=17 size=308 card=4)
      1   concatenation  (cr=7 pr=0 pw=0 time=0 us)
      1    filter  (cr=7 pr=0 pw=0 time=0 us)
      1     nested loops  (cr=7 pr=0 pw=0 time=0 us)
      1      nested loops  (cr=6 pr=0 pw=0 time=0 us cost=8 size=154 card=2)
      1       table access by index rowid dependency$ (cr=4 pr=0 pw=0 time=0 us cost=4 size=70 card=2)
      1        index range scan i_dependency2 (cr=3 pr=0 pw=0 time=0 us cost=2 size=0 card=4)(object id 107)
      1       index range scan i_obj1 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 36)
      1      table access by index rowid obj$ (cr=1 pr=0 pw=0 time=0 us cost=2 size=42 card=1)
      0    filter  (cr=0 pr=0 pw=0 time=0 us)
      0     nested loops  (cr=0 pr=0 pw=0 time=0 us)
      0      nested loops  (cr=0 pr=0 pw=0 time=0 us cost=8 size=154 card=2)
      0       table access by index rowid dependency$ (cr=0 pr=0 pw=0 time=0 us cost=4 size=70 card=2)
      0        index range scan i_dependency2 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=4)(object id 107)
      0       index range scan i_obj1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 36)
      0      table access by index rowid obj$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=42 card=1)

********************************************************************************

sql id: gsfnqdfcvy33q
plan hash: 2453887050
delete from superobj$
where
 subobj# = :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          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: 0
optimizer mode: choose
parsing user id: sys   (recursive depth: 1)

rows     row source operation
-------  ---------------------------------------------------
      0  delete  superobj$ (cr=1 pr=0 pw=0 time=0 us)
      0   index unique scan i_superobj1 (cr=1 pr=0 pw=0 time=0 us cost=0 size=26 card=1)(object id 72)

********************************************************************************

sql id: 3kywng531fcxu
plan hash: 2667651180
delete from tab_stats$
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          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: 0
optimizer mode: choose
parsing user id: sys   (recursive depth: 1)

rows     row source operation
-------  ---------------------------------------------------
      0  delete  tab_stats$ (cr=1 pr=0 pw=0 time=0 us)
      0   index unique scan i_tab_stats$_obj# (cr=1 pr=0 pw=0 time=0 us cost=0 size=13 card=1)(object id 430)

********************************************************************************

sql id: b5cr4hhndmbuf
plan hash: 2918346288
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.01          0          3          2           1
fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          0          3          2           1

misses in library cache during parse: 0
optimizer mode: choose
parsing user id: sys   (recursive depth: 1)

rows     row source operation
-------  ---------------------------------------------------
      0  update  tab$ (cr=3 pr=0 pw=0 time=0 us)
      1   table access cluster tab$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=137 card=1)
      1    index unique scan i_obj# (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 3)

********************************************************************************

sql id: gx87sann8asuf
plan hash: 0
begin
  aw_trunc_proc(ora_dict_obj_type, ora_dict_obj_name,
  ora_dict_obj_owner);
end;

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           1
fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           1

misses in library cache during parse: 0
optimizer mode: choose
parsing user id: sys   (recursive depth: 1)
********************************************************************************

declare
 type attrs_cur is ref cursor;
 m_cur       attrs_cur;
 m_event   varchar2(512);
 m_user    varchar2(512);
 m_owner   varchar2(512);
 m_user1   varchar2(512);
 m_type    varchar2(512);
 m_name    varchar2(5120);
 m_column  varchar2(5120);
 m_cnt     number;
 m_stmt    varchar2(512);
 m_ret     varchar2(3000);
 m_ret1     varchar2(512);
 m_o_stmt varchar2(5120);
begin
 m_stmt:='select sys.dbms_standard.dictionary_obj_type from dual';
 execute immediate m_stmt into m_type;
 if(not (m_type='table' or m_type='trigger' or m_type='user'))
 then
   return;
 end if;
 m_stmt:='select sys.dbms_standard.sysevent from dual';
 execute immediate m_stmt into m_event;
 m_stmt:='select sys_context(''userenv'',''session_user'') from dual';
 execute immediate m_stmt into m_user;
 m_stmt:='select sys.dbms_standard.login_user  from dual';
 execute immediate m_stmt into m_user1;
 m_stmt:='select sys.dbms_standard.dictionary_obj_owner from dual';
 execute immediate m_stmt into m_owner;
 m_stmt:='select sys.dbms_standard.dictionary_obj_name from dual';
 execute immediate m_stmt into m_name;
 if (m_owner!='mdsys' and m_owner!='sys' and m_type='table' and m_event='create')
 then
   m_stmt:='select sdo_geor_def.listallgeorasterfieldsstr(:1,:2) from dual';
   execute immediate m_stmt  into m_ret  using sys.dbms_assert.schema_name(m_owner),sys.dbms_assert.simple_sql_name(m_name);
   m_ret:=trim(m_ret);
   while (length(m_ret)!=0) loop
     if (instr(m_ret,' $$__## ')!=0)
     then
       m_ret1:=trim(substr(m_ret,1,instr(m_ret,' $$__## ')-1));
       m_ret:=trim(substr(m_ret,instr(m_ret,' $$__## ') 8));
     else
       m_ret1:=trim(m_ret);
       m_ret:='';
     end if;
     m_stmt:='begin sdo_geor_utl.createdmltrigger(:1,:2); end;';
    execute immediate m_stmt using m_owner||'.'||m_name,sys.dbms_assert.qualified_sql_name(m_ret1);
   end loop;
   return;
 end if;
  if (m_name!='mdsys' and m_type='user' and m_event='drop')
  then
     m_stmt:='call sdo_geor_def.dodropuserandtable()';
     execute immediate m_stmt;
  end if;
  if (m_owner!='mdsys' and m_owner!='sys' and m_type='table' and m_event='drop')
  then
     m_stmt:='call sdo_geor_def.dodropuserandtable()';
     execute immediate m_stmt;
  end if;
  if (m_owner!='mdsys' and m_owner!='sys' and m_type='table' and m_event='truncate')
  then
     m_stmt:='call sdo_geor_def.dotruncatetable()';
     execute immediate m_stmt;
  end if;
  if (m_owner!='mdsys' and m_owner!='sys' and m_type='table' and m_event='alter')
  then
     m_stmt:='call sdo_geor_def.doalterrenametable()';
     execute immediate m_stmt;
  end if;
  if (m_owner!='mdsys' and m_owner!='sys' and m_type='table' and m_event='rename')
  then
     m_stmt:='call sdo_geor_def.dorenametable()';
     execute immediate m_stmt;
  end if;
 if (m_event='drop' and m_type='table')
 then
   m_stmt:='delete from sdo_geor_ddl__table$$ where id=2';
   execute immediate m_stmt;
 end if;
 if (m_type='user' and m_event='drop')
 then
   m_stmt:='delete from sdo_geor_ddl__table$$';
   execute immediate m_stmt;
 end if;
 exception
   when others then
     if(sqlcode=-13391)
     then
       m_stmt:=sqlerrm;
       m_stmt:=substr(m_stmt,11);
       m_stmt:='call mderr.raise_md_error(''md'', ''sdo'', -13391,'''||m_stmt||''')';
       execute immediate m_stmt;
     end if;
end;

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           1
fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           1

misses in library cache during parse: 0
optimizer mode: all_rows
parsing user id: 57     (recursive depth: 1)
********************************************************************************

sql id: 7dwyqv9sawkyb
plan hash: 1388734953
select sys.dbms_standard.login_user 
from
 dual

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
parse        1      0.00       0.00          0          0          0           0
execute      1      0.00       0.00          0          0          0           0
fetch        1      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          0          0           1

misses in library cache during parse: 0
optimizer mode: all_rows
parsing user id: 57     (recursive depth: 2)

rows     row source operation
-------  ---------------------------------------------------
      1  fast dual  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

sql id: 76p07nh763cdv
plan hash: 0
call sdo_geor_def.dotruncatetable()

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        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.00          0          0          0           0

misses in library cache during parse: 0
optimizer mode: all_rows
parsing user id: 57     (recursive depth: 2)
********************************************************************************

sql id: gt6jkrh79f7ns
plan hash: 3216348639
select count(*)
from
 sdo_geor_sysdata_table  where sdo_owner = :1 and georaster_table_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          1          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          1          0           1

misses in library cache during parse: 0
optimizer mode: all_rows
parsing user id: 57     (recursive depth: 3)

rows     row source operation
-------  ---------------------------------------------------
      1  sort aggregate (cr=1 pr=0 pw=0 time=0 us)
      0   table access by index rowid sdo_geor_sysdata_table (cr=1 pr=0 pw=0 time=0 us cost=0 size=36 card=1)
      0    index range scan unique_georasters (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 65914)

********************************************************************************

sql id: 2abfv4wqwk3s9
plan hash: 144376333
select count(*)
from
 mdsys.sdo_tin_pc_sysdata_table  where sdo_owner = :1 and table_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          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          0          0           1

misses in library cache during parse: 0
optimizer mode: all_rows
parsing user id: 57     (recursive depth: 3)

rows     row source operation
-------  ---------------------------------------------------
      1  sort aggregate (cr=0 pr=0 pw=0 time=0 us)
      0   index range scan sdo_tin_pc_idx (cr=0 pr=0 pw=0 time=0 us cost=0 size=36 card=1)(object id 66392)

********************************************************************************

sql id: 2rc2s1cr5crg6
plan hash: 654814892
select count(*)
from
 all_views  where owner = 'mdsys' and view_name = 'rdf_model$'

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: 0
optimizer mode: all_rows
parsing user id: 57     (recursive depth: 3)

rows     row source operation
-------  ---------------------------------------------------
      1  sort aggregate (cr=5 pr=0 pw=0 time=0 us)
      0   filter  (cr=5 pr=0 pw=0 time=0 us)
      0    nested loops  (cr=5 pr=0 pw=0 time=0 us cost=4 size=85 card=1)
      0     nested loops  (cr=5 pr=0 pw=0 time=0 us cost=3 size=63 card=1)
      0      nested loops  (cr=5 pr=0 pw=0 time=0 us cost=3 size=58 card=1)
      1       table access by index rowid user$ (cr=2 pr=0 pw=0 time=0 us cost=1 size=18 card=1)
      1        index unique scan i_user1 (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 46)
      0       index range scan i_obj5 (cr=3 pr=0 pw=0 time=0 us cost=2 size=40 card=1)(object id 40)
      0      index unique scan i_view1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=5 card=1)(object id 75)
      0     index range scan i_user2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=22 card=1)(object id 47)
      0    nested loops  (cr=0 pr=0 pw=0 time=0 us cost=2 size=21 card=1)
      0     index range scan i_objauth1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)(object id 62)
      0     fixed table full x$kzsro (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
      0    fixed table full x$kzspr (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
      0    nested loops  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
      0     index full scan i_user2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)
      0     index range scan i_obj4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=9 card=1)(object id 39)

********************************************************************************

sql id: 06nvwn223659v
plan hash: 0
alter session set events '10046 trace name context off'

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: 0
parsing user id: 85 

 

********************************************************************************

overall totals for all non-recursive statements

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
parse        2      0.01       0.05          0          6          0           0
execute      2      0.04       0.01          0         45          9           0
fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.06       0.07          0         51          9           0

misses in library cache during parse: 1

elapsed times include waiting on following events:
  event waited on                             times   max. wait  total waited
  ----------------------------------------   waited  ----------  ------------
  sql*net message to client                       2        0.00          0.00
  sql*net message from client                     2        7.99         13.14

overall totals for all recursive statements

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
parse       37      0.00       0.05          0          0          0           0
execute     38      0.01       0.11          0          7          4           6
fetch       29      0.00       0.00          0         44          0          24
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      104      0.01       0.17          0         51          4          30

misses in library cache during parse: 1

   22  user  sql statements in session.
   18  internal sql statements in session.
   40  sql statements in session.
********************************************************************************
trace file: c:\app\administrator\diag\rdbms\xyz\xyz\trace\xyz_ora_7816.trc
trace file compatibility: 11.1.0.7
sort options: default

       1  session in tracefile.
      22  user  sql statements in trace file.
      18  internal sql statements in trace file.
      40  sql statements in trace file.
      32  unique sql statements in trace file.
     686  lines in trace file.
       5  elapsed seconds in trace file.