Performance - General

Show currently exectuing sql

select sql_text
from v$sqlarea
where users_executing > 0
/

Session statistics

select sn.name
, st.value
from v$sesstat st
, v$statname sn
where st.STATISTIC# = sn.STATISTIC#
and st.VALUE > 0
and st.SID = &SID
order  by value desc
/

Resource intensive sql

change 8192 to match block size

select sql_text
,      executions
,      to_char((((disk_reads+buffer_gets)/executions) * 8192)/1048576, '9,999,999,990.00')
   as total_gets_per_exec_mb
,      to_char((( disk_reads             /executions) * 8192)/1048576, '9,999,999,990.00')
   as disk_reads_per_exec_mb
,      to_char((( buffer_gets            /executions) * 8192)/1048576, '9,999,999,990.00')
   as buffer_gets_per_exec_mb
,      parsing_user_id
from   v$sqlarea
where  executions > 10
order by 6 desc
/

db_cache_advice needs to be on for the above to work

File io stats

Requires timed_statistics=true

set lines 80 pages 999
col fname heading "File Name" format a60
col sizemb heading "Size(Mb)" format 99,999
col phyrds heading "Reads" format 999,999,999
col readtim heading "Time" format 99.999
col phywrts heading "Writes" format 9,999,999
col writetim heading "Time" format 99.999
select  lower(name) fname
,       (bytes / 1048576) sizemb
,       phyrds
, readtim
,       phywrts
, writetim
from    v$datafile df
,       v$filestat fs
where   df.file# = fs.file#
order   by 1
/

In session tracing

To switch it on:

exec dbms_system.set_sql_trace_in_session (<sid>, <serial#>, true);

To switch it off:

exec dbms_system.set_sql_trace_in_session (<sid>, <serial#>, false);

switch on event 10046

To switch it on:

alter session set events '10046 trace name context forever, level 8';

To switch it off:

alter session set events '10046 trace name context off'; 

Rows per block

select    avg(row_count) avg
, max(row_count) max
, min(row_count) min
from      (
  select  count(*) row_count
  from    &table_name
  group   by substr(rowid, 1, 15)
  )
/

Show the buffer cache advisory

Note. The current setting is halfway down and has a read factor of one.

set lines 100 pages 999
col est_mb format 99,999
col estd_physical_reads format 999,999,999,999,999
select size_for_estimate est_mb
, estd_physical_read_factor
, estd_physical_reads
from v$db_cache_advice
where name = 'DEFAULT'
order by size_for_estimate
/

alter system set db_cache_advice=on;