TOP 10 SQL QUERIES

 The top 10 sql queries which consuming high IO, CPU's. in oracle db. 
 I am getting the sql query by applying the hash value in v$sqlarea. The queries are below.
Really it is very helpful for daily activity.
1.Top sql command:

SQL> select c.* from
       (select disk_reads,
          buffer_gets,
                   rows_processed,
               executions,
               first_load_time,
          sql_text
              from v$sqlarea
          where parsing_user_id !=0
          order by
             buffer_gets/decode(executions,null,1,0,1,executions) desc ) c
   where rownum < 11;
  
2..--Top 10 by Buffer Gets:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        buffer_gets, executions, buffer_gets/executions "Gets/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE buffer_gets > 10000
 ORDER BY buffer_gets DESC)
WHERE rownum <= 10
;
3...-Top 10 by Physical Reads:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        disk_reads, executions, disk_reads/executions "Reads/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE disk_reads > 1000
 ORDER BY disk_reads DESC)
WHERE rownum <= 10;

4. Top 10 by Executions:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        executions, rows_processed, rows_processed/executions "Rows/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE executions > 100
 ORDER BY executions DESC)
WHERE rownum <= 10;
  
5. Top 10 by Parse Calls:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        sharable_mem, executions, hash_value,address
   FROM V$SQLAREA
  WHERE sharable_mem > 1048576
 ORDER BY sharable_mem DESC)
WHERE rownum <= 10;
6. Top 10 by Version Count:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        version_count, executions, hash_value,address
   FROM V$SQLAREA
  WHERE version_count > 20
 ORDER BY version_count DESC)
WHERE rownum <= 10;

7. Long running sql:

set lines 200
col opname for a35
col progress for a15
col progress for a8
col TARGET for a45
col USERNAME for a8
select a.sid,a.serial#,b.username,b.opname,LAST_UPDATE_TIME,round(b.SOFAR*100 / b.TOTALWORK,2) || '%' as progress,
b.TIME_REMAINING,b.target from gV$SESSION_LONGOPS b,gV$SESSION a where a.sid=b.sid and TIME_REMAINING <> 0 order by 6;

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
set lines 200
set pages 200
col opname for a35
col progress for a15
col progress for a8
col TARGET for a45
select distinct a.inst_id,a.sid,a.serial#,b.username,b.opname,a.logon_time,round(b.SOFAR*100 / b.TOTALWORK,2) || '%' as progress,
b.TIME_REMAINING,b.target from gV$SESSION_LONGOPS b,gV$SESSION a where a.inst_id=b.inst_id and a.sid=b.sid and TIME_REMAINING <> 0 order by a.inst_id,logon_time  ;