10g通过sql_id等可以很简单获得执行计划 不必要把整个sql text写上去

9i比较麻烦(若sql语句很长的话),今天看oracle trouble shooting performance 有个脚本 便于查9i 执行计划
特此记录

SQL>  SELECT address, hash_value, child_number, sql_text
  2      FROM v$sql
  3      WHERE sql_text LIKE '%&sql_text%' AND sql_text NOT LIKE '%v$sql%';
Enter value for sql_text: select * from dual
old   3:     WHERE sql_text LIKE '%&sql_text%' AND sql_text NOT LIKE '%v$sql%'
new   3:     WHERE sql_text LIKE '%select * from dual%' AND sql_text NOT LIKE '%v$sql%'

ADDRESS  HASH_VALUE CHILD_NUMBER
-------- ---------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
2FA78674  942515969            0
select * from dual

SQL> delete plan_table
  2  ;

3 rows deleted.

SQL> INSERT INTO plan_table ( operation, options,
  2                          object_node, object_owner, object_name, optimizer,
                        search_columns, id, parent_id, position, cost,
  3    4                          cardinality, bytes, other_tag, partition_start,
  5                          partition_stop, partition_id, other, distribution,
  6                          cpu_cost, io_cost, temp_space, access_predicates,
  7                          filter_predicates)
  8  SELECT
  9         operation, options, object_node, object_owner, object_name,
 10         optimizer, search_columns, id, parent_id, position, cost,
 11         cardinality, bytes, other_tag, partition_start, partition_stop,
 12         partition_id, other, distribution, cpu_cost, io_cost, temp_space,
 13         access_predicates, filter_predicates
 14  FROM v$sql_plan
 15  WHERE address = '2FA78674'
 16  AND hash_value = 942515969
 17  AND child_number = 0;

2 rows created.

SQL> select * from table(dbms_xplan.display);