Оптимизация SQL: методы уточнения стоимости в Oracle 11g Борчук Леонид

2 Сложность современных оптимизаторов Источники ошибок оценок стоимости Вид статистики Количество параметровПример параметраОбъектов Системная9spuspeed1 Таблица6num_rowsK Секция4num_rowsL Столбец гистограммаM Индекс7clustering_factorN Секция индекса5clustering_factorO 9+6xK+4xL+1xM+7xN+5xO Общее количество параметров:

3 Источники ошибок оценок стоимости Классификация ошибок Отсутствующая статистика Устаревшая статистика Неполная статистика Корреляция значений Нереляционные элементы Bind переменные

4 Пути устранения ошибок Источники ошибок оценок стоимости До выполнения запроса (сбор статистики) Во время выполнения запроса (динамическая оценка) После выполнения запроса (поправочные коэффициенты)

5 Устаревшая статистика 1.Изменение данных производится постоянно, в то время как сбор статистики происходит по требованию. 2. Принципиальная проблема: определение полезности сбора статистики до его начала. 3.Различного рода синтетические критерии определения полезности. Пороговое значение количества измененных строк таблицы с момента последнего сбора статистики. 4.В 11g произошло множество изменений стандартной процедуры сбора статистики, что сделало ее более гибкой. Уточнение статистики до выполнения запроса

6 Изменения DBMS_STATS 1.Изменился механизм автоматического запуска процедуры сбора статистики: процедура GATHER_STATS_JOB была заменена системой Automatic Maintenance Tasks Management. Более гибкое управление окном регламентного обслуживания, на каждый день недели доступны свои настройки окна обслуживания. По умолчанию в будние дни окно обслуживания длится 4 часа (с 10 вечера до 2 утра), в выходные дни - 20 часов (с 6 утра до 2 ночи). 2.Изменился механизм установки параметров сбора статистики по объектам базы данных. Устаревшая процедура SET_PARAM была заменена семейством процедур SET_*_PREFS (GLOBAL, DATABASE, SCHEMA, TABLE), позволяющих устанавливать свои параметры сбора статистики на каждом из классе объектов, вплоть до уровня конкретной таблицы.

7 Изменения DBMS_STATS Уточнение статистики до выполнения запроса 3. Новый алгоритм DBMS_STATS.AUTO_SAMPLE_SIZE – позволяет корректно оценивать NDV. 4. Новая функция DBMS_STATS.CREATE_EXTENDED_STATS позволяет создать статистику по группе колонок таблицы или по выражению. 5. Появилась возможность сбора инкрементальной статистики (параметр INCREMENTAL), позволяющая не выполнять полное сканирование секционированной таблицы для обновления глобальной статистики. 6. Появилась возможность одновременного сбора статистики по разным объектам (параметр CONCURRENT)

8 Уточнение статистики во время выполнения запроса Dynamic sampling Позволяет собрать перед оптимизацией отсутствующую статистику по объектам запроса: 1.На уровне экземпляра или сессии, для всех запросов. 2.На уровне запроса. В 11g: 1.По умолчанию OPTMIZER_DYNAMIC_SAMPLING равен 2. 2.На уровне сессии работает только для таблиц с отсутствующей статистикой

9 Dynamic sampling. Пример SQL> create table t1 2 as 3 select * 4 from all_objects 5 where rownum explain plan for select * from t1; | Id | Operation | Name | Rows | Bytes | Cost | Time | | 0 | SELECT STATEMENT | | | | 33 | | | 1 | TABLE ACCESS FULL | T1 | 499 | 77K | 33 | 00:00:01 | Note dynamic sampling used for this statement (level=2) SELECT NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT 1 AS C1, 1 AS C2 FROM T1) ===================== PARSING IN CURSOR #6 len=321 dep=1 STAT #4 id=1 cnt=1 STAT #4 id=2 cnt=499 ** Executed dynamic sampling query: level : 2 sample pct. : actual sample size : 499 filtered sample card. : 499 orig. card. : 2533 block cnt. table stat. : 31 block cnt. for sampling: 31 max. sample block cnt. : 64 sample block cnt. : 31 min. sel. est. : ** Using dynamic sampling card. : 499 ** Dynamic sampling updated table card. Уточнение статистики во время выполнения запроса

10 Dynamic sampling. Сложные предикаты create unique index i1 on T1 (object_id); create index i2 on T1 (object_name); create index i3 on T1 (created asc, last_ddl_time desc); select * from t1 where object_id>1000; select * from t1 where object_id>1000 and created > ' and last_ddl_time < ' ; select * from t1 where (object_name sysdate-100) and object_id>1000; SELECT NVL(SUM(C1), 0), NVL(SUM(C2), 0) FROM (SELECT 1 AS C1, CASE WHEN "T1"."OBJECT_ID" > 1000 THEN 1 ELSE 0 END AS C2 FROM "T1") SELECT NVL(SUM(C1), 0), NVL(SUM(C2), 0), NVL(SUM(C3), 0) FROM (SELECT 1 AS C1, 1 AS C2, 1 AS C3 FROM "T1" "T1" WHERE "T1"."OBJECT_ID" > 1000 AND ROWNUM

11 Dynamic sampling. Сложные предикаты (2) Уточнение статистики во время выполнения запроса SELECT NVL(SUM(C1), 0), NVL(SUM(C2), 0), NVL(SUM(C3), 0), NVL(SUM(C4), 0) FROM (SELECT 1 AS C1, CASE WHEN "T1"."OBJECT_ID" > 1000 AND "T1"."CREATED" > ' :00:00' AND "T1"."LAST_DDL_TIME" < ' :00:00' THEN 1 ELSE 0 END AS C2, CASE WHEN "T1"."CREATED" > ' :00:00' THEN 1 ELSE 0 END AS C3, CASE WHEN "T1"."OBJECT_ID" > 1000 THEN 1 ELSE 0 END AS C4 FROM "T1") ** Performing dynamic sampling initial checks. ** ** Dynamic sampling initial checks returning TRUE (level = 2). ** Dynamic sampling updated index stats.: I1, blocks=1 ** Dynamic sampling updated index stats.: I3, blocks=12 ** Dynamic sampling updated table stats.: blocks=31 ** Executed dynamic sampling query: … … … index I3 selectivity est.: index I1 selectivity est.:

12 ПредикатDynamic SamplingИндексы object_id>1000ДАI1 object_id>1000 and created > ' ' and last_ddl_time 1000 ДА, для простых предикатовI1 Dynamic sampling. Сложные предикаты (3) SELECT NVL(SUM(C1), 0), NVL(SUM(C2), 0), NVL(SUM(C3), 0) FROM (SELECT 1 AS C1, CASE WHEN ("T1"."OBJECT_NAME" ! -100 AND SYS_OP_DESCEND("T1"."LAST_DDL_TIME") < ! -100)) AND "T1"."OBJECT_ID" > 1000 THEN 1 ELSE 0 END AS C2, CASE WHEN "T1"."OBJECT_ID" > 1000 THEN 1 ELSE 0 END AS C3 FROM "T1") ** Dynamic sampling initial checks returning TRUE (level = 2). ** Dynamic sampling updated index stats.: I1, blocks=1 ** Dynamic sampling updated table stats.: blocks=31 Уточнение статистики во время выполнения запроса

13 Dynamic sampling. Типы таблиц Уточнение статистики во время выполнения запроса 1.Обычные 2.Индекс-организованные 3.Объектные 4.Вложенные 5.Временные 6.Секционированные: - Только при отсутствии глобальной статистики. Глобальная статистика позволяет вычислить поправочный коэффициент. И динамическая оценка не нужна - Запускается для секций с отсутствующей статистикой

14 Dynamic sampling на уровне запроса Уточнение статистики во время выполнения запроса 1.Управляется хинтом dynamic_sampling({level}) – общий уровень, для всех таблиц запроса 2.Управляется хинтом dynamic_sampling({alias}{level}) – включает безусловную динамическую оценки статистики по таблице. Рекомендации могут быть использованы не всегда. Не зависит от наличия статистики по таблице.

15 Уточнение статистики во время выполнения запроса Dynamic sampling для коллекции create or replace function gen_numbers(n in number default null) return array PIPELINED as begin for i in 1.. nvl(n, ) loop pipe row(i); end loop; return; end; SQL> explain plan for select /*+ dynamic_sampling(a 2) */ * from table(gen_numbers(5)) a; | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 5 | 10 | 35 (0)| 00:00:01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| GEN_NUMBERS | 5 | 10 | 35 (0)| 00:00:01 | Note dynamic sampling used for this statement (level=2)

16 Уточнение устаревшей статистики Уточнение статистики во время выполнения запроса begin dbms_stats.gather_table_stats(ownname => null, tabname => 't1', method_opt => 'FOR ALL INDEXED COLUMNS SIZE 254); end; / delete from t1 where mod(object_id,2)=1; commit; explain plan for select /*+ dynamic_sampling(a 2) */ * from t1 a where object_id>1000; | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| | 0 | SELECT STATEMENT | | 244 | | 6 (0)| | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 244 | | 6 (0)| |* 2 | INDEX RANGE SCAN | I1 | 306 | | 1 (0)| Note dynamic sampling used for this statement (level=2) ** Executed dynamic sampling query: min. sel. est. : ** Using single table dynamic sel. est. :

17 Закрытый цикл обработки запросов Уточнение статистики после выполнения запроса 1. Обновление соответствующего курсора информацией, полученной по завершении выполнения запроса. 2. Использование информации, собранной на этапе выполнения, в процессе оптимизации запроса.

18 Уточнение статистики после выполнения запроса Cardinality feedback

19 Уточнение статистики после выполнения запроса Кандидаты для оценки Таблицы селективности которых могут быть низкого качества: 1.Отсутствующая статистика 2.Объединения нескольких И/ИЛИ предикатов фильтрации 3.Предикаты, содержащие сложные операторы, которые не могут быть правильно оценены оптимизатором ПредикатDynamic SamplingCardinality feedback object_id>1000ДАНЕТ object_id>1000 and created > ' ' and last_ddl_time 1000 ДА, для простых предикатовДА

20 Уточнение статистики после выполнения запроса Формальный критерий выбора предиката SQL_ID 56fpp1b1643cu, child number select count(*) from t1 where (object_id>1000) and created > ' ' and last_ddl_time < ' ' | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | 235 (100)| | | 1 | SORT AGGREGATE | | 1 | 21 | | | |* 2 | TABLE ACCESS FULL| T1 | | 1099K| 235 (1)| 00:00:04 | SQL_ID 0nu4q2r35nj4g, child number select count(*) from t1 where (object_id>1000+object_id-object_id) and and created > ' ' and last_ddl_time < ' ' | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | 235 (100)| | | 1 | SORT AGGREGATE | | 1 | 21 | | | |* 2 | TABLE ACCESS FULL| T1 | | 1135K| 235 (1)| 00:00:04 | Note - cardinality feedback used for this statement

21 Уточнение статистики после выполнения запроса Реализация cardinality feedback. Cursor SQL> select optimizer_cost, elapsed_time, child_number, is_shareable 2 from v$sql 3 where sql_id = '0nu4q2r35nj4g'; OPTIMIZER_COST ELAPSED_TIME CHILD_NUMBER I N Y SQL> select child_number, use_feedback_stats, reason 2 from V$SQL_SHARED_CURSOR 3 where sql_id = '0nu4q2r35nj4g'; CHILD_NUMBER U REASON Y Optimizer mismatch(13) 1 N IS_SHAREABLE:N Курсор более не используется и в числе первых будет вытеснен из разделяемого пула USE_FEEDBACK_STATS: Y Будет выполнен принудительный полный разбор, так что оптимизатор сможет повторно оптимизировать запрос, используя уточненные оценки селективности

22 Уточнение статистики после выполнения запроса Реализация cardinality feedback SELECT /*+ OPT_ESTIMATE (TABLE "T1" ROWS= ) */ COUNT(*) … Single Table Cardinality Estimation for T1[T1] Card: Original: >> Single Tab Card adjusted from: to: Rounded: Computed: Non Adjusted: Access Path: TableScan Cost: Resp: Degree: 0 Cost_io: Cost_cpu: Resp_io: Resp_cpu: Access Path: index (RangeScan) Index: I_DATE resc_io: resc_cpu: ix_sel: ix_sel_with_filters: Cost: Resp: Degree: 1 Access Path: index (RangeScan) Index: I_LAST_DDL_TIME resc_io: resc_cpu: ix_sel: ix_sel_with_filters: Cost: Resp: Degree: 1 Table: T1 Alias: T1 Card: Original: Rounded: 2707 Computed: Non Adjusted: Access Path: TableScan Cost: Resp: Degree: 0 Cost_io: Cost_cpu: Resp_io: Resp_cpu: Access Path: index (RangeScan) Index: I_DATE resc_io: resc_cpu: ix_sel: ix_sel_with_filters: Cost: Resp: Degree: 1 Access Path: index (RangeScan) Index: I_LAST_DDL_TIME resc_io: resc_cpu: ix_sel: ix_sel_with_filters: Cost: Resp: Degree: 1

23 Уточнение статистики после выполнения запроса Ограничения cardinality feedback 1.Исправляется только селективность отдельных таблиц (уточнение селективности соединений – в будущих версиях?) 2.Аксиома: данные в таблицах не изменяются SQL оценки совпадают nomonitoring delete оценки не совпадают по-прежнему nomonitoring SQL оценки не совпадают cardinality feedback, коэффициент delete оценки не совпадают коэффициент не меняется

24 Одного плана выполнения недостаточно SELECT count(distinct e.employee_id), min(j.start_date), max(j.end_date) FROM employees e, job_history jh WHERE e.job_id = :job_id AND e.employee_id = jh.employee_id Уточнение статистики после выполнения запроса HJ FS EJH NL IS EJH В 10g: один план выполнения для всех значений bind переменных, оптимальный для первого набора переменных

25 Bind-aware cursor sharing Уточнение статистики после выполнения запроса Оптимизатор Bind Cursor Re-optimize Share Проблема: накладные расходы, время и память. Повышенное потребление памяти может привести к вытеснению курсоров из кеша и повторному полному разбору часто выполняемых запросов. Для некоторых систем это может стать серьезной проблемой производительности. План похож на оптимальный

26 create index i_object_type on T1(object_type); begin dbms_stats.gather_table_stats(ownname => null, tabname => 't1', method_opt => 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY); end; BACS. Пример variable object_type varchar2(255); exec :object_type:='TABLE'; select max(object_id) from t1 where object_type = :object_type; SQL_ID 18jqvb5zrw66d, child number | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| | 0 | SELECT STATEMENT | | | | 5 (100)| | 1 | SORT AGGREGATE | | 1 | 15 | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 123 | 1845 | 5 (0)| |* 3 | INDEX RANGE SCAN | I_OBJECT_TYPE | 123 | | 1 (0)| exec :object_type:='SYNONYM'; SQL_ID 18jqvb5zrw66d, child number | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| | 0 | SELECT STATEMENT | | | | 290 (100)| | 1 | SORT AGGREGATE | | 1 | 15 | | |* 2 | TABLE ACCESS FULL| T1 | | 405K| 290 (1)| Уточнение статистики после выполнения запроса

27 BACS. Алгоритм работы Уточнение статистики после выполнения запроса 1.Основывается на профиле bind переменных, в котором содержится информация о селективности и значениях bind переменных. 2.Для решения о совместном использовании курсора создается профиль текущих значений bind-переменных. 3.Профиль сравнивается с существующими, и если оценки селективности попадают в допустимый диапазон, курсор используется совместно. 4.В ином случае Oracle перекомпилирует запрос с текущим значением bind переменных 5.Если это ведет к тому же самому плану выполнения, то профили сливаются, а один из курсоров освобождается. 6.Изначально диапазон bind-переменных мал, и увеличивается по мере слияния/появления новых значений.

28 BACS. Курсор Уточнение статистики после выполнения запроса SQL> select optimizer_cost, elapsed_time, child_number, is_shareable, is_bind_sensitive, is_bind_aware 2 from v$sql 3 where sql_id = '18jqvb5zrw66d'; OPTIMIZER_COST ELAPSED_TIME CHILD_NUMBER I I I N Y N Y Y Y Y Y Y SQL> select child_number, bind_equiv_failure, load_optimizer_stats 2 from V$SQL_SHARED_CURSOR 3 where sql_id = '18jqvb5zrw66d'; CHILD_NUMBER B L N Y 1 Y N 2 Y N IS_BIND_SENSITIVE: Курсор чувствителен к значению bind переменных IS_BIND_AWARE: Курсор использует BACS BIND_EQUIV_FAILURE: Селективность bind-переменной не соответствует используемой оптимизатором для текущего курсора. LOAD_OPTIMIZER_STATS: Необходим полный разбор для BACS

29 BACS. Профиль Уточнение статистики после выполнения запроса select * from v$sql_cs_statistics where sql_id = '18jqvb5zrw66d'; CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME Y Y Y select * from v$sql_cs_histogram where sql_id = '18jqvb5zrw66d'; CHILD_NUMBER BUCKET_ID COUNT select * from v$sql_cs_selectivity where sql_id = '18jqvb5zrw66d'; CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH =OBJECT_TYP =OBJECT_TYP V$SQL_CS_SELECTIVITY содержит диапазоны селективности по каждому предикату, попадание в которые позволяет повторно использовать курсор V$SQL_CS_HISTOGRAM – информация adaptive cursor sharing

30 BACS. Ограничения 1.Может быть использован только для предикатов вида Пример:name = : bind salary > :bnd 2. Не используется для сложных предикатов substr(name,1,5) = :bind 3. Для предикатов эквивалентности нужны гистограммы, иначе разницы в планах выполнения не будет 4. IS_BIND_SENSITIVE Уточнение статистики после выполнения запроса

31 Adaptive Cursor Sharing Цель: дальнейшее снижение накладных расходов Уточнение статистики после выполнения запроса Механизм: BACS включается только для курсоров, у которых на одном из выполнений селективность отличалась от измеренной селективности при первом выполнении запроса.

32 ACS. Пример Уточнение статистики после выполнения запроса variable object_type varchar2(255); exec :object_type:='TABLE'; select max(object_id) from t1 where object_type = :object_type; SQL_ID 18jqvb5zrw66d, child number | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| | 0 | SELECT STATEMENT | | | | 5 (100)| | 1 | SORT AGGREGATE | | 1 | 15 | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 123 | 1845 | 5 (0)| |* 3 | INDEX RANGE SCAN | I_OBJECT_TYPE | 123 | | 1 (0)| select optimizer_cost, elapsed_time, child_number, is_shareable, is_bind_sensitive, is_bind_aware from v$sql where sql_id = '18jqvb5zrw66d'; OPTIMIZER_COST ELAPSED_TIME CHILD_NUMBER I I I Y Y N select child_number, bind_equiv_failure, load_optimizer_stats from V$SQL_SHARED_CURSOR where sql_id = '18jqvb5zrw66d'; CHILD_NUMBER B L N Y

33 Особенности ACS Уточнение статистики после выполнения запроса 1.Позволяет выбирать разные планы выполнения, но это не означает, что это может быть достаточно во всех случаях 2.Информация ACS может быть вытеснена из разделяемого пула 3.Механизм ACS срабатывает только во время вызова PARSE, он не будет работать при одиночном разборе и множественных выполнениях: - SQL в PL/SQL, execute immediate, open/fetch/close - session_cached_cursor Алгоритм (знать свои данные): Если значение A требует особого плана выполнения, то - добавить в запрос уникальный хинт или предикат A=A; Если значение B требует особого плана выполнения, то - добавить в запрос уникальный хинт или предикат B=B; Оставить запрос без изменений в ином случае

34 Уточнение статистики после выполнения запроса Параметры CURSOR_SHARING = FORCE ???? alter system set "_optimizer_use_feedback"=false; alter system set "_optimizer_extended_cursor_sharing_rel"=none; alter system set "_optimizer_extended_cursor_sharing"=none; alter system set "_optimizer_adaptive_cursor_sharing"=false;

35 Литература 1.Markl, V. LEO: самонастраивающийся оптимизатор запросов для DB2 (перевод Сергей Кузнецов) 2.Chaudhuri, S. Self-Tuning Database Systems: A Decade of Progress / Surajit Chaudhuri, Vivek Narasayya // Proceedings of the Second International Conference on Automatic Computing, p , June 13-16, Lee, A. Closing the query processing loop in Oracle 11g / Allison W.Lee, Mohamed Zait // In Proceedings of the VLDB Endowment, 2008 Блоги: 1.Inside the Oracle Optimizer 2.Jonathan Lewis 3.Greg Rahn 4.Christian Antognini 5.Kerry Osborne 6.OakTable network Оптимизация SQL: методы уточнения стоимости в Oracle 11g

36 Вопросы Оптимизация SQL: методы уточнения стоимости в Oracle 11g