Reading Explain Plan

How to Read the Explain Plan?

Reading the Execution Plan:

EXPLAIN PLAN FOR
select e.last_name,m.manager_name
from emp e, manager m
where e.mgr_id = m. emp_id
and e.deptno = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

output:

| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |     1 |    27 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |               |       |       |            |          |
|   2 |   NESTED LOOPS               |               |     1 |    27 |     3   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMPLOYEES     |     1 |    15 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    12 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."DEPARTMENT_ID"=10)
   4 - access("E"."MANAGER_ID"="M"."EMPLOYEE_ID")


****************Explaination:**********************************************
What is Cardinality.??
Cardinality: Estimated no. of rows to be returned.
how to calculate cardinality by formula

Formula:
Cardinality= Total no. of rows/No. of distinct rows

Access Method: How rows are retrieved from tables via either table scan or Index scan.
Join Method: The method (Nested Loop, Hash Join, Sort Merge Join) used to Join tables.
Join Order: Order in which tables are join to each other.
Parallel execution:
Partition Pruning :to check whether partition pruning is happening or not.



How to read execution plan.?

-> The line which is more intended toward right will execute first.
-> If both the line comes at same position then the one which executed first will be the first.




COST : Amount of resources used
what is Resources? (I/O and CPU)

Nested loop: Two tables outer table (Driving table: smallest table should come first)and inner tables(Driven tables)
Now each rows in Outer tables looks for the matching rows in Inner table
and in this case thaere is index on common column which means first its going to Index getting the row id  and then getting the row.


**********************************
Acces Method:

Index Unique San:In this employee id is primary key so by default unique index will be there


explain plan for
select * from employees
where employee_id=100;

select * from TABLE(DBMS_XPLAN.DISPLAY);


Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=100)

Explanation :

So what exactly here it is doing is going to index geting the ROW ID where the Value is 100
-----------------------------------------------------------------------------------

Nested loop using optimizer hint to get good response time here we are not believing in throughput Tuning.
so cost will be high

explain plan for
select /*+ first_rows */
e.employee_id,e.first_name,d.department_name
from employees e,departments d
where e.department_id=d.department_id;

| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   106 |  3180 |   109   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                |             |       |       |            |          |
|   2 |   NESTED LOOPS               |             |   106 |  3180 |   109   (0)| 00:00:02 |
|   3 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1498 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")


Sort Merge Join:

Sort Merge Join performs better than Nested Loop for large data sets.
SME are useful when Join condition between two tables is other than inequality like <,<=,>>=


2 tables T1 and  T2.
Reads the table 1 sorts the rows based on common column
Reads Table 2 sort the rows on common column and Merges the rows based on the Join condition.



HASH JOIN:
Used for large data set.
Join condition should be quality conditon other than inequality like SME.
Out of two table it picks smaller table which is driving table and build a Hash Table