- Оптимизация запросов со связанными bind переменными
- Cardinality feedback
- Dynamic Sampling
- Oracle 12: Адаптивные планы
Оптимизация запросов со связанными bind переменными
1. При первом разборе происходит полный разбор запроса (hard parse)
План запроса помещается в глобальный кэш БД с определенным sql_id
2. При повторном выполнении происходит частичный разбор (soft parse)
Происходит только синтаксический разбор, проверки прав доступа и проверки bind переменных. Что для разных вариаций sql_id создает дочерние child_sql_id
Из-за такого механизма работы Oracle вытекает частая проблема oltp систем, где существует огромное число маленьких запросов, отличающихся друг от друга только фильтрами или параметрами. Это приводит к быстрому вытеснению планов из кэша и их последующему повторному hard parse.
В итоге может оказаться, что большую часть времени БД занимается разбором запросов, а не собственно их выполнением.
Отсюда вывод: по возможности используйте bind переменные в вариациях одного запроса, замен константных фильтров, т.к. это даст нам только один план запроса (child_sql_id) при разных значениях переменных на равномерно распределенном столбце.
Я не зря сказал ранее "на равномерно распределенном столбце", т.к. с bind переменными есть проблема: по умолчанию Oracle не знает какие данные будут переданы в запрос и из-за этого может сгенерить неверный план запроса.
Посмотрим на примере по умолчанию. Создадим таблицу с неравномерно распределенным столбцом "n" (9 строк со значением = 1, и 1млн-9 строк со значением 2):
create table t as select level as id, case when level < 10 then 1 else 2 end as n from dual connect by level < 1000000; create index t_i on t(n); begin dbms_stats.gather_table_stats(user,'T',method_opt=>'for columns n size 1'); end;Столбец не имеет гистограмм, но есть статистика по уникальным значениям. Построим план запроса с bind переменной = 1:
explain plan for select * from t where n = :n;
select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500K| 3906K| 508 (2)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| T | 500K| 3906K| 508 (2)| 00:00:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=TO_NUMBER(:N))
Oracle закономерно ожидает в результате половину таблицу и выбирает full scan, хотя мы то знаем, что тут был бы лучше Index scan.К счастью с 11 версии Oracle может заглядывать в значения bind переменных и подбирать под них нужные планы.
Для этого соберем гистограмму с 2 вершинами и повторим эксперимент:
begin dbms_stats.gather_table_stats(user,'T',method_opt=>'for columns n size 2'); end; var n number; exec :n := 1 select count(*) from t where n = :n; select * from table(dbms_xplan.display_cursor(format=>'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 4qjwcfhq4s9vt, child number 2 ------------------------------------- select count(*) from t where n = :n Plan hash value: 4142320527 ------------------------------------------- | Id | Operation | Name | E-Rows | ------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | SORT AGGREGATE | | 1 | |* 2 | INDEX RANGE SCAN| T_I | 185 | -------------------------------------------Oracle сгенерировал новый child_sql_id под новое значение bind переменной и выбрал правильный доступ по индексу.
Данный план был закеширован в глобальную память и если прямо сейчас выполнить заново с параметром 2, то мы получим тотже план (child number 2).
Замечу что на этом этапе уже надо смотреть план уже выполненного запроса, т.к. oracle не умеет показывать план и заглядывать в bind переменные, но при реальном выполнении запроса значения bind переменных смотрятся.
exec :n := 2 select count(*) from t where n = :n; select * from table(dbms_xplan.display_cursor(format=>'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 4qjwcfhq4s9vt, child number 2 ------------------------------------- select count(*) from t where n = :n Plan hash value: 4142320527 ------------------------------------------- | Id | Operation | Name | E-Rows | ------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | SORT AGGREGATE | | 1 | |* 2 | INDEX RANGE SCAN| T_I | 185 | -------------------------------------------
но oracle пометит этот запрос на пересмотр, т.к. план совсем не сошелся с реальными данными и при последующем применении сгенерирует новый child_sql_id (child number 3) под нашу bind переменную:
exec :n := 2
select count(*) from t where n = :n;
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 4qjwcfhq4s9vt, child number 3
-------------------------------------
select count(*) from t where n = :n
Plan hash value: 2966233522
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| T | 999K|
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N"=:N)
Из всего этого можно сделать вывод, что вопреки частому заблуждению, Oracle умеет генерировать правильные планы по bind переменным, но делает это не сразу, а при повторном вызове и при наличии гистограммы.
Второе: реальный план запроса с bind переменными можно узнать только во время или после выполнения запроса, т.к. "explain plan" не подсматривает в bind переменные.
Cardinality feedback
Ora Blog
Oracle мониторит и исправляет следующии "estimated rows" оценки на основе реальных "actual rows"
* Single table cardinality (after filter predicates are applied)
* Index cardinality (after index filters are applied)
* Cardinality produced by a group by or distinct operator
Dynamic Sampling
Ora Blog
Применимо для запросов со сложными предикатами фильтрации, которые дают существенную ошибку оптимизатора.
Включение dynamic sampling в зависимости от параметра пробирует от 32 блоков таблицы на предикаты фильтрации и определяем реальный лучший план.
Oracle 12: Адаптивные планы
Смена плана во время выполнения запроса. На основе кол-ва данных полученных из шагов запроса генерируется оптимальный план следующего шага.
Адаптивность может сменить:
* Тип соединения NL <-> HJ
* тип parallel distribution
* Bitmap Index Pruning
