RE: Logical Standby Issues (cont.)

Interesting.

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 [mailto:strickland.mark_at_gmail.com] Sent: Thursday, August 10, 2006 3:34 PM
To: Rich Amick
Cc: oracle-l_at_freelists.org
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.

Mark

--
http://www.freelists.org/webpage/oracle-l