Направление Oracle  
  Перевод "тюнинг Oracle 9i" - разделы про оптимизатор  

введение

Настоящий перевод проводился автором в процессе изучения СУБД Oracle, когда чтение англоязычной документации по такой многодетальной теме выявило ухудшение понимания - надо еще отвлекаться на перевод. Поэтому в итоге было принято решение провести перевод - конспект отдельных глав руководства по тюнингу Oracle 9i, представлявших интерес. Главы переводились в разное время, и планируется перевод еще нескольких глав. Однако нужно обратить внимание читателя на тот момент, чтоданный материалне есть художественный перевод, но только лишь заметки наиболее важных для автора моментов

Выложен он как обычно - без какой либо ответственности и с надежной, что кому нибудь оно поможет. Цифры после имени разделов в скобках соответствуют разделам родной документации Oracle по тюнингу 9i, где и можно получить более детальную информацию. Использовать этот перевод можно, но вся ответственность не на авторе, но на использующем. Ну и не забываем при использовании указывать ссылку на настоящий материал и моё имя - как автора перевода. Где еще можно получить информацию - в родной документации от Oracle, правда на английском, купив официальный русский перевод (делает компания RdTex, стоит, насколько помню, порядка 6-10 тыс. руб.), или купив книги каких либо авторов, что явно не есть первоисточник. За сим - поехали

об оптимизации и оптимизаторе Oracle 9i (1)

Примечание: Oracle категорически рекомендует использовать CBO, и многие опции работают только с CBO

самый общий обзор

основной задачей оптимизации является выбор оптимальных методов доступа к данным, среди которых могут быть следующие, но не только: доступ просмотром таблиц, индексов, вложенные циклы (nested loop) и соединения по хэшу. Поступающий запрос парсится и, в зависимости от типа оптимизации, обрабатывается до получения плана выполнения, после чего происходит выполнение и возврат данных пользователю

Для поступающего запроса оптимизатор проводит последовательно следующие операции:

  • проверку выражения и корректности условий
  • трансформацию текста запроса
  • определение подходов (методов) оптимизации
  • выбор методов доступа
  • выбор порядка соединения
  • выбор методов соединения

Целями оптимизации могут быть максимально быстрый полный ответ (по умолчанию) или же максимально быстрый возврат первых строк. Методы работы оптимизатора зависят от поставленной цели, которая может выставляться опцией OPTIMIZER_MODE (выставляется для экземпляра и/или сессии), подсказками SQL (FIRST_ROWS(n),FIRST_ROWS,ALL_ROWS,CHOOSE,RULE) и зависит от собираемой статистики (пакет DBMS_STATS и команда ANALIZE)

В режиме быстрого отклика (fast response) может быть использован старый метод (FIRST_ROWS), сохраненный для совместимости или новый FIRST_ROWS(n), учитывающий n при оптимизации

CBO призван выработать план выполнения самый малозатратный (с позиции путей выборки), с учетом собранной статистики. Он производит следующие операции:

  • генерирует потенциальные планы выполнения согласно «путей доступа» и подсказок SQL
  • вычисляет стоимость выполнения согласно статистике о распределени данных и опциях хранения в затрагиваемых таблицах, индексах и разделах (пропорционально ожидаемой утилизации аппаратных ресурсов, хотя есть нюансы при параллельных запросах). Конкретно вычисляется стоимость путей доступа (access path) и стоимость порядков соединения (join order)
  • сравниваются стоимости и выбирается самая нижняя

CBO включает в качестве основных компонент: механизм перезаписи запросов (transformation), механизм оценки (estimator) и генератор планов выполнения

Механизм перезаписи запросов (query transformer) производит разбор запроса, состоящего из смысловых блоков, определяет отношения между блоками и производит переписывание запроса для его начальной оптимизации. Методы перезаписи применяются последовательно

  • слияние представлений (выталкивание представления в содержащий его запрос, т.е. объединение фильтров)
  • вталкивание префиксов (для «не окученных» слиянием - условия выборки вталкиваются в подзапрос для уменьшения возвращаемого подмножества)
  • раз-вложение подзапросов (замена вложенного подзапроса соединением) и перезапись с материализованными представлениями

Механизм оценки (estimator) производит вычисление показателей - селективности, кардинальности и стоимости - зависящих друг от друга. При доступности используется статистика. В конечном итоге вычисляется «тотальная» стоимость каждого потенциального плана выполнения

Селективность отражает количество возвращаемых строк по отношению к общему количеству строк в источнике (таблице, представлении, результате соединения или группировки). Может принимать значения от 0.0 до 1.0. При отсутствии статистики используются зависящие от предикатов (видов фильтров) внутренние значения. При наличии статистики используется она, а при наличии гистограмм - данные гистограмм

Кардинальность отражает количество строк в источнике. Базовая кардинальность отражает количество строк в таблице (из ANALYZE), а при отсутствии статистики учитывает количество занимаемых таблицей экстентов. Эффективная кардинальность отражает количество строк в выборке, и зависит от предикатов всех колонок, и вычисляется как продукт от базовой кардинальности и селективности каждого задействованного предиката (фильтра). Кардинальность соединения отражает количество строк в соединении (join) , вычисляется как продукт кардинальности двух источников, умноженная на селективность предикатов соединения. Уникальная кардинальность отражает количество отличных (distinct) значений в колонках и различается для колонок таблицы. Групповая кардинальность отражает количество строк после группировки и зависит от базовой кардинальности и отличительной кардинальности по группируемым столбцам

Стоимость отражает использование ресурсов, в CBO учитывается ввод/вывод, процессорное время и утилизация памяти. Понятие путь доступа (access path) отражает утилизацию ресурсов и может быть просмотром таблицы, полным сканированием индекса или сканирование индекса. Стоимость соединения отражает совокупность отдельных стоимостей доступа для нескольких отдельных источников данных и может представать в виде стоимость соединения вложенного цикла (nested loop join) при котором внутренний источник просматривается отдельно для каждой строки внешнего источника, стоимость соединения сортированного слияния (sort merge join) и стоимость объединения по хэшу (hash merge join) при котором вычисляется хэш для значений слияния внешнего источника (при необходимости разбивается на куски, влезающие в память) и при проходе по внутреннему источнику происходит сравнение з начений колонок соединения с вычисленными хэшами внешнего источника

методы доступа

Методы доступа (access path) поддерживаются следующие:

  • Full table scans (предпочтителен на больших объёмах выборки из источника, на очень маленьких таблицах, на высокой параллельности. Можно использовать подсказку FULL(имя_таблицы))
  • RowId table scans (самый быстрый способ получить доступ к конкретной строке, обычно используется в спарке с выборкой ID сканированием индекса)
  • Index scans
    • index unique scan
    • index range scan (включает фильтр диапазона, ограничивающий любую одну или обе границы, можно явно использовать подсказку INDEX(алиас_таблицы имя_индекса), т.к. при использовании bind variable оптимизатор не знает значения и включает FULL SCAN. Значения сортируются, что позволяет при совпадении исключать отдельную обработку ORDER BY)
    • index range scan descending (отличается обратной сортировкой, используется совместно с ORDER BY DESC, можно использовать подсказку INDEX_DESC(алиас_таблицы имя_индекса))
    • index skip scans (т.к. выборка из индекса может оказаться гораздо быстрее выборки из таблицы, оптимизатор рассматривает использование последующих колонок составного индекса, начальные опускаются)
    • fast full index scans (является альтернативой сканированию таблицы, если хоть одна из колонок индекса имеет ограничение NOT NULL, быстрее полного сканирования индекса за счет параллелизма и multiblock, доступен только в CBO включением опции OPTIMIZER_FEATURES_ENABLE или подсказкой INDEX_FFS(имя_таблицы имя_индекса). Для использования должна быть собрана статистика по индексам, нельзя использовать с bitmap индексами, не предоставляет возможности сортировки)
    • index join (объединение индексов для исключения сканирования таблиц, не предоставляет возможности сортировки. доступен только в CBO включением опции OPTIMIZER_FEATURES_ENABLE или подсказкой INDEX_JOIN(имя_таблицы имя_индекса))
    • bitmap join (использует битовую карту для сохранения значение rowid. позволяет эффективно сливать индексы для множественных условий в классе WHERE, использует булевы операции для обработки OR и/или AND )
  • Cluster scans (используется для кластерных таблиц, где данные хранятся в индексированном кластере. Все строки с одним значением ключа хранятся хранятся в одном блоке данных, причем сначала Oracle вытаскивает rowid одной из строк, и использует его для лоцирования всех строк с тем же значением кластерного ключа)
  • Hash scans (используется для hash - кластерных таблиц, где данные хранятся в hash - кластере. Все строки с одним значением ключа хранятся хранятся в одном блоке данных, причем сначала Oracle вытаскивает rowid одной из строк, и использует его для лоцирования всех строк с тем же значением к ластерного ключа)
  • Sample table scans (используется для конструкций FROM SAMPLE и SAMPLE BLOCK, не поддерживает join и remote таблицы, но можно обойти промежуточным CREATE AS SELECT, требует CBO)

обработка соединений

Обработка оптимизатором соединений (join) включает выборку путей доступа (access path), методов соединения (join methods) и порядка соединения (join order). Порядок соединения может быть переопределен подсказкой. При обработке оптимизатор:

  • распознает, есть ли в соединении таблицы с уникальными значениями (по наличию constraints), и, при наличии - ставит их вперед в order join и продолжает оптимизацию следующих таблиц
  • при наличии множественного объединения таблица с условием outer обязана находиться в конце order join, это правило оптимизатор никогда не нарушает

методы соединений

Методами соединения являются перечисленные ниже. Для anti-joins (т.е. NOT IN) по умолчанию используется nested loop join, но может быть переопределено подсказками NL_AJ,MERGE_AJ,HASH_AJ. Аналогично nested loop join используется для semi - joins (т.е. IN или EXISTS), но может быть переопределено подсказками NL_SJ,MERGE_SJ,HASH_SJ. Также CBO распознает звездообразные запросы (RBO не распознает)

  • соединение вложенным циклом (nested loop join, используется при малом количестве строк в соединяемых таблицах, подсказка USE_NL(таблица таблица2), расчитывает стоимость чтения каждой внешней таблицы и соответствующих ей записей внутренней в память)
  • соединение сортированным слиянием (sorted megre join, обычно хуже чем hash join, но может использоваться, если есть уже отсортированные данные и и спользуются условия соединения, отличные от равно или не равно. Также всегда используется в RBO. Подсказка USE_MERGE(таблица1...2...3...) Расчитывает стоимость как чтение всех данных таблиц в память и сортировки памяти)
  • соединение хэшом (hash join, используется для больших объемов соединеняемых и возвращаемых данных по равенству значений, подсказка USE_HASH(таблица1...2...3...), должны быть включены параметры PGA_AGGREGATE_TARGET (или устаревшее HASH_AREA_SIZE) и HASH_JOIN_ENABLED. Хэш строится в памяти по меньшей таблице, которая при необходимости делится на размещаемые по размеру в памяти блоки (расчет хэша идет до конца, принеобходимости партиции хранятся на диске во временных экстентах), вторая таблица сканируется и при сравнении совпадающие строки отдаются. Но если хэшируемая таблица не умещается в памяти, то производительность этого метода низкая)
  • картезианское соединение (cartezian join используется, когда нет условий для соединения, обычно является результатом плохо написанных запросов SQL, подсказка ORDERED )
  • внешнее соединение (outer join) является расширением общего механизма оптимизации соединений со своими нюансами:
    • nested loop outer join возвращает све строки внешней (outer) таблицы, даже если нет соответствия во внутренней (inner), порядок таблиц четко задается определением соединения - внешняя является ведущей (для каждой строки внешней (ведущей, driving) сканируются все строки внутренней), тогда как для обычного соединения ведущая и ведомая выбираются по стоимости. Условия использования - небольшой объём выборки и возможность сделать внешнюю таблицу ведущей
    • hash outer join используется при большом объёме выборки. Порядок соединения, в отличие от простого hash join, не расчитывается по стоимости, а определяется описанием соединения - внешняя таблица используется для построения хэша
    • sort merge outer join используется при невозможности назначить внешнюю таблицу ведущей, является менее эффективным, чем первые два, но при большом объеме данных или запросе отдельной подходящей сортировки, когда nested loop outer join неэффективен, имеет приоритет перед hash outer join
    • full outer join - комбинация left и right outer join

За работу оптимизатора отвечает ряд параметров

  • OPTIMIZER_FEATURES_ENABLE - включает функциональность оптимизатора, соотвтетсвующую определенной версии
  • OPTIMIZER_MODE - режим работы оптимизатора
  • CURSOR_SHARING - позволяет эмулировать связанные переменные в запросах
  • DB_FILE_MULTIBLOCK_READ_COUNT - влияет на стоимость операций full scan (table и index)
  • HASH_AREA_SIZE - объём выделенной под построение хэшей памяти. Не рекомендуется использовать, замена - параметр PGA_AGGREGATE_TARGET
  • SORT_AREA_SIZE - объём выделенной под сотрировки памяти. Не рекомендуется использовать, замена - параметр PGA_AGGREGATE_TARGET
  • HASH_JOIN_ENABLED - включает и выключает HASH JOIN метод соединений
  • OPTIMIZER_INDEX_CACHING - отражает подразумеваемый оптимизатором процент индексных блоков в буферном кэше, что влияет на стоимость использования индексов и вложенных запросов
  • OPTIMIZER_INDEX_COST_ADJ - включает подстройку веса стоимости использования индексов. Default 100 (например установка в 50 уменьшает стоимость использования индекса в 2 раза от дефолтной модели)
  • OPTIMIZER_MAX_PERMUTATIONS - ограничивает максимальное число перебора вариантов пр построении планов выполнения, установка в 1000 и ниже приводит в целом к парсингу длиной не более нескольких секунд
  • PARTITION_VIEW_ENABLED - при включении CBO сканирует только требуемую партицию
  • QUERY_REWRITE_ENABLED - фактически включает перезапись запроса с учетом материализованных представлений
  • STAR_TRANSFORMATION_ENABLED - включает возможность использовать bitmap индексы вместо картезианского объединения

операции оптимизатора Oracle 9i (2)

преобразование операций

Конструкция IN преобразуется оптимизатором в объединение нескольких выборок по условию равно. Выигрыш при переписывании достигается привлечением использования индесков с выборкой по уникальному значению. Специальной подсказки операции нет, но можно указать предпочтительный индекс. Внимание - это отдельный вид операции плана выполнения INLIST, характеризуемый сканированием по одному и тому же индексу, в отличие переписываниея запроса до нескольких и объединения разных результатов

Соединения нескольких условий по OR уже выгодно отличается возможностью использования сканирования по индексам. Фактическая перезапись запроса сводится к выделению подзапросов по одному условию и последующего объединения (union) результатов. Подсказки включающая - USE_CONCAT и выключающая - NO_EXPAND

В случае использования удаленных таблиц оптимизатор пытается выставить их ведущими, но в документации есть примеры, когда ведущей нужно принудительно (подсказками) выставлять внутреннюю таблицу

В случае распределенных запросов оптимизатор использует тот же алгоритм, что и для локальных. Если все таблицы на одном удаленном узле - запрос отправляется на этот узел и только получается результат. Если таблицы на разных узлах - запрос разбивается на соответствующие фрагменты и обрабатывается на соответствующих узлах, а результаты сокпонуются локальным сервером. Также CBO учитывает наличие индексов в удаленных базах, RBO не учитывает

Операции сортировки

  • sort unique Уникальная сортировка инициируется пр использовании слова DISTINCT или если уникальное значение нужно для следующей операции
  • sort aggregate Сортировка агрегирования инициируется при запросе операций агрегирования
  • sort group Сортировка группирования используется при запросе формирования групп
  • sort join Сортировка соединения используется операцией sort merge join, когда нужен уникальный ключ
  • sort order by Сортировка упорядочивания инициируется, когда нет подходящего индекса

оптимизатор может создать представления, если не может разобрать существующиее представление (view) или при использовании временных или встроенных (inline) представлений

Объединения (UNION и UNION ALL) используются оптимизатором при выявлении слов UNION или UNION ALL, а также при необходимости при перезаписи запросов. Подсказок нет

Оптимизатор переписывает LIKE на "=" в случае, если нет метасимволов в условии и если поле не является полем переменной длины (например VARCHAR(20)). Подсказок нет

Запросы с условиями ANY и SOME переписываются в общем случае на объединение нескольких запросов. В случае ипользования подзапросов ANY и SOME заменяются на конструкцию EXIST

Запросы с условием ALL замещаются запросом с несколькими AND условиями. В случае подзапросов ALL меняется на ANY и, далее, на EXIST

Условия BETWEEN всегда замещается парой <= и =>

Условие NOT всегда переписывается методом изымания его и смены оператора отношения противоположным оператором отношения

Транзитивность. Запрос с несколькими условиями для одной колонки, когда одно из условий есть оператор отношения с другой колонкой, а остальные - операторы отношения с константами, распознаются оптимизатором. При наличии индексов по колонкам обработка условий объединяется, что экономит ресурсы

Оптимизатор автоматизирует максимальное упрощение условия фильтра от начальных, что дает эффект на сложных комбинациях условий с OR и AND

DETERMINISTIC - оставлено на будущее

переписывание запроса (transformation)

  • при наличии в классе WHERE множества условий OR запрос переписывается в объединение нескольких запросов (по количеству OR). Если есть индексы для каждого условия - преобразование проводится, если для кого то хоть одного требуется FULL SCAN - преобразования не проводится. Также CBO учитывает статистику. При IN-list или OR по одной колонке преобразование не проводится, т.к. спользуется более эффективный INLIST
  • комплексные запросы переписываются оптимизатором к виду эквивалентного объединения (join), что позволяет использовать технику оптимизации объединений, и только при невозможности - обрабатываются отдельно вложенный и родительский запрос
  • при обращении к простым представлениям оптимизатор может подставить вместо представления имя таблицы и добавить условия из представления в фильтр запроса (view megre). В отдельных случаях при включении дополнительного параметра OPTIMIZER_FEATURES_ENABLE или подстказой MEGRE включается комплексное слияние представлений (complex view merge), что расширяет подмножество охватываемых технологией представлений. Слова UNION, MINUS, INTERSECT, CONNECT BY, ROWNUM, AVG/MIN/MAX/SUM полностью исключают технологию, а GROUP BY и DISTINCT требуют complex view merge
  • для неокученных техникой view merge запросов используется технология вталкивания предикатов (pushes predicates), когда условия фильтра "вталкиваются" во внутренний блок (подзапрос), что уменьшает использование ресурсов

Примечания:

  • В случае применения функций агрегации к представлению с функциями агрегации производится перезапись вида AVG(AVG(...))
  • В outer join представление из одной таблицы в правой части подвергается view megre, многотабличное - переписывается с вталкиванием предикатов
  • В случаях, когда использовать техники слияния и вталкивания предиката для запроса в представлением невозможно (например ROWID) - используется доступ через представление, которое вычисляется как часть плана исполнения
  • Компонованные запросы (UNION,UNION ALL,INTERSECT,MINUS) обрабатываются как результат оптимизированных подзапросов

сбор статистики оптимизатора Oracle9i (3)

Статистику по распределению данных и характеристикам хранения таблиц, индексов и партиций необходимо собирать периодически. Для сбора статистики используется пакет DBMS_STATS (процедуры можно посмотреть по desc DBMS_STATS) и команда SQL ANALIZE (последняя устарела для большинства операций, но не для всех). При вычислении статистик может использоваться полное вычисление, вычистление по случайному подмножеству и объявленные пользователем методы оценки. Собираемая статистика включает в себя:

  • Таблицы - количество строк, блоков и средняя длина строки
  • Столбцы - количество уникальных значений, количество пустых значений и распределение данных в виде гистограммы
  • Индексы - количество листовых блоков, количество уровней и индекс кластеризации
  • Системная статистика - производительность и утилизация ввода/вывода, производительность и утилизация процессора

Основным средством сбора статистики является пакет DBMS_STATS, процедуры которого могут запускаться последовательно и параллельно, но обработка индексов работает только последовательно. Для партицированных объектов может и должна собираться как глобальная статистика, так и статистика партиций

Системная статистика важна для работы оптимизатора, ее нужно собирать за определенный промежуток времени и можно переключать (выгружать и загружать) собранные значения. Старые запросы не переразбираются после обновления системной статистики, в отличие от статистики таблиц, индексов и партиций. Примеры сбора статистики и ее загрузки из таблицы:

 
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode => 'interval',
           interval => 720,stattab => 'mystats',statid => 'OLTP');
END;
/

VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,'DBMS_STATS.IMPORT_SYSTEM_STATS(''mystats'',
         ''OLTP'');' SYSDATE, 'SYSDATE + 1');
COMMIT;
END;
/

Статистика индексов собирается принудительно функцией DBMS_STAT.GATHER_INDEX_STATS или же при создании B-tree или bitmap индекса с суффиксом COMPUTE STATISTICS. Во втором случае объём собираемой статистики зависит от того, партицирован или нет индекс

При сборе новой статистики оптимизатора рекомендуется сохранить старую (например, функцией DBMS_STAT.EXPORT_SCHEMA_STATS), для возможного последующего отката (например, функцией DBMS_STAT.EXPORT_SCHEMA_STATS). В случае, если новая статистика улучшает быстродействие для большинства запросов, можно провести откат к старой статистике и сохранение принудительных планов выполнения (outline) для проблемных запросов, после чего восстановить новую версию собранной статистики. Для сохранения статистики можно указать таблицу (создается процедурой DBMS_STATS.CREATE_STAT_TABLE), схему и идентификатор среза ( параметры stattab, statown и statid соответственно)

Охват объектов при сборе статистики регулируется опциями функций, и могут охватывать как полный перерасчет (GATHER), так и расчет для объектов без статистики (GATHER EMPTY) или с устаревшей статистикой (GATHER STALE)

Существует возможность автоматизации процесса сбора статистики, для которого необходимо перевести соответствующие таблицы или базу в целом в режим мониторинга, для чего используются функции DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING или DBMS_STATS.ALTER_DATABASE_TAB_MONITORING, или же можно использовать модификатор MONITORING в предлажениях CREATE TABLE и ALTER TABLE. Для выключения мониторинга для таблицы или базы используются те же процедуры или \ ключевое слово NOMONITORING. Режим мониторинга отслеживает объём сделанных модификаций (INSERT, UPDATE, DELETE) и, при изменении более 10% от объёма объекта, такой объект помечается как имеющий устаревшую статистику

После включения мониторинга можно собирать статистику с опцией GATHER STALE, при этом модификация более 10% объёма объекта отслеживается по представлению USER_TAB_MODIFICATIONS, которое может быть очищено процедурой DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO. Сбор статистики рекомендуется проводить регулярно на уровне схем ( процедура GATHER_SCHEMA_STATS) или базы в целом (процедура GATHER_DATABASE_STATS). Также можно формировать списки объектов с устаревшей статистикой для дальнейшей ручной обработки

Альтернативой пакету DBMS_STATS является каманда ANALYZE, которая категорически не рекомендуется к использованию Oracle для целей сборки статистики CBO, но должна использоваться для сборки статистики, отличной от статистики CBO - для использования классов VALIDATE или LIST CHAINED ROWS, а также для сбора информации о листах свободных блоков (freelist blocks)

Т.к. статистика используется оптимизатором CBO в процессе работы, то в случае отсутствия статистики используются некие значения по умолчанию, конкретные величины которых приведены в документации (для 9i это глава 3 Performance Tuning Guide and Reference)

Получить доступ к текущей статистике можно процедурами пакета DBMS_STATS (например DBMS_STATS.GET_TABLE_STATS) или же через представления словаря, к которым относятся - DBA_TABLES, DBA_TAB_COL_STATISTICS, DBA_INDEXES, DBA_CLUSTERS, DBA_TAB_PARTITIONS, DBA_TAB_SUBPARTITIONS, DBA_IND_PARTITIONS, DBA_IND_SUBPARTITIONS, DBA_PART_COL_STATISTICS, DBA_SUBPART_COL_STATISTICS. Собранная объектная статистика может быть получена следующими запросами:

SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, 
       TO_CHAR(LAST_ANALYZED, 'MM/DD/YYYY HH24:MI:SS') 
       FROM DBA_TABLES 
       WHERE TABLE_NAME IN ('SO_LINES_ALL','SO_HEADERS_ALL',
             'SO_LAST_ALL');

SELECT INDEX_NAME "NAME", NUM_ROWS, DISTINCT_KEYS "DISTINCT", 
       LEAF_BLOCKS, CLUSTERING_FACTOR "CF", BLEVEL "LEVEL", 
       AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY"
       FROM DBA_INDEXES
       WHERE OWNER = 'SH'
       ORDER BY INDEX_NAME;       

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY
       FROM DBA_TAB_COL_STATISTICS
       WHERE TABLE_NAME ="PA_EXPENDITURE_ITEMS_ALL"
       ORDER BY COLUMN_NAME;

Гистограммы используются для более качественного учета распределения данных. Существуют гистограммы, распределенные по весу (в каждый диапазон заносится примерно одинаковое количество значений) и по значению, иначе частоте (когда значений меньше, чем диапазонов, и каждый диапазон отражает одно значение). Гистограммы могут быть построены процедурой DBMS_STATS.GATHER_TABLE_STATS с дополнительными опциями. Рекомендуется указывать SIZE AUTO для автоматического выбора количества диапазонов. Пример:

EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('scott','emp', METHOD_OPT => 'FOR COLUMNS SIZE 10 sal');

Доступ к данным гистограмм можно получить из представлений словаря DBA_HISTOGRAMS (количество диапазонов гистограммы в полях ENDPOINT_NUMBER, ENDPOINT_VALUE),DBA_PART_HISTOGRAMS,DBA_SUBPART_HISTOGRAMS,DBA_TAB_COL_STATISTICS. А непосредственно значения гистограмм можно получить запросом:

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE 
       FROM DBA_HISTOGRAMS 
       WHERE TABLE_NAME ="SO_LINES_ALL" AND 
             COLUMN_NAME="S2" 
       ORDER BY ENDPOINT_NUMBER;

понятия индексов и кластеров БД Oracle9i (4)

Индексы создают дополнительную нагрузку на систему, и потому рекомендуется удалять неиспользуемые индексы, которые можно вычислить, активировав режим мониторинга ALTER INDEX MONITORING USAGE. При создании индекса необходимо учесть возможность неявного влияния на обработку запросов, обратить внимание на изменения в планах выполнения запросов и изменении времени обработки запросов системы

При построении индекса ключем является колонка или выражение, по которым строится индекс. Имеет смысл выбрать ключ, если он чато используется в классе WHERE, часто происходят объединения (join) по ключу, ключ имеет высокую селективность. Важно понимать, что наличие индекса увеличивает производительность выборок (select), но уменьшает производительность модификаций (insert,update,delete)

Не рекомендуется: строить обычный B-индекс по полю с малым количеством уникальных значений - предпочтительны bitmap (низкая селективность), строить индекс по очень часто модифицируемым полям (нагрузка на индекс, undo и redo), строить индекс по полю, задействованному только в функциях и операторах класса WHERE (min/max/avg ... предпочтителен FULL SCAN),

Композитные индексы увеличивают селективность и уменьшают ввод/вывод. Наибольший выигрыш при запросах, повторяющих лидирующую часть списка полей индекса (еще есть skip index scan). Строить композитные индексы стоит, если есть несколько полей, часто повтрояющихся в условиях фильтрации одного или разных запросов. Порядок полей должен определяться частотой использования полей в условиях, а при прочих равных вперед нужно ставить уже упорядоченные поля, например констраинтом UNIQUE

Управлять использованием индексов можно подсказками - NO INDEX выключает индекс, FULL активирует полное сканирование, а опции INDEX, INDEX_COMBINE, AND_EQUAL позволяют принудительно использовать индекс или список индексов

Пересоздание индекса возможно для целей сжатия, дефрагментации или изменения характеристик хранения. Наиболее быстрым методом является ALTER INDEX REBUILD, удаление и пересоздание обычно медленнее. Также для уменьшения дефрагментации можно использовать ALTER INDEX COALESCE. Еще одной снеочевидной возможностью является использование неуникального индекса для правил целостности типа UNIQUE и PRIMARY KEY. При использовании правил ссылочной целостности существует удобный трюк, заключающийся во включении правила в режиме ENABLE NOVALIDATE, что позволяет не блокировать таблицу на время включения, т.к. не производится проверка валидности уже существующих данных

Еще одной разновидностью индексов являются оснванные на функции индексы, которые могут быть использованы для обработки запросов с аналогичными функциями и выражениями в фильтре запроса. Для использования такой возможности должны быть включены опции QUERY_REWRITE_ENABLED и QUERY_REWRITE_INTEGRITY. Другой разновидностью является индекс-организованные таблицы, данные которых хранятся в индексе и довольно оперативно доступны по ключу индекса

Bitmap индексы предпочтительны для полей с малой или средней кардинальностью и большим количеством строк в каждом значении и большим количеством строк вообще, но плохо приспособлены под нагруженную OLTP среду. В отличие от B-tree индексов, которых зачастую нужно строить несколько, отдельных и композитных, с разным порядком полей bitmap индексы хорошо комбинируются и при этом выполняют работу как одиночных, так и составных. Однако там, где блокировка одного вхождения B-tree индекса блокирует один ROWID, блокировка одного вхождения (entry) bitmap индекса блокирует обычно несколько записей, что вредно для конкурентной нагруженной OLTP системы. В контексте модификации данных кэширование движка несколько сглаживает ситуацию, производя одну модификацию bitmap раздела даже на несколько модифицированных одним предложением строк, и в отсутствие ситуации высокой конкурентной нагруженности системы показывается несколько лучшая производительность относительно B-tree индесков

Можно использовать подсказки INDEX (bitmap || b-tree) и INDFEX_COMBINE, причем последняя анализирует подходящие индексы и выбирает с самой низкой стоимостью доступа. При создании bitmap индексов рекомендуется по возможности везде выставить в таблице NOT NULL для большей компактности, для этого же можно заменить поля с переменной длиной на с поля постоянной. Для эффективного мапирования битовой карты на ROWID можно использовать опции запроса ALTER TABLE - MINIMIZE RECORDS_PER_BLOCK (ограничение количества строк в блоке) и NOMINIMIZE RECORDS_PER_BLOCK(снятие ограничения количества строк в блоке). Также есть несколько параметров инициализации, влияющих на функционирование bitmap индексов - CREATE_BITMAP_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, SORT_AREA_SIZE

Если для таблицы есть bitmap индексы, оптимизатор будет принудительно использовать bitmap access plan, при этом использования bitmap индекса может и не быть, если дешевле использовать b-tree индексы. Однако для одиночного b-tree индекса использовать bitmap access plan будет только при использовании подсказки. В процессе использования bitmap access plan производится переконвертация rowid из задействованных b-tree индексов в битовые карты

Использование bitmap индексов имеет ограничения - не поддерживается RBO, не реализует правила ссылочной целостности, для прямых загрузок (direct load) не работает флаг SORTED_INDEX. Также в отдельных случаях имеет смысл создавать bitmap индексы для использования в соединениях (join) таблиц

Еще одной разновидностью индексов является доменный индекс, строимый по предоставляемым пользователем типам данных (CREATE INDEXTYPE) для увеличения производительности доступа к пользовательским типам данных

Использование кластеров позволяет хранить данные разных таблиц, увязанных по ключевому полю, вместе. При созаднии кластера рекомендуется учитывать: стоит кластеризовать чисто объединяемые таблицы, в т.ч. мастер - слэйв таблицы, не стоит кластеризовать редко объединяммые таблицы, таблицы с частой модификацией ключевого поля (модификация кластеризованных данных дольше за счет возможной миграции блоков), не кластеризовать таблицы, по которым часто проводится FULL SCAN (дольше некластеризованного варианта, читаются больше блоков т.к. таблицы хранятся вместе), если данные всех таблиц для одного ключа занимают больше одного или двух блоков (т.к. читаются все блоки для получения данных одного ключа), если количество строк в таблицах сильно различается (что приводит к коллизии высокой кардинальности ключа и низкой данных, ухудшая производительность)

Еще одним вариантом является хэш - кластер. Рекомендуется учитывать: имеет смысл, если часто запрашивается условие эквивалентности по столбцам - будущим ключам кластера в классе WHERE и если возможно заранее определить количество строк таблицы с учетом будущих изменений, не рекомендуется, если часто используется FULL SCAN или клгда часто модифицируется ключ кластера

подсказки оптимизатора Oracle9i (5)

Информация по подсказкам детально и ясно изложена в документации и носит больше справочных характер, почему здесь и опускается

оптимизация SQL запросов Oracle9i (6)

Целями оптимизации всегда являются или уменьшение времени отдачи результатов пользователю, или уменьшение потребления ресурсов за это же время. Добиться результатов можно уменьшением нагрузки (оптимизация плана выполнения), балансированием нагрузки (по времени) или распараллеливанием нагрузки. Для начала необходимо определить ресурсоёмкие запросы (может помочь включение trace + tkprof)

Другим методом является сбор статистики за интересующий период, как минимум V$FILESTAT, V$SYSSTAT, V$SQLAREA (or V$SQL), V$SQLTEXT and V$SQL_PLAN). Далее имеет смысл выбрать статистику утилизации ресурсов запросами к V$SQLAREA.BUFFER_GETS, V$SQLAREA.DISK_READS и V$SQLAREA.SORTS относительно суммарных величин. Далее, после определения проблемных запросов, необходимо собрать предварительные данные для тюнинга:

  • полный текст SQL запроса (из v$sqltext)
  • структура таблиц, задействованных в запросе
  • определения индексов (включая перечень и порядок полей, уникальность или нет и т.п.)
  • статистика оптимизатора (включая количество строк каждой таблицы, селективность колонок индекса, дату анализирования)
  • инфомацию о связанных представлениях (view)
  • текущий и, возможно, предыдущий планы выполнения (полученные через EXPLAIN PLAN, V$SQL_PLAN, или вывод TKPROF)

Кстати иметь планы исполнения всех типовых запросов очень полезно на предмет последующего сравнения и выявления причин уменьшения производительности

В случаях недоступности статистики (и не только) Oracle может использовать технологию предварительной оценки (dynamic sampling), для чего необходимо установить параметр OPTIMIZER_DYNAMIC_SAMPLING, при этом параметр OPTIMIZER_FEATURES_ENABLE должен показывать версию 9.0.2 и выше

Документация описывает следующие пути оптимизации SQL запросов

  • Выверить статистику оптимизатора
  • Пересмотреть план выполнения В OLTP окружении целью является мансимально селективный фильтр, что подразумевает отдачу как можно меньшего набора данных запросом, что также полезно при использовании объединений. Важет также оптимальный путь доступа. При контроле плана выполнения рекомендуется обратить внимание на:
    • Ведомая таблица (driven) имеет хороший фильтр
    • Порядок объединения подразумевает отдачу минимального количества значений следующему шагу
    • Метод объединения должен соответствовать отдаваемым значениям. Например nested loop join по индексам может быть неоптимален для возврата большого количества значений
    • Представления используются эффективно (Можно получить доступ к представлению через select)
    • Нежелательны картезианские множества даже на малых таблицах
    • Доступ к таблицам эффективен (например full scan не означает неэффективность - он может быть предпочтителен на малых таблицах или при использовании hash join)
  • Реструктурировать запрос SQL Часто проще написать новый запрос, чем выправлять старый. Общие рекомендации таковы:
    • по возможности используйте в классе WHERE комбинации AND и =
    • избегать функций и сравнения смешаных типов, при необходимости использования функций в фильтре не использовать их на колонках с предполагаемым доступом через индекс, лучше перенести функцию в другую часть выражения к безиндексной колонке
    • правильно писать разные запросы под разные задачи, но не универсальный запрос под несколько задач
    • селективный предикат у родителя - использовать EXIST, если селективный предикат в подзапросе - использовать IN (для OLTP, для DSS в общем случае предпочтетелен EXIST)
    • При наличии расширенной информации можно выбрать заведомо оптимальный метод доступа, используя подсказки (hints). То же касается и последовательности соединения. Если индекс эффективнее, то избегать fulle scan. Избегать использования индекса, возвращающего 10000 строк, если есть возвращающий 100 строк. Выбирать порядок соединения, при котором соединяется меньшее количество строк с таблицей, расположенной далее в порядке соединения
    • Представления требуют особого внимания. Не рекомендуются комплексные объединения представлений, не рекомендуется повторно использовать представления для новых целей, необходимо внимательно исследовать outer join с представлениями и unnesting subquery - возможно более правильный путь - использование таблиц напрямую
    • анализировать целесообразность использования временных таблиц и других методов сохранения промежуточной информации
  • Реструктурировать индексы Индекс не обязательно правильно. Пути оптимизации - удалить неселективные индексы, переопределить порядок колонок или добавить новые колонки для увеличения селективности, обеспечить высокую производительность индекса
  • Модифицировать или выключить триггера и правила ссылочной целостности (constraints) Триггера и правила ссылочной целостности могут создавать повышенную нагрузку на систему, может быть полезно переработать или отключить их
  • Реструктурировать данные Если прежние шаги отработаны - можно посмотреть в эту сторону. Рекомендации - проанализировать дизайн, привлечь партицирование, определить зависимые значения и избегать GROUP BY на критичных ко времени отклика данных
  • Выстроить план выполнения over time Этот метод подразумевает выборку оптимальной статистики оптимизатора и использвание сохраненных планов исполнения
  • Отразить данные максимально быстро Приложение должно пробовать получать доступ к каждой строке только один раз. Можно использовать CASE конструкцию для формирования нескольких вычисляемых столбцов, использовать DML RETURNING класс, модифицировать все требуемые данные в одной конструкции (constraints или оптимальные алгоритмы)

стабилизация планов выполнения Oracle9i (7)

Возможность стабилизации планов выполнения реализуется сохранением схем планов выполнения для отдельных или всех запросов. Такие схемы могут группироваться по категориям для упрощения администрирования. Стабилизация позволяет снять зависимость от модификации статистик оптимизатора, переходе с RBO на CBO, смене релиза движка

Существует полное соответствие между текстом SQL и сохраненной схемой плана выполнения (outlines). Данные храняться в таблицах OL$, OL$HINTS, OL$NODES, по которым существуют представления USER_OUTLINES и USER_OUTLINE_HINTS (редактирование таблиц непосредственно запрещено). для корректной работы механизма необходимы корректные значения переменных QUERY_REWRITE_ENABLED, STAR_TRANSFORMATION_ENABLED, OPTIMIZER_FEATURES_ENABLE. Манипулирование outlines производят пакеты DBMS_OUTLN (пользователю нужна роль EXECUTE_CATALOG_ROLE) и DBMS_OUTLN_EDIT (выполнение доступно всем). При установке опции CREATE_STORED_OUTLINES = true активируется автоматический сбор outlines, или же можно создать outline для отдельного запроса командой CREATE OUTLINE (пользователю требуется привилегия CREATE ANY OUTLINE, для CREATE OUTLINE FROM также привилегия SELECT ). Для редактирования частных outlines необходимо создать таблицу редактирования DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES (также результат даёт скрипт utleditol.sql)

При использовании outlines всегда используется CBO. Включение происходит командой USE_STORED_OUTLINES = (true|имя_категории). Если true - категория default, иначе - указанное имя (действует до сброса или отключения, если категория не найдена - используется default). Также существует похожая команда USE_PRIVATE_OUTLINES, действующая в масштабах текущей сессии. В документации представлены примеры решения типовых задач - переход с RBO на CBO и обновление релиза CBO

оптимизатор RBO (8)

RBO используется, если выбран режим оптимизатора (OPTIMIZER_MODE) RULE, или (OPTIMIZER_MODE=CHOOSE, нет статистики и не используются подсказки). Если выбран тип оптимизации FIRST_ROWS, FIRST_ROWS(), ALL_ROWS - даже при отсутствии статистики используется CBO (со статистикой по умолчанию). При работе RBO всегда выбирается самый дешевый из путей доступа (вес оценки операции) в последовательности:

  • 1. доступ к одиночной строке по ID
  • 2. доступ к одиночной строке по cluster join
  • 3. доступ к одиночной строке по hash cluster key с уникальным индексом или первичным ключем
  • 4. доступ к одиночной строке по уникальному индексу или первичному ключу
  • 5. доступ к кластерное соединение
  • 6. доступ к hash cluster key
  • 7. доступ к index cluster key
  • 8. доступ к композитный индекс
  • 9. доступ к индекс по одному столбцу
  • 10. доступ к поиск по диапазону индексированной колонки
  • 11. доступ к поиск по открытому с одного конца диапазону индексированной колонки
  • 12. доступ к sort merge join
  • 13. доступ к MAX и MIN по индексированной колонке
  • 14. доступ к order by по индексированной колонке
  • 15. доступ к full table scan

Для обработки объединений в RBO и CBO выполняются правила - проверяется наличие соединения с результатом не больше одной строки (используется PRIMARY key или UNIQUE), и, при наличии, такое соединение ставится вперед порядка соединения, кроме того в outer join таблица outer всегда должна идти последней

Оптимизатор RBO генерирует потенциальные порядки соединения (справа) как

  • Для заполнения порядка соединения выбирается таблица с максимальным весом оценки операции, пока не заполнит весь порядок
  • Для каждой таблицы выбирается метод соединения с прежней таблицей или набором данных относительно sort merge (вес 12). Если вес доступа меньше 11, то выбирается nested loop, если вес доступа меньше 12 и методом соединения с прежним источником является условие равенства, выбирается sort merge, иначе опять выбирается nested loop
  • Далее оптимизатор выбирает среди построенных планов, до реализации цели максимизировать количество nested loop соединений с использованием внутренней таблицей индексов (inner таблица обрабатывается много итераций и использование индексов резко увеличивает производительность)
  • Обычно оптимизатор не рассматривает порядок, в котором таблицы появляются во FROM классе в плане выполнения. Оптимизатор делает выбор, применяя приведенные в указанном порядке правила:
    • выбирается план, в котором как можно раньше идет операция nested loops с full scan для inner table
    • если есть связь, выбирается план с наиболее ранней sort merge операцией
    • если все еще есть связь, выбирается план, в котором первая в порядке соединения таблица имеет наибольший вес доступа. Если выбор из планов с доступом по одноколоночному индексу, выбирается план с доступом к первой таблице наиболее сливаемым индексом. Если же доступ к первой таблице идет сканированием по диапазону, выбирается план с доступом к первой таблице композитным индексом с максимальным количеством лидирующих колонок
    • если все еще есть связь, выбирается план, в котором первая таблица находится дальше в классе FROM

При обработке запросов RBO преобразует OR условия в объединения UNION ALL, если каждый подзапрос может быть обработан индексным доступом, однако если хоть один из полученных после трансформации подзапросов требует full scan, трансформация не производится

Также при использовании RBO рекомендуется обращать внимание на возможный альтернативный синтаксис, что может обеспечить оптимизацию времени выполнения запросов

использование EXPLAIN PLAN (9)

Команда EXPLAIN PLAN отображает план выполнения запроса. Первое требование - создание таблицы разбора скриптом @?/RDBMS/ADMIN/UTLXPLAN.SQL, которую рекомендуется удалять и пересоздавать при каждом обновлении движка. Дальше можно проводить разбор командой EXPLAIN PLAN [SET STATEMENT_ID = 'некий_id'] [INTO имя_таблицы] FOR текст_запроса

Скрипты UTLXPLS.sql и UTLXPLP.sql (используют пакет DBMS_XPLAN) выводят результаты разбора для последовательных и параллельных запросов. Также можно создать свой запрос, например:

SELECT cardinality "Rows",lpad(' ',level-1)||operation||
       ' '||options||' '||object_name "Plan"
       FROM PLAN_TABLE                
       CONNECT BY prior id = parent_id AND 
               prior statement_id = statement_id
               START WITH id = 0 AND statement_id = 'bad1'
       ORDER BY id;

Примеры разбора запросов, описание полей таблицы разбора и описание распознаваемых операций оптимизатора приведены в соответствующем заметкам разделе конфигурации

использование sql trace и tkprof (10)

Является основным методом, гарантирующим получение реального плана исполнения. AUTOTRACE и EXPLAIN PLAN не гарантируют получение плана исполнения, повторяющего реальный план исполнения. Механизм сбора статистики расписан у меня в Oracle FAQ в объёме большем, чем в документации. Также нужно обратить внимание на возможность обработки собранного trace файла утилитой tkprof, предоставляющий более удобный для проведения аналитики вид

Опции конфигурирования, описание работы и выводимые данныеутилиты TKPROF приведены в соответствующем заметкам разделе конфигурации

использование autotrace в sqlplus (11)

Команда SET AUTOTRACE (OFF|ON|ON EXPLAIN|ON STATISTICS|TRACEONLY) активирует опцию и выводит не только результаты запросов, но и план выполнения и статистику запроса, при этом у пользователя должна быть создана и присвоена роль PLUSTRACE. Также должна быть создана таблица трассировки скриптом @?/SQLPLUS/ADMIN/PLUSTRCE.SQL Дополнительные опции конфигурирования SQLPLUS приведены в соответствующем заметкам разделе конфигурации


 
     
   
   
    Нравится      

(C) Белонин С.С., 2000-2018. Дата последней модификации страницы:2018-01-09 13:25:19