指令碼：獲得現有語句的執行計劃

ljm0211發表於2012-07-02
指令碼
$more get_plan_by_hash.sh
#!/bin/ksh

sqlplus -s /nolog <connect user/pass
set lines 1000
set pages 1000
set feedback off
set trimspool on
col sql_text format a80

--先根據hash值獲得SQL語句
select sql_text from
v\$sqltext_with_newlines
where hash_value=$1
order by piece;

--得到這個語句的執行計劃
prompt
prompt execute plan;
set serveroutput on size 1000000
begin
dbms_output.put_line('--------------------------------------------------------------------------------');
for i in (select rpad('|'||substr(lpad(' ',1 * (depth-1))||operation||
decode(options, null,'',' '||options), 1, 32), 33, ' ')||'|'||
rpad(decode(id, 0, '----- '||to_char(hash_value)||' -----',
substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null,
object_name)||' ',1, 20)), 21, ' ')||'|'||
lpad(decode(cardinality,null,'  ',decode(sign(cardinality-1000),
-1, cardinality||' ',decode(sign(cardinality-1000000), -1,
trunc(cardinality/1000)||'K',decode(sign(cardinality-1000000000), -1,
trunc(cardinality/1000000)||'M',trunc(cardinality/1000000000)||'G')))),
7, ' ') || '|' ||
lpad(decode(bytes,null,' ',decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576),
-1, trunc(bytes/1024)||'K',decode(sign(bytes-1073741824),
-1, trunc(bytes/1048576)||'M',trunc(bytes/1073741824)||'G')))), 6, ' ')
|| '|' ||
lpad(decode(cost,null,' ',decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as Explain_plan
from v\$sql_plan
where hash_value = $1
and child_number = (select max(child_number) from v\$sql_plan where
hash_value = $1))
loop
dbms_output.put_line(i.Explain_plan);
end loop;
dbms_output.put_line('--------------------------------------------------------------------------------');
end;
/
exit
EOF

呼叫方法：
$./get_plan_by_hash.sh 861426306

861426306 為執行過的SQL語句的hash值。
這個語句是已經執行過的語句，其Hash值在動態檢視中才有可能存在。

如果是新的SQL語句，動態檢視中還沒有這樣的資訊存在，建議在SQL Plus中採用：
SQL> set autotrace on explain
的方式獲得，或者採用如下方式獲得：
set autot off
set feedback on
set lines 120
set pages 999

explain plan for (SQL語句);

select * from table(dbms_xplan.display);

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

相關文章