sqlmon.sql

一个Oracle性能监视工具

set feedback off
set verify off
set serveroutput on
declare

t_number1 number :=0;
t_lines   number :=&1;
t_sid     number :=&2;

cursor c_usrsqls is
 select sqt.sorts
       ,sqt.disk_reads
       ,sqt.buffer_gets
       ,sqt.rows_processed
       ,sqt.executions
       ,sqt.first_load_time
       ,sqt.sharable_mem
       ,sqt.persistent_mem
       ,sqt.runtime_mem
       ,sqt.users_opening
       ,sqt.users_executing
   from v$sqlarea               sqt
       ,v$session               ses
  where ses.sql_address = sqt.address
    and ses.sql_hash_value = sqt.hash_value
    and ses.sid = t_sid;

cursor c_usrsqlt is
 select sqt.sql_text
       ,sqt.piece
   from v$sqltext_with_newlines sqt
       ,v$session               ses
  where ses.sql_address = sqt.address
    and ses.sql_hash_value = sqt.hash_value
    and ses.sid = t_sid
  order by 2;

begin
 dbms_output.enable(100000);

 for r_usrsqls in c_usrsqls 
 loop
   dbms_output.put_line('first_load_time='||
                         ltrim(r_usrsqls.first_load_time)
                       );
   dbms_output.put_line('sorts='||
                         ltrim(to_char(r_usrsqls.sorts,'990'))||
                        ',disk_reads='||
                         ltrim(to_char(r_usrsqls.disk_reads,'999999990'))||
                        ',buffer_gets='||
                         ltrim(to_char(r_usrsqls.buffer_gets,'999999990'))||
                        ',rows_processed='||
                         ltrim(to_char(r_usrsqls.rows_processed,'999999990'))
                       );
   dbms_output.put_line('executions='||
                         ltrim(to_char(r_usrsqls.executions,'9999990'))||
                        ',users_opening='||
                         ltrim(to_char(r_usrsqls.users_opening,'990'))||
                        ',users_executing='||
                         ltrim(to_char(r_usrsqls.users_executing,'990'))
                       );
   dbms_output.put_line('sharable_mem='||
                         ltrim(to_char(r_usrsqls.sharable_mem,'99990'))||
                        ',persistent_mem='||
                         ltrim(to_char(r_usrsqls.persistent_mem,'99990'))||
                        ',runtime_mem='||
                         ltrim(to_char(r_usrsqls.runtime_mem,'99990'))
                       );
 end loop;

 dbms_output.put_line('--------------------------------------------------------------------------');
 t_number1 := 0;
 for r_usrsqlt in c_usrsqlt 
 loop
   dbms_output.put_line(rpad(ltrim(r_usrsqlt.sql_text),64));
   t_number1 := t_number1 + 1;
   exit when t_number1 = t_lines;
 end loop;
end;
/