John, Since I'm up, I'll give this a shot. The v$undostat.maxquerylen is, not surprisingly, the maximum length of time it took to satisfy a query. It's quite useful information when it's time to pick a value for UNDO_RETENTION. Much larger than maxquerylen, and you'll (potentially) suffer from excess I/O and undo tablespace space consumption. Much less than it, you're likely to suffer from ORA-1555.
As to determining which specific SQL is responsible for that long running time, you might try ELAPSED_TIME/EXECUTIONS from V$SQL. However, there are a few problems there. If the long run was a one-time (or relatively infrequent) spike, then you may never find it, if the problem SQL sees lots of executions, as it will get averaged out. Also, the maxquerylen value is since instance startup. The SQL in V$SQL could be aged out at any time, and so may not even be there. To track it down, you may try some statspack snapshots over the problem timeframe, and see what that gets you. Or, the brute force method, of course, just reduce UNDO_RETENTION, and see which query blows up first! ;-) Hope that helps, -Mark -----Original Message----- From: John Dunn [mailto:[EMAIL PROTECTED] Sent: Tue 1/6/2004 3:54 AM To: Multiple recipients of list ORACLE-L Cc: Subject: RE: undotbs01.dbf just keeps growing Daniel How can i use the v$undostat.maxquerylen value to configuure the undo tablespace? Also, how can I tell what the longest transaction is actually doing?, i.e what sql is being run? John -----Original Message----- Sent: 05 January 2004 17:54 To: Multiple recipients of list ORACLE-L Jared, It sure is nice to be missed. I'll make sure my secretary calls you about my future vacation plans...:) You've nailed the problem. Autoextend, automatic undo and high undo retention is a recipe for high disk usage. The aum algorithm is such that preference is given to extending over reuse (especially since expire time propogation is a problem). In order to find the length of the longest transaction, reference the v$undostat.maxquerylen value. Beware as there are known bugs with this view, so examine the output carefully to make sure it makes sense. Daniel Fink Jared Still wrote: > The data file(s) for your undo tablespace is likely set > as autoextend with an unlimited size. > > Run the attached script to check it. > > If so, you can use this to put a limit on it: > > alter database datafile '<your file name>' autoextend on next 200m > maxsize 2000m; > > Adjust the numbers for your system. > > You should probably investigate why it continues to grow so large. > > I haven't yet converted our production databases to UNDO, having > only recently migrated to 9i, so I don't have any useful advice > past this. > > There are others that will be able to offer more for this. ( Dan > Fink, where are you? This might even get Kirti to take a break > from his book for a few minutes ) > > HTH > > Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
<<winmail.dat>>
