Top SQLs in Oracle Database session

Finding Top rated SQL's is the key process while working on Database performance. Sure we can find these SQL's using AWR report but still we should know which view to access to find Top rated SQL's. PFB SQL's:-

1. TOP 10 SQL STATEMENT WITH LARGE NO. OF DISK READS :-

set lin 400
column "SQL_TEXT" format a100
column EXECUTIONS format 9999999
column DISK_READS format 999999999
column USER FORMAT a10
select substr(sql_text,0,100) as "SQL_TEXT",PARSING_SCHEMA_NAME as "USER",executions ,disk_reads from v$sqlarea  where decode(executions ,0,disk_reads,disk_reads/executions)
> (select avg(decode(executions,0,disk_reads,disk_reads/executions))  + stddev(decode(executions,0,disk_reads,disk_reads/executions))
from v$sqlarea) and ROWNUM < 11 order by disk_reads desc
/

2. TOP 10 SQL STATEMENT WITH FULL TABLE SCANS :-

column "SQL_TEXT" format a100
column OPERATION format a12
column OPTIONS format a12
column USER FORMAT a10
select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",p.operation,p.options from v$sqlarea t, v$sql_plan p where t.hash_value=p.hash_value and p.operation='TABLE ACCESS'
and p.options='FULL' and p.object_owner not in ('SYS','SYSTEM') and ROWNUM < 11 order by DISK_READS DESC, EXECUTIONS DESC
/

3. TOP 10 SQL STATEMENT WITH MOST CPU UTILIZATION :-

column "SQL_TEXT" format a100
column EXECUTIONS format 9999999
column CPU_TIME format 999999999
column USER FORMAT a10
select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.CPU_TIME from v$sqlarea t where ROWNUM < 11 order by CPU_TIME DESC,EXECUTIONS DESC
/

4. TOP 10 SQL STATEMENT WITH MOST BUFFER GETS :-

column SQL_TEXT format a100
column EXECUTIONS format 9999999
column BUFFER_GETS format 999999999
column USER FORMAT a10
select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.BUFFER_GETS from v$sqlarea t where ROWNUM < 11 order by BUFFER_GETS DESC,EXECUTIONS DESC
/

5. TOP 10 SQL STATEMENT WITH MOST NO. OF EXECUTIONS :-

column SQL_TEXT format a100

column EXECUTIONS format 9999999

column USER FORMAT a10

select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS from v$sqlarea t where ROWNUM < 11 order by EXECUTIONS DESC

/

6. TOP 10 SQL STATEMENT WITH MOST NO. OF SORTS :-

column SQL_TEXT format a100
column EXECUTIONS format 9999999
column SORTS format 99999999
column USER FORMAT a10
select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.SORTS from v$sqlarea t where ROWNUM < 11 order by SORTS DESC,EXECUTIONS DESC
/

7. TOP 10 SQL STATEMENT WITH MOST SHARABLE MEMORY :-

column SQL_TEXT format a100

column EXECUTIONS format 9999999

column SHARABLE_MEM format 99999999

column USER FORMAT a10

select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.SHARABLE_MEM from v$sqlarea t where ROWNUM < 11 order by SHARABLE_MEM DESC,EXECUTIONS DESC

/

8. TOP 10 SQL STATEMENT WITH MOST NO. of PARSE CALLS :-

column SQL_TEXT format a100

column EXECUTIONS format 9999999

column PARSE_CALLS format 99999999

column USER FORMAT a10

select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.PARSE_CALLS from v$sqlarea t where ROWNUM < 11 order by PARSE_CALLS DESC,EXECUTIONS DESC

/

I hope this article helped you.

Regards,
Amit Rath