Top SQL .... Buffer Gets
prompt PARSING_SCHEMA_NAME='SCOTT' <<<<<<<<<<--------------
declare
v_sql_id varchar2(20);
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select sql_id,buffer_gets,substr(sql_text,1,4000)
from v$sqlarea
where PARSING_SCHEMA_NAME='SCOTT'
order by buffer_gets desc;
begin
dbms_output.put_line('sql_id '||' '||' Reads'||' '||' Text');
dbms_output.put_line ('-----'||'-----'||' '||'---------------------------------------------------');
dbms_output.put_line(' ');
open c1;
for i in 1 .. 10 loop
dbms_output.put_line(' ');
fetch c1 into v_sql_id,top5, text1;
dbms_output.put_line(v_sql_id||' '||rpad(to_char(top5),9)|| ' '||substr(text1,1,66));
len1 :=length(text1);
x := 66;
while len1 > x-1 loop
dbms_output.put_line('" '||substr(text1,x,64));
x := x+64;
end loop;
end loop;
end;
/
Query to get the 25 worst memory abusers
ReplyDeleteprompt PARSING_SCHEMA_NAME='SCOTT' <<<<<<<<<<--------------
set serveroutput on
declare
v_INST_ID number ;
v_sql_id varchar2(20);
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select inst_id,sql_id,buffer_gets,substr(sql_text,1,4000) from gv$sqlarea
where 1=1
--and PARSING_SCHEMA_NAME='SCOTT'
order by buffer_gets desc;
begin
dbms_output.put_line('INST_ID'||' '||'sql_id '||' '||' Reads'||' '||' Text');
dbms_output.put_line ('------'||' '||'-----' ||'----- '||' '||'---------------------------------------------------');
dbms_output.put_line(' ');
open c1;
for i in 1 .. 25 loop
dbms_output.put_line(' ');
fetch c1 into v_INST_ID,v_sql_id,top5, text1;
dbms_output.put_line(v_INST_ID||' '||v_sql_id||' '||rpad(to_char(top5),9)|| ' '||substr(text1,1,66));
len1 :=length(text1);
x := 66;
while len1 > x-1 loop
dbms_output.put_line('" '||substr(text1,x,64));
x := x+64;
end loop;
end loop;
end;
/