Re: Sizing Undo tablespace

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<http://uk.mc237.mail.yahoo.com/mc/compose?to=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