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
