www.pudn.com > oramon.zip > sqlmon.sql

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; 
/