| keep pool [message #399453] |
Wed, 22 April 2009 05:25  |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I am new to DB performance related issues.
What is the possibility to use keep pool in oracle and when to implement this?
Can you please provide the reference book for Performance tuning?
|
|
|
|
|
|
| Re: keep pool [message #399455 is a reply to message #399453] |
Wed, 22 April 2009 05:41  |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
Actually when I take the statspack report, below recommendations has suggested by statspack report.
You are not using your KEEP pool to cache frequently referenced tables and indexes. This may cause unnecessary I/O. When configured properly, the KEEP pool guarantees full caching of popular tables and indexes. Remember, an average buffer get is often 100 times faster than a disk read. Any table or index that consumes > 10% of the data buffer, or tables & indexes that have > 50% of their blocks residing in the data buffer should be cached into the KEEP pool. You can fully automate this process using scripts.
Consider setting your optimizer_index_caching parameter to assist the cost-based optimizer. Set the value of optimizer_index_caching to the average percentage of index segments in the data buffer at any time, which you can estimate from the v$bh view.
Please suggest how to tune my database properly?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: keep pool [message #399896 is a reply to message #399551] |
Fri, 24 April 2009 07:20 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
| Quote: | What is the possibility to use keep pool in oracle and when to implement this?
|
You should only start worrying about things like assigning tables to the KEEP or RECYCLE pools after you've completely finished the earlier steps of tuning your system, such as tuning all of the problem SQL and rewriting any poorly performing PL/SQL. You are very unlikely to notice any benefits from assigning things to the KEEP pool while you still have poorly written SQL hanging around.
|
|
|
|
| Re: keep pool [message #399898 is a reply to message #399453] |
Fri, 24 April 2009 07:29 |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
What keep pool and recycle pool does?
I am not getting properly.
Please explain with the examples.
|
|
|
|
|
|
| Re: keep pool [message #400781 is a reply to message #399898] |
Wed, 29 April 2009 08:53 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Db blocks aer not aged out of the KEEP pool, so as long as the pool is large enough, blocks read into the Keep pool will still be there when you look for them again.
The Recycle pool works similarly to the main buffer pool - it is there so that blocks read from tables assigned to the Recycle pool will not force out blocks from the main buffer pool.
They're part of the 'Last 5%' of the tuning effort. After all of the Sql in your system has been tuned, then you can assign tables to these pools based on how the data from them will be accessed.
While you've still got any noticable amount of poorly performing SQL in the system, you'll never notice improvements from buffer pool tuning.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
