testing

29.06.37.33.851680_at_verizon.net>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse_at_google.com
Injection-Info: m7g2000cwm.googlegroups.com; posting-host=63.158.168.108;

   posting-account=ytcoAwwAAADhCs0M3G1mFO5tqSfx4ge9 Xref: news.f.de.plusline.net comp.databases.oracle.server:188502

Mladen Gogala wrote:
> On Sat, 28 Oct 2006 16:14:08 -0700, Charles Hooper wrote:
>
> > First, make certain that the whole database instance is properly
> > configured as a whole. Gaja Krishna Vaidyanatha's "101 Oracle
> > Performance Tuning" book will be very helpful. The book attacks
> > problems by using the wait event interface that is built into Oracle.
> > This was one of the first tuning books that I read, and I was able to
> > improve the performance of one database application by a factor of five
> > or six - a 2.5 hour run time against a mostly untuned database dropped
> > to 19 to 25 minutes. Gaja Krishna Vaidyanatha coined the phrase
> > "compulsive tuning disorder" - it is important to know when to stop
> > tuning.
>
> Charles, I second your recommendation for that book. I also have to
> express dissent with "configuring database instance as a whole"
> methodology. I consider this to be a dangerous illusion. Database is just
> the place where data are kept, nothing more, nothing less. Processes are
> what can be tuned, not places. Database instance is a place, not a process.
> What the person who engages in tuning must concentrate on is application,
> not the database. That is a crucial distinction. So called "tuning
> instance" was a black art that used to involve setting database buffer
> cache, log buffer size, timed statistics and some arcane undocumented
> parameters, like "_trace_files_public". It is a hodge-podge of the same
> type that Cary Millsap calls "Method C". There are general recommendations
> to follow, for the vast majority of the applications to work well, but one
> does, generally speaking, not "configure database instance as a whole".
> I may have misunderstood you, you haven't used the word "tune", you used
> the verb "configure", but further down, you do explicitly talk about
> tuning.
>
>
> >
> > Once the database instance is tuned, and nothing more can be gained
> > from looking at the wait interface, if the performance problem is still
> > present, take a look at performing a 10046 trace at level 8 for the
> > session that is experiencing the performance problem.
>
>
> There is no such point. You can decide that your application system is
> working fast enough, so that you don't have to do any more tuning, but the
> wait interface is the truth, the way and the tuning process itself. Such a
> bold claim comes from the simple logic: In order to make application work
> faster, one has to eliminate the places where the application waits for
> something to be done. In order to do so, one must first identify waits.
> The output from the 10046, with waits and binds is also looking into the
> wait interface. When you use sort=(exeela) argument, you are looking into
> the SQL statement(s) that you have to wait for the longest time.
> Sometimes, people claim that wait-based tuning is obsolete, and that one
> has to take a "holistic approach" and engage in "response time tuning".
> It is important to note that both methodologies are essentially the same.
> The response time tuning uses the very same logic as the wait-based
> tuning, only applies it with slightly more project management skills.
> This approach attempts to identify the steps where the time is
> spent. Instead of tuning just wait events, you tune the process itself,
> most likely by tuning the algorithm used. In other words, you're tuning
> waits for the natural process steps, not just some artificial database
> events.
>
> This might look like nitpicking, but I believe that the methodology and
> the paradigms are extremely important as they define the logic we employ
> in our day to day routines. Allow me to recommend a completely
> non-database book, "The Goal", by Eli Goldratt, which is an "economic
> novel" about tuning a factory. This has been one of the most helpful books
> that I read in a long time.
>
> --
> http://www.mladen-gogala.com

Thanks for the clarification/correction of my comments, and the additional detail. I can't disagree with what you wrote.

My comments referring to tuning the database instance may not have been as clear as I had hoped. The intention was to imply that the database performance should be first tuned as a whole: change the size of the redo logs from 10MB to a size that forces log switches every 20 to 30 minutes (between 256MB and 512MB for my database), verify that there is minimal contention on the drives containing the redo logs, verify that the parameters in the initialization file are set to appropriate values, verify that the operating system for the database instance is correctly configured, etc. This will also likely include examination of the wait events for sessions that appear to be the greatest contributor to the system level wait events. The point at which to one should determine nothing more can be gained from this approach is hard to determine, the point at which one should switch to Cary Millsap's Method R (if I remember the name of his term right). "Hodge-podge" might be a good description of this method. It is easy to develop patterns at this stage: See a problem A, book M states to fix this by changing parameter Z, then measure the result of the change and hope that the performance is not worse.

My comment: "nothing more can be gained from looking at the wait interface" should have been further clarified, since, as you indicate, it implies that the wait interface plays no part in later tuning exercises, such as a 10046 trace. Something like this might have been better: "nothing significant can be gained from looking at the wait interface to tune at the database instance (whole database level)." That still is not clear enough.

My comments regarding Jonathan Lewis' book were a bit short. The 10053 trace that he describes in his book is very helpful when the 10046 trace does not provide enough information to help resolve the source of bottlenecks.

Mladen, great comments. I have heard of the "The Goal" book, and I hope to find time to read it one of these days. "The Toyota Way" is also a good book about tuning a factory, but I didn't much care for the way the book down-plays the importance of computer systems in achieving those goals. :-)

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Dec 26 2006 - 17:34:59 CST