Can I clean shared pool sql area in sga? 1

[greenpee]

Hi,

I have OCIStmtExecute: ORA-04031: unable to allocate 4200 bytes of shared memory problem so offen now. I increase the shared memory but couldn't solve the problem. I check my v$sgastat and found 'shared pool sql area' is half size of my shared memory. Do you think this is my problem? Can I delete * in v$sql_area? If yes, how?

Thanks,

 

[SantaMufasa]

GreenPee,

Another issue to consider when receiving this error is the appropriate sizing of the related parameter, "java_pool_size". Oracle recommends that the minimum value is "java_pool_size=20000000" (20MB).

Now, to directly deal with your question, "Can I clean shared pool sql area in sga?", here is my code that I run from a script I call, "SharedPool.sql":

set pagesize 35
set linesize 200
set echo off
col a heading "Sharable|Memory" format 9,999,999,999
col b heading "Persistent|Memory" like a
col c heading "Runtime|Memory" like a
select count(*)
	, sum(sharable_mem) a
	, sum(PERSISTENT_MEM) b
	, sum(RUNTIME_MEM) c
from v$sqlarea
/
prompt Flushing Shared_Pool...
alter system flush shared_pool
/
select count(*)
	, sum(sharable_mem) a
	, sum(PERSISTENT_MEM) b
	, sum(RUNTIME_MEM) c
from v$sqlarea
/

The above code:
1) discloses the current consumption statistics of your Shared Pool,
2) flushes the currently unused contents of your Shared Pool, and
3) re-confirms the consumption statistics of your Shared Pool following the flush.

Let us know if this helps.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[stressman]

Hy we had the same problem few months ago and we also tried the flush memory solution but it not solved the problem just reduce the frequence of the problem.
We so found out that it could be an oracle software problem. We so upgarde oracle to 8i1.7.4.1 and since the problem never occurr again may be it is also your solution

Best regards