Re: v$undostat question

On Feb 15, 10:12 am, Chuck <skilover_nos..._at_bluebottle.com> wrote:
> Mark D Powell wrote:
> > On Feb 13, 10:00 am, Chuck <skilover_nos..._at_bluebottle.com> wrote:
> >> Oracle 9.2.0.8
>
> >> How is it possible for maxquerylen to go from 1 to 9000 in a single 10
> >> minute interval? Is this a bug? Can't find anything about it on
> >> metalink. TIA
>
> >> SELECT begin_time,
> >>     end_time,
> >>     maxquerylen
> >> FROM v$undostat
> >> WHERE begin_time BETWEEN
> >> to_date('10-FEB-08 19:19:19', 'dd-mon-yy hh24:mi:ss')
> >> AND
> >> to_date('10-FEB-08 19:29:19',    'dd-mon-yy hh24:mi:ss')
> >> ORDER BY begin_time;
>
> >> BEGIN_TIME         END_TIME           MAXQUERYLEN
> >> ------------------ ------------------ -----------
> >> 10-FEB-08 19:19:19 10-FEB-08 19:29:19           1
> >> 10-FEB-08 19:29:19 10-FEB-08 19:39:19        9387
>
> >> 2 rows selected
>
> > Interesting.  I have no clue and do not have the time to attempt to
> > follow up the one idea I have.  Namely if the database was just
> > restarted about 19:39 on Feb 10 if the reported query run time could
> > include the prior run time for a transaction that now was being rolled
> > back after the restart or after a transaction failover.
>
> > By any chance was the database re-started around the time of the first
> > entry?
>
> Nope. Instance has been up for nearly a year.
>
>
>
> > Is TAF is use on this system? (If so, was there a failover)
>
> TAF?
>
> The instance is on a clustered node (Veritas active/passive) but has
> stated above has been up on the same node for nearly a year.
>
>
>
> > I bet the answer is no to both but those were the only ideas I had.
> > That would leave a 9.2.0.8 bug of some kind.

Or maybe there is parallelization going on, how many processors do you have? Or maybe it is adding something from the other node as they blab back and forth. Do you know what was happening at that time? Backups?

>
> That's what I'm guessing too. Well, this instance will finally go to
> 10gR2 in about a month so I'm not too worried about it.

See Note:420525.1 anyways.

>
> Thanks all. Just wanted to be sure I wasn't overlooking something.

I notice that stats$undostat from statspack has an instance column, unlike v$undostat. See your friendly sprepins.sql.

jg

--
@home.com is bogus.
Howard, we hardly knew ye:  http://www.signonsandiego.com/uniontrib/20080215/news_1m15gerber.html