Potential blocker issue!

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 .......