Я и ёжик
Member
Откуда: СПб
Сообщений: 1469
|
При доработке FAQ "Основные причины ошибок CBO" наткнулся на некоторые особенности поведения оптимизатора в Oracle 10g.
Тестовый пример строится полностью аналогично примеру Тома Кайта "about in memory collection" в одном из последних Oracle Magazin.
Пример у Тома ваполнен на 9iR2.
В кратце из некой базовой таблицы (emp) выбираются записи по условию, что некое поле ( emp_name) есть в списке ( IN) возвращаемом табличной функцией ( str2tbl ).
Сценарий следующий
1) Сначала запрос выполняется плохо, поскольку предположение оптимизатора о числе строк возвращаемых табличной функцией неверно (8196).
2) Вставляем хинт CARDINALITY c указанием количества строк (10)возвращаемых табличной функцией, становится лучше, но не до конца.
3) Добавив в подзапрос условие 'where rownum >= 0' добиваемся невозможности выполнить UNESTING подзапроса, или как говорит Том "материализуем" подзапрос.
И в результате получаем эффективный план где для каждой строки возвращенной из табличной функции идет доступ по индексу к базовой таблице.
Теперь делаю тоже самое на Oracle 10g ( 10.1.0.2.0 ):
1) Результат аналогичен 9i. ( тут я вру, уже тут у 10g все хорошо было, но почему пока не разобрался, будем считать случайным стечением случайных факторов :) )
2) Вставляю Cardinality:
SQL> select *
2 from emp
3 where ename in ( select /*+ cardinality(t 10 ) */ *
4 from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t
5 );
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=106 Card=10 Bytes=
910)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car
d=1 Bytes=89)
2 1 NESTED LOOPS (Cost=106 Card=10 Bytes=910)
3 2 SORT (UNIQUE)
4 3 COLLECTION ITERATOR (PICKLER FETCH) OF 'STR2TBL'
5 2 INDEX (RANGE SCAN) OF 'ENAME_IDX' (INDEX) (Cost=0 Card
=1)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
807 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
|
|
Блеск и красота, уже указания CARDINALITY оказалось достаточно для получения нормального плана, без всяких трюков с 'where rownum>0'.
Это вызвано тем, что в 10g изменилась обработка случаев View Merging и Subquery Unnesting, в оценке планов теперь участвуют планы не только после преобразования запроса, как было раньше, но и оцениваются планы до преобразования. Т.е. теперь случаев когда надо применять хинты NO_MERGE, NO_UNNEST или трюки типа 'where rownum>0' должно остаться очень мало.
Но вот тут среди нашей бочки меда находится ложка дегтя. Продолжаем эксперимент, а что же в 10g будет с запросом который в 9i давал наилучшие результаты:
SQL> select *
2 from emp
3 where ename in ( select /*+ cardinality(t 10 ) */ *
4 from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t
5 where rownum >= 0
6 );
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2675161 Card=1 Byt
es=89)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=542 Card=2946
5 Bytes=2622385)
3 1 FILTER
4 3 COUNT
5 4 FILTER
6 5 COLLECTION ITERATOR (PICKLER FETCH) OF 'STR2TBL'
Statistics
----------------------------------------------------------
29465 recursive calls
0 db block gets
538 consistent gets
191 physical reads
0 redo size
807 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
|
|
Вах, приехали, в 10g с таким запросом мы получаем результат от которого в 9i при помощи этого трюка как раз и избавлялись.
Похоже в 10g введя стоимостную оценку выполнения merging и unnesting, запросы где их нельзя выполнить почемуто решили выполнять в лоб. В трассировке event 10053, не видно даже попытки выполнить соединение в правильном порядке, толко разбор запроса и подзапроса по отдельности.
Такое поведение кассается не только табличных функций но и запросов из базовых таблиц ( с собранной и актуальной статистикой).
Например в вышеприведенном примере вместо табличной функции подставляю базовую таблицу с 3 записями и получаю такой же далекий от оптимальности план:
SQL> select *
2 from emp
3 where ename in ( select name
4 from emp_name t
5 where rownum >= 0
6 );
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=187707 Card=1 Byte
s=89)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=542 Card=2946
5 Bytes=2622385)
3 1 FILTER
4 3 COUNT
5 4 FILTER
6 5 TABLE ACCESS (FULL) OF 'EMP_NAME' (TABLE) (Cost=7
Card=3 Bytes=30)
|
|
Таким образом при переводе приложений со старых версий Oracle на 10g надо иметь ввиду, что запросы котрые были "оптимизированы" хинтованием или какими либо трюками в целях запрета View Merging или Subquery Unnesting в новой среди могут выполнятся неэффективно.
Возможно такое поведение можно изменить какими либо инициализационными параметрами, но я пока таковых не нашел (ну кроме тупого выставления optimizer_features_enable в 9.2.0 ).
P.S. Зато нашел интересный скрытый параметр для любителей хинтов _optimizer_ignore_hints, значение по умолчанию false, пока.... |
Изменение поведения оптимизатора 9i->10g [new]
Re: Изменение поведения оптимизатора 9i->10g [new]