Check the Execution plan from SQL ID of SQL query in Oracle

Check the Execution plan from SQL ID of SQL Query in Oracle

Get the SQL ID for the SQL Statement

-- First you need to execute the SQL statement to get the SQL ID from v$sql view.
Select sql_id from v$sql where sql_text like 'SELECT * FROM EMP';
SQL_ID
-------------
4ttqgu8uu8fus

Get the explain plain or execution plan for the SQL ID in Oracle

set line 200 pages 200
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('&sqlid'));
---Explain Plan
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID 4ttqgu8uu8fus, child number 0
-------------------------------------
SELECT * FROM EMP
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 165K(100)| |
| 1 | TABLE ACCESS FULL| EMP | 38M| 2522M| 165K (1)| 00:00:07 |
--------------------------------------------------------------------------

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.