Monitor buffer hit ratio and buffer sizing

[Nicolas Dumeige]

Hello,

What script do you use to check the buffer hit ratio on Oracle (9.2.0.4) and verify the best size for every buffer ?

Regards

Nicolas

[Jean-Luc Oudart]

Nicolas,

a good start is perfstat.
Run a few snapshots and reports. This will give you a baseline you will refer in the future. note as much as possible of conditions (nb users, avg load, peak , ...)

Regards,
Jean-Luc

[Jean-Luc Oudart]

For the shared pool, I have a script you should run a few times during the day
cf. attachment.

javapool if you don't use java , reduce it !

Regards,
Jean-Luc

[Jean-Luc Oudart · Accepted solution]

Also find this document on tuning the Buffer Cache Using Multiple Buffer Pools.

cf. attachment

Regards,
Jean-Luc

[Nicolas Dumeige]

Jean-Luc,

Thanks for all your document and script.

For the perfstat (amongs other stuff), Tom Kyte (http://asktom.oracle.com/) has given his method on how to read the spreport output in an Oracle Press book :
http://www.amazon.com/exec/obidos/tg/detail/-/0072230657?v=glance
you may find some hints intersting...

We have batched perfstat snapshot all day long and that's one of our key piece of information as we're looking on how to improve response time four our client (Cognos Impromptu on a 700 Go datawarehouse)

My original post is due to "free buffer waits" in the v$session_wait -> db writter issue possibly.

Just out of curisosity, how many database writer have you configured ? Do you use AsyncIO ? Do you work on OLTP or Datawarehouse environnement ?

Cheers

Nicolas

[Hein van den Heuvel]

fwiw,

Before you get too carried away,
Be sure to have checked out:

http://www.hotsos.com/downloads/registered/00000005.pdf

"Why a 99%+ Database Buffer Cache Hit Ratio is Not Ok
Cary Millsap/Hotsos Enterprises, Ltd."

[sorry, but the good folks at hotsos require a (free) registration. Fortunately they send little or no Email]

Hein.

[Nicolas Dumeige]

Hein,

I've read about this article somewhere but did not get the document. Can't you attach the PDF (zipped if necessary) ?
If it's too large, explain in what way you found it relevant on my post ?
I'm not obsessed whith hit ration, I've see a lot "free buffer wait" and I want to tune the DB Writer, either by using 2 or 4 DBW process without AsyncIO or with AsyncIO and 2 or 4 I/O Slave s.

Cheers

Nicolas

[Hein van den Heuvel]

> I've read about this article somewhere but did not get the document. Can't you attach the PDF (zipped if necessary) ?

I could, but I will not. The hotsos folks must have their reasons to request registration. It is not up to me to sabotage that.

> If it's too large, explain in what way you found it relevant on my post ?

With your one line question we can only speculate as to why you want to know the buffer hit ratio. IMHO this ratio is largely irrelevant for serious tuning. This article does a reasonably job explaining why.
A) It is not the ratio that counts... it is the remaining IOs that count! If the hit ratio is 99.99%, but you are doing 1,000,000 buf-gets/sec then you are still doing 1000 IO/sec and need a better hit rate still. On the other hand, if you are only doing 100 buf -gets/sec then 99% hit rate is excessive and you are wasting memory.

> I'm not obsessed whit hitratio, I've see a lot "free buffer wait"

Ah, great. So you are already enlightend. But how were we supposed to know that! It is things like free buffer waits that warrant serious in vestigation.

Back to hit ratio...
If you run statspack with a high enough level (7?) then you'll get intersting buffer tuning information with the "Buffer Pool Advisory" sample below. In the real example below we actually saw a 'knee' in the curve. It suggests that if one made thise buffer pool 1.6 times bigger you have 1/2 the IOs left but raise it a little more to 1.8 tmes original and one 1/10 of the IOs are expected.


hth,
Hein.

Buffer Pool Advisory for DB: PERF2 Instance: PERF2 End Snap: 437
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate

Size for Size Buffers for Est Physical Estimated
P Estimate (M) Factr Estimate Read Factor Physical Reads
--- ------------ ----- ---------------- ------------- ------------------
D 256 .1 31,760 2.00 44,275,859
D 512 .2 63,520 1.71 37,984,246
D 768 .3 95,280 1.42 31,520,817
D 1,024 .4 127,040 1.26 28,017,626
D 1,280 .5 158,800 1.21 26,792,606
D 1,536 .6 190,560 1.17 25,850,715
D 1,792 .7 222,320 1.13 25,029,845
D 2,048 .8 254,080 1.09 24,230,318
D 2,304 .9 285,840 1.05 23,309,340
D 2,560 1.0 317,600 1.00 22,233,495
D 2,576 1.0 319,585 1.00 22,171,431
D 2,816 1.1 349,360 0.96 21,201,036
D 3,072 1.2 381,120 0.90 19,908,948
D 3,328 1.3 412,880 0.83 18,318,251
D 3,584 1.4 444,640 0.75 16,624,300
D 3,840 1.5 476,400 0.65 14,516,581
D 4,096 1.6 508,160 0.54 12,068,153
D 4,352 1.7 539,920 0.32 7,197,825
D 4,608 1.8 571,680 0.04 790,013
D 4,864 1.9 603,440 0.03 697,274
D 5,120 2.0 635,200 0.02 536,129

[Nicolas Dumeige]

Hein,

"With your one line question we can only speculate as to why you want to know the buffer hit ratio."

You're absolutly right. But at the same time, you have to realize that the question was posted in order to get more info and more usefully, to undestand what kind of info is of any help. if I already knew this stuff before, I would not post a question. As a matter of fact, since my first question I've done my homework.

"If you run statspack ..."

I did. Then I found the SQL statement that produce this output (and others) to get the picture on our KEEP/RECYCLE/DEFAULT cache.

Thanks for you time Hein,
and don't get carried away either with the "sabotage" thing, I hardly think that a web site is a safe !

Cheers

Nicolas

[Stephen Andreassend]

A good way to monitor the buffer cache is to look at a time series graph.

The Oracle Performance Manager module of OEM has a good graph of this. This is good for looking at the system in real-time, but its a poor choice for historical information.

What I do is run a nohup job to dump the buffer cache hit ratio, buffer reads, and physical reads etc to a CSV file to build up a historical pattern, and then graph it in Excel. The script I use is oraIOStat, and its attached if you want to do it this way.

The benefit of this is that you get the hit ratio, but you will also see how many buffers are scanned in memory, which can then point you towards poor performing SQL.