С помощью трассировки 10046 удалось установить наличие подобных приведенному ниже проблемных запросов
INSERT INTO TMP_CALC_XXX_ROWS ...
SELECT /*+ dynamic_sampling(5)*/ ...
FROM TMP_CXX_CALC_PRMS, TMP_CALC_XXX_ROWS
WHERE ...
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1985.84 1944.19 404 6915999 39854 18197
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1985.84 1944.19 404 6915999 39854 18197
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 223 (recursive depth: 3)
Rows Row Source Operation
------- ---------------------------------------------------
18197 HASH GROUP BY (cr=6915137 pr=0 pw=0 time=1329164718 us)
37079 NESTED LOOPS (cr=6915137 pr=0 pw=0 time=1941458873 us)
18197 TABLE ACCESS FULL TMP_CXX_CALC_PRMS (cr=210 pr=0 pw=0 time=109235 us)
37079 INLIST ITERATOR (cr=6914927 pr=0 pw=0 time=1941202535 us)
37079 TABLE ACCESS BY INDEX ROWID TMP_CALC_XXX_ROWS (cr=6914927 pr=0 pw=0 time=1940822545 us)
680003693 INDEX RANGE SCAN XIF_TCPR_SET2 (cr=1310251 pr=0 pw=0 time=680812352 us)(object id 1059417)
Таблицы TMP_CXX_CALC_PRMS и TMP_CALC_XXX_ROWS - обе global temporary, статистика по ним не собирается, поскольку все вычисления - одна транзакция, а таблицы по ходу исполнения неоднократно обновляются (пришлось бы достаточно часто перезапускать процедуры сбора статистики, что на этапе разработки казалось слишком дорогостоящим).
Путем несложных рассуждений, можно сформулировать более оптимальный план для этого запроса, а именно: полный просмотр обеих таблиц с последующим HASH-соединением.
Собственно, проблема состоит в том, что оптимизатор сам почему-то не догадался применить такой план. Поиск причин этого - и есть предмет данной статьи.
Перед тем, как начать свои исследования, я попытался набросать план действий. Вышло примерно следующее:
1) попытаться создать простой test case, который бы воспроизводил проблему;
2) попытаться сузить круг условий, при которых проблема проявляется;
3) трассировать оптимизатор (10053), чтобы понять причины происходящего.
Архив с тестовыми скриптами можно скачать отсюда.
Итак, по порядку.
Во всех скриптах используются одинаковые по структуре таблицы с одинаковым наполнением. Их можно воспроизвести на любой системе.
Скрипт _case1.sql показывает, как влияет агрессивность динамического семплирования на план исполнения. В _case1.log представлены соответствующие результаты: четко видно, что без семплирования, используя дефолтные значения (напомню: статистика отсутствует), оптимизатор генерирует приличный план (LIO=3968), во всех остальных случаях - LIO~240509. Разница очень существенна, да и по планам видно, что имитация проблемы промышленной системы, в общем, удалась.
Следующий эксперимент - _case2.sql и результаты _case2.log: все то же самое, но таблицы обычные, не временные. Здесь картина полностью противоположная: без семплирвоания - крах, с семплированием - очень даже неплохо! Такая ситуация наталкивает на мысль, что динамическое семплирование для временных таблиц работает как-то по-другому.
Идем далее - _case3.sql и _case3.log: здесь попытка сравнить запрос без семплирования с такими же запросами, как в первом експерименте (без семплирования и с оным), но предварительно собрав статистику. Видно, что со статистикой мы получаем более-менее нормальный план. Единственное, что в моем промышленном случае вот так собрать статистику - нет возможности.
Ну и последний эксперимент - без статистики, максимально агрессивное семплирование, с трассировкой оптимизатора: _case4.sql, _case4.log - временная таблица; _case5.sql, _case5.log - постоянная таблица. Соответствующие трассы оптимизатора - _case4_wo_stats.trc и _case5_wo_stats.trc.
Как и следовало ожидать, для временной таблицы - план не оптимален, для постоянной - оптимален.
Здесь, как говорится, начинается развлечение для интеллектуалов - найти разницу в двух трассировочных файлах, чтобы понять причину разного поведения CBO. После нескольких потраченных часов начала вырисовываться картина происходящего. В общем, первая существенная разница видна в том, что Join order[2] секции SM Join (with index on outer) для временной таблицы признан победителем, тогда как для постоянной - эта секция показывает существенно более высокую стоимость 2441, против 885 для временной.
Далее я пытался понять причину такой разницы (остальные способы соединения и доступа больше можно не рассматривать, поскольку очень вероятно, что в них нет ничего интересного). Вернувшись в начало файлов, я обнаружил одну интересную деталь:
1) для временных таблиц была такая запись "** Dynamic sampling could not determine index block count: TT_TEST_CBO1_IDX2";
2) для постоянных таблиц - "** Dynamic sampling updated index stats.: TT_TEST_CBO1_IDX2, blocks=1580".
Проверяя стоимости планов с калькулятором и выясняя откуда взялись стоимости, становится ясно, что для временной таблицы - ввиду указанной выше записи - были использованы дефолтные значения статистики индекса, а именно:
Index Stats::
Index: TT_TEST_CBO1_IDX2 Col#: 2 (NOT ANALYZED)
LVLS: 1 #LB: 25 #DK: 100 LB/K: 1.00 DB/K: 1.00 CLUF: 800.00
а для постоянной - все тоже самое
Index Stats::
Index: TT_TEST_CBO1_IDX2 Col#: 2 (NOT ANALYZED)
LVLS: 1 #LB: 25 #DK: 100 LB/K: 1.00 DB/K: 1.00 CLUF: 800.00
но статистика #LB была определена в процессе динамического семплирования, и равна 1580.
Вот откуда и получилась заниженная стоимость доступа по индексу для временной таблицы со всеми вытекающими последствиями!
Для временной таблицы:
SM Join (with index on outer)
Access Path: index (FullScan)
Index: TT_TEST_CBO1_IDX2
resc_io: 826.00 resc_cpu: 7132309
ix_sel: 1 ix_sel_with_filters: 1
Cost: 826.65 Resp: 826.65 Degree: 1
Outer table:
resc: 826.65 card 33428.00 bytes: 28 deg: 1 resp: 826.65
стоимость = 1+25*1+800*1 = 826!
Для постоянной -
SM Join (with index on outer)
Access Path: index (FullScan)
Index: TT_TEST_CBO1_IDX2
resc_io: 2381.00 resc_cpu: 18206149
ix_sel: 1 ix_sel_with_filters: 1
Cost: 2382.65 Resp: 2382.65 Degree: 1
Outer table:
resc: 2382.65 card 33428.00 bytes: 28 deg: 1 resp: 2382.65
стоимость = 1+1580*1+800*1 = 2381! (Учитываем округления!)
Вот, собственно, и все. Своему коллеге я посоветовал либо пользоваться хинтами, дабы направить оптимизатор в нужном направлении, либо удалить индексы, чтобы не "смущать" оптимизатор их заниженной стоимостью (после удаления индексов необходимость в подсказке dynamic_sampling не отпадает). Пожертвовать индексами в моем случае вполне допустимо, ибо как, известно, что в оптимальных планах (запросов моего коллеги, естественно :0) они не используются.
Остается единственный вопрос: почему для временной таблицы динамическое семплирование собирает меньше информации, чем для постоянной?
Литератрура:
Jonathan Lewis. Cost-Based Oracle Fundamentals.
Том Кайт: о выборке, хранении и индексировании
No comments:
Post a Comment