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 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Смотрите видео EXECUTION PLAN TUTORIAL using a real world example онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Ken Kim 28 Декабрь 2023, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 21 раз и оно понравилось 0 людям.