Re: v$undostat question

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?

Is TAF is use on this system? (If so, was there a failover)

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.

Just speculating -- Mark D Powell -- Received on Wed Feb 13 2008 - 10:57:29 CST