set serverout on
set pages 200
alter session set nls_date_format = 'dd-mon-yy hh24:mi:ss';
select sysdate from dual;

prompt
prompt Queries ordered by disk read rate...
prompt
declare
  cursor c1 is select HASH_VALUE, 
		      EXECUTIONS, 
		      DISK_READS, 
		      BUFFER_GETS, 
		      DISK_READS/EXECUTIONS read_rate, 
		      USERNAME 
	       from v$sqlarea a, sys.dba_users b 
	       where PARSING_USER_ID = user_id 
	       and   DISK_READS > 1000000 order by 5 desc;

  v_HASH_VALUE NUMBER;
  v_EXECUTIONS NUMBER;
  v_DISK_READS NUMBER;
  v_BUFFER_GETS NUMBER;
  v_read_rate NUMBER;
  v_USERNAME dba_users.USERNAME%TYPE;
  v_count NUMBER := 10;
begin
  open c1;
  dbms_output.put_line(rpad('HASH VALUE',15)||rpad('EXECUTIONS',12)||rpad('DISK READS',12)||rpad('BUFFER GETS',13)||rpad('READ RATE',11)||rpad('USERNAME',12));
  for i in 1..v_count loop
    fetch c1 into v_HASH_VALUE, v_EXECUTIONS, v_DISK_READS, v_BUFFER_GETS, v_read_rate, v_USERNAME;
    dbms_output.put_line(rpad(to_char(v_HASH_VALUE), 15)||rpad(to_char(v_EXECUTIONS), 12)||rpad(to_char(v_DISK_READS), 12)||rpad(to_char(v_BUFFER_GETS), 13)||rpad(to_char(trunc(v_read_rate)),11)||rpad(v_USERNAME,12));
  end loop;
  close c1;
end;
/

prompt
prompt
prompt
prompt Queries ordered by buffers got rate...
declare
  cursor c1 is select HASH_VALUE,
                      EXECUTIONS,
                      DISK_READS,
                      BUFFER_GETS,
                      BUFFER_GETS/EXECUTIONS read_rate,
                      USERNAME
               from v$sqlarea a, sys.dba_users b
               where PARSING_USER_ID = user_id
               and   DISK_READS > 1000000 order by 5 desc;

  v_HASH_VALUE NUMBER;
  v_EXECUTIONS NUMBER;
  v_DISK_READS NUMBER;
  v_BUFFER_GETS NUMBER;
  v_read_rate NUMBER;
  v_USERNAME dba_users.USERNAME%TYPE;
  v_count NUMBER := 10;
begin
  open c1;
  dbms_output.put_line(rpad('HASH VALUE',15)||rpad('EXECUTIONS',12)||rpad('DISK READS',12)||rpad('BUFFER GETS',13)||rpad('READ RATE',11)||rpad('USERNAME',12));
  for i in 1..v_count loop
    fetch c1 into v_HASH_VALUE, v_EXECUTIONS, v_DISK_READS, v_BUFFER_GETS, v_read_rate, v_USERNAME;
    dbms_output.put_line(rpad(to_char(v_HASH_VALUE), 15)||rpad(to_char(v_EXECUTIONS), 12)||rpad(to_char(v_DISK_READS), 12)||rpad(to_char(v_BUFFER_GETS), 13)||rpad(to_char(trunc(v_read_rate)),11)||rpad(v_USERNAME,12));
  end loop;
  close c1;
end;
/

prompt
prompt
prompt
prompt Queries ordered by buffers got...
prompt
declare
  cursor c1 is select HASH_VALUE,
                      EXECUTIONS,
                      DISK_READS,
                      BUFFER_GETS,
                      BUFFER_GETS/EXECUTIONS read_rate,
                      USERNAME
               from v$sqlarea a, sys.dba_users b
               where PARSING_USER_ID = user_id
               and   DISK_READS > 1000000 order by 4 desc;

  v_HASH_VALUE NUMBER;
  v_EXECUTIONS NUMBER;
  v_DISK_READS NUMBER;
  v_BUFFER_GETS NUMBER;
  v_read_rate NUMBER;
  v_USERNAME dba_users.USERNAME%TYPE;
  v_count NUMBER := 10;
begin
  open c1;
  dbms_output.put_line(rpad('HASH VALUE',15)||rpad('EXECUTIONS',12)||rpad('DISK READS',12)||rpad('BUFFER GETS',13)||rpad('READ RATE',11)||rpad('USERNAME',12));
  for i in 1..v_count loop
    fetch c1 into v_HASH_VALUE, v_EXECUTIONS, v_DISK_READS, v_BUFFER_GETS, v_read_rate, v_USERNAME;
    dbms_output.put_line(rpad(to_char(v_HASH_VALUE), 15)||rpad(to_char(v_EXECUTIONS), 12)||rpad(to_char(v_DISK_READS), 12)||rpad(to_char(v_BUFFER_GETS), 13)||rpad(to_char(trunc(v_read_rate)),11)||rpad(v_USERNAME,12));
  end loop;
  close c1;
end;
/

prompt
prompt
prompt
prompt Queries ordered by disk reads...
declare
  cursor c1 is select HASH_VALUE,
                      EXECUTIONS,
                      DISK_READS,
                      BUFFER_GETS,
                      DISK_READS/EXECUTIONS read_rate,
                      USERNAME
               from v$sqlarea a, sys.dba_users b
               where PARSING_USER_ID = user_id
               and   DISK_READS > 1000000 order by 3 desc;

  v_HASH_VALUE NUMBER;
  v_EXECUTIONS NUMBER;
  v_DISK_READS NUMBER;
  v_BUFFER_GETS NUMBER;
  v_read_rate NUMBER;
  v_USERNAME dba_users.USERNAME%TYPE;
  v_count NUMBER := 10;
begin
  open c1;
  dbms_output.put_line(rpad('HASH VALUE',15)||rpad('EXECUTIONS',12)||rpad('DISK READS',12)||rpad('BUFFER GETS',13)||rpad('READ RATE',11)||rpad('USERNAME',12));
  for i in 1..v_count loop
    fetch c1 into v_HASH_VALUE, v_EXECUTIONS, v_DISK_READS, v_BUFFER_GETS, v_read_rate, v_USERNAME;
    dbms_output.put_line(rpad(to_char(v_HASH_VALUE), 15)||rpad(to_char(v_EXECUTIONS), 12)||rpad(to_char(v_DISK_READS), 12)||rpad(to_char(v_BUFFER_GETS), 13)||rpad(to_char(trunc(v_read_rate)),11)||rpad(v_USERNAME,12));
  end loop;
  close c1;
end;
/

prompt
prompt
prompt
prompt Queries ordered by normalized disk reads...
declare
  cursor c1 is select HASH_VALUE,
                      EXECUTIONS,
                      (DISK_READS*5) + BUFFER_GETS READS,
                      ((DISK_READS*5) + BUFFER_GETS)/EXECUTIONS read_rate,
                      USERNAME
               from v$sqlarea a, sys.dba_users b
               where PARSING_USER_ID = user_id
               and   DISK_READS > 1000000 order by 3 desc;

  v_HASH_VALUE NUMBER;
  v_EXECUTIONS NUMBER;
  v_READS NUMBER;
  v_read_rate NUMBER;
  v_USERNAME dba_users.USERNAME%TYPE;
  v_count NUMBER := 10;
begin
  open c1;
  dbms_output.put_line(rpad('HASH VALUE',15)||rpad('EXECUTIONS',12)||rpad('DISK READS',12)||rpad('READ RATE',11)||rpad('USERNAME',12));
  for i in 1..v_count loop
    fetch c1 into v_HASH_VALUE, v_EXECUTIONS, v_READS, v_read_rate, v_USERNAME;
    dbms_output.put_line(rpad(to_char(v_HASH_VALUE), 15)||rpad(to_char(v_EXECUTIONS), 12)||rpad(to_char(v_READS), 12)||rpad(to_char(trunc(v_read_rate)),11)||rpad(v_USERNAME,12));
  end loop;
  close c1;
end;
/
exit
