Re: oracle performance

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