Re: trigger- problem: too many open cursors
Date: Thu, 2 Apr 2009 11:40:31 -0700 (PDT)
Message-ID: <32af2123-7bde-491f-bd3f-02b936676b9b_at_z23g2000prd.googlegroups.com>
On Apr 2, 4:46 am, Andreas Mosmann <mosm..._at_expires-30-04-2009.news- group.org> wrote:
> ddf schrieb am 01.04.2009 in
> <c5b7fcdd-fedc-4273-82a6-2e40b41d5..._at_e2g2000vbe.googlegroups.com>:
>
> > On Apr 1, 7:00 am, Andreas Mosmann <mosm..._at_expires-30-04-2009.news-
> > group.org> wrote:
> > I doubt that there is a mistake, except in the concept that all
> > cursors are explicit cursors you've coded. Oracle can open additional
>
> Could you explain this?
> What happens, if I f.e. execute an update on this Table Table1, that
> concerns 1000 rows. Will there be created 1000 Cursors? And if so, what
> part of my code makes this happen? How can I avoid this?
Depends on your coding. If you don't want to use simple tools, look into tracing to see exactly what is going on. See metalink Note: 39817.1 http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php http://www.databasejournal.com/features/oracle/article.php/3469891/Collecting-Oracle-Extended-Trace-10046-event.htm http://www.freelists.org/post/oracle-l/Event-10046-trace for example.
>
> > cursors to aid in processing your query, and it appears that at times
> > you have a heavier than 'normal' user load consuming the cursors your
> > trigger normally uses.
> > I suggest you increase the value of the open_cursors parameter; you
> > can double this value and it won't affect memory until these cursors
> > are needed.
>
> I can increase this value, but in fact I still do not understand where
> these open cursors come from.
There is something to be said for David's "up open_cursors and be done with it" approach, and something to be said for figuring out what is really going on. It is entirely possible something non-obvious is happening with your code that should be fixed. Nothing obvious jumped out at me in the code you posted, but I'm not particularly good at seeing those things. I'm hypothesizing your encodexml is requiring a hard parse and new cursor for each changed row, that should stand out when you look at the session cursors with any of the displays or tracing. Looks like you are using bind variables properly so it shouldn't do that, but... you have to look and see.
It's also possible the cursor security enhancements for 11g have some sort of bug or feature that impacts this. Also wondering about bug 8328200 in tracing...
jg
-- _at_home.com is bogus. http://www3.signonsandiego.com/stories/2009/apr/02/coca-cola-australia-040209/?zIndex=76469 http://ecoworldly.com/2009/03/15/zaproot-cow-urine-soda/Received on Thu Apr 02 2009 - 13:40:31 CDT