Показаны сообщения с ярлыком explain. Показать все сообщения
Показаны сообщения с ярлыком explain. Показать все сообщения

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

Oracle: адаптивные технологии оптимизации запросов

Метки: , , , , ,

Оптимизация запросов со связанными 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
0 коммент.

воскресенье, 28 июля 2013 г.

SQLite: две особенности этой базы

Метки: , , , , , , , ,
Хочу рассказать про 2 отличия SQLite от других БД, решение которых придется долго искать, если вы только начинаете работать с ней.

1. Приведение к верхнему регистру UPPER юникод строк UTF8.
Необходимость в этом у меня появилась при регистронезависимом поиске по подстроке LIKE и как оказалось поумолчанию эта функций в SQLite нет и предлагается самим ее реализовать.
В интернете множество вариантов решения через настройку SQLite и доустановку дополнительных пакетов. Но мне такой способ не подходил, т.к. на шаред хостинге это не сделать.

К счастью SQLite позволяет делать собственные пользовательские функции, чем мы и воспользуемся.
Далее код на php для создания собственной функции UPPER - UPPER_UTF8. 
createFunction('UPPER_UTF8', 'upper_ru', 1);
//array('SQLITE_DB', 'upper_ru') - если используете в составе класса 'SQLITE_DB'

?>
После этих нехитрых манупуляций в составе SQLite появится новая функция UPPER_UTF8 и с помощью ее можно будет делать регистронезависимые запросы: 
SELECT 
  t.* 
FROM
  table t
WHERE
  UPPER_UTF8(t.col) LIKE UPPER_UTF8('%кАкойТо Запрос%')

2. Правильное создание индексов для поиска по диапазону в БД SQLite.
Если вы хотите осуществить быстрый поиск по таблице, то во всех субд для этого необходимо создать индекс. Но в SQLite еще нужно соблюсти правильную последовательность.
Так если вы создаете индекс по нескольким полям, то столбец диапозона должен обязательно идти последним.

Далее 2 примера, сначала неправильный: 
--таблица
CREATE TABLE "cashes" (
    "id" INTEGER PRIMARY KEY  NOT NULL,
    "date" DATE NOT NULL,
    "uid" INT(10) NOT NULL DEFAULT ('1'),
    "visible" TINYINT(4) NOT NULL DEFAULT ('1')
);

--индекс
CREATE INDEX "XIF_CASHES_DUV" on cashes (date DESC, uid ASC, visible ASC);

--вызов плана
EXPLAIN QUERY PLAN SELECT
      c.id, c.uid, c.date
     FROM cashes c
     WHERE
      c.date BETWEEN '2013-04-01' AND '2013-06-01'
      AND c.uid = 1 AND c.visible = 1
     ORDER BY
      c.date;

--план
SEARCH TABLE cashes AS c USING INDEX XIF_CASHES_USR (uid=?) (~2 rows)
А дальше, как нужно было правильно создавать индекс. Поле даты - поле диапозона, должно было идти последним: 
--индекс
CREATE INDEX "XIF_CASHES_DUV" on cashes (uid ASC, visible ASC, date DESC);

--план запроса
SEARCH TABLE cashes AS c USING INDEX XIF_CASHES_DUV (uid=? AND visible=? AND date>? AND date<?) (~42 rows)
Причем если бы мы искали точное соответствие по дате, то сработали бы оба индекса, но при поиске по диапазону только последний.
0 коммент.
Подписаться на: Сообщения (Atom)