How To Get Execution Path Reports on Query Statements?
Submitted by: AdministratorIf 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/retneclgg
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;
<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
SQL> CONNECT HR/retneclgg
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;
<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
Execution Plan
-----------------------------------------------------------
Plan hash value: 3851899397
<pre>
-----------------------------------------------------------
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")
</pre>
Submitted by: Administrator
-----------------------------------------------------------
Plan hash value: 3851899397
<pre>
-----------------------------------------------------------
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")
</pre>
Submitted by: Administrator
Read Online Oracle Database Job Interview Questions And Answers
Top Oracle Database Questions
☺ | How To Recover a Dropped Table in Oracle? |
☺ | How Run SQL*Plus Commands That Are Stored in a Local File? |
☺ | How Much Memory Your 10g XE Server Is Using? |
☺ | What Is a Table Index in Oracle? |
☺ | How To Use Values from Other Tables in UPDATE Statements using Oracle? |
Top DB Oracle Categories
☺ | Oracle PL-SQL Interview Questions. |
☺ | Oracle DBA Interview Questions. |
☺ | Oracle D2K Interview Questions. |
☺ | OCI Interview Questions. |
☺ | Forms Reports Interview Questions. |