|
Не используется индекс на следующий день
#40080127
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
Здравствуйте. При выполнении плана запроса на следующий день, отображается другой план запроса и индексы перестают использоваться (при этом Cost возрастает в 2 раза).
Есть 2 таблицы (БД Oracle 12.2.0.1.0):
DEPARTMENT_DOC_MASTER
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 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)
index IDX_DEPARTMENT_DOC_MASTER_TYPE_OPENDT_CLOSEDT_TRUNC
on DEPARTMENT_DOC_MASTER (DEPARTMENT_TYPE, TRUNC("OPEN_DATE"), TRUNC("CLOSE_DATE"))
index IDX_DEPARTMENT_DOC_MASTER_TYPE_OPENDT_CLOSEDT
on DEPARTMENT_DOC_MASTER (DEPARTMENT_TYPE, OPEN_DATE, CLOSE_DATE)
DEPARTMENT_DOC_VERSION
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:
Этот запрос ORM Hybernate (Java) отправляет на выполнение Oracle
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.
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 (department1_.open_date is not null)
and (trunc(current_date) between trunc(department1_.open_date) and nvl(trunc(department1_.close_date), trunc(current_date)));
Исходный запрос выглядит немного компактнее (но не в этом суть)
1.
2.
3.
4.
5.
@Query("from DepartmentDocVersion bp join fetch bp.master" +
" where bp.master.departmentType in ?1 " +
" and bp.master.openDate is not null" +
" and trunc(current_date) between trunc(bp.master.openDate) and coalesce(trunc(bp.master.closeDate), trunc(current_date))"
)
После создания индекса план запроса выглядит следующим образом (Cost = 445, индексы используются):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
Plan hash value: 3411709025
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 210 | 62370 | 445 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 210 | 62370 | 445 (0)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENT_DOC_MASTER | 210 | 28350 | 25 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_DEPARTMENT_DOC_MASTER_TYPE_OPENDT_CLOSEDT_TRUNC | 755 | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENT_DOC_VERSION | 1 | 162 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_DEPARTMENT_DOC_VERSION_MASTER_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 2 - filter(NVL(TRUNC(INTERNAL_FUNCTION(""DEPARTMENT1_"".""CLOSE_DATE"")),TRUNC(CURRENT_DATE))>=TRUNC(CURRENT_DATE) AND "
" ""DEPARTMENT1_"".""OPEN_DATE"" IS NOT NULL)"
" 3 - access(""DEPARTMENT1_"".""DEPARTMENT_TYPE""='VSP' AND TRUNC(INTERNAL_FUNCTION(""OPEN_DATE""))<=TRUNC(CURRENT_DATE))"
" 5 - access(""DEPARTMENT0_"".""DEPARTMENT_DOC_MASTER_ID""=""DEPARTMENT1_"".""ID"")"
Note
-----
- this is an adaptive plan
А на следующий день план запроса меняется на этот (Cost = 997, индексы не используются):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
Plan hash value: 901981106
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4193 | 1216K| 997 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 4193 | 1216K| 997 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPARTMENT_DOC_MASTER | 4193 | 552K| 450 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPARTMENT_DOC_VERSION | 85125 | 13M| 547 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 1 - access(""DEPARTMENT0_"".""DEPARTMENT_DOC_MASTER_ID""=""DEPARTMENT1_"".""ID"")"
" 2 - filter(NVL(TRUNC(INTERNAL_FUNCTION(""DEPARTMENT1_"".""CLOSE_DATE"")),TRUNC(CURRENT"
" _DATE))>=TRUNC(CURRENT_DATE) AND ""DEPARTMENT1_"".""DEPARTMENT_TYPE""='VSP' AND "
" TRUNC(INTERNAL_FUNCTION(""OPEN_DATE""))<=TRUNC(CURRENT_DATE) AND "
" ""DEPARTMENT1_"".""OPEN_DATE"" IS NOT NULL)"
Note
-----
- this is an adaptive plan
В соседнем форуме ( 22339748 ) рекомендовали собрать статистику по индексам сразу после их создания. Выполнял:
1.
2.
3.
4.
5.
-- indexes
begin
dbms_stats.gather_index_stats(OWNNAME=>'TEST', INDNAME=>'IDX_DEPARTMENT_DOC_MASTER_TYPE_OPENDT_CLOSEDT_TRUNC');
dbms_stats.gather_index_stats(OWNNAME=>'TEST', INDNAME=>'IDX_DEPARTMENT_DOC_MASTER_TYPE_OPENDT_CLOSEDT');
end;
Но на следующее утро план менялся и Cost снова вырос (индексы не использовались).
После сбора статистики по таблице (ниже под спойлером) план запроса сразу становился равен 997.
1.
2.
3.
4.
-- table
begin
dbms_stats.gather_table_stats(OWNNAME=>'TEST', TABNAME=>'DEPARTMENT_DOC_MASTER', METHOD_OPT=>'FOR ALL COLUMNS');
end;
Подозреваю, что Oracle автоматически выполняетет сбор статистики по таблице ночью, а на утро получаю другой план запроса.
Есть ли какие-нибудь способы, чтобы план запроса оставался равным 445 (как сразу после создания индексов) и индексы использовались постоянно? |
|
|
