Oracle DBA FAQ - Introduction to Command-Line SQL*Plus Client Tool

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6 7 8 9

(Continued from previous part...)

How To Get Execution Statistics Reports on Query Statements?

If your user account has autotrace configured by the DBA, you can use the "SET AUTOTRACE ON STATISTICS" command to turn on execution statistics reports on query statements. The tutorial exercise bellow shows you a good example:

SQL> CONNECT HR/retneciyf

SQL> SET AUTOTRACE ON STATISTICS

SQL> SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE
  2  FROM EMPLOYEES E, JOBS J
  3  WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000;

LAST_NAME             SALARY JOB_TITLE
----------------- ---------- -----------------------------
King                   24000 President
Kochhar                17000 Administration Vice President
De Haan                17000 Administration Vice President
Russell                14000 Sales Manager
Partners               13500 Sales Manager
Hartstein              13000 Marketing Manager

6 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        720  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

Part: 1 2 3 4 5 6 7 8 9

More...