ORACLE MONITOR

    PRESENTATION

    ( last change 02/10/2001
    )

    
 

    
 oracle + unix + cron + perl + ftp + web 
=
An Oracle performance/tuning Monitor
(( All for free! ))
(( Once you've spent millions ))
(( on hardware and software! ))


feedback: holg@earthlink.net

View the Monitor Home Page

The idea is this:

• Take things you want to know about the database.
• Develop the appropriate oracle queries to extract that information.
• Run those queries on a timely basis (use cron).
• 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.
• Ftp the web pages to your intranet/internet site.
• Use the web browser to view.

Benefits:

• The queries are run automatically and timely.
• 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 historically valuable.
• The information is a browser bookmark away.
• If a problem occurs historical data is available.

Lets define some conventions (though arbitrary) to focus on tunning/performance issues.

How much "work" is a database doing?

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.

Topics:

• Monitoring:
  • 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 most writes.
  • 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).
  • Work:
    • rwm - Read/Writes in 1 Minute increments.
    • rwv - Read/Writes in 5 Minute increments.
  • Efficiency:
    • lcv - Library Cache statistics in 5 Minute increments.
    • ssv - System Statistics in 5 Minute increments. (ssv.log, ssvname.log)
  • Health:
    • ewh - Extent Warnings by the Hour
    • viw - Validate Indexes by the Week
• Some basics:
  • Naming conventions
  • The crontab file
• References:
  • Download full source: (For DOJV instance)
  • Perl:
    • In 5 Minutes
    • References
• Unix items of possible interest:
  • My unix scripts: www.holg.com/unix
  • My unix line editor:ule - www.holg.com/ule
  • My perl based cross tabulation generating program: XTAB - www.holg.com/xtab

.