Re: exec STATSPACK.SNAP renders ORA-03113

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<3fcbb925$0$13682$afc38c87_at_news.optusnet.com.au>...
> "Wilson Guerrero-Coltters" <wcoltters_at_yahoo.com> wrote in message
> news:ff6f1d30.0312011325.3c4906f2_at_posting.google.com...
> > Hi,
> >
> > I'm hitting the same problem here. Statspack just stopped working today.
> > I killed after 20 minutes, all the time near 100% CPU.
> > After several runs I gave up.
> > It freeze here
> >
> > INSERT into stats$latch
> > ( snap_id
> > , dbid
> > , instance_number
> > , name
> > , latch#
> > , level#
> > , gets
> > , misses
> > , sleeps
> > , immediate_gets
> > , immediate_mi
> >
> > But i'm on 9i
> >
> > Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
> > PL/SQL Release 9.0.1.0.0 - Production
> > CORE 9.0.1.0.0 Production
> > TNS for Compaq Tru64 UNIX: Version 9.0.1.0.0 - Production
> > NLSRTL Version 9.0.1.0.0 - Production
>
> Well, you're on the initial release of Oracle 9i Release 1, and although the
> bug-fix has been back-ported to 9.0.1 (according to Metalink, anyway), that
> certainly will require the application of a patch before you get the
> benefit. From memory, I believe they're up to 9.0.1.0.4.
>
> > Any suggestions welcome.
> >
> > PS: We don't have access to metalink
>
> Er, well, then you're stuffed. Because patches can only be gotten from
> Metalink as far as I know. Presumably this isn't a licensed system, then?
>
> Regards
> HJR
To anyone interested, I managed to make STATSPACK work again. As have been said by Howard, this must be related to a bug in taking latches.
While we wait for a patch or something
I commented the following lines in the STATSPACK package body:

/*

     insert into stats$latch
          ( snap_id
          , dbid
          , instance_number
          , name
          , latch#
          , level#
          , gets
          , misses
          , sleeps
          , immediate_gets
          , immediate_misses
          , spin_gets
          , sleep1
          , sleep2
          , sleep3
          , sleep4
          , wait_time
          )
     select l_snap_id
          , p_dbid
          , p_instance_number
          , name
          , latch#
          , level#
          , gets
          , misses
          , sleeps
          , immediate_gets
          , immediate_misses
          , spin_gets
          , sleep1
          , sleep2
          , sleep3
          , sleep4
          , wait_time
       from v$latch;

As you see, it makes a select on v$latch, where may be the problem is. There also lines similar to this for v$latch_children and v$latch_parent (these
views also produce a hung when selected), but they are only used with a snapshot level greater than 10. I only require level 6.

Admitedly, with this I will not have the latch statistics in the report, but before this
 "inconvenient" and seeing the statspack reports I didn't have problems with this.
Of course, I don't
know yet if this is gonna cause more problems. So far, a day, there have been no
problems to users or applications. So keep far from v$latch, v$latch_children and
v$latch_parent and hopefully everything will be ok (I hope) while your waiting for your
patch.
Please, understand that this worked for me, while we continue to investigate if this
will cause us more problems. We DO NOT know yet. Take it at your own risk.

Regards,
WGC PS: Still interested to Howard answers to a previous post. Received on Tue Dec 02 2003 - 13:27:16 CST