There are multiple tools you can use to look at Oracle execution plans. I’m going to show you how to find an execution plan via SQL*Plus. I know I’ve said this before, but I’m a big believer is knowing how to find information when you do not have a GUI at your disposal.
I have a very basic table called ‘demo_table’ which contains 2 attributes (‘attrib1’ and ‘attrib2’). I executed the query ‘select attrib1 from demo.demo_table;’. Now I want to see the execution plan for this query.
The first thing I need to know is what the SQL_ID of my query is. To find this I am going to use a system view called ‘v$sql’.
SQL> select SQL_ID, SQL_TEXT from v$sql
where SQL_TEXT like '%demo%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
2yduygc33apu2 select
SQL_ID, SQL_TEXT from v$sql where SQL_TEXT like
'%demo%'
fxrwxk6afast0 select
attrib1 from demo.demo_table
Now that I have the SQL_ID for the query I executed, I can find the execution plan.
SQL> select plan_table_output
from table(dbms_xplan.display_cursor('fxrwxk6afast0',null,'basic'));
PLAN_TABLE_OUTPUT
------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select attrib1 from demo.demo_table
Plan hash value: 2294683009
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| 1 | TABLE ACCESS FULL | DEMO_TABLE |
The previous example used the ‘basic’ format option. Let’s see what the output looks like with ‘typical’ formatting:
SQL> select plan_table_output
from table(dbms_xplan.display_cursor('fxrwxk6afast0',null,'typical'));
PLAN_TABLE_OUTPUT
----------------------------------------------------
SQL_ID fxrwxk6afast0,
child number 0
-------------------------------------
select attrib1 from demo.demo_table
Plan hash value: 2294683009
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation |
Name | Rows | Bytes |
Cost (%CPU) | Time |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | | | 2 (100) |
|
| 1 | TABLE ACCESS FULL | DEMO_TABLE | 1 | 13 | 2
(0) | 00:00:01 |
Finally, let’s look at ‘All’ formatting output.
SQL> select plan_table_output
from table(dbms_xplan.display_cursor('fxrwxk6afast0',null,'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fxrwxk6afast0,
child number 0
-------------------------------------
select attrib1 from demo.demo_table
Plan hash value: 2294683009
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation |
Name | Rows | Bytes |
Cost (%CPU) | Time |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | | | 2 (100) |
|
| 1 | TABLE ACCESS FULL | DEMO_TABLE | 1 | 13 | 2
(0) | 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 /
DEMO_TABLE@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 -
"ATTRIB1"[NUMBER,22]
This was a very quick example on how to find an execution plan in Oracle via SQL*Plus. There is much more to learn so please read the Oracle document to find more information on this topic!
No comments:
Post a Comment