Hello guys,
at my current project i am performing some performance tests for oracle data guard. The question is "How does a LGWR SYNC transfer influences the system performance?"
To get some performance values, that i can compare i just built up a normal oracle database in the first step.

Now i am performing different tests like creating "large" indexes, massive parallel inserts/commits, etc. to get the bench mark.

My database is an oracle 10.2.0.4 with multiplexed redo log files on AIX.

I am creating an index on a "normal" table .. i execute "dbms_workload_repository.create_snapshot()" before and after the CREATE INDEX to get an equivalent timeframe for the AWR report.
After the index is built up (round about 9 GB) i perform an awrrpt.sql to get the AWR report.

And now take a look at these values from the AWR

                                                                   Avg
                                             %Time  Total Wait    wait     Waits
Event                                 Waits  -outs    Time (s)    (ms)      /txn
---------------------------- -------------- ------ ----------- ------- ---------
......
......
log file parallel write              10,019     .0         132      13      33.5
log file sync                           293     .7           4      15       1.0
......
......

How can this be possible?

Regarding to the documentation

-> log file sync: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm#sthref3120

Wait Time: The wait time includes the writing of the log buffer and the post.

-> log file parallel write: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm#sthref3104

Wait Time: Time it takes for the I/Os to complete. Even though redo records are written in parallel, the parallel write is not complete until the last I/O is on disk.

This was also my understanding .. the "log file sync" wait time should be higher than the "log file parallel write" wait time, because of it includes the I/O and the response time to the user session.
I could accept it, if the values are close to each other (maybe round about 1 second in total) .. but the different between 132 seconds and 4 seconds is too noticeable.

Is the behavior of the log file sync/write different when performing a DDL like CREATE INDEX (maybe async .. like you can influence it with the initialization parameter COMMIT_WRITE??)?
Do you have any idea how these values come about?

Any thoughts/ideas are welcome.

Thanks and Regards

The logwriter can write even when you don't issue a commit - for example when there is one MB of redo outstanding in the buffer. If you build an index of about 9GB, it is not surprising that you cause about 10,000 log file writes of about 1MB each, which get written without a log file sync being issued.

The 293 log file syncs probably come from other (background) activity and sys-recursive activity - and thy take about 15ms because they are trailing the larger writes that are constantly streaming out from your 9GB build.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {code} (lowercase, curly brackets, no spaces) so that the text appears in

fixed format

.

"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan

Thank you for Nice Idea.

In this case, How can we reduce "log file parallel write" and "log file sync" waited time?


A NOLOGGING can help, can't it?

Thank you for your idea with NOLOGGING.

Thank you for a Data Guard configuration recommend with NOLOGGING.
I'll aware with nologging

"Carl Sagan is always right"

Indeed he was!

As Sagan so wisely remarked, "The speed of light is very fast". It takes a true genius to say something so profound ... so stunning ...

Just a jape! I loved Cosmos and remember fighting (often physically) with my siblings to watch re-runs of the show, as they preferred Top of the Pops.

Regards - Don Lewis

Maybe I wasn't clear enough in the previous posting: It is recommended to run the primary database of a data guard configuration in FORCE LOGGING mode. That will cause all NOLOGGING clauses, issued at the primary database, to be ignored. It therefore makes not much sense in my view, to setup a test load for a Data Guard configuration with any NOLOGGING statements in it, because these will have no effect later in the real environment

Kind regards
Uwe

http://uhesse.wordpress.com

James Burke, right?

Remember when that probe approached Halley's comet (may have been another)? He got confused and said that the data it was feeding back was realtime - a 16-year old spod in the studio had to set him right by saying there was a few minutes' delay as it was so far away!

Connections was good, though.