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 Set Up Autotrace for a User Account?

If an Oracle user wants to use the autotrace feature, you can use the tutorial as an example to create the required table PLAN_TABLE, the required security role PLUSTRACE, and grant the role to that user:

SQL> CONNECT HR/retneciyf

SQL> @\oraclexe\app\oracle\product\10.2.0\server
  \RDBMS\ADMIN\UTLXPLAN.SQL
Table (HR.PLAN_TABLE) created.

SQL> CONNECT / AS SYSDBA

SQL> @C:\oraclexe\app\oracle\product\10.2.0\server
   \SQLPLUS\ADMIN\PLUSTRCE.SQL

   SQL> drop role plustrace;
   Role (PLUSTRACE) dropped.
   
   SQL> create role plustrace;
   Role (PLUSTRACE) created.
   
   SQL> grant plustrace to dba with admin option;
   Grant succeeded.

SQL> GRANT PLUSTRACE TO HR;
Grant succeeded.

Remember that PLAN_TABLE table must be created under the user schema HR.

How To Get Execution Path Reports on Query Statements?

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

SQL> CONNECT HR/retneciyf

SQL> SET AUTOTRACE ON EXPLAIN

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.

Execution Plan
-----------------------------------------------------------
Plan hash value: 3851899397

-----------------------------------------------------------
Id|Operation           |Name     |Rows|Bytes| Cost|Time   
  |                    |         |    |     |/%CPU|
-----------------------------------------------------------
 0|SELECT STATEMENT    |         |  59| 2832|  4/0|00:00:01
 1| NESTED LOOPS       |         |  59| 2832|  4/0|00:00:01
*2|  TABLE ACCESS FULL |EMPLOYEES|  59| 1239|  3/0|00:00:01
 3|  TABLE ACCESS      |JOBS     |   1|   27|  1/0|00:00:01
  |     BY INDEX ROWID |         |    |     |     |
*4|   INDEX UNIQUE SCAN|JOB_ID_PK|   1|     |  0/0|00:00:01
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."SALARY">12000)
   4 - access("E"."JOB_ID"="J"."JOB_ID")

(Continued on next part...)

Part: 1 2 3 4 5 6 7 8 9

More...