RBO или CBO?

dbstalker, 18 июля

Автор статьи: Михайлов Владимир Васильевич, разработчик баз данных Oracle, Москва.

Конечно же, обширная документация Oracle пестрит подобного рода замечаниями в отношении RBO: "Oracle Corporation strongly advises the use of cost-based optimization. Rule-based optimization will be deprecated in a future release." Однако, реальность диктует свои условия, и на сегодняшний день OPTIMIZER_MODE=RULE является её неизменной частью, и тем самым касается непосредственно меня, обыкновенного разработчика баз данных.

Теперь по теме. База данных: Oracle9i Release 9.2.0.6.0 - Production. Подсказки в запросе меняют план выполнения самого запроса при установленном OPTIMIZER_MODE=RULE на уровне инстанса. При этом в плане выполнения появляется стоимость ("cost"), само понятие относящееся к CBO, хотя в том же плане выполнения имеется строка "SELECT STATEMENT RULE". Налицо противоречие.

Возникает два предположения. Первое: имеет место быть переключение на CBO. Однако, как убедиться в этом? Не может ли случиться такое, что эти подсказки использует RBO, а появление стоимости - "побочное явление" (это второе предположение)? Какой вариант из двух правильный? Ещё одно дополнение: сбор статистики в базе не ведётся.

Находим подходящую документацию: Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2). Приведу выдержки из неё, которые имеют отношение к вышесказанному и, как кажется, способные дать исчерпывающий ответ:

1) При OPTIMIZER_MODE=RULE - The optimizer chooses a rule-based approach for all SQL statements regardless of the presence of statistics. (Chapter 1, Introduction to the Optimizer) Тут всё совпадает: режим оптимизатора соответствующий, статистика не собирается (хотя, как оказывается, это не важно);
2) Hints (except for the RULE hint) invoke the cost-based optimizer (CBO). If you have not gathered statistics, then defaults are used. (Chapter 5, Optimizer Hints). Тут уже менее понятно. Понятно, что при наличии подсказки вызывается CBO, однако, неясно, какие такие "defaults", чему они равны, и как он их использует в случае отсутствия статистики. Но в данной статье нас это мало интересует.
3) Посмотрим, что говорится о подсказках в соответствующей главе про RBO. Читаем Chapter 8, Using the Rule-Based Optimizer. Слово "hint" упоминается только один раз: "If OPTIMIZER_MODE=CHOOSE, if statistics do not exist, and if you do not add hints to SQL statements, then SQL statements use the RBO.". Может, при OPTIMIZER_MODE=RULE это условие тоже выполняется?

В итоге, документация ответила на наш вопрос: возникает переключение на CBO. Проверим?

Создаём таблицу T c двумя полями типа NUMBER и заполняем 100 тысячами записей: первое поле - от 1 до 100 тысяч, второе, скажем, числом 10. По второму полю создаём неуникальный индекс. Статистика не собрана. Пишем два запроса:

SELECT * FROM t WHERE t2 = 10   и   SELECT /*+ INDEX(t NT_T2)*/ * FROM t WHERE t2 = 10.

Выполянем оба запроса и смотрим их планы выполнения:

SELECT * FROM t WHERE t2 = 10
------------------------------------------------------------
Statement Id=0 Type=SELECT STATEMENT
Cost=0 TimeStamp=16-07-08::15::40:10
SELECT STATEMENT RULE
(3) TABLE ACCESS BY INDEX ROWID T [Not Analyzed]
(2) NON-UNIQUE INDEX RANGE SCAN NK_T2 [Not Analyzed]

SELECT /*+ INDEX(NT_T2)*/ * FROM t WHERE t2 = 10
------------------------------------------------------------
Statement Id=0 Type=SELECT STATEMENT
Cost=13 TimeStamp=16-07-08::15::40:29
(1) SELECT STATEMENT RULE
Est. Rows: 20 Cost: 13
(3) TABLE ACCESS BY INDEX ROWID T [Not Analyzed]
(3) Est. Rows: 20 Cost: 13
(2) NON-UNIQUE INDEX RANGE SCAN NK_T2 [Not Analyzed]
Est. Rows: 8 Cost: 306

Как видим, различий нет, кроме появления упомянутой стоимости на основе "defaults". Хорошо, идём далее. Используем пакет DBMS_STATS и две его процедуры gather_table_stats и gather_index_stats, указав в них только имя схемы и имя таблицы (индекса). Снова выполняем те же самые запросы и смотрим их планы выполнения:

SELECT * FROM t WHERE t2 = 10
------------------------------------------------------------
Statement Id=0 Type=SELECT STATEMENT
Cost=0 TimeStamp=16-07-08::15::41:06 SELECT STATEMENT RULE
(3) TABLE ACCESS BY INDEX ROWID T [Analyzed]
(3) Blocks: 186 Est. Rows: ?? of 100 000
(2) NON-UNIQUE INDEX RANGE SCAN NK_T2 [Analyzed]

SELECT /*+ INDEX(NT_T2)*/ * FROM t WHERE t2 = 10
------------------------------------------------------------
Statement Id=0 Type=SELECT STATEMENT
Cost=14 TimeStamp=16-07-08::15::41:22 (1) SELECT STATEMENT RULE
Est. Rows: 100 000 Cost: 14
(2) TABLE ACCESS FULL T [Analyzed]
(2) Blocks: 186 Est. Rows: 100 000 of 100 000 Cost: 14

Что мы видим? План выполнения первого запроса не изменился, всё "по правилам", то есть по RBO, как и описано в документации. А вот второго: Несмотря на подсказку, оптимизатор выбрал полное сканирование таблицы. Что это может означать? Если бы ипользовался RBO, то никаких изменений мы бы не наблюдали, как и в представленном случае запроса без подсказки, поскольку для RBO ничего не изменилось (статистика RBO недоступна). Однако, всё говорит о том, что в действительности сработал CBO, для которого подсказки - лишь подсказки, никак не директивы. Предложение "SELECT STATEMENT RULE" в планах выполнения, по-видимому, означает текущее состояние параметра OPTIMIZER_MODE, а не выбранный при построении плана выполнения тип оптимизатора.

Итог. Применением подсказки мы добились изменения плана выполения. Получение иного плана выполнения может оказаться достаточным для решения конкретной задачи, хотя и ограниченным по сравнению со случаем наличия актуальной статистики, когда вариантов планов выполнения может оказаться больше, да и сами они могут быть более подходящими, чем при её отсутствии. В Chapter 1, Introduction to the Optimizer, эти возможности, доступные CBO, перечислены (The following features require use of the CBO):

  • Partitioned tables and indexes
  • Index-organized tables
  • Reverse key indexes
  • Function-based indexes
  • SAMPLE clauses in a SELECT statement
  • Parallel query and parallel DML
  • Star transformations and star joins
  • Extensible optimizer
  • Query rewrite with materialized views
  • Enterprise Manager progress meter
  • Hash joins
  • Bitmap indexes and bitmap join indexes
  • Index skip scans

Вот и всё!

P.S.: правда, в той же документации, в той же Chapter 5 есть описание подсказки DRIVING_SITE: "The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.". Что бы могло означать второе предложение из цитаты?.. Продолжение следует?

2 комментария

Прокоментировать

Vladimir
24 июля 2008 г. в 17:58

Всё-таки, при дальнейшем изучении документации было обнаружено предложение о том, что подсказки кроме RULE включают CBO (та же Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2), Chapter 5, Optimizer Hints): "Hints (except for the RULE hint) invoke the cost-based optimizer (CBO). If you have not gathered statistics, then defaults are used.". То есть, если тщательно искать и обязательно в разных местах, то найдёшь ответы на многие вопросы!

Данил
11 декабря 2008 г. в 10:16

В дополнение: в 10.2.0.3 использование ANSI-синтаксиса и left join почти всегда приводит к игнорированию RBO и использованию CBO, а вот если переписать запрос через (+), то как правило всё ок.

 

Новый комментарий

Я не спамер: введите суму 9+5



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Печь булерьян в дом
21 сентября, 1 ответа
Как Открыть Футбольную Школу
20 сентября, 1 ответа
IP телефония
20 сентября, 1 ответа