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- Hide quoted text -
>>
>> - Show quoted text -

It's not the performance issues after changing cursor_sharing from exact to anything else that worries me the most. It's the fact that on more than one occasion I have seen queries return different results (different number of rows, empty columns) even without special DB features turned on (but with Oracle Spatial it's a sure disaster). Problem is you would have to (re)test your complete application(s) to be sure these problems are not in your DB, and NEVER have a guarantee they will not hit you afterwards.

Shakespeare Received on Fri Jan 02 2009 - 02:18:59 CST