Thursday, October 11, 2012
All Undo related queries
To check retention guarantee for undo tablespace:
---------------------------------------------------
select tablespace_name,status,contents,logging,retention from dba_tablespaces where tablespace_name like '%UNDO%';
To show ACTIVE/EXPIRED/UNEXPIRED Extents of Undo Tablespace:
-------------------------------------------------------------
select tablespace_name,
status,
count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",
sum(blocks)*8/(1024*1024) total_space
from dba_undo_extents
group by tablespace_name, status;
Extent Count and Total Blocks:
-------------------------------
set linesize 152
col tablespace_name for a20
col status for a10
select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
where tablespace_name in ('&undotbsp')
group by tablespace_name,status;
To show UndoRetention Value:
-----------------------------
Show parameter undo_retention;
Undo retention in hours:
-------------------------
col "Retention" for a30
col name for a30
col value for a50
select name "Retention",value/60/60 "Hours" from v$parameter where name like '%undo_retention%';
To check space related statistics of UndoTablespace from stats$undostat of 90 days:
-------------------------------------------------------------------------------------
select UNDOBLKS,BEGIN_TIME,MAXQUERYLEN,UNXPSTEALCNT,EXPSTEALCNT,NOSPACEERRCNT from stats$undostat
where BEGIN_TIME between sysdate-90 and sysdate and UNXPSTEALCNT > 0;
To check space related statistics of UndoTablespace from v$undostat:
----------------------------------------------------------------------
select
sum(ssolderrcnt) "Total ORA-1555s",
round(max(maxquerylen)/60/60) "Max Query HRS",
sum(unxpstealcnt) "UNExpired STEALS",
sum(expstealcnt) "Expired STEALS"
from v$undostat
order by begin_time;
Date wise occurrence of ORA-1555:
------------------------------------
select to_char(begin_time, 'mm/dd/yyyy hh24:mi') "Int. Start",
ssolderrcnt "ORA-1555s", maxquerylen "Max Query",
unxpstealcnt "UNExp SCnt",UNXPBLKRELCNT "UnEXPblks", expstealcnt "Exp SCnt",EXPBLKRELCNT "ExpBlks",
NOSPACEERRCNT nospace
from v$undostat where ssolderrcnt>0
order by begin_time;
Total number of ORA-1555s since instance startup:
-------------------------------------------------
select 'TOTAL # OF ORA-01555 SINCE INSTANCE STARTUP : '
to_char(startup_time,'DD-MON-YY HH24:MI:SS')
from v$instance;
To check for Active Transactions:
----------------------------------
set head on
select usn,extents,round(rssize/1048576)
rssize,hwmsize,xacts,waits,optsize/1048576 optsize,shrinks,wraps
from v$rollstat where xacts>0
order by rssize;
Undo Space Utilization by each Sessions:
------------------------------------------
set lines 200
col sid for 99999
col username for a10
col name for a15
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,rn.name,(t.used_ublk *8)/1024/1024 SizeGB
from v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;
List of long running queries since instance startup:
----------------------------------------------------
set head off
select 'LIST OF LONG RUNNING - QUERY SINCE INSTANCE STARTUP' from dual;
set head on
select *
from
(select to_char(begin_time, 'DD-MON-YY hh24:mi:ss') BEGIN_TIME ,
round((maxquerylen/3600),1) Hours
from v$undostat
order by maxquerylen desc)
where rownum < 11;
Undo Space used by all transactions:
--------------------------------------
set lines 200
col sid for 99999
col username for a10
col name for a15
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,rn.name,(t.used_ublk *8)/1024/1024 SizeGB
from v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;
List of All active Transactions:
---------------------------------
select sid,username,
t.used_ublk ,t.used_urec
from v$transaction t,v$session s
where t.addr=s.taddr;
To list all Datafile of UndoTablespace:
-----------------------------------------
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDO%'
or tablespace_name like '%RBS%'
order by tablespace_name,file_name;
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDOTBS2%'
order by tablespace_name,file_name;
col file_name for a40
set pagesize 100
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%APPS_UNDOTS1%'
order by tablespace_name,file_name;
select file_name,tablespace_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_data_files where file_name like '%undo%' order by file_name;
To check when a table is last analysed:
----------------------------------------
select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED,DEPENDENCIES,DROPPED from dba_tables where TABLE_NAME like 'MLC_PICK_LOCKS_DETAIL';
select OWNER,TABLE_NAME,TABLESPACE_NAME,LAST_ANALYZED,PARTITIONED,DEPENDENCIES,DROPPED from dba_tables where TABLE_NAME like 'APPS.XLA_AEL_GL_V';
To list all Undo datafiles with status and size
------------------------------------------------
show parameter undo
show parameter db_block_size
col tablespace_name form a20
col file_name form a60
set lines 120
select tablespace_name, file_name, status, bytes/1024/1024 from dba_data_files
where tablespace_name=(select tablespace_name from dba_tablespaces where contents='UNDO');
Total undo space:
---------------------
select sum(bytes)/1024/1024/1024 GB from dba_data_files where tablespace_name='&Undo_TB_Name';
Undo Tablespace:
--------------------
select tablespace_name from dba_tablespaces where tablespace_name like '%UNDO%';
To find MaxQueryLength from stats$undostat:
--------------------------------------------
Select Max(MAXQUERYLEN) from stats$undostat;
>select max(maxquerylen) from v$undostat;
>select begin_date,u.maxquerylen from
(select to_char(begin_time,'DD-MON-YYYY:HH24-MI-SS') begin_date,maxquerylen
from v$undostat order by maxquerylen desc) u where rownum<11>
>select begin_date,u.maxquerylen from
(select maxquerylen,to_char(begin_time,'DD-MON-YYYY:HH24-MI-SS') begin_date from
v$undostat order by maxquerylen DESC) u where rownum<26 asc="asc" begin_date="begin_date" by="by" desc="desc" maxquerylen="maxquerylen" order="order">
>select begin_date,u.maxquerylen from
(select maxquerylen,to_char(begin_time,'DD-MON-YYYY:HH24-MI-SS') begin_date from
v$undostat order by maxquerylen DESC) u where rownum<26 by="by" desc="desc" maxquerylen="maxquerylen" order="order">
>select sum(u.maxquerylen)/25 AvgUndoRetTime
from (select maxquerylen from v$undostat order by maxquerylen desc) u where rownum<26>
>select sum(u.maxquerylen)
from (select maxquerylen from v$undostat order by maxquerylen desc) u where rownum<26>
DBA_UNDO_EXTENTS:
-------------------
set linesize 152
col tablespace_name for a20
col status for a10
select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status
order by tablespace_name;
Mapping Undo Segments to usernames:
------------------------------------
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,(rs.rssize)/1024/1024 MB,rn.name
from v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;
Total Undo Statistics:
----------------------------
alter session set nls_date_format='dd-mon-yy hh24:mi';
set lines 120
set pages 2000
select BEGIN_TIME, END_TIME, UNDOBLKS, TXNCOUNT , MAXQUERYLEN , UNXPSTEALCNT ,
EXPSTEALCNT , SSOLDERRCNT , NOSPACEERRCNT
from v$undostat;
Total Undo Statistics since specified year:
---------------------------------------------
select 'TOTAL STATISTICS SINCE Jan 01, 2005 - STATSPACK' from dual;
set head on
set lines 152
column undotsn format 999 heading 'Undo
TS#';
column undob format 9,999,999,999 heading 'Undo
Blocks';
column txcnt format 9,999,999,999,999 heading 'Num
Trans';
column maxq format 999,999 heading 'Max Qry
Len (s)';
column maxc format 9,999,999 heading 'Max Tx
Concurcy';
column snol format 9,999 heading 'Snapshot
Too Old';
column nosp format 9,999 heading 'Out of
Space';
column blkst format a13 heading 'uS/uR/uU/
eS/eR/eU' wrap;
column unst format 9,999 heading 'Unexp
Stolen' newline;
column unrl format 9,999 heading 'Unexp
Relesd';
column unru format 9,999 heading 'Unexp
Reused';
column exst format 9,999 heading 'Exp
Stolen';
column exrl format 9,999 heading 'Exp
Releas';
column exru format 9,999 heading 'Exp
Reused';
select undotsn
, sum(undoblks) undob
, sum(txncount) txcnt
, max(maxquerylen) maxq
, max(maxconcurrency) maxc
, sum(ssolderrcnt) snol
, sum(nospaceerrcnt) nosp
, sum(unxpstealcnt)
'/'
sum(unxpblkrelcnt)
'/'
sum(unxpblkreucnt)
'/'
sum(expstealcnt)
'/'
sum(expblkrelcnt)
'/'
sum(expblkreucnt) blkst
from stats$undostat
where dbid in (select dbid from v$database)
and instance_number in (select instance_number from v$instance)
and end_time > to_date('01012005 00:00:00', 'DDMMYYYY HH24:MI:SS')
and begin_time < (select sysdate from dual)
group by undotsn;
>SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM v$undostat;
Checking for Recent ORA-1555:
-----------------------------
show parameter background
cd
ls -ltr
tail
view
shift + G ---> to get the tail end...
?ORA-1555 ---- to search of the error...
shift + N ---- to step for next reported error...
Rollback segment queries:
--------------------------
Wraps
-----
select name,extents,rssize/1048576 rssizeMB ,xacts,writes/1024/1024,optsize/1048576 optsize,
shrinks,wraps,extends,aveshrink/1048576,waits,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn
order by wraps;
Wraps column as high values for the all segments size of rollback segments are small for
long running queries and transactions by increasing the rollback segments size we can avoid the ORA-01555 errors
Undo Contention:
-----------------
Rollback Segment Contention
----------------------------
prompt If any ratio is > .01 then more rollback segments are needed
column "total_waits" format 999,999,999
column "total_timeouts" format 999,999,999
column "Ratio" format 99.99999
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
Sample Output:
REM NAME WAITS GETS Ratio
REM ------------------------------ ---------- ---------- ---------
REM SYSTEM 0 269 .00000
REM R01 0 304 .00000
REM R02 0 2820 .00000
REM R03 0 629 .00000
REM R04 1 511 .00196
REM R05 0 513 .00000
REM R06 1 503 .00199
REM R07 0 301 .00000
REM R08 0 299 .00000
Looking at the tcl script to see what sql gets performed to determine rollback
segment contention:
---------------------
select count from v$waitstat where class = 'system undo header';
select count from v$waitstat where class = 'system undo block';
select count from v$waitstat where class = 'undo header';
select count from v$waitstat where class = 'undo block';
Rollback Segment Information:
-----------------------------------
set lines 152
col segment_type for a10
col tablespace_name for a20
select owner,tablespace_name,extents,next_extent/1024 next_extnentKB,max_extents,pct_increase
from dba_segments
where segment_type='ROLLBACK';
> set lines 152
col name for a15
select name,extents,rssize/1048576 rssizeMB ,xacts,writes/1024/1024,optsize/1048576 optsize,
shrinks,wraps,aveshrink/1048576,waits,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn and rs.xacts>0;
>select name,extents,rssize/1048576 rssizeMB ,xacts,writes/1024/1024,optsize/1048576 optsize,
shrinks,wraps,extends,aveshrink/1048576,waits,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn
order by wraps;
>select name,extents,optsize/1048576 optsize,
shrinks,wraps,aveshrink/1048576,aveactive,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn;
>select sum(rssize)/1024/1024/1024 sizeGB from v$rollstat;
>select sum(xacts) from v$rollstat;
select sum(rssize)/1024/1024/1024 sizeGB from v$rollstat where xacts=0;
select sum(rssize)/1024/1024/1024 sizeGB from v$rollstat where xacts>0;
select sum(xacts) from v$rollstat;
>select tablespace_name,segment_name,initial_extent,next_extent,min_extents,max_extents,status
from dba_rollback_segs
where status='ONLINE';
>select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_data_files where file_name like '%&filename%';
>select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='&tbs';
Optimize Oracle UNDO Parameters:
-----------------------------------------
Actual Undo Size
SELECT SUM(a.bytes/1024/1024/1024) "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#;
UNDO_SIZE
----------
209715200
Undo Blocks per Second:
-------------------------
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
3.12166667
Undo Segment Summary for DB:
--------------------------------
Undo Segment Summary for DB: S901 Instance: S901 Snaps: 2 -3
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/
TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU
---- -------------- ---------- -------- ---------- -------- ------ -------------
1 20,284 1,964 8 12 0 0 0/0/0/0/0/0
Undo Segment Stats for DB:
---------------------------
Undo Segment Stats for DB: S901 Instance: S901 Snaps: 2 -3
-> ordered by Time desc
Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/
End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU
------------ ------------ -------- ------- -------- ------- ------ -------------
12-Mar 16:11 18,723 1,756 8 12 0 0 0/0/0/0/0/0
12-Mar 16:01 1,561 208 3 12 0 0 0/0/0/0/0/0
Undo Segment Space Required = (undo_retention_time * undo_blocks_per_seconds)
As an example, an UNDO_RETENTION of 5 minutes (default) with 50 undo blocks/second (8k blocksize)
will generate:
-------------------
Undo Segment Space Required = (300 seconds * 50 blocks/ seconds * 8K/block) = 120 M
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDO%'
or tablespace_name like '%RBS%'
or tablespace_name like '%ROLLBACK%'
order by tablespace_name,file_name;
select a.owner,a.tablespace_name,b.status, a.extents,a.next_extent/1024 next_extnentKB,a.max_extents,a.pct_increase from dba_segments a,dba_tablespaces b
where segment_type='ROLLBACK' and a.tablespace_name=b.tablespace_name;
select tablespace_name,status from dba_tablespaces where tablespace_name='ROLLBACK';
Actual Undo Size:
-----------------
SELECT SUM(a.bytes/1024/1024) "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#;
UNDO_SIZE
----------
209715200
Undo Blocks per Second:
------------------------
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
3.12166667
DB Block Size
------------------
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';
DB_BLOCK_SIZE [Byte]
--------------------
4096
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'
/
ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
OPTIMAL UNDO RETENTION [Sec]
----------------------------
16401
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Calculate Needed UNDO Size for given Database Activity
If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time
that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:
Again, all in one query:
---------------------------------------
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'
/
ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
NEEDED UNDO SIZE [MByte]
------------------------
131.695313
----------------------------------------------------------------------------------------------------------------------------
Checking when tables are last analyzed
select
OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED,DEPENDENCIES,DROPPED from
dba_tables where TABLE_NAME like 'MLC_END_USER_REGISTRATION';
DECLARE
v_table_space_name VARCHAR2(30);
v_table_space_size_in_MB NUMBER(9);
v_auto_extend BOOLEAN;
v_undo_retention NUMBER(9);
v_retention_guarantee BOOLEAN;
v_undo_info_return BOOLEAN;
BEGIN
v_undo_info_return := dbms_undo_adv.undo_info(v_table_space_name, v_table_space_size_in_MB, v_auto_extend, v_undo_retention, v_retention_guarantee);
dbms_output.put_line(’UNDO Tablespace Name: ‘
v_table_space_name);
dbms_output.put_line(’UNDO Tablespace size (MB) : ‘
TO_CHAR(v_table_space_size_in_MB));
dbms_output.put_line(’If UNDO tablespace is auto extensible above size indicates max possible size of the undo tablespace’);
dbms_output.put_line(’UNDO tablespace auto extensiable is : ‘
CASE WHEN v_auto_extend THEN ‘ON’ ELSE ‘OFF’ END);
dbms_output.put_line(’Undo Retention (Sec): ‘
v_undo_retention);
dbms_output.put_line(’Retention : ‘
CASE WHEN v_retention_guarantee THEN ‘Guaranteed ‘ ELSE ‘NOT Guaranteed’ END);
END;
undo_autotune
This function is used to find auto tuning of undo retention is ENABLED or NOT.
Set serverout on
declare
v_autotune_return Boolean := null;
v_autotune_enabled boolean := null;
begin
v_autotune_return:= dbms_undo_adv.undo_autotune(v_autotune_enabled);
dbms_output.put_line(CASE WHEN v_autotune_return THEN 'Information is available :' ELSE 'Information is NOT available :' END
CASE WHEN v_autotune_enabled THEN 'Auto tuning of undo retention is ENABLED' ELSE 'Auto tuning of undo retention is NOT enabled' END);
end;
/
select dbms_undo_adv.longest_query from dual
select dbms_undo_adv.required_retention from dual
select dbms_undo_adv.best_possible_retention from dual
select dbms_undo_adv.required_undo_size(1800) from dual
DECLARE
v_undo_health_return number;
v_retention number;
v_utbsize number;
v_problem VARCHAR2(1024);
v_recommendation VARCHAR2(1024);
v_rationale VARCHAR2(1024);
BEGIN
v_undo_health_return := dbms_undo_adv.undo_health(problem => v_problem,
recommendation => v_recommendation,
rationale => v_rationale,
retention => v_retention,
utbsize => v_utbsize);
dbms_output.put_line(’Problem : ‘
v_problem);
dbms_output.put_line(’Recommendation= : ‘
v_recommendation);
dbms_output.put_line(’Rationale : ‘
v_retention);
dbms_output.put_line(’Retention : ‘
v_retention);
dbms_output.put_line(’UNDO tablespace size : ‘
v_utbsize);
END;
undo_advisor:
-------------
It uses oracle’s advisor framework to find out problem and provide recommendations.
DECLARE
v_undo_advisor_return VARCHAR2(100);
BEGIN
v_undo_advisor_return := dbms_undo_adv.undo_advisor(instance => 1);
dbms_output.put_line(v_undo_advisor_return);
END;
----------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
select tablespace_name,status,contents,logging,retention from dba_tablespaces where tablespace_name like '%UNDO%';
To show ACTIVE/EXPIRED/UNEXPIRED Extents of Undo Tablespace:
-------------------------------------------------------------
select tablespace_name,
status,
count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",
sum(blocks)*8/(1024*1024) total_space
from dba_undo_extents
group by tablespace_name, status;
Extent Count and Total Blocks:
-------------------------------
set linesize 152
col tablespace_name for a20
col status for a10
select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
where tablespace_name in ('&undotbsp')
group by tablespace_name,status;
To show UndoRetention Value:
-----------------------------
Show parameter undo_retention;
Undo retention in hours:
-------------------------
col "Retention" for a30
col name for a30
col value for a50
select name "Retention",value/60/60 "Hours" from v$parameter where name like '%undo_retention%';
To check space related statistics of UndoTablespace from stats$undostat of 90 days:
-------------------------------------------------------------------------------------
select UNDOBLKS,BEGIN_TIME,MAXQUERYLEN,UNXPSTEALCNT,EXPSTEALCNT,NOSPACEERRCNT from stats$undostat
where BEGIN_TIME between sysdate-90 and sysdate and UNXPSTEALCNT > 0;
To check space related statistics of UndoTablespace from v$undostat:
----------------------------------------------------------------------
select
sum(ssolderrcnt) "Total ORA-1555s",
round(max(maxquerylen)/60/60) "Max Query HRS",
sum(unxpstealcnt) "UNExpired STEALS",
sum(expstealcnt) "Expired STEALS"
from v$undostat
order by begin_time;
Date wise occurrence of ORA-1555:
------------------------------------
select to_char(begin_time, 'mm/dd/yyyy hh24:mi') "Int. Start",
ssolderrcnt "ORA-1555s", maxquerylen "Max Query",
unxpstealcnt "UNExp SCnt",UNXPBLKRELCNT "UnEXPblks", expstealcnt "Exp SCnt",EXPBLKRELCNT "ExpBlks",
NOSPACEERRCNT nospace
from v$undostat where ssolderrcnt>0
order by begin_time;
Total number of ORA-1555s since instance startup:
-------------------------------------------------
select 'TOTAL # OF ORA-01555 SINCE INSTANCE STARTUP : '
to_char(startup_time,'DD-MON-YY HH24:MI:SS')
from v$instance;
To check for Active Transactions:
----------------------------------
set head on
select usn,extents,round(rssize/1048576)
rssize,hwmsize,xacts,waits,optsize/1048576 optsize,shrinks,wraps
from v$rollstat where xacts>0
order by rssize;
Undo Space Utilization by each Sessions:
------------------------------------------
set lines 200
col sid for 99999
col username for a10
col name for a15
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,rn.name,(t.used_ublk *8)/1024/1024 SizeGB
from v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;
List of long running queries since instance startup:
----------------------------------------------------
set head off
select 'LIST OF LONG RUNNING - QUERY SINCE INSTANCE STARTUP' from dual;
set head on
select *
from
(select to_char(begin_time, 'DD-MON-YY hh24:mi:ss') BEGIN_TIME ,
round((maxquerylen/3600),1) Hours
from v$undostat
order by maxquerylen desc)
where rownum < 11;
Undo Space used by all transactions:
--------------------------------------
set lines 200
col sid for 99999
col username for a10
col name for a15
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,rn.name,(t.used_ublk *8)/1024/1024 SizeGB
from v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;
List of All active Transactions:
---------------------------------
select sid,username,
t.used_ublk ,t.used_urec
from v$transaction t,v$session s
where t.addr=s.taddr;
To list all Datafile of UndoTablespace:
-----------------------------------------
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDO%'
or tablespace_name like '%RBS%'
order by tablespace_name,file_name;
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDOTBS2%'
order by tablespace_name,file_name;
col file_name for a40
set pagesize 100
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%APPS_UNDOTS1%'
order by tablespace_name,file_name;
select file_name,tablespace_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_data_files where file_name like '%undo%' order by file_name;
To check when a table is last analysed:
----------------------------------------
select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED,DEPENDENCIES,DROPPED from dba_tables where TABLE_NAME like 'MLC_PICK_LOCKS_DETAIL';
select OWNER,TABLE_NAME,TABLESPACE_NAME,LAST_ANALYZED,PARTITIONED,DEPENDENCIES,DROPPED from dba_tables where TABLE_NAME like 'APPS.XLA_AEL_GL_V';
To list all Undo datafiles with status and size
------------------------------------------------
show parameter undo
show parameter db_block_size
col tablespace_name form a20
col file_name form a60
set lines 120
select tablespace_name, file_name, status, bytes/1024/1024 from dba_data_files
where tablespace_name=(select tablespace_name from dba_tablespaces where contents='UNDO');
Total undo space:
---------------------
select sum(bytes)/1024/1024/1024 GB from dba_data_files where tablespace_name='&Undo_TB_Name';
Undo Tablespace:
--------------------
select tablespace_name from dba_tablespaces where tablespace_name like '%UNDO%';
To find MaxQueryLength from stats$undostat:
--------------------------------------------
Select Max(MAXQUERYLEN) from stats$undostat;
>select max(maxquerylen) from v$undostat;
>select begin_date,u.maxquerylen from
(select to_char(begin_time,'DD-MON-YYYY:HH24-MI-SS') begin_date,maxquerylen
from v$undostat order by maxquerylen desc) u where rownum<11>
>select begin_date,u.maxquerylen from
(select maxquerylen,to_char(begin_time,'DD-MON-YYYY:HH24-MI-SS') begin_date from
v$undostat order by maxquerylen DESC) u where rownum<26 asc="asc" begin_date="begin_date" by="by" desc="desc" maxquerylen="maxquerylen" order="order">
>select begin_date,u.maxquerylen from
(select maxquerylen,to_char(begin_time,'DD-MON-YYYY:HH24-MI-SS') begin_date from
v$undostat order by maxquerylen DESC) u where rownum<26 by="by" desc="desc" maxquerylen="maxquerylen" order="order">
>select sum(u.maxquerylen)/25 AvgUndoRetTime
from (select maxquerylen from v$undostat order by maxquerylen desc) u where rownum<26>
>select sum(u.maxquerylen)
from (select maxquerylen from v$undostat order by maxquerylen desc) u where rownum<26>
DBA_UNDO_EXTENTS:
-------------------
set linesize 152
col tablespace_name for a20
col status for a10
select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from dba_undo_extents
group by tablespace_name, status
order by tablespace_name;
Mapping Undo Segments to usernames:
------------------------------------
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,(rs.rssize)/1024/1024 MB,rn.name
from v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;
Total Undo Statistics:
----------------------------
alter session set nls_date_format='dd-mon-yy hh24:mi';
set lines 120
set pages 2000
select BEGIN_TIME, END_TIME, UNDOBLKS, TXNCOUNT , MAXQUERYLEN , UNXPSTEALCNT ,
EXPSTEALCNT , SSOLDERRCNT , NOSPACEERRCNT
from v$undostat;
Total Undo Statistics since specified year:
---------------------------------------------
select 'TOTAL STATISTICS SINCE Jan 01, 2005 - STATSPACK' from dual;
set head on
set lines 152
column undotsn format 999 heading 'Undo
TS#';
column undob format 9,999,999,999 heading 'Undo
Blocks';
column txcnt format 9,999,999,999,999 heading 'Num
Trans';
column maxq format 999,999 heading 'Max Qry
Len (s)';
column maxc format 9,999,999 heading 'Max Tx
Concurcy';
column snol format 9,999 heading 'Snapshot
Too Old';
column nosp format 9,999 heading 'Out of
Space';
column blkst format a13 heading 'uS/uR/uU/
eS/eR/eU' wrap;
column unst format 9,999 heading 'Unexp
Stolen' newline;
column unrl format 9,999 heading 'Unexp
Relesd';
column unru format 9,999 heading 'Unexp
Reused';
column exst format 9,999 heading 'Exp
Stolen';
column exrl format 9,999 heading 'Exp
Releas';
column exru format 9,999 heading 'Exp
Reused';
select undotsn
, sum(undoblks) undob
, sum(txncount) txcnt
, max(maxquerylen) maxq
, max(maxconcurrency) maxc
, sum(ssolderrcnt) snol
, sum(nospaceerrcnt) nosp
, sum(unxpstealcnt)
'/'
sum(unxpblkrelcnt)
'/'
sum(unxpblkreucnt)
'/'
sum(expstealcnt)
'/'
sum(expblkrelcnt)
'/'
sum(expblkreucnt) blkst
from stats$undostat
where dbid in (select dbid from v$database)
and instance_number in (select instance_number from v$instance)
and end_time > to_date('01012005 00:00:00', 'DDMMYYYY HH24:MI:SS')
and begin_time < (select sysdate from dual)
group by undotsn;
>SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM v$undostat;
Checking for Recent ORA-1555:
-----------------------------
show parameter background
cd
ls -ltr
tail
view
shift + G ---> to get the tail end...
?ORA-1555 ---- to search of the error...
shift + N ---- to step for next reported error...
Rollback segment queries:
--------------------------
Wraps
-----
select name,extents,rssize/1048576 rssizeMB ,xacts,writes/1024/1024,optsize/1048576 optsize,
shrinks,wraps,extends,aveshrink/1048576,waits,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn
order by wraps;
Wraps column as high values for the all segments size of rollback segments are small for
long running queries and transactions by increasing the rollback segments size we can avoid the ORA-01555 errors
Undo Contention:
-----------------
Rollback Segment Contention
----------------------------
prompt If any ratio is > .01 then more rollback segments are needed
column "total_waits" format 999,999,999
column "total_timeouts" format 999,999,999
column "Ratio" format 99.99999
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
Sample Output:
REM NAME WAITS GETS Ratio
REM ------------------------------ ---------- ---------- ---------
REM SYSTEM 0 269 .00000
REM R01 0 304 .00000
REM R02 0 2820 .00000
REM R03 0 629 .00000
REM R04 1 511 .00196
REM R05 0 513 .00000
REM R06 1 503 .00199
REM R07 0 301 .00000
REM R08 0 299 .00000
Looking at the tcl script to see what sql gets performed to determine rollback
segment contention:
---------------------
select count from v$waitstat where class = 'system undo header';
select count from v$waitstat where class = 'system undo block';
select count from v$waitstat where class = 'undo header';
select count from v$waitstat where class = 'undo block';
Rollback Segment Information:
-----------------------------------
set lines 152
col segment_type for a10
col tablespace_name for a20
select owner,tablespace_name,extents,next_extent/1024 next_extnentKB,max_extents,pct_increase
from dba_segments
where segment_type='ROLLBACK';
> set lines 152
col name for a15
select name,extents,rssize/1048576 rssizeMB ,xacts,writes/1024/1024,optsize/1048576 optsize,
shrinks,wraps,aveshrink/1048576,waits,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn and rs.xacts>0;
>select name,extents,rssize/1048576 rssizeMB ,xacts,writes/1024/1024,optsize/1048576 optsize,
shrinks,wraps,extends,aveshrink/1048576,waits,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn
order by wraps;
>select name,extents,optsize/1048576 optsize,
shrinks,wraps,aveshrink/1048576,aveactive,rs.status,rs.curext
from v$rollstat rs, v$rollname rn where rn.usn=rs.usn;
>select sum(rssize)/1024/1024/1024 sizeGB from v$rollstat;
>select sum(xacts) from v$rollstat;
select sum(rssize)/1024/1024/1024 sizeGB from v$rollstat where xacts=0;
select sum(rssize)/1024/1024/1024 sizeGB from v$rollstat where xacts>0;
select sum(xacts) from v$rollstat;
>select tablespace_name,segment_name,initial_extent,next_extent,min_extents,max_extents,status
from dba_rollback_segs
where status='ONLINE';
>select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_data_files where file_name like '%&filename%';
>select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='&tbs';
Optimize Oracle UNDO Parameters:
-----------------------------------------
Actual Undo Size
SELECT SUM(a.bytes/1024/1024/1024) "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#;
UNDO_SIZE
----------
209715200
Undo Blocks per Second:
-------------------------
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
3.12166667
Undo Segment Summary for DB:
--------------------------------
Undo Segment Summary for DB: S901 Instance: S901 Snaps: 2 -3
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/
TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU
---- -------------- ---------- -------- ---------- -------- ------ -------------
1 20,284 1,964 8 12 0 0 0/0/0/0/0/0
Undo Segment Stats for DB:
---------------------------
Undo Segment Stats for DB: S901 Instance: S901 Snaps: 2 -3
-> ordered by Time desc
Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/
End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU
------------ ------------ -------- ------- -------- ------- ------ -------------
12-Mar 16:11 18,723 1,756 8 12 0 0 0/0/0/0/0/0
12-Mar 16:01 1,561 208 3 12 0 0 0/0/0/0/0/0
Undo Segment Space Required = (undo_retention_time * undo_blocks_per_seconds)
As an example, an UNDO_RETENTION of 5 minutes (default) with 50 undo blocks/second (8k blocksize)
will generate:
-------------------
Undo Segment Space Required = (300 seconds * 50 blocks/ seconds * 8K/block) = 120 M
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDO%'
or tablespace_name like '%RBS%'
or tablespace_name like '%ROLLBACK%'
order by tablespace_name,file_name;
select a.owner,a.tablespace_name,b.status, a.extents,a.next_extent/1024 next_extnentKB,a.max_extents,a.pct_increase from dba_segments a,dba_tablespaces b
where segment_type='ROLLBACK' and a.tablespace_name=b.tablespace_name;
select tablespace_name,status from dba_tablespaces where tablespace_name='ROLLBACK';
Actual Undo Size:
-----------------
SELECT SUM(a.bytes/1024/1024) "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#;
UNDO_SIZE
----------
209715200
Undo Blocks per Second:
------------------------
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
3.12166667
DB Block Size
------------------
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';
DB_BLOCK_SIZE [Byte]
--------------------
4096
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'
/
ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
OPTIMAL UNDO RETENTION [Sec]
----------------------------
16401
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Calculate Needed UNDO Size for given Database Activity
If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time
that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:
Again, all in one query:
---------------------------------------
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'
/
ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
NEEDED UNDO SIZE [MByte]
------------------------
131.695313
----------------------------------------------------------------------------------------------------------------------------
Checking when tables are last analyzed
select
OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED,DEPENDENCIES,DROPPED from
dba_tables where TABLE_NAME like 'MLC_END_USER_REGISTRATION';
DECLARE
v_table_space_name VARCHAR2(30);
v_table_space_size_in_MB NUMBER(9);
v_auto_extend BOOLEAN;
v_undo_retention NUMBER(9);
v_retention_guarantee BOOLEAN;
v_undo_info_return BOOLEAN;
BEGIN
v_undo_info_return := dbms_undo_adv.undo_info(v_table_space_name, v_table_space_size_in_MB, v_auto_extend, v_undo_retention, v_retention_guarantee);
dbms_output.put_line(’UNDO Tablespace Name: ‘
v_table_space_name);
dbms_output.put_line(’UNDO Tablespace size (MB) : ‘
TO_CHAR(v_table_space_size_in_MB));
dbms_output.put_line(’If UNDO tablespace is auto extensible above size indicates max possible size of the undo tablespace’);
dbms_output.put_line(’UNDO tablespace auto extensiable is : ‘
CASE WHEN v_auto_extend THEN ‘ON’ ELSE ‘OFF’ END);
dbms_output.put_line(’Undo Retention (Sec): ‘
v_undo_retention);
dbms_output.put_line(’Retention : ‘
CASE WHEN v_retention_guarantee THEN ‘Guaranteed ‘ ELSE ‘NOT Guaranteed’ END);
END;
undo_autotune
This function is used to find auto tuning of undo retention is ENABLED or NOT.
Set serverout on
declare
v_autotune_return Boolean := null;
v_autotune_enabled boolean := null;
begin
v_autotune_return:= dbms_undo_adv.undo_autotune(v_autotune_enabled);
dbms_output.put_line(CASE WHEN v_autotune_return THEN 'Information is available :' ELSE 'Information is NOT available :' END
CASE WHEN v_autotune_enabled THEN 'Auto tuning of undo retention is ENABLED' ELSE 'Auto tuning of undo retention is NOT enabled' END);
end;
/
select dbms_undo_adv.longest_query from dual
select dbms_undo_adv.required_retention from dual
select dbms_undo_adv.best_possible_retention from dual
select dbms_undo_adv.required_undo_size(1800) from dual
DECLARE
v_undo_health_return number;
v_retention number;
v_utbsize number;
v_problem VARCHAR2(1024);
v_recommendation VARCHAR2(1024);
v_rationale VARCHAR2(1024);
BEGIN
v_undo_health_return := dbms_undo_adv.undo_health(problem => v_problem,
recommendation => v_recommendation,
rationale => v_rationale,
retention => v_retention,
utbsize => v_utbsize);
dbms_output.put_line(’Problem : ‘
v_problem);
dbms_output.put_line(’Recommendation= : ‘
v_recommendation);
dbms_output.put_line(’Rationale : ‘
v_retention);
dbms_output.put_line(’Retention : ‘
v_retention);
dbms_output.put_line(’UNDO tablespace size : ‘
v_utbsize);
END;
undo_advisor:
-------------
It uses oracle’s advisor framework to find out problem and provide recommendations.
DECLARE
v_undo_advisor_return VARCHAR2(100);
BEGIN
v_undo_advisor_return := dbms_undo_adv.undo_advisor(instance => 1);
dbms_output.put_line(v_undo_advisor_return);
END;
----------------------------------------------------------------------------------------------------------------------------------------
Wednesday, October 3, 2012
Subscribe to:
Posts (Atom)