2 Replies Latest reply: Jan 7, 2010 4:09 AM by Donatello Settembrino RSS

    v$undostat and maxqueryid

    user12086635
      Hi,

      Using Oracle 11.1.0.7 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!

      Best
      Jens
        • 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

            http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2174.htm#i1422075

            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
            http://www.oracle.com/technology/oramag/code/tips2004/032904.html

            best regards

            Donatello Settembrino