Re: avoid dynamic SQL
- From: amonte <ax.mount@xxxxxxxxx>
- To: Martin Berger <martin.a.berger@xxxxxxxxx>
- Date: Tue, 30 Jun 2009 10:37:54 +0200
Hi Martin
the formulas are such as
1/1+2+3+4+5+6+7
2/5+43+434+33+22
1
2+3+4+4
The shared pool is full of BEGIN :EXITCODE statements and is causing
ORA-4031 :-[
Using 9.2.0.8
TIA
Alex
2009/6/30 Martin Berger <martin.a.berger@xxxxxxxxx>
> Alex,
>
> Can you give some examples of the formula, please?
> what version are you on? In 11g (and 10.2.0.4 with backport and event [1] )
> you can use dbms_shared_pool.purge to purge single sql statements. Maybe
> that helps?
> Another method I would try is to handle the cursor of your execute
> immediate explicite and close it asap afterwards.
>
> But as I have to confess I does not know what really causes the ORA-4031,
> these are all pure suggestions.
> Can you check what fills up your SGA?
>
> best regards,
> Martin
>
> [1]
> http://kerryosborne.oracle-guy.com/2008/09/flush-a-single-sql-statement/
>
>
>
>
> Am 30.06.2009 um 08:42 schrieb amonte:
>
>
> Hi all
>>
>>
>> I have some problem with some dynamic sql generated by a package, it is
>> causing ORA-4031 and I have to reboot the instance to get rid of the
>> problem.
>>
>> The code does something like
>>
>> for i in (... cursor ...)
>> loop
>> select formula
>> into vFormula
>> from calculators
>> where .......
>> execute immediate 'BEGIN :EXITCODE:='||vFormula||'; END;';
>> end loop;
>>
>> vFormula contains mathematical formulas
>>
>> The cursor returns around 30000 rows and this is hammering the shared pool
>> 30000 times!
>>
>> I dont see how can I avoid this dynamic SQL without doing some major
>> changes (how the formulas are stored for example) and wonder if anyone have
>> some idea.
>>
>>
>> TIA
>>
>>
>> Alex
>>
>>
>
Other related posts:
