Oracle tuning: Using explain plan

In order to make queries run faster, you should try to have oracle explain to you how the query should be performed.

The whole explanation for oracle tuning can be found on the Oracle Tuning guide, but here are the basics. First, you need a PLAN_TABLE:

CREATE TABLE plan_table (
    statement_id     VARCHAR2(30),
    timestamp        DATE,
    remarks          VARCHAR2(80),
    operation        VARCHAR2(30),
    options          VARCHAR2(30),
    object_node      VARCHAR2(128),
    object_owner     VARCHAR2(30),
    object_name      VARCHAR2(30),
    object_instance  NUMERIC,
    object_type      VARCHAR2(30),
    optimizer        VARCHAR2(255),
    search_columns   NUMERIC,
    id               NUMERIC,
    parent_id        NUMERIC,
    position         NUMERIC,
    cost             NUMERIC,
    cardinality      NUMERIC,
    bytes            NUMERIC,
    other_tag        VARCHAR2(255)
    other            LONG );

Next, you need to output the SQL

EXPLAIN PLAN SET STATEMENT_ID = 'MyPlan' FOR SELECT * FROM myTable WHERE myName LIKE 'Dude%';

SELECT * FROM PLAN_TABLE WHERE STATEMENT_ID = 'MyPlan'

Now look at the output. Take a special look at the cost and any full table scans.

Full table scans may or may not be a problem (depending on the size of the table).

Note: If using TOAD, you only need to switch to the “Explain” tab. You can set the name of the plan table to use by using View->Options->Oracle->Explain Table Name. You will probably need to do this since it defaults to TOAD_PLAN_TABLE..