如何查找疯狂增长arch的进程

· 作者 小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接: http://www.oracleblog.cn/working-case/how-to-find-out-arch-increase-crazily/

如果我们要找到是什么进程引起了arch疯狂增长，我们可以用这样的方法来查找：

我们假设我们要生成一个大表，用ctas的方法来做，由于是ddl语句，我们在v$sqlarea里面找不到对应的语句，但是我们发现这个session产生大量的arch，那么我们怎么去找这个session呢？

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

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，我们会发现其实里面是在操作数据字典：

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中也是类似对数据字典的操作：

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
 
……
SQL> select object_name from dba_objects where object_id='30361'; 
OBJECT_NAME                    OBJECT_TYPE                         
------------------------------ ------------------                 
X3                             TABLE

· 【文章发布信息】发表于： 2008-01-21 @ 22:39:27 · ||分类： ..experience, Working case

留条评论