Re: Sizing Undo tablespace
- From: Karl Arao <karlarao@xxxxxxxxx>
- To: hrishy <hrishys@xxxxxxxxxxx>
- Date: Wed, 8 Jul 2009 18:33:09 +0800
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@xxxxxxxxxxx> 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@xxxxxxxxx>* wrote:
>
>
> From: Karl Arao <karlarao@xxxxxxxxx>
> Subject: Re: Sizing Undo tablespace
> To: hrishys@xxxxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxx<http://uk.mc237.mail.yahoo.com/mc/compose?to=hrishys@xxxxxxxxxxx>
> > 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
>>
>>
>>
>
>
Other related posts:
