oracle 9i 獲取sql執行計劃(書寫長的sql)

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);

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-626167/，如需轉載，請註明出處，否則將追究法律責任。