TA的每日心情 | 开心 2023-8-9 11:05 |
---|
|
- SET ECHO OFF
- SET FEEDBACK 6
- SET HEADING ON
- SET LINESIZE 256
- SET PAGESIZE 50000
- SET TERMOUT ON
- SET TIMING OFF
- SET TRIMOUT ON
- SET TRIMSPOOL ON
- SET VERIFY OFF
- CLEAR COLUMNS
- CLEAR BREAKS
- CLEAR COMPUTES
- COLUMN instance_name_print FORMAT a13 HEADING 'Instance Name'
- COLUMN snap_id FORMAT 9999999 HEADING 'Snap ID'
- COLUMN startup_time FORMAT a21 HEADING 'Instance Startup Time'
- COLUMN begin_interval_time FORMAT a20 HEADING 'Begin Interval Time'
- COLUMN end_interval_time FORMAT a20 HEADING 'End Interval Time'
- COLUMN elapsed_time FORMAT 999,999,999,999.99 HEADING 'Elapsed Time (min)'
- COLUMN db_time FORMAT 999,999,999,999.99 HEADING 'DB Time (min)'
- COLUMN pct_db_time FORMAT 999999999 HEADING '% DB Time'
- COLUMN cpu_time FORMAT 999,999,999.99 HEADING 'CPU Time (min)'
- BREAK ON instance_name_print ON startup_time
- SELECT
- i.instance_name instance_name_print
- , s.snap_id snap_id
- , TO_CHAR(s.startup_time, 'mm/dd/yyyy HH24:MI:SS') startup_time
- , TO_CHAR(s.begin_interval_time, 'mm/dd/yyyy HH24:MI:SS') begin_interval_time
- , TO_CHAR(s.end_interval_time, 'mm/dd/yyyy HH24:MI:SS') end_interval_time
- , 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) elapsed_time
- , ROUND((e.value - b.value)/1000000/60, 2) db_time
- , 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) pct_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;
复制代码
|
|