Categories:
.NET (357)
C (330)
C++ (183)
CSS (84)
DBA (2)
General (7)
HTML (4)
Java (574)
JavaScript (106)
JSP (66)
Oracle (114)
Perl (46)
Perl (1)
PHP (1)
PL/SQL (1)
RSS (51)
Software QA (13)
SQL Server (1)
Windows (1)
XHTML (173)
Other Resources:
How To Get Execution Path Reports on Query Statements
How To Get Execution Path Reports on Query Statements? - Oracle DBA FAQ - Introduction to Command-Line SQL*Plus Client Tool
✍: FYIcenter.com
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")
2007-04-30, 5686👍, 0💬
Popular Posts:
How To Enter Numeric Values as HEX Numbers? - MySQL FAQs - Introduction to SQL Basics If you want to...
How To Analyze Tables with "mysqlcheck"? - MySQL FAQs - Administrator Tools for Managing MySQL Serve...
How can we format data inside DataGrid? Use the DataFormatString property.
.NET INTERVIEW QUESTIONS - What are Daemon threads and how can a thread be created as Daemon? Daemon...
How do I use a scriptlet to initialize a newly instantiated bean? A jsp:useBean action may optionall...