tab2 - приводится с сокращениями, поскольку в текущем повествовании детали реализации этой части не существенны и не ставится цель оптимизировать ее (это отдельная тема для дискуссии с дизайнером структуры этих таблиц).
with tab1 as
(select 1 prm, '100' s_code, 0 as s_min, 100 as s_max from dual
union all
select 1 prm, '101_150' s_code, 100 as s_min, 150 as s_max from dual
--и так 17 раз)
select
s_code, count(pnf) s_cnt, sum(summ) s_sum, avg(summ) s_avg
from tab1,
(select summ,...
from mpayment,pculars prt, pnfile pf, PnfParams pp, PnfParams pp2
where ...
union
select summ,...
from pslist pl, pculars prt, pnfile, PnfParams pp,PnfParams pp2
where ...) tab2
where
(prm = 1 and summ > s_min and summ <= s_max)
or (prm = 2 and p_tp = s_code)
or (prm = 3 and s_code = 'les_min' and summ < 406)
or (prm = 3 and s_code = 'eq_min' and summ = 406)
or (prm = 3 and s_code = 'mor_min' and summ > 406)
or (prm = 4)
or (prm = 5 and w_st = 1)
group by s_code;
Explain plan для данного запроса выглядит так:
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6539 | 357K| | 1397K (1)| 04:39:26 |
| 1 | HASH GROUP BY | | 6539 | 357K| | 1397K (1)| 04:39:26 |
| 2 | NESTED LOOPS | | 6539 | 357K| | 1397K (1)| 04:39:26 |
| 3 | VIEW | | 37589 | 1468K| | 118K (2)| 00:23:45 |
| 4 | SORT UNIQUE | | 37589 | 2973K| 7448K| 118K (22)| 00:23:45 |
| 5 | UNION-ALL | | | | | | |
|* 6 | FILTER | | | | | | |
|* 7 | HASH JOIN OUTER | | 37588 | 2973K| 2608K| 94545 (3)| 00:18:55 |
|* 8 | FILTER | | | | | | |
|* 9 | HASH JOIN OUTER | | 37588 | 2165K| 1800K| 52872 (3)| 00:10:35 |
|* 10 | HASH JOIN | | 37588 | 1358K| 1360K| 11304 (4)| 00:02:16 |
|* 11 | HASH JOIN | | 37588 | 917K| 1104K| 6823 (6)| 00:01:22 |
|* 12 | TABLE ACCESS FULL | MPAYMENT | 37588 | 660K| | 4404 (8)| 00:00:53 |
|* 13 | TABLE ACCESS FULL | PNFILE | 612K| 4188K| | 1803 (3)| 00:00:22 |
| 14 | TABLE ACCESS FULL | PCULARS | 607K| 7122K| | 3713 (2)| 00:00:45 |
|* 15 | TABLE ACCESS FULL | PNFPARAMS | 544K| 11M| | 40595 (3)| 00:08:08 |
|* 16 | TABLE ACCESS FULL | PNFPARAMS | 584K| 12M| | 40595 (3)| 00:08:08 |
|* 17 | FILTER | | | | | | |
|* 18 | FILTER | | | | | | |
| 19 | NESTED LOOPS OUTER | | 1516 | 130K| | 15118 (1)| 00:03:02 |
|* 20 | FILTER | | | | | | |
| 21 | NESTED LOOPS OUTER | | 1516 | 97K| | 10703 (1)| 00:02:09 |
| 22 | NESTED LOOPS | | 1516 | 66704 | | 6289 (1)| 00:01:16 |
| 23 | MERGE JOIN | | 1516 | 48512 | | 3254 (2)| 00:00:40 |
|* 24 | TABLE ACCESS BY INDEX ROWID | PNFILE | 612K| 4188K| | 1698 (1)| 00:00:21 |
|* 25 | INDEX FULL SCAN | XPK_PТFILE | 30890 | | | 1306 (1)| 00:00:16 |
|* 26 | TABLE ACCESS BY INDEX ROWID| MPAYMENT | 1 | 18 | | 7 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | XIF1561_MPAYMENT | 8 | | | 3 (0)| 00:00:01 |
|* 28 | SORT JOIN | | 1516 | 37900 | | 1555 (3)| 00:00:19 |
|* 29 | TABLE ACCESS FULL | PSLIST | 1516 | 37900 | | 1554 (3)| 00:00:19 |
| 30 | TABLE ACCESS BY INDEX ROWID | PCULARS | 1 | 12 | | 2 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | XAK1_PCULARS | 1 | | | 1 (0)| 00:00:01 |
|* 32 | TABLE ACCESS BY INDEX ROWID | PNFPARAMS | 1 | 22 | | 3 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | IDX$$_0EFF0002 | 1 | | | 2 (0)| 00:00:01 |
|* 34 | TABLE ACCESS BY INDEX ROWID | PNFPARAMS | 1 | 22 | | 3 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | IDX$$_0EFF0002 | 1 | | | 2 (0)| 00:00:01 |
| 36 | SORT AGGREGATE | | 1 | 15 | | | |
|* 37 | TABLE ACCESS BY INDEX ROWID | PSLIST | 1 | 15 | | 4 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN | IFK_PNL_PNF | 1 | | | 3 (0)| 00:00:01 |
|* 39 | VIEW | | 1 | 16 | | 34 (0)| 00:00:01 |
| 40 | UNION-ALL | | | | | | |
| 41 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 42 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 43 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 44 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 45 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 46 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 47 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 48 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 49 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 50 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 51 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 52 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 53 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 54 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 55 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 56 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 57 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
С 3-го по 38-ой шаги по причинам указанным выше предлагаю не не рассматривать углубленно.
Собственно, зацепкой для дальнейшего поиска был скачок стоимости на шаге 2. Как учил К. Милшап: прилагать усилия нужно в направлении наибольшей отдачи. Поэтому я попытался исследовать именно эту особенность плана. Следующим шагом - собрал статистику времени исполнения, которая должна подтвердить (или опровергнуть) наличие проблемного места в запросе (оставляю только часть плана, которая имеет отношение к дальнейшему изложению):
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 6539 | 17 |00:06:00.33 | 4495K|
| 2 | NESTED LOOPS | | 1 | 6539 | 2448K|00:05:54.91 | 4495K|
| 3 | VIEW | | 1 | 37589 | 590K|00:02:02.82 | 4495K|
--------------------------
пропущено
--------------------------
|* 39 | VIEW | | 590K| 1 | 2448K|00:03:42.54 | 0 |
| 40 | UNION-ALL | | 590K| | 10M|00:03:13.55 | 0 |
| 41 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
| 42 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
| 43 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
| 44 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
| 45 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
| 46 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
| 47 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
| 48 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
| 49 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
| 50 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
| 51 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
| 52 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
| 53 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
| 54 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
| 55 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
| 56 | FAST DUAL | | 590K| 1 | 590K|00:00:03.45 | 0 |
| 57 | FAST DUAL | | 590K| 1 | 590K|00:00:03.44 | 0 |
----------------------------------------------------------------------------------------------------------------------------
Такой отчет получен после исполнения запроса с использованием подсказки gather_plan_statistics и последующего вызова
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
Статистика времени выполнения подтверждает наличие ресурсоемкого шага 2, хотя прогноз оптимизатора был явно преувеличен. Колонка Starts показывает количество запусков соответствующих шагов, очевидно что шаги, связанные с обработкой "синтетической" таблицы достаточно дорогостоящи.
Собранная информация дает повод предположить, что выбраный способ получения агрегатных значений, а именно соединение исходных данных с "синтетической" таблицей с последующей агрегацией, является не эффективным, следовательно от него нужно избавляться.
Сделать это можно достаточно просто: например воспользоваться функцией decode, а в моем случае больше подойдет case expression, так как условия вычисления параметров достаточно сложны. Итак, я предложил своему коллеге переписать запрос вот таким способом:
select
-- Первый показатель
sum(sum_0_100) cnt__0_100,
sum(s_sum_0_100) sum__0_100,
avg(s_sum_0_100) avg__0_100,
-- Второй показатель
sum(sum_100_150) cnt__100_150,
sum(s_sum_100_150) sum__100_150,
avg(s_sum_100_150) avg__100_150,
-- Третий показатель
sum(sum_150_200) cnt__150_200,
sum(s_sum_150_200) sum__150_200,
avg(s_sum_150_200) avg__150_200
-- Остальные оставил коллеге реализовать самостоятельно
from (
select
case when summ<=100 then 1 else 0 end sum_0_100,
case when summ<=100 then summ else null end s_sum_0_100,
case when summ>100 and summ<=150 then 1 else 0 end sum_100_150,
case when summ>100 and summ<=150 then summ else null end s_sum_100_150,
case when summ>150 and summ<=200 then 1 else 0 end sum_150_200,
case when summ>150 and summ<=200 then summ else null end s_sum_150_200
from
(
select summ,...
from mpayment, pculars prt, pnfile pf, PnfParams pp, PnfParams pp2
where ...
union
select summ,...
from pslist pl, pculars prt, pnfile, PnfParams pp, PnfParams pp2
where ...
);
в итоге план времени выполнения имеет вид
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:01:28.50 | 1447K|
| 2 | VIEW | | 1 | 37589 | 590K|00:01:26.29 | 1447K|
| 3 | SORT UNIQUE | | 1 | 37589 | 590K|00:01:25.11 | 1447K|
| 4 | UNION-ALL | | 1 | | 590K|00:01:21.17 | 1447K|
|* 5 | FILTER | | 1 | | 501K|00:00:42.74 | 421K|
|* 6 | HASH JOIN OUTER | | 1 | 37588 | 501K|00:00:41.25 | 421K|
|* 7 | FILTER | | 1 | | 501K|00:00:30.47 | 232K|
|* 8 | HASH JOIN OUTER | | 1 | 37588 | 501K|00:00:28.48 | 232K|
|* 9 | HASH JOIN | | 1 | 37588 | 501K|00:00:17.50 | 43938 |
|* 10 | HASH JOIN | | 1 | 37588 | 501K|00:00:10.25 | 26878 |
|* 11 | TABLE ACCESS FULL | MPAYMENT | 1 | 37588 | 502K|00:00:02.05 | 18797 |
|* 12 | TABLE ACCESS FULL | PNFILE | 1 | 612K| 612K|00:00:01.23 | 8081 |
| 13 | TABLE ACCESS FULL | PCULARS | 1 | 607K| 617K|00:00:01.24 | 17060 |
|* 14 | TABLE ACCESS FULL | PNFPARAMS | 1 | 544K| 555K|00:00:04.81 | 188K|
|* 15 | TABLE ACCESS FULL | PNFPARAMS | 1 | 584K| 555K|00:00:05.02 | 188K|
|* 16 | FILTER | | 1 | | 89790 |00:00:35.03 | 1025K|
| 17 | HASH GROUP BY | | 1 | 1 | 90274 |00:00:34.76 | 1025K|
|* 18 | HASH JOIN | | 1 | 1430 | 90900 |00:00:33.39 | 1025K|
|* 19 | FILTER | | 1 | | 92035 |00:00:28.88 | 1018K|
| 20 | NESTED LOOPS OUTER | | 1 | 1426 | 92041 |00:00:28.51 | 1018K|
|* 21 | FILTER | | 1 | | 92035 |00:00:24.55 | 686K|
| 22 | NESTED LOOPS OUTER | | 1 | 1426 | 92114 |00:00:24.06 | 686K|
| 23 | NESTED LOOPS | | 1 | 1426 | 92035 |00:00:19.49 | 354K|
|* 24 | HASH JOIN ANTI | | 1 | 1426 | 92035 |00:00:16.45 | 33816 |
|* 25 | HASH JOIN | | 1 | 1516 | 590K|00:00:08.99 | 15001 |
|* 26 | TABLE ACCESS FULL | PSLIST | 1 | 1516 | 595K|00:00:01.19 | 6927 |
|* 27 | TABLE ACCESS FULL | PNFILE | 1 | 612K| 612K|00:00:01.23 | 8074 |
|* 28 | TABLE ACCESS FULL | MPAYMENT | 1 | 37588 | 502K|00:00:01.56 | 18815 |
| 29 | TABLE ACCESS BY INDEX ROWID| PCULARS | 92035 | 1 | 92035 |00:00:02.56 | 320K|
|* 30 | INDEX UNIQUE SCAN | XAK1_PCULARS | 92035 | 1 | 92035 |00:00:01.15 | 184K|
|* 31 | TABLE ACCESS BY INDEX ROWID | PNFPARAMS | 92035 | 1 | 54328 |00:00:03.84 | 332K|
|* 32 | INDEX RANGE SCAN | IDX$$_0EFF0002 | 92035 | 1 | 54629 |00:00:01.75 | 277K|
|* 33 | TABLE ACCESS BY INDEX ROWID | PNFPARAMS | 92035 | 1 | 54255 |00:00:03.48 | 331K|
|* 34 | INDEX RANGE SCAN | IDX$$_0EFF0002 | 92035 | 1 | 54566 |00:00:01.55 | 277K|
|* 35 | TABLE ACCESS FULL | PSLIST | 1 | 604K| 604K|00:00:01.21 | 6925 |
----------------------------------------------------------------------------------------------------------------------
Собственно, план стал легче на выброшенную часть запроса.
Можно сказать что поставленная цель достигнута, ибо, как указывал выше, часть запроса, поставляющая исходные данные для агрегации - пока вне критики по известным причинам.
P.S. Описанные выше упражнения происходили на версии Oracle 10gR2 (10.2.0.1).
No comments:
Post a Comment