103 lines
4.4 KiB
SQL
103 lines
4.4 KiB
SQL
-- +----------------------------------------------------------------------------+
|
|
-- | Jeffrey M. Hunter |
|
|
-- | jhunter@idevelopment.info |
|
|
-- | www.idevelopment.info |
|
|
-- |----------------------------------------------------------------------------|
|
|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
|
|
-- |----------------------------------------------------------------------------|
|
|
-- | DATABASE : Oracle |
|
|
-- | FILE : awr_snapshots_dbtime_xls.sql |
|
|
-- | CLASS : Automatic Workload Repository |
|
|
-- | PURPOSE : Provide a list of all AWR snapshots and the total database time |
|
|
-- | (DB Time) consumed within its interval. The output from this |
|
|
-- | script can be used when loading the data into MS Excel. |
|
|
-- | NOTE : As with any code, ensure to test this script in a development |
|
|
-- | environment before attempting to run it in production. |
|
|
-- +----------------------------------------------------------------------------+
|
|
|
|
SET TERMOUT OFF;
|
|
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
|
|
SELECT rpad(sys_context('USERENV', 'INSTANCE_NAME'), 17) current_instance FROM dual;
|
|
SET TERMOUT ON;
|
|
|
|
PROMPT
|
|
PROMPT +------------------------------------------------------------------------+
|
|
PROMPT | Report : AWR Snapshots (DB Time Report) for Microsoft Excel |
|
|
PROMPT | Instance : ¤t_instance |
|
|
PROMPT +------------------------------------------------------------------------+
|
|
|
|
SET ECHO OFF
|
|
SET FEEDBACK OFF
|
|
SET HEADING OFF
|
|
SET LINESIZE 32767
|
|
SET PAGESIZE 50000
|
|
SET TERMOUT OFF
|
|
SET TIMING OFF
|
|
SET TRIMOUT ON
|
|
SET TRIMSPOOL ON
|
|
SET VERIFY OFF
|
|
|
|
CLEAR COLUMNS
|
|
CLEAR BREAKS
|
|
CLEAR COMPUTES
|
|
|
|
DEFINE fileName=awr_snapshots_dbtime_xls
|
|
|
|
COLUMN dbname NEW_VALUE _dbname NOPRINT
|
|
SELECT name dbname FROM v$database;
|
|
|
|
COLUMN spool_time NEW_VALUE _spool_time NOPRINT
|
|
SELECT TO_CHAR(SYSDATE,'YYYYMMDD') spool_time FROM dual;
|
|
|
|
SPOOL &FileName._&_dbname._&_spool_time..txt
|
|
|
|
SELECT
|
|
'Instance Name' || chr(9)
|
|
|| 'Instance Startup Time' || chr(9)
|
|
|| 'Begin Interval Time' || chr(9)
|
|
|| 'End Interval Time' || chr(9)
|
|
|| 'Elapsed Time (min)' || chr(9)
|
|
|| 'DB Time (min)' || chr(9)
|
|
|| '% DB Time'
|
|
FROM dual;
|
|
|
|
SELECT
|
|
i.instance_name || chr(9)
|
|
|| TO_CHAR(s.startup_time, 'mm/dd/yyyy HH24:MI:SS') || chr(9)
|
|
|| TO_CHAR(s.begin_interval_time, 'mm/dd/yyyy HH24:MI:SS') || chr(9)
|
|
|| TO_CHAR(s.end_interval_time, 'mm/dd/yyyy HH24:MI:SS') || chr(9)
|
|
|| ROUND(EXTRACT(DAY FROM s.end_interval_time - s.begin_interval_time) * 1440 +
|
|
EXTRACT(HOUR FROM s.end_interval_time - s.begin_interval_time) * 60 +
|
|
EXTRACT(MINUTE FROM s.end_interval_time - s.begin_interval_time) +
|
|
EXTRACT(SECOND FROM s.end_interval_time - s.begin_interval_time) / 60, 2) || chr(9)
|
|
|| ROUND((e.value - b.value)/1000000/60, 2) || chr(9)
|
|
|| ROUND(((((e.value - b.value)/1000000/60) / (EXTRACT(DAY FROM s.end_interval_time - s.begin_interval_time) * 1440 +
|
|
EXTRACT(HOUR FROM s.end_interval_time - s.begin_interval_time) * 60 +
|
|
EXTRACT(MINUTE FROM s.end_interval_time - s.begin_interval_time) +
|
|
EXTRACT(SECOND FROM s.end_interval_time - s.begin_interval_time) / 60) ) * 100), 2) instance_db_time
|
|
FROM
|
|
dba_hist_snapshot s
|
|
, gv$instance i
|
|
, dba_hist_sys_time_model e
|
|
, dba_hist_sys_time_model b
|
|
WHERE
|
|
i.instance_number = s.instance_number
|
|
AND e.snap_id = s.snap_id
|
|
AND b.snap_id = s.snap_id - 1
|
|
AND e.stat_id = b.stat_id
|
|
AND e.instance_number = b.instance_number
|
|
AND e.instance_number = s.instance_number
|
|
AND e.stat_name = 'DB time'
|
|
ORDER BY
|
|
i.instance_name
|
|
, s.snap_id;
|
|
|
|
SPOOL OFF
|
|
|
|
SET FEEDBACK 6
|
|
SET HEADING ON
|
|
SET TERMOUT ON
|
|
|
|
PROMPT Wrote report to &FileName._&_dbname._&_spool_time..txt
|
|
PROMPT
|