Re: Sizing Undo tablespace

From: hrishy <hrishys_at_yahoo.co.uk>
Date: Wed, 8 Jul 2009 11:32:05 +0000 (GMT)
Message-ID: <283282.97270.qm_at_web23705.mail.ird.yahoo.com>

Hi Karl
 
Thanks for the help.
 
UNXPSTEALCNT     

UNXPBLKRELCNT     
UNXPBLKREUCNT     
EXPSTEALCNT     

EXPBLKRELCNT    
EXPBLKREUCNT    
 
are all zero.
Activeblks,unexpiredblks,expiredblks however are not zero.  
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: "hrishy" <hrishys_at_yahoo.co.uk>
Cc: oracle-l_at_freelists.org
Date: Wednesday, 8 July, 2009, 11:33 AM

Hi Hrishy,

You still need some more info to arrive on that tuning conclusion. Your query just consumed 108MB (if you have 8192 blocksize)

Monitor your V$UNDOSTAT, check if these columns are having non-zero values and if they happen often, else it should be okay...

• indication of space pressure UNXPSTEALCNT      The number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests UNXPBLKRELCNT     The number of unexpired blocks removed from undo segments to be used by other transactions UNXPBLKREUCNT     The number of unexpired undo blocks reused by transactions EXPSTEALCNT     The number of attempts when expired extents were stolen from other undo segments to satisfy a space requests EXPBLKRELCNT     The number of expired extents stolen from other undo segments to satisfy a space request EXPBLKREUCNT     The number of expired undo blocks reused within the same undo segments
• UNDO_RETENTION not properly set SSOLDERRCNT     The number of ORA-1555 errors that occurred during the interval
• serious space problem NOSPACEERRCNT     The number of Out-of-Space errors

Also, the "end_time - begin_time" should be your peak workload.

I'll just refer you to these Metalink Notes for further reading...

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

10g NEW FEATURE on AUTOMATIC UNDO RETENTION       Doc ID:     240746.1

Automatic Tuning of Undo_retention Causes Space Problems       Doc ID:     420525.1

FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU)       Doc ID:     461480.1

ORA-1555 Using Automatic Undo Management - How to troubleshoot       Doc ID:     Note:389554.1

ORA-01555 Using Automatic Undo Management - Causes and Solutions       Doc ID:     Note:269814.1

ORA-01555 "Snapshot too old" - Detailed Explanation       Doc ID:     Note:40689.1

Full UNDO Tablespace In 10gR2
      Doc ID:     413732.1

Database Transaction's info
      Doc ID:     832368.1

LOBS - Storage, Read-consistency and Rollback       Doc ID:     Note:162345.1

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

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

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 - 06:32:05 CDT