-
Shared Pool Problem
I am calling sqlplus from a perl script which executes a select statement.The select statement uses bind variables in the fashion given below
exec :start_poll_time:=$start_poll_time;
exec :end_poll_time:=$end_poll_time;
exec :message_id:=$message_id;
The select statement is select mvr_message_code,file_name from
cm_incominglog where receive_datetm between (:start_poll_time,'ddmmyyyyhh24miss') and (:end_poll_time,'ddmmyyyyhh24miss')
The problem is the shared pool is unable to flush out the select statements and the no of sql statements in the shared pool is large.I would like to know in which fashion the bind variables need to be declared and passed
-
What is the specific error message it is showing ?
or check the shared pool free memory by
select * from v$sgastat
where name ='free memory'
and pool = 'shared pool'
The bind variables seems ok, need not require to parse the statement again.
You can search in v$sql for how many versions of the statement exists.
cheers