RE: Logical Standby Issues (cont.)
- From: "Rich Amick" <rAmick@xxxxxxxxxxx>
- To: "'Mark Strickland'" <strickland.mark@xxxxxxxxx>
- Date: Thu, 10 Aug 2006 15:53:26 -0700
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@xxxxxxxxx]
Sent: Thursday, August 10, 2006 3:34 PM
To: Rich Amick
Cc: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
