ReSQL.ru
     
powered by simpleCommunicator - 2.0.50     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Медленная работа запроса на одном из значений параметра
9 сообщений из 9, страница 1 из 1
Медленная работа запроса на одном из значений параметра
    #40078579
chikaginsk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chikaginsk
Участник
Здравствуйте. Возникла проблема с медленной работой запроса на одном из значений параметра.
Есть 2 таблицы (БД Oracle 12.2.0.1.0):
 
DEPARTMENT_DOC_MASTER
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
    ID                  NUMBER(15)         not null        primary key,
    CREATED_BY          VARCHAR2(255 char) not null,
    CREATED_DATE        TIMESTAMP(6)       not null,
    UPDATED_BY          VARCHAR2(255 char),
    UPDATED_DATE        TIMESTAMP(6),
    USER_MIDDLENAME     VARCHAR2(255 char),
    USER_MIDDLENAME_UPD VARCHAR2(255 char),
    USER_NAME           VARCHAR2(255 char),
    USER_NAME_UPD       VARCHAR2(255 char),
    USER_SURNAME        VARCHAR2(255 char),
    USER_SURNAME_UPD    VARCHAR2(255 char),
    ADDRESS             VARCHAR2(255 char),
    CLOSE_DATE          TIMESTAMP(6),
    DEPARTMENT_TYPE     VARCHAR2(20 char)  not null,
    EMAIL               VARCHAR2(255 char),
    OPEN_DATE           TIMESTAMP(6),
    GUID                RAW(16)            not null

index IDX_DEPARTMENT_DOC_MASTER_TYPE on DEPARTMENT_DOC_MASTER (DEPARTMENT_TYPE)


DEPARTMENT_DOC_VERSION
Код: 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.
28.
    ID                       NUMBER(15)         not null        primary key,
    CREATED_BY               VARCHAR2(255 char) not null,
    CREATED_DATE             TIMESTAMP(6)       not null,
    UPDATED_BY               VARCHAR2(255 char),
    UPDATED_DATE             TIMESTAMP(6),
    USER_MIDDLENAME          VARCHAR2(255 char),
    USER_MIDDLENAME_UPD      VARCHAR2(255 char),
    USER_NAME                VARCHAR2(255 char),
    USER_NAME_UPD            VARCHAR2(255 char),
    USER_SURNAME             VARCHAR2(255 char),
    USER_SURNAME_UPD         VARCHAR2(255 char),
    FINISH_DATE              TIMESTAMP(6),
    FULL_NAME                VARCHAR2(255 char) not null,
    OSB_CODE_GLOBAL          VARCHAR2(20 char),
    OSB_CODE_LOCAL           VARCHAR2(20 char),
    SHORT_NAME               VARCHAR2(255 char) not null,
    START_DATE               TIMESTAMP(6)       not null,
    TB_CODE_GLOBAL           VARCHAR2(20 char),
    TB_CODE_LOCAL            VARCHAR2(20 char),
    VSP_CODE_GLOBAL          VARCHAR2(20 char),
    VSP_CODE_LOCAL           VARCHAR2(20 char),
    DEPARTMENT_DOC_MASTER_ID NUMBER(15)         not null        foreign key

index IDX_DEPARTMENT_VERSIONS on DEPARTMENT_DOC_VERSION (TB_CODE_GLOBAL)
index IDX_DEPARTMENT_VERSIONS_OSB on DEPARTMENT_DOC_VERSION (OSB_CODE_GLOBAL)
index IDX_DEPARTMENT_VERSIONS_VSP on DEPARTMENT_DOC_VERSION (VSP_CODE_GLOBAL)
index IDX_DEPARTMENT_VERSIONS_FULLNAME on DEPARTMENT_DOC_VERSION (FULL_NAME)
index IDX_DEPARTMENT_DOC_VERSION_MASTER_ID on DEPARTMENT_DOC_VERSION (DEPARTMENT_DOC_MASTER_ID)



Выполняется соединение этих двух таблиц по внешнему ключу DEPARTMENT_DOC_VERSION.DEPARTMENT_DOC_MASTER_ID:
 
Код: 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.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
select department0_.id                       as id1_2_0_,
       department1_.id                       as id1_0_1_,
       department0_.created_by               as created_by2_2_0_,
       department0_.created_date             as created_date3_2_0_,
       department0_.updated_by               as updated_by4_2_0_,
       department0_.updated_date             as updated_date5_2_0_,
       department0_.user_middlename          as user_middlename6_2_0_,
       department0_.user_middlename_upd      as user_middlename_up7_2_0_,
       department0_.user_name                as user_name8_2_0_,
       department0_.user_name_upd            as user_name_upd9_2_0_,
       department0_.user_surname             as user_surname10_2_0_,
       department0_.user_surname_upd         as user_surname_upd11_2_0_,
       department0_.finish_date              as finish_date12_2_0_,
       department0_.full_name                as full_name13_2_0_,
       department0_.department_doc_master_id as department_doc_ma22_2_0_,
       department0_.osb_code_global          as osb_code_global14_2_0_,
       department0_.osb_code_local           as osb_code_local15_2_0_,
       department0_.short_name               as short_name16_2_0_,
       department0_.start_date               as start_date17_2_0_,
       department0_.tb_code_global           as tb_code_global18_2_0_,
       department0_.tb_code_local            as tb_code_local19_2_0_,
       department0_.vsp_code_global          as vsp_code_global20_2_0_,
       department0_.vsp_code_local           as vsp_code_local21_2_0_,
       department1_.created_by               as created_by2_0_1_,
       department1_.created_date             as created_date3_0_1_,
       department1_.updated_by               as updated_by4_0_1_,
       department1_.updated_date             as updated_date5_0_1_,
       department1_.user_middlename          as user_middlename6_0_1_,
       department1_.user_middlename_upd      as user_middlename_up7_0_1_,
       department1_.user_name                as user_name8_0_1_,
       department1_.user_name_upd            as user_name_upd9_0_1_,
       department1_.user_surname             as user_surname10_0_1_,
       department1_.user_surname_upd         as user_surname_upd11_0_1_,
       department1_.address                  as address12_0_1_,
       department1_.close_date               as close_date13_0_1_,
       department1_.department_type          as department_type14_0_1_,
       department1_.email                    as email15_0_1_,
       department1_.open_date                as open_date16_0_1_,
       department1_.guid                     as guid17_0_1_
from department_doc_version department0_
         inner join department_doc_master department1_ on department0_.department_doc_master_id = department1_.id
where (department1_.department_type in ('VSP'))
  and (department0_.start_date <= current_date and (department0_.finish_date is null or department0_.finish_date >= current_date)
       or department0_.start_date > current_date)
  and (department1_.open_date is not null)
  and (department1_.open_date <= current_date and (department1_.close_date is null or department1_.close_date >= current_date)
       or department1_.open_date > current_date);


Изначальный запрос (HQL) выполняется в Java ORM Hibernate (затем формируется вышеуказанный запрос Oracle):
 
Код: java
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
    @Query("from DepartmentDocVersion bp join fetch bp.master" +
            " WHERE" +
            " bp.master.departmentType in ?1 " +
            " and (((bp.startDate <= current_date)" +
            " and (bp.finishDate is null or bp.finishDate >= current_date)) or bp.startDate > current_date)" +

            " and (bp.master.openDate is not null" +
            " and (((bp.master.openDate <= current_date)" +
            " and (bp.master.closeDate is null or bp.master.closeDate >= current_date))" +
            " or bp.master.openDate > current_date))"
    )


При значении 'VSP' в условии "where (department1_.department_type in ('VSP'))" адаптивный план запроса следующий (Cost очень высокий):
 
Код: 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.
Plan hash value: 901981106
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                        | 49747 |    13M|       |  1556   (1)| 00:00:01 |
|*  1 |  HASH JOIN         |                        | 49747 |    13M|  6712K|  1556   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPARTMENT_DOC_MASTER  | 49747 |  6121K|       |   310   (1)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPARTMENT_DOC_VERSION | 61893 |  9791K|       |   412   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   1 - access(""DEPARTMENT0_"".""DEPARTMENT_DOC_MASTER_ID""=""DEPARTMENT1_"".""ID"")"
"   2 - filter(""DEPARTMENT1_"".""DEPARTMENT_TYPE""='VSP' AND ((""DEPARTMENT1_"".""CLOSE_DATE"" IS "
"              NULL OR ""DEPARTMENT1_"".""CLOSE_DATE"">=CURRENT_DATE) AND "
"              ""DEPARTMENT1_"".""OPEN_DATE""<=CURRENT_DATE OR ""DEPARTMENT1_"".""OPEN_DATE"">CURRENT_DATE) AND "
"              ""DEPARTMENT1_"".""OPEN_DATE"" IS NOT NULL)"
"   3 - filter((""DEPARTMENT0_"".""FINISH_DATE"" IS NULL OR "
"              ""DEPARTMENT0_"".""FINISH_DATE"">=CURRENT_DATE) AND ""DEPARTMENT0_"".""START_DATE""<=CURRENT_DATE OR "
"              ""DEPARTMENT0_"".""START_DATE"">CURRENT_DATE)"
 
Note
-----
   - this is an adaptive plan



При этом, если в условии поменять значение на другое (например, 'TB'), то план запроса меняется (скорость выполнения увеличивается в разы):
 
Код: 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.
Plan hash value: 3812244782
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                                |   293 | 84384 |   421   (1)| 00:00:01 |
|*  1 |  HASH JOIN                           |                                |   293 | 84384 |   421   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENT_DOC_MASTER          |   293 | 36918 |     9   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IDX_DEPARTMENT_DOC_MASTER_TYPE |   360 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL                  | DEPARTMENT_DOC_VERSION         | 61893 |  9791K|   412   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   1 - access(""DEPARTMENT0_"".""DEPARTMENT_DOC_MASTER_ID""=""DEPARTMENT1_"".""ID"")"
"   2 - filter(((""DEPARTMENT1_"".""CLOSE_DATE"" IS NULL OR ""DEPARTMENT1_"".""CLOSE_DATE"">=CURRENT_DATE) AND "
"              ""DEPARTMENT1_"".""OPEN_DATE""<=CURRENT_DATE OR ""DEPARTMENT1_"".""OPEN_DATE"">CURRENT_DATE) AND "
"              ""DEPARTMENT1_"".""OPEN_DATE"" IS NOT NULL)"
"   3 - access(""DEPARTMENT1_"".""DEPARTMENT_TYPE""='TB')"
"   4 - filter((""DEPARTMENT0_"".""FINISH_DATE"" IS NULL OR ""DEPARTMENT0_"".""FINISH_DATE"">=CURRENT_DATE) AND "
"              ""DEPARTMENT0_"".""START_DATE""<=CURRENT_DATE OR ""DEPARTMENT0_"".""START_DATE"">CURRENT_DATE)"
 
Note
-----
   - this is an adaptive plan


Подозреваю, что всё дело в количестве записей и при значении 'VSP' индекс по полю DEPARTMENT_TYPE не рабоотает, т.к. выбирается большой процент запией от всех строк таблицы DEPARTMENT_DOC_MASTER:
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
Значение, кол-во записей
'VSP',83859
'OSB',525
'TB',365
'DZO',57
'OTHER',31
'GOSB',20


Подскажите пожалуйста каким образом можно ускорить выполнение данного запроса (добавить индексы в таблицы или ущё что-то) со значением 'VSP' в параметре (хинты не помогут, т.к. изначальный запрос выполняется в HQL).
...
Рейтинг: 0 / 0
18.06.2021, 13:53
| Ответить | Цитировать | Написать  
Медленная работа запроса на одном из значений параметра
    #40078583
chikaginsk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chikaginsk
Участник
План запроса с 'VSP':
...
Рейтинг: 0 / 0
18.06.2021, 13:57
| Ответить | Цитировать | Написать  
Медленная работа запроса на одном из значений параметра
    #40078586
chikaginsk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chikaginsk
Участник
Визуализация плана запроса (если это как-то может помочь):
...
Рейтинг: 0 / 0
18.06.2021, 14:01
| Ответить | Цитировать | Написать  
Медленная работа запроса на одном из значений параметра
    #40078587
Leonid Kudryavtsev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid Kudryavtsev
Участник
chikaginsk

Выполняется соединение этих двух таблиц по внешнему ключу DEPARTMENT_DOC_VERSION.DEPARTMENT_DOC_MASTER_ID:

Если Вы посмотрите "хороший план", то "соединение ... по внешнему ключу" не обнаруживается. Hash join и full table scan'ы

Я бы попытался в индекс
index IDX_DEPARTMENT_DOC_VERSION_MASTER_ID on DEPARTMENT_DOC_VERSION (DEPARTMENT_DOC_MASTER_ID)
добавить start_date, finish_date

Почему version в запросе написали вперед master'а? Оно, конечно, в данном случае пофиг, но с точки зрения красоты коды - я всегда пишу наоборот.

Аналогично и в index IDX_DEPARTMENT_DOC_MASTER_TYPE on DEPARTMENT_DOC_MASTER (DEPARTMENT_TYPE)
хорошо бы добавить open и close date
...
Рейтинг: 0 / 0
18.06.2021, 14:09
| Ответить | Цитировать | Написать  
Медленная работа запроса на одном из значений параметра
    #40078592
chikaginsk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chikaginsk
Участник
Leonid Kudryavtsev,

"Хороший план" - это который адаптивный? Там действительно полное сканирование каждой таблицы и HASH JOIN по ним.
Хотя в плане на картинке (в сообщении 22337227 ) отображён индекс по внешнему ключу. Но он (если судить по Cost) никак не задействован.
Если Вас не затруднит, то могли бы дать совет как можно ускорить запрос? Структуру таблиц менять мне не дадут. Может как-то можно изменить условия в запросе или добавить какие-то индексы?
...
Рейтинг: 0 / 0
18.06.2021, 14:24
| Ответить | Цитировать | Написать  
Медленная работа запроса на одном из значений параметра
    #40078596
Leonid Kudryavtsev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid Kudryavtsev
Участник
chikaginsk

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


Или добавить индексы или поля в существующий индекс.

Если full table scan Вас устраивает, можете попытаться прохинтовать запрос. Но IMHO, full table scan это значит, что индексы не работают и их нужно отправлять в топку / делать новые
...
Рейтинг: 0 / 0
18.06.2021, 14:37
| Ответить | Цитировать | Написать  
Медленная работа запроса на одном из значений параметра
    #40078598
Leonid Kudryavtsev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid Kudryavtsev
Участник
1. При запросе с VSP - индексы не работают
2. При запросе с TB - у оптимизатора просто сорвало крышу, даже думать что этот план значит не хочется. Т.к. ясно, что по хорошему должно быть Nested Loop и два Index Scan'а

Думаю, что добавление дат в индекс должно помочь

Ну и судя по всему, это не только должно помочь, но и будет правильно. Т.к. ясно, что на исторических таблицах 95% запросов будет содержать условия с датами.
...
Рейтинг: 0 / 0
18.06.2021, 14:40
| Ответить | Цитировать | Написать  
Медленная работа запроса на одном из значений параметра
    #40078612
chikaginsk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chikaginsk
Участник
Leonid Kudryavtsev,

Благодарю за подробный ответ!
После прочтения статьи про Hash Join подозревал, что нужны индексы на полях дат, но хотелось узнать мнение опытных людей.
Прошу сделать небольшое уточнение - индексы для полей дат лучше создавать отдельные для каждого поля с датой (которые используются в условии WHERE) или в каждой таблице создать по одному составному индексу для обеих полей с датами?
И ещё, в таблице DEPARTMENT_DOC_MASTER уже есть индекс (судя, по плану запроса, он не используется ни в одном из случаев) по полю DEPARTMENT_TYPE (которое используется в условии WHERE). Корректно ли будет добавить это поле в один составной индекс вместе с полями дат или для дат лучше создать отдельный(е) индекс(ы)?
...
Рейтинг: 0 / 0
18.06.2021, 15:03
| Ответить | Цитировать | Написать  
Медленная работа запроса на одном из значений параметра
    #40078630
Leonid Kudryavtsev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid Kudryavtsev
Участник
chikaginsk

Прошу сделать небольшое уточнение - индексы для полей дат лучше создавать отдельные для каждого поля с датой (которые используются в условии WHERE) или в каждой таблице создать по одному составному индексу для обеих полей с датами?

Категорически нет.
Должно быть один индекс, включающий в себя поля участвующие в варажении в where.

Т.е.
DEPARTMENT_DOC_MASTER_ID, start_date, finish_date
и
DEPARTMENT_TYPE, open_date, close_date

Тогда Oracle будет отбирать по DEPARTMENT_DOC_MASTER_ID по b-tree, накладывать фильтр на start_date и finish_date и только за результатом залезать в основную таблицу.

Если создать несколько индексов, то в лучшем случае они не будут использоваться, а в худщем IMHO & AFAIK приведут ко всякой порнографии (IMHO !) вида b-tree to bitmap index conversion и прочем радостям современных БД технологий.

IMHO. Могу ошибаться.
...
Рейтинг: 0 / 0
18.06.2021, 15:39
| Ответить | Цитировать | Написать  
9 сообщений из 9, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Медленная работа запроса на одном из значений параметра
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=17&tid=1880101]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 45ms
get topic data:
 14ms
get forum data:
 3ms
get page messages:
 49ms
get tp. blocked users:
 2ms
others: 239ms
total:  384ms
созд. / загр.: 384ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]