Re: avoid dynamic SQL
- From: amonte <ax.mount@xxxxxxxxx>
- To: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- Date: Wed, 1 Jul 2009 11:05:46 +0200
I have 500MB Shared Pool and the instance dies once every 3 to 4 weeks due
to 4031
Alex
2009/6/30 Goulet, Richard <Richard.Goulet@xxxxxxxxxxx>
> Alex,
>
> What is your current shared pool size? Also, is it possible for your
> developer to close/rollback his efforts so that the space can be reclaimed.
> Say:
>
> for i in (... cursor ...)
> loop
> select formula
> into vFormula
> from calculators
> where .......
> execute immediate 'BEGIN :EXITCODE:='||vFormula||'; Rollback; END;';
> end loop;
>
>
> *Dick Goulet***
> Senior Oracle DBA
> PAREXEL International
>
>
> ------------------------------
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *amonte
> *Sent:* Tuesday, June 30, 2009 2:42 AM
> *To:* Oracle-L Group
> *Subject:* avoid dynamic SQL
>
> 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:
