воскресенье, 14 февраля 2016 г.

Oracle: кеширование планов со связанными bind переменными

Эта заметка краткий пересказ статьи https://habrahabr.ru/company/postgrespro/blog/275755/

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 переменные.