Re: Sizing Undo tablespace

From: hrishy <hrishys_at_yahoo.co.uk>
Date: Wed, 8 Jul 2009 07:27:54 +0000 (GMT)
Message-ID: <897020.33474.qm_at_web23707.mail.ird.yahoo.com>

Hi Karl
 

Thanks for the script.
According to the script i need a undo tablespace with a size of 34Mb. However past few days through my monitoring script i am getting a errro message saying my undo tablespace is above 90% full.
 

And i ran another script to find undo hoggers SELECT TO_CHAR(begin_time, 'DD-MON-RR HH24:MI'),
          TO_CHAR(end_time, 'DD-MON-RR HH24:MI'),
          tuned_undoretention,
          maxquerylen, maxqueryid,UNDOBLKS
FROM v$undostat
ORDER BY end_time
 

and this gives me a sqlid (maxqueryid) where the undoblks value is 13841.
 

Should i start tuning the query in question ?
 

regards
Hrishy
 

• On Wed, 8/7/09, Karl Arao <karlarao_at_gmail.com> wrote:

From: Karl Arao <karlarao_at_gmail.com>
Subject: Re: Sizing Undo tablespace
To: hrishys_at_yahoo.co.uk
Cc: oracle-l_at_freelists.org
Date: Wednesday, 8 July, 2009, 8:12 AM

Hi Hrishy,

You could check this Metalink Note...

How To Size UNDO Tablespace For Automatic Undo Management
      Doc ID:     262066.1

You'll get from the note the query below:

SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes" FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),

(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat), 
(select block_size as DBS from dba_tablespaces where tablespace_name= 
(select upper(value) from v$parameter where name = 'undo_tablespace'));

• Karl Arao http://karlarao.wordpress.com

On Wed, Jul 8, 2009 at 2:50 PM, hrishy <hrishys_at_yahoo.co.uk> wrote:

Hi
 

I am trying to size my undo tablepsace by looking at the undo rates.i.e the column UNDOBLKS in v$undostat.
 

Value for undoblks for particular sql is 13841 and my database size is 8kb.Does this mean that i need to have a undo tablespace size of
 

select 8192*13841/1024/1024 from dual
108Gb ?
 

Not the particular sql does a corelated update of mere  72762 rows and the figure of 108Gb doesnt make much sense .This is a JDBC app and i dont have much idea if this sql is in some for loop or something.
 

regards
Hrishy
 

regards
Hrishy
 

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 08 2009 - 02:27:54 CDT