SQL Performance Diagnostics Scripts

I use the following SQL Scripts for finding top SQLs that may cause performance degradation.

Top SQL by Disk Reads

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end "Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1)) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by disk_reads desc nulls last;

Top SQL by Buffer Gets

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end "Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1)) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by buffer_gets desc nulls last;

Top SQL by CPU

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000)
"CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets
"Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then
null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end
"Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1))
"Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module
"Module"
from v$sql s
order by cpu_time desc nulls last;

Top SQL by Executions

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000)
"CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets
"Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then
null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end
"Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1))
"Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module
"Module"
from v$sql s
order by executions desc nulls last;