Home » SQL & PL/SQL » SQL & PL/SQL » How to keep sql code (Oracle 9i, AIX)
How to keep sql code [message #352404] Tue, 07 October 2008 08:27 Go to next message
psoftneto
Messages: 44
Registered: July 2008
Member
Hi all,

Is it possible to keep one sql code to avoid parsing ?
I know we can keep a table is that possible for one sql ?
Do we need to set db_keep_cache_size or buffer_pool_keep ?

Thanks
Re: How to keep sql code [message #352405 is a reply to message #352404] Tue, 07 October 2008 08:37 Go to previous messageGo to next message
psoftneto
Messages: 44
Registered: July 2008
Member
Do we use dbms_shared_pool.keep ?

Thanks.
Re: How to keep sql code [message #352407 is a reply to message #352404] Tue, 07 October 2008 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This should not prevent from parsing, just from loading the package.
Let Oracle manages its shared pool; if the package is frequently used then it will be in memory, if it is not frequently used then why should it use memory that other can benefit?

Regards
Michel
Re: How to keep sql code [message #352411 is a reply to message #352407] Tue, 07 October 2008 08:54 Go to previous messageGo to next message
psoftneto
Messages: 44
Registered: July 2008
Member
So when we see that one sql is called frequently on statspack. What can we do to improve performance ? Should we store execution plan on outln user ?

Thanks.
Re: How to keep sql code [message #352421 is a reply to message #352411] Tue, 07 October 2008 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
So when we see that one sql is called frequently on statspack

Called? fetched, executed or parsed?

Regards
Michel
Re: How to keep sql code [message #352424 is a reply to message #352421] Tue, 07 October 2008 10:08 Go to previous messageGo to next message
psoftneto
Messages: 44
Registered: July 2008
Member
Executions is high (3,208)
Re: How to keep sql code [message #352426 is a reply to message #352424] Tue, 07 October 2008 10:10 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
But not parse?

Regards
Michel
Previous Topic: ORA-01745 invalide host bind variable
Next Topic: SQL Queries (merged)
Goto Forum:
  


Current Time: Thu Feb 13 13:43:26 CST 2025