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.

[Hein van den Heuvel]

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

I may have picked an ugly word there. Sorry. It was intended to point to sabotaging their plan, not about hacking a website.

Let's try it differently.
Hotsos could just as easily (more easily!) make the document available to anyone passing by. They did not.
They choose to ask for registration (I don't like that, but they did).
I would seem wrong to me if someone else (me) were to put their document in a place where just anyone can grab it.

Now if someone where to send me an Email asking me for a copy, that I would consider. (hint! :^).

Cheers,
Hein.

[Stephen Andreassend]

Now if there is a poor performing SQL trace session, I run the attached script to generate a trace report on the application.

[Nicolas Dumeige]

Stephen,
Thanks for files attached. I'll test those soon.

Hein,
That my hint :D
/*+ nicolas.dumeige@wanadoo.fr */

Cheers

Nicolas

[Stephen Andreassend]

And if you dont know the session that you want to generate a SQL trace on, run top and get the Unix pid, then pass it into this script to generate the SQL trace report.

eg
$ORACLE_HOME/bin/SqlTrace_OSPID.sh 43324

[Stephen Andreassend]

Re-attaching SqlTrace.sh with latest version.

[Volker Borowski]

Hi,

since Oracle 9, consider the use of dynamic SGA.

You can dynamicly (while DB is up and runnning) adjust buffersizes to distribute memory between shared_pool and buffer-cache, even for diffent blocksizes in one database.

I.e. for daily dialog and a lot of statements, a big shared_pool might be helpful, while nightly batchprocessing with a few reporting selects that might require some table-scans a big buffer cache would be fine.

I have to admit, that the above reason is some training szenario :-)

But at least it seems usefull to me to be able to decide at runtime if you want to increase the buffercache. With db_block_buffers, you need to bounce the DB. So read about sga_max_size and db_cache_size and its brothers.

Volker

[Nicolas Dumeige]

Volker,

I agree with you, Oracle 9 represent in many way a real improvement, specially regarding dynamic settings.

Odd enought, our night batch are not so different from the daily user activity.
(
By night you do parallel load with some heavy transformation but executed step by step from global temporary table to the mains fact tables. We seldom do an update, because it's an accounting environnement, if a mouvement is wrong, one or more mouvements are inserted to correct the figures.
User make request on large partionned table. The reports are built on large data selection, from fact table to global temporary table, then transformed (decoding, agregation, ...) and inserted in a shared partionned report table. Just like the batch, all operations are insert statements.
As you can see, a lot in commun.
)

So I don't know if we would benefit from a specifiq tuning based on the type of activity. In all case, the segment of the fact table 30 G compressed data (160 Go uncompressed) can not be maintenaid in any buffer cache, so there are flagged RECYCLE. Only latest and more accessed data segment use KEEP option.

In any case, thanks for your suggestions.

Cheers

Nicolas

[Volker Borowski]

Nicolas,

this profile seems difficult to tune, and I do not think the buffer quality would be a good key-value for such amounts of data. You will always have not enough memory :-)
Try to cut IO count, and do not worry to much about quality, because in this load-profile you rarely re-use blocks, after you already got them once. Poor way to achive better quality by enlarging.

The only way would be to use a bigger blocksize on this table. I.e. if you have 1k average row-length on a 8k blocksize, a block can be used 8 times for insert, before it becomes useless for an insert (and will vanish somtime later from the cache).
So one READ for 8 Inserts.
If you can use 16k blocksize for this table, one READ would be able to feed 16 inserts, and this would cut READ-activity for inserts by 50% (automaticly increasing quality, because the block is used for 50% more gets).

I would at least do some tests on this.
Check here: http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96536/ch133.htm#1015709

It might be worth to dump keep / recycle & db_block_buffers for db_cache_size and db_32k_cache_size. Place the table in charge in a seperate tablespace with a bigger block_size. Watch out for contention on INITRANS storageslots for parallel INSERTS. It may give you distribution problems on the SELECTS later, you can not say beforehand, but I'd give it a try at least.

Beside this, keep in mind, that building re-buildable tables with NOLOGGING saves a lot of writes of UNDO. Keep in mind to check your recovery-procedures in this case.
(I am no fan of NOLOGGING for safty reasons, but in this case .....)

Nice nut to crack, whish for success :-)
Volker

[Nicolas Dumeige]

Hello Volker,

You've mentioned several changes we're willing to test :

1) Use a tablespace with type temporary and 32k block size associated with a 1 Gb cache.

2) Two specifiq FS with Force Direct IO for the muliplexed Redo Logs

3) Test on the DB Writer. You can either de-activate the AsyncIO option and use several DBW process, or keep it and use several IO slaves.

4) Set fact tables and associated index with initrans=4 and freelist=7 (or freelist=11).

We realise that the largest endevour must be on the SQL tuning, but those are fast tricks to improve performance.

Best regards and thanks for your encouragements.

Nicolas

[Stephen Andreassend]

"Beside this, keep in mind, that building re-buildable tables with NOLOGGING saves a lot of writes of UNDO. Keep in mind to check your recovery-procedures in this case."

NOLOGGING reduces the redo writes, not the UNDO. And this feature is only available when you use direct path inserts using /*+ APPEND */ SQL hints or SQL*Loader, so it may have limited use.

Also let us know if you were able to create a DB with a 32K block size, I tried and encountered an Oracle bug on 9204.

[Nicolas Dumeige]

Hello,

Thanks for the Redo/Undo precision ;)

When you use the direct path, you must have exclusive access on the object. Even with that restriction, you do have some APPEND / NOLOGGING hints.

We will not create a new DB but only a new tablespace with 32k block size. Given that, I'll get back to ITRC if you encouter the Ora Bug.

By the way, what is the identifier of that bug please ?

Cheers,

Nicolas