wcache oddity

[Michael Steele_2]

What up HP folk:

Question for you all. I've got a standby server with standby databases. Its life is all about oracle archive log sync ups. It is notoriously slow. Slow to the point that everyone hates it. Performance analysis reveals only one thing, single digit %wcache in the sar -b reports. Rule of thumb is 75% for wcache but I've accepted lower. I've never seen single digits though.

The box is the same as every other VPar on five superdomes. All pa-risc domes, all 8 cpu per VPar.

Reading about %wcache performance suggests re-indexing the database, or, problems with writing too much unstructured data. I read this as structured data living in a data base and unstructured data living in a flat file.

Love to hear the best and brightest insights on this!

Thanks!

[Hein van den Heuvel · Accepted solution]

What are the actual bwrit/lwrite write rates?
If Oracle is using direct-io, then it should be bypassing the buffer cache. The rates would be low, and the %wcache rate irrelevant. ?!

http://docs.hp.com/en/B3921-90010/sar.1M.html
" %wcache
Buffer cache hit ratio for write requests e.g., 1 - bwrit/lwrit;"

Also, for standby, you would hope Oracle would a DB page once and only once. How could the write cache be doing anything useful if oracle is doing a good job?

Sounds like ITRC topic # 672181

That topic mentions "Oracle recommends".
And indeed recent Oracle documentation (
http://download.oracle.com/docs/cd/B19306_01/server.102/b15658/tuning.htm )
indicates:
"The %rcache column value should be greater than 90 and the %wcache column value should be greater than 60. Otherwise, the system may be disk I/O bound."

But this is for a 'real database' (and even for that I have my doubts about the validity of the statement). A standby DB is a different animal!

Allthough admittedly I have little ahnds-on experience with standby databases, I would expact them likely to be IO bound. It has no serious computing, searching, optimizing to do!

Now, please don't get me wrong, I'm sure you are not asking an idle (sic) question here.
I'm sure there is a real problem, but I do strongly doubt that %wcache is a useful indicator. At best it is an effect, not a root cause.

So lets try to find some more pertinent indicators!
What does the top-ten wait events for the the standby database show in STATSPACK or AWR?
What are the rough data rates? How much archive data coming in? How much CPU time burned?

Hope this helps some,
Hein van den Heuvel ( at gmail dot com )
HvdH Performance Consulting

[Michael Steele_2]

.