RE: Where do you find undo info in trace files, and how
- From: Wolfson Larry - lwolfs <lawrence.wolfson@xxxxxxxxxx>
- To: "'jreyes@xxxxxxxxxxxxxxxx'" <jreyes@xxxxxxxxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 29 Sep 2004 18:23:07 -0500
Juan,
Since you're looking at UNDO did you see this formula?
Larry
--Written by Lawrence Wolfson 2004
-- This script helps determine how well your UNDO tablespace is performing
-- and how you may change your parameters.
-- OEM appears to be reporting "Worst case" information.
-- Formula below is from 9i Data Administrator's Guide Chapter 13 page 11.
--desc v$UNDOSTAT
DEFINE BS=8192
SET LINESIZE 200
SET PAGESIZE 10000
SET NEWPAGE NONE
COL Started FOR A20 HEA ' S T A R T T I M E'
COL Ended FOR A20 HEA ' E N D T I M E'
COL UNDOTSN FOR 9,999 HEA 'ACTIVE| UNDO '
COL UNDOBLKS FOR 999,999,999
COL TXNCOUNT FOR 999,999,999
COL MAXQUERYLEN FOR 999,999,999
COL MAXCONCURRENCY FOR 9999 HEA 'MAX|CON'
COL UNXPSTEALCNT FOR 999,999,999
COL UNXPBLKRELCNT FOR 999,999,999
COL UNXPBLKREUCNT FOR 999,999,999
COL EXPSTEALCNT FOR 999,999,999
COL EXPBLKRELCNT FOR 999,999,999
COL EXPBLKREUCNT FOR 999,999,999
COL SSOLDERRCNT FOR 999,999,999
COL NOSPACEERRCNT FOR 999,999,999
SET FEEDBACK OFF
SET HEADING OFF
SET VERIFY OFF
SPOOL $OR/UNDOSTATS.$SID
SELECT 'V$UNDOSTAT has 1008 rows repesenting every 10 minutes for a 7 day
period.' FROM DB;
SELECT 'V$UNDOSTATs for '||name FROM DB;
SELECT 'MAX CONCURRENT TRANSACTION IS==>'||MAX(MAXCONCURRENCY) FROM
V$UNDOSTAT;
SELECT 'MAX QUERY LENGTH in MINUTES IS==>'||ROUND( MAX(MAXQUERYLEN)/60,1)
FROM V$UNDOSTAT;
SELECT 'UNDO BLOCKS/SEC (AVG) IS==>'||ROUND( AVG(UNDOBLKS)/600)
FROM V$UNDOSTAT;
SELECT 'UNDO NEEDED FOR 1 HOUR IS==>'||ROUND(
1*3600*(AVG(UNDOBLKS)/600)*&&BS/1073741824,2)||' Gig' FROM V$UNDOSTAT;
SELECT 'UNDO NEEDED FOR 2 HOURS IS==>'||ROUND(
2*3600*(AVG(UNDOBLKS)/600)*&&BS/1073741824,2)||' Gig' FROM V$UNDOSTAT;
SELECT 'UNDO NEEDED FOR 12 HOURS
IS==>'||ROUND(12*3600*(AVG(UNDOBLKS)/600)*&&BS/1073741824,2)||' Gig' FROM
V$UNDOSTAT;
SELECT 'UNDO NEEDED FOR 24 HOURS
IS==>'||ROUND(24*3600*(AVG(UNDOBLKS)/600)*&&BS/1073741824,2)||' Gig' FROM
V$UNDOSTAT;
SET HEADING ON
SET FEEDBACK ON
SELECT
TO_CHAR(BEGIN_TIME,'DD-MON-YYYY HH24 MI SS') Started
-- ,TO_CHAR( END_TIME,'DD-MON-YYYY HH24 MI SS') Ended
-- ,UNDOTSN
,UNDOBLKS
,TXNCOUNT
,MAXQUERYLEN
,MAXCONCURRENCY
,UNXPSTEALCNT
-- ,UNXPBLKRELCNT
-- ,UNXPBLKREUCNT
-- ,EXPSTEALCNT
-- ,EXPBLKRELCNT
-- ,EXPBLKREUCNT
-- ,SSOLDERRCNT
-- ,NOSPACEERRCNT
FROM V$UNDOSTAT
ORDER BY 1
;
SPOOL OFF
UNDEFINE BS
**********************************************************************
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination,
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
