121 lines
5.4 KiB
SQL
121 lines
5.4 KiB
SQL
@@header
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display load profile from STATSPACK
|
|
* All figures are per second
|
|
*
|
|
* 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 2 CENTER "( All figures per second from STATSPACK)" -
|
|
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,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 "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)/ elapsed_sec ) ) ) / 1024) redo_size_per_sec
|
|
, ROUND(MAX(DECODE(ss2.name, 'session logical reads', ROUND ((ss2.value - ss1.value)/ elapsed_sec )))) logical_reads_per_sec
|
|
, ROUND(MAX(DECODE(ss2.name, 'db block changes', ROUND ((ss2.value - ss1.value)/ elapsed_sec )))) block_changes_per_sec
|
|
, ROUND(MAX(DECODE(ss2.name, 'physical reads', ROUND ((ss2.value - ss1.value)/ elapsed_sec )))/1024) phy_reads_per_sec
|
|
, ROUND(MAX(DECODE(ss2.name, 'physical writes', ROUND ((ss2.value - ss1.value)/elapsed_sec)))/1024) phy_writes_per_sec
|
|
, ROUND(MAX(DECODE(ss2.name, 'user calls', ROUND ((ss2.value - ss1.value)/elapsed_sec)))) user_calls_per_sec
|
|
, ROUND(MAX(DECODE(ss2.name, 'parse count (total)', ROUND ((ss2.value - ss1.value)/elapsed_sec)))) parses_per_sec
|
|
, ROUND(MAX(DECODE(ss2.name, 'parse count (hard)', ROUND ((ss2.value - ss1.value)/elapsed_sec)))) hard_parses_reads_per_sec
|
|
, ROUND(MAX(DECODE(ss2.name, 'sorts (memory)', ROUND ((ss2.value - ss1.value)/elapsed_sec)))) sorts_mem_per_sec
|
|
, ROUND(MAX(DECODE(ss2.name, 'sorts (disk)', ROUND ((ss2.value - ss1.value)/elapsed_sec)))) sorts_disk_per_sec
|
|
, ROUND(MAX(DECODE(ss2.name, 'logons cumulative', ROUND ((ss2.value - ss1.value)/elapsed_sec)))) logons_per_sec
|
|
, ROUND(MAX(DECODE(ss2.name, 'user commits', ROUND ((ss2.value - ss1.value)/elapsed_sec)))) commit_per_sec
|
|
, ROUND(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
|
|
GROUP BY s1.snap_id
|
|
, s1.snap_time
|
|
, elapsed_sec
|
|
order by s1.snap_time asc
|
|
/
|
|
|
|
|
|
@@footer |