spool Undo_Diag.out  

ttitle off
set pages 999
set lines 150
set verify off 

set termout set trimout on
set trimspool on

REM   
REM ------------------------------------------------------------------------  
  
REM   
REM  ---------------------------------------------------------------  
REM  
  
set space 2  

REM  REPORTING TABLESPACE INFORMATION: 
REM   
REM  This looks at Tablespace Sizing - Total bytes and free bytes  
REM   
 
column tablespace_name  format a30            heading 'TS Name'  
column sbytes           format 9,999,1); font-weight: bold">999  heading Total MBytescolumn fbytes           format Free MBytescolumn file_name        format a30            heading File Name'
column kount            format 999            heading Ext'  
 
compute sum of fbytes  tablespace_name  
of sbytes  report  
 report  
 
break on tablespace_name skip 2  
 
select a.tablespace_name,a.file_name,round(a.bytes/10241024,1); font-weight: bold">0) sbytes,round(sum(b.bytes1024),1); font-weight: bold">0) fbytes,1)">count(*) kount,autoextensible  
from   dba_data_files a,dba_free_space b  
where  a.file_id  =  b.file_id  
and a.tablespace_name in (select z.tablespace_name from dba_tablespaces z where retention like %GUARANTEE')
group  by a.tablespace_name,1)">file_name,a.bytes,autoextensible
order  by a.tablespace_name  
/  
 
set linesize 160  
 
 
REM   
REM  If you can significantly reduce physical reads  adding incremental  
REM  data buffers...do it.  To determine whether adding data buffers will  
REM  help,set db_block_lru_statistics = TRUE   
REM  db_block_lru_extended_statistics in the init.ora parameters.  
REM  You can determine how many extra hits you would get from memory as  
REM  opposed to physical I/O from disk.  **NOTE:  Turning these  will  
REM  impact performance.  One shift of statistics gathering should be enough  
REM  to get the required information.  
REM   
  

REM   
REM  REM



col tablespace_name format a30 heading "Tablespace"
col tb format a15 heading "TB Status"
col df format a10 heading "DF Status"
col extent_management format a15 heading "Extent|Management"
col allocation_type format a8 heading "Type"
col segment_space_management format a7 heading "AutoSegment"
col retention format a11 heading "RetentionLevel"
col autoextensible format a5 heading "Auto?"
col mx format 999 heading "Max Allowed"

select t.tablespace_name,t.status tb,d.status df,extent_management,allocation_type,segment_space_management,retention,autoextensible,(maxbytes1024) mx
 dba_tablespaces t,dba_data_files d
where t.tablespace_name = d.tablespace_name
and retention '
/


col status format a20 head "Status"
col cnt format 999 head "How Many?"

select status,1)">) cnt
 dba_rollback_segs
group  status
/


  

120
999

on

alter session set nls_date_format=dd-Mon-yyyy hh24:mi;


prompt
prompt  ############## RUNTIME ############## 
prompt

col rdate head "Run Time"

select sysdate rdate  dual;

prompt 
prompt  ############## DATAFILES ############## 
prompt 

col retention head "Retention"
col tablespace_name format a30 head "TBSP Name"
col file_id format 999 head "File #"
col a format  head "Bytes Alloc (MB)"
col b format 999 head " Bytes Used (MB)"
col autoextensible head "AutoExt"
col extent_management head "Ext Mngmnt"
col allocation_type head "Type"
col segment_space_management head "SSM"

select tablespace_name,1)">file_id,1)">sum(bytes) a,1)">sum(maxbytes) b,1)"> dba_data_files
where tablespace_name select tablespace_name  dba_tablespaces
    )
by file_idorder  tablespace_name
/

off

ttitle REM  
  
REM
REM  REPORTING UNDO EXTENTS INFORMATION:  
REM   
REM  REM 
REM  Undo Extents breakdown information
REM

ttitle center "Rollback Segments Breakdown" skip 

col status format a20
col cnt format *) cnt 

ttitle center "Undo Extents" skip 

col segment_name format a30 heading "Name"
col "ACT BYTES" format 999 head "ActiveExtents"
col "UNEXP BYTES" format 999 head "UnxpiredExtents"
col "EXP BYTES" format 999 head "ExpiredExtents"

 segment_name,nvl(sum(act),1)">) "ACT BYTES",1)">sum(unexp),1)">) "UNEXP BYTES",1)">sum(exp),1); font-weight: bold">0) "EXP BYTES"
  (
  sum(bytes),1); font-weight: bold">0) act,1); font-weight: bold">00 unexp,1); font-weight: bold">00 exp
     DBA_UNDO_EXTENTS
   where statusACTIVE'  segment_name
  union
  00 act,nvl(0) unexp,1)">UNEXPIRED0) EXPIRED segment_name
)  segment_name;

ttitle center "Undo Extents Statistics" skip 

col size format  heading "Size"
col "HOW MANY" format  heading "How Many?"
col st heading a12 heading "Status"

select distinct status st,1)">*) "HOW MANY",1)">sum(bytes) "SIZE"
 dba_undo_extents


col segment_name format a30 heading "Name"
col TABLESPACE_NAME for a20
col BYTES for 
col BLOCKS 
col status  a15 heading "Status"
col segment_name heading "Segment"
col extent_id heading "ID"


 SEGMENT_NAME,TABLESPACE_NAME,EXTENT_ID,1)">FILE_IDby 1,1); font-weight: bold">3,1); font-weight: bold">4,1); font-weight: bold">5



REM
REM  REM 
REM  Undo Extents Contention breakdown
REM  Take out column TUNED_UNDORETENTION if customer 
REM   prior to 10.2.x
REM
REM   The time frame can be adjusted with this query
REM   By default using around 4 hour window of time
REM
REM   Ex.
REM   Using sysdate-.04 looking at the last hour
REM   Using sysdate16 looking at the last 4 hours
REM   Using sysdate32 looking at the last 8-1 looking at the last 24 hours
REM

140

ttitle center "Undo Extents Error Conditions (Default - Last 4 Hours)" skip 


col UNXPSTEALCNT format 999  heading "# UnexpiredStolen"
col EXPSTEALCNT format 999   heading "# ExpiredReused"
col SSOLDERRCNT format 999   heading "ORA1555Error"
col NOSPACEERRCNT format 999 heading "Out-Of-spaceError"
col MAXQUERYLEN format 999   heading "Max QueryLength"
col TUNED_UNDORETENTION format 999  heading "Auto-AjustedUndo Retention"
col hours format 999 heading "Tuned(HRs)"

select inst_id,to_char(begin_time,1)">MM/DD/YYYY HH24:MI) begin_time,UNXPSTEALCNT,EXPSTEALCNT,SSOLDERRCNT,NOSPACEERRCNT,MAXQUERYLEN,TUNED_UNDORETENTION,TUNED_UNDORETENTION6060 hours
 gv$undostat
where begin_time between (sysdate16) 
                      sysdate
 inst_id,begin_time
/

  



ttitle off 
  
col name format a30  
col gets format   
col waits format   
 
PROMPT  ROLLBACK HIT STATISTICS:  
REM   
  
REM  GETS - # of gets on the rollback segment header 
REM  WAITS of waits for the  segment header  
  
set head ;  
 
 name,waits,gets  
   v$rollstat,v$rollname  
where  v$rollstat.usn  v$rollname.usn  
  
 
col pct head "< 2% ideal"
 
select The average of waits/gets is '||  
   round((sum(waits) / sum(gets)) * 100,1); font-weight: bold">2)||% PCT 
From    v$rollstat  
  
  

  
PROMPT  REDO CONTENTION :

REM   
REM  If the ratio to gets is more than 1% or  segments  
REM   
REM  Another way to gauge rollback contention is:  
REM   
  
column xn1 format 9999999  
column xv1 new_value xxv1 noprint  
 

 
select class,1)">count  
   v$waitstat  
where  class in (system undo header',1)">system undo blockundo headerundo block          )  
/  

off

Total requests = count) xn1,1)">count) xv1  
    v$waitstat  
Contention for system undo header = ||  
       (count/(&xxv1+0.00000000001),1); font-weight: bold">4)) 100  v$waitstat  
where   class = '  
Contention for system undo block =     v$waitstat  
Contention for undo header = Contention for undo block =   

REM   
REM  NOTE: Not as useful  AUM configured 
REM 
REM  If the percentage for an area rollback segments.  Note:  This value  usually very  
REM  small 
REM  and has been rounded  places.  
REM   
REM   
REM   
REM  The following shows how often user processes had to wait for   
REM  the redo log buffer:  
  
select name = value  
   v$sysstat  
where  name redo log space requests  
 
REM   
REM  This value should be near 0.  If this value increments consistently,REM  processes have had in the redo buffer.   this  
REM  condition exists over time,increase the size of LOG_BUFFER  the  
REM  init.ora file in increments of 5% until the value nears .  
REM  ** NOTE: increasing the LOG_BUFFER value will increase total SGA size.  
REM   
REM  ---------------------------------------------------------------------  
  
  
col name format a15  
col gets format 9999999  
col misses format   
col immediate_gets heading IMMED GETS' format   
col immediate_misses heading IMMED MISS  
col sleeps format 999999  
 
PROMPT  LATCH CONTENTION:  
REM   
REM  GETS of successful willingto-wait requests  a latch  
REM  MISSES of times an initial willingwait request was unsuccessful  
REM  IMMEDIATE_GETS of successful immediate requests  each latch  
REM  IMMEDIATE_MISSES = # of unsuccessful immediate requests  each latch  
REM  SLEEPS of times a process waited  requests a latch after an  
REM           initial willingwait request  
REM   
REM  If the latch requested with a willing-wait request is not  
REM  available,the requesting process waits a short time  requests  
REM  again.  
REM  with an immediate request  available,REM  the requesting process does  wait,but continues processing  
REM   

on  
   v$latch  
redo allocationredo copy)  
Ratio of MISSES to GETS: ||  
        sum(misses)/(sum(gets)0.00000000001) 100),1)">    v$latch  
where   name /  
 

Ratio of IMMEDIATE_MISSES to IMMEDIATE_GETS: sum(immediate_misses)sum(immediate_misses+immediate_gets)' 

REM   
REM  If either ratio exceeds of LOG_SMALL_ENTRY_MAX_SIZE reduces the number   
REM  processes copying information  the redo allocation latch.  
REM   
REM  Increasing the size  LOG_SIMULTANEOUS_COPIES will reduce contention  
REM   redo copy latches.  
  
REM   
REM  ---------------------------------------------------------------  
REM  This looks at overall io activity against individual  
REM  files within a tablespace  
REM   
REM  Look for a mismatch across disk drives in terms of IO  
REM   
REM  Also,examine the Blocks per Read Ratio  heavily accessed  
REM  TSs - if this value is significantly above 1 then you may have  
REM  full tablescans occurring (with multi-block IO)  
REM   
REM  If activity on the files is unbalanced,move files around  balance  
REM  the load.  Should see an approximately even set  numbers across files  
REM   
  
1  

PROMPT  REPORTING I:
 
column pbr       format 99999999  heading Physical|Blk Readcolumn pbw       format 999999    heading Physical|Blks Wrtncolumn pyr       format Physical|Readscolumn readtim   format Read|Timecolumn name      format a55       heading DataFile Namecolumn writetim  format Write|Timeof f.phyblkrd,f.phyblkwrt  fs.name name,f.phyblkrd pbr,f.phyblkwrt pbw,f.readtim,f.writetim  
   v$filestat f,v$datafile fs  
where  f.file#  =  fs.file#  
 fs.name  
  
 
REM   
REM    
PROMPT  GENERATING WAIT :  
REM   
REM  This will show wait stats  certain kernel instances.  This  
REM  may show the need  additional rbs,wait lists,db_buffers  
REM   
 
column class  heading Class Typecount  heading Times Waited'  format 99,1); font-weight: bold">999 
column time   heading Total Times'   format 999  
 
where  count > 0  
 class  
  
 
REM   
REM  Look at the wait statistics generated above (if any). They will  
REM  tell you where there is contention  the system.  There will  
REM  usually be some contention in any system - but if the ratio   
REM  waits for a particular operation starts to rise,you may need   
REM  add additional resource,such as more database buffers,1)"> buffers,REM  or  segments  
REM   
REM    
PROMPT  ROLLBACK EXTENT :  
REM   


 
column usn        format 999          heading Undo #column extents    format Extentscolumn RSSize     format Size in|Bytescolumn optsize    format Optimal|Sizecolumn hwmsize    format 999   heading High Water|Markcolumn shrinks    format 999        heading Num of|Shrinkscolumn wraps      format Num of|Wrapscolumn extends    format 999      heading Num of|Extendscolumn aveactive  format Average size|Active Extents rownum noprint  
 
 usn,extents,RSSize,optsize,hwmsize,shrinks,wraps,extends,aveactive  
   v$rollstat  
 rownum  
/  







prompt
prompt  ############## RUNTIME ############## 
prompt

col rdate head "Run Time"

 dual;

prompt 
prompt  ############## HISTORICAL DATA ############## 
prompt 

col x format Max Concurrent|Last 7 Days"
col y format Since Startup"

max(maxconcurrency) x  v$undostat
/
max(maxconcurrency) y  sys.wrh$_undostat


col i format 999 head "1555 Errors"
col j format 999 head "Undo Space Errors"

sum(ssolderrcnt) i  v$undostat
where end_time > sysdate2
sum(nospaceerrcnt) j 

prompt 
prompt  ############## CURRENT STATUS OF SEGMENTS  ############## 
prompt  ##############   SNAPSHOT IN TIME INFO     ##############
prompt  ##############(SHOWS CURRENT UNDO ACTIVITY)##############
prompt 

col segment_name format a30 head "Segment Name"
col "ACT BYTES" format  head "Active Bytes"
col "UNEXP BYTES" format  head "Unexpired Bytes"
col " head "Expired Bytes"

select segment_name,1)"> BYTES"
from (exp
   from dba_undo_extents  segment_name
union 
exp
union
 segment_name)
1


prompt 
prompt  ############## UNDO SPACE USAGE ############## 
prompt 

col usn format  head "Segment#"
col shrinks format  head "Shrinks"
col aveshrink format Avg Shrink Size"

select usn,aveshrink  v$rollstat



prompt
prompt  ############## RUNTIME ############## 
prompt

col rdate head "Run Time"

 dual;

col inst_id format  head "Instance #"
col Parameter format a35 wrap
col "Session Value" format a25 wrapped
col "Instance Value" format a25 wrapped

prompt
prompt  ############## PARAMETERS ############## 
prompt

  a.inst_id,a.ksppinm  "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value"
       x$ksppi a,x$ksppcv b,x$ksppsv c
     where a.indx = b.indx and a.indx  c.indx
       and a.inst_id=b.inst_id and b.inst_idc.inst_id
       and a.ksppinm _undo_autotune_smu_debug_mode_highthreshold_undoretentionundo_tablespaceundo_retentionundo_management;



prompt
prompt  ############## RUNTIME ############## 
prompt

col rdate head "Run Time"

 dual;

prompt 
prompt  ############## WAITS FOR UNDO (Since Startup) ############## 
prompt 

col inst_id head "Instance#"
col eq_type format a3 head "Enq"
col total_req# format  head "Total Requests"
col total_wait# format  head "Total Waits"
col succ_req# format  head "Successes"
col Failed_req# format  head "Failures"
col cum_wait_time format 999 head "CummalitveTime"

select * from v$enqueue_stat where eq_typeUSHW

prompt 
prompt  ############## LOCKS  UNDO ############## 
prompt 

col addr head "ADDR"
col KADDR head "KADDR"
col sid head "Session"
col osuser format a10 head "OS User"
col machine format a15 head "Machine"
col program format a17 head "Program"
col process format a7 head "Process"
col lmode head "Lmode"
col request head "Request"
col ctime format 999 head "Time(Mins)"
col block head "Blocking?"

/*+ RULE */  a.SID,b.process,b.OSUSER,b.MACHINE,b.PROGRAM,addr,kaddr,lmode,request,1)">round(ctime60,1)">) ctime,block 
 
v$lock a,v$session b 
where 
a.sidb.sid
and a.type

prompt 
prompt  ############## TUNED RETENTION HISTORY (Last  Days) ############## 
prompt  ##############        LOWEST AND HIGHEST DATA        ############## 
prompt 

col low format 999 head "Undo RetentionLowest Tuned Value"
col high format Highest Tuned Value"

select end_time,tuned_undoretention from v$undostat where tuned_undoretention  (
min(tuned_undoretention) low
)
max(tuned_undoretention) high
 TRANSACTIONS ############## 
prompt 

col sql_text format a40 word_wrapped head "sql Code"

 a.start_date,a.start_scn,a.status,c.sql_text
from v$transactionsqlarea c
where b.saddr=a.ses_addr and c.addressb.sql_address
and b.sql_hash_valuec.hash_value
select current_scn database


col a format  head "UnexStolen"
col b format  head "ExStolen"
col c format  head "UnexReuse"
col d format  head "ExReuse"

prompt 
prompt  ############## WHOS STEALING WHAT? (Last 2 Days) ############## 
prompt 

select unxpstealcnt a,expstealcnt b,unxpblkreucnt c,expblkreucnt d
from v$undostat
where (unxpstealcnt > 0 or expstealcnt > 0)
and end_time > sysdate-2
/

set termout on
set trimout off
set trimspool off
set pages 999

set termout off
set trimout on
set trimspool on

prompt
prompt  ############## RUNTIME ############## 
prompt

col rdate head "Run Time"

select sysdate rdate from dual;

col current_scn head "SCN Now"
col start_date head "Trans Started"
col start_scn head "SCN for Trans"
col ses_addr head "ADDR"

prompt 
prompt  ############## Historical V$UNDOSTAT (Last 2 Days) ############## 
prompt 


col end_time format a18 Head "Date/Time"
col maxq format 999,999 head "Query|Maximum|Minutes"
col maxquerysqlid head "sqlID"
col undotsn format 999,999 head "TBS"
col undoblks format 999,999,999 head "Undo|Blocks"
col txncount format 999,999 head "# of|Trans"
col unexpiredblks format 999,999 head "# of Unexpired"
col expiredblks format 999,999 head "# of Expired"
col tuned format 999,999 head "Tuned Retention|(Minutes)"

select end_time,round(maxquerylen/60,0) maxq,maxquerysqlid,undotsn,undoblks,txncount,unexpiredblks,expiredblks,round(tuned_undoretention/60,0) Tuned
from dba_hist_undostat
where end_time > sysdate-2
order by 1
/

prompt 
prompt  ############## RECENT MISSES FOR UNDO (Last 2 Days) ############## 
prompt 

set lines 500
select * from v$undostat where maxquerylen > tuned_undoretention
and end_time > sysdate-2
order by 2
/

select * from sys.wrh$_undostat where maxquerylen > tuned_undoretention
and end_time > sysdate-2
order by 2
/

prompt 
prompt  ############## AUTO-TUNING TUNE-DOWN DATA    ############## 
prompt  ############## ROLLBACK DATA (Since Startup) ############## 
prompt 

col name format a60 head "Name"
col value format 999,999 head "Counters"

select name,value from v$sysstat
where name like %down retention or name like 'une down
or name like %undo segmentrollback%undo record
/

prompt 
prompt  ############## Long Running Query History ############## 
prompt 

col end_time head "Date"
col maxquerysqlid head "sql ID"
col runawayquerysqlid format a15 head "Runaway sql ID"
col results format a35 word_wrapped head "Space Issues"
col status head "Status"
col newret head "Tuned Down|Retention"

select end_time,runawayquerysqlid,status,decode(status,1,1)">'Slot Active'Reached Best Retention'Runaway Query'Runaway Query-ActiveSpace PressureSpace Pressure Currently'Tuned Down (to undo_retention) due to Space Pressure'Tuning Down due to Runawayto Runaway'Runaway tuned down to last tune down valueMax Tuned Down - Not Auto-Tuning-Tuning (Active)'Other (||status||')) Results,spcprs_retention NewRet
from sys.wrh$_undostat
where status > 1
/



prompt 
prompt  ############## Details on Long Run Queries ############## 
prompt 

col sql_fulltext head "sql Text"
Col sql_id heading "sql ID"

select sql_id,sql_fulltext,last_load_time "Last Load",round(elapsed_time/60/60/24,0) "Elapsed Days" 
from v$sql where sql_id in 
(select maxquerysqlid from sys.wrh$_undostat 
where status > 1)
/

set termout on
set trimout off
set trimspool off
set pages 999

set termout off
set trimout on
set trimspool on

prompt
prompt  ############## RUNTIME ############## 
prompt

col rdate head "Run Time"

select sysdate rdate from dual;

prompt 
prompt  ############## IN USE Undo Data ############## 
prompt 

select 
((select (nvl(sum(bytes),0)) 
from dba_undo_extents 
where tablespace_name in (select tablespace_name from dba_tablespaces
   where retention like %GUARANTEE )
and status in ('ACTIVE'UNEXPIRED)) *100) / 
(select sum(bytes) 
from dba_data_files 
where tablespace_name in (select tablespace_name from dba_tablespaces
   where retention like  )) "PCT_INUSE" 
from dual; 


select tablespace_name,retention
from dba_tablespaces where retention like 
/

col c format 999,999 head "Sum of Free"

select (nvl(sum(bytes),0)) c from dba_free_space
where tablespace_name in
(select tablespace_name from dba_tablespaces where retention like )
/

col d format 999,999 head "Total Bytes"

select sum(bytes) d from dba_data_files
where tablespace_name in
(select tablespace_name from dba_tablespaces where retention like )
/


PROMPT
PROMPT  ############## UNDO SEGMENTS ############## 
PROMPT

col status head "Status"
col z format 999,999 head "Total Extents"
break on report
compute sum on report of z

select status,count(*) z from dba_undo_extents
group by status
/

col z format 999,999 head "Undo Segments"

select status,count(*) z from dba_rollback_segs
group by status
/


prompt 
prompt  ############## CURRENT STATUS OF SEGMENTS  ############## 
prompt  ##############   SNAPSHOT IN TIME INFO     ##############
prompt  ##############(SHOWS CURRENT UNDO ACTIVITY)##############
prompt 


col segment_name format a30 head "Segment Name"
col "ACT BYTES" format 999,999 head "Active Bytes"
col "UNEXP BYTES" format 999,999 head "Unexpired Bytes"
col "EXP BYTES" format 999,999 head "Expired Bytes"

select segment_name,nvl(sum(act),0) "ACT BYTES",nvl(sum(unexp),0) "UNEXP BYTES",nvl(sum(exp),0) "EXP BYTES"
from (select segment_name,nvl(sum(bytes),0) act,00 unexp,00 exp
   from dba_undo_extents where status= group by segment_name
union 
select segment_name,00 act,0) unexp,00 exp
from dba_undo_extents where status= group by segment_name
union
select segment_name,0) exp
from dba_undo_extents where status='EXPIRED group by segment_name)
group by segment_name
order by 1
/

prompt 
prompt  ############## UNDO SPACE USAGE ############## 
prompt 

col usn format 999,999 head "Segment#"
col shrinks format 999,999 head "Shrinks"
col aveshrink format 999,999 head "Avg Shrink Size"

select usn,aveshrink from v$rollstat
/
set termout on
set trimout off
set trimspool off
spool off