$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博客

脚本：获得现有语句的执行计划

最新文章