如果我们要找到是什么进程引起了arch疯狂增长，我们可以用这样的方法来查找：
我们假设我们要生成一个大表，用ctas的方法来做，由于是ddl语句，我们在v$sqlarea里面找不到对应的语句，但是我们发现这个session产生大量的arch，那么我们怎么去找这个session呢？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> SQL> select distinct sid from v$mystat; SID ---------- 11 SQL> create table x1 tablespace ts_big as select rownum as x1_num,t.* from dba_source t 2 / 表已创建。 SQL> create table x2 tablespace ts_big as select rownum as x1_num,t.* from dba_source t 2 / 表已创建。 SQL> SQL> create table x3 tablespace ts_big as select x1.* from x1,x2; |
我们这边可以看到我当前的sid是11，我们用ctas创建一个大表x3。此时arch开始飞快的增长……
由于一般这样的故障，都是由于一个session引发了大量的归档日志，在这里我们用v$sestat来看在单位时间内哪个session生成的redo量最多，哪个session切换redo次数最多。
注：statname中的两个参数说明：
redo size：Total amount of redo generated in bytes；
redo entries：Number of times a redo entry is copied into the redo log buffer
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
SQL> create table t1 as select sid,value,NAME,sysdate as chkdt from v$sesstat a,v$statname b where a.STATISTIC#=b.STATISTIC# and b.CLASS=2; ####### 过一段时间后，我们再次截取关于每个session关于redo的信息（v$statname.class=2表示针对redo）#### SQL> create table t2 as select sid,value,NAME,sysdate as chkdt from v$sesstat a,v$statname b where a.STATISTIC#=b.STATISTIC# and b.CLASS=2; ###### 我们查看是哪个sid在单位时间内生成的redo最多 ########### SQL> select t1.SID,t1.NAME,sum((t2.VALUE-t1.VALUE)/(t2.CHKDT-t1.CHKDT)) from t1,t2 where t1.sid=t2.sid and t1.name=t2.name and t1.name in ('redo size','redo entries') group by t1.sid,t1.name order by 2,3; SID NAME SUM((T2.VALUE-T1.VALUE)/(T2.CHKDT-T1.CHKDT)) ---------- ---------------------------------------------------------------- -------------------------------------------- 1 redo entries 0 3 redo entries 0 4 redo entries 0 7 redo entries 0 10 redo entries 0 16 redo entries 0 9 redo entries 0 6 redo entries 0 5 redo entries 0 2 redo entries 34560 12 redo entries 72000 8 redo entries 383040 11 redo entries 12876480 1 redo size 0 3 redo size 0 4 redo size 0 6 redo size 0 7 redo size 0 10 redo size 0 9 redo size 0 16 redo size 0 5 redo size 0 2 redo size 4308480 12 redo size 38718720 8 redo size 82702080 11 redo size 9.6422E+10 已选择26行。 SQL> |
我们看到sid为11的session产生了大量的redo，因此使得arch疯狂的增长。在此，我们可以kill掉这个罪魁祸首了。
由于我们的语句是ddl语句，因此我们如果去找对应的sql，我们会发现其实里面是在操作数据字典：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> select sql_text from v$sqlarea a,v$session b where a.address=b.sql_address and b.sid=11; SQL_TEXT -------------------------------------------------------------------------------- insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,o ffset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,chars etform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,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) SQL> SQL> |
我们虽然不能找到sql，但是我们已经能发现这个session，kill之：）
如果问题已经发生了，我们只能用logmnr来分析arch了，但是在这里ctas的ddl在arch中也是类似对数据字典的操作：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
SQL> select SQL_REDO,OPERATION,seg_name,SEG_OWNER from logmnr1; SQL_REDO -------------------------------------------------------------------------------- OPERATION -------------------------------- SEG_NAME -------------------------------------------------------------------------------- SEG_OWNER -------------------------------- insert into "UNKNOWN"."OBJ# 30361"("COL 1","COL 2","COL 3","COL 4","COL 5","COL 6") values (HEXTORAW('c3056005'),HEXTORAW('524d414e'),HEXTORAW('44424d535f524356 4d414e'),HEXTORAW('5041434b41474520424f4459'),HEXTORAW('c21925'),HEXTORAW('20202 0202020414e4420286e65656473746279206973204e554c4c204f520a')); DIRECT INSERT insert into "UNKNOWN"."OBJ# 30361"("COL 1","COL 2","COL 3","COL 4","COL 5","COL 6") values (HEXTORAW('c3056006'),HEXTORAW('524d414e'),HEXTORAW('44424d535f524356 4d414e'),HEXTORAW('5041434b41474520424f4459'),HEXTORAW('c21926'),HEXTORAW('20202 020202020202020206e766c2869735f7374616e6462792c20274e2729203d206465636f6465286e6 56564737462792c2054525545232c202759272c20274e2729290a')); DIRECT INSERT insert into "UNKNOWN"."OBJ# 30361"("COL 1","COL 2","COL 3","COL 4","COL 5","COL 6") values (HEXTORAW('c3056007'),HEXTORAW('524d414e'),HEXTORAW('44424d535f524356 4d414e'),HEXTORAW('5041434b41474520424f4459'),HEXTORAW('c21927'),HEXTORAW('20202 0204f5244455220425920746872656164232c206c6f775f73636e2c20616c5f7374616d702064657 3633b0a')); DIRECT INSERT insert into "UNKNOWN"."OBJ# 30361"("COL 1","COL 2","COL 3","COL 4","COL 5","COL 6") values (HEXTORAW('c3056008'),HEXTORAW('524d414e'),HEXTORAW('44424d535f524356 4d414e'),HEXTORAW('5041434b41474520424f4459'),HEXTORAW('c21928'),HEXTORAW('2d2d2 05468697320637572736f722069732075736564206279206b726d6b646d7228292e20206b726d6b6 46d72282920757365730a')); DIRECT INSERT …… |
|
1 2 3 4 |
SQL> select object_name from dba_objects where object_id='30361'; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------ X3 TABLE |