trigger- problem: too many open cursors
Andreas Mosmann
it is a bit difficult to explain and more difficult to analyze and we
look for a hint where to search for our problem.
We use Oracle 11.1.0.6.0 and there are triggers, that write down changes
of some tables compressed into a special changelog-table.
Something like
CREATE TRIGGER MyOwner.MyTrigger BEFORE UPDATE
ON TABLE1
FOR EACH ROW
declare
EOraDatumAuszerhalb EXCEPTION;
PRAGMA EXCEPTION_INIT(EOraDatumAuszerhalb, -20100);
lvs_ChangeLog_Fields varchar2(4000);
lvs_ChangeLog_Program varchar2(64);
lvs_ChangeLog_IPAdress varchar2(64);
lvs_ChangeLog_Machine varchar2(64);
begin
-- some probably uninteresting code
if (:new.cdatumfaellung>sysdate) or
(:new.cdatumpflanzung>sysdate) or
(:new.cdatumfaellung<:new.cdatumpflanzung) then
raise EOraDatumAuszerhalb;--exception
end if;
lvs_ChangeLog_Fields:=
EncodeXML('CID',:old.CID,:new.CID)||
EncodeXML('CIDNEBENANLAGE',:old.CIDNEBENANLAGE,:new.CIDNEBENANLAGE)||
EncodeXML...
;
if lvs_ChangeLog_Fields is not null then
lvs_ChangeLog_Fields:='<Spalten>'||chr(13)||lvs_ChangeLog_Fields||'</Spalten>'||chr(13);
select
program,
machine,
sys_context('USERENV','IP_ADDRESS')
into
lvs_ChangeLog_Program,
lvs_ChangeLog_Machine,
lvs_ChangeLog_IPAdress
from
v$session
where
audsid=sys_context('USERENV','SESSIONID');
insert into BAUMPRG.TBCHANGELOG(
clogid,
clogtime,
clogtable,
CLOGIDUSER,
CLOGOSUSER,
CLOGPROGRAM,
CLOGMACHINE,
CLOGIPADDRESS,
clogschema,
clogreason,
cdataid,
cdatafields
)values(
BAUMPRG.PChangeLog.NewGUID(),
sysdate,
'TABLE1',
:new.CIDNUTZER,
SYS_CONTEXT('USERENV','OS_USER'),
lvs_ChangeLog_Program,
lvs_ChangeLog_Machine,
lvs_ChangeLog_IPAdress,
'OWNER',
'U',
:old.cid,
lvs_ChangeLog_Fields
);
end if;
end;
This is only a Code fragment.
EncodeXML is a short function, that returns an XML- string- fragment
only if there are differences between old and new value.
Now the problem: Sometimes (no way to find out all circumstances) there
is an error
ORA-01000: maximum open cursors exceeded
ORA-06512: at MyOwner.MyTrigger, line 167
ORA-04088: error during execution of trigger MyOwner.MyTrigger
Line 167 is the line, where the insert statement is executed
There are no loops in the code and so we do not know where new cursors
are opened. What can we do to find out our mistake(s)?
Many thanks
Andreas
--
wenn email, dann AndreasMosmann <bei> web <punkt> de
ddf
I doubt that there is a mistake, except in the concept that all
cursors are explicit cursors you've coded. Oracle can open additional
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.
David Fitzjarrell
jgar the jorrible
group.org> wrote:
> hi ng,
> Now the problem: Sometimes (no way to find out all circumstances) there
> is an error
>
> ORA-01000: maximum open cursors exceeded
> ORA-06512: at MyOwner.MyTrigger, line 167
> ORA-04088: error during execution of trigger MyOwner.MyTrigger
>
> Line 167 is the line, where the insert statement is executed
>
> There are no loops in the code and so we do not know where new cursors
> are opened. What can we do to find out our mistake(s)?
>
> Many thanks
> Andreas
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de
EM (at least in 10g) has a display of open cursors for a session. You
can also google around for similar code, but I've found the EM display
easy and eye-opening. It's a link on the session details page. The
display has a sql id link to the sql of each cursor, where you can see
the plan, waits, shared cursor statistics, etc.
You might also want to google around for discussions of open_cursors
and session_cached_cursors. The vendor supplied code I'm stuck with
is profligate with cursors, and I think that is OK, since so much is
shared. YMMV.
jg
--
@home.com is bogus.
You'd think Google Calendar would know about daylight savings time...
http://catless.ncl.ac.uk/Risks/25.62.html#subj4
D'Oh!
Andreas Mosmann
<c5b7fcdd-fedc-4273...@e2g2000vbe.googlegroups.com>:
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?
> 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.
> David Fitzjarrell
Thank you
Andreas Mosmann
jgar the jorrible
>
> > On Apr 1, 7:00 am, Andreas Mosmann <mosm...@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
--
@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/