Возможно ли ускорить запрос / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Возможно ли ускорить запрос

14 сообщений из 14, страница 1 из 1

Возможно ли ускорить запрос

#39797160

chikaginsk

Участник

Сообщения: 117

Рейтинг: 0 / 0

Приветствую всех! Есть острая необходимость ускорить выполнение селекта, но пока все попытки (перестраивание запроса, хинты...) закончились неудачей. Прошу помощи в оптимизации, нужно уменьшить время выполнения.

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

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 секунд).
План запроса следующий:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

--------------------------------------------------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------------------------------------------------

...

Рейтинг:

0 / 0

05.04.2019, 12:13

| Ответить | Цитировать | Написать

Возможно ли ускорить запрос

#39797162

andreymx

Участник

Откуда: Запорожье

Сообщения: 56 486

Рейтинг: 0 / 0

chikaginsk

Код: plsql

where to_char(t7.dt,'yyyyMM') = (('2018'/*:YEAR*/)||('06'/*:MONTH*/))

можем выбиться из индекса из-за ту-чара

...

Рейтинг:

0 / 0

05.04.2019, 12:16

| Ответить | Цитировать | Написать

Возможно ли ускорить запрос

#39797166

chikaginsk

Участник

Сообщения: 117

Рейтинг: 0 / 0

Забыл написать, что параметры :RAION и (особенно) :AREA принимают довольно много значений (десятки и сотни).

...

Рейтинг:

0 / 0

05.04.2019, 12:20

| Ответить | Цитировать | Написать

Возможно ли ускорить запрос

#39797170

chikaginsk

Участник

Сообщения: 117

Рейтинг: 0 / 0

andreymx,

Поле t7.dt не участвует в индексах. Индексы есть по полям t7.conf_id и t7.met_id (отдельные индексы по каждому из полей).

...

Рейтинг:

0 / 0

05.04.2019, 12:24

| Ответить | Цитировать | Написать

Возможно ли ускорить запрос

#39797290

feagor

Гость

chikaginsk,

без понимания того что вообще делает запрос могу предложить только избавиться от exists в subq2, в котором судя по всему и причина, её видно по

Код: plaintext

* 18 |       TABLE ACCESS BY INDEX ROWID        | TABLE8                 | 109624012 | 1753984192 |       4 | 00:00:01 |
| * 19 |        INDEX RANGE SCAN                  | XT186S1                |         1 |            |       3 | 00:00:01 |

попробуйте

Код: plsql

1.
2.
3.
4.

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

...

Рейтинг:

0 / 0

05.04.2019, 14:17

| Ответить | Цитировать | Написать

Возможно ли ускорить запрос

#39797292

feagor

Гость

feagor,

без left, просто join

...

Рейтинг:

0 / 0

05.04.2019, 14:19

| Ответить | Цитировать | Написать

Возможно ли ускорить запрос

#39797322

Dshedoo

Участник

Сообщения: 310

Рейтинг: 0 / 0

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

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

...

Рейтинг:

0 / 0

05.04.2019, 14:58

| Ответить | Цитировать | Написать

Возможно ли ускорить запрос

#39797335

Dshedoo

Участник

Сообщения: 310

Рейтинг: 0 / 0

Я же правильно понимаю, что

Код: plsql

1.
2.

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

Равноценно:

Код: plsql

1.
2.

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

А не

Код: plsql

1.
2.
3.

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

...

Рейтинг:

0 / 0

05.04.2019, 15:15

| Ответить | Цитировать | Написать

Возможно ли ускорить запрос

#39797350

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

DshedooЯ же правильно понимаю, что

?

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

Код: plsql

1.
2.

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

Равноценно:

Код: plsql

1.
2.

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

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

Код: plsql

1.
2.

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

SY.

...

Рейтинг:

0 / 0

05.04.2019, 15:36

| Ответить | Цитировать | Написать

Возможно ли ускорить запрос

#39797368

Dshedoo

Участник

Сообщения: 310

Рейтинг: 0 / 0

SYDshedooЯ же правильно понимаю, что

?

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

Код: plsql

1.
2.

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

Равноценно:

Код: plsql

1.
2.

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

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

Код: plsql

1.
2.

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

SY.

Эм....
Это же тоже самое, что и я написал о_О

...

Рейтинг:

0 / 0

05.04.2019, 15:48

| Ответить | Цитировать | Написать

Возможно ли ускорить запрос

#39797535

SeaGate

Участник

Откуда: Новосибирск

Сообщения: 1 757

Рейтинг: 6 / 0

chikaginsk,

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

Стоит проверить производительность с no_merge для subq2:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

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 имеет тот же план отдельно, что и в итоговом запросе.

...

Рейтинг:

0 / 0

05.04.2019, 21:13

| Ответить | Цитировать | Написать

Возможно ли ускорить запрос

#39797566

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

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

chikaginsk

Код: plsql

to_char(t7.dt,'yyyyMM') = (('2018'/*:YEAR*/)||('06'/*:MONTH*/))

не мешайте оптимизатору рассчитывать кардинальность, замените это на between.

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

...

Рейтинг:

0 / 0

06.04.2019, 00:58

| Ответить | Цитировать | Написать

Возможно ли ускорить запрос

#39798532

chikaginsk

Участник

Сообщения: 117

Рейтинг: 0 / 0

andreymx, feagor, Dshedoo, SY, SeaGate, xtender
Благодарю всех за ответы!

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

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

andreymx, xtender
Убрал to_char с поля даты, переделал на between, разницы в производительности не заметил после этого, но хоть оптимизатору мешать не будет.

...

Рейтинг:

0 / 0

09.04.2019, 09:22

| Ответить | Цитировать | Написать

Возможно ли ускорить запрос

#39798534

chikaginsk

Участник

Сообщения: 117

Рейтинг: 0 / 0

xtenderнадо проверять и показывать планы со статистиками по выполнению с фетчем всех строк, а не только первых.

Для нормального анализа, надо показывать статистику таблиц и планы выполнения со статистиками!
Можно ссылку на статью или док по этой теме?
Статистиками админ заведует, говорит всё собирается.
Но хотелось бы самому разобраться в этом вопросе - в будущем понадобится.

...

Рейтинг:

0 / 0

09.04.2019, 09:25

| Ответить | Цитировать | Написать

14 сообщений из 14, страница 1 из 1

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Возможно ли ускорить запрос

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&msg=39797162&tid=1882603]:	0ms
get settings:	9ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	38ms
get topic data:	11ms
get forum data:	2ms
get page messages:	48ms
get tp. blocked users:	1ms
others:	14ms

total:	145ms