Find top SQL queries consuming resources cause performance in Oracle

Find the top 10 SQL queries by high 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

 ORDER BY buffer_gets DESC)
WHERE rownum <=10
;

Find top 10 SQL queries by high 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
 ORDER BY disk_reads DESC)
WHERE rownum  <=10
;

Find top 10 SQL queries for higher no of execution:

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
 ORDER BY executions DESC)
WHERE rownum  <=10
;

Find top 10 SQL queries by higher parse calls

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

Find top 10 SQL queries by high shared memory:

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

Find top 10 SQL queries by high 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
   ORDER BY version_count DESC)
WHERE rownum  <=10
;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.