Re: SQL Flooding Shared Memory

On Dec 30, 10:30 am, "gym dot scuba dot kennedy at gmail" <kenned..._at_verizon.net> wrote:
> <shweta.kapar..._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.- Hide quoted text -
>
> - Show quoted text -

Yes, to rid yourself of this problem the code absolutely needs to use bind variables, but *you* can't do that, this is a task for the application vendor as it's their code, not yours. You might get some mileage out of setting cursor_sharing to FORCE (a change I don't recommend), but be aware this change may affect other query plans. And remember if you make this change it's merely a crutch to prop up bad code; it doesn't really fix anything.

You need to contact the vendor and discuss this issue with them and propose a solution, which also means you'll need to test any new code they write.

Good luck with this.

David Fitzjarrell Received on Tue Dec 30 2008 - 13:38:01 CST