Oracle Database - Session Statistics (V$SESSTAT)

> Database > Oracle Database

1 - About

3 - 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
Advertising

4 - How to

4.1 - See

V$SESSTAT

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;

5 - Statistics

5.1 - Recursive Call

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

5.2 - 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.

5.3 - Physical read

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

5.4 - Redo

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

5.5 - 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.
Advertising

5.6 - 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.

5.7 - Rows Processed

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