Slow Apply on Logical Standby due to SYS.AUD$


Struck a problem today with Logical Standby due to SYS.AUD$.

References

Background

We are using a Logical Standby database for reporting jobs. The database software is 12.1.0.2.

The apply rate was hovering around 0 MB/sec.

Investigation

ADDM Report

The ADDM report showed:

Finding 1: Unusual "Other" Wait Event
Impact is .29 active sessions, 87.35% of total activity.
--------------------------------------------------------
Wait event "Data Guard server operation completion" in wait class "Other" was 
consuming significant database time.
 
   Recommendation 1: Application Analysis
   Estimated benefit is .29 active sessions, 87.35% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the cause for high "Data Guard server operation completion" 
      waits. Refer to Oracle's "Database Reference" for the description of 
      this wait event.

However, Data Guard Server Operation Completion In Top Wait Events in 12c (Doc ID 2121610.1) said that:

This wait event as a top wait event is not a cause of concern and can be ignored. RFS is waiting for “Data Guard server operation completion” which is “Used to sleep before re-checking for disk I/O completion before stalling on the network for the next request”.

Emphasis Mine

Active Session History (ASH) Report

In this case, the ASH report gave more useful information:

SQL ID Plan Hash Executions % Activity Event % Event Top Row Source % Row Source SQL Text
1ggjcdq355dxs 444042542 285 13.59 CPU + Wait for CPU 13.59 TABLE ACCESS – FULL 13.59 update /*+ streams restrict_al…

Partial text of SQL ID 1ggjcdq355dxs was:

update /*+ streams restrict_all_ref_cons */
   "SYS"."AUD$" p
   set
     "ACTION#"=decode(:1, 'N', "ACTION#", :2),
	 "AUDITID"=decode(:3, 'N', "AUDITID", :4),
	 "AUTH$GRANTEE"=decode(:5, 'N', "AUTH$GRANTEE", :6),

And I found a hit straightaway in Slow Performance In Logical Standby Database Due To Lots Of Activity On Sys.Aud$ (Doc ID 862173.1).

Solution

Slow Performance In Logical Standby Database Due To Lots Of Activity On Sys.Aud$ (Doc ID 862173.1) said to run the following commands (we do not want to mix audit records from the primary and logical standby):

alter database stop logical standby apply;
BEGIN
  dbms_logstdby.skip('DML','SYS','AUD$');
  dbms_logstdby.skip('SCHEMA_DDL','SYS','AUD$');
END;
/
alter database start logical standby apply;

The apply rate jumped to 8 MB/sec. Still not good, but much better than zero (0).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s