Проблема с оптимизатором Oracle в UNION ALL
Я довольно смущен таким поведением оптимизатора Oracle. Это связано с объединением всех операций из CTE. Если у кого-то есть идеи, я весь в игре.
--Relevant data structures:
--t_positionperf (index: POSITIONPERF_X1 (account_id, hist_date, security_id)
--t_positionhist (index: POSITIONHIST_X1 (position_id, hist_date, hist_type)
-- PK_T_POSITIONHIST (hist_date, position_id, hist_type)
--v_positiontype (very simple "case-when" translation of a tiny lookup table)
with q as (
select
pp.position_id, pp.hist_date, pp.account_id, pp.income, pp.expense,
ph.position_type_id, ph.price, ph.quantity, ph.factor, ph.daily_accrual,
n.daily_accrual as new_daily_accrual, nvl(n.is_loan, v.is_loan) as new_is_loan
from
t_positionperf pp
left outer join t_positionhist ph
on pp.position_id = ph.position_id
and pp.hist_date = ph.hist_date
and ph.hist_type = 'O' --the 'O' join set from t_positionhist
left outer join v_positiontype v
on ph.position_type_id = v.position_type_id
left outer join (
select
x.position_id, x.hist_date, x.daily_accrual, v2.is_loan
from t_positionhist x
join v_positiontype v2 on x.position_type_id = v2.position_type_id
where x.hist_type = 'N'
) n --the 'N' join set from t_positionhist
on ph.position_id = n.position_id
and ph.hist_date = n.hist_date
where pp.account_id in (5018,5312)
and pp.hist_date between to_date('01-jan-14') and to_date('31-jan-14')
)
select
q.account_id,q.hist_date,q.position_id,q.income,
q.expense,q.position_type_id,q.price,q.quantity,q.factor
from q
Это использует разумный путь доступа.
Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
37640 consistent gets
5115 physical reads
0 redo size
227442 bytes sent via SQL*Net to client
4952 bytes received via SQL*Net from client
409 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6116 rows processed
Execution Plan:
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 941 | 88454 | 5283 (1)| 00:01:04 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS OUTER | | 941 | 88454 | 5283 (1)| 00:01:04 |
|* 3 | HASH JOIN RIGHT OUTER | | 941 | 71516 | 2456 (1)| 00:00:30 |
| 4 | TABLE ACCESS FULL | T_POSITIONTYPE | 64 | 1216 | 3 (0)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 941 | 53637 | 2452 (1)| 00:00:30 |
| 6 | INLIST ITERATOR | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| T_POSITIONPERF | 941 | 22584 | 567 (1)| 00:00:07 |
|* 8 | INDEX RANGE SCAN | POSITIONPERF_X1 | 941 | | 6 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | T_POSITIONHIST | 1 | 33 | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | POSITIONHIST_X1 | 1 | | 1 (0)| 00:00:01 |
| 11 | VIEW PUSHED PREDICATE | | 1 | 18 | 3 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 27 | 4 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | T_POSITIONHIST | 1 | 20 | 3 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | POSITIONHIST_X1 | 1 | | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | T_POSITIONTYPE | 1 | 7 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_T_POSITIONTYPE | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Однако, если вы добавляете UNION ALL к этому, выберите из CTE, чтобы запрос выглядел следующим образом:
with q as (...)
select q.account_id, q... etc.
UNION ALL
select q.account_id, max(q...) etc.
group by q.account_id
Оптимизатор теперь хочет материализовать CTE (TEMP TABLE TRANSFORMATION). Эта часть имеет смысл для меня, потому что СОЮЗ ВСЕХ из того же CTE. Чего я не понимаю, так это того, что материализация CTE заставляет его выбирать другой (и гораздо худший) путь доступа. Кажется, что при создании набора соединений N теряется возможность помещать предикат в таблицу T_POSITIONHIST. Вы можете видеть, что теперь он выполняет полное сканирование таблицы в T_POSITIONHIST, и в результате дисковый ввод / вывод прошел через крышу:
Statistics
----------------------------------------------------------
362 recursive calls
53 db block gets
546116 consistent gets
521527 physical reads
688 redo size
250541 bytes sent via SQL*Net to client
4952 bytes received via SQL*Net from client
409 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6117 rows processed
Execution Plan
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1882 | 205K| 13 (54)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6CB3_54C7EBF8 | | | | |
|* 3 | FILTER | | | | | |
|* 4 | HASH JOIN OUTER | | 941 | 81867 | 147K (3)| 00:29:32 |
|* 5 | HASH JOIN RIGHT OUTER | | 941 | 63047 | 2456 (1)| 00:00:30 |
| 6 | VIEW | V_POSITIONTYPE | 64 | 448 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T_POSITIONTYPE | 64 | 448 | 3 (0)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 941 | 56460 | 2452 (1)| 00:00:30 |
| 9 | INLIST ITERATOR | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| T_POSITIONPERF | 941 | 22584 | 567 (1)| 00:00:07 |
|* 11 | INDEX RANGE SCAN | POSITIONPERF_X1 | 941 | | 6 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | T_POSITIONHIST | 1 | 36 | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | POSITIONHIST_X1 | 1 | | 1 (0)| 00:00:01 |
| 14 | VIEW | | 2358K| 44M| 145K (3)| 00:29:02 |
| 15 | VIEW | | 2358K| 51M| 145K (3)| 00:29:02 |
|* 16 | HASH JOIN | | 2358K| 67M| 145K (3)| 00:29:02 |
| 17 | VIEW | V_POSITIONTYPE | 64 | 448 | 3 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | T_POSITIONTYPE | 64 | 448 | 3 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | T_POSITIONHIST | 2358K| 51M| 145K (3)| 00:29:01 |
| 20 | UNION-ALL | | | | | |
| 21 | VIEW | | 941 | 106K| 6 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6CB3_54C7EBF8 | 941 | 56460 | 6 (0)| 00:00:01 |
| 23 | HASH GROUP BY | | 941 | 99K| 7 (15)| 00:00:01 |
| 24 | VIEW | | 941 | 99K| 6 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6CB3_54C7EBF8 | 941 | 56460 | 6 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Странная часть, вы можете частично обойти эту проблему, дублируя CTE, вот так:
with q as (...), q2 as (.../*duplicate of q*/)
select q.account_id, q... etc.
UNION ALL
select q2account_id, max(q2...) etc.
group by q2.account_id
Поскольку он больше не хочет материализовать результирующий набор, он может использовать более умный путь доступа, за исключением того, что он должен сделать это дважды, поэтому стоимость примерно удваивается (но все же намного меньше, чем действительно плохой путь доступа, который он использует выше).
Статистика
57 recursive calls
0 db block gets
73855 consistent gets
0 physical reads
0 redo size
202937 bytes sent via SQL*Net to client
4953 bytes received via SQL*Net from client
409 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6117 rows processed
1 ответ
Так что в конце концов я нашел ответ: очевидно, решение оптимизатора материализовать CTE (блок "С") нарушит способность оптимизатора выполнять преобразование запроса на основе затрат, что, в свою очередь, нарушает способность выполнять определенные операции, такие как Join Predicate Pushdown (который является предикат с заданным представлением в плане объяснения (см. выше) и объединение сложных представлений (метод переписывания блоков запросов). Это может привести к тому, что оптимизатор сделает ужасные вещи, такие как ненужное полное сканирование таблицы в больших таблицах, когда вы ожидаете, что он будет использовать индексированный доступ из предикатов, прикрепленных к запросу.
Я столкнулся с проблемой в нескольких других ситуациях и в конечном итоге столкнулся со следующим поучительным постом блога при проведении исследования: http://oracle-randolf.blogspot.com/2008/01/subquery-factoring-and-cost-based-query.html
Похоже, что первоначально любое использование CTE отключало CBQT, но в 11.2.0.3 оптимизатор будет использовать CBQT, если представления WITH являются встроенными (т. Е. НЕ материализованы), поэтому теперь проблема видна только в том случае, если для просмотра выбран вариант WITH. Смотрите комментарий по ссылке:Тимур Ахмадеев сказал... просто хочу отметить, что проблема больше не существует в 11.2.0.3. Отслеживается в разделе "разрешить CBQT, если встроены все представления WITH" (11740670)
Обе эти две стратегии объясняются этим: (1) дублирование представления WITH и (2) предложение Ярослава Шабалина использовать подсказку INLINE