This content has been marked as final. Show 2 replies
According to this :- http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_3110.htm
MAXQUERYID should list the SQL identifier of the longest running SQL statement in the period (10 minutes)
But when I run the following on a busy PROD system the sql returned is most definitely not the longest running, so my results seem to also indicate issues with maxqueryid.
select SQL_TEXT from v$sqlarea where sql_id in (select maxqueryid from v$undostat);
You could use v$transaction to identify users of undo.
MAXQUERYID SQL identifier of the longest running SQL statement in the period
as shown in the following link
but does not identify (necessarily) that this SQL statement uses UNDO.
MAXQUERYID (in combination with MAXQUERYLEN) if anything you need to estimate correctly the UNDO_RETENTION.
To get an idea about undo_retention and sizing of UNDO tablespace try looking here