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, 1294👍, 0💬
Popular Posts:
How To Run SQL Commands in SQL*Plus in Oracle? If you want to run a SQL command in SQL*Plus, you nee...
How To Start Instance with a Minimal Initialization Parameter File in Oracle? The sample initializat...
How To Get Year, Month and Day Out of DATETIME Values in SQL Server Transact-SQL? You can use DATEPA...
What Is the Difference Between GETDATE() and GETUTCDATE() in SQL Server Transact-SQL? The difference...
How to download Microsoft SQL Server 2005 Express Edition in SQL Server? Microsoft SQL Server 2005 E...