JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Appendix B: Tuning to 90%.


Addenda

V$enqueue_stat (7th May 2002)

Top command

 

Errata

 

Back to Book Index

 

V$enqueue_stat (7th May 2002)

I made the point in book (p.581) that it is too easy to get carried away playing about with X$ objects, but that there were a couple of items which really should be exposed to view. One of these was x$ksqst - which is a set of statistics about enqueue types and how often they have been used.

This object has now been enhanced, and exposed through the V$ view v$enqueue_stat in Oracle 9.0.1. The view now displays gets, waits, failures, and most significantly, the wait time for each type of enqueue. Consequently, when you see in v$session_event (or v$system_event) that a lot of time has been lost on the event enqueue then you have just a little more information which may pin down which type of enqueue is causing the problem.

Top of page

 

top command:

When reading this appendix late one night, I suddenly realised that I had failed to mention the first mechanical test I run when called to tune an Oracle-based system. This is to take a couple of minutes to run the top utility at a time when the system is behaving badly.

On most UNIX systems, top is a command-line utility that displays some information about current memory and CPU usage, and the busiest processes. Where top is not available, I use the local variant to produce a similar report (see Guilty Party in the monitoring and tuning section for a sample of top output).

There are a few safe inferences to be drawn very quckly from the typical output:

CPU is running at 100% and the Oracle processes are using most of it There is probably some overactive SQL doing far too many logical I/Os. The database can probably be tuned - It is, however, possible that you need to spend on hardware.
CPU is running at 100% and some 'homegrown' front-end process is using most of it. There is probably no point in trying to tune the database - the application as a whole can go no faster if the front-end is responsible for the work being done.
CPU is running at 100% and is shared ca. 50/50 between a front-end and the Oracle prcoesses. It is reasonably probable that the front-end is talking to Oracle using lots of very small dialogues - single row fetches etc. It may be necessary to redesign the application code. Tuning Oracle cannot do better than double the performance of the system.
CPU utilisation is low, and the system is showing many processes persistently blocked, and lots of 'waitio'. It is likely that Oracle is running some very inefficient SQL that forces excessive disk activity. The database can probably be tuned.
CPU utilisation is low, but the system is NOT giving any indication of waiting for I/O. It is likely that the way the application has been coded is generating excessive contention (e.g. waiting row locked rows, waiting for ITL slots). It may be possible to tune the problem out of the database, it may be necessary to redesign the application code..

Top of page


Errata

In the section on v$sql (p.570) I list a very simple example of an SQL statement that I use to get an idea of whether there is some inherently expensive SQL running on the system. I introduced this statement with the phrase: "The query I typically use is of the form:". This introduction is absolutely correct, the SQL is often something like the code that follows - but I usually manage to spot the typos when running it against a live system. There are two errors in the SQL (and my thanks to Wil Stott of CSC Computer Sciences Ltd for pointing them out).

	parsing_user should read parsing_user_id
	row_processed should read rows_processed.

There are two other items on this website about this type use of code - the Guilty Party note, and a more subtle query which tries to be a little clever about working out the relative significance of expensive queries. Contrary to the comment in my book, both of these queries (originally written many years ago) access v$sqlarea rather than v$sql. However, the style of the first approach should result in Oracle using the available pseudo-index, and the second is going to hammer the shared_pool whether you use v$sql or v$sqlarea. Times have changed, of course, and both these notes really ought to be updated to cater for changes and enhancements in the underlying Oracle implementation - one day I'll find the time.

Top of page


Back to Book Index