PROMPT

PROMPT

PROMPT~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PROMPT------ /) (\

PROMPT------ .-._((,~~.))_.-,

PROMPT------ `-. @@ ,-'

PROMPT------ / ,o--o. \

PROMPT------ ( ( .__. ) )

PROMPT------ ) `----' (

PROMPT------ / \

PROMPT------ / \

PROMPT------ / \

PROMPT------ "The Silly Cow"

PROMPT----- Script: iointensive_sql_mem.sql

PROMPT----- Author: Prashant 'The FatDBA'

PROMPT----- Cat: Performance Management and Issue Identification

PROMPT----- Version: V1.2 (Date: 11-02-2014)

PROMPT-----

PROMPT-----

PROMPT-----

PROMPT~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~set feed off;

set pagesize 10000;

set wrap off;

set linesize 200;

set heading on;

set tab on;

set scan on;

set verify off;

column sql_text format a40 heading 'SQL-Statement'

column executions format 999,999 heading 'Total|Runs'

column reads_per_run format 999,999,999.9 heading 'Read-Per-Run|[Number of]'

column disk_reads format 999,999,999 heading 'Disk-Reads|[Number of]'

column buffer_gets format 999,999,999 heading 'Buffer-Gets|[Number of]'

column hit_ratio format 99 heading 'Hit|Ratio [%]'

ttitle left 'I/O-intensive SQL-Statements in the memory (V$SQLAREA)' -

skip 2

SELECT sql_text, executions,

round(disk_reads / executions, 2) reads_per_run,

disk_reads, buffer_gets,

round((buffer_gets - disk_reads) / buffer_gets, 2)*100 hit_ratio

FROM v$sqlarea

WHERE executions > 0

AND buffer_gets > 0

AND (buffer_gets - disk_reads) / buffer_gets < 0.80

ORDER BY 3 desc;