Home » SQL & PL/SQL » SQL & PL/SQL » shared pool memory flush
shared pool memory flush [message #37301] Wed, 30 January 2002 06:06 Go to next message
Messages: 2
Registered: January 2002
Junior Member
Is there any other possibility to maintain the shared pool memory with out flushing. Please do note that I am not intrested in increasing the shared pool memory. The normal free space left in my database is 10%(around 35 MB).
Pals ,those who can put some light ....please...
Re: shared pool memory flush [message #37304 is a reply to message #37301] Wed, 30 January 2002 06:44 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
i didnt get u. do u want keep some objects in shared memory permanantly?.

use dbms_shared_pool package to keep objects permanantly in shared pool.
Re: shared pool memory flush [message #37309 is a reply to message #37301] Wed, 30 January 2002 08:15 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
in 8.1.6 or above, Oracle can use a feature to rewrite sql where literals have been used and replace it with a bind variable equivalent form which is what stays in the shared pool. Set cursor_sharing=force at session level or in init file.
select * from emp where emp_no = 1;
select * from emp where emp_no = 2;
end up as:
select * from emp where emp_no = :var;

Oracle warns that is comes with some unexpected side effects too so be careful to test very thoroughly. e.g. subst(ename, 1, 10) would become substr(ename, :var1, :var2) etc.

Also see this URL (use the whole thing including spaces)
http://www.akadia.com/services/ora_important_2000.html#Flush Shared Pool when it reaches 60-70% of it's capacity
Previous Topic: frist three higher values
Next Topic: cross tabulation question
Goto Forum:

Current Time: Thu Aug 13 06:42:12 CDT 2020