Database Buffer Cache Information

Objects that take more than 5% of the buffer cache:

select o.owner, o.object_type, o.object_name, count(b.objd)
  from v$bh b, dba_objects o
  where b.objd = o.object_id
  group by o.owner, o.object_type, o.object_name
  having count (b.objd) > (select to_number(value*.05)
    from v$parameter
    where name = 'db_block_buffers');

Operating System Information

• CPU and device utilization: iostat $every_n_seconds $n_times
• Processor related statistics: : mpstat $every_n_seconds $n_times
• CPU utilization: sar -u $every_n_seconds $n_times
• Device utilization: sar -d $every_n_seconds $n_times
• Virtual memory utilization: vmstat $every_n_seconds $n_times

Rollback Segment Information

select n.name, s.xacts, s.gets, s.waits, 
  s.extents, s.wraps, s.extends, s.hwmsize
  from v$rollname n, v$rollstat s
  where n.usn = s.usn;

select segment_name, initial_extent,
  next_extent, min_extents
  from dba_rollback_segs;

Number of data blocks used by a transaction for its undo/redo information

select max(used_ublk)
  from v$transaction;

Shared Pool Information

How much parsing has your system been doing?

select a.value total_parses,
  b.value hard_parses,
  a.value - b.value soft_parses,
  round((b.value/a.value)*100,1) hard_parse_percentage
  from v$sysstat a, v$sysstat b
  where a.name = 'parse count (total)'
  and   b.name = 'parse count (hard)';

Are SQL statements being pushed from the library cache too often:

select sum(reloads)/sum(pins) from v$librarycache;

Show how much memory objects take in your shared pool:

select name, sharable_mem
  from v$db_object_cache
  where type in ('PACKAGE', 'PACKAGE_BODY', 'FUNCTION', 'PROCEDURE')
  order by sharable_mem desc;

Put an object into the Keep pool:

exec dbms_shared_pool.keep('STANDARD');

Sorting Information

select name, value
  from v$sysstat
  where name like '%sort%';

select tablespace_name, current_users, total_extents,
  total_blocks, used_extents, used_blocks
  from v$sort_segment;

System and Wait Events

Either set up statspack ($ORACLE_HOME/rdbms/admin/spdoc.txt) or take a delta of V$SYSTEM_EVENT:

drop table sys_event_start;
drop table sys_event_end;

create table sys_event_start as
  select * from v$system_event;

-- Wait a while
create table sys_event_end as
  select * from v$system_event;

-- View the deltas
select e1.event, 
  (e2.total_waits-e1.total_waits) "Delta Waits",
  (e2.total_timeouts-e1.total_timeouts) "Delta Timeouts",
  (e2.time_waited-e1.time_waited) "Delta Time Waited",
  (e2.average_wait-e1.average_wait) "Delta Average Wait"
  from sys_event_start e1, sys_event_end e2
  where e1.event = e2.event;

Find out what is happening on the session level. You probably want to replace the s.username is not null clause with something that is meaningful to you. If you remove it altogether, you will also get the events associated with background processes such as PMON and SMON.

select s.username, s.program, s.status,
  e.event, e.total_waits, e.total_timeouts,
  e.time_waited, e.average_wait
  from v$session s, v$session_event e
  where s.sid = e.sid
    and e.event like 'latch free'
    and s.status = 'ACTIVE'
    and s.username is not null;

Look at statistics of some common wait events:

select *
  from v$system_event
  where event in ('latch free',
                  'log file sync',
                  'log file parallel write', 
                  'control file parallel write',
                  'db file sequential read');

Find the wait events for currently connected sessions. Run multiple times:

select w.sid, s.username, w.event, p1text, p1, p2text, p2, 
  p3text, p3, w.wait_time, w.state, w.seconds_in_wait
  from v$session s, v$session_wait w
  where s.sid = w.sid
    and w.event not like 'SQL*Net%'
    and s.username is not null
    order by seconds_in_wait;

Wait Parameters for latch free waits

Turn on SQL Trace

You should set timed_statistics to true in your init.ora. Negligable overhead and gives you a lot more meaningfull figures.

alter session set sql_trace=true;

or for another session:

select sid, serial#
  from v$session
  where username= 'DIRK';

       SID    SERIAL#
---------- ----------
        35          3

execute dbms_system.set_sql_trace_in_session('35', '3', TRUE);

execute dbms_system.set_sql_trace_in_session('35', '3', FALSE);