Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Logical Standby Issues (cont.)

RE: Logical Standby Issues (cont.)

From: Rich Amick <>
Date: Thu, 10 Aug 2006 15:53:26 -0700
Message-ID: <>


Even if you totally "fix" the latch contention during the mining, you'll only decrease the time it takes by ~20%. Seems to me that CPU time should be the first target to try to reduce. (Trying to tune this outside of Oracle code fixes)

What are the values of 'parse time cpu', 'reloads', 'db block gets', 'consistent gets', 'physical reads direct'?

It might also be useful to get AWR reports at 5 minute intervals during both the mining and the apply processes. Then compare the reports over time to try to discern why you are seeing the consistent slowdown.

-----Original Message-----

From: Mark Strickland [] Sent: Thursday, August 10, 2006 3:34 PM
To: Rich Amick
Subject: Re: Logical Standby Issues (cont.)

Top 5 Timed Events while Log Miner was, er, mining were:

Event	                            Waits   Time(s)	Percent Total DB
Time	Wait Class

CPU time 18,364 78.76 latch free 10,740 4,853 20.81 Other Queue Monitor Task Wait 57 274 1.17 Other log file parallel write 7,898 34 .14 System I/O reliable message 331 27 .12 Other

After Log Mining completed and while SQL Apply was continuing, the Top 5 Timed Events were:

Event	                            Waits   Time(s)	Percent Total DB
Time	Wait Class

CPU time 8,118 53.64 Queue Monitor Task Wait 52 244 1.61 Other log file parallel write 6,624 21 .14 System I/O reliable message 484 19 .12 Other db file parallel write 1,234 12 .08 System I/O

Log Miner was busy for the first 3 hours of the test. After that, the test ran for almost 2-1/2 hours longer before completing. The activity of Log Miner with its latch contention didn't seem to interfere much with SQL Apply. The transactions/minute steadily went down throughout the course of the test. There wasn't a sudden speed-up after Log Miner finished.

My experience from Oracle7 with contention for the shared pool latch that actually got worse when the shared pool got bigger makes me suspect a chain or linked list of some sort that steadily gets longer and has to be read with each execution of the SQL statement. The linear nature of the slowdown sure makes it look like something like that. Admittedly, I'm grasping.


Received on Thu Aug 10 2006 - 17:53:26 CDT

Original text of this message