Search This Blog

Total Pageviews

Monday, 26 September 2011

Oracle Undo statistics


Oracle Undo statistics  ..

-- single instance 
set lines 1000 pages 200
column inst_id head inst#         format 99
column hour    head "hour|(dd-mm-yyyy-hh24)"                         justify centre
column "activesize"          head "active|size(mb)"    format 99,999 justify right
column undosize   head "undo|size(mb)"    format 99,999 justify right
column expiredsize   head "expired|size(mb)"   format 99,999 justify right
column unexpiredsize          head "unexpired|size(mb)"   format 99,999 justify right
column expiredrelsize          head "expired rel|size(mb)"          format 99,999 justify right
column maxconcurrency          head "max |concurrent|txn(#)"          format 99,999 justify right
column txncount   head "total|txn (#)"    format 99,999 justify right
column maxquerylen   head "max query|length(sec)"          format 99,999 justify right
column nospaceerrcnt          head "nospace|errors (#)"   format 99,999 justify right
column ssolderrcnt   head "ora-01555|errors(#)"   format 99,999 justify right
select
  to_char(a.begin_time, 'dd-mm-yyyy-hh24')      "hour"
, round(sum(a.activeblks* 8 )/1024)   "activesize"
, round(sum(a.undoblks * 8 )/1024)   "undosize"
, round(sum(a.expiredblks * 8 )/1024)   "expiredsize"
, round(sum(a.unexpiredblks * 8 )/1024)  "unexpiredsize"
, round(sum(a.expblkrelcnt * 8 )/1024)   "expiredrelsize"
, max(maxconcurrency)     maxconcurrency
, sum(txncount)     txncount
, max(maxquerylen)     maxquerylen
, sum(nospaceerrcnt)     nospaceerrcnt
, sum(ssolderrcnt)     ssolderrcnt
from v$undostat a
group by to_char(a.begin_time, 'dd-mm-yyyy-hh24')
order by 1
/



-- for Rac 
set lines 1000  pages 200
column inst_id head inst#                                 format 99
column hour   head "hour|(dd-mm-yyyy-hh24)"                   justify centre
column activesize     head "active|size(mb)"            format 99,999 justify right
column undosize  head "undo|size(mb)"              format 99,999 justify right
column expiredsize head "expired|size(mb)"           format 99,999 justify right
column unexpiredsize  head "unexpired|size(mb)"         format 99,999 justify right
column expiredrelsize  head "expired rel|size(mb)"       format 99,999 justify right
column maxconcurrency  head "max |concurrent|txn(#)"     format 99,999 justify right
column txncount  head "total|txn (#)"              format 99,999 justify right
column maxquerylen  head "max query|length(sec)"      format 99,999 justify right
column nospaceerrcnt  head "nospace|errors (#)"         format 99,999 justify right
column ssolderrcnt  head "ora-01555|errors(#)"        format 99,999 justify right
select
  a.inst_id
, to_char(a.begin_time, 'dd-mm-yyyy-hh24')  hour
, round(sum(a.activeblks* 8 )/1024)   activesize
, round(sum(a.undoblks * 8 )/1024)   undosize
, round(sum(a.expiredblks * 8 )/1024)   expiredsize
, round(sum(a.unexpiredblks * 8 )/1024)  unexpiredsize
, round(sum(a.expblkrelcnt * 8 )/1024)   expiredrelsize
, max(maxconcurrency)     maxconcurrency
, sum(txncount)     txncount
, max(maxquerylen)     maxquerylen
, sum(nospaceerrcnt)     nospaceerrcnt
, sum(ssolderrcnt)     ssolderrcnt
from gv$undostat a
group by a.inst_id, to_char(a.begin_time, 'dd-mm-yyyy-hh24')
order by 2 ;

    hour        active     undo  expired unexpired expired rel concurrent   total   max query    nospace ora-01555
(dd-mm-yyyy-h size(mb) size(mb) size(mb)  size(mb)    size(mb)     txn(#) txn (#) length(sec) errors (#) errors(#)
------------- -------- -------- -------- --------- ----------- ---------- ------- ----------- ---------- ---------
12-09-2016-09      488        2      570         4           0          4     181       1,125          0         0
12-09-2016-10    1,463        2    1,717         4           0          4     362       1,121          0         0
12-09-2016-11    1,463        2    1,711         6           0          4     346       1,110          0         0

Rac Output ..
                                                                           max
          hour        active     undo  expired unexpired expired rel concurrent   total   max query    nospace ora-01555
inst# (dd-mm-yyyy-h size(mb) size(mb) size(mb)  size(mb)    size(mb)     txn(#) txn (#) length(sec) errors (#) errors(#)
----- ------------- -------- -------- -------- --------- ----------- ---------- ------- ----------- ---------- ---------
    1 12-09-2016-09      976        2    1,141         7           0          4     232       1,128          0         0
    2 12-09-2016-09       63        2    1,697         3           0          3     171       1,070          0         0
    1 12-09-2016-10    1,463        2    1,717         4           0          4     362       1,121          0         0

No comments:

Oracle DBA

anuj blog Archive