|
|
SQL ScriptStatspack Database Load Profile Script |
|
Displays all Load Profile statistics for many statspack snapshots in single report. This makes for easy trend analysis of statspack reports longer time periods which can then be imported into Spreadsheet for graphing
REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved.
-- -- Get the current database/instance information - this will be used -- later in the report along with bid, eid to lookup snapshots column inst_num heading "Inst Num" new_value inst_num format 99999; column inst_name heading "Instance" new_value inst_name format a10; column db_name heading "DB Name" new_value db_name format a10; column dbid heading "DB Id" new_value dbid format 9999999999 just c; variable dbid number; variable inst_num number; variable inst_name varchar2(20); variable db_name varchar2(20); variable l_instance_num_def number; begin /* change the instance_num if you have many instances in the same perfstat schema (e.g. RAC/OPS) */ :l_instance_num_def := 1; select dbid, instance_number, instance_name,db_name INTO :dbid, :inst_num, :inst_name, :db_name FROM STATS$DATABASE_INSTANCE WHERE instance_number = :l_instance_num_def ; end; / print dbid print db_name -- -- Tablespace IO summary statistics set pages 20000 set lines 200 set trimspool on set term off set echo off set feed off spool sprepprofile.txt column snap_id heading 'SnapId' format 9990; column ls format 999,999,999,990.99 heading "Logons/s"; column rs format 999,999,999,990.99 heading "Redo Size/s"; column lr format 999,999,999,990.99 heading "Logical Reads/s"; column dc format 999,999,999,990.99 heading "DB Block Changes/s"; column pr format 999,999,999,990.99 heading "Physical Reads/s"; column pw format 999,999,999,990.99 heading "Physical Writes/s"; column tr format 999,999,999,990.99 heading "Transactions/s"; col snapt format a12 heading 'Snap Time' select brs.snap_id ,ers.snap_id , to_char(es.snap_time,'dd Mon HH24:mi') snapt , round((ecl.value-bcl.value)/(round(((es.snap_time - bs.snap_time) * 60 * 24 * 60), 0)),2) ls , round((ers.value-brs.value)/(round(((es.snap_time - bs.snap_time) * 60 * 24 * 60), 0)),2) rs , round((elr.value-blr.value)/(round(((es.snap_time - bs.snap_time) * 60 * 24 * 60), 0)),2) lr , round((edc.value-bdc.value)/(round(((es.snap_time - bs.snap_time) * 60 * 24 * 60), 0)),2) dc , round((epr.value-bpr.value)/(round(((es.snap_time - bs.snap_time) * 60 * 24 * 60), 0)),2) pr , round((epw.value-bpw.value)/(round(((es.snap_time - bs.snap_time) * 60 * 24 * 60), 0)),2) pw , round(((euc.value+eur.value)-(buc.value+bur.value))/(round(((es.snap_time - bs.snap_time) * 1440 * 60), 0)),2) tr from (select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'redo size') brs ,(select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'redo size') ers ,(select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'session logical reads') blr ,(select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'session logical reads') elr ,(select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'db block changes') bdc ,(select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'db block changes') edc ,(select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'physical reads') bpr ,(select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'physical reads') epr ,(select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'physical writes') bpw ,(select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'physical writes') epw ,(select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'user commits') buc ,(select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'user commits') euc ,(select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'user rollbacks') bur ,(select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'user rollbacks') eur ,(select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'logons cumulative') bcl ,(select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'logons cumulative') ecl , stats$snapshot bs , stats$snapshot es where es.snap_id = ers.snap_id and bs.snap_id = brs.snap_id and ers.snap_id = brs.snap_id + 1 and ers.snap_id = elr.snap_id and brs.snap_id = blr.snap_id and ers.snap_id = edc.snap_id and brs.snap_id = bdc.snap_id and ers.snap_id = epr.snap_id and brs.snap_id = bpr.snap_id and ers.snap_id = epw.snap_id and brs.snap_id = bpw.snap_id and ers.snap_id = euc.snap_id and brs.snap_id = buc.snap_id and ers.snap_id = eur.snap_id and brs.snap_id = bur.snap_id and ers.snap_id = ecl.snap_id and brs.snap_id = bcl.snap_id and ers.value >= brs.value and to_char(bs.snap_time,'DY') NOT IN ('SAT','SUN') order by brs.snap_id; spool off
Return to Index of SQL Scripts
General Information :
Home | Company Profile
| Contact Us | Legal
Database Support :
Proactive Support
|
Pay Per Incident Support
Expert Services :
High Availability
|
Creative Solutions
|
Training
Build Services :
Oracle Installations
|
Revive your Oracle Setup
|
Low Cost Development
Free Oracle Resources:
DBA Scripts
|
Oracle Links