admin 管理员组

文章数量: 1086019

Encourter the Potential blocker issue!

Some one said it is bug:

Bug 6495842: GES POTENTIAL BLOCKER ON RESOURCE TX CAUSE RAC TO HANG
Bug 7372971: RAC : HANG DUE TO GES DEADLOCK AND POTENTIAL BLOCKER

Generate the ADDM report and suggested to change "db_writer_processes" parameter, it was "4" currently which cause the free buffer waits event high.

After checking the alert logs I found the Potential blocker and Mview failure SQL always happened at same time or in the following consecutive time.

Notice the ORA-01555.

I check the unto space usage but find it only 29% full.

Then I move to check the undo retention and find it is only 900.

suspect some restart the instance with the default parameter file.

 

I use the follwoing SQL

SQL> select max(maxquerylen)/3600 "TIME IN HRS" from v$undostat;

TIME IN HRS
-----------
 2.94944444

SQL> select max(maxquerylen) "TIME IN HRS" from v$undostat;

TIME IN HRS
-----------
      10618

The biggest SQL is almost 3hours long.

So I 'd like to change the Mview refresh frequency to 1 hours to make the refresh done in shorter time and  change the  undo_retention from 900 to 18000,Hope this action can avoid the error ORA-01555.

Do not find the error again after change.

Extract from the alert log.

Tue Nov  2 16:59:25 2010
GES: Potential blocker (pid=19068) on resource TX-00870007-001B3C18;
enqueue info in file /opt/app/oracle/admin/RPTS/bdump/rpts3_lmd0_15052.trc and DIAG trace file
Tue Nov  2 16:59:25 2010
GES: Potential blocker (pid=19171) on resource FB-00000013-0C03999A;
enqueue info in file /opt/app/oracle/admin/RPTS/udump/rpts3_ora_19068.trc and DIAG trace file
Tue Nov  2 17:18:16 2010
ORA-01555 caused by SQL statement below (SQL ID: fd958md7mtzqa, Query Duration=6475 sec, SCN: 0x07b2.408c8ec2):
Tue Nov  2 17:18:16 2010
SELECT /*+ */ "A2"."IDENTIFIER","A2"."FIRSTOCCURRENCE","A2"."LASTOCCURRENCE","A2"."CLEAREDTIMESTAMP","A2"."INTERNALTICKETNUM","A2"."TALLY","A2"."SUMMARY","A2"."ALERTKEY","A2"."AGENT","A2"."IPADDRESS","A2"."CUSTOMERNAME","A2"."NODE","A2"."MANAGER","A2"."ALERTGROUP","A2"."GRADE","A2"."DEVSTATUS","A2"."ORIGINALMESSAGE","A2"."DELETEDAT","A2"."ORIGINALSEVERITY","A2"."SERVERNAME","A2"."SERVERSERIAL","A2"."CUSTOMERSTATUS","A2"."MANAGINGHOST","A2"."MAINTFLAG","A2"."MAST","A2"."STARTTIME","A2"."ENDTIME","A2"."CTARECEIVETIME","A2"."OWNERNAME" FROM "REPORTER"."REPORTER_STATUS" "A2", (SELECT DISTINCT "A3"."SERVERNAME" "SERVERNAME","A3"."SERVERSERIAL" "SERVERSERIAL" FROM "REPORTER"."MLOG$_REPORTER_STATUS" "A3" WHERE "A3"."SNAPTIME$$">:1 AND "A3"."DMLTYPE$$"<>'D') "A1" WHERE "A2"."SERVERNAME"="A1"."SERVERNAME" AND .......

本文标签： potential Blocker Issue

版权声明：本文标题：Potential blocker issue! 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.roclinux.cn/b/1738259914a1952467.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。