RSS Feed

Method R Discussion Group

Hi Cary,

Please correct me if I am wrong.
We Use the EXPLAIN PLAN statement to determine the execution plan Oracle Database follows to execute a specified SQL statement. This statement inserts a row describing each step of the execution plan into a specified table. We use v$sql_plan view to see the execution plan Oracle Database HAS followed to execute a specified SQL statement. What is the significance of using EXPLAIN PLAN over not using v$sql_plan view? As most of of the time I saw people use EXPLAIN PLAN, even many experienced people do the same and recommend the same.
If I am correct above, then isn’t is better to have realistic information (which plan Oracle database has followed) than going for more theoretical approach suing EXPLAIN PLAN?

Suraj,

The biggest benefit of using the EXPLAIN PLAN command is not actually having to run the SQL statement that you want to test. That can save you a lot of time when you are testing a SQL statement than can run for hours.

There are cases where the predicted execution plan is different than the actual execution plan. In many cases this is caused by a data type mismatch (i.e. comparing a number to a string) which highlights a data design issue in the application.

Another issue with V$SQL_PLAN is that the execution plan that you are interested in may have aged out when you want to retrieve it.

Can’t we try a procedure like below, which will provide the execution plan directly from your v$sql_plan and give you the output in the explain plan format and moreover it will tell you how this sql was executed rather than how it will going to execute:

create or replace procedure
  explain_plan(hash_value number)
is begin
  insert into plan_table
  select
    null,
    (select nvl(max(plan_id),0)+1 from plan_table),
  null,
    remarks,
    operation,
    options,
    object_node,
    object_owner,
    object_name,
    object_alias,
    null,
    object_type,
    optimizer,
    search_columns,
    id,
    parent_id,
    depth,
    position,
    cost,
    cardinality,
    bytes,
    other_tag,
    partition_start,
    partition_stop,
    partition_id,
    other,
    distribution,
    cpu_cost,
    io_cost,
    temp_space,
    access_predicates,
    filter_predicates,
    projection,
    time,
    qblock_name
  from v$sql_plan
  where hash_value=explain_plan.hash_value;
  for f in (
    select PLAN_TABLE_OUTPUT from table(dbms_xplan.DISPLAY))
  loop
    dbms_output.put_line(f.PLAN_TABLE_OUTPUT);
  end loop;
end;
/
This procedure is for Oracle 10.1.0.2.0, but can modify it according to the versions of Oracle.

The way to use this procedure is
exec explain_plan (<hash_value of the sql>)