Re: Sizing Undo tablespace
- From: hrishy <hrishys@xxxxxxxxxxx>
- To: Karl Arao <karlarao@xxxxxxxxx>
- Date: Wed, 8 Jul 2009 11:32:05 +0000 (GMT)
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@xxxxxxxxx> wrote:
From: Karl Arao <karlarao@xxxxxxxxx>
Subject: Re: Sizing Undo tablespace
To: "hrishy" <hrishys@xxxxxxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx
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@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> 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:
