( last change 02/10/2001
This monitor is set up to be
a DBA tool, but the same mechanism
View the Monitor Home Page
oracle + unix + cron + perl + ftp + web
An Oracle performance/tuning Monitor
(( All for free! ))
(( Once you've spent millions ))
(( on hardware and software! ))
could be used as part of any application.
The idea is this:
- Take things you want to know about
- Develop the appropriate oracle
queries to extract that information.
- Run those queries on a timely basis
- Utilize unix scripting to extract
the appropriate data and append it to raw data files.
- Use perl to process the raw
data files into html formatted web pages.
the web pages to your intranet/internet site.
- Use the web browser to view.
- The queries are run automatically and
- Most queries run against the v$tables
which are memory resident.
- The raw data files are processed externally
to the database thus not impacting it.
- No Oracle tables are required for data
storage or processing.
- The generated raw files can become
- The information is a browser bookmark
- If a problem occurs historical data
Lets define some conventions (though
arbitrary) to focus on tunning/performance issues.
How much "work" is a database
We will define "work" as how
much physical reading and writing is done on the database.
How "efficient" is the
database in operation?
We will define "efficient"
as how much activity is taking place in memory (cashe hit ratios).
Also we can add large full table scans and disk vrs memory sorts.
How "healthy" is the database.
We will define "healthy" as
the databases use of physical space, or basically extent allocation
factors. Also index "dead" space.
- Oracle's default offering: utlbstat/utlestat
- This contains a great deal of information, but it only measures
from the time of starting the database through the present.
- The output is not particullary user friendly.
- It does serve as a good starting point.
- A typical graphics Monitor from QUEST software: Sqlab
- Generates lots of info, nice graphs, can be usefull.
- Tends to provide "too" much information.
- Read/Write Tick Tock scripts (By hand).
- This extends the utlb-utle stat concept.
- First you run the Tick script (rwtick), then you wait
a while and run the Tock script (rwtock).
- Tick collects read/write stats for each database file.
- Tock collects the same stats at a later time, and then through
scripting, calculates the differences, and reports for each file,
each device, each application (subject to naming conventions),
and lists in decending order files having the most reads and
- To View.
- Now you can run rwtock again and again to see where
I/O's are accumulation as time moves foreward.
- Lets walk through some examples to
see our conventions applied in an automated manner (cron).
- Read/Writes in 1 Minute increments.
- Read/Writes in 5 Minute increments.
- Library Cache statistics in 5 Minute increments.
- System Statistics in 5 Minute increments. (ssv.log, ssvname.log)
- Extent Warnings by the Hour
- Validate Indexes by the Week
- Some basics:
- Unix items of possible interest: