JL Computer Consultancy

Cover 95% of your trouble-shooting in 3(.5) scripts.

Pre-August 1999

The Jonathan Lewis axioms of database tuning:



If you have got your database to run within 50% of optimum you've done incredibly well.



Identifying the problems after the system is in production is easy

Corollary 1 - Don't waste time chasing minor details, there may be important things to do.

Corollary 2 - Get a competent technician in for a couple of days early on to outline the worst traps and best options.

Once a system is in production there is little point in making a fuss about bad design and the things should have been done. Experience has taught me time and again that the biggest performance issues are introduced almost concurrently with the decision to use Oracle, and by the time that performance is actually seen to be a problem, both time and budget have run out. The only approach is to make the smallest number of cost-effective, low-risk changes that tone down the scale of the disaster.

Finding the best point of attack comes down to 2 very obvious checks, though these is a third option (which can run at two different levels) which may give some extra clues.

The first is simply (as the manual always said) the SQL that is being executed. v$sqlarea is a portion of the shared pool that allows you to see what has happened recently, how often it has happened, when it first occured, and how much it has cost to date. The possible problems are that each execution may be unreasonably expensive, or that the code is being executed too frequently. The script highlights both problems by listing the statements with the highest absolute cost, but also printing with each statement the cost per execution.

The second is based on the system I/O. Even if a suitable quantity of SQL is being executed, and the execution path is reasonable, it is possble that there is a physical data distribution on the data devices themselves. v$filestat reports for each data file the IO done, and the average I/O time for each file. The things to watch out for are - an extreme fraction of the I/O occurring on any one file; unreasonable times per I/O (I tend to take 2/100 of a second as a baseline), indications of large numbers of tablescans.

Finally, once the SQL and the I/O seem to be reasonably healthy, look at Oracle's internal wait list v$system_event, or in extreme cases v$session_event (code waiting to be uploaded) to find out if there is any more slack that can be taken up easily. It is not easy to make general-purpose comments about the figures you see in the event lists as so many of them are supposed to be large, and some are bound to get large if certain features of the application are true - nevertheless any event which happens frequently and whose cost is NOT close to a whole number of seconds is worth thinking about.

The three different scripts also highlight three approaches to using the v$ perfomance monitoring views. v$sqlarea is something you just have to dump from time to time. With v$filestat I have dumped the whole view regularly so that I can take differences at a later date. The code for handling v$system_event is designed to dump the change over a short time interval but leaves no trail for later processing. All three approaches are valid for different types of circumstances.

Back to Main Index of Topics