Oracle Top 25 sql

Oracle  top 10 sqls'

Oracle top sqls'
Oracle top sql
Oracle sql full text
t25.sql


--- ===========================
prompt Top SQL by Disk Reads
col "SQL"  format a45
col Module  format a12
select sql_id,
substr(sql_text,1,100)                         "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 gv$sql s
where PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
and rownum<25
and Module!='DBMS_SCHEDULER'
order by disk_reads desc nulls last;
-- ( 'SYS' ,  'SYSTEM' ,  'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' ,'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , ' ANONYMOUS ' , 'XDB','ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )

prompt Top SQL by Buffer Gets
col "SQL"  format a45
col Module  format a12
select
inst_id,
sql_id,
substr(sql_text,1,100)                       "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 gv$sql s
where PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
and rownum<25
and Module!='DBMS_SCHEDULER'
order by buffer_gets desc nulls last;

prompt Top SQL by CPU
col "SQL"  format a45
col Module  format a12
select
inst_id,
sql_id,
substr(sql_text,1,100)                      "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 gv$sql s
where PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
and rownum<25
and Module!='DBMS_SCHEDULER'
order by cpu_time desc nulls last;

prompt Top SQL by Executions
col "SQL" format a45
col Module format a12
select
inst_id,
sql_id,
substr(sql_text,1,100)                       "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 gv$sql s
where PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
and rownum<25
and Module!='DBMS_SCHEDULER'
order by executions desc nulls last;
col rows_processed  format a10
col sqltext   format a80
select rownum as rank, a.* from (select inst_id,sql_id,buffer_gets, lpad(rows_processed ||decode(users_opening + users_executing, 0, ' ','*'),20) "rows_processed",executions, loads,(decode(rows_processed,0,1,1)) * buffer_gets/ decode(rows_processed,0,1,rows_processed) avg_cost,
substr(sql_text,1,80) sqltext
from gv$sqlarea
where hash_value = hash_value
and PARSING_SCHEMA_NAME='&user'
order by 6 desc) a
where rownum <25
/

prompt sql full text
set long 5000
select inst_id,SQL_FULLTEXT from GV$SQLAREA where SQL_ID='&sql_id';
-- ==========================================================
Top explain plan
column load format a6 justify right
column executes  format 9999999
col load   format a22
break on load on executes skip 1
-- break on sql_id on executes skip 1
select
sql_id||'->'||substr(to_char(s.pct, '99.00'), 2) || '%' load,s.executions executes, p.sql_text
from ( select
address,
buffer_gets,
executions,
pct,
rank() over (order by buffer_gets desc) ranking
from ( select
 address,
 buffer_gets,
 executions,
 100 * ratio_to_report(buffer_gets) over () pct
 from sys.gv_$sql
where
-- command_type != 47 --- for plsql execution
PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
) where buffer_gets > 50 * executions ) s,
sys.gv_$sqltext p
where s.ranking <= 25
and p.address = s.address
order by 1, s.address, p.piece ;