Oracle Database Interview Question:

How To Get Execution Statistics Reports on Query Statements?

Submitted by: Administrator

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/retneclgg

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;

<pre>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</pre>
6 rows selected.
Submitted by: Administrator

<pre>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
</pre>
Submitted by: Administrator