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

Откуда:
Сообщений: 3936
Есть примерно такой запрос:
select ...
from ACCIDENT_LIST AL
join BM_PERIODIC_ACCT PA on (PA.PERIOD_STOP > AL.MOMENT_BEG and PA.MOMENT < AL.MOMENT_END)
join BM_TARIFFEL TE on (TE.TARIFFEL_ID = PA.TARIFFEL_ID)
join BM_TARIFFEL_TYPE TT on (TT.TARIFFEL_TYPE_ID = TE.TARIFFEL_TYPE_ID and TT.MNEMONIC = 'fee')
join BM_SERVICE_MONEY SM on (SM.PERIODIC_ACCT_ID = PA.PERIODIC_ACCT_ID and SM.CHARGE_TYPE_ID = 1 and SM.PARENT_ID is null)
join BM_SERVICE_STATUS SS on (SS.SERVICE_ID = SM.SERVICE_ID and SS.DATE_BEG <= PA.MOMENT and (SS.DATE_END is null or SS.DATE_END > PA.MOMENT))
join SERVICES S on (S.SERVICE_ID = SM.SERVICE_ID)
--join ACCIDENT_RCPT AR on (AR.ACCIDENT_ID = AL.ACCIDENT_ID and AR.ACCOUNT_ID = S.ACCOUNT_ID)
where AL.ACCIDENT_ID = 1858
and AL.STATUS = 2
and SM.MONEY != 0

Работает быстро, возвращает порядка 5к строк.
Но как только убираю комментарий с ACCIDENT_RCPT, запрос перестает выполняться (висит более 3 минут).

Вроде бы все нужные индексы имеются:
+

CREATE TABLE ACCIDENT_LIST
(
  ACCIDENT_ID    NUMBER                         NOT NULL,
  MOMENT_BEG     DATE,
  MOMENT_END     DATE,
  DURATION       NUMBER,
  STATUS         NUMBER                         NOT NULL,
  DESCRIPTION    VARCHAR2(2000 BYTE),
  REASON         VARCHAR2(2000 BYTE),
  INPUT_MOMENT   DATE,
  STATUS_MOMENT  DATE,
  CLOSE_MOMENT   DATE,
  RESULT         VARCHAR2(2000 BYTE),
  RESULT_CNT     NUMBER,
  RESULT_SUM     NUMBER,
  PARAM_LEVEL    NUMBER,
  PARAM_CLASS    VARCHAR2(80 BYTE)
);
CREATE UNIQUE INDEX ACCIDENT_LIST_PK ON ACCIDENT_LIST (ACCIDENT_ID);

CREATE TABLE ACCIDENT_RCPT
(
  ACCIDENT_ID  NUMBER                           NOT NULL,
  MASK_TYPE    VARCHAR2(40 BYTE),
  MASK_VALUE   VARCHAR2(200 BYTE),
  CLIENT       NUMBER                           NOT NULL,
  DETAILS      CLOB,
  MOMENT       DATE,
  STATUS       NUMBER,
  AMOUNT       NUMBER,
  CUSTOMER_ID  NUMBER,
  ACCOUNT_ID   NUMBER,
  PAYMENT_ID   NUMBER
);
CREATE UNIQUE INDEX ACCIDENT_RCPT_PK ON ACCIDENT_RCPT (ACCIDENT_ID, CLIENT);
CREATE INDEX ACCIDENT_RCPT_MOMENT ON ACCIDENT_RCPT (MOMENT);
CREATE INDEX ACCIDENT_RCPT_CUSTOMER ON ACCIDENT_RCPT (ACCIDENT_ID, CUSTOMER_ID);
CREATE INDEX ACCIDENT_RCPT_ACCOUNT ON ACCIDENT_RCPT (ACCIDENT_ID, ACCOUNT_ID);
CREATE INDEX ACCIDENT_RCPT_PAYMENT ON ACCIDENT_RCPT (ACCIDENT_ID, PAYMENT_ID);

CREATE TABLE SERVICES
(
  SERVICE_ID      NUMBER                        NOT NULL,
  DOMAIN_ID       NUMBER                        NOT NULL,
  GROUP_ID        NUMBER                        NOT NULL,
  CUSTOMER_ID     NUMBER                        NOT NULL,
  ACCOUNT_ID      NUMBER                        NOT NULL,
  TYPE_ID         NUMBER                        NOT NULL,
  TARIFF_ID       NUMBER                        NOT NULL,
  CARD_ID         NUMBER,
  LOGIN           VARCHAR2(255 BYTE)            NOT NULL,
  PASSWD          VARCHAR2(255 BYTE),
  CR_PASSWD       VARCHAR2(255 BYTE),
  STATUS          NUMBER                        NOT NULL,
  ACTUAL_STATUS   NUMBER                        NOT NULL,
  DESCRIPTION     VARCHAR2(255 BYTE),
  CREATE_DATE     DATE,
  RECKONING_DATE  DATE,
  ACTIVITY_DATE   DATE,
  STATUS_DATE     DATE,
  START_DATE      DATE,
  STOP_DATE       DATE,
  PROXY_AP_ID     NUMBER
);
CREATE UNIQUE INDEX SERVICES_PK ON SERVICES (SERVICE_ID);
CREATE UNIQUE INDEX SERV_UNILOGIN_IDX ON SERVICES (DOMAIN_ID, TYPE_ID, LOGIN);
CREATE INDEX SERV_ACCOUNT_IDX ON SERVICES (ACCOUNT_ID);
CREATE INDEX SERV_ACT_STATUS_IDX ON SERVICES (ACTUAL_STATUS);
CREATE INDEX SERV_CARD_IDX ON SERVICES (CARD_ID);
CREATE INDEX SERV_CREATED_IDX ON SERVICES (CREATE_DATE);
CREATE INDEX SERV_CUSTOMER_IDX ON SERVICES (CUSTOMER_ID);
CREATE INDEX SERV_GROUP_IDX ON SERVICES (GROUP_ID);
CREATE INDEX SERV_LOGIN_IDX ON SERVICES (LOGIN);
CREATE INDEX SERV_LOGINU_IDX ON SERVICES (UPPER("LOGIN"));
CREATE INDEX SERV_RECKONINGD_IDX ON SERVICES (RECKONING_DATE);
CREATE INDEX SERV_STARTD_IDX ON SERVICES (START_DATE);
CREATE INDEX SERV_STATUSD_IDX ON SERVICES (STATUS_DATE);
CREATE INDEX SERV_STATUS_IDX ON SERVICES (STATUS);
CREATE INDEX SERV_STOPD_IDX ON SERVICES (STOP_DATE);
CREATE INDEX SERV_TARIFF_IDX ON SERVICES (TARIFF_ID);
CREATE INDEX SERV_TYPE_IDX ON SERVICES (TYPE_ID);



План выполнения я посмотрел, на мой взгляд нормальный план и индекс задействован.
Но я не особо в них разбираюсь.
Первый план с закомментированным соединением (запрос работает быстро):
+


Plan
SELECT STATEMENT ALL_ROWSCost: 571 165 Bytes: 2 080 Cardinality: 13
21 NESTED LOOPS Cost: 571 165 Bytes: 2 080 Cardinality: 13
18 NESTED LOOPS Cost: 571 152 Bytes: 1 963 Cardinality: 13
15 NESTED LOOPS Cost: 570 651 Bytes: 21 710 Cardinality: 167
12 HASH JOIN Cost: 8 874 Bytes: 1 371 276 Cardinality: 12 697
4 VIEW VIEW index$_join$_006 Cost: 3 Bytes: 494 Cardinality: 26
3 HASH JOIN
1 INDEX RANGE SCAN INDEX TFEL_TYPE_MNEMONIC_IDX Cost: 1 Bytes: 494 Cardinality: 26
2 INDEX FAST FULL SCAN INDEX (UNIQUE) BM_TARIFFEL_TYPE_PK Cost: 1 Bytes: 494 Cardinality: 26
11 HASH JOIN Cost: 8 871 Bytes: 1 521 277 Cardinality: 17 093
9 NESTED LOOPS Cost: 8 538 Bytes: 1 281 975 Cardinality: 17 093
6 TABLE ACCESS BY INDEX ROWID TABLE ACCIDENT_LIST Cost: 1 Bytes: 37 Cardinality: 1
5 INDEX UNIQUE SCAN INDEX (UNIQUE) ACCIDENT_LIST_PK Cost: 0 Cardinality: 1
8 TABLE ACCESS BY INDEX ROWID TABLE BM_PERIODIC_ACCT Cost: 8 537 Bytes: 649 534 Cardinality: 17 093
7 INDEX RANGE SCAN INDEX PER_ACCT_STOP_IDX Cost: 1 652 Cardinality: 341 850
10 TABLE ACCESS FULL TABLE BM_TARIFFEL Cost: 332 Bytes: 1 205 022 Cardinality: 86 073
14 TABLE ACCESS BY INDEX ROWID TABLE BM_SERVICE_MONEY Cost: 53 Bytes: 22 Cardinality: 1
13 INDEX RANGE SCAN INDEX SVRM_PERIODIC_ACCT_IDX Cost: 2 Cardinality: 61
17 TABLE ACCESS BY INDEX ROWID TABLE BM_SERVICE_STATUS Cost: 3 Bytes: 21 Cardinality: 1
16 INDEX RANGE SCAN INDEX SRV_ST_SERVICE_IDX Cost: 2 Cardinality: 1
20 TABLE ACCESS BY INDEX ROWID TABLE SERVICES Cost: 1 Bytes: 9 Cardinality: 1
19 INDEX UNIQUE SCAN INDEX (UNIQUE) SERVICES_PK Cost: 0 Cardinality: 1


Второй план с открытым соединением (запрос виснет):
+


Plan
SELECT STATEMENT ALL_ROWSCost: 15 697 Bytes: 174 Cardinality: 1
22 TABLE ACCESS BY INDEX ROWID TABLE BM_SERVICE_STATUS Cost: 3 Bytes: 21 Cardinality: 1
21 NESTED LOOPS Cost: 15 697 Bytes: 174 Cardinality: 1
19 NESTED LOOPS Cost: 15 694 Bytes: 153 Cardinality: 1
16 NESTED LOOPS Cost: 15 693 Bytes: 134 Cardinality: 1
13 NESTED LOOPS Cost: 15 692 Bytes: 120 Cardinality: 1
10 NESTED LOOPS Cost: 15 682 Bytes: 410 Cardinality: 5
7 NESTED LOOPS Cost: 9 Bytes: 180 Cardinality: 3
4 NESTED LOOPS Cost: 2 Bytes: 46 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE ACCIDENT_LIST Cost: 1 Bytes: 37 Cardinality: 1
1 INDEX UNIQUE SCAN INDEX (UNIQUE) ACCIDENT_LIST_PK Cost: 0 Cardinality: 1
3 INDEX RANGE SCAN INDEX ACCIDENT_RCPT_ACCOUNT Cost: 1 Bytes: 9 Cardinality: 1
6 TABLE ACCESS BY INDEX ROWID TABLE SERVICES Cost: 7 Bytes: 98 Cardinality: 7
5 INDEX RANGE SCAN INDEX SERV_ACCOUNT_IDX Cost: 1 Cardinality: 7
9 TABLE ACCESS BY INDEX ROWID TABLE BM_SERVICE_MONEY Cost: 15 009 Bytes: 44 Cardinality: 2
8 INDEX RANGE SCAN INDEX SVRM_SERVICE_IDX Cost: 332 Cardinality: 71 133
12 TABLE ACCESS BY INDEX ROWID TABLE BM_PERIODIC_ACCT Cost: 2 Bytes: 38 Cardinality: 1
11 INDEX UNIQUE SCAN INDEX (UNIQUE) PERIODIC_ACCT_PK Cost: 1 Cardinality: 1
15 TABLE ACCESS BY INDEX ROWID TABLE BM_TARIFFEL Cost: 1 Bytes: 14 Cardinality: 1
14 INDEX UNIQUE SCAN INDEX (UNIQUE) TAREL_PK Cost: 0 Cardinality: 1
18 TABLE ACCESS BY INDEX ROWID TABLE BM_TARIFFEL_TYPE Cost: 1 Bytes: 19 Cardinality: 1
17 INDEX UNIQUE SCAN INDEX (UNIQUE) BM_TARIFFEL_TYPE_PK Cost: 0 Cardinality: 1
20 INDEX RANGE SCAN INDEX SRV_ST_SERVICE_IDX Cost: 2 Cardinality: 1
28 май 21, 20:48    [22328673]     Ответить | Цитировать Сообщить модератору
 Re: Oracle: Что не хватает для ускорения запроса?  [new]
flexgen
Member

Откуда: Город на песке
Сообщений: 845
Alibek B.,

Возможно, надо пересобрать статистики для таблиц ACCIDENT_LIST и ACCIDENT_RCPT.
28 май 21, 21:05    [22328677]     Ответить | Цитировать Сообщить модератору
 Re: Oracle: Что не хватает для ускорения запроса?  [new]
Alibek B.
Member

Откуда:
Сообщений: 3936
Да, выполнил dbms_stats.gather_table_stats на этих двух таблицах, помогло.
28 май 21, 21:25    [22328678]     Ответить | Цитировать Сообщить модератору
 Re: Oracle: Что не хватает для ускорения запроса?  [new]
Правильный Вася
Member

Откуда:
Сообщений: 444
Возможно, я неправ, но мне кажется странным излишеством включение поля ACCIDENT_ID в каждый из этих индексов:
CREATE INDEX ACCIDENT_RCPT_CUSTOMER ON ACCIDENT_RCPT (ACCIDENT_ID, CUSTOMER_ID);
CREATE INDEX ACCIDENT_RCPT_ACCOUNT ON ACCIDENT_RCPT (ACCIDENT_ID, ACCOUNT_ID);
CREATE INDEX ACCIDENT_RCPT_PAYMENT ON ACCIDENT_RCPT (ACCIDENT_ID, PAYMENT_ID);
Оно и так уже проиндексировано.
А с учетом того, что второе поле в каждом индексе NULL, то это вызывает разрастание этого индекса без осмысленного наполнения и использования, что замедляет доступ к самому индексу. ИМХО
28 май 21, 23:10    [22328714]     Ответить | Цитировать Сообщить модератору
 Re: Oracle: Что не хватает для ускорения запроса?  [new]
Alibek B.
Member

Откуда:
Сообщений: 3936
Чистого индекса по accident_id нет, только составной accident_id+client.
А в запросах условия customer_id и account_id всегда идут вместе с accident_id, поэтому и индексы такие.
29 май 21, 10:18    [22328746]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить