如何查找疯狂增长arch的进程
· 作者 小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接: http://www.oracleblog.cn/working ... h-increase-crazily/ ·
--------------------------------------------------------------------------------
如果我们要找到是什么进程引起了arch疯狂增长,我们可以用这样的方法来查找:
我们假设我们要生成一个大表,用ctas的方法来做,由于是ddl语句,我们在v$sqlarea里面找不到对应的语句,但是我们发现这个session产生大量的arch,那么我们怎么去找这个session呢?
[php]
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;
[/php]
我们这边可以看到我当前的sid是11,我们用ctas创建一个大表x3。此时arch开始飞快的增长……
由于一般这样的故障,都是由于一个session引发了大量的归档日志,在这里我们用v$sestat来看在单位时间内哪个session生成的redo量最多,哪个session切换redo次数最多。
[php]
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>
[/php]
我们看到sid为11的session产生了大量的redo,因此使得arch疯狂的增长。在此,我们可以kill掉这个罪魁祸首了。
由于我们的语句是ddl语句,因此我们如果去找对应的sql,我们会发现其实里面是在操作数据字典:
[php]
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>
[/php]
我们虽然不能找到sql,但是我们已经能发现这个session,kill之:)
如果问题已经发生了,我们只能用logmnr来分析arch了,但是在这里ctas的ddl在arch中也是类似对数据字典的操作:
[php]
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
.
[/php]
[本帖最后由 jimmyhe1981 于 2008-1-21 23:28 编辑]