EXECUTION PLAN TUTORIAL using a real world example
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 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Watch video EXECUTION PLAN TUTORIAL using a real world example online without registration, duration hours minute second in high quality. This video was added by user Ken Kim 28 December 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 21 once and liked it 0 people.