Re: SQL Flooding Shared Memory

On Dec 31, 4:18 am, Shakespeare <what..._at_xs4all.nl> wrote:
> ddf schreef:
>
>
>
>
>
> > 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
>
> Setting cursor_sharing to FORCE has proven many times to give wrong
> results in queries.
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

Which is why I stated I do not recommend such a change. It IS, however, an option for the OP if he/she chooses to consider it.

David Fitzjarrell Received on Wed Dec 31 2008 - 08:40:46 CST