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 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Auf dieser Seite können Sie das Online-Video EXECUTION PLAN TUTORIAL using a real world example mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer Ken Kim 28 Dezember 2023 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 42 Mal angesehen und es wurde von 1 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
