Re: SQL Flooding Shared Memory

On Dec 30, 7: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

It shows absolutely no use of bind variables. And not every time that basic query is run would it be necessary for a hard parse as the exact same date and time could be used (although, as you've noted, that isn't a likely occurrence). Using a bind variable would reduce your query count to 1 and would make the application a bit more efficient as one hard parse and 11k soft parses would occur.

This is truly poor coding of an application.

David Fitzjarrell Received on Tue Dec 30 2008 - 08:02:04 CST