====================================================================
-- Script: top_5_sql_on_sqlstats.sql
-- Author: Quanwen Zhao
-- Updated: May 17, 2019
-- Trying not to check v$sql, as you can see Connor's this blog post
-- https://connor-mcdonald.com/2019/03/04/less-slamming-vsql/
====================================================================

-- Top_5_buffer_gets
-- Ranking Top 5 SQL for buffer_gets (High CPU) on v$sqlstats
-- Number of buffer gets for all cursors with this SQL text and plan
SET LINESIZE 32767
SET PAGESIZE 50000

COLUMN sql_id FORMAT a13
COLUMN sql_text FORMAT a60
COLUMN buffer_gets FORMAT 999,999,999,999,999

SELECT *
FROM (SELECT sql_id
             , sql_text
             , buffer_gets
             , DENSE_RANK() OVER (ORDER BY buffer_gets DESC) AS buffer_gets_rank
      FROM v$sqlstats
      WHERE buffer_gets > 1000000
     )
WHERE buffer_gets_rank <= 5
/

SET LINESIZE 80
SET PAGESIZE 14

-- Top_5_disk_reads
-- Ranking Top 5 SQL for disk_reads (High I/O) on v$sqlstats
-- Number of disk reads for all cursors with this SQL text and plan
SET LINESIZE 32767
SET PAGESIZE 50000

COLUMN sql_id FORMAT a13
COLUMN sql_text FORMAT a70
COLUMN disk_reads FORMAT 999,999,999,999,999

SELECT *
FROM (SELECT sql_id
             , sql_text
             , disk_reads
             , DENSE_RANK() OVER (ORDER BY disk_reads DESC) AS disk_reads_rank
      FROM v$sql
      WHERE disk_reads > 100000
     )
WHERE disk_reads_rank <= 5
/

SET LINESIZE 80
SET PAGESIZE 14


-- Top_5_poor_parsing_applications
-- Ranking Top 5 SQL for poor parsing applications (parse_calls/executions) on v$sqlstats
-- parse_calls, Number of parse calls for all cursors with this SQL text and plan
-- executions, Number of executions that took place on this object since it was brought into the library cache
SET LINESIZE 32767
SET PAGESIZE 50000

COLUMN sql_id FORMAT a13
COLUMN sql_text FORMAT a70
COLUMN parse_calls/executions FORMAT 999,999,999,999,999 HEADING 'parse_calls|executions'
COLUMN parse_app_rank FORMAT 999 HEADING 'parse_app|rank'

SELECT *
FROM (SELECT sql_id
             , sql_text
             , parse_calls/executions
             , DENSE_RANK() OVER (ORDER BY DECODE(executions, 0, 0, parse_calls / executions) DESC) AS parse_app_rank
      FROM v$sql
      WHERE DECODE(executions, 0, 0, parse_calls / executions) > 1
     )
WHERE parse_app_rank <= 5
/

SET LINESIZE 80
SET PAGESIZE 14

-- Top_5_shared_memory
-- Ranking Top 5 SQL for shared memory (Memory hogs) on v$sqlstats
-- Total shared memory (in bytes) currently occupied by all cursors with this SQL text and plan
SET LINESIZE 32767
SET PAGESIZE 50000

COLUMN sql_id FORMAT a13
COLUMN sql_text FORMAT a60
COLUMN sharable_mem FORMAT 999,999,999,999,999

SELECT *
FROM (SELECT sql_id
             , sql_text
             , sharable_mem
             , DENSE_RANK() OVER (ORDER BY sharable_mem DESC) AS sharable_mem_rank
      FROM v$sqlstats
      WHERE sharable_mem > 10000000
     )
WHERE sharable_mem_rank <= 5
/

SET LINESIZE 80
SET PAGESIZE 14