 |  | |
| IT сфера | ||
 |  |
| | |
ОБ АВТОРЕ
- Новости - Введение - Резюме IT - Контакты |
||
| |
| | |
МАТЕРИАЛЫ
- Разработка - Статьи - Ссылки |
||
| |
| |||||||||
| |||||||||
введениеНастоящий перевод проводился автором в процессе изучения СУБД 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 призван выработать план выполнения самый малозатратный (с позиции путей выборки), с учетом собранной статистики. Он производит следующие операции:
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) поддерживаются следующие:
обработка соединенийОбработка оптимизатором соединений (join) включает выборку путей доступа (access path), методов соединения (join methods) и порядка соединения (join order). Порядок соединения может быть переопределен подсказкой. При обработке оптимизатор:
методы соединенийМетодами соединения являются перечисленные ниже. Для 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 не распознает)
За работу оптимизатора отвечает ряд параметров
операции оптимизатора Oracle 9i (2)преобразование операцийКонструкция IN преобразуется оптимизатором в объединение нескольких выборок по условию равно. Выигрыш при переписывании достигается привлечением использования индесков с выборкой по уникальному значению. Специальной подсказки операции нет, но можно указать предпочтительный индекс. Внимание - это отдельный вид операции плана выполнения INLIST, характеризуемый сканированием по одному и тому же индексу, в отличие переписываниея запроса до нескольких и объединения разных результатов Соединения нескольких условий по OR уже выгодно отличается возможностью использования сканирования по индексам. Фактическая перезапись запроса сводится к выделению подзапросов по одному условию и последующего объединения (union) результатов. Подсказки включающая - USE_CONCAT и выключающая - NO_EXPAND В случае использования удаленных таблиц оптимизатор пытается выставить их ведущими, но в документации есть примеры, когда ведущей нужно принудительно (подсказками) выставлять внутреннюю таблицу В случае распределенных запросов оптимизатор использует тот же алгоритм, что и для локальных. Если все таблицы на одном удаленном узле - запрос отправляется на этот узел и только получается результат. Если таблицы на разных узлах - запрос разбивается на соответствующие фрагменты и обрабатывается на соответствующих узлах, а результаты сокпонуются локальным сервером. Также CBO учитывает наличие индексов в удаленных базах, RBO не учитывает Операции сортировки
оптимизатор может создать представления, если не может разобрать существующиее представление (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)
Примечания:
сбор статистики оптимизатора 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 относительно суммарных величин. Далее, после определения проблемных запросов, необходимо собрать предварительные данные для тюнинга:
Кстати иметь планы исполнения всех типовых запросов очень полезно на предмет последующего сравнения и выявления причин уменьшения производительности В случаях недоступности статистики (и не только) Oracle может использовать технологию предварительной оценки (dynamic sampling), для чего необходимо установить параметр OPTIMIZER_DYNAMIC_SAMPLING, при этом параметр OPTIMIZER_FEATURES_ENABLE должен показывать версию 9.0.2 и выше Документация описывает следующие пути оптимизации SQL запросов
стабилизация планов выполнения 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 всегда выбирается самый дешевый из путей доступа (вес оценки операции) в последовательности:
Для обработки объединений в RBO и CBO выполняются правила - проверяется наличие соединения с результатом не больше одной строки (используется PRIMARY key или UNIQUE), и, при наличии, такое соединение ставится вперед порядка соединения, кроме того в outer join таблица outer всегда должна идти последней Оптимизатор RBO генерирует потенциальные порядки соединения (справа) как
При обработке запросов 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 приведены в соответствующем заметкам разделе конфигурации |
| Нравится |