Home » RDBMS Server » Performance Tuning » keep pool (10g)
keep pool [message #399453] Wed, 22 April 2009 05:25 Go to next message
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 #399454 is a reply to message #399453] Wed, 22 April 2009 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
What is the possibility to use keep pool in oracle and when to implement this?

Almost never.
Only export should envisage to use it.

Regards
Michel
Re: keep pool [message #399455 is a reply to message #399453] Wed, 22 April 2009 05:41 Go to previous messageGo to next message
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 #399458 is a reply to message #399455] Wed, 22 April 2009 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Hire an expert to do it.

Regards
Michel
Re: keep pool [message #399459 is a reply to message #399453] Wed, 22 April 2009 05:48 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you very much Michel.
Re: keep pool [message #399544 is a reply to message #399453] Wed, 22 April 2009 20:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
grpatwari wrote on Wed, 22 April 2009 03:25

Can you please provide the reference book for Performance tuning?


When all else fails, Read The Fine Manual
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/toc.htm


http://www.orafaq.com/forum/t/84315/74940/


Re: keep pool [message #399551 is a reply to message #399453] Wed, 22 April 2009 22:22 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you very much BlackSwan.
Re: keep pool [message #399896 is a reply to message #399551] Fri, 24 April 2009 07:20 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #399902 is a reply to message #399898] Fri, 24 April 2009 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
What keep pool and recycle pool does?

Nothing special.
Did you read Database Concepts?

Regards
Michel

[Updated on: Fri, 24 April 2009 07:46]

Report message to a moderator

Re: keep pool [message #400781 is a reply to message #399898] Wed, 29 April 2009 08:53 Go to previous message
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.
Previous Topic: Job queue process takes more then 90 % cpu
Next Topic: value BETWEEN x AND y
Goto Forum:
  


Current Time: Tue Dec 03 17:38:42 CST 2024