2 Replies Latest reply on Jan 7, 2010 10:09 AM by Donatello Settembrino

    v$undostat and maxqueryid


      Using Oracle one of our customers had space problems with UNDO tablespace. We tried to get an overview of whats going on using v$undostat.

      One of the columns is MAXQUERYID and join to v$sqlarea we tried to pinpoint longrunning sql. But even on heavy used production system (or low used testsystems) we always see one statement: select 1 from obj$ where name = 'DBA_QUEUE_SCEDULES'

      Anyone knows why we always get this and/or maybe what it is? It is not running slow if tried in sqlplus, so we think we miss a point here!

        • 1. Re: v$undostat and maxqueryid
          Robert Geier
          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.
          • 2. Re: v$undostat and maxqueryid
            Donatello Settembrino
            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

            best regards

            Donatello Settembrino