Interpreting/ Understanding SQL query Explain Plan in Oracle

What's an explain plan?

An explain plan is a representation of the access path that is taken when a query is executed within Oracle.

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT,UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.

The row source tree is the core of the execution plan. It shows the following information:

·       An ordering of the tables referenced by the statement

·       An access method for each table mentioned in the statement

·       A join method for tables affected by join operations in the statement

·       Data operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

·       Optimization, such as the cost and cardinality of each operation

·       Partitioning, such as the set of accessed partitions

·       Parallel execution, such as the distribution method of join inputs

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.

What is Cost ?

The Oracle Optimizer is a cost-based optimizer. The execution plan selected for a SQL statement is just one of the many alternative execution plans considered by the Optimizer. The Optimizer selects the execution plan with the lowest cost, where cost represents the estimated resource usage for that plan. The lower the cost the more efficient the plan is expected to be. The optimizer’s cost model accounts for the IO, CPU, and network resources that will be used by the query.

Query processing can be divided into 7 phases:

1) Syntactic                   : Checks the syntax of the query

2) Semantic                   : Checks that all objects exist and are accessible

3) View Merging : Rewrites query as join on base tables as opposed to using views

4) Statement Transformation : Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)

5) Optimization  : Determines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path. With the Cost Based Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.

6) QEP Generation          : QEP = Query Evaluation Plan

7) QEP Execution            : QEP = Query Evaluation Plan

Steps [1]-[6] are handled by the parser. Step [7] is the execution of the statement.

The explain plan is produced by the parser. Once the access path has been decided upon it is stored in the library cache together with the statement itself. We store queries in the library cache based upon a hashed representation  of that query. When looking for a statement in the library cache, we first apply a hashing algorithm to the statement and then we look for this hash value in the library cache. This access path will be used until the query is re-parsed.

Terminology

Row Source	A set of rows used in a query may be a select from a base object or the result set returned by joining 2 earlier row sources
Predicate	where clause of a query
Tuples / Cardinality	rows
Driving Table	This is the row source that we use to seed the query. If this returns a lot of rows then this can have a negative affect on all subsequent operations
Probed Table	This is the object we lookup data in after we have retrieved relevant key data from the driving table.

How does Oracle access data?

At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically Oracle finds the data to read by using the following methods:

·         Full Table Scan (FTS)

·         Index Lookup (unique & non-unique)

·         Rowid

Understanding the execution plan :
In order to determine if you are looking at a good execution plan or not, you need to understand how the Optimizer determined the plan in the first place. You should also be able to look at the execution lan and assess if the Optimizer has made any mistake in its estimations or calculations, leading to a suboptimal plan. The components to assess are:

• Carnality – Estimate of the number of rows coming out of each of the operations.
• Access method – The way in which the data is being accessed, via either a table scan or index access.
• Join method – The method (e.g., hash, sort-merge, etc.) used to join tables with each other.
• Join type – The type of join (e.g., outer, anti, semi, etc.).
• Join order – The order in which the tables are joined to each other.
• Partition pruning – Are only the necessary partitions being accessed to answer the query?
• Parallel Execution – In case of parallel execution, is each operation in the plan being conducted in parallel? Is the right data redistribution method being used?

Explaining with sample example:

$ sqlplus /nolog

SQL> connect test@SIT

Enter password: xxxxxx

Connected.

See the plan:

                                                (figure: sample_1)

Explain plan Hierarchy : Simple explain plan:-

The rightmost uppermost operation of an explain plan is the first thing that the explain plan will execute. In this case TABLE ACCESS FULL EMP is the first operation. This statement means we are doing a full table scan of table EMP. When this operation completes then the resultant row source is passed up to the next level of the query for processing. In this case it is the SELECT STATEMENT which is the top of the query.

COST= part of the explain plan as well. For example the following query indicates that the CBO has been used because there is a cost in the cost field:

SELECT STATEMENT Cost=69

The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable.

Full Table Scan (FTS) :

In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock I/O to read the blocks from disk.  Multiblock I/O is controlled by the parameter  <PARAM:db_block_multi_block_read_count>.

This defaults to:

db_block_buffers / ( (PROCESSES+3) / 4 )

Cardinality :

The cardinality is the estimated number of rows that will be returned by each operation. The Optimizer determines the cardinality for each operation based on a complex set of formulas that use both table and column level statistics as input (or the statistics derived by dynamic sampling). One of the simplest formulas is used when there is a single equality predicate in a single table query (with no histogram). In this case the Optimizer assumes a uniform distribution and calculates the cardinality for the query by dividing the total number of rows in the table by the number of distinct values in the column used in the where clause predicate.

Maximum values are OS dependant

Buffers from FTS operations are placed on the Least Recently Used (LRU) end of the buffer cache so will be quickly aged out. FTS is not recommended for large tables unless you are reading >5-10% of it (or so) or you intend to run in parallel.

Example FTS explain plan:

SQL> explain plan for select * from dual;

(Figure : sample_2)

Index look-up :

Data is accessed by looking up key values in an index and returning rowids. A rowid uniquely identifies an individual row in a particular data block. This block is read via single block i/o.

In this example an index is used to find the relevant row(s) and then the table is accessed to lookup the ename column (which is not included in the index):

( figure : sample_3 )

Notice the 'TABLE ACCESS BY INDEX ROWID' section. This indicates that the table data is not being accessed via a FTS operation but rather by a rowid lookup. In this case the rowid has been produced by looking up values in the index first. The index is being accessed by an 'INDEX UNIQUE SCAN' operation. This is explained below. The index name in this case is PK_EMPID. If all the required data resides in the index then a table lookup may be unnecessary and all you will see is an index access with no table access.

In the following example all the columns (empid) are in the index. Notice that no table access takes place:

SQL> explain plan for
select empid,fname,deptid from emp where empno=101010;

SQL> format column fname a20;

SQL> select empid,fname,deptno from emp where empid=101010;

Sample Query Output:

     EMPID          FNAME                    DEPTNO

    ----------        --------------------           ----------

    101010          GOURANGA                   4006

Indexes are presorted so sorting may be unnecessary if the sort order required is the same as the index.

By default Oracle automatically determines the columns that need histograms based on the column

usage statistics and the presence of a data skew. If you need (or want) to create a histogram manually

you can use the following command.

SQL > Exec DBMS_STATS.GATHER_TABLE_STATS('HR','EMP');

(Figure : sort_sample_1)

In this case the index is sorted so the rows will be returned in the order of the index hence a sort is unnecessary.

Methods of Index lookup:

There are 4 methods of index lookup:

·         Index unique scan

·         Index range scan

·         Index full scan

·         Index fast full scan

Method for looking up a single key value via a unique index scan. Always returns a single value You must supply AT LEAST the leading column of the index to access data via the index, However this may return > 1 row as the uniqueness will not be guaranteed. 

In certain circumstances it is possible for the whole index to be scanned as opposed to a Index range scan (i.e. where no constraining predicates are provided for a table). Index full scans are  only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort.

For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order. The optimizer may decide that selecting all the information from the index and not sorting is more efficient than doing a FTS or a Fast Full Index Scan and then sorting.

1) Index unique scan: A "unique" scan scans for a single value in a unique index.

( see figure sample_3 )

2) Index range scan : A "range" scan starts at some starting value, and reads index entries sequentially (i,.e. along the b-tree) until it encounters a value that runs past a second value (a search for a single value on a non-unique index is a range scan.

( see  figure sort_sample_1)

3) Index full scan : An index full scan is when we read the index a block at a time - from start to finish. We'll read the root block, navigate down the left hand side of the index (or right if we are doing a descending full scan) and then when we hit the leaf block - we'll read across the entire bottom of the index - a block at a time - in sorted order. We use single block IO, not multi-block IO for this operation.

See the example:

( Figure : Index_Fullscan)

In certain circumstances it is possible for the whole index to be scanned as opposed to a range scan (i.e. where no constraining predicates are provided for a table). Full index scans are  only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort.

For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order. The optimizer may decide that selecting all the information from the index and not sorting is more efficient than doing a FTS or a Fast Full Index Scan and then sorting.

An Index full scan will perform single block i/o's and so it may prove to be inefficient. Index PK_EMPID is a concatenated.

4) Index Fast full scan : An index fast full scan reads the ENTIRE index, unsorted, as it exists on disk. It is basically using the index as a "skinny" version of the table. The query in question would only be accessing attributes in the index (we are not using the index as a way to get to the table, we are using the index INSTEAD of the table) We use multi-block I/O and read all of the leaf, branch and the root block. We ignore the branch and root blocks and just process the (un-ordered) data on the leaf blocks.

See the example:

( Figure : index_fastscan )

This is because we are selecting all of the index.

Note that INDEX FAST FULL SCAN is the mechinism behind fast index create and recreate. Index CIDX_IDNAME is a concatenated index on emp (empid,fname).

Facts related to index scan : Use of Index may not perform the query well...

Even if the schemas are the same, the optimizer can choose different execution plans if the costs are different. Some factors that affect the costs include the following:

     - Data volume and statistics
     - Bind variable types and values
     - Initialization parameters - set globally or at session level

Looking Beyond Execution Plans

       The execution plan operation alone cannot differentiate between well-tuned statements and those that perform poorly. For example, an EXPLAIN PLAN output that shows that a statement uses an index does not necessarily mean that the statement runs efficiently. Sometimes indexes can be extremely inefficient. In this case, you should examine the following:
The columns of the index being used 

Their selectivity (fraction of table being accessed)

       It is best to use EXPLAIN PLAN to determine an access plan, and then later prove that it is the optimal plan through testing. When evaluating a plan, examine the statement's actual resource consumption.

Rowid

This is the quickest access method available Oracle simply retrieves the block specified and extracts the rows it is interested in. Most frequently seen in explain plans as Table access by Rowid .

SQL> explain plan for select * from dept where rowid = ':x';

See the figure : access_by_rowid.

( Figure : access_by_rowid)

Table is accessed by rowid following index lookup: ( See figure  rowid_with_index_uniquescan)

( Figure : rowid_with_index_uniquescan )

Joins :

A Join is a predicate that attempts to combine 2 row sources We only ever join 2 row sources together Join steps are always performed serially even though underlying row sources may have been accessed in parallel. Join order - order in which joins are performed

The join order makes a significant difference to the way in which the query is executed. By accessing particular row sources first, certain predicates may be satisfied that are not satisfied by with other join orders. This may prevent certain access paths from being taken.

Suppose there is a concatenated index on A(a.col1,a.col2). Note that a.col1 is the leading column. Consider the following query:

select A.col4
from   A,B,C
where  B.col3 = 10
and    A.col1 = B.col1
and    A.col2 = C.col2
and    C.col3 = 5 ;

We could represent the joins present in the query using the following schematic:

  B     <---> A <--->    C
col3=10                col3=5

There are really only 2 ways we can drive the query: via B.col3 or C.col3. We would have to do a Full scan of A to be able to drive off it. This is unlikely to be efficient with large tables;

If we drive off table B, using predicate B.col3=10 (as a filter or lookup key) then we will retrieve the value for B.col1 and join to A.col1. Because we have now filled the leading column of the concatenated index on table A we can use this index to give us values for A.col2 and join to A.

However if we drive of table c, then we only get a value for a.col2 and since this is a trailing column of a concatenated index and the leading column has not been supplied at this point, we cannot use the index on a to lookup the data.

So it is likely that the best join order will be B A C. The CBO will obviously use costs to establish whether the individual access paths are a good idea or not.  If the CBO does not choose this join order then we can hint it by changing the from

clause to read:

from B,A,C

and using the /*+ ordered */ hint. The resultant query would be:

select /*+ ordered */ A.col4
from   B,A,C
where  B.col3 = 10
and    A.col1 = B.col1
and    A.col2 = C.col2
and    C.col3 = 5 ;

Join Types:

·         Sort Merge Join (SMJ)

·         Nested Loops (NL)

·         Hash Join

Sort Merge Join:

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

                   MERGE
                 /      \
            SORT        SORT
             |             |
        Row Source 1  Row Source 2

If the row sources are already (known to be) sorted then the sort operation is unecessary as long as both 'sides' are sorted using the same key. Presorted row sources include indexed columns and row sources that have already been sorted in earlier steps. Although the merge of the 2 row sources is handled serially, the row sources could be accessed in parallel.

explain plan for

select /*+ ordered */ e.deptno,d.deptno

from emp e,dept d

where e.deptno = d.deptno

order by e.deptno,d.deptno; 

Sorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particularly efficient join method.

Nested Loops :

First we return all the rows from row source 1 Then we probe row source 2 once for each row returned from row source 1

Row source 1
~~~~~~~~~~~~
Row 1 --------------       -- Probe ->       Row source 2
Row 2 --------------       -- Probe ->       Row source 2
Row 3 --------------       -- Probe ->       Row source 2

Row source 1 is known as the outer table
Row source 2 is known as the inner table

Accessing row source 2 is known a probing the inner table For nested loops to be efficient it is important that the first row source returns as few rows as possible as this directly controls the number of probes of the second row source. Also it helps if the access method for row source 2 is efficient as this operation is being repeated once for every row returned by row source 1.

See the plan for below example:

select b.fname,a.name,b.salary

from dept a

inner join emp b on a.deptno = b.deptno

where b.deptno=:b1;

( Figure : nested_loop_plan)

Hash Join :

This join is more efficient in theory than NL & SMJ Only accessible via the CBO Smallest row source is chosen and used to build a hash table and a bitmap The second row source is hashed and checked against the hash table looking for joins. The bitmap is used as a quick lookup to check if rows are in the hash table and are especially useful when the hash table is too large to fit in memory.

In cases where a very small table is being joined to a large table, the Oracle hash join will often dramatically speed-up the query. Hash joins are far faster than nested loop joins in certain cases, often in cases where your SQL is joining a large table to a small table.  

However, in a production database with very large tables, it is not always easy to get your database to invoke hash joins without increasing the RAM regions that control hash joins.  For large tables, hash joins requires lots of RAM.


Oracle places limits of the amount of RAM available for hash joins so that no single session will "hog" all of the RAM.  According to this research using Oracle 10g, the hash join maximum is only to only 200 megabytes (5% of pga_aggregate_target). 

The Oracle DBA controls the optimizers' propensity to invoke hash joins because the DBA must allocate the RAM resources to Oracle (using the hash_area_size and pga_aggregate_target parameters) for the optimizer to choose a hash join.  The CBO will only choose a hash join if you have allocated Oracle enough RAM area in which to perform the hash join.

In the above example, when we remove conditions from where condition, it is going for hash join.

See the below example to show hash join.

Explain plan for

select b.fname,a.name,b.salary

from dept a

inner join emp b on a.deptno = b.deptno;

( Figure : hash_join_plan)

Caution:
Unfortunately, the Oracle hash join is more memory intensive than a nested loop join. To be faster than a nested loop join, we must set the hash_area_size large enough to hold the entire hash table in memory (about 1.6 times the sum of the rows in the table).

If the Oracle hash join overflows the hash_area_size memory, the hash join will page into the TEMP tablespace, severely degrading the performance of the hash join. You can use the following script, to dynamically allocate the proper hash_area_size for your SQL query in terms of the size of your hash join driving table.

Fundamental difference between hash and nested loop joins :

Basically the most fundamental (or biggest or most important) difference between nested loop and hash joins is that "Hash joins can not look up rows from the inner (probed) row source based on values retrieved from the outer (driving) row source, nested loops can".

In other words, when joining table A and B (A is driving table, B is the probed table), then a nested loop join can take 1st row from A and perform a lookup to B using that value (of the column(s) you join by). Then nested loop takes the next row from A and performs another lookup to table B using the new value. And so on and so on and so on.

This opens up additional access paths to the table B, for example when joining ORDERS and ORDER_ITEMS by ORDER_ID (and ORDER_ID is leading column of PK in ORDER_ITEMS table), then for whatever orders are taken from ORDERS table, we can perform a focused, narrow index range scan on ORDER_ITEMS for every ORDER_ID retrieved from the driving ORDERS table. A hash join can’t do that.

Of course this doesn’t mean that hash joins can’t use any indexes for tables they read – index range scans and unique lookups can still be used under a hash join, but only if there are constant values in the query text (in form of literal or bind variables). If there are no such constant (filter) conditions under a hash join, then the other options to use that index would be to do an INDEX FULL SCAN (which is a range scan from end to end of the index) or INDEX FAST FULL SCAN (which is like a full table scan through the entire index segment). However none of these opportunities give the same benefits as nested loops looking up rows from row source B dynamically based on what was retrieved from A during runtime.

Note that this nested loops benefit isn’t limited to indexes only on table B, the difference is more fundamental than just a specific access path. For example, if table B happens to be a single table hash cluster or indexed X$ table, then the nested loop is also able to do “optimized” lookups from these row-sources, based on the values retrieved from table A.

Summery Hash Joins and Nested Loops joins:

Hash Joins - Hash joins are used for joining large data sets. The optimizer uses the smaller of the two tables or data sources to build a hash table, based on the join key, in memory. It then scans the larger table, and performs the same hashing algorithm on the join column(s). It then probes the previously built hash table for each value and if they match, it returns a row.

Nested Loops joins - Nested loops joins are useful when small subsets of data are being joined and if there is an efficient way of accessing the second table (for example an index look up). For every row in the first table (the outer table), Oracle accesses all the rows in the second table (the inner table). Consider it like two embedded FOR loops. In Oracle Database 11g the internal implementation for nested loop joins changed to reduce overall latency for physical I/O so it is possible you will see two NESTED LOOPS joins in the operations column of the plan, where you previously only saw one on
earlier versions of Oracle

Cartesian Product :

A Cartesian Product is done where they are no join conditions between 2 row sources and there is no alternative method of accessing the data Not really a join as such as there is no join! Typically this is caused by a coding mistake where a join has been left out. It can be useful in some circumstances - Star joins uses cartesian products.

Notice that there is no join between the 2 tables:

(Figure : cartesian_product_plan)

Operations :

Operations that show up in explain plans

·         sort

·         filter

·         view

Sorts :

There are a number of different operations that promote sorts

·         order by clauses

·         group by

·         sort merge join

SORT GROUP BY NOSORT
     INDEX FULL SCAN .....

In this case the group by operation simply groups the rows it does not do the sort operation as this has already been completed.

Sorts are expensive operations especially on large tables where the rows do not fit in memory and spill to disk. By default sort blocks are placed into the buffer cache. This may result in aging out of other blocks that may be re-read by other processes. To avoid this you can use the parameter <Parameter:SORT_DIRECT_WRITES> which does not place sort blocks into the buffer cache.

Filter :

Has a number of different meanings used to indicate partition elimination may also indicate an actual filter step where one row source is filtering another functions such as min may introduce filter steps into query plans

In this example there are 2 filter steps. The first is effectively like a NL except that it stops when it gets something that it doesn't like (i.e. a bounded NL). This is there because of the not in. The second is filtering out the min value:

(Figure : filter_plan)

View:

When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the 'view' will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable.

In the following example the select contains an inline view which cannot be merged:

example:

explain plan for

select firstname, tot

  from employee e,

       (select empid, sum(salary) tot from emp group by empid) tmp

 where e.employeeid = tmp.empid;

( Figure : view_plan)

In this case the inline view tmp which contains an aggregate function cannot be merged into the main query. The explain plan shows this as a view step.

Remote Queries :

Only shows remote in the OPERATION column OTHER column shows query executed on remote node OTHER_NODE shows where it is executed Different operational characteristics for RBO & CBO

RBO - Drags everything across the link and joins locally
CBO - Uses cost estimates to determine whether to execute remotely or locally

example:

explain plan for

select * from

hr.employee_insurance_details@remote_dblink;

In this case the whole query has been sent to the remote site. The other column shows nothing.

Parallel Query :

Main indicators that a query is using PQO:

[:Q1000004] entries in the explain plan

Checkout the other column for details of what the slaves are executing

v$pq_slave will show any parallel activity

Columns to look in for information

>> other - contains the query passed to the slaves

>> other_tag - describes the contents of other

>> object_node - indicates order of pqo slaves

Parallel Query operates on a producer/consumer basis. When you specify parallel degree 4 oracle tries to allocate 4 producer slaves and 4 consumer slaves. The producers can feed any of the consumers. If there are only 2 slaves available then we use these. If there is only 1 slave available then we go serial If there are none available then we use serial. If parallel_min_percent is set then we error ora 12827 instead of using a lower number of slaves or going serial

Consumer processes typically perform a sorting function. If there is no requirement for the data to be sorted then the consumer slaves are not produced and we end up with the number of slaves used matching the degree of parallelism as opposed to being 2x the degree.

Parallel Terms :

PARALLEL_FROM_SERIAL	This means that source of the data is serial but it is passed to a parallel consumer
PARALLEL_TO_PARALLEL	Both the consumer and the producer are  parallel
PARALLEL_COMBINED_WITH_PARENT	This operation has been combined with the parent operator. For example in a sort merge join the sort operations would be shown as PARALLEL_COMBINED_WITH_PARENT because the sort and the merge are handled as 1 operation.
PARALELL_TO_SERIAL	The source of the data is parallel but it is passed to a serial consumer. This typically will happen at the top of the explain plan but could occur anywhere

Examples of parallel queries :

Assumptions

DEPT is small compared to EMP
DEPT has an index (DEPT_INDX) on deptno column

Three examples are presented

Query #1:  Serial
Query #2:  Parallel
Query #3:  Parallel, with forced optimization to RULE and forced usage of DEPT_INDX

Sample Query #1 (Serial)

select A.name,avg(B.salary),max(B.salary)

from dept A,emp B

where A.deptno = B.deptno

group by A.name

order by max(B.salary),avg(B.salary) desc;

Sample Query #2 (Query #1 with parallel hints)

select /*+ parallel(B,4) parallel(A,4) */

A.name,avg(B.salary),max(B.salary)

from dept A,emp B

where A.deptno = B.deptno

group by A.name

order by max(B.salary),avg(B.salary) desc;

Sample Query #3 (Query #2 with fudged hints)

select /*+ index(A pk_deptno) parallel(B,4) parallel(A,4) */

A.name,avg(B.salary),max(B.salary)

from dept A,emp B

where A.deptno = B.deptno

group by A.name

order by max(B.salary),avg(B.salary) desc;

How to obtain explain plans

Explain plan for

Main advantage is that it does not actually run the query - just parses the sql. This means that it executes quickly. In the early stages of tuning explain plan gives you an idea of the potential performance of your query without actually running it. You can then make a judgement as to any modifications you may choose to make.

Autotrace

Autotrace can be configured to run the sql & gives a plan  and statistics afterwards or just give you an explain plan without executing the query.

Tkprof

Analyzes trace file

Click here to see white paper on The Oracle Optimizer Explain the Explain Plan.

If you are using plsqldev.exe tool, then open "Explain plan window" and write the query inside the window, then press F8 / execute to find "Execution plan" of a query.

This document will be helpful for beginner's  ( developers) . I will publish the advance concept of execution plan and performance tuning tips in next document.

Using V$SQL_PLAN Views

In addition to running the EXPLAIN PLAN command and displaying the plan, you can use the V$SQL_PLAN views to display the execution plan of a SQL statement:

After the statement has executed, you can display the plan by querying the V$SQL_PLAN view. V$SQL_PLAN contains the execution plan for every statement stored in the cursor cache. Its definition is similar to the PLAN_TABLE. See "PLAN_TABLE Columns".

The advantage of V$SQL_PLAN over EXPLAIN PLAN is that you do not need to know the compilation environment that was used to execute a particular statement. For EXPLAIN PLAN, you would need to set up an identical environment to get the same plan when executing the statement.

The V$SQL_PLAN_STATISTICS view provides the actual execution statistics for every operation in the plan, such as the number of output rows and elapsed time. All statistics, except the number of output rows, are cumulative. For example, the statistics for a join operation also includes the statistics for its two inputs.

The statistics in V$SQL_PLAN_STATISTICS are available for cursors that have been compiled with the STATISTICS_LEVEL initialization parameter set to ALL.

The V$SQL_PLAN_STATISTICS_ALL view enables side-by-side comparisons of the estimates that the optimizer provides for the number of rows and elapsed time. This view combines both V$SQL_PLAN and V$SQL_PLAN_STATISTICS information for every cursor.