- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Monitor buffer hit ratio and buffer sizing
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-16-2004 02:32 AM
тАО04-16-2004 02:32 AM
Re: Monitor buffer hit ratio and buffer sizing
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-16-2004 02:33 AM
тАО04-16-2004 02:33 AM
Re: Monitor buffer hit ratio and buffer sizing
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-16-2004 02:44 AM
тАО04-16-2004 02:44 AM
Re: Monitor buffer hit ratio and buffer sizing
Thanks for files attached. I'll test those soon.
Hein,
That my hint :D
/*+ nicolas.dumeige@wanadoo.fr */
Cheers
Nicolas
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-16-2004 03:02 AM
тАО04-16-2004 03:02 AM
Re: Monitor buffer hit ratio and buffer sizing
eg
$ORACLE_HOME/bin/SqlTrace_OSPID.sh 43324
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-16-2004 03:02 AM
тАО04-16-2004 03:02 AM
Re: Monitor buffer hit ratio and buffer sizing
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-16-2004 09:27 AM
тАО04-16-2004 09:27 AM
Re: Monitor buffer hit ratio and buffer sizing
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-16-2004 10:25 AM
тАО04-16-2004 10:25 AM
Re: Monitor buffer hit ratio and buffer sizing
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2004 05:40 AM
тАО04-18-2004 05:40 AM
Re: Monitor buffer hit ratio and buffer sizing
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2004 07:56 AM
тАО04-18-2004 07:56 AM
Re: Monitor buffer hit ratio and buffer sizing
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2004 06:11 PM
тАО04-18-2004 06:11 PM
Re: Monitor buffer hit ratio and buffer sizing
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.