OraFAQ Forum: Performance Tuning » dataguard tunning: checkpoint completed

Home » RDBMS Server » Performance Tuning » dataguard tunning: checkpoint completed (11.1.0.7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

dataguard tunning: checkpoint completed [message #419793]

Tue, 25 August 2009 11:08

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

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: dataguard tunning: checkpoint completed [message #419798 is a reply to message #419793]

Tue, 25 August 2009 11:36

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...

Report message to a moderator

Re: dataguard tunning: checkpoint completed [message #419799 is a reply to message #419793]

Tue, 25 August 2009 11:40

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: dataguard tunning: checkpoint completed [message #419803 is a reply to message #419799]

Tue, 25 August 2009 11:54

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)

Report message to a moderator

Re: dataguard tunning: checkpoint completed [message #419807 is a reply to message #419803]

Tue, 25 August 2009 12:05

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: dataguard tunning: checkpoint completed [message #420020 is a reply to message #419793]

Wed, 26 August 2009 10:12

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

Report message to a moderator

Re: dataguard tunning: checkpoint completed [message #420031 is a reply to message #419793]

Wed, 26 August 2009 11:47

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: dataguard tunning: checkpoint completed [message #420175 is a reply to message #419793]

Thu, 27 August 2009 07:22

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?

Report message to a moderator

Previous Topic:	oracle query running slow
Next Topic:	A question about LRU in shared pool

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 18 06:28:35 CDT 2024