oracledba.help
TroubleShooting

SQLPlus, Get Session Statistics

Overview

The following SQLPlus commands can be used to get session statistics.

 Turn on all statistics: SET AUTOTRACE ON
 To turn off statistics: SET AUTOTRACE OFF

Getting Individual Statistics

SET TIMING ON

Elapsed: 00:00:00.04

SET AUTOTRACE ON STATISTICS

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

SET AUTOTRACE ON EXPLAIN

 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 879063579
  --------------------------------------------------------------------------------------------------------------------------- 
  | Id  | Operation               | Name               | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib | 
  --------------------------------------------------------------------------------------------------------------------------- 
  |   0 | SELECT STATEMENT        |                    |    15 |  2415 |     1 (100)| 00:00:01 |        |      |            | 
  |*  1 |  COUNT STOPKEY          |                    |       |       |            |          |        |      |            | 
  |   2 |   VIEW                  |                    |    15 |  2415 |     1 (100)| 00:00:01 |        |      |            | 
  |*  3 |    SORT ORDER BY STOPKEY|                    |    15 |  2355 |     1 (100)| 00:00:01 |        |      |            | 
  |   4 |     PX COORDINATOR      |                    |       |       |            |          |        |      |            | 
  |   5 |      PX SEND QC (RANDOM)| :TQ10000           |    15 |  1680 |     0   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  | 
  |   6 |       VIEW              | GV$SESSION_LONGOPS |       |       |            |          |  Q1,00 | PCWP |            | 
  |   7 |        FIXED TABLE FULL | X$KSULOP           |    15 |  1680 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            | 
  --------------------------------------------------------------------------------------------------------------------------- 

 Predicate Information (identified by operation id):
 ---------------------------------------------------
   1 - filter(ROWNUM<=15)
   3 - filter(ROWNUM<=15)

 Note
 -----
 - statement not queuable: gv$ statement