Re: oracle performance

rawass wrote:
> Dear All,
>
> I need to know what are in general the steps that I have to follow in
> order to diagnose the reasons behind a performance problem on oracle
> dtabases? Please if you can detail each step in your answer.
>
> Best Regards

Excellent tips provided in this thread. I agree with the book recommendations listed, all are excellent, and all focus on different levels of performance tuning.

Ask yourself this question: how do you know that there is a performance problem in the Oracle database? Tuning database applications requires identifying the performance bottleneck, and eliminating it, or reducing it so that something else then becomes the bottleneck. The bottleneck could be on the server: Oracle not told to use the available memory, incorrect initialization parameters, wrong RAID type, congestion on writing the redo logs, contention with other sessions, server CPU at 100% utilization, table and index statistics out of date, etc. The bottleneck could be on the network equipment: server network card is failing, bad or inefficient network card drivers in the server, inefficient TCP/IP stack in the operating system on the server, bad network cabling, failing switch or hub equipment, client network card is failing, bad or inefficient network card drivers in the client, inefficient TCP/IP stack in the operating system on the client, high latency of network packets between the server and client. The bottleneck could be on the client: viruses and spyware installed, client CPU at 100% utilization, contention with other programs running on the client. The bottleneck could be in the application software that is accessing the database: inefficient single SQL statements, inefficient processing of SQL statements - results of one SQL statement causes another to be executed and its results cause another to be executed - rather than executing a single SQL statement, writing results of SQL statements to temp files on the client and then reading from those temp files, committing 1000 times when once would be sufficient, etc.

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.

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. The output of this trace allows you to see the various SQL statements executed, the delays (wait events) associated with each SQL statement, and the severity of each delay. Cary Millsap's book describes in detail how to initiate such a trace and how to decode the resulting log. The benefit of using the 10046 trace file is that it also allows you to examine the otherwise meaningless client side wait events. As I was still not satisfied with the run time of the one previously mentioned database application, now that its run time is exceeding an hour due to a corresponding increase in the amount of data being processed, I performed a 10046 trace on the session. I found that roughly 57 minutes of the time was spent on the wait event SQL*Net message from client, about 3 minutes on db file sequential read, and about 2.5 minutes on log file sync, with the rest of the wait events accumulating to fractions of a couple seconds. With the help of Cary Millsap's book, I found that of the 60+ minutes of run time, the database server accounted for about 6 minutes of that time. This then told me that the database application was likely CPU bound on the client. I moved the database application to a different client computer, from one with dual 3+ GHz Xeon processors and 1GB of memory to one with a single 3.8 GHz Xeon processor, 2MB secondary cache, and 2GB of memory. The run time of the database application dropped to a bit over 55 minutes, and the client CPU was still pegged for a good part of the run. What if, the 6 minutes of database server time were removed from the equation? So, I replace the database server that was apparently having delays associated with index lookups and writing of the redo logs due to excessive commits (the server was replaced for other reasons, but for the sake of argument we will assume that the server was replaced to eliminate the 6 minute delay). Now the run time of the database application required roughly 45 minutes to complete, but was still taking too long. Many of the database applications that I wrote were executing 20 times faster against the new database server, yet this database application only executes 10% to 20% faster?

Time to determine what is causing the bottleneck on the client: Network packet traces with Ethereal/Wireshark showed odd activity looking for a translation library on a server, file system activity with FileMon showed writing database results to temp files, and examination of V$SESSTAT and V$SESSION_EVENT in one minute intervals showed an unbelievable number of commits per second, round trips between client and server per second, and parse/fetch/executes per second. In the end I was forced to conclude that due to the design of this packaged, off the shelf database application, just was not efficient.

Jonathan Lewis' "Cost-Based Oracle Fundamentals" is an excellent book that will help you understand what is going on under the covers of Oracle, as it processes SQL statements. While reading the book, you may find yourself shouting out loud - "Ohhh, that's why that happens." This was the first book about Oracle databases that I read that resulted in the above shouting.

The performance tuning guide for Oracle 10.2 is a good reference also.

When performance tuning, it is important to identify and eliminate bottlenecks. As in the above case, it sometimes comes down to poor design decisions on the part of application developers.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Oct 28 2006 - 18:14:08 CDT