Oracle Database - Session Statistics (VSESSTAT)

About

Articles Related

Example

The second part of the autotrace output are a part of the session statistics.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
       1598  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         15  rows processed

How to

See

VSESSTAT

SELECT
  statname.display_name AS "Statistics Name",
  sesstat.value
FROM
  v$sesstat sesstat,
  V$STATNAME statname
WHERE
  sesstat.statistic# = statname.statistic#
AND sesstat.sid      = SYS_CONTEXT( 'USERENV', 'SID' )
ORDER BY
  statname.display_name;

Statistics

Recursive Call

recursive_calls : Number of SQL statements executed in order to execute your SQL statement

Logical I/O

db block gets and consistent gets are the most important part of the output. They represent the Oracle Database - Buffer IO (Logical IO). The less we latch, the better.

• db block gets : Total number of block read from the buffer cache in current mode
• consistent gets : Number of times, a consistent read was requested for a block in the buffer cache. Consistent reads may require read asides to the undo (rollback) information, and these reads to the undo will be counted here as well.

Physical read

Oracle Database - Physical Read : Number of physical reads from the datafiles into the buffer cache

Redo

Oracle Database - Redo Size statistics : Total amount of redo generated in bytes during the execution of this statement

SQL*Net Statistics

Oracle Database - SQL*Net Statistics

• bytes sent via SQL*Net to client : Total number of bytes sent to the client from the server
• bytes received via SQL*Net from client : Total number of bytes received from the client
• Oracle Database - SQL*Net roundtrips to from client : Total number of SQL*Net mesages sent to and received from the client. This includes round-trips for fetches from a multiple-row result set.

Sorts

• sorts (memory) : Sort done in the user's session memory (sort area). Controlled via the sort_area_size database parameter.
• sorts (disk) : Sorts that use the disk (temporary tablespace) because the sort exceeded the user's sort area size.

Rows Processed

• Rows processed by modifications or returned from a SELECT statement.