explain_plan.sql 584 Bytes
Newer Older
O'Reilly Media, Inc. committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
-- The SQL*Plus script explain_plan.sql displays the
-- execution plan for the specified statement id.

UNDEFINE 1;

SELECT
  DECODE(id, 0, '', LPAD(' ', 2*(level - 1)) ||
  level || '.' || position) || ' ' ||
  operation || ' ' ||
  options || ' ' ||
  object_name || ' ' ||
  object_type || ' ' ||
  DECODE(optimizer, NULL, '', 'Optimizer = ' || optimizer) || ' ' ||
  DECODE(cost, NULL, '', 'Cost = ' || position)
AS execution_plan
FROM
  plan_table
CONNECT BY PRIOR
  id = parent_id
AND
  statement_id = '&&1'
START WITH
  id = 0
AND
  statement_id = '&1';