Re: ioug-a question

There seems to be a lot of interest in the "tuning revolution" here, so...

The basics revolve around the views v$system_event, v$session_event, and v$session_wait, and v$event_name - and the 10046 event. Rather than try to paraphrase/summarize/expound upon the details, here are some of the best sites for researching the topic. (I'm sure there are other excellent issites, but these are the ones I know about that most certainly qualify.)

Historical note: A year ago at the IOUG-A conference, this was considered
"revolutionary". This year it was the most widely presented and discussed
topic at the conference. In fact, the technique has been around for a long time, it just wasn't widely known or accepted. My initiation to it was after an Oracle consultant came out and left something called APS7 installed on an Oracle7 system - in 1997! (Aside: Did APS8 ever exist?). APS7 was written by Milsap's group at Oracle and some of it uses wait-based tuning techniques. Poking around and looking at the scripts opened the door to profound revelation.

Motivational scenario: Cast: DBA (you) and PHB (Pointy-haired boss) PHB: "This tuning report (or GUI tool) shows the cache-hit ratio as too low. You should tune the database"
DBA: "That is to be expected. Batch manipulated 100 GB of data last night. Cache-hit ratio is a meaningless metric anyway." PHB: "But this book says it should be > 95%. Besides, we are on the English system - we don't use metric!"

www.oraperf.com - Anjo Kolk
Anjo Kolk's YAPP paper (a pioneering work on the topic). Consider it as prerequisite background reading and the departure point for your journey.

www.hotsos.com - Cary Milsap
Requires (free) registration. Click on "Knowledge On-line". There are acres of papers here that are at the core of modern tuning techniques, including "Oracle Kernel Event Documentation Index", "Oracle System Performance Analysis Using Event 10046", Why 99% Database Buffer Cache Hit Ratio is NOT OK", "Performance Management Myths and Facts", "Why You Shoud Focus on LIOs Instead of PIOs", ad infinitum...

www.orapub.com - Craig Shallahamer
Requires (free) registration. Click on "Technical Papers" at the top . Related items include #149 - Gaja's "Myths & Follore...", #134 - Beresniewicz's "Pirahnas in the Pool", and #113 = Shallahamer's "Direct Contention Identification...". There are several others related to wait-based tuning also.

www.miracleas.dk - Miracle A/S (Mogens Nørgaard, Bjørn Engsig, et al) Click on "Technical Information". Anjo Kolk's YAPP paper is also available here, as is the Miracle Monitor, and some other good stuff. (Mogens has the reputation of first saying, in public at least, that "Ratios are for losers!")

www.ixora.com.au - Steve Adams
Tons of stuff related to wait-based tuning - far too much to list.

www.evdbt.com - Tim Gorman
Click on "Download" at the bottom, in the middle. I don't see any papers specifically on the topic, but there might be soon - he did a full day seminar on advanced diagnostics at IOUG-A Live 2002 where the basics of wait-based tuning were covered in detail. A number of the scripts (e.g. sesstime.sql, systime.sql, etc.) are directly related.

www.jlcomp.demon.co.uk - Jonathan Lewis
Under "Index of Topics" -> "Monitoring and Tuning" -> "Tuning" ->
"v$sytem_event" ... and probably other places on the site...

Note that many of these people are active participants in ORACLE-L. Hotsos (Milsap) and OraPub (Shallahamer) both offer training in these techniques. Also, another esteemed list member, Kirti Deshpande, did an excellent "Quick Tips" session on identifying wait events at IOUG-A 2002.

Don Granaman
[OraSaurus - Honk if you remember UFI !]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Don Granaman
  INET: granaman_at_cox.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).