Oracle: значительно улучшить производительность запросов
У меня есть следующий запрос, для которого я пытаюсь улучшить производительность:
select atx.journal_id
,ab.c_date
from acct_batch ab
join acct_tx atx on ab.acct_id = atx.acct_id
and ab.batch_id = atx.batch_id
join journal j on j.journal_id = atx.journal_id
and j.journal_type_id = 6
join acct a on a.acct_id = atx.acct_id
and a.acct_type_id = 32
join payments p on p.payment_id = j.payment_id
join routing r on r.route_id = p.route_id
and r.acq_code = 'RZ_NS'
join acq_acct aa on aa.acq_code = r.acq_code
and aa.acq_acct_code = r.acq_acct_code
and aa.slc = 'MXM'
where ab.c_date between to_date(to_char('01-JUL-2015')) and last_day(sysdate);
Я выполнил и рассмотрел план объяснения, и общая стоимость составляет 7388. Из этого самым дорогим является объединение с journal Стол, который имеет стоимость 6319.
Таблица содержит приблизительно 1,6 миллиона строк с 87 разделами, из которых только две содержат строки (раздел 6 с 1,4 миллионами и раздел 12 с приблизительным остатком 200 тыс. Строк).
Первое, что я попробовал, это переписать запрос, чтобы избежать полного сканирования при сопоставлении фактического journal_type_id с 6, но я думаю, что мое понимание было неверным, потому что стоимость осталась 7388.
select atx.journal_id
,ab.c_date
from acct_batch ab
join acct_tx atx on ab.acct_id = atx.acct_id
and ab.batch_id = atx.batch_id
join (select
journal_id
, payment_id
from journal
where journal_type_id = 6) j on j.journal_id = atx.journal_id
join acct a on a.acct_id = atx.acct_id
and a.acct_type_id = 32
join payments p on p.payment_id = j.payment_id
join routing r on r.route_id = p.route_id
and r.acq_code = 'RZ_NS'
join acq_acct aa on aa.acq_code = r.acq_code
and aa.acq_acct_code = r.acq_acct_code
and aa.slc = 'MXM'
where ab.c_date between to_date(to_char('01-JUL-2015')) and last_day(sysdate);
Я искал много ресурсов, и одной из причин, по которой я решил переписать запрос, было это видео.
Я все еще активно ищу способы улучшить производительность, но я подумал, что я бы здесь написал вопрос, чтобы, возможно, получить некоторые подсказки.
Я думаю, что человек из видео говорит о том, что первое, что нужно сделать, - это определить, какой у вас "движущий стол" (тот, который определяет, какие строки выбираются - на основе ключа), поэтому я сейчас ищу для способа переписать запрос, чтобы идентифицировать и максимально использовать эту таблицу управления и ее индексы.
Я не знаю, нахожусь ли я на правильном пути, но, пожалуйста, остановите меня, если вы думаете, что я должен поступить иначе. Также, пожалуйста, обратите внимание, что я абсолютный новичок в настройке производительности, на самом деле это мой первый.
Любая помощь приветствуется.
Обновить:
Некоторые из индексов, которые содержат столбцы, используемые в запросах:
╔════════════╦═══════════════╦════════════╦═══════════╦═════════════╦═══════════════════════════════════╗
║ Table ║ IndexName ║ Uniqueness ║ IndexType ║ Partitioned ║ Columns ║
╠════════════╬═══════════════╬════════════╬═══════════╬═════════════╬═══════════════════════════════════╣
║ Acct_Batch ║ Acct_Batch_PK ║ UNIQUE ║ NORMAL ║ NO ║ Acct_ID, Batch_ID ║
║ Acct_TX ║ Acct_TX_IDX ║ NONUNIQUE ║ NORMAL ║ YES ║ Acct_ID, Batch_ID ║
║ Acct_TX ║ Acct_TX_BIDX ║ NONUNIQUE ║ NORMAL ║ YES ║ Journal_ID, Acct_ID ║
║ Journal ║ Journal_PK ║ UNIQUE ║ NORMAL ║ YES ║ Journal_ID ║
║ Journal ║ JType_BIDX ║ NONUNIQUE ║ NORMAL ║ YES ║ Journal_Type_ID, Book_Date ║
║ Journal ║ JType_BIDX_2 ║ NONUNIQUE ║ NORMAL ║ YES ║ MCODE, Journal_Type_ID, Book_Date ║
║ Journal ║ JPay_BIDX ║ NONUNIQUE ║ NORMAL ║ YES ║ Payment_ID, Journal_ID ║
╚════════════╩═══════════════╩════════════╩═══════════╩═════════════╩═══════════════════════════════════╝
Дайте мне знать, если вам нужно увидеть больше индексов или подробностей относительно других таблиц.
Примерный план объяснения:
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 160 | 7388 (1)| 00:01:29 | | |
|* 1 | FILTER | | | | | | | |
| 2 | NESTED LOOPS | | | | | | | |
| 3 | NESTED LOOPS | | 1 | 160 | 7388 (1)| 00:01:29 | | |
|* 4 | HASH JOIN | | 4 | 604 | 7380 (1)| 00:01:29 | | |
| 5 | NESTED LOOPS | | | | | | | |
| 6 | NESTED LOOPS | | 107 | 14338 | 7372 (1)| 00:01:29 | | |
|* 7 | HASH JOIN | | 27 | 3186 | 7298 (1)| 00:01:28 | | |
| 8 | NESTED LOOPS | | | | | | | |
| 9 | NESTED LOOPS | | 102 | 10302 | 978 (0)| 00:00:12 | | |
| 10 | NESTED LOOPS | | 11 | 638 | 37 (0)| 00:00:01 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | ACQ_ACCT | 11 | 253 | 4 (0)| 00:00:01 | | |
|* 12 | INDEX RANGE SCAN | AA_PK | 16 | | 2 (0)| 00:00:01 | | |
| 13 | TABLE ACCESS BY INDEX ROWID | ROUTES | 1 | 35 | 3 (0)| 00:00:01 | | |
|* 14 | INDEX RANGE SCAN | R_A_BIDX | 1 | | 2 (0)| 00:00:01 | | |
| 15 | PARTITION RANGE ALL | | 95 | | 84 (0)| 00:00:02 | 1 | 84 |
|* 16 | INDEX RANGE SCAN | P_R_ID_BIDX | 95 | | 84 (0)| 00:00:02 | 1 | 84 |
| 17 | TABLE ACCESS BY LOCAL INDEX ROWID| PAYMENTS | 9 | 387 | 100 (0)| 00:00:02 | 1 | 1 |
| 18 | PARTITION RANGE ALL | | 107K| 1782K| 6319 (1)| 00:01:16 | 1 | 87 |
|* 19 | TABLE ACCESS FULL | JOURNAL | 107K| 1782K| 6319 (1)| 00:01:16 | 1 | 87 |
| 20 | PARTITION RANGE ITERATOR | | 4 | | 2 (0)| 00:00:01 | KEY | KEY |
|* 21 | INDEX RANGE SCAN | ATX_A_IDX | 4 | | 2 (0)| 00:00:01 | KEY | KEY |
| 22 | TABLE ACCESS BY LOCAL INDEX ROWID | ACCT_TX | 4 | 64 | 3 (0)| 00:00:01 | 1 | 1 |
|* 23 | INDEX RANGE SCAN | AB_B_A_IDX | 5006 | 85102 | 8 (0)| 00:00:01 | | |
|* 24 | INDEX UNIQUE SCAN | ACC_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | ACCT | 1 | 9 | 2 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------
2 ответа
Таким образом, после более тщательного изучения кода данные отображаются на основе столбцов, перечисленных в SELECT В рамках запроса я заметил, что последняя присоединенная таблица не вносит никакого вклада (не требует отображения каких-либо данных из нее) в вывод.
join acq_acct aa on aa.acq_code = r.acq_code
and aa.acq_acct_code = r.acq_acct_code
and aa.slc = 'MXM'
Следовательно, я переместил этот запрос в EXISTS пункт и повторно запустил запрос. Мой измененный запрос выглядит так:
select atx.journal_id
,ab.c_date
from acct_batch ab
join acct_tx atx on ab.acct_id = atx.acct_id
and ab.batch_id = atx.batch_id
join journal j on j.journal_id = atx.journal_id
and j.journal_type_id = 6
join acct a on a.acct_id = atx.acct_id
and a.acct_type_id = 32
join payments p on p.payment_id = j.payment_id
join routing r on r.route_id = p.route_id
and r.acq_code = 'RZ_NS'
where ab.c_date between to_date(to_char('01-JUL-2015')) and last_day(sysdate)
and exists (select 1
from acq_acct aa
where aa.acq_code = r.acq_code
and aa.acq_acct_code = r.acq_acct_code
and aa.slc = 'MXM');
Это помогло улучшить стоимость моего запроса с 7388 до 292, что является колоссальной разницей.
Надеюсь, я правильно понял это, и мое объяснение также имело смысл.
Если кто-то считает, что мои выводы были неверными или "логические рассуждения" были неверными, пожалуйста, оставьте комментарий (сейчас мои выводы / объяснения сверху имеют смысл для меня).
Сначала убедитесь, что ваша статистика обновляется: оптимизатор сильно зависит от статистики! Во-вторых, вы должны сказать что-то о количестве строк, которые вы получаете с помощью этого запроса: в зависимости от количества строк, которые выбирает каждое условие, может быть лучше полное сканирование, чем поиск по индексу.