EXECUTION PLAN TUTORIAL using a real world example

Published: 28 December 2023
on channel: Ken Kim

Recently I tuned a query that was taking over 5 minutes to complete. The query was joining on 6 tables with several filters. This video will cover how to capture the actual execution plan, identify the root cause, explain query slowness caused by KEY LOOKUP, and use a covering index. After tuning the query runtime reduced from 5:02 minutes to 7 seconds.

SCRIPT USED IN THE VIDEO:

ALTER SESSION SET STATISTICS_LEVEL='ALL';

RUN THE SELECT STMT.

GET THE EXECUTION PLAN.
--https://sqlmaria.com/2017/08/08/using...
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST +peeked_binds +cost +bytes'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dc2sqnr3xrn4z',0,'ALLSTATS LAST +peeked_binds +cost +bytes'));

SELECT inst_id, sql_id, child_number, plan_hash_value
, EXECUTIONS, ROUND(ROWS_PROCESSED/DECODE(EXECUTIONS,0,1,EXECUTIONS)) AVG_ROWS
, ROUND(ELAPSED_TIME/1000) ELAPSED_TIME_ms,ROUND(ELAPSED_TIME/DECODE(EXECUTIONS,0,1,EXECUTIONS)/1000,0) AVG_ELAPSED_TIME_ms
, DISK_READS, ROUND(DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS)) AVG_DISK_READS
, BUFFER_GETS, ROUND(BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS)) AVG_BUFFER_GETS
, LAST_ACTIVE_TIME, sql_fulltext
FROM gv$sql
WHERE sql_id IN ('d45m386zby1th ')
ORDER BY inst_id, sql_id, last_active_time DESC;

The tuned query EXECUTION PLAN in the video is incorrect.
I am posting the correct execution plan showing the covering index and query improvement.
To view copy to notepad and use the COURIER NEW font.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 748 (100)| 501 |00:00:07.31 | 44634 | 15858 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 136 | 748 (1)| 501 |00:00:07.31 | 44634 | 15858 | 83M| 7220K| 77M (0)|
| 2 | NESTED LOOPS | | 1 | 27 | 2133 | 720 (1)| 1232K|00:00:06.48 | 44597 | 15822 | | | |
|* 3 | HASH JOIN | | 1 | 132 | 7524 | 324 (1)| 11637 |00:00:00.51 | 4027 | 4000 | 1925K| 1925K| 1951K (0)|
|* 4 | HASH JOIN | | 1 | 132 | 5940 | 322 (1)| 11637 |00:00:00.50 | 4019 | 3997 | 2082K| 2082K| 1625K (0)|
| 5 | NESTED LOOPS | | 1 | 124 | 3348 | 74 (2)| 10913 |00:00:00.07 | 309 | 300 | | | |
|* 6 | TABLE ACCESS FULL | CUSTOMER | 1 | 1 | 15 | 72 (2)| 10 |00:00:00.03 | 247 | 245 | | | |
|* 7 | INDEX RANGE SCAN | IDX$$_6B270002 | 10 | 102 | 1224 | 2 (0)| 10913 |00:00:00.04 | 62 | 55 | | | |
| 8 | INDEX FAST FULL SCAN | NU_PATID_DSN_DEV_PRODID | 1 | 1 | 18 | 2 (0)| 943K|00:00:00.30 | 3710 | 3697 | | | |
| 9 | VIEW | index$_join$_004 | 1 | 288 | 3456 | 2 (0)| 288 |00:00:00.01 | 8 | 3 | | | |
|* 10 | HASH JOIN | | 1 | | | | 288 |00:00:00.01 | 8 | 3 | 1476K| 1476K| 1513K (0)|
| 11 | INDEX FAST FULL SCAN| AK_DEVICE_IDX | 1 | 288 | 3456 | 1 (0)| 288 |00:00:00.01 | 4 | 2 | | | |
| 12 | INDEX FAST FULL SCAN| XPKDEVICE_IDX | 1 | 288 | 3456 | 1 (0)| 288 |00:00:00.01 | 4 | 1 | | | |
|* 13 | INDEX RANGE SCAN | NU_TRANS_COVERING_IDX | 11637 | 1 | 22 | 3 (0)| 1232K|00:00:05.87 | 40570 | 11822 | | | | --COVERING INDEX to negate KEY LOOKUP!
| 14 | TABLE ACCESS FULL | EMR | 1 | 1 | 57 | 2 (0)| 3993 |00:00:00.01 | 37 | 36 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

On this page of the site you can watch the video online EXECUTION PLAN TUTORIAL using a real world example with a duration of hours minute second in good quality, which was uploaded by the user Ken Kim 28 December 2023, share the link with friends and acquaintances, this video has already been watched 42 times on youtube and it was liked by 1 viewers. Enjoy your viewing!

13,912

323