SQL Script Get System Stats

Updated: 02-Jun-2005
Version: 1.0

Description

Displays the values of any System Statistics in place or in a stats table

Parameters

&statlabel - Statistics Label
&tname - Table name of the system stats

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set serveroutput on size 1000000
set verify off 
set echo off

REM 
REM this file queries the values in a stored system stats table.

spool getsysstats.lst

ACCEPT tname PROMPT 'Enter value for stattabname : '
ACCEPT statlabel PROMPT 'Enter value for stat id : '

DECLARE 

l_stattab_owner VARCHAR2(30) := 'SYSTEM';
l_statid       VARCHAR(20) := '&statlabel';
l_stattab_name VARCHAR2(30) := '&tname';

STATUS VARCHAR2(20); 
DSTART DATE; 
DSTOP DATE; 
PVALUE NUMBER; 
PNAME VARCHAR2(30); 

BEGIN 
  PNAME := 'cpuspeed'; 
  DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab =>l_stattab_name, statid => l_statid, statown => l_stattab_owner); 
  DBMS_OUTPUT.PUT_LINE('Status                                                   : '||status); 
  DBMS_OUTPUT.PUT_LINE('Start date                                               : '||to_char(dstart,'dd-mon-yyyy hh24:mi')); 
  DBMS_OUTPUT.PUT_LINE('Stop Date                                                : '||to_char(dstop,'dd-mon-yyyy hh24:mi'));
  DBMS_OUTPUT.PUT_LINE('cpu in mhz                                               : '||pvalue); 
  PNAME := 'sreadtim'; 
  DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab =>l_stattab_name, statid => l_statid, statown => l_stattab_owner); 
  DBMS_OUTPUT.PUT_LINE('single block readtime (random read) in ms                : '||pvalue); 
  PNAME := 'mreadtim'; 
  DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab =>l_stattab_name, statid => l_statid, statown => l_stattab_owner); 
  DBMS_OUTPUT.PUT_LINE('multiblock readtime (sequental read) in ms               : '||pvalue); 
  PNAME := 'mbrc'; 
  DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab =>l_stattab_name, statid => l_statid, statown => l_stattab_owner); 
  DBMS_OUTPUT.PUT_LINE('average multiblock readcount for sequenial read in blocks: '||pvalue); 
  PNAME := 'maxthr'; 
  DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab =>l_stattab_name, statid => l_statid, statown => l_stattab_owner); 
  DBMS_OUTPUT.PUT_LINE('maximum IO system throughput in bytes/sec                : '||pvalue); 
  PNAME := 'slavethr'; 
  DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab =>l_stattab_name, statid => l_statid, statown => l_stattab_owner); 
  DBMS_OUTPUT.PUT_LINE('maximum IO slave throughput in bytes/sec                 : '||pvalue); 
END; 
/    
   

Related Links

Oracle 10g: (None) Gather System Statistics

Return to Index of SQL Scripts

Site Map

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