Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle reactive monitoring tools

Oracle Database Tips by Donald Burleson

There is an important distinction between reactive (problem happening, take a look), and proactive Oracle tuning (monitor continuously, locate trends and alert before problem).

While proactive monitoring has show great success for predictive analysis and anticipation of upcoming problems, there are always cases where Oracle performance degrades and the DBA must intercede to locate the source of the bottleneck.

There are several ways to perform reactive Oracle monitoring:

  • Reactive Monitoring Software -Tools such as Quest Spotlight and Confio Ignite provide great real-time information about Oracle performance.  Downside, they are expensive and require in-dept knowledge of Oracle internal metrics.
  • Reactive Monitoring Scripts - Many Oracle professionals use the Ault Oracle tuning script collection, 670 ready-to-run scripts for monitoring real-time performance.

The problem with reactive tuning scripts is that many of the fixed tables (the x$ tables) are accumulated values, increasing since database startup time.  Hence, it's important to compare values between two periods and take the delta values to see what is changing.

In the IOUG 2006 paper titled ?BACK PORTING ADDM, AWR, ASH AND METRICS TO ORACLE 9I & 8I?, John Kanagaraj has an interesting script that takes two time-series samples to monitor current performance metrics, allowing the Oracle DBA to see changes in real time.  Note the references to the undocumented fixed x$ tables x$ksusgsta, x$kslei and x$ksled.

rem   Notes
rem   Has to be run by SYS to create the package; Requires STATSPACK$IDLE_EVENT
rem   Usage:
rem         set serveroutput on size 1000000 format wrapped
rem         set linesize 120
rem         set trimspool on
rem         For system level snapshots
rem         execute xxdba_pack.start_sys_snap;
rem         -- let the database do something
rem         execute xxdba_pack.end_sys_snap;
create or replace package sys.xxdba_pack as
      procedure start_sys_snap;
      procedure end_sys_snap;
create or replace package body sys.xxdba_pack as
target_sid integer;
cursor sys_stat is
      select indx,ksusdnam, ksusgstv
      from x$ksusgsta
      where ksusgstv != 0;
cursor sys_evt is
                s.indx          indx,
                d.kslednam      event,
                s.ksleswts      total_waits,
                s.kslestmo      total_timeouts,
                s.kslestim      time_waited,
                s.kslesmxt      max_wait
                x$kslei s, x$ksled d
        where   s.indx = d.indx
        and     s.ksleswts != 0
        and     d.kslednam not in (
                select event from perfstat.stats$idle_event
        order by
      type sys_stat_type is table of sys_stat%rowtype index by binary_integer;
      sys_stat_list sys_stat_type;
      type sys_evt_type is table of sys_evt%rowtype index by binary_integer;
      sys_evt_list sys_evt_type;
      m_sys_start_time      date;
      m_sys_start_flag      char(1);
      m_sys_end_time  date;
procedure start_sys_snap is
      m_sys_start_time := sysdate;
      for r in sys_stat loop
            sys_stat_list(r.indx).ksusgstv := r.ksusgstv;
      end loop;
      for i in sys_evt loop
            sys_evt_list(i.indx).event := i.event;
            sys_evt_list(i.indx).total_waits := i.total_waits;
            sys_evt_list(i.indx).total_timeouts := i.total_timeouts;
            sys_evt_list(i.indx).time_waited := i.time_waited;
            sys_evt_list(i.indx).max_wait := i.max_wait;
See IOUG 2006 proceedings CD for full script.
John Kanagaraj also shows his script in-action, with the DBA specifying the time between snapshots, a truly amazing script:
03:46:01 SQL> @snap_sys
Please enter number of seconds between snapshots : 30
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
System stats  - 01-Dec 03:50:21
Interval:-  31 seconds
Name                                                                     Value
----                                                                     -----
logons cumulative                                                           34
logons current                                                              -1
opened cursors cumulative                                                2,516
opened cursors current                                                     -67
user commits                                                               211
user rollbacks                                                               4
user calls                                                               4,318
recursive calls                                                        283,055
recursive cpu usage                                                      7,102
session logical reads                                                7,523,432
CPU used when call started                                               6,781
CPU used by this session                                                 8,824
<snip - cut out irrelevant data>
db block gets                                                          254,833
consistent gets                                                      7,270,588
physical reads                                                          85,521
db block changes                                                        41,170
consistent changes                                                      18,416
physical writes                                                          1,078
physical writes non checkpoint                                           1,065
redo entries                                                            20,645
redo size                                                            4,630,604
redo wastage                                                            71,012
redo writes                                                                283
redo blocks written                                                      9,664
redo write time                                                            210
data blocks consistent reads - undo records applied                     18,198
no work - consistent read gets                                       1,972,837
table scans (short tables)                                              55,748
table scans (long tables)                                                    7
table scan rows gotten                                               2,356,966
table scan blocks gotten                                               143,332
table fetch by rowid                                                 3,825,394
table fetch continued row                                              208,480
parse time cpu                                                             235
parse time elapsed                                                         271
parse count (total)                                                      3,420
parse count (hard)                                                          19
execute count                                                          102,984
bytes sent via SQL*Net to client                                       897,927
bytes received via SQL*Net from client                                 844,937
SQL*Net roundtrips to/from client                                        4,723
sorts (memory)                                                           1,990
sorts (rows)                                                            20,111
session cursor cache hits                                                1,435
session cursor cache count                                                -229
cursor authentications                                                       2
buffer is pinned count                                              11,928,248
buffer is not pinned count                                           5,904,527
Event                           TotalWaits   TotTmOuts   TimeWaitd     MaxWait
------                         ----------- ----------- ----------- -----------
latch free                           1,345       1,293       2,435           0
control file parallel write             10           0           9           0
buffer busy waits                      589           0         508           0
log file parallel write                284           0         209           0
LGWR wait for redo copy                  3           0           0           0
log file sync                          107           0          85           0
db file sequential read             21,885           0       5,560           0
db file scattered read               9,926           0       9,532           0
db file parallel write                  58           0           0           0
file open                              173           0           5           0
SQL*Net more data to client            274           0           5           0
SQL*Net more data from client           30           0          34           0
SQL*Net break/reset to client            6           0           0           0
PL/SQL procedure successfully completed.


For a full collection of Oracle monitoring scripts, we recommend the Mike Ault Oracle script collection, 681 scripts for under $80.  You can instantly download his scripts here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.