View the Total Size
of Undo:-
SQL> select tablespace_name,sum(bytes)/1024/1024 from
dba_data_files group by tablespace_name where tablespace_name = 'UNDO_TS';
TABLESPACE_NAME
SUM(BYTES)/1024/1024
------------------------------
--------------------
UNDO_TS
13206
View used Size space
of Undo :-
SQL> select tablespace_name,sum(bytes)/1024/1024 from
dba_segments where tablespace_name = 'UNDO_TS' group by tablespace_name;
TABLESPACE_NAME
SUM(BYTES)/1024/1024
------------------------------
--------------------
UNDO_TS
12864.6172
View undo Segment statistics
based on status:-
SQL> select tablespace_name, status, sum(blocks) *
8192/1024/1024 "MB" from dba_undo_extents group by tablespace_name,
status;
TABLESPACE_NAME
STATUS MB
------------------------------
--------- ----------
UNDO_TS
EXPIRED 7.1796875
UNDO_TS
UNEXPIRED 12863.4609
View the No. Undo
block required per second:-
SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC" FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
180.633333
To know the Optimal
undo_rention seconds:-
Optimal Undo Retention =
Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REQ_PSC)
SQL>
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25)
"UNDO RETENTION [Sec]",
ROUND((d.undo_size
/ (to_number(f.value) *
g.undo_block_per_sec)))
"OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT
SUM(a.bytes) undo_size
FROM
v$datafile a,
v$tablespace
b,
dba_tablespaces
c
WHERE
c.contents = 'UNDO'
AND c.status
= 'ONLINE'
AND b.name =
c.tablespace_name
AND a.ts# =
b.ts#
) d,
v$parameter
e,
v$parameter
f,
(
SELECT
MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM
v$undostat
) g
WHERE e.name
= 'undo_retention'
AND f.name =
'db_block_size';
ACTUAL UNDO
SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
------------------------
------------------------- ----------------------------
13206 18000
9358
To know the undo
space required for the current database load/activity:-
SQL> SELECT
d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25)
"UNDO RETENTION [Sec]",
(TO_NUMBER(e.value)
* TO_NUMBER(f.value) *
g.undo_block_per_sec)
/ (1024*1024)
"NEEDED
UNDO SIZE [MByte]"
FROM (
SELECT
SUM(a.bytes) undo_size
FROM v$datafile
a,
v$tablespace
b,
dba_tablespaces
c
WHERE
c.contents = 'UNDO'
AND c.status
= 'ONLINE'
AND b.name =
c.tablespace_name
AND a.ts# =
b.ts#
) d,
v$parameter
e,
v$parameter
f,
(
SELECT
MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM
v$undostat
) g
WHERE e.name
= 'undo_retention'
AND f.name =
'db_block_size' 24 ;
ACTUAL UNDO
SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
------------------------
------------------------- ------------------------
13206 18000
25401.5625
Undo space Estimate
Formula :-
Undo_rettention * undo_blcoks_per_sec * db_block_size
Optimal undo retention size Formual :-
Actual undo
size
___________________
undo_blcoks_per_sec
* db_block_size
===============
oracle 9i
undo usage:-
set linesize
120
set pagesize
60
alter session set nls_date_format = "dd-Mon-yyyy
hh24:mi:ss";
COL TXNCOUNT
FOR 99,999,999 HEAD 'Txn. Cnt.'
COL
MAXQUERYLEN FOR 99,999,999 HEAD 'Max|Query|Sec'
COL
MAXCONCURRENCY FOR 9,999 HEAD 'Max|Concr|Txn'
COL
bks_per_sec FOR 99,999,999 HEAD 'Blks per|Second'
COL
kb_per_second FOR 99,999,999 HEAD 'KB per|Second'
COL
undo_mb_required FOR 999,999 HEAD 'MB undo|Needed'
COL
ssolderrcnt FOR 9,999 HEAD 'ORA-01555|Count'
COL
nospaceerrcnt FOR 9,999 HEAD 'No Space|Count'
break on
report
compute max
of txncount -
maxquerylen
-
maxconcurrency
-
bks_per_sec
-
kb_per_second
-
undo_mb_required
on report
compute sum
of -
ssolderrcnt
-
nospaceerrcnt
on report
SELECT
begin_time,
decode(txncount,0,lag(txncount)
over (order by end_time), txncount) - lag(txncount) over (order by end_time) as
txncount,
maxquerylen,
maxconcurrency,
undoblks/((end_time
- begin_time)*86400) as bks_per_sec,
(undoblks/((end_time
- begin_time)*86400)) * t.block_size/1024 as kb_per_second,
((undoblks/((end_time
- begin_time)*86400)) * t.block_size/1024) * TO_NUMBER(p2.value)/1024 as
undo_MB_required,
ssolderrcnt,
nospaceerrcnt
FROM
v$undostat s,
dba_tablespaces
t,
v$parameter
p,
v$parameter
p2
WHERE
t.tablespace_name = UPPER(p.value)
AND p.name =
'undo_tablespace'
AND p2.name
= 'undo_retention'
ORDER BY
begin_time;
show parameter undo
clear
computes
Which queries are using undo segments
select
a.name, b.xacts, c.sid, c.serial#, c.username, d.sql_text
from
v$rollname a, v$rollstat b, v$session c, v$sqltext d,
v$transaction
e
where a.usn
= b.usn
and b.usn =
e.xidusn
and c.taddr
= e.addr
and
c.sql_address = d.address
and
c.sql_hash_value = d.hash_value
order by
a.name, c.sid, d.piece;
ouput:-
name xacts sid serial# username sql_text
RB1 1 5 33 USER1 delete from test1;
RB2 1 7 41 USER9 update orders set items = 5 where orderno =
555;
Undo Management in
oracle:-
How undo Tablespace Shrinks ---
Explanation by Thomas Kyte.
Question:-
Just few
days ago, the UNDOTBS tablespace is about 99.8% used
(UNDOTBS
size is 16G in one file with autoextend 'NO', database size is 450G).
The database
was running huge work. I tried to add one datafile to the UNDOTBS.
My colleague
told me that we don't need to do that because Oracle will automatically
handle it,
if add more space to it, the UNDOTBS will continue to grow,so I did not
add a file
to it. Maybe one hour or maybe two hours later, the UNDBTBS tablespace
shrink to
about 14G, is less than 76% used.
I am
wondering how Oracle handle UNDOTBS tablespace size if 100% used.
Answer by
Thomas Kyte:-
The undo
tablespace is a series of circular queues, a set of undo segments (made up of
extents).
The undo
tablespace will have a set of undo segments in it, each of these is a circular
queue, the extents point to each other, the last extent in the segment points
to the first extent.
The size of
this queue is controlled by you indirectly via your setting of undo_retention.
So, let's
say you set undo_retention to 1 hour. We will ATTEMPT to hold undo in the undo
segments for at least one hour.
So, as you
are aware, as we are processing transactions we write UNDO to these segments.
Now, as you
generate undo, we get to the end of an extent and want to advance into the next
extent (the last extent remember considers the FIRST extent to be its NEXT
extent - the circular queue concept).
Before we
advance into the next extent - it must not contain any active transactions - if
it does, we cannot advance and would simply add another new extent into this
queue. We would get that extent from
a) free
space in the undo tablespace OR
b) by
extending the datafiles for the undo tablespace to get more free space
(not in your
case, you disabled that) OR
c) by
stealing an extent from some other undo segment - finding its oldest unused
extent and stealing it.
If all of
that failed, you would receive an error and the statement you were executing
would be rolled back.
Now, let's
say the next extent is not active - before we advance into it, we'll check the
age of the data in it. If the age of the data is more than 1 hour (our
retention period) - we'll advance into it. If the age of the data is less than
one hour we will try
a) add
extent from free space so as to not overwrite the data
b) add
extent from free space after autoextending a file
c) to steal
an extent from another undo segment that is not active and has data over an
hour old.
If none of
that works, we'll have to advance into that extent and prematurely
"expire" this undo (eg: violate your undo retention request). We will
not fail (unless you set the undo retention guarantee)
You can
monitor all of that in v$undostat
Now, after a
while, some extents in your undo tablespace might become older than one hour
hold (data is no longer needed), we can and do release these extents over time
as we online and offline undo segments and such.
That is the
"shrinking" you saw - we just freed up data we didn't need anymore,
putting it back into the free space so that when we need to grow again - it is
there.
Free space in Undo Tablespace is the
totalsize of the undo minus the sum of bytes irrespecitve of status.
SQL> select tablespace_name, status, sum(bytes)/1024/1024
from dba_undo_extents group by tablespace_name, status;
TABLESPACE_NAME
STATUS SUM(BYTES)/1024/1024
------------------------------
--------- --------------------
UNDO_TS
ACTIVE 256
UNDO_TS
EXPIRED 75.1171875
UNDO_TS UNEXPIRED
7106.03125
SQL> select tablespace_name, sum(bytes)/1024/1024 from
dba_free_space where tablespace_name = 'UNDO_TS' group by tablespace_name;
TABLESPACE_NAME
SUM(BYTES)/1024/1024
------------------------------
--------------------
UNDO_TS
5768.3125
SQL> select tablespace_name, sum(bytes)/1024/1024 from
dba_Data_files where tablespace_name = 'UNDO_TS' group by tablespace_name;
TABLESPACE_NAME
SUM(BYTES)/1024/1024
------------------------------
--------------------
UNDO_TS
13206
SQL> select tablespace_name, sum(bytes)/1024/1024 from
dba_segments where tablespace_name = 'UNDO_TS' group by tablespace_name;
TABLESPACE_NAME
SUM(BYTES)/1024/1024
------------------------------
--------------------
UNDO_TS
7461.14844
SQL> select totalsize - usedextents "Free Space in
Undo Tablespace" from
( select sum(bytes)/1024/1024 usedextents from
dba_undo_extents),
( select sum(bytes)/1024/1024 totalsize from dba_data_files
where tablespace_name = 'UNDO_TS' group by tablespace_name);
Free Space in
Undo Tablespace
-----------------------------------
5563.85156
To find the No. of transaction utilizing
undo segments.
SQL>
select
substr(lkd.os_user_name,1,8)
"OS User",
substr(lkd.oracle_username,1,8)
"DB User",
substr(obj.owner,1,8)
"Schema",
substr(obj.object_name,1,20)
"Object Name",
substr(obj.object_type,1,10)
"Type",
substr(rbs.segment_name,1,5)
"RBS",
substr(trn.used_urec,1,12)
"# of Records"
from
v$locked_object
lkd,
dba_objects
obj,
dba_rollback_segs
rbs,
v$transaction
trn,
v$session
ses
where
obj.object_id
= lkd.object_id
and
rbs.segment_id = lkd.xidusn
and
trn.xidusn = lkd.xidusn
and
trn.xidslot = lkd.xidslot
and
trn.xidsqn = lkd.xidsqn -- added for completeness
and
ses.taddr = trn.addr
;
For more info check
the below ..
How to determine undo usage in Oracle
Overview
Undo logs. Traditionally transaction undo information was
stored in Rollback Segments until a commit or rollback statement was issued.
Automatic undo management allows the DBA to specify how long undo information
should be retained after commit, preventing “snapshot too old” errors on long
running queries.
This is done by setting the UNDO_RETENTION parameter. The
default is 900 seconds (5 minutes), and you can set this parameter to guarantee
that Oracle keeps undo logs for extended periods of time. Rather than having to
define and manage rollback segments, you can simply define an Undo tablespace
and let Oracle take care of the rest. Turning on automatic undo management is
easy. All you need to do is create an undo tablespace and set UNDO_MANAGEMENT =
AUTO.
With the below information gathered by SQL query, you may
have a idea to prevent undo space is running out so quickly.
Undo Segments
In Undo Segments there are three types of extents, they are
Unexpired Extents – Undo data whose age is less than the
undo retention period.
Expired Extents – Undo data whose age is greater than the
undo retention period.
Active Extents – Undo data that is part of the active
transaction.
The sequence for using extents is as follows,
1. A new extent will be allocated from the undo tablespace
when the requirement arises. As undo is being written to an undo segment, if
the undo reaches the end of the current extent and the next extent contains
expired undo then the new undo (generated by the current transaction) will wrap
into that expired extent, in preference to grabbing a free extent from the undo
tablespace free extent pool.
2. If this fails because of no available free extents and we
cannot autoextend the datafile, then Oracle tries to steal an expired extent
from another undo segment.
3. If it still fails because there are no extents with
expired status then Oracle tries to reuse an unexpired extent from the current
undo segment.
4. If even that fails, Oracle tries to steal an unexpired
extent from another undo segment.
5. If all the above fails, an Out-Of-Space error will be
reported.
Check the overall status for undos.
select tablespace_name, status, sum(blocks) *
8192/1024/1024/1024 GB from dba_undo_extents group by tablespace_name, status;
TABLESPACE_NAME STATUS GB
------------------------------ --------- ----------
UNDOTBS1 UNEXPIRED 2.29626465
UNDOTBS2 UNEXPIRED 11.0892944
UNDOTBS1 EXPIRED 7.20245361
UNDOTBS2 EXPIRED 1.80932617
UNDOTBS2 ACTIVE .015625
Undo Blocks per Second
SELECT
MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM
v$undostat;
Optimal Undo Retention
209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec]
Using Inline Views,
you can do all in one query!
SELECT
d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25)
"UNDO RETENTION [Sec]",
ROUND((d.undo_size
/ (to_number(f.value) *
g.undo_block_per_sec)))
"OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT
SUM(a.bytes) undo_size
FROM
v$datafile a,
v$tablespace
b,
dba_tablespaces
c
WHERE
c.contents = 'UNDO'
AND c.status
= 'ONLINE'
AND b.name =
c.tablespace_name
AND a.ts# =
b.ts#
) d,
v$parameter
e,
v$parameter
f,
(
SELECT
MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM
v$undostat
) g
WHERE e.name
= 'undo_retention'
AND f.name =
'db_block_size'
Calculate Needed UNDO Size for given Database Activity
SELECT
d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25)
"UNDO RETENTION [Sec]",
(TO_NUMBER(e.value)
* TO_NUMBER(f.value) *
g.undo_block_per_sec)
/ (1024*1024)
"NEEDED
UNDO SIZE [MByte]"
FROM (
SELECT
SUM(a.bytes) undo_size
FROM
v$datafile a,
v$tablespace
b,
dba_tablespaces
c
WHERE
c.contents = 'UNDO'
AND c.status
= 'ONLINE'
AND b.name =
c.tablespace_name
AND a.ts# =
b.ts#
) d,
v$parameter
e,
v$parameter
f,
(
SELECT
MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM
v$undostat
) g
WHERE e.name
= 'undo_retention'
AND f.name =
'db_block_size'
http://www.orafaq.com/node/61
http://maheshkumardba.blogspot.in/2012/07/scripts-undo-estimates.html
No comments:
Post a Comment