Re: SQL Flooding Shared Memory

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Tue, 30 Dec 2008 11:43:26 -0800 (PST)
Message-ID: <149862b7-111c-4f9b-859b-569950f3a28d@b41g2000pra.googlegroups.com>


On Dec 30, 8:02 am, shweta.kapar..._at_googlemail.com wrote:
> 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

If this is canned code you might consider the changing the value of the database parameter cursor_sharing. The default value is EXACT. You might want to consider setting it to SIMILAR or even FORCE. I would try SIMILAR before I tried FORCE. See the Oracle version# Reference manual for a description of the parameter.

To the optimizer the SQL will be different and it is possible (likely even) that some plans will change. Some of the changes may not be for the better.

An application that creates SQL statements using constants in all its calls is a poorly designed application. Such applications are often subject to SQL injection weakness. If this is a home grown application it needs to be changed to use bind variables in the SQL. If vendor purchased it should be junked unless the vendor is made aware of the issue and agrees to modify the application in a timely fashion.

HTH -- Mark D Powell -- Received on Tue Dec 30 2008 - 13:43:26 CST

Original text of this message