/* ************************************************ */
/*                                                  */
/* fnctn:   Sessions With The Highest I/O           */
/*                                                  */
/* name:    @c:\oracle\sql\max_read.sql             */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */

create or replace procedure max_lreads(
crt_num IN number) as
sql_text varchar2(64);

session_sid	number;
session_serial	number;
session_username	varchar2(30);
session_value    number; 
session_address raw(4);
session_hash_value number;
session_status varchar2(20);
process_id  number;
unix_id   varchar2(30);

cursor c1 is
select sql_text
from v$sqltext
where  address = session_address
and    hash_value = session_hash_value
order by piece;

cursor c2 is
select b.sid,b.serial#,b.username,a.value+c.value values_col,
       b.sql_address,b.sql_hash_value,b.status,
       d.pid,d.username
from  v$sesstat a,v$session b,v$sesstat c, 
      v$process d
where a.statistic# = 38
and c.statistic# = 37
and b.sid=a.sid
and b.sid=c.sid
and b.paddr = d.addr
and b.username is not null
order by values_col desc;

begin
open c2;
dbms_output.enable(1000000);
dbms_output.put_line('                  Sessions With The Highest I/O ');
dbms_output.put_line('                  =============================');
dbms_output.put_line(' ');
for ctr in 1..CRT_NUM loop
dbms_output.put_line('******************************************************************************');
     fetch c2 into session_sid,session_serial,session_username,session_value,
                   session_address,session_hash_value,session_status,
                   process_id,unix_id;
     dbms_output.put_line('Session Id     : '||to_char(session_sid)||'  Process Id  : '||process_id);
     dbms_output.put_line('Serial Number  : '||to_char(session_serial));
     dbms_output.put_line('ID             : '||session_username||'   Status: '||session_status);
      Declare
        session_actual_name varchar2(30);
      BEGIN
         select b.user_name
         into session_actual_name
         from applsys.fnd_logins a, applsys.fnd_user b
         where a.pid= process_id 
         and a.user_id = b.user_id
         and a.login_name = unix_id
         and to_char(a.start_time,'DD-MON-YY') =
           to_date(sysdate,'DD-MON-YY');
     dbms_output.put_line('UserName       : '||session_actual_name||' Unix Id: '||unix_id);
     EXCEPTION
        WHEN NO_DATA_FOUND then NULL;
        WHEN TOO_MANY_ROWS THEN NULL;
     END;
        
     dbms_output.put_line('Logical Reads  : '||to_char(session_value,'999,999,990'));
     dbms_output.put_line('******************************************************************************');
                            
     open c1;
     loop
        fetch c1 into sql_text;
        if c1%NOTFOUND then
           if c1%ROWCOUNT=0 then
               dbms_output.put_line('No SqlText Found...');
           end if;
           exit ;
        else
            dbms_output.put_line(lower(sql_text));
        end if;
     end loop;
     close c1;
      dbms_output.put_line('-----------------------------------------------------------------------------');
end loop;
close c2;
end;
/
