SQL Flooding Shared Memory

Hi All

I have identified the SQL occupying max shared pool space. Detailed as follows:::

SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
shared_pool_size                     big integer 754974720

SQL>select bytes/1024/1024 FREE_MB
from v$sgastat

        where name = 'free memory'
        and pool = 'shared pool'

SQL>SELECT substr(sql_text,1,100) "Stmt", count(*),

                sum(sharable_mem)    "Mem",
                sum(users_opening)   "Open",
                sum(executions)      "Exec"
          FROM v$sql
         GROUP BY substr(sql_text,1,100)
        HAVING sum(sharable_mem) > 10000000


Stmt													COUNT(*)	Mem		Open	Exec
---------------------------------------------------------------------------------------------------
----------     ---------       -----   ------
select objid,   title, TO_CHAR(escalate_time, 'YYYY/MM/DD HH24:MI:SS')
from T where 	11325		225210490	1	11323

I have taken 1 SQL Statement for sample from v$sql, there are 11325 such copies ::

select objid, title, TO_CHAR(escalate_time, 'YYYY/MM/DD HH24:MI:SS'),

   from T where
escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI- SS')
 and FLOOR( flags / 16 ) * 2 = FLOOR( flags / 8 )  order by escalate_time asc, creation_time asc, objid asc

• There are around 11K copies of above SQL statement. WIth diffrence only in escalate_time < TO_DATE('2008-12-30-03-56-32', 'YYYY-MM-DD-HH24-MI- SS') portion.

This SQL is run by the Application code. Does this show the poor use of bind variables. I mean no bind variable is used and therefore every time the SQL is executed.. a new copy of the SQL is loaded and executed.

How we can get rid of above problem.

Many thanks Received on Tue Dec 30 2008 - 07:02:18 CST