shared pool memory flush [message #37301] |
Wed, 30 January 2002 06:06 |
shivakumaar.R
Messages: 2 Registered: January 2002
|
Junior Member |
|
|
Hi,
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 #37309 is a reply to message #37301] |
Wed, 30 January 2002 08:15 |
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
|
|
|