oracle top 20 sql for the particular schema

SET LINESIZE 500  PAGESIZE 1000  FEEDBACK OFF  VERIFY OFF  SERVEROUTPUT ON
DECLARE

CURSOR c_sql IS
SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
-- a.address
a.sql_id
FROM gv$sqlarea a
where PARSING_SCHEMA_NAME='OS' ---<<<<<<<<<<<<<<
ORDER BY 2 DESC;

BEGIN

Dbms_Output.Enable(1000000);

Dbms_Output.Put_Line(Rpad('SQL Text',50,' ') ||
Lpad('Reads/Execution',16,' ') ||
Lpad('Buffer Gets',12,' ') ||
Lpad('Disk Reads',12,' ') ||
Lpad('Executions',12,' ') ||
Lpad('Sorts',12,' ') ||
-- Lpad('Address',10,' '));
Lpad('sql_id',10,' '));
Dbms_Output.Put_Line(Rpad('-',50,'-') || ' ' ||
Lpad('-',15,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',9,'-'));

<< top_sql >>
FOR cur_rec IN c_sql LOOP
Dbms_Output.Put_Line(Rpad(cur_rec.sql_text,50,' ') ||
Lpad(cur_rec.reads_per_execution,16,' ') ||
Lpad(cur_rec.buffer_gets,12,' ') ||
Lpad(cur_rec.disk_reads,12,' ') ||
Lpad(cur_rec.executions,12,' ') ||
lpad(cur_rec.sorts,12,' ') ||' '||
-- Lpad(cur_rec.address,10,' '));
Lpad(cur_rec.sql_id,12,' '));

-- IF c_sql%ROWCOUNT = 1 THEN
IF c_sql%ROWCOUNT = 20 THEN
EXIT top_sql;
END IF;
END LOOP;

END;
/




SQL Text                                           Reads/Execution Buffer Gets  Disk Reads  Executions       Sorts    sql_id
-------------------------------------------------- --------------- ----------- ----------- ----------- ----------- ---------
/* SQL Analyze(0) */ select /*+  full(t)    parall       398138061  1241785047  1194414183           3           0 c45yd6m5kjfc
/* SQL Analyze(0) */ select /*+  full(t)    parall       130386544   536003895   521546176           4           0 cn6j67uy5wtu




-- With address 

SET LINESIZE 500  PAGESIZE 1000  FEEDBACK OFF  VERIFY OFF  SERVEROUTPUT ON
DECLARE

CURSOR c_sql IS
SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address,
a.sql_id
FROM gv$sqlarea a
where PARSING_SCHEMA_NAME='OS' ---<<<<<<<<<<<<<<
ORDER BY 2 DESC;

BEGIN

Dbms_Output.Enable(1000000);

Dbms_Output.Put_Line(Rpad('SQL Text',50,' ') ||
Lpad('Reads/Execution',16,' ') ||
Lpad('Buffer Gets',12,' ') ||
Lpad('Disk Reads',12,' ') ||
Lpad('Executions',12,' ') ||
Lpad('Sorts',12,' ') ||
Lpad('Address',10,' ')||
Lpad('sql_id',10,' '));
Dbms_Output.Put_Line(Rpad('-',50,'-') || ' ' ||
Lpad('-',15,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',9,'-'));

<< top_sql >>
FOR cur_rec IN c_sql LOOP
Dbms_Output.Put_Line(Rpad(cur_rec.sql_text,50,' ') ||
Lpad(cur_rec.reads_per_execution,16,' ') ||
Lpad(cur_rec.buffer_gets,12,' ') ||
Lpad(cur_rec.disk_reads,12,' ') ||
Lpad(cur_rec.executions,12,' ') ||
lpad(cur_rec.sorts,12,' ') ||' '||
Lpad(cur_rec.address,10,' ')||' '||
Lpad(cur_rec.sql_id,12,' '));

-- IF c_sql%ROWCOUNT = 1 THEN
IF c_sql%ROWCOUNT = 20 THEN
EXIT top_sql;
END IF;
END LOOP;

END;
/