Re: avoid dynamic SQL
- From: amonte <ax.mount@xxxxxxxxx>
- To: martin.a.berger@xxxxxxxxx
- Date: Wed, 1 Jul 2009 23:20:05 +0200
I am not sure why heaps are not reused, it is probably the formulas are very
different in length
Alex
2009/7/1 Martin Berger <martin.a.berger@xxxxxxxxx>
> 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:
