Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Возможно ли ускорить запрос  [new]
chikaginsk
Member

Откуда:
Сообщений: 89
Приветствую всех! Есть острая необходимость ускорить выполнение селекта, но пока все попытки (перестраивание запроса, хинты...) закончились неудачей. Прошу помощи в оптимизации, нужно уменьшить время выполнения.
+
select subq1.area ,count(subq2.met_id) N
from 
(
 select t5.area ,t5.sep_id
 from TABLE1 t1 ,TABLE2 t2 ,TABLE3 t3 ,TABLE4 t4 ,TABLE5 t5
 where t1.type = 'RAION   '
   and t1.value in (/*:RAION*/'01-01           ','01-02           ','01-03           ')
   and t1.acnt_id = t2.acnt_id
   and t2.cst_cd = 'FL      '
   and t2.acnt_id = t3.acnt_id
   and t3.status in ('20','40','50')
   and t3.s_id = t4.s_id
   and t4.sep_id = t5.sep_id
   and t5.fst_cd = 'AB   '
   and t5.area in (/*:AREA*/'0101-121','0102-039','0103-067')
) subq1
,TABLE6 t6
,(
 select t7.conf_id ,t7.met_id
 from TABLE7 t7
 where to_char(t7.dt,'yyyyMM') = (('2018'/*:YEAR*/)||('06'/*:MONTH*/))
   and exists (select t8.met_id from TABLE8 t8 where t8.met_id = t7.met_id and t8.type in ('35','40','45','50','60','70','80'))
 group by t7.conf_id ,t7.met_id
) subq2
where t6.sep_id(+) = subq1.sep_id
  and subq2.conf_id = t6.conf_id
group by subq1.area

Если оставить только subq1, то время выполнения (без подсчёта count(subq2.met_id) N) составляет около 6 секунд (PL/SQL Developer выводит записи через это время). Если добавить TABLE6 t6, то время выполнения увеличивается до 9 секунд.
При добавлении subq2 запрос выполняется около 25 минут (отдельно subq2 отрабатывает за 25-30 секунд).
План запроса следующий:
+
--------------------------------------------------------------------------------------------------------------------------
| Id   | Operation                                | Name                   | Rows      | Bytes      | Cost    | Time     |
--------------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                         |                        |       509 |      11707 | 2083580 | 06:56:43 |
|    1 |   HASH GROUP BY                          |                        |       509 |      11707 | 2083580 | 06:56:43 |
|    2 |    VIEW                                  | VM_NWVW_1              |    211413 |    4862499 | 2083580 | 06:56:43 |
|    3 |     HASH GROUP BY                        |                        |    211413 |   55813032 | 2083580 | 06:56:43 |
|    4 |      NESTED LOOPS SEMI                   |                        |    211413 |   55813032 | 2071555 | 06:54:19 |
|  * 5 |       HASH JOIN                          |                        |    211413 |   52430424 | 1228696 | 04:05:45 |
|  * 6 |        HASH JOIN                         |                        |     95922 |   20719152 |  162611 | 00:32:32 |
|  * 7 |         HASH JOIN                        |                        |     69984 |   12737088 |  152835 | 00:30:35 |
|  * 8 |          TABLE ACCESS STORAGE FULL       | TABLE5                 |    564025 |   16920750 |   16619 | 00:03:20 |
|  * 9 |          HASH JOIN                       |                        |    339212 |   51560224 |  132462 | 00:26:30 |
| * 10 |           HASH JOIN                      |                        |    313700 |   37016600 |  113858 | 00:22:47 |
| * 11 |            HASH JOIN                     |                        |    268779 |   21771099 |   44038 | 00:08:49 |
| * 12 |             TABLE ACCESS STORAGE FULL    | TABLE1                 |    268779 |   13170171 |   37264 | 00:07:28 |
| * 13 |             INDEX STORAGE FAST FULL SCAN | CM_ACNT_ID_CST_IDX     |   1792211 |   57350752 |    2259 | 00:00:28 |
| * 14 |            TABLE ACCESS STORAGE FULL     | TABLE3                 |   2091743 |   77394491 |   63780 | 00:12:46 |
|   15 |           TABLE ACCESS STORAGE FULL      | TABLE4                 |   3098168 |  105337712 |    9920 | 00:02:00 |
|   16 |         TABLE ACCESS STORAGE FULL        | TABLE6                 |   2047920 |   69629280 |    4667 | 00:00:57 |
| * 17 |        TABLE ACCESS STORAGE FULL         | TABLE7                 |   4431141 |  141796512 | 1055808 | 03:31:10 |
| * 18 |       TABLE ACCESS BY INDEX ROWID        | TABLE8                 | 109624012 | 1753984192 |       4 | 00:00:01 |
| * 19 |        INDEX RANGE SCAN                  | XT186S1                |         1 |            |       3 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
5 апр 19, 12:13    [21853856]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли ускорить запрос  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 50871
chikaginsk
where to_char(t7.dt,'yyyyMM') = (('2018'/*:YEAR*/)||('06'/*:MONTH*/))
можем выбиться из индекса из-за ту-чара
5 апр 19, 12:16    [21853862]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли ускорить запрос  [new]
chikaginsk
Member

Откуда:
Сообщений: 89
Забыл написать, что параметры :RAION и (особенно) :AREA принимают довольно много значений (десятки и сотни).
5 апр 19, 12:20    [21853870]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли ускорить запрос  [new]
chikaginsk
Member

Откуда:
Сообщений: 89
andreymx,

Поле t7.dt не участвует в индексах. Индексы есть по полям t7.conf_id и t7.met_id (отдельные индексы по каждому из полей).
5 апр 19, 12:24    [21853879]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли ускорить запрос  [new]
feagor
Member

Откуда: Москва
Сообщений: 162
chikaginsk,

без понимания того что вообще делает запрос могу предложить только избавиться от exists в subq2, в котором судя по всему и причина, её видно по
* 18 |       TABLE ACCESS BY INDEX ROWID        | TABLE8                 | 109624012 | 1753984192 |       4 | 00:00:01 |
| * 19 | INDEX RANGE SCAN | XT186S1 | 1 | | 3 | 00:00:01 |
попробуйте
select t7.conf_id ,t7.met_id
 from TABLE7 t7 left join TABLE8 t8 on t7.met_id = t8.met_id and t8.type in ('35','40','45','50','60','70','80')
 where to_char(t7.dt,'yyyyMM') = (('2018'/*:YEAR*/)||('06'/*:MONTH*/))
 group by t7.conf_id ,t7.met_id
5 апр 19, 14:17    [21854045]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли ускорить запрос  [new]
feagor
Member

Откуда: Москва
Сообщений: 162
feagor,

без left, просто join
5 апр 19, 14:19    [21854050]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли ускорить запрос  [new]
Dshedoo
Member

Откуда:
Сообщений: 280
select subq1.area ,count(subq2.met_id) N
from 
     (select t5.area ,t5.sep_id
        from TABLE1 t1 
        join TABLE2 t2 on t1.acnt_id = t2.acnt_id 
                      and t1.type = 'RAION   '
                      and t1.value in (/*:RAION*/'01-01           ','01-02           ','01-03           ')
                      and t2.cst_cd = 'FL      '
        join TABLE3 t3 on t2.acnt_id = t3.acnt_id 
                      and t3.status in ('20','40','50')
        join TABLE4 t4 on t3.s_id = t4.s_id
        join TABLE5 t5 on t4.sep_id = t5.sep_id 
                      and t5.fst_cd = 'AB   '
                      and t5.area in (/*:AREA*/'0101-121','0102-039','0103-067')
      ) subq1 
              join 
     (select t7.conf_id ,t7.met_id, t6.sep_id
        from TABLE7 t7 
        join TABLE6 t6 on t7.conf_id = t6.conf_id
        join TABLE8 t8 on t8.met_id = t7.met_id 
                      and t8.type in ('35','40','45','50','60','70','80')
       where to_char(t7.dt,'yyyyMM') = (('2018'/*:YEAR*/)||('06'/*:MONTH*/))
       group by t7.conf_id, t7.met_id, t6.sep_id
      ) subq2 
              on subq2.sep_id = subq1.sep_id
group by subq1.area
5 апр 19, 14:58    [21854097]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли ускорить запрос  [new]
Dshedoo
Member

Откуда:
Сообщений: 280
Я же правильно понимаю, что

where t6.sep_id(+) = subq1.sep_id
  and subq2.conf_id = t6.conf_id


Равноценно:

subq1 left join t6 on t6.sep_id = subq1.sep_id
           join subq2 on subq2.conf_id = t6.conf_id


А не

subq1 left join 
                t6 join subq2 on subq2.conf_id = t6.conf_id
                              on t6.sep_id = subq1.sep_id

?
5 апр 19, 15:15    [21854126]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли ускорить запрос  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9399
Dshedoo
Я же правильно понимаю, что

?


Неправильно.

where t6.sep_id(+) = subq1.sep_id
  and subq2.conf_id = t6.conf_id


Равноценно:

where t6.sep_id = subq1.sep_id
  and subq2.conf_id = t6.conf_id


Со всеми вытекающими. То что ты видимо хочешь:

where t6.sep_id(+) = subq1.sep_id
  and subq2.conf_id = t6.conf_id(+)


SY.
5 апр 19, 15:36    [21854148]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли ускорить запрос  [new]
Dshedoo
Member

Откуда:
Сообщений: 280
SY
Dshedoo
Я же правильно понимаю, что

?


Неправильно.

where t6.sep_id(+) = subq1.sep_id
  and subq2.conf_id = t6.conf_id


Равноценно:

where t6.sep_id = subq1.sep_id
  and subq2.conf_id = t6.conf_id


Со всеми вытекающими. То что ты видимо хочешь:

where t6.sep_id(+) = subq1.sep_id
  and subq2.conf_id = t6.conf_id(+)


SY.


Эм....
Это же тоже самое, что и я написал о_О
5 апр 19, 15:48    [21854170]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли ускорить запрос  [new]
SeaGate
Member

Откуда: Новосибирск
Сообщений: 1599
chikaginsk,

В текущем плане произошел complex view merging для subq2.
В виду:
chikaginsk
Если добавить TABLE6 t6, то время выполнения увеличивается до 9 секунд.
При добавлении subq2 запрос выполняется около 25 минут (отдельно subq2 отрабатывает за 25-30 секунд).

Стоит проверить производительность с no_merge для subq2:
select subq1.area ,count(subq2.met_id) N
from 
(
 select t5.area ,t5.sep_id
 from TABLE1 t1 ,TABLE2 t2 ,TABLE3 t3 ,TABLE4 t4 ,TABLE5 t5
 where t1.type = 'RAION   '
   and t1.value in (/*:RAION*/'01-01           ','01-02           ','01-03           ')
   and t1.acnt_id = t2.acnt_id
   and t2.cst_cd = 'FL      '
   and t2.acnt_id = t3.acnt_id
   and t3.status in ('20','40','50')
   and t3.s_id = t4.s_id
   and t4.sep_id = t5.sep_id
   and t5.fst_cd = 'AB   '
   and t5.area in (/*:AREA*/'0101-121','0102-039','0103-067')
) subq1
,TABLE6 t6
,(
 select /*+ no_merge*/t7.conf_id ,t7.met_id
 from TABLE7 t7
 where to_char(t7.dt,'yyyyMM') = (('2018'/*:YEAR*/)||('06'/*:MONTH*/))
   and exists (select t8.met_id from TABLE8 t8 where t8.met_id = t7.met_id and t8.type in ('35','40','45','50','60','70','80'))
 group by t7.conf_id ,t7.met_id
) subq2
where t6.sep_id(+) = subq1.sep_id
  and subq2.conf_id = t6.conf_id
group by subq1.area

Если производительность не устроит, то убедиться, что subq2 имеет тот же план отдельно, что и в итоговом запросе.
5 апр 19, 21:13    [21854435]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли ускорить запрос  [new]
xtender
Member

Откуда: Мск
Сообщений: 5067
chikaginsk
Если оставить только subq1, то время выполнения (без подсчёта count(subq2.met_id) N) составляет около 6 секунд (PL/SQL Developer выводит записи через это время).
chikaginsk
Если добавить TABLE6 t6, то время выполнения увеличивается до 9 секунд.
надо проверять и показывать планы со статистиками по выполнению с фетчем всех строк, а не только первых.

chikaginsk
to_char(t7.dt,'yyyyMM') = (('2018'/*:YEAR*/)||('06'/*:MONTH*/))
не мешайте оптимизатору рассчитывать кардинальность, замените это на between.

Для нормального анализа, надо показывать статистику таблиц и планы выполнения со статистиками!
6 апр 19, 00:58    [21854519]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли ускорить запрос  [new]
chikaginsk
Member

Откуда:
Сообщений: 89
andreymx, feagor, Dshedoo, SY, SeaGate, xtender
Благодарю всех за ответы!

feagor,
Проверил все предложенные варианты и остановился на Вашем. Убрал exists и получил значительную прибавку производительности.

SeaGate,
Хинт этот пробовал, но ускорения не было, хотя план изменился.

andreymx, xtender
Убрал to_char с поля даты, переделал на between, разницы в производительности не заметил после этого, но хоть оптимизатору мешать не будет.
9 апр 19, 09:22    [21856721]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли ускорить запрос  [new]
chikaginsk
Member

Откуда:
Сообщений: 89
xtender
надо проверять и показывать планы со статистиками по выполнению с фетчем всех строк, а не только первых.

Для нормального анализа, надо показывать статистику таблиц и планы выполнения со статистиками!

Можно ссылку на статью или док по этой теме?
Статистиками админ заведует, говорит всё собирается.
Но хотелось бы самому разобраться в этом вопросе - в будущем понадобится.
9 апр 19, 09:25    [21856723]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить