• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » Explain plan in oracle

Explain plan in oracle

January 26, 2022 by techgoeasy 2 Comments

We have already posted on Autotrace in Oracle, tkprof, sql_trace. Here we will be looking for the Oracle Explain Plan utility and how we can effectively use it to tune the Oracle sql statement

Oracle Explain Plan : Tuning Tool

Table of Contents

  • What is Explain Plan in oracle
  • How to find the Oracle Explain Plan?
  • How to Read the explain Plan
  • What to look for ?
  • how to get execution plan in oracle using sql_id in Cursor
  • how to get execution plan in oracle using sql_id in AWR/how to get old execution plan in oracle
  • Actual Sql execution plan
  • How to find the change in explain plan in oracle for a sql id

What is Explain Plan in oracle

  • Oracle database internally creates a query execution plan in order to fetch the desired data from the physical tables. The query execution plan is nothing but a set of methods on how the database will access the data from the tables. This query execution plan is crucial as different execution plans will need different cost and time for the query execution
  • It shows the execution path of a SQL statement
  • it indicates what tables it will read first and in what order
  • it indicates what indexes it will use, or if new indexes are needed
  • It Helps verify that Oracle will retrieve the data the way you expect it to

How to find the Oracle Explain Plan?


Must have full access to a PLAN_TABLE or create PLAN_TABLE with utlxplan.sql delivered by Oracle

Insert the following command at the top of your SQL statement

SQL> explain plan for <sql statement>;
Explained
SQL> select * from table(dbms_xplan.display);

Example

SQL> explain plan for select * from dual;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT

| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 2 | 2 |
| 1 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 |
Note: cpu costing is off

With multiple statements, you can specify a statement identifier and use that to identify your specific execution plan

explain plan
set statement_id = '<identifier>'
into plan_table
for  <sql statement>
;

Example
explain plan
set statement_id = 'x1'
into plan_table
for select user_name, user_id from emp
;

Once this is executed, we can view the explain plan with the help of the below queries. This does not execute the query, it just creates the explain plan

col operation format a13 trunc
col options format a15 trunc
col object_name format a23 trunc
col id format 9999
col parent_id format 9999
col position format 9999
col operations format a25
select lpad(' ',2*level) || operation operations,options,object_name
from plan_table
where statement_id = 'x1'
connect by prior id = parent_id and statement_id = 'x1'
start with id = 1 and statement_id = 'x1'
order by id;

Or
SQL> set linesize 132  
SQL> SELECT * FROM TABLE(dbms_xplan.display);
or
SQL> set linesize 132  
SQL> SELECT * FROM TABLE(dbms_xplan.display(statement_id=>'x1'));

If you want the output in HTML format, you can use the below query

set pages 0 head off
set linesize 132
set long 1000000
col xplan format a100
spool id1.html
SELECT dbms_xplan.display_plan(statement_id => 'id1',type=>'HTML') AS XPLAN
FROM dual;
spool off;

if you want the additional information also, you can execute the below statement

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'))

How to Read the explain Plan

  • Read innermost out, top/down
  • Join operations always require two sets. The order you read the sets is top down, so the first set is the driving set and the second is the probed set. In the case of a nested loop, the first set is the outer loop. In the case of a hash join, the first set is used to build the hash table.
  • One join is performed at a time, so you only need to consider two sets and their join operation at any one time.

Let’s take an example

Here the complete order will be 3,2,4,2,1,5,1,0
(a) It will first go to step 3, do a full table scan and pass the rows to step 2 which will build the hash table
(b) It will go to 4 and select the rows and give the rows to step 2. Here Hash join will be done.
(c) Now the rows selected will be given to step 1 which will build the hash table
(d) It will go to step 5, select the rows and pass it to step 1. Here Hash join will be done.
(e) Since no more children, it will output the rows in step 0

What to look for ?

-Look for TABLE ACCESS… (FULL) …
-Costly if the table is big
-Costly if performed many times
-Look for INDEX… (RANGE SCAN)…
-Expected for non-unique indexes
-Suspicious for unique indexes

Joins Info from Tuning Perspective

Nested Loops

  • Good for on-line screens and reports
  • Read all rows from table 1
  • Then access table 2 once for each row returned from table 1
  • Fastest if rows returned from table 1 are small , access to table2 is inexpensive.
  • This means either a UNIQUE lookup or a SMALL Range Scan.

Merge Join

  • Better than nested loops for joining a large number of rows

Hash Join

  • Common in Parallel Queries
  • Better than Merge Join
  • Smallest table is passed over and a hashing algorithm is applied to each row to create a hash table and a bitmap.
  • Second table is passed over and a hashing algorithm applied and check for match (ie. joins)
  • The bitmap is used as a quick lookup and is especially useful when hash table is too large to fit in memory.

Sort Merge Join

  • Rows are produced from table 1 and are then sorted
  • Rows are produced from table 2 and sorted by the same sort key as table 1
  • Table 1 and 2 are NOT accessed concurrently
  • Sorted rows from both sides are then merged together(joined)

how to get execution plan in oracle using sql_id in Cursor

If the statement is in Cursor( Shared pool), we can find the explain plan taken by the Oracle to execute the query using the below query. Here you need to provide the sql_id of the sql statement

col operation for a20
col object_name for a20
col options for a20
col optimizer for a12
col child_number a3
SELECT child_number, id , lpad (' ', depth) || operation operation , options , object_name , optimizer , cost
FROM V$SQL_PLAN
WHERE sql_id = '&sql_id'
ORDER BY child_number, id;

OR
select * from table(dbms_xplan.display_cursor('&1', NULL, 'ALL'));

OR
select * from table( DBMS_XPLAN.display_cursor('&1', NULL,'ADVANCED ROWS ALLSTATS'));
The above statement will provide lot of additional information

how to get execution plan in oracle using sql_id in AWR/how to get old execution plan in oracle

If a sql statement has been executed in past and it is in the Oracle Automatic Workload Repository, then we can find the explain plan using the below command

select * from table( DBMS_XPLAN.DISPLAY_AWR('&1', NULL,NULL,'ADVANCED ROWS ALLSTATS'));

This will list all the available plan in AWR and this will include the old execution plan also

Actual Sql execution plan

The plan you get through by using explain plan command is not an actual execution plan, it is the estimated execution plan. Oracle may choose a different plan at the time of execution depending on the table statistics and bind variable. So sometimes it will be better to execute the query and then find the explain plan for the query in V$SQL_PLAN

How to find the change in explain plan in oracle for a sql id

Many times because of stats issues, addition in the index, and other parameters, The sql start performing badly. One of the reasons could be a change in the execution plan of the sql. The below sql will show the history of all the execution/explain plans taken by the particular sql. This is very useful when your developer complains about performance on the sql query which was running nice yesterday

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
SELECT ss.snap_id,
ss.instance_number node,
begin_interval_time,
sql_id,
plan_hash_value,
NVL(executions_delta,0) execs,
(elapsed_time_delta/DECODE(NVL(executions_delta,0),0,1,executions_delta))/1000000
avg_etime,
(buffer_gets_delta /DECODE(NVL(buffer_gets_delta,0),0,1,executions_delta))
avg_lio
FROM DBA_HIST_SQLSTAT S,
DBA_HIST_SNAPSHOT SS
WHERE sql_id = NVL('&sql_id','fhf133jjk234')
AND ss.snap_id = S.snap_id
AND ss.instance_number = S.instance_number
AND executions_delta > 0
ORDER BY 1,2,3
/

Related Articles
How to find the change in explain plan in oracle for a sql id
sql tuning advisor
Nested Loop Join in Oracle 11g
Hash join in Oracle with example
Various Joins Method in Oracle
https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm

Filed Under: Oracle, Oracle Database Tagged With: Oracle Performance Tuning Tools, Tuning Tools

Reader Interactions

Comments

  1. Niraj Dora says

    January 6, 2020 at 7:02 am

    I Got your reference from bobbydurrett’s blog… I really like the concepts you discuss here…

    Reply
    • techgoeasy says

      January 30, 2020 at 4:18 am

      Thanks Niraj

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Password Version in oracle
  • How to login as user without changing the password in Oracle database(alter user identified by values)
  • How to check encrypted tablespace in the Database
  • How To Export -Import TDE Master Encryption Key
  • How to Configure Auto Login TDE Wallet

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us