Re: null event causing concern

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1063984761.892697_at_yasure>...
> John Ashton wrote:
>
> >We seem to have a pl/sql procedure that can occionally take a very
> >long time to complete (> 13 hours). It is a big task but it would
> >normally complete within 2 hours.
> >
> >As I'm not a DBA and I have limited rights so I have only been able
> >get a partial view into what is happening (yes I have asked the
> >support company X's DBA to investigate but there are political reasons
> >why I feel I need to look at this too).
> >
> >The only thing I can see that bothers me is the following from
> >v$session_wait for the PL/SQL procedure session:
> >
> >EVENT P1 P2 P3 TIME SECONDS_IN_WAIT STATE
> >null event 301 87541 1 -1 26 WAITED KNOWN TIME
> >
> >Every time I see this null event, P1 is either 301 or 150 but I don't
> >know what this parameter refers to. I thought maybe it was file# but
> >there are no files above 160.
> >
> >Can anyone tell me what this event might be?
> >
> >Thanks
> >
> >
> I can't tell you whether the above has any relationship to your problem
> but I can tell you that you should
> get a copy of Tom Kyte's book "Expert one-on-one Oracle" or go to
> http://tahiti.oracle.com and learn
> how to use DBMS_PROFILER. It will point you to where the problem is ...
> though not the underlying cause.

I realise that you think that most people who post to newsgroups are idiots who don't bother to read manuals or understand the tools they use but I did mention that I have limited rights to the live server and cannot see much. This includes no read rights to any SQL_TRACE files, no rights to execute DBMS_PROFILER, no login rights to PERFSTAT. The politics of why this is I don't really want to get into.

I cannot duplicate the problem on our local development server (DBMS_PROFILER and STAT_PACK look fine) and it does not occur all the time on the live server. I suspect it occurs when the process has to deal with a certain amount of data which pushes it above a threshold that causes the problem.

Happily, Jonathon has given me some ideas which I will follow up and try to replicate on our server. Received on Mon Sep 22 2003 - 04:16:41 CDT