EXPLAIN PLAN

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer  to execute a SQL statement.
EXPLAIN PLAN takes less than a minute to EXPLAIN a query that takes four hours to run because it does not actually execute the SQL statement, it only outlines the plan to use and inserts this execution plan in an Oracle table (PLAN_TABLE).

Why we will use EXPLAIN PLAN without TRACE?
The statement is not executed; it only shows what will happen if the statement is executed. 
When do you use EXPLAIN without TRACE?
When the query will take exceptionally long to run.

How to use EXPLAIN PLAN?

1. Create PLAN TABLE: Execute the script "utlxplan.sql". File location is below

oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql    

2. EXPLAIN Query: Run the EXPLAIN PLAN for the query to be optimized 

EXPLAIN PLAN FOR
select ename,sal,empno,deptno
from emp
where deptno=10;    

Using Tag:

EXPLAIN  PLAN FOR

SET STATEMENT_ID='SQL1'

select ename,sal,empno,deptno
from emp
where deptno=10;

3. PLAN Table is populated: Select the output from PLAN TABLE

select operation, options, object_name, id, parent_id
from plan_table
where statement_id = 'SQL1'   

OR we can use below query to see the output in proper format 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);    

It shows the following information: 

• The row source tree is the core of the execution plan. 
• 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. 

Query processing can be divided into 7 phases :

• Syntactic          : Checks the syntax of the query
• Semantic          : Checks that all objects exist and are accessible
• View Merging  : Rewrites query as join on base tables as opposed to using views
• Statement Transformation : Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)
• 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.
• QEP Generation    : QEP = Query Evaluation Plan.
• QEP Execution      : QEP = Query Evaluation Plan.

In Toad how we will work on Explain plan :

Using toad , this can be achieved by following the steps below.

• Connect to the Oracle SID
• Open a SQL editor, and write the SQL query for which the explain plan is required.
• CTRL+E will produce the explain plan for the query - which basically means , this is the most likely path oracle will chose while executing the SQL. 
• Analyze the cost of the query , and identify the areas which are causing the cost to grow high.Mostly this happens when full table access is performed, or hashed joins are used , instead of full index scans and nested loops.
• This is the fastest way to identify if a query you have written has some tuning gaps and can be rewritten to perform better in distributed and scalable high volume environments.