Oracle mechanics

19.06.2011

Недорогое картезианское произведение при _optimizer_new_join_card_computation в 11.1

Filed under: CBO,hints,Optimizer features,Oracle — Игорь Усольцев @ 14:38
Tags: ,

При определённых условиях использование оптимизатором [условно] нового, более точного метода (optimizer new join cardinality computation) для расчёта кол-ва строк, получаемых в результате соединений наборов данных, может приводить к ошибочно низкой оценке стоимости и, как следствие, выбору не самых оптимальных для конкретного случая методов соединений типа merge join cartesian

«Новый» механизм расчёта появился в Oracle 9i и его использование регулируется  значением параметра _optimizer_new_join_card_computation (с описанием compute join cardinality using non-rounded input values), значение по умолчанию TRUE, т.е. этот метод расчёта используется оптимизатором

Далее — свежий пример для версии 11.1.0.7 и некоторые возможные способы решения проблемы

Проблема проявилась в резком увеличении времени выполнения запроса (из нескольких таблиц и обзора) типа

select
*
 from
 VIEW_1              v,
 ENTITY              c,
 ENTITY              l,
 ENTITY_ATTR         ea,
 ENTITY_ATTR         v,
 ENTITY_ATTR         pub,
 ENTITY_ATTR         p
 where ...

при уменьшении списка выбираемых столбцов

select
 p.e_id     as id,
 v.attr     as name,
 pub.attr   as second_name
 from
...

В плане выполнения вместо NESTED LOOPS появлялась недорогая операция MERGE JOIN CARTESIAN (с одной из внутренних таблиц обзора), обрабатывая рез-ты которой запрос мог успешно дождаться ora-01555: snapshot too old…

Проблемная (значительно увеличивающая время выполнения запроса) часть плана выполнения выглядела следующим образом:

----------------------------------------------------------------+-----------------------------------+
| Id  | Operation                         | Name                | Rows  | Bytes | Cost  | Time      |
----------------------------------------------------------------+-----------------------------------+
...
| 14  | MERGE JOIN CARTESIAN              |                     |    13 |  2470 |   689 |  00:00:08 |
| 15  |  NESTED LOOPS                     |                     |     1 |   172 |   239 |  00:00:03 |
| 16  |   NESTED LOOPS                    |                     |     1 |   136 |   238 |  00:00:03 |
| 17  |    NESTED LOOPS                   |                     |     1 |   100 |   237 |  00:00:03 |
| 18  |     NESTED LOOPS                  |                     |     1 |    64 |   236 |  00:00:03 |
| 19  |      NESTED LOOPS                 |                     |     1 |    50 |   235 |  00:00:03 |
| 20  |       TABLE ACCESS BY INDEX ROWID | ENTITY_ATTR         |    29 |  1044 |   234 |  00:00:03 |
| 21  |        INDEX RANGE SCAN           | ENTITY_ATTR_NAME_IDX|  2861 |       |     3 |  00:00:01 |
| 22  |       INDEX RANGE SCAN            | IDX_LNK_TYPE        |     1 |    14 |     1 |  00:00:01 |
| 23  |      INDEX RANGE SCAN             | PK_LINK             |     1 |    14 |     1 |  00:00:01 |
| 24  |     TABLE ACCESS BY INDEX ROWID   | ENTITY_ATTR         |     1 |    36 |     1 |  00:00:01 |
| 25  |      INDEX RANGE SCAN             | ENTITY_ATTR_ID_NAME |     1 |       |     1 |  00:00:01 |
| 26  |    TABLE ACCESS BY INDEX ROWID    | ENTITY_ATTR         |     2 |    72 |     1 |  00:00:01 |
| 27  |     INDEX RANGE SCAN              | ENTITY_ATTR_ID_NAME |     1 |       |     1 |  00:00:01 |
| 28  |   INLIST ITERATOR                 |                     |       |       |       |           |
| 29  |    TABLE ACCESS BY INDEX ROWID    | ENTITY_ATTR         |     1 |    36 |     1 |  00:00:01 |
| 30  |     INDEX RANGE SCAN              | ENTITY_ATTR_ID_NAME |     1 |       |     1 |  00:00:01 |
| 31  |  BUFFER SORT                      |                     |   20K |  358K |   688 |  00:00:08 |
| 32  |   INDEX FAST FULL SCAN            | PK_ENTITY_ATTR2     |   20K |  358K |   449 |  00:00:05 |
...
----------------------------------------------------------------+-----------------------------------+

Обращает на себя внимание картезианское произведение (MERGE JOIN CARTESIAN, строка #14) с невысокой итоговой стоимостью 689, большую часть которой составляет стоимость непростой операции BUFFER SORT (стоимостью 688), очевидно, являющейся суммой стоимостей операций NESTED LOOPS #15 (outer table соединения) и INDEX FAST FULL SCAN #32 (inner table)

239 + 449 = 688

Более важно в плане другое — картезианское соединение (#14) предположительно возвращает 13 строк, что на первый взгляд арифметически неверно, т.к. источники данных (data sources) для этого соединения: NESTED LOOPS (#15) и BUFFER SORT (#31) возвращают 1 и 20,000 строк соответственно, что при картезианском соединении должно дать 1 * 20,000 = 20,000, но никак не 13!

В трейсе 10053 можно найти описание метода, которым пользовался оптимизатор при расчёте:

***************
Now joining: ENTITY_ATTR[EA]#0
***************
NL Join
Outer table: Card: 0.00 Cost: 239.43 Resp: 239.43 Degree: 1 Bytes: 172  <<-- 239 = стоимость NESTED LOOPS(#15)
Access path analysis for ENTITY_ATTR
Inner table: ENTITY_ATTR Alias: EA

Access Path: TableScan
NL Join: Cost: 3123.74 Resp: 3123.74 Degree: 1      <<-- TABLE ACCESS FULL отклоняется из-за высокой стоимости
...
Access Path: index (index (FFS))
Index: PK_ENTITY_ATTR2
resc_io: 446.00 resc_cpu: 76941650                  <<-- ~ 449 = стоимость INDEX FAST FULL SCAN(#32)
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Inner table: ENTITY_ATTR Alias: EA
Access Path: index (FFS)
NL Join: Cost: 688.72 Resp: 688.72 Degree: 1        <<-- ~ 689 = стоимость MERGE JOIN CARTESIAN(#14)
Cost_io: 685.30 Cost_cpu: 79911587                           = стоимость BUFFER SORT(#31) + поправка
Resp_io: 685.30 Resp_cpu: 79911587                           = IFFS(#32) + NL(#15) + 1
kkofmx: index filter:"EA"."ATTR_NAME"='tovar_id'

Access Path: index (FullScan)                       <<-- INDEX FULL SCAN отклоняется из-за высокой стоимости
Index: PK_ENTITY_ATTR2
...
NL Join : Cost: 710.96 Resp: 710.96 Degree: 1
Cost_io: 710.40 Cost_cpu: 12998392
Resp_io: 710.40 Resp_cpu: 12998392
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: PK_ENTITY_ATTR2
resc_io: 4711.00 resc_cpu: 85702502
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 471.47 Resp: 471.47 Degree: 0                 <<-- imho, опечатка, судя по предыдущим строкам, должно быть так:
****** finished trying bitmap/domain indexes ******   -- Cost: 4714.7 Resp: 4714.7 Degree: 0

Best NL cost: 688.72
resc: 688.72 resc_io: 685.30 resc_cpu: 79911587
resp: 688.72 resp_io: 685.30 resc_cpu: 79911587
Join Card: 12.694069 = = outer (0.000623) * inner (20371.000000) * sel (1.000000) Join Card - Rounded: 13 Computed: 12.69
Best:: JoinMethod: NestedLoop
Cost: 688.72 Degree: 1 Resp: 688.72 Card: 12.69 Bytes: 190

Чтобы получить показанную на шаге #14 плана Join Cardinality (13) оптимизатор использует «уточнённую» до 6-го знака стоимость кол-ва строк внешней таблицы соединения outer (0.000623) :

Join Card: 12.694069 = = outer (0.000623) * inner (20371.000000) * sel (1.000000) Join Card - Rounded: 13 Computed: 12.69

что не совсем понятно на физическом уровне, т.к. представить себе подзапрос, возвращающий чуть больше 6/1000 строки, или картезианское произведение с множеством, состоящим менее, чем из одного члена, достаточно трудно ;) Однако, судя по постоянному (по умолчанию) использованию, метод оправдан для расчётов и даёт хорошие результатыты для большинства случаев (к сожалению, не для рассматриваемого или, возможно, не при использовании MERGE JOIN CARTESIAN в качестве метода соединения)

Исключить возможность выбора оптимизатором неудачного (ошибочного) способа соединения можно следующими способами

Отключая упомянутый механизм optimizer new join cardinality computation на уровне сессии

SQL> alter session set "_optimizer_new_join_card_computation" = false;

Session altered.

или на уровне отдельного запроса

select/*+ opt_param('_optimizer_new_join_card_computation', 'false')*/
 p.e_id     as id,
 v.attr     as name,
 pub.attr   as second_name
 from some_view_1 v,
 ...

Модифицируя текста запроса (меняя синтаксис или применяя подсказки, если возможно) для исключения возможности картезианского произведения с одной из внутренних таблиц обзора VIEW_1

1) добавляя поле (препятствующее картезианскому произведению) в список выбираемых полей

select
 p.e_id     as id,
 v.attr     as name,
 pub.attr   as second_name,
 C.TOVAR_ID
 from
 VIEW_1              v,
 ENTITY              c,
 ENTITY              l,
 ENTITY_ATTR         ea,
 ENTITY_ATTR         v,
 ENTITY_ATTR         pub,
 ENTITY_ATTR         p
 where ...

2) либо исключая возможность MERGE JOIN с обзором, используя subquery factoring

with v as (select * from some_view_1)
 select
 p.e_id      as id,
 v.attr      as name,
 pub.attr    as second_name
 from v,
 ENTITY              c,
 ENTITY              l,
 ENTITY_ATTR         ea,
 ENTITY_ATTR         v,
 ENTITY_ATTR         pub,
 ENTITY_ATTR         p
 where ...

3) либо просто исключая возможность MERGE JOIN подсказкой NO_MERGE

select/*+ NO_MERGE(v)*/
 p.e_id      as id,
 v.attr      as name,
 pub.attr    as second_name
 from
 VIEW_1 v,
 ...

4) либо отключая подсказкой возможность любых преобразований запроса оптимизатором (грубый, но эффективный в данном случае метод)

select/*+ NO_QUERY_TRANSFORMATION*/
 p.e_id      as id,
 v.attr      as name,
 pub.attr    as second_name
 from
 VIEW_1 v,
 ...

5) либо отключая только возможность преобразований типа complex view merging для сессии:

SQL> alter session set "_complex_view_merging" = false;

Session altered.

или подсказкой на уровне запроса:

select/*+ opt_param('_complex_view_merging', 'false')*/
 p.e_id      as id,
 v.attr      as name,
 pub.attr    as second_name
 from
 VIEW_1 v,
 ...

Описанный случай похож на Bug 8427248: SUBOPTIMAL PLAN CAUSES POOR PERFORMACE и Bug 7528216: QUERY ACCESSING A VIEW IS VERY SLOW (первый имеет статус Not a Bug, второй исправлен в 11.2)

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: