| ADP - Analyse, Design & Programmierung GmbH | |
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 statementssql*plus allows
to automatically display statistics for an sql statement entered by
turning on autotrace.
Important statisticsbytes received via SQL*Net from clientbytes sent via SQL*Net to clientconsistent getsconsistent gets are current mode gets. This might
entail a reconstruction of the block with the undo (rollback)
mechanism.
db block getsdb 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)
physical readsphysical reads = physical
io (as opposed to logical
io).
recursive callsredo sizesorts (disk)sorts (memory)SQL*Net roundtrips to/from clientSee how setting the arraysize
affects SQL*Net roundtrips to/from client. |