Re: EXPLAIN PLAN

First, there are no 'silver bullets' in database tuning. Ignore anyone who tries to tell you anything different to this.

Your database design (everything - tables, indexes, clusters, stored procedures, normalisation degree, etc., etc., etc.), the volume of data, the exact nature of the operations being performed, the server hardware, etc., etc., etc. *all* make a difference.

So, where do you start! :-)

I suggest you look at the Oracle7 (or 8) Server Tuning Guide, which should have come with your database software.

If you want to pay from some extra books (which I believe you should, as you are new to this area of Oracle) look at the 'Oracle Press' series of books, some of which are specifically related to tuning. Ditto the O'Reilly Oracle books.

The *best* Oracle SQL tuning book *IMHO* is Guy Harrison's 'Oracle SQL High-Performance Tuning' by Prentice Hall. Check it out.

Oh, BTW, like Harrison notes, don't overlook SQL_TRACE/tkprof, they will give you more detailed output than just EXPLAIN PLAN (which they can also produce).

You might also want to look at more 'friendly' tools like SQLab, etc.

Hope the above helps. Let me know if you need more info.

Steve Phelan.

Ps.

I just finished tuning a critical piece of SQL for a client (running against a multi-million row data warehouse) and managed to get a 16 times perfomance improvement after just a couple of hours work. In other words, the time you spend reading the above books won't be wasted! :-)

AScott wrote in message <19980210225100.RAA00243_at_ladder03.news.aol.com>...
>Can anyone suggest a rough guide to the Explain Plan
>output. I have tried looking through the Oracle manuals but has usual
>they do not seem to describe what you should be looking for in your
>retrieval path. i.e. hints and cheats and best ways to achieve
>
>Cheers
Received on Wed Feb 11 1998 - 00:00:00 CST