120 lines
5.1 KiB
SQL
120 lines
5.1 KiB
SQL
@@header
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display load profile from STATSPACK
|
|
* All figures delta between two consecutive snapshots
|
|
*
|
|
* Syntax : @file_name <no_of_days> [dbid] [instancenumber]
|
|
*
|
|
* dbid and instancenumber are optional.
|
|
* If not passes then it takes current instance's dbid and instancenumber
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 02-Jun-07 Vishal Gupta First Draft
|
|
*
|
|
*/
|
|
set term on
|
|
|
|
|
|
DEFINE days="&&1"
|
|
DEFINE DBID="&&2"
|
|
DEFINE INSTANCE_NUMBER="&&3"
|
|
|
|
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 3 CENTER ""
|
|
COLUMN snap_id HEADING "Snap#" FORMAT 999999
|
|
COLUMN snap_time HEADING "Snap Time" FORMAT a17
|
|
COLUMN elapsed_sec HEADING "Elap|(Sec)" FORMAT 999,999
|
|
COLUMN redo_size HEADING "Redo|Size|(KB)" FORMAT 9,999,999
|
|
COLUMN logical_reads HEADING "Logical|Reads|(K Blocks)" FORMAT 9,999,999,999
|
|
COLUMN block_changes HEADING "Block|Changes|(K)" FORMAT 9,999,999
|
|
COLUMN phy_reads HEADING "Phy|Reads|(K Blocks)" FORMAT 9,999,999
|
|
COLUMN phy_writes HEADING "Phy|Writes|(K Blocks)" FORMAT 9,999,999
|
|
COLUMN user_calls HEADING "User|Calls|('000s)" FORMAT 9,999
|
|
COLUMN parses HEADING "Parses|('000s)" FORMAT 9,999
|
|
COLUMN hard_parses_reads HEADING "Hard|Parses|('000s)" FORMAT 99,999
|
|
COLUMN sorts_mem HEADING "Sorts#|(Mem)|('000s)" FORMAT 99,999
|
|
COLUMN sorts_disk HEADING "Sorts#|(Disk)|('000s)" FORMAT 99,999
|
|
COLUMN logons HEADING "Logons" FORMAT 9,999
|
|
COLUMN commit HEADING "Commits" FORMAT 99,999
|
|
COLUMN rollback HEADING "Rollbacks" FORMAT 99,999
|
|
|
|
WITH snap
|
|
AS
|
|
(SELECT s.dbid
|
|
, s.instance_number
|
|
, s.snap_id
|
|
, LAG (s.snap_id) OVER (PARTITION BY s.dbid, s.instance_number ORDER BY s.dbid, s.instance_number, s.snap_id) prev_snap_id
|
|
, s.snap_time
|
|
, (s.snap_time - LAG (s.snap_time) OVER (PARTITION BY s.dbid, s.instance_number ORDER BY s.dbid, s.instance_number, s.snap_id) ) *1440 * 60 elapsed_sec
|
|
FROM stats$snapshot s
|
|
, v$database d
|
|
, v$instance i
|
|
WHERE s.dbid = NVL(&&DBID,d.dbid)
|
|
AND s.instance_number = NVL(&&INSTANCE_NUMBER, i.instance_number)
|
|
AND s.snap_time > sysdate - &&days
|
|
and s.snap_id > 40000
|
|
-- and to_char(s1.snap_time,'HH24') = '08'
|
|
)
|
|
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
|
|
, ROUND(MAX(DECODE(ss2.name, 'redo size', ROUND ((ss2.value - ss1.value)) ) ) / 1024) redo_size
|
|
, ROUND(MAX(DECODE(ss2.name, 'session logical reads', ROUND ((ss2.value - ss1.value)/1024)))) logical_reads
|
|
, ROUND(MAX(DECODE(ss2.name, 'db block changes', ROUND ((ss2.value - ss1.value)/1024)))) block_changes
|
|
, ROUND(MAX(DECODE(ss2.name, 'physical reads', ROUND ((ss2.value - ss1.value)/1024)))) phy_reads
|
|
, ROUND(MAX(DECODE(ss2.name, 'physical writes', ROUND ((ss2.value - ss1.value)/1024)))) phy_writes
|
|
, ROUND(MAX(DECODE(ss2.name, 'user calls', ROUND ((ss2.value - ss1.value)/1000)))) user_calls
|
|
, ROUND(MAX(DECODE(ss2.name, 'parse count (total)', ROUND ((ss2.value - ss1.value)/1000)))) parses
|
|
, ROUND(MAX(DECODE(ss2.name, 'parse count (hard)', ROUND ((ss2.value - ss1.value)/1000)))) hard_parses_reads
|
|
, ROUND(MAX(DECODE(ss2.name, 'sorts (memory)', ROUND ((ss2.value - ss1.value)/1000)))) sorts_mem
|
|
, ROUND(MAX(DECODE(ss2.name, 'sorts (disk)', ROUND ((ss2.value - ss1.value)/1000)))) sorts_disk
|
|
, ROUND(MAX(DECODE(ss2.name, 'logons cumulative', ROUND ((ss2.value - ss1.value))))) logons
|
|
, ROUND(MAX(DECODE(ss2.name, 'user commits', ROUND ((ss2.value - ss1.value))))) commit
|
|
, ROUND(MAX(DECODE(ss2.name, 'user rollbacks', ROUND ((ss2.value - ss1.value))))) rollback
|
|
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
|
|
GROUP BY s1.snap_id
|
|
, s1.snap_time
|
|
, elapsed_sec
|
|
order by s1.snap_time asc
|
|
/
|
|
|
|
|
|
@@footer |