Re: avoid dynamic SQL
- From: Martin Berger <martin.a.berger@xxxxxxxxx>
- To: ax.mount@xxxxxxxxx
- Date: Tue, 30 Jun 2009 10:06:02 +0200
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:
