Re: SQL Flooding Shared Memory

From: gym dot scuba dot kennedy at gmail <kennedyii_at_verizon.net>
Date: Tue, 30 Dec 2008 16:30:06 GMT
Message-ID: <i0s6l.1973$BC4.92@nwrddc02.gnilink.net>

<shweta.kaparwan_at_googlemail.com> wrote in message news:fc6b00b5-9161-496f-bb83-ee29a4a4b5ae_at_f3g2000yqf.googlegroups.com...
> 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'
> FREE_MB
> ----------
> 32MB
>
>
>
> 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
>
>

Use bind variables. Received on Tue Dec 30 2008 - 10:30:06 CST

Original text of this message