In this demo we shall explore the execution plan of a query
and find out the bottlenecks in poor performing queries.
Let us take a sample query:-
select e.employee_id, d.department_name, e.first_name,
e.last_name, e.hire_date, l.city
from demo_emp e, demo_dept d, demo_location l
where d.department_id = 60 and d.department_id =
e.department_id and l.location_id = d.location_id;
To get the execution plan we need to follow the mentioned
steps:-
GENERATING THE EXECUTION PLAN
________________________________
Step 1 - explain plan for
select
e.employee_id, d.department_name, e.first_name, e.last_name, e.hire_date,
l.city
from
demo_emp e, demo_dept d, demo_location l
where
d.department_id = 60 and d.department_id = e.department_id and l.location_id =
d.location_id;
OUTPUT:-
explain plan succeeded.
Step 2 - select * from table(dbms_xplan.display);
OUTPUT:-
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3010691218
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 5 |
670 | 10 (10)| 00:00:01 |
|* 1 | HASH JOIN | | 5 |
670 | 10 (10)| 00:00:01 |
|* 2 | HASH JOIN | |
1 | 73 | 7
(15)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEMO_DEPT |
1 | 43 | 3
(0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| DEMO_LOCATION | 23 |
690 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | DEMO_EMP |
5 | 305 | 3
(0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
2 -
access("L"."LOCATION_ID"="D"."LOCATION_ID")
3 -
filter("D"."DEPARTMENT_ID"=60)
5 -
filter("E"."DEPARTMENT_ID"=60)
Note
-----
- dynamic sampling
used for this statement (level=2)
24 rows selected
****************************************************************************
Now Let us do a detailed analysis.
There are multiple things the execution plan says:-
1 – Total cost is 36
(just add all the costs)
|
Cost (%CPU)
|
|
10 (10)
|
|
10 (10)
|
|
7 (15)
|
|
3 (0)
|
|
3 (0)
|
|
3 (0)
|
|
|
2 – Total memory used is 2451 Bytes
|
Bytes
|
|
670
|
|
670
|
|
73
|
|
43
|
|
690
|
|
305
|
3 – There are 3 Full table scans
|
TABLE ACCESS FULL
|
DEMO_DEPT
|
1
|
43
|
|
TABLE ACCESS FULL
|
DEMO_LOCATION
|
23
|
690
|
|
TABLE ACCESS FULL
|
DEMO_EMP
|
5
|
305
|
This is the major pain point in which we shall focus in our demo.
We shall try to reduce the number of full table scans.
The most common approach is to use an index.
“Our
demo developer might have ignored the fact and is complaining about slow
performing query”
Let us create an index in first table executed “DEMO_DEPT”
CREATE INDEX DEMO_DEPT_ID ON DEMO_DEPT (DEPARTMENT_NAME);
Now if we explain again we find the plan as
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2307362991
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 5 |
670 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN | | 5 |
670 | 9 (12)| 00:00:01 |
|* 2 | HASH JOIN | | 1 |
73 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEMO_DEPT |
1 | 43 | 2
(0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | DEMO_DPT_ID |
1 | | 1
(0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DEMO_LOCATION | 23 |
690 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | DEMO_EMP | 5
| 305 | 3
(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Now we avoided one full table scan and used an INDEX RANGE SCAN. The
cost reduces to 33. Thus now the query shall execute faster.
This number may not be very impressive for this demo tables which
are very small in size. But in real life a lot can be gained by Indexing. Try
the same for the other tables and see the difference.
In this demo we covered the
index part only, stay tuned for more…
Disclaimer – This document is intended for information purpose
only. Please communicate with me on – akash007.pramanik@gmail.com before using
in any live production environment.
Good
ReplyDelete