Re: avoid dynamic SQL
- From: Martin Berger <martin.a.berger@xxxxxxxxx>
- To: exriscer@xxxxxxxxx, Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Jul 2009 11:16:12 +0200
From my point of view, the base questions are:
1) why are the heaps for the 'execute immediate ...' sniplets are not
reused, even they are not needed anymore. (that's the reason for the
ORA-4031, but please prove me wrong if anyone knows better!)
2) what's the best (in terms of time? cpu? mem-consumption,
mem-framgentation, ...) way to circumvent this issue, and why does it
avoid 1)?
Without an answer to question 1 (which I am still searching) I guess
it's hard to solve 2).
regards,
Martin
On Wed, Jul 1, 2009 at 11:02, LS Cheng<exriscer@xxxxxxxxx> wrote:
> That wont work because his formulas are not really variables which can be
> bind. It will if he uses them in SQL predicates
>
>
> Thanks
>
> --
> LSC
>
>
> On Wed, Jul 1, 2009 at 8:16 AM, Jurijs Velikanovs <j.velikanovs@xxxxxxxxx>
> wrote:
>>
>> begin execute immediate 'alter session set cursor_sharing=''FORCE'''; end;
>> /
>>
>> Would be a quick fix. In your case.
>>
>> Yury
>>
>> On Tue, Jun 30, 2009 at 4:42 PM, amonte<ax.mount@xxxxxxxxx> wrote:
>> > 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
>> >
>> >
>>
>>
>>
>> --
>> Jurijs
>> +371 29268222 (+2 GMT)
>> ============================================
>> http://otn.oracle.com/ocm/jvelikanovs.html
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>
>
--
Martin Berger martin.a.berger@xxxxxxxxx
Lederergasse 27/2/14 +43 660 660 83306
1080 Wien http://berx.at/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
