Re: avoid dynamic SQL
- From: amonte <ax.mount@xxxxxxxxx>
- To: Vladimir Andreev <vandreev@xxxxxxxxx>
- Date: Tue, 30 Jun 2009 13:58:18 +0200
Guess extenral is the way to go
I have thought about passing the variable to unix bc
Thank you
Alex
2009/6/30 Vladimir Andreev <vandreev@xxxxxxxxx>
> Hi Alex,
>
> I don't think this can be avoided - you need an engine to calculate these
> formulae. You could try using the SQL engine instead of the PL/SQL engine
> (as in
> open c for 'select '||vFormula||' from dual';
> fetch c into exitcode;
> )
> but that would thrash the shared pool just as efficiently.
>
> The only way I could think of is to do these calculations outside of the
> database: fetch the formula and evaluate it on the client. Or create an
> external procedure and call it from PL/SQL, passing the formula via a bind
> variable.
> http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg11rtn.htm
>
> HTH,
> Flado
>
>
>
> On Tue, Jun 30, 2009 at 10:37, amonte <ax.mount@xxxxxxxxx> wrote:
>
>> 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:
