102 lines
4.8 KiB
SQL
102 lines
4.8 KiB
SQL
set echo on
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display load profile from STATSPACK
|
|
* All figures are per second
|
|
*
|
|
* Syntax : @file_name <no_of_days>
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 02-Jun-07 Vishal Gupta First Draft
|
|
*
|
|
*/
|
|
set echo off
|
|
set verify off
|
|
set pages 10000
|
|
set lines 150
|
|
TTITLE CENTER "L O A D P R O F I L E T R E N D" -
|
|
SKIP 1 CENTER "###################################" -
|
|
SKIP 1 CENTER "( All figures per second from STATSPACK)" -
|
|
SKIP 1 CENTER ""
|
|
COLUMN snap_id HEADING "Snap#" FORMAT 999,999
|
|
COLUMN snap_time HEADING "Snap Time" FORMAT a17
|
|
COLUMN elapsed_sec HEADING "Elap|(Sec)" FORMAT 999,999,999
|
|
COLUMN redo_size_per_sec HEADING "Redo|Size|(KB)" FORMAT 999,999
|
|
COLUMN logical_reads_per_sec HEADING "Logical|Reads|(Blocks)" FORMAT 999,999
|
|
COLUMN block_changes_per_sec HEADING "Block|Changes" FORMAT 99,999,999
|
|
COLUMN phy_reads_per_sec HEADING "Phy|Reads|(Blocks)" FORMAT 9,999,999
|
|
COLUMN phy_writes_per_sec HEADING "Phy|Writes|(Blocks)" FORMAT 9,999,999
|
|
COLUMN user_calls_per_sec HEADING "User|Calls" FORMAT 9,999
|
|
COLUMN parses_per_sec HEADING "Parses" FORMAT 99,999
|
|
COLUMN hard_parses_reads_per_sec HEADING "Hard|Parses" FORMAT 99,999
|
|
COLUMN sorts_mem_per_sec HEADING "Sorts#|(Mem)" FORMAT 99,999
|
|
COLUMN sorts_disk_per_sec HEADING "Sorts#|(Disk)" FORMAT 99,999
|
|
COLUMN logons_per_sec HEADING "Logons" FORMAT 9,999
|
|
COLUMN commit_per_sec HEADING "Commits" FORMAT 99,999
|
|
COLUMN rollback_per_sec HEADING "Roll|backs" FORMAT 99,999
|
|
|
|
WITH snap
|
|
AS
|
|
(SELECT dbid
|
|
, instance_number
|
|
, snap_id
|
|
, LAG (snap_id) OVER (PARTITION BY dbid, instance_number ORDER BY dbid, instance_number, snap_id) prev_snap_id
|
|
, snap_time
|
|
, (snap_time - LAG (snap_time) OVER (PARTITION BY dbid, instance_number ORDER BY dbid, instance_number, snap_id) ) *1440 * 60 elapsed_sec
|
|
FROM stats$snapshot)
|
|
SELECT /*+ USE_NL(s1,s2,ss1,ss2) */
|
|
s1.snap_id
|
|
, TO_CHAR(s1.snap_time,'dd-mm-yy hh24:mi:ss') snap_time
|
|
-- , s1.elapsed_sec
|
|
, MAX(DECODE(ss2.name, 'redo size', ROUND ((ss2.value - ss1.value)/ elapsed_sec ) ) ) / 1024 redo_size_per_sec
|
|
, MAX(DECODE(ss2.name, 'session logical reads', ROUND ((ss2.value - ss1.value)/ elapsed_sec ))) logical_reads_per_sec
|
|
, MAX(DECODE(ss2.name, 'db block changes', ROUND ((ss2.value - ss1.value)/ elapsed_sec ))) block_changes_per_sec
|
|
, MAX(DECODE(ss2.name, 'physical reads', ROUND ((ss2.value - ss1.value)/ elapsed_sec )))/1024 phy_reads_per_sec
|
|
, MAX(DECODE(ss2.name, 'physical writes', ROUND ((ss2.value - ss1.value)/elapsed_sec)))/1024 phy_writes_per_sec
|
|
, MAX(DECODE(ss2.name, 'user calls', ROUND ((ss2.value - ss1.value)/elapsed_sec))) user_calls_per_sec
|
|
, MAX(DECODE(ss2.name, 'parse count (total)', ROUND ((ss2.value - ss1.value)/elapsed_sec))) parses_per_sec
|
|
, MAX(DECODE(ss2.name, 'parse count (hard)', ROUND ((ss2.value - ss1.value)/elapsed_sec))) hard_parses_reads_per_sec
|
|
, MAX(DECODE(ss2.name, 'sorts (memory)', ROUND ((ss2.value - ss1.value)/elapsed_sec))) sorts_mem_per_sec
|
|
, MAX(DECODE(ss2.name, 'sorts (disk)', ROUND ((ss2.value - ss1.value)/elapsed_sec))) sorts_disk_per_sec
|
|
, MAX(DECODE(ss2.name, 'logons cumulative', ROUND ((ss2.value - ss1.value)/elapsed_sec))) logons_per_sec
|
|
, MAX(DECODE(ss2.name, 'user commits', ROUND ((ss2.value - ss1.value)/elapsed_sec))) commit_per_sec
|
|
, MAX(DECODE(ss2.name, 'user rollbacks', ROUND ((ss2.value - ss1.value)/elapsed_sec))) rollback_per_sec
|
|
FROM snap s1
|
|
, stats$sysstat ss1
|
|
, stats$sysstat ss2
|
|
where s1.dbid = ss1.dbid
|
|
and s1.instance_number = ss1.instance_number
|
|
and s1.prev_snap_id = ss1.snap_id
|
|
and s1.dbid = ss2.dbid
|
|
and s1.instance_number = ss2.instance_number
|
|
and s1.snap_id = ss2.snap_id
|
|
and ss1.name = ss2.name
|
|
and ss2.name in ('redo size'
|
|
,'session logical reads'
|
|
,'db block changes'
|
|
,'physical reads'
|
|
,'physical writes'
|
|
,'user calls'
|
|
,'parse count (total)'
|
|
,'parse count (hard)'
|
|
,'sorts (memory)'
|
|
,'sorts (disk)'
|
|
,'logons cumulative'
|
|
,'execute count'
|
|
,'user commits'
|
|
,'user rollbacks'
|
|
)
|
|
and elapsed_sec > 0
|
|
and s1.snap_id > 2880
|
|
and s1.snap_time > sysdate - &days
|
|
GROUP BY s1.snap_id
|
|
, s1.snap_time
|
|
, elapsed_sec
|
|
order by s1.snap_time asc
|
|
/
|
|
|