Operating System - HP-UX
1748161 Members
3790 Online
108758 Solutions
New Discussion юеВ

Re: Monitor buffer hit ratio and buffer sizing

 
SOLVED
Go to solution
Hein van den Heuvel
Honored Contributor

Re: Monitor buffer hit ratio and buffer sizing

> 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
Regular Advisor

Re: Monitor buffer hit ratio and buffer sizing

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
Esteemed Contributor

Re: Monitor buffer hit ratio and buffer sizing

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

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

Cheers

Nicolas
All different, all Unix
Stephen Andreassend
Regular Advisor

Re: Monitor buffer hit ratio and buffer sizing

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
Regular Advisor

Re: Monitor buffer hit ratio and buffer sizing

Re-attaching SqlTrace.sh with latest version.
Volker Borowski
Honored Contributor

Re: Monitor buffer hit ratio and buffer sizing

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
Esteemed Contributor

Re: Monitor buffer hit ratio and buffer sizing

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
All different, all Unix
Volker Borowski
Honored Contributor

Re: Monitor buffer hit ratio and buffer sizing

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
Esteemed Contributor

Re: Monitor buffer hit ratio and buffer sizing

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

All different, all Unix
Stephen Andreassend
Regular Advisor

Re: Monitor buffer hit ratio and buffer sizing

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