Mogens
Anjo Kolk wrote:
This is from grandpa's memory:
The wait interface (v$system_event, v$session_event, v$session_wait) were
introduced in 7.0.12. So if my memory works correctly at this early hour, that
was 1992. In 1995, I wrote the Oracle7 wait events and enqueue paper, after Jeff
Needham explained a couple of events in the 7.1/7.2 performance and tuning
guide. I saw that and decide that we need to do them all. In 1996 I got
involved in tuning one of the largest Oracle installations in the world and had
to convince the developers that they were doing the wrong thing. So I came up
with YAPP (actually even to day that site is still using the initial scripts
that we developed for them based on YAPP). I also started to give talks about
this way of tuning, I gave talks to support and consultant goups (1997 and
later). Then some one in support decided that a white paper was needed and that
became the YAPP white paper, published 1998.
S o a couple of dates and names:
1992 - Juan Loaiza (designed the wait interface)
1994 - Jeff Needham (documented 4/5 events in the performance and tuning guide
of 7.1/7.2)
1995/1996 - Oracle7 wait events and enqueue papers (Anjo Kolk)
1996 - YAPP developed onsite at one of the largest OLTP OPS sites in the world
1997/1998 - people insite of Oracle are getting exposed to the wait interface
tuning (Mogens Norgaard, Cary Millsap, Shari Yamaguchi)
1998 - white paper was published
1998 - oraperf website
2000 - other companies start to work with the wait interface (Hotsos/Miracle)
2001 - a lot of books are published with wait interface / YAPP methodology
2003 - Richard Niemic rewrites his book and throws out the buffer cache hit
ratio and introduces the wait interface, like it was invented yesterday ;-)
A couple of things that I have noted over the years:
1) DBAs want tricks (what parameter should I set) to solve perform ance problems,
not a methodology to solve performance problems
(learn a man how to fish instead of feeding him)
2) response time tuning is not new, the problem is that most tools out there
don't use it and most books don't mention it. It is a
complete paradigm shift for most DBA.
3) YAPP/response time tuning is not perfect (by any means), but it is so much
better than hit ratio and short list tuning.
4) Response time tuning will become more important as there are now products
coming out that do end-to-end response time tuning (actually there already a
couple).
Watch this space, as things will evolve over the next couple of months/years !
Anjo.
Jared Still wrote:Nice post. The 'revolution' is indeed not that new, more
of an underground guerilla movement.
And it wasn't televised. :)
Jared
On Sunday 21 April 2002 10:33, Don Granaman wrote: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 a nd 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 "Knowledg e 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 "Technica l 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: Jared Still
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
