Re: EXPLAIN PLAN

Hello.

There is some basic information about how to interpret the explain plan in both the Oracle DBA Handbook 7.3 and Oracle PL/SQL programming books I have. My guess is that the fullest coverage of it would occur in any of the Tuning books if you like to read the oraclepress series.

Basically, if you have your optimizer set to rule based, then the cost for any plan will be 0, for a cost to show you need to have the optimizer set to cost based. You also have to analyze the tables and indexes that are used in the SQL statement that you wish to generate the explain plan for.

You can use the dbms_utility package's analyze_schema procedure to analyze all objects in a schema at once.

execute dbms_utility.analyze_schema('<schema name>','COMPUTE');

As far as tricks go, it seems that if you use cost based optimization, the optimizer does a decent job of deciding which tables to join first, so the order in which the elements appear in your sql statment is not as vital as it would be using rule based.

Try to avoid full table scans by designing the proper indexes. Try to avoid sorting operations unless they are necessary. Joins are usually less expensive than sub-queries (nested selects). You can usually rewrite an SQL query to remove the sub-queries and replace them with joins.
Set operations are the fastest, so you may beable to use unions or intersects instead of a join or subquery in some cases.

You can experiment with different index types (b*tree, bit-map), different datamodels, and SQL syntax and see how the costs compare, trying to get the cheapest costs overall for all the query types in your application.

I hope this helps.

Sincerely,
Kenneth E. Murphy
Unverisity of Missouri - Columbia
Office of Social & Economic Data Analysis kenneth_at_oseda.missouri.edu

AScott <ascott_at_aol.com> wrote in article <19980210225301.RAA00558_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