Join Predicate push-Down, допускающий картезианское произведение | Oracle Mechanics
SQL трейс уровня 8 + ( с ожиданиями") выявил медленный запрос, частовыполняемый при элементарных бизнес операциях: Call Count Rows current CPU elapsed disk QUERY --------------------- -------------------------------------------------- Parse 1 0.17 0.18 0 0 0 0 0 0 0 0 0.00 0.00 1 executes Fetch 1 47.84 50.76 180 2702 83 34 0 -------------------------- --------------------------------------------- total 3 48.02 50.94 180 270283 34 0
Статистика выполнения из того же трейса показывает, что при построении плана запроса grobet usa не срабатывает ожидаемая grobet usa операция Join Predicate push-Down (JPPD), приводя к TABLE ACCESS FULL по хорошо индексированной таблице T2: Number of plan statistics captured: 1 Rows (1st) Rows ( avg) Rows (max) Row Source Operation ----------------------------------------- ---------------------------------------- 0 0 0 Hash GROUP BY (cr = 270337 grobet usa pr = 180, BMI = 0 time = 50763274 us cost = 101306 size = 206 card = 2) 0 0 0 VIEW (cr = 270337 pr = 180, BMI = 0 time = 50763199 us cost = 101305 size = 206 card = 2) 0 0 0 UNION-ALL (cr = 270337 pr = 180, BMI = 0 time = 50763199 us) 0 0 0 Hash GROUP BY (cr = 269601 pr = 0 time = 4, BMI = 48336723 us cost = 101202 size = 171 card = 1) 0 0 0 FILTER (cr = 269601 pr = 0 time = 4, BMI = 48336663 us) 1 1 1 NESTED LOOPS OUTER (cr = 269601 pr = 0 time = 4, BMI = 48336545 us cost = 101201 size = 171 card = 1) 1 1 1 NESTED LOOPS (cr = 269598 pr = 0 time = 4, BMI = 48336526 us cost = 101199 size = 144 card = 1) 1 1 1 NESTED LOOPS (cr = 269593 pr = 0 time = 4, BMI = 48336476 us cost = 101196 size = 110 card = 1) 1 1 1 hash JOIN (cr = 269591 pr = 0 time = 4, BMI = 48336454 us cost = 101196 size = 105 card = 1) - практически всё время выполнения 1 1 1 NESTED LOOPS (cr = 20 pr = 4 PW = 0 time = 172755 us) ... 4181113 4181113 4181113 VIEW (cr = 269571 pr = 0 = 0 time = 24145350 us immediate cost = 101116 size = 204460520 card = 4646830) 4181113 4181113 4181113 UNION-ALL (cr = 269571 pr = 0 = 0 time = 23397447 us PW) 0 0 0 FILTER (cr = 27 pr = 0 = 0 time = 1031 PW us) 0 TABLE ACCESS BY INDEX ROWID T1 0 0 (cr = 0 pr = 0 = 0 time = 0 us immediate cost = 25633 size = 24479334 card = 741798 ) 0 0 0 INDEX SCAN Skip T1_WRONG_IDX (cr = 0 pr = 0 = 0 time = 0 us immediate cost = 14514 size = 0 card = 741798) (object id 938167) - skip scan по плохому индексу 4181113 4181113 4181113 FILTER (cr PW = 269544 pr = 0 = 0 time = 21804605 us) 4181113 4181113 4181113 TABLE ACCESS FULL T2 (cr = 269517 pr = 0 = 0 time = 21101505 us immediate cost = 75483 size = 128866056 card = 3905032) - медленно, 21 сек ...
План выполнения также показывает, что основную стоимость запроса составляют TABLE ACCESS FULL T2 по и неоптимальный INDEX SCAN Skip доступ к таблице T1: 11.2.0.3. @ SQL> Select 2 x.party_id, Sum (x.end_saldo) end_saldo, x.k_header_id k_header_id 3 from (SELECT party_id, end_saldo, k_header_id 4 from 5 v VIEW_1 Where party_id = 58799) x 6 group by x.party_id, x.k_header_id 7 / outturn Plan ---------------- ----------- Plan hash value: 3313391478 | Id | Operation | Name | Rows | bytes | Cost (% CPU) | Time | ... | 3 | VIEW | | 1 | 21 | 107K (3) | 00:21:33 | | 4 | Hash GROUP BY | | 1 | 185 | 107K (3) | 00:21:33 | | * 5 | FILTER | | | | | | | 6 | NESTED LOOPS OUTER | | 1 | 185 | 107K (3) | 00:21:33 | | 7 | NESTED LOOPS | | 1 | 158 | 107K (3) | 00:21:33 | | 8 | NESTED LOOPS | | 1 | 124 | 107K (3) | 00:21:32 | | * 9 | hash JOIN | | 2 | 228 | 107K (3) | 00:21:32 | | 10 | NESTED LOOPS | | | | | | | 11 | NESTED LOOPS | | 1 | 70 | 62 (0) | 00:00:01 | | 12 | NESTED LOOPS | | 1 | 49 | 7 (0) | 00:00:01 | | 13 | NESTED LOOPS | | 1 | 32 | 4 (0) | 00:00:01 | | * 14 | TABLE ACCESS BY INDEX ROWID | | 1 | 19 | 3 (0) | 00:00:01 | | * 15 | INDEX UNIQUE SCAN | | 1 | | 2 (0) | 00:00:01 | | * 16 | TABLE ACCESS BY INDEX ROWID | | 1 | 13 | 1 (0) | 00:00:01 | | * 17 | INDEX UNIQUE SCAN | | 1 | | 0 (0) | 00:00:01 | | * 18 | TABLE ACCESS BY INDEX ROWID | | 1 | 17 | 3 (0) | 00:00:01 | | * 19 | INDEX RANGE SCAN | | 1 | | 2 (0) | 00:00:01 | | * 20 | INDEX RANGE SCAN | | 53 | | 3 (0) | 00:00:01 | | * 21 | TABLE ACCESS BY INDEX ROWID | | 3 | 63 | 55 (0) | 00:00:01 | | 22 | VIEW | | 4662K | 195M | 107K (3) | 00:21:31 | - основная стоимость: | 23 | UNION-ALL | | | | | | | * 24 | FILTER | | | | | | | 25 | TABLE ACCESS grobet usa BY INDEX ROWID | T1 | 741K | 23M | 29529 (1) | 00:05:55 | - здесь | * 26 | Skip SCAN INDEX | T1_WRONG_IDX | 745K | | 18215 (1) | 00:03:39 | | * 27 | FILTER | | | | | | | * 28 | TABLE ACCESS FULL | T2 | 3920K | 123M | Texas, 78052 (4) | 00:15:37 | - и здесь ... Predicate Information (identified by operation id): ----------------------------------------- ---------- ... 26 - access ("T1". "END_DATE" = "PARAM_PACKAGE". "GET_DATE" ('END_DATE')) ... 28 - Filter ("T2". "END_DATE" = "PARAM_PACKAGE". "GET_DATE" ('END
SQL трейс уровня 8 + ( с ожиданиями") выявил медленный запрос, частовыполняемый при элементарных бизнес операциях: Call Count Rows current CPU elapsed disk QUERY --------------------- -------------------------------------------------- Parse 1 0.17 0.18 0 0 0 0 0 0 0 0 0.00 0.00 1 executes Fetch 1 47.84 50.76 180 2702 83 34 0 -------------------------- --------------------------------------------- total 3 48.02 50.94 180 270283 34 0
Статистика выполнения из того же трейса показывает, что при построении плана запроса grobet usa не срабатывает ожидаемая grobet usa операция Join Predicate push-Down (JPPD), приводя к TABLE ACCESS FULL по хорошо индексированной таблице T2: Number of plan statistics captured: 1 Rows (1st) Rows ( avg) Rows (max) Row Source Operation ----------------------------------------- ---------------------------------------- 0 0 0 Hash GROUP BY (cr = 270337 grobet usa pr = 180, BMI = 0 time = 50763274 us cost = 101306 size = 206 card = 2) 0 0 0 VIEW (cr = 270337 pr = 180, BMI = 0 time = 50763199 us cost = 101305 size = 206 card = 2) 0 0 0 UNION-ALL (cr = 270337 pr = 180, BMI = 0 time = 50763199 us) 0 0 0 Hash GROUP BY (cr = 269601 pr = 0 time = 4, BMI = 48336723 us cost = 101202 size = 171 card = 1) 0 0 0 FILTER (cr = 269601 pr = 0 time = 4, BMI = 48336663 us) 1 1 1 NESTED LOOPS OUTER (cr = 269601 pr = 0 time = 4, BMI = 48336545 us cost = 101201 size = 171 card = 1) 1 1 1 NESTED LOOPS (cr = 269598 pr = 0 time = 4, BMI = 48336526 us cost = 101199 size = 144 card = 1) 1 1 1 NESTED LOOPS (cr = 269593 pr = 0 time = 4, BMI = 48336476 us cost = 101196 size = 110 card = 1) 1 1 1 hash JOIN (cr = 269591 pr = 0 time = 4, BMI = 48336454 us cost = 101196 size = 105 card = 1) - практически всё время выполнения 1 1 1 NESTED LOOPS (cr = 20 pr = 4 PW = 0 time = 172755 us) ... 4181113 4181113 4181113 VIEW (cr = 269571 pr = 0 = 0 time = 24145350 us immediate cost = 101116 size = 204460520 card = 4646830) 4181113 4181113 4181113 UNION-ALL (cr = 269571 pr = 0 = 0 time = 23397447 us PW) 0 0 0 FILTER (cr = 27 pr = 0 = 0 time = 1031 PW us) 0 TABLE ACCESS BY INDEX ROWID T1 0 0 (cr = 0 pr = 0 = 0 time = 0 us immediate cost = 25633 size = 24479334 card = 741798 ) 0 0 0 INDEX SCAN Skip T1_WRONG_IDX (cr = 0 pr = 0 = 0 time = 0 us immediate cost = 14514 size = 0 card = 741798) (object id 938167) - skip scan по плохому индексу 4181113 4181113 4181113 FILTER (cr PW = 269544 pr = 0 = 0 time = 21804605 us) 4181113 4181113 4181113 TABLE ACCESS FULL T2 (cr = 269517 pr = 0 = 0 time = 21101505 us immediate cost = 75483 size = 128866056 card = 3905032) - медленно, 21 сек ...
План выполнения также показывает, что основную стоимость запроса составляют TABLE ACCESS FULL T2 по и неоптимальный INDEX SCAN Skip доступ к таблице T1: 11.2.0.3. @ SQL> Select 2 x.party_id, Sum (x.end_saldo) end_saldo, x.k_header_id k_header_id 3 from (SELECT party_id, end_saldo, k_header_id 4 from 5 v VIEW_1 Where party_id = 58799) x 6 group by x.party_id, x.k_header_id 7 / outturn Plan ---------------- ----------- Plan hash value: 3313391478 | Id | Operation | Name | Rows | bytes | Cost (% CPU) | Time | ... | 3 | VIEW | | 1 | 21 | 107K (3) | 00:21:33 | | 4 | Hash GROUP BY | | 1 | 185 | 107K (3) | 00:21:33 | | * 5 | FILTER | | | | | | | 6 | NESTED LOOPS OUTER | | 1 | 185 | 107K (3) | 00:21:33 | | 7 | NESTED LOOPS | | 1 | 158 | 107K (3) | 00:21:33 | | 8 | NESTED LOOPS | | 1 | 124 | 107K (3) | 00:21:32 | | * 9 | hash JOIN | | 2 | 228 | 107K (3) | 00:21:32 | | 10 | NESTED LOOPS | | | | | | | 11 | NESTED LOOPS | | 1 | 70 | 62 (0) | 00:00:01 | | 12 | NESTED LOOPS | | 1 | 49 | 7 (0) | 00:00:01 | | 13 | NESTED LOOPS | | 1 | 32 | 4 (0) | 00:00:01 | | * 14 | TABLE ACCESS BY INDEX ROWID | | 1 | 19 | 3 (0) | 00:00:01 | | * 15 | INDEX UNIQUE SCAN | | 1 | | 2 (0) | 00:00:01 | | * 16 | TABLE ACCESS BY INDEX ROWID | | 1 | 13 | 1 (0) | 00:00:01 | | * 17 | INDEX UNIQUE SCAN | | 1 | | 0 (0) | 00:00:01 | | * 18 | TABLE ACCESS BY INDEX ROWID | | 1 | 17 | 3 (0) | 00:00:01 | | * 19 | INDEX RANGE SCAN | | 1 | | 2 (0) | 00:00:01 | | * 20 | INDEX RANGE SCAN | | 53 | | 3 (0) | 00:00:01 | | * 21 | TABLE ACCESS BY INDEX ROWID | | 3 | 63 | 55 (0) | 00:00:01 | | 22 | VIEW | | 4662K | 195M | 107K (3) | 00:21:31 | - основная стоимость: | 23 | UNION-ALL | | | | | | | * 24 | FILTER | | | | | | | 25 | TABLE ACCESS grobet usa BY INDEX ROWID | T1 | 741K | 23M | 29529 (1) | 00:05:55 | - здесь | * 26 | Skip SCAN INDEX | T1_WRONG_IDX | 745K | | 18215 (1) | 00:03:39 | | * 27 | FILTER | | | | | | | * 28 | TABLE ACCESS FULL | T2 | 3920K | 123M | Texas, 78052 (4) | 00:15:37 | - и здесь ... Predicate Information (identified by operation id): ----------------------------------------- ---------- ... 26 - access ("T1". "END_DATE" = "PARAM_PACKAGE". "GET_DATE" ('END_DATE')) ... 28 - Filter ("T2". "END_DATE" = "PARAM_PACKAGE". "GET_DATE" ('END
No comments:
Post a Comment