StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIs this long running select query running optimally?
text
Body
copied!<p>I have a long running query (~10 minutes) that is generated by an application which I cannot control. However the underlying tables that the query runs on are in my control. I am pasting the query and it's execution plan here. I don't have sufficient expertise to interpret the execution plan and identify what changes if any can be made on the underlying tables (like adding/removing indexes), to help execute this any faster.</p> <p>The query itself looks like a straight forward star schema join where a large fact table is being joined against multiple dimension tables. Can someone suggest any changes that can be made to these tables, especially the main fact table with ~ 50 million rows to make these kind of queries run faster? </p> <p>The record counts of the tables involved are listed below</p> <pre><code>/B28/EUDIQSBV 58,528,276 /B28/SUDDX6I4 7,330 /B28/SUDDHZNC 312 /B28/SUDDZMED 267 /B28/SUDDFNH0 84 /B28/SUDD8DGN 76 /B28/SUDDDY4S 75 /B28/SUDDCQ0M 46 /B28/SUDD741B 36 /B28/SUDDC4PW 23 /B28/SUDD94IW 21 /B28/DUDIQSBVD 11 /B28/SUDDXGOI 8 /B28/DUDIQSBVP 7 /B28/SUDDCD88 5 /B28/SUDD8UPI 2 /BI0/0600000051 0 </code></pre> <p><strong>EDIT:</strong></p> <p>On request, here are the list of indexes in the /B28/EUDIQSBV table.</p> <pre><code>Index using Dimension /CPMB/UDIQSBV1 Index using Dimension /CPMB/UDIQSBV2 Index using Dimension /CPMB/UDIQSBV3 Index using Dimension /CPMB/UDIQSBV4 Index using Dimension /CPMB/UDIQSBV5 Index using Dimension /CPMB/UDIQSBV6 Index using Dimension /CPMB/UDIQSBV7 Index using Dimension /CPMB/UDIQSBV8 Index using Dimension /CPMB/UDIQSBV9 Index using Dimension /CPMB/UDIQSBVA Index using Dimension /CPMB/UDIQSBVB Index using Dimension /CPMB/UDIQSBVC Index using Dimension /CPMB/UDIQSBVD Non-Unique Index (Key Fields) for Table /B28/EUDIQSBV </code></pre> <p>And the table definition itself is as follows: <img src="https://i.stack.imgur.com/W55Jp.png" alt="Table Definition"></p> <p><strong>The query:</strong></p> <pre><code>SELECT "S1080"."/B28/S_UDDZMED" AS "/CPMB/UDDZMED" , "S1081"."/B28/S_UDD8DGN" AS "/CPMB/UDD8DGN" , "S1082"."/B28/S_UDDFNH0" AS "/CPMB/UDDFNH0" , "S1083"."/B28/S_UDDX6I4" AS "/CPMB/UDDX6I4" , "S1084"."/B28/S_UDDXGOI" AS "/CPMB/UDDXGOI" , "S1085"."/B28/S_UDD741B" AS "/CPMB/UDD741B" , "S1086"."/B28/S_UDDC4PW" AS "/CPMB/UDDC4PW" , "S1087"."/B28/S_UDD8UPI" AS "/CPMB/UDD8UPI" , "S1088"."/B28/S_UDD94IW" AS "/CPMB/UDD94IW" , "S1089"."/B28/S_UDDCD88" AS "/CPMB/UDDCD88" , "S1090"."/B28/S_UDDDY4S" AS "/CPMB/UDDDY4S" , "S1091"."/B28/S_UDDCQ0M" AS "/CPMB/UDDCQ0M" , "S1092"."/B28/S_UDDHZNC" AS"/CPMB/UDDHZNC" , SUM ("F"."/B28/S_SDATA") AS "/CPMB/SDATA" FROM "/B28/EUDIQSBV" "F" JOIN "/B28/DUDIQSBVP" "DP" ON "F"."KEY_UDIQSBVP" = "DP"."DIMID" JOIN "/BI0/0600000051" "Z10" ON "F"."KEY_UDIQSBV4" = "Z10"."SID" JOIN "/B28/SUDDZMED" "S1080" ON "F"."KEY_UDIQSBV1" = "S1080"."SID" JOIN "/B28/SUDD8DGN" "S1081" ON "F"."KEY_UDIQSBV2" = "S1081"."SID" JOIN "/B28/SUDDFNH0" "S1082" ON "F"."KEY_UDIQSBV3" = "S1082"."SID" JOIN "/B28/SUDDX6I4" "S1083" ON "F"."KEY_UDIQSBV4" = "S1083"."SID" JOIN "/B28/SUDDXGOI" "S1084" ON "F"."KEY_UDIQSBV5" = "S1084"."SID" JOIN "/B28/SUDD741B" "S1085" ON "F"."KEY_UDIQSBV6" = "S1085"."SID" JOIN "/B28/SUDDC4PW" "S1086" ON "F"."KEY_UDIQSBV7" = "S1086"."SID" JOIN "/B28/SUDD8UPI" "S1087" ON "F"."KEY_UDIQSBV8" = "S1087"."SID" JOIN "/B28/SUDD94IW" "S1088" ON "F"."KEY_UDIQSBV9" = "S1088"."SID" JOIN "/B28/SUDDCD88" "S1089" ON "F"."KEY_UDIQSBVA" = "S1089"."SID" JOIN "/B28/SUDDDY4S" "S1090" ON "F"."KEY_UDIQSBVB" = "S1090"."SID" JOIN "/B28/SUDDCQ0M" "S1091" ON "F"."KEY_UDIQSBVC" = "S1091"."SID" JOIN "/B28/DUDIQSBVD" "DD" ON "F"."KEY_UDIQSBVD" = "DD"."DIMID" JOIN "/B28/SUDDHZNC" "S1092" ON "DD"."/B28/S_UDDHZNC" = "S1092"."SID" WHERE (((("F"."KEY_UDIQSBV6" = 28)) AND (("F"."KEY_UDIQSBV2" = 40)) AND (("F"."KEY_UDIQSBV8" = 2)) AND (("F"."KEY_UDIQSBV9" IN (2 , 3 , 5 , 7, 8 , 10 , 11 , 13 , 14 , 15 , 16 , 18 , 19 , 20 , 21))) AND (("F"."KEY_UDIQSBV7" IN (2 ,3 ,4 ,5,6 ,7 ,8 ,9 ,10 ,11, 12 ,13 ,15 ,17 , 18 , 19 , 20))) AND (("F"."KEY_UDIQSBVA" = 153)) AND (("F"."KEY_UDIQSBVC" IN (7 ,9 , 17 , 18 ,19 ,20,26 , 27 ,28 , 29 ,30 , 31 , 33 ,35 ,37 ,39 ,40 ,41 ,43 ,44 ,46))) AND (("F"."KEY_UDIQSBVB" IN (15 , 17 ,18 ,19 ,22 ,23 ,24 , 25 ,26 ,28 ,30 ,31 ,32 ,33 ,34 ,35 ,36 ,37 ,38 ,39 ,40 , 42 ,43 ,44 ,45 ,46 ,47 ,48 ,51 ,54 ,55 ,59 ,60 ,63 ,64 , 65 ,66 ,67 ,68 ,69 ,70 ,72 ,73 ,74 ,75))) AND (("F"."KEY_UDIQSBV3" IN (3 ,5 ,13 ,16 ,21 ,25 ,26 ,31 , 32 ,33,39 ,40 ,41 ,42 ,43 ,44 ,48 ,53 ,54 ,55 ,56 ,57 , 59 ,60 ,63 ,67 ,72 ,73 ,76 ,79))) AND (("DD"."/B28/S_UDDHZNC" = 360)) AND (("F"."KEY_UDIQSBV5" IN (2 ,3 ,4 ,7 ,8))) AND (("F"."KEY_UDIQSBV1" IN (59 ,190))) AND (("DP"."SID_0CHNGID" = 0)) AND (("DP"."SID_0RECORDTP" = 0)) AND (("DP"."SID_0REQUID" <= 122208)))) AND "Z10"."SID" <> 2000008999 GROUP BY "S1080"."/B28/S_UDDZMED", "S1081"."/B28/S_UDD8DGN", "S1082"."/B28/S_UDDFNH0", "S1083"."/B28/S_UDDX6I4", "S1084"."/B28/S_UDDXGOI", "S1085"."/B28/S_UDD741B", "S1086"."/B28/S_UDDC4PW", "S1087"."/B28/S_UDD8UPI", "S1088"."/B28/S_UDD94IW", "S1089"."/B28/S_UDDCD88", "S1090"."/B28/S_UDDDY4S", "S1091"."/B28/S_UDDCQ0M", "S1092"."/B28/S_UDDHZNC" </code></pre> <p><strong>And here is the explain plan:</strong></p> <pre><code> SELECT STATEMENT ( Estimated Costs = 117,703 , Estimated #Rows = 2,613,944 ) | --- 91 TEMP TABLE TRANSFORMATION | |-- 6 LOAD AS SELECT SYS_TEMP_0FD9D6DF0_8B5A52DE | | | --- 5 NESTED LOOPS | | ( Estim. Costs = 2 , Estim. #Rows = 1 ) | | Estim. CPU-Costs = 23,804 Estim. IO-Costs = 2 | | | |-- 2 TABLE ACCESS BY INDEX ROWID /B28/SUDDHZNC | | | ( Estim. Costs = 1 , Estim. #Rows = 1 ) | | | Estim. CPU-Costs = 15,463 Estim. IO-Costs = 1 | | | | | ------1 INDEX UNIQUE SCAN /B28/SUDDHZNC~001 | | ( Estim. Costs = 1 , Estim. #Rows = 1 ) | | Search Columns: 1 | | Estim. CPU-Costs = 8,171 Estim. IO-Costs = 1 | | Access Predicates | | | --- 4 TABLE ACCESS BY INDEX ROWID /B28/DUDIQSBVD | | ( Estim. Costs = 1 , Estim. #Rows = 1 ) | | Estim. CPU-Costs = 8,341 Estim. IO-Costs = 1 | | | ------3 INDEX RANGE SCAN /B28/DUDIQSBVD~010 | Search Columns: 1 | Estim. CPU-Costs = 1,050 Estim. IO-Costs = 0 | Access Predicates | --- 90 HASH GROUP BY | ( Estim. Costs = 117,701 , Estim. #Rows = 2,613,944 ) | Estim. CPU-Costs = 9,692,785,028 Estim. IO-Costs = 117,393 | --- 89 HASH JOIN | ( Estim. Costs = 1,018 , Estim. #Rows = 2,613,944 ) | Estim. CPU-Costs = 4,919,768,467 Estim. IO-Costs = 862 | Access Predicates | |-- 11 VIEW index$_join$_026 | | ( Estim. Costs = 2 , Estim. #Rows = 71 ) | | Estim. CPU-Costs = 15,820,708 Estim. IO-Costs = 1 | | Filter Predicates | | | --- 10 HASH JOIN | | Access Predicates | | | |-- 8 INLIST ITERATOR | | | | | ------7 INDEX UNIQUE SCAN /B28/SUDDDY4S~001 | | Search Columns: 1 | | Estim. CPU-Costs = 478,575 Estim. IO-Costs = 0 | | Access Predicates | | | ------9 INDEX FAST FULL SCAN /B28/SUDDDY4S~0 | ( Estim. Costs = 1 , Estim. #Rows = 71 ) | Estim. CPU-Costs = 26,652 Estim. IO-Costs = 1 | --- 88 HASH JOIN | ( Estim. Costs = 1,008 , Estim. #Rows = 2,613,944 ) | Estim. CPU-Costs = 4,626,808,323 Estim. IO-Costs = 861 | Access Predicates | |-- 16 VIEW index$_join$_006 | | ( Estim. Costs = 2 , Estim. #Rows = 257 ) | | Estim. CPU-Costs = 15,859,258 Estim. IO-Costs = 1 | | Filter Predicates | | | --- 15 HASH JOIN | | Access Predicates | | | |-- 13 INLIST ITERATOR | | | | | ------12 INDEX UNIQUE SCAN /B28/SUDDZMED~001 | | Search Columns: 1 | | Estim. CPU-Costs = 71,490 Estim. IO-Costs = 0 | | Access Predicates | | | ------14 INDEX FAST FULL SCAN /B28/SUDDZMED~0 | ( Estim. Costs = 1 , Estim. #Rows = 257 ) | Estim. CPU-Costs = 73,152 Estim. IO-Costs = 1 | --- 87 HASH JOIN | ( Estim. Costs = 998 , Estim. #Rows = 2,613,944 ) | Estim. CPU-Costs = 4,333,781,729 Estim. IO-Costs = 860 | Access Predicates | |-----17 TABLE ACCESS FULL /B28/SUDDX6I4 | ( Estim. Costs = 7 , Estim. #Rows = 7,126 ) | Estim. CPU-Costs = 1,460,670 Estim. IO-Costs = 7 | Filter Predicates --- 86 HASH JOIN | ( Estim. Costs = 982 , Estim. #Rows = 2,613,944 ) | Estim. CPU-Costs = 4,054,123,372 Estim. IO-Costs = 853 | Access Predicates | |-- 22 VIEW index$_join$_010 | | ( Estim. Costs = 2 , Estim. #Rows = 82 ) | | Estim. CPU-Costs = 15,809,908 Estim. IO-Costs = 1 | | Filter Predicates | | | --- 21 HASH JOIN | | Access Predicates | | | |-- 19 INLIST ITERATOR | | | | | ------18 INDEX UNIQUE SCAN /B28/SUDDFNH0~001 | | Search Columns: 1 | | Estim. CPU-Costs = 363,600 Estim. IO-Costs = 0 | | Access Predicates | | | ------20 INDEX FAST FULL SCAN /B28/SUDDFNH0~0 | ( Estim. Costs = 1 , Estim. #Rows = 82 ) | Estim. CPU-Costs = 29,402 Estim. IO-Costs = 1 | --- 85 HASH JOIN | ( Estim. Costs = 972 , Estim. #Rows = 2,613,944 ) | Estim. CPU-Costs = 3,761,172,378 Estim. IO-Costs = 852 | Access Predicates | |-----23 TABLE ACCESS FULL /BI0/0600000051 | ( Estim. Costs = 2 , Estim. #Rows = 3,013 ) | Estim. CPU-Costs = 638,207 Estim. IO-Costs = 2 | Filter Predicates --- 84 HASH JOIN | ( Estim. Costs = 961 , Estim. #Rows = 2,614,378 ) | Estim. CPU-Costs = 3,482,910,035 Estim. IO-Costs = 850 | Access Predicates | |-- 28 VIEW index$_join$_028 | | ( Estim. Costs = 2 , Estim. #Rows = 46 ) | | Estim. CPU-Costs = 15,784,258 Estim. IO-Costs = 1 | | Filter Predicates | | | --- 27 HASH JOIN | | Access Predicates | | | |-- 25 INLIST ITERATOR | | | | | ------24 INDEX UNIQUE SCAN /B28/SUDDCQ0M~001 | | Search Columns: 1 | | Estim. CPU-Costs = 152,460 Estim. IO-Costs = 0 | | Access Predicates | | | ------26 INDEX FAST FULL SCAN /B28/SUDDCQ0M~0 | ( Estim. Costs = 1 , Estim. #Rows = 46 ) | Estim. CPU-Costs = 20,402 Estim. IO-Costs = 1 | --- 83 HASH JOIN | ( Estim. Costs = 950 , Estim. #Rows = 2,614,378 ) | Estim. CPU-Costs = 3,189,946,691 Estim. IO-Costs = 849 | Access Predicates | |-- 33 VIEW index$_join$_018 | | ( Estim. Costs = 2 , Estim. #Rows = 21 ) | | Estim. CPU-Costs = 15,768,808 Estim. IO-Costs = 1 | | Filter Predicates | | | --- 32 HASH JOIN | | Access Predicates | | | |-- 30 INLIST ITERATOR | | | | | ------29 INDEX UNIQUE SCAN /B28/SUDDC4PW~001 | | Search Columns: 1 | | Estim. CPU-Costs = 66,045 Estim. IO-Costs = 0 | | Access Predicates | | | ------31 INDEX FAST FULL SCAN /B28/SUDDC4PW~0 | ( Estim. Costs = 1 , Estim. #Rows = 21 ) | Estim. CPU-Costs = 14,152 Estim. IO-Costs = 1 | --- 82 HASH JOIN | ( Estim. Costs = 940 , Estim. #Rows = 2,614,378 ) | Estim. CPU-Costs = 2,897,002,547 Estim. IO-Costs = 848 | Access Predicates | |-- 38 VIEW index$_join$_022 | | ( Estim. Costs = 2 , Estim. #Rows = 20 ) | | Estim. CPU-Costs = 15,766,258 Estim. IO-Costs = 1 | | Filter Predicates | | | --- 37 HASH JOIN | | Access Predicates | | | |-- 35 INLIST ITERATOR | | | | | ------34 INDEX UNIQUE SCAN /B28/SUDD94IW~001 | | Search Columns: 1 | | Estim. CPU-Costs = 56,250 Estim. IO-Costs = 0 | | Access Predicates | | | ------36 INDEX FAST FULL SCAN /B28/SUDD94IW~0 | ( Estim. Costs = 1 , Estim. #Rows = 20 ) | Estim. CPU-Costs = 13,902 Estim. IO-Costs = 1 | --- 81 HASH JOIN | ( Estim. Costs = 930 , Estim. #Rows = 2,614,378 ) | Estim. CPU-Costs = 2,604,061,103 Estim. IO-Costs = 847 | Access Predicates | |-- 43 VIEW index$_join$_014 | | ( Estim. Costs = 2 , Estim. #Rows = 8 ) | | Estim. CPU-Costs = 15,750,358 Estim. IO-Costs = 1 | | Filter Predicates | | | --- 42 HASH JOIN | | Access Predicates | | | |-- 40 INLIST ITERATOR | | | | | ------39 INDEX UNIQUE SCAN /B28/SUDDXGOI~001 | | Search Columns: 1 | | Estim. CPU-Costs = 10,650 Estim. IO-Costs = 0 | | Access Predicates | | | ------41 INDEX FAST FULL SCAN /B28/SUDDXGOI~0 | ( Estim. Costs = 1 , Estim. #Rows = 8 ) | Estim. CPU-Costs = 10,902 Estim. IO-Costs = 1 | --- 80 HASH JOIN | ( Estim. Costs = 919 , Estim. #Rows = 2,614,378 ) | Estim. CPU-Costs = 2,311,137,359 Estim. IO-Costs = 846 | Access Predicates | |-----44 TABLE ACCESS FULL SYS_TEMP_0FD9D6DF0_8B5A52DE | ( Estim. Costs = 2 , Estim. #Rows = 1 ) | Estim. CPU-Costs = 7,291 Estim. IO-Costs = 2 --- 79 HASH JOIN | ( Estim. Costs = 909 , Estim. #Rows = 2,614,378 ) | Estim. CPU-Costs = 2,033,957,731 Estim. IO-Costs = 844 | Access Predicates | |-- 57 NESTED LOOPS | | ( Estim. Costs = 6 , Estim. #Rows = 5 ) | | Estim. CPU-Costs = 71,420 Estim. IO-Costs = 6 | | | |-- 55 NESTED LOOPS | | | ( Estim. Costs = 4 , Estim. #Rows = 1 ) | | | Estim. CPU-Costs = 33,366 Estim. IO-Costs = 4 | | | | | |-- 52 NESTED LOOPS | | | | ( Estim. Costs = 3 , Estim. #Rows = 1 ) | | | | Estim. CPU-Costs = 25,024 Estim. IO-Costs = 3 | | | | | | | |-- 49 NESTED LOOPS | | | | | ( Estim. Costs = 2 , Estim. #Rows = 1 ) | | | | | Estim. CPU-Costs = 16,683 Estim. IO-Costs = 2 | | | | | | | | | |-- 46 TABLE ACCESS BY INDEX ROWID /B28/SUDDCD88 | | | | | | ( Estim. Costs = 1 , Estim. #Rows = 1 ) | | | | | | Estim. CPU-Costs = 8,341 Estim. IO-Costs = 1 | | | | | | | | | | | ------45 INDEX UNIQUE SCAN /B28/SUDDCD88~001 | | | | | Search Columns: 1 | | | | | Estim. CPU-Costs = 1,050 Estim. IO-Costs = 0 | | | | | Access Predicates | | | | | | | | | --- 48 TABLE ACCESS BY INDEX ROWID /B28/SUDD8UPI | | | | | ( Estim. Costs = 1 , Estim. #Rows = 1 ) | | | | | Estim. CPU-Costs = 8,341 Estim. IO-Costs = 1 | | | | | | | | | ------47 INDEX UNIQUE SCAN /B28/SUDD8UPI~001 | | | | Search Columns: 1 | | | | Estim. CPU-Costs = 1,050 Estim. IO-Costs = 0 | | | | Access Predicates | | | | | | | --- 51 TABLE ACCESS BY INDEX ROWID /B28/SUDD741B | | | | ( Estim. Costs = 1 , Estim. #Rows = 1 ) | | | | Estim. CPU-Costs = 8,341 Estim. IO-Costs = 1 | | | | | | | ------50 INDEX UNIQUE SCAN /B28/SUDD741B~001 | | | Search Columns: 1 | | | Estim. CPU-Costs = 1,050 Estim. IO-Costs = 0 | | | Access Predicates | | | | | --- 54 TABLE ACCESS BY INDEX ROWID /B28/SUDD8DGN | | | ( Estim. Costs = 1 , Estim. #Rows = 1 ) | | | Estim. CPU-Costs = 8,341 Estim. IO-Costs = 1 | | | | | ------53 INDEX UNIQUE SCAN /B28/SUDD8DGN~001 | | Search Columns: 1 | | Estim. CPU-Costs = 1,050 Estim. IO-Costs = 0 | | Access Predicates | | | ------56 TABLE ACCESS FULL /B28/DUDIQSBVP | ( Estim. Costs = 2 , Estim. #Rows = 5 ) | Estim. CPU-Costs = 38,054 Estim. IO-Costs = 2 | Filter Predicates | --- 78 TABLE ACCESS BY INDEX ROWID /B28/EUDIQSBV | ( Estim. Costs = 893 , Estim. #Rows = 3,032,059 ) | Estim. CPU-Costs = 1,714,945,175 Estim. IO-Costs = 838 | Filter Predicates | --- 77 BITMAP CONVERSION TO ROWIDS | --- 76 BITMAP AND | |-- 60 BITMAP OR | | | |-----58 BITMAP INDEX SINGLE VALUE /B28/EUDIQSBV~040 | | Search Columns: 1 | | Access Predicates | ------59 BITMAP INDEX SINGLE VALUE /B28/EUDIQSBV~040 | Search Columns: 1 | Access Predicates | |-- 64 BITMAP MERGE | | | --- 63 BITMAP KEY ITERATION | | | |-----61 TABLE ACCESS FULL SYS_TEMP_0FD9D6DF0_8B5A52DE | | ( Estim. Costs = 2 , Estim. #Rows = 1 ) | | Estim. CPU-Costs = 7,271 Estim. IO-Costs = 2 | ------62 BITMAP INDEX RANGE SCAN /B28/EUDIQSBV~160 | Search Columns: 1 | Access Predicates | |-----65 BITMAP INDEX SINGLE VALUE /B28/EUDIQSBV~050 | Search Columns: 1 | Access Predicates |-- 71 BITMAP OR | | | |-----66 BITMAP INDEX SINGLE VALUE /B28/EUDIQSBV~080 | | Search Columns: 1 | | Access Predicates | |-----67 BITMAP INDEX SINGLE VALUE /B28/EUDIQSBV~080 | | Search Columns: 1 | | Access Predicates | |-----68 BITMAP INDEX SINGLE VALUE /B28/EUDIQSBV~080 | | Search Columns: 1 | | Access Predicates | |-----69 BITMAP INDEX SINGLE VALUE /B28/EUDIQSBV~080 | | Search Columns: 1 | | Access Predicates | ------70 BITMAP INDEX SINGLE VALUE /B28/EUDIQSBV~080 | Search Columns: 1 | Access Predicates | --- 75 BITMAP MERGE | --- 74 BITMAP KEY ITERATION | |-----72 TABLE ACCESS FULL /BI0/0600000051 | ( Estim. Costs = 2 , Estim. #Rows = 3,013 ) | Estim. CPU-Costs = 638,207 Estim. IO-Costs = 2 | Filter Predicates ------73 BITMAP INDEX RANGE SCAN /B28/EUDIQSBV~070 Search Columns: 1 Access Predicates Filter Predicates </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload