| Help: performance tuning [message #62091] |
Thu, 24 June 2004 05:37  |
Jadie
Messages: 64
Registered: January 2002
|
Member |
|
|
Any answer will be appriated for the following questions:
1. DB block size: my Oracle database recommends to change the DB block size from 4K to 8K. Our database is an OLTP DB. The transaction size is very small. Changing the DB block size needs rebuild a database and a few hours downtime.
My question is: how to define the better DB block size for the DB? (from Oracle, OLTP DB block size can be 4K or 8K)
2. Redo log buffer: we have 10M for log buffer. But "redo buffer allocation retries" is increasing. We have 3 log files (10M/each), log_checkpoint_interval = 10000, log_checkpoint_timeout = 1800; We have around 100M archive log every day. (we have very small amount of transactions at this moment)
My question is: how can I indenfy if there is any issue about the redo log configuration? What cause the "redo buffer allocation retries" increase? (10M log buffer is quite huge)
3. Shared Pool: we have 500M for the shared pool. It is a huge memory allocation for shared pool. But its free memory is about 30M...
My question is: how can I know how the shared pool memory is allocated? what is consuming so many of the memory?
Thanks... really appreciate any help.
Calla
|
|
|
|
| Re: Help: performance tuning [message #62099 is a reply to message #62091] |
Thu, 24 June 2004 17:55  |
IA
Messages: 91
Registered: March 2004
|
Member |
|
|
Hi,
Answers to
Q1. Check your operating system max block size. Set oracle block size to be the max block size ur operating system can handle. For eg, if ur op-sys is 8k and u set oracle block size to 4k, then effectively, you are incurring more IO because you are having a system read 8k for 4k requirement.
Q2. The redo allocation retries is refering to your redo log buffer, not the size of your redo log files. Set your redo log buffer to a bigger size.
Q3. Look at the new advisory table v$db_cache_advise (i think from memory) and see the effect of changing your cache size of db, shared pool and redo. Assuming you are on 9i Oracle.
At the end of the day, it all depends on your application and how you use the database.
Hope this helps ... IA
|
|
|
|
| Re: Help: performance tuning [message #62112 is a reply to message #62099] |
Fri, 25 June 2004 07:14 |
Jadie
Messages: 64
Registered: January 2002
|
Member |
|
|
Thanks for your response, IA.
Q1. we are using Solaris 9, the file system block size is 8K. Does it mean that DB block size is better to be 8K?
Q2. I read some articles about log buffer size. The Oracle experts said 5M log buffer is pretty big, allocating more memory to it just wastes the memory. But I don't know what cause the log buffer bottleneck
Q3. yes I am using 9i. But why all the pool name in this dictionary is DEFAULT? and ADVICE_STATUS is all OFF. :(
Thanks again
Calla
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
