92 lines
3.5 KiB
MySQL
92 lines
3.5 KiB
MySQL
@@header
|
|
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Statistics gathering operations history
|
|
* Parameters : 1 - Number of Days (Default 7)
|
|
* 2 - Operations (e.g gather_database_stats, gather_table_stats) (Use % as wild card, \ as escape)
|
|
* 3 - Target (Use % as wild card, \ as escape)
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 22-Jun-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE days
|
|
UNDEFINE operation
|
|
UNDEFINE target
|
|
UNDEFINE where_clause
|
|
|
|
|
|
DEFINE days="&&1"
|
|
DEFINE operation="&&2"
|
|
DEFINE target="&&3"
|
|
DEFINE where_clause="&&4"
|
|
|
|
|
|
COLUMN _days NEW_VALUE days NOPRINT
|
|
COLUMN _operation NEW_VALUE operation NOPRINT
|
|
COLUMN _target NEW_VALUE target NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&days','','7','&&days') "_days"
|
|
, DECODE('&&operation','','gather_database_stats%','&&operation') "_operation"
|
|
, DECODE('&&target','','%',UPPER('&&target')) "_target"
|
|
FROM DUAL
|
|
;
|
|
|
|
set term on
|
|
|
|
|
|
PROMPT
|
|
PROMPT ***********************************************************************
|
|
PROMPT * S T A T I S T I C S G A T H E R H I S T O R Y
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Number of Days = '&&days'
|
|
PROMPT * - Gather Operation = '&&operation'
|
|
PROMPT * Possible Values - alter_stats_history_retention
|
|
PROMPT * gather_database_stats(auto)
|
|
PROMPT * [gather|delete|import|export|restore]_database_stats
|
|
PROMPT * [gather|delete|import|export|restore|lock|unlock]_schema_stats
|
|
PROMPT * [gather|delete|import|export|restore|lock|unlock|copy|set]_table_stats
|
|
PROMPT * [gather|delete|import|export|restore]_dictionary_stats
|
|
PROMPT * [gather|delete|import|export|restore]_fixed_objects_stats
|
|
PROMPT * [gather|delete|import|export|purge|get|set]_system_stats
|
|
PROMPT * - Target = '&&target'
|
|
PROMPT * - Where Clause = "&&where_clause"
|
|
PROMPT ***********************************************************************
|
|
|
|
|
|
COLUMN operation HEADING "Operation" FORMAT a29
|
|
COLUMN duration HEADING "Duration" FORMAT a28
|
|
COLUMN start_time HEADING "StartTime" FORMAT a22
|
|
COLUMN end_time HEADING "EndTime" FORMAT a22
|
|
COLUMN target HEADING "Target" FORMAT a60
|
|
|
|
SELECT o.operation
|
|
, TO_CHAR(o.start_time,'Dy DD-MON-YY HH24:MI:SS') start_time
|
|
, TO_CHAR(o.end_time,'Dy DD-MON-YY HH24:MI:SS') end_time
|
|
, REPLACE(o.end_time - o.start_time,'+0000000','+') duration
|
|
, o.target
|
|
FROM sys.wri$_optstat_opr o
|
|
--WHERE o.start_time > CAST( systimestamp - &&days as TIMESTAMP)
|
|
WHERE o.start_time > cast(current_timestamp - &&days as timestamp WITH TIME ZONE )
|
|
AND o.operation LIKE '&&operation' ESCAPE '\'
|
|
AND UPPER(NVL(o.target,'x')) LIKE '&&target' ESCAPE '\'
|
|
AND UPPER(NVL(o.target,'x')) NOT LIKE 'SYS.UTL_RECOMP%'
|
|
&&where_clause
|
|
ORDER BY o.start_time asc
|
|
;
|
|
|
|
@@footer |