- Administration
- Structure
- Performance
- Backup
- Miscellaneous
- SQL*Net
|
|
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
/
|
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';
Note. use tkprof to interpret the results.
|
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
/
db_cache_advice needs to be on for the above to work
alter system set db_cache_advice=on;
|
|
