Collections:
Show Execution Path Reports in Oracle
How To Get Execution Path Reports on Query Statements in Oracle?
✍: 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")
⇒ Show Execution Statistics Reports in Oracle
⇐ Setup Autotrace for a User Account in Oracle
2020-06-08, 3481🔥, 0💬
Popular Posts:
How To Fix the INSERT Command Denied Error in MySQL? The reason for getting the "1142: INSERT comman...
How To Format Time Zone in +/-hh:mm Format in SQL Server Transact-SQL? From the previous tutorial, y...
What is sqlservr.exe - Process - SQL Server (SQLEX?PRESS) in SQL Server? Process sqlservr.exe is the...
How to run Queries with SQL Server Management Studio Express in SQL Server? 1. Launch and connect SQ...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...