Re: v$undostat question

On Feb 13, 9: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

Maxquerylen looks like the cumulative time for the SQL (sql_id is maxqueryid), not the time for that 10 minute window. Here's a test in 10.2.0.3. (Grant execute on dbms_lock to the user first.)

create function f return number as
begin
 lock table t in row share mode; <-- so undo is used  dbms_lock.sleep(1800); <-- longer than 600 seconds  return 1;
end;
/
select f from dual;

In another session, I keep checking v$undostat. I see for this SQL, maxquerylen first has 306, then another row where it's 909, later 1509. Each row has a 10 minute time span (end_time-begin_time). I don't know why I get 306 for the first one. But the other two have difference of 600 seconds between them. So the time is cumulative.

Yong Huang Received on Wed Feb 20 2008 - 15:33:02 CST