Oracle understands the relationship between DDL operations and REDO

Directory

• Understanding the relationship between DDL operations and REDO
  • Will DDL produce redo
  • Analyze Create and drop by 10046 Trace
  • If the drop fails, the redo change

Understanding whether DDL operations are related to REDO DDL yields REDO

SQL to use:

---查看redo的大小SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;NAME                                      VALUE-------------------------------------- ----------redo size                                   0        

---Create a table to see the resulting redo size

SQL> create table kyeup_tb1 as select * from v$datafile;Table created.SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;NAME                    VALUE------------------ ----------redo size               61072  

---from the above to see when creating the table redo size is 61072 bytes, then delete this table will produce redo how much?

SQL> drop table kyeup_tb1;Table dropped.SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;NAME                    VALUE-----------------   ----------redo size              101420

Redo size produced by---drop table: 101420-61072 = 40348

The drop TABLE statement produces bytes redo data, less than create table;

Here we need to look at the DDL statement execution process.

Analyze Create and drop by 10046 Trace

It is possible that Oracle needs to insert data into the base table when it is create TABLE, while the drop table requires delete/update data

Let's use 10046来 to track what the Create table and drop table do.

SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;NAME                                      VALUE---------------------------------------------------------------- ----------redo size                                 0SQL> oradebug setmypid;Statement processed.SQL> oradebug tracefile_name;/oracle/app/oracle/diag/rdbms/kyeupdbfs/kyeupdbfs/trace/kyeupdbfs_ora_55251.trcSQL> oradebug event 10046 trace name context forever,level 1;Statement processed.SQL> create table kyeuptb1(id int,name varchar2(12));Table created.SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;NAME                                      VALUE---------------------------------------------------------------- ----------redo size                                  8880

---profiling trace

[[email protected] ~]# cat/oracle/app/oracle/diag/rdbms/kyeupdbfs/kyeupdbfs/trace/kyeupdbfs_ora_55251.trc |   Egrep ' insert|update|delete|create ' CREATE TABLE kyeuptb1 (ID m_stmt:= ' INSERT into sdo_geor_ddl__table$$ values (1) ';     m_stmt:= ' INSERT into sdo_geor_ddl__table$$ values (2) '; m_stmt:= ' Call Mderr.raise_md_error (' MD ', ' SDO ', -13391, ' georaster reserved names cannot is used to create regular TR Iggers. ') '; Insert INTO obj$ (owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj# , Flags,oid$,spare1,spare2,spare3) VALUES (: 1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18) insert Into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups, CACHEHINT,HWMINCR, Spare1, Scanhint, Bitmapranges) VALUES (: 1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, DECODE (: 17,0,null,:17),: 18,:19) insert INTO tab$ (Obj#,ts#,file#,block#,bobj#,tab#,intcols,kernelcols,clucols, Audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime,samplesize,cols,property, DEGREE,INSTANCES,DATAOBJ#,AVGSPC_FLB,FLBCNT,TRIGFLAG,SPARE1,SPARE6) VALUES (: 1,:2,:3,:4,decode (: 5,0,null,:5), Decode (: 6,0,null,:6),: 7,:8,decode (: 9,0,null,:9),: 10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24, : 25,decode (: 26,1,null,:26), decode (: 27,1,null,:27),: 28,:29,:30,:31,:32,:33) insert INTO col$ (obj#,name,intcol#, Segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property, Charsetid,charsetform,spare1,spare2,spare3) VALUES (: 1,:2,:3,:4,:5,:6,decode (: 5,182/*dtyiym*/,:7,183/*dtyids*/, : 7,decode (: 7,0,null,:7)), Decode (: 5,2,decode (: 8,-127/*maxsb1minal*/,null,:8), 178,:8,179,:8,180,:8,181,:8,182, : 8,183,:8,231,:8,null),: 9,0,:10,:11,decode (: 12,0,null,:12),: 13,:14,:15,:16,:17,:18,:19,:20) m_stmt:= ' Begin SDO_ Geor_utl.createdmltrigger (: 1,:2);   end; ';   m_stmt:= ' Delete from sdo_geor_ddl__table$$ where id=2 '; m_stmt:= ' Delete from Sdo_geor_ddl__table$$ '; update seg$ set Type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user #=:11,iniexts=:12,lists=decode (: 65535, NULL,:), Groups=decode (: +, 65535, NULL,:), cachehint=:15, hwmincr=:16 , Spare1=decode (: 17,0,null,:17), scanhint=:18, bitmapranges=:19 where Ts#=:1 and File#=:2 and Block#=:3

Insert,update and so on when---create TABLE, and now start tracking down the drop table (exit to Redo)

SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;NAME                                      VALUE---------------------------------------------------------------- ----------redo size                                 0SQL> oradebug setmypid;Statement processed.SQL> oradebug tracefile_name;/oracle/app/oracle/diag/rdbms/kyeupdbfs/kyeupdbfs/trace/kyeupdbfs_ora_55296.trcSQL> oradebug event 10046 trace name context forever,level 1;Statement processed.SQL> drop table kyeuptb1;Table dropped.SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name=‘redo size‘;NAME                                      VALUE---------------------------------------------------------------- ----------redo size                                  8552

---drop produces less redo than create; analyze trace

[[email protected] ~]# cat/oracle/app/oracle/diag/rdbms/kyeupdbfs/kyeupdbfs/trace/kyeupdbfs_ora_55296.trc |  Egrep ' insert|update|delete|create ' need use Delete_topo_geometry_layer () to deregister table ' m_stmt:= ' Insert   into sdo_geor_ddl__table$$ values (1) ';     m_stmt:= ' INSERT into sdo_geor_ddl__table$$ values (2) '; m_stmt:= ' Call Mderr.raise_md_error (' MD ', ' SDO ', -13391, ' georaster reserved names cannot is used to create regular TR Iggers. ') '; INSERT into sdo_geor_ddl__table$$ VALUES (2) Select Decode (u.type#, 2, U.ext_username, u.name), O.name, t.update$, t . insert$, t.delete$, t.enabled, Decode (Bitand (T.property, 8192), 8192, 1, 0), Decode (Bitand (T.property, 65536 ), 65536, 1, 0), Decode (Bitand (T.property, 131072), 131072, 1, 0), (select O.name from obj$ o where O . obj# = U.spare2 and o.type# =57) from sys.obj$ O, sys.user$ u, sys.trigger$ T, sys.obj$ Bo where t.baseobject=bo.obj# an D bo.name =: 1 and Bo.spare3 =: 2 and bO.namespace = 1 and t.obj#=o.obj# and o.owner#=u.user# and o.type# = Bitand (property,16) =0 and Bitand (property,8) =0 ORDER by O.obj#delete from Object_usage where obj# in (select a.obj# from Object_usage A, ind$ b where a.obj# = B.ob J # and b.bo# =: 1) Delete from sys.cache_stats_1$ where dataobj# =: 1delete com$ where obj#=:1delete from hist_head$ where obj# =: 1delete from compression$ where Obj#=:1 m_stmt:= ' Begin Sdo_geor_utl.createdmltrigger (: 1,:2);   end; ';   m_stmt:= ' Delete from sdo_geor_ddl__table$$ where id=2 '; m_stmt:= ' Delete from sdo_geor_ddl__table$$ ';d elete from sdo_geor_ddl__table$$ where Id=2delete from col$ where obj#=  : 1delete from icol$ where Bo#=:1delete from icoldep$ where obj# in (select obj# from ind$ where bo#=:1) Delete from jijoin$ where obj# in (select obj# from jijoin$ where tab1obj# =: 1 or tab2obj# =: 1) Delete from jirefreshsql$ where iobj# in ( Select iobj# from jirefreshsql$ where tobj# =: 1) Delete from ccol$ where obj#=:1delete from ind$ where Bo#=:1delete from cdef$ where Obj#=:1delete ecol$ where tabobj# =: 1delete from tab$ where obj#=:1delete from idl_ub1$ wh  Ere obj#=:1 and Part=:2delete from idl_char$ where Obj#=:1 and Part=:2delete from idl_ub2$ where Obj#=:1 and Part=:2delete From idl_sb4$ where Obj#=:1 and Part=:2delete from ncomp_dll$ where obj#=:1 returning dllname into:2delete coltype$ wher E Obj#=:1delete from subcoltype$ where Obj#=:1delete ntab$ where Obj#=:1delete lob$ where Obj#=:1delete refcon$ where obj# =:1delete from opqtype$ where Obj#=:1delete from cdef$ where Obj#=:1delete from objauth$ where obj#=:1delete from obj$ whe Re obj# =: 1update seg$ set Type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11, Iniexts=:12,lists=decode (: 65535, NULL,:), Groups=decode (: +, 65535, NULL,:), cachehint=:15, Hwmincr=:16, Spare1=decode (: 17,0,null,:17), scanhint=:18, bitmapranges=:19 where Ts#=:1 and File#=:2 and Block#=:3delete from seg$ where Ts#=:1 and File#=:2 and Block#=:3

If the drop fails, the redo change

Sql> Select B.name,a.value from V$mystat a,v$statname b where a.statistic#=b.statistic# and b.name= ' redo size '; name VALUE--------------------------------------------------------------------------Redo Siz e 0sql> oradebug setmypid; Statement processed. Sql> Oradebug tracefile_name;/oracle/app/oracle/diag/rdbms/kyeupdbfs/kyeupdbfs/trace/kyeupdbfs_ora_55343. Trcsql> oradebug Event 10046 Trace name context Forever,level 1; Statement processed. sql> drop table Kyeuptb111;drop table kyeuptb111 *error at line 1:ora-00942:table or view does not existsql& Gt                                      Select B.name,a.value from V$mystat a,v$statname b where a.statistic#=b.statistic# and b.name= ' redo size '; name                               VALUE--------------------------------------------------------------------------Redo Size 384sql> CREATE table aa;create table AA *error at line 1:ora-00906:miSsing left parenthesissql> Select B.name,a.value from V$mystat a,v$statname b where a.statistic#=b.statistic# and B.nam E= ' redo size '; NAME VALUE-------------------------------------------------------------  -------------Redo size 384sql>

Redo is not generated when create fails, but drop fails to produce redo (insert into occurs when deleted).

---analysis is as follows

[[email protected] ~]# cat /oracle/app/oracle/diag/rdbms/kyeupdbfs/kyeupdbfs/trace/kyeupdbfs_ora_55343.trc |egrep ‘insert|update|delete|create‘         ‘Need use delete_topo_geometry_layer() to deregister table ‘   m_stmt:=‘insert into sdo_geor_ddl__table$$ values (1)‘;   m_stmt:=‘insert into sdo_geor_ddl__table$$ values (2)‘;     

Oracle understands the relationship between DDL operations and REDO