|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
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.
bytes received via SQL*Net from client
bytes sent via SQL*Net to client
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 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.
The amount (in bytes) of redo generated.
The number of sorts that has written at least once to disk.
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.