Re: SQL Flooding Shared Memory

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 31 Dec 2008 11:18:22 +0100
Message-ID: <495b46ee$0$195$e4fe514c@news.xs4all.nl>

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 Received on Wed Dec 31 2008 - 04:18:22 CST