ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 脚本:获得现有语句的执行计划

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

原创 Linux操作系统 作者:ljm0211 时间:2012-07-02 10:57:56 0 删除 编辑
$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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-14

  • 博文量
    272
  • 访问量
    437191