René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Recording statistics with Oracle

Oracle records statistics which can be used to tune the instance in the following three dynamic views: v$sesstat, v$sysstat and v$mystat.
The following query can be used to retrieve all statistic data about the current session. If you're interested in other sessions as well, use v$sesstat. 
column cls format a30
break on cls skip 1

select 
  decode (bitand(  1,class),  1,'User ',              '') ||
  decode (bitand(  2,class),  2,'Redo ',              '') ||
  decode (bitand(  4,class),  4,'Enqueue ',           '') ||
  decode (bitand(  8,class),  8,'Cache ',             '') ||
  decode (bitand( 16,class), 16,'Parallel Server ',   '') ||
  decode (bitand( 32,class), 32,'OS ',                '') ||
  decode (bitand( 64,class), 64,'SQL ',               '') ||
  decode (bitand(128,class),128,'Debug ',             '') cls,
  name,value from v m, v s where m.statistic# = s.statistic# order by 1
/
See also Finding one's SID.

Automatically display statistics for SQL statements

SQL*Plus allows to automatically display statistics for an SQL statement entered by turning on autotrace.

Important statistics

bytes received via SQL*Net from client

bytes sent via SQL*Net to client

consistent gets

db block gets + consistent gets = logical io (as opposed to physical io).
consistent gets are current mode gets. This might entail a reconstruction of the block with the undo (rollback) mechanism.

db block gets

db block gets + consistent gets = logical io (as opposed to physical io).
db block gets are current mode gets, blocks that are read as they are (even if these are being modified by another session)

process last non-idle time

This statistic records when a session was non-idle the last time.

physical reads

physical reads = physical io (as opposed to logical io).

recursive calls

redo size

The amount (in bytes) of redo generated.

sorts (disk)

The number of sorts that has written at least once to disk.

sorts (memory)

The number of sorts that didn't write to disk (were done entirly in memory).

SQL*Net roundtrips to/from client

See how setting the arraysize affects SQL*Net roundtrips to/from client.