Re: SQL Flooding Shared Memory

On Dec 31, 9:40 am, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

The best source of version specific problems using cursor_sharing = FORCE or SIMILAR is in Oracle support document #94036.1 Init.ora Parameter "CURSOR_SHARING" Reference Note

There is a long list of referenced bugs with versions that the bug affects. Most of the bugs are associated with specific features or functions so sites that do not use these features/functions are likely to avoid the bugs.

All our installations use EXACT due to query performance issues encountered on past attempts to change the parameter.

HTH -- Mark D Powell -- Received on Wed Dec 31 2008 - 10:40:24 CST