127 lines
5.4 KiB
SQL
127 lines
5.4 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Undo statistics
|
|
* Parameters : 1 - Number of hours (Optional)
|
|
* 2 - Instance Number (Optional)
|
|
* 3 - Whereclause
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 16-Dec-15 Vishal Gupta Add where clause as input
|
|
* 17-Oct-13 Vishal Gupta Fixed decode statement in errors columns
|
|
* and added SQL Id in the output
|
|
* 14-Mar-12 Vishal Gupta Parameterized heading and dividers
|
|
* 15-May-07 Vishal Gupta Formatted the output
|
|
* 05-Aug-06 Vishal Gupta First Draft
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
|
|
UNDEFINE NoOfHours
|
|
UNDEFINE inst_id
|
|
UNDEFINE WHERECLAUSE
|
|
|
|
DEFINE NoOfHours="&&1"
|
|
DEFINE inst_id="&2"
|
|
DEFINE WHERECLAUSE="&3"
|
|
|
|
COLUMN _NoOfHours NEW_VALUE NoOfHours NOPRINT
|
|
COLUMN _inst_id NEW_VALUE inst_id NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&NoOfHours','','10','&&NoOfHours') "_NoOfHours"
|
|
, DECODE('&&inst_id','','%','&&inst_id') "_inst_id"
|
|
FROM DUAL
|
|
;
|
|
|
|
set term on
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
|
|
DEFINE COUNT_FORMAT=999,999,999
|
|
DEFINE COUNT_DIVIDER="1"
|
|
DEFINE COUNT_HEADING="#"
|
|
--DEFINE COUNT_DIVIDER="1000"
|
|
--DEFINE COUNT_HEADING="#1000"
|
|
DEFINE BYTES_FORMAT="999,999"
|
|
DEFINE BYTES_DIVIDER="1024/1024"
|
|
DEFINE BYTES_HEADING="MB"
|
|
--DEFINE BYTES_DIVIDER="1024/1024/1024"
|
|
--DEFINE BYTES_HEADING="GB"
|
|
DEFINE TIME_FORMAT=9,999
|
|
DEFINE TIME_DIVIDER="1"
|
|
DEFINE TIME_HEADING="sec"
|
|
|
|
|
|
PROMPT
|
|
Prompt **************************************************************
|
|
Prompt * Undo Statistics
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Number of Hours = '&&NoOfHours'
|
|
PROMPT * - Instance Number = '&&inst_id'
|
|
PROMPT * - WHERE Clause = '&&WHERECLAUSE'
|
|
PROMPT **************************************************************
|
|
PROMPT
|
|
|
|
|
|
COLUMN inst_id HEADING I# FORMAT 99
|
|
COLUMN Hour HEADING "Hour|(YYYYMMDD-HH24)" FORMAT a15
|
|
COLUMN tablespace_name HEADING "TableSpace" FORMAT a15
|
|
COLUMN "ActiveSize" HEADING "Active|Size|(&&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN UndoSize HEADING "Undo|Size|(&&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN ExpiredSize HEADING "Expired|Size|(&&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN UnExpiredSize HEADING "UnExpired|Size|(&&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN ExpiredRelSize HEADING "Expired|Rel|Size|(&&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN MAXCONCURRENCY HEADING "Max |Concurrent|txn|(#)" FORMAT &COUNT_FORMAT
|
|
COLUMN TXNCOUNT HEADING "Total|txn|(#)" FORMAT &COUNT_FORMAT
|
|
COLUMN MAXQUERYLEN HEADING "Max Query|Length|(sec)" FORMAT &BYTES_FORMAT
|
|
COLUMN MAXQUERYID HEADING "Max SQL Id" FORMAT a13
|
|
COLUMN tuned_undoretention HEADING "Tuned|Undo" FORMAT 9999999
|
|
COLUMN NOSPACEERRCNT HEADING "nospace|errors|(#)" FORMAT a10
|
|
COLUMN SSOLDERRCNT HEADING "ORA-01555|errors|(#)" FORMAT a10
|
|
|
|
--BREAK ON "Hour"
|
|
|
|
select to_char(a.BEGIN_TIME, 'YYYYMMDD-HH24') "Hour"
|
|
, a.INST_ID
|
|
, t.tablespace_name
|
|
&&_IF_ORA_10gR1_OR_HIGHER , ROUND(SUM(A.ACTIVEBLKS * t.block_size ) / &BYTES_DIVIDER ) "ActiveSize"
|
|
, ROUND(SUM(A.UNDOBLKS * t.block_size ) / &BYTES_DIVIDER ) "UndoSize"
|
|
&&_IF_ORA_10gR1_OR_HIGHER , ROUND(SUM(A.EXPIREDBLKS * t.block_size ) / &BYTES_DIVIDER ) "EXPIREDSize"
|
|
, ROUND(SUM(A.EXPBLKRELCNT * t.block_size ) / &BYTES_DIVIDER ) "EXPIREDRelSize"
|
|
&&_IF_ORA_10gR1_OR_HIGHER , ROUND(SUM(A.UNEXPIREDBLKS * t.block_size ) / &BYTES_DIVIDER ) "UNEXPIREDSize"
|
|
, max(MAXCONCURRENCY) MAXCONCURRENCY
|
|
, sum(TXNCOUNT) TXNCOUNT
|
|
, max(a.MAXQUERYLEN) MAXQUERYLEN
|
|
&&_IF_ORA_10gR1_OR_HIGHER , max(a.MAXQUERYID) MAXQUERYID
|
|
&&_IF_ORA_10gR1_OR_HIGHER , max(a.tuned_undoretention) tuned_undoretention
|
|
, DECODE(sum(NOSPACEERRCNT),0,'',sum(NOSPACEERRCNT)) NOSPACEERRCNT
|
|
, DECODE(sum(SSOLDERRCNT),0,'',sum(SSOLDERRCNT)) SSOLDERRCNT
|
|
from gv$undostat a
|
|
LEFT OUTER JOIN v$tablespace t2 ON a.undotsn = t2.ts#
|
|
LEFT OUTER JOIN dba_tablespaces t ON t2.name = t.tablespace_name
|
|
where a.inst_Id LIKE DECODE('&&inst_id',NULL,'%','','%','&&inst_id')
|
|
AND a.begin_time > (sysdate - (&NoOfHours)/24 )
|
|
&&WHERECLAUSE
|
|
group by to_char(a.BEGIN_TIME, 'YYYYMMDD-HH24')
|
|
, a.INST_ID
|
|
, a.undotsn
|
|
, t.tablespace_name
|
|
order by 1,2
|
|
/
|
|
|
|
@@footer
|