Home » RDBMS Server » Performance Tuning » dataguard tunning: checkpoint completed (11.1.0.7)
dataguard tunning: checkpoint completed [message #419793] Tue, 25 August 2009 11:08 Go to next message
aline
Messages: 92
Registered: February 2002
Member
Hi,

I have some performance problems with one of my standby databases.
the top wait events (more than 60% of time) is checkpoint completed.
I had take db_writers to the max value (20) but it has solved nothing.

any idea?

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
checkpoint completed                               495       2,326   4699   63.8
SQL*Net vector data from client                  2,318         445    192   12.2
parallel recovery control message reply          2,085         208    100    5.7
rdbms ipc reply                                     89         173   1940    4.7
RFS write                                        1,102         115    104    3.2


[change [quote] to [code] to preserve formatting]

[Updated on: Thu, 27 August 2009 07:13] by Moderator

Report message to a moderator

Re: dataguard tunning: checkpoint completed [message #419795 is a reply to message #419793] Tue, 25 August 2009 11:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Top 5 Timed Events Avg %Total
Realize that with EVERY Top 5 list, some item will be at the top of the list.
Simply because an item is the top most item, it does NOT mean that it is a problem to be fixed.

Assume for the sake of this discussion, you change something so that "checkpoint completed" is no longer at the top of this list.
Then will you go searching for how to get the new TOP item off the list?

You seem to suffer from Compulsive Tuning Disorder.

What other "proof" do you have that "checkpoint completed" is a problem?
Re: dataguard tunning: checkpoint completed [message #419798 is a reply to message #419793] Tue, 25 August 2009 11:36 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
Hi,

Thank for your answer.
I hope I have no compulsive tuning disorder.
It could be a new professional disease Smile.

So my standby has a 24 hour lag!
So I have an issue with it.
The best way (I think) to find bottleneck is to take snapshot ans to see top wait events.
The first are checkpoint completed and enq: CF - contention.
Now, I'm trying to find the meaning of this events.

I don't know if "checkpoint completed" is a problem but I'm sure I have one.
If you have better solutions to find where it is...
Re: dataguard tunning: checkpoint completed [message #419799 is a reply to message #419793] Tue, 25 August 2009 11:40 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>So my standby has a 24 hour lag!
Is the lag "constant" at around 24 hours; never less than 23 & never more than 25 hours?
Re: dataguard tunning: checkpoint completed [message #419803 is a reply to message #419799] Tue, 25 August 2009 11:54 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
no,

When nothing happen in the primary, the lag decrease eventually to 0.
Now, the lag between primary and standby is 22 hours (actually it decreases)
Re: dataguard tunning: checkpoint completed [message #419807 is a reply to message #419803] Tue, 25 August 2009 12:05 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
aline wrote on Tue, 25 August 2009 09:54
no,

When nothing happen in the primary, the lag decrease eventually to 0.
Now, the lag between primary and standby is 22 hours (actually it decreases)

Thanks for the response.
I was asking because some sites actually delay redo application at standby to avoid propagating errors into standby.

From my perspective, 2 possible bottlenecks are likely:
1) network
2) standby system

Is the hardware of the standby system comparable to the Primary;
with regard to CPU number & speed, RAM, I/O subsystem?

Bottom line question, should the standby be able to keep up with the Primary?
Re: dataguard tunning: checkpoint completed [message #420020 is a reply to message #419793] Wed, 26 August 2009 10:12 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
Hi BlackSwan,

The hardware is not the same.
Standby site has slower disks and less memory.
I know I must have same configuration in both sites, but I have to work with this.
So I had to optimize a bad system, it's my poor job Sad


Re: dataguard tunning: checkpoint completed [message #420031 is a reply to message #419793] Wed, 26 August 2009 11:47 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>The hardware is not the same.
>Standby site has slower disks and less memory.
No amount of software tuning can compensate for deficient hardware.
You can't tune a dump truck to keep up with a race car.
Re: dataguard tunning: checkpoint completed [message #420175 is a reply to message #419793] Thu, 27 August 2009 07:22 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
From the documentation

Quote:
checkpoint completed

A session waits for a checkpoint to complete. This could happen, for example, during a close database or a local checkpoint.

Wait Time: 5 seconds

Parameters: None


From metalink Note:240875.1 :
Quote:
The top database wait events should be "db file parallel read" and "checkpoint completed" during media recovery. Refer to recovery monitoring best practices section.


How long was your snapshot taken over?
Previous Topic: oracle query running slow
Next Topic: A question about LRU in shared pool
Goto Forum:
  


Current Time: Thu Dec 08 16:24:24 CST 2016

Total time taken to generate the page: 0.11069 seconds