批量查看当前运行sql的执行计划脚本

来源：互联网发布：淘宝旺铺模板管理在哪编辑：程序博客网时间：2024/06/16 01:13

cat sql.sh

a=$1

sqlplus -s zabbix/zabbix@$a <<!!
declare
   text long ;
   sql_str   long ;
begin
for i in (
   select     c.username,
      a.sql_id,
      a.ELAPSED_SECONDS,
          b.SQL_FULLTEXT
from v\$session_longops a, v\$sqlarea b,v\$session c
        where a.sql_id = b.sql_id
   and a.sql_hash_value = b.hash_value
   and a.target not like '%SYSTEM%'
   and a.ELAPSED_SECONDS > 10
   and a.sid = c.sid
   and a.SERIAL#= c.SERIAL#
   and c.username not in ( 'SYS')
   and rownum < 10 )
   loop
    dbms_output.put_line(to_char(i.SQL_FULLTEXT)) ;
    dbms_output.put_line('   ') ;
    execute immediate 'alter session set current_schema='||i.username;
    execute immediate 'explain plan for '||i.SQL_FULLTEXT;
    dbms_output.put_line('username='||i.username||' sql_id='||i.sql_id||' elapsed_seconds='||i.ELAPSED_SECONDS);
     for t in ( select PLAN_TABLE_OUTPUT from table(dbms_xplan.display ))
     loop
       dbms_output.put_line(to_char(t.PLAN_TABLE_OUTPUT)) ;
     end loop ;

    dbms_output.put_line('############################################################################################################################################');

   end loop ;
EXCEPTION WHEN OTHERS THEN
raise ;

end ;
/

有错误请指出，谢谢！

0 0