Как ускорить запрос oracle

Обновлено: 07.07.2024

Оптимизация SQL и оптимизация экземпляра Oracle на основе CBO

Оптимизация SQL является важным аспектом оптимизации данных.В этой статье будет проанализирована собственная CBO-оптимизация Oracle, которая представляет собой метод оптимизации, основанный на затратах. Oracle нужны различные статистические данные в качестве основы для оптимизации, чтобы автоматически оптимизировать операторы SQL. Внешние будут анализировать процесс выполнения sql и информацию о потреблении ресурсов через отслеживание sql. Проблемы с производительностью базы данных часто возникают после того, как система развернута в течение определенного периода времени, то есть когда большое количество пользователей начинает использовать систему, когда увеличивается объем обработки данных и различная вычислительная сложность системы, на этот раз часто прослеживается до начального этапа проектирования системы. Таким образом, нам все еще нужно писать эффективные операторы SQL на этапе кодирования. Я видел много статей по оптимизации sql в Интернете, но они не удовлетворительны, некоторые представляют собой очень общие описания, а некоторые - в корне неправильные методы, поэтому я повторно делюсь своим процессом обучения.

Обработка запросов и оптимизация запросов - это две взаимосвязанные концепции: процесс выполнения операторов SQL для получения данных во время обработки запроса, а оптимизация запросов - это процесс получения наилучшего плана выполнения путем анализа операторов SQL и других ресурсов. Вот лучший план выполнения. Я имею в виду план, который потребляет меньше всего ресурсов, таких как ЦП и системный ввод-вывод, содержащий сервер базы данных. Выполнение SQL делится на три этапа: этап синтаксического анализа, этап оптимизации оператора и этап выполнения запроса.

1.1 Этап синтаксического анализа

Анализ синтаксиса выполняется в SGA (SGA относится к глобальной области системы, включая буфер базы данных, буфер журнала повторного выполнения, общий пул, пул Java, большой пул, пул потоков), где оператор SQL разлагается на запрос реляционной алгебры, т. Е. , с помощью этих запросов реляционной алгебры, чтобы проверить, неверна ли грамматика этого sql, правильны ли ключевые слова и т. д.

1.2 Этап оптимизации предложения

Это самая важная часть из этих трех шагов. По умолчанию Oracle использует CBO для выбора наилучшего плана выполнения. Вы можете спросить, что такое CBO? ,Ok! CBO на самом деле является программой оптимизации, основанной на затратах, то есть она будет оценивать потребление затрат и преобразовывать такие ресурсы, как цикл выполнения ЦП, память, скорость ввода-вывода и т. Д., Во временные затраты. Конечно, лучше всего тот, у кого меньше всего времени. Например, синтаксический анализ Oracle также делится на жесткий и мягкий синтаксический анализ. Для разных версий Oracle количество жесткого синтаксического анализа разное. В Oracle12 количество жесткого синтаксического анализа составляет 19, а в Oracle11g количество жесткого синтаксического анализа - 59. .

На этом этапе Oracle преобразует дерево синтаксического анализа в логический запрос, а затем преобразует логический запрос в план физического запроса. И существует несколько видов физических планов запросов, потому что оптимизатор часто генерирует несколько эффективных планов запросов, а затем на основе этих планов делает оценки потребления затрат. Обратите внимание, что это всего лишь оценка волонтеров, и не все планы были реализованы. Итак, что является основанием для оценки оракула? Как правило, оценка будет основываться на следующих факторах: a, операция соединения и последовательность соединения, задействованного в запросе b, алгоритм, выполняемый операцией c, метод чтения данных, такой как чтение памяти или диска d, запрашивайте метод передачи данных между каждой операцией.

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

1.3 Выполнение запроса

Самый простой шаг в выполнении запроса - выполнить физический план запроса на шаге 2 прямо сейчас, а затем вернуть обработанные данные пользователю.

2.1 Метод оптимизации

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

Ниже представлены три метода оптимизации для удовлетворения различных требований запроса:

1. All_Rows: метод по умолчанию, цель оптимизации - достичь максимальной пропускной способности запроса.

2. FIRST_ROWS_n: оптимизировать вывод первых n строк данных в запросе, цель - удовлетворить потребности быстрого ответа.

3. FIRST_ROWS: используйте оптимизацию затрат CBO, чтобы вывести первые несколько строк данных как можно скорее, чтобы удовлетворить минимальные требования к времени отклика.

Oracle обеспечивает оптимизацию на трех уровнях: уровне экземпляра, уровне сеанса и уровне оператора.

Запросите метод оптимизации CBO для текущей базы данных:

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

2.2 Рабочий процесс оптимизатора

CBO выполняет оптимизацию SQL за 4 шага

1. Преобразование операторов SQL на основе статистических данных: это означает, что CBO считает, что преобразованный запрос оператора будет более эффективным, поэтому преобразуйте свой оператор sql в другую форму, например, OR, которое вы пишете, преобразуется в UNION ALL, а между преобразуется в> = и <= и т. д.

2. Выберите путь доступа в соответствии с ситуацией с ресурсами: он относится к ресурсам, потребляемым для доступа к данным определенного пути.

3. Выберите метод подключения на основе статистических данных: если задействовано несколько таблиц, CBO выберет метод подключения на основе статистических данных и ключевой информации таблицы и выберет метод с наименьшими вычислительными затратами среди нескольких методов подключения как лучший. способ подключения.

4. Определите порядок подключения. Относится к количеству строк данных, используемых для определения наилучшего порядка подключения.

2.3 Статистика

--Просмотр текущего статуса работы gather_stats_job

- запросить статистический анализ таблицы, принадлежащей пользователю scott: sample_size представляет количество строк выборки.
select last_analyzed,table_name,owner,num_rows,sample_size from dba_tables where owner='SCOTT';

--- Статистика для всех таблиц режима Скотт (собрана вручную)
execute dbms_stats.gather_schema_stats(ownname => 'scott');

3.1 Оптимизировать запрос

1. Оптимизируйте запрос: объясните. Для запроса индекса при использовании Like, только% находится не в первой позиции, будет эффективным. При использовании запроса с несколькими столбцами, только когда первое поле этих полей используется в условии запроса, index будет использоваться только для ссылки, или при запросе условий индекс будет использоваться в запросе, когда все столбцы в двух условиях до и после являются индексами.

2. Оптимизировать структуру базы данных, разложить таблицы с множеством полей на несколько таблиц, добавить промежуточные таблицы, добавить избыточные поля, оптимизировать скорость вставки, отключить проверки уникальности, использовать пакетную вставку, запретить проверки внешнего ключа, запретить автоматическую отправку, оптимизировать таблицы оптимизировать
3. Оптимизировать сервер базы данных, оборудование: память, io, оптимизировать параметры.

4. Используйте переменные связывания: все мы знаем, что Oracle делится на жесткий анализ и мягкий анализ. В SGA общий пул предназначен для хранения проанализированного оператора SQL. В настоящее время общий пул содержит окончательный план выполнения оператора SQL. . Если есть тот же оператор SQL-запроса, нет необходимости повторно анализировать этот оператор SQL, но план выполнения оператора SQL выполняется непосредственно из общего пула. Общий пул используется, чтобы избежать жесткого синтаксического анализа, потому что каждый раз, когда вы переходите к жесткому синтаксическому анализу, вам необходимо повторно анализировать синтаксис и семантику оператора, а затем оптимизировать сгенерированный окончательный план выполнения с помощью CBO, который потребляет ресурсы ЦП. Использовать переменные связывания, которые мы обычно добавляем в оператор SQL при разработке Java? Выполнить, а затем передать параметры.

Например: выберите ename, job, sal из scott.emp, где deptno =?

Затем мы передаем параметры, которые не только предотвращают внедрение SQL, но и оптимизируют SQL.

5. Исключите подзапросы: для некоторых вложенных подзапросов добавьте вложенные операторы SQL, например:

select * from scott.emp e1 where e1.sal>

(select avg(sal) from scott.emp e2 where e2.deptno=e1.deptno);

Такой оператор SQL должен каждый раз выполнять N * M операций. Для определенных значений вы можете использовать трассировку SQL ниже для анализа производительности.

from scott.emp e1,(select e2.deptno ,avg(e2.sal) avg_sal from scott.emp e2 group by deptno) d

where e1.deptno=d.deptno and e1.sal >d.avg_sal

Оптимизированному sql нужно только выполнить операцию N + M, и его масштабируемость выше, и результаты вычислений не будут увеличиваться экспоненциально. Хотя изначально кажется, что оптимизированный оператор SQL кажется немного длиннее, если вам интересно, правильно он или нет, вы можете использовать инструмент анализа операторов SQL, о котором мы поговорим дальше, чтобы сравнить его, и вы можете проверить его с помощью план его выполнения.

3.2 Инструмент оптимизации операторов SQL

Используйте план объяснения для команды, чтобы получить план выполнения оператора SQL, поэтому сначала мы создаем таблицу, необходимую для выполнения этой команды.В каталоге установки oracle нам нужно найти файл utlxplan.sql и выполнить его. Путь к этому файлу, который я здесь, находится в E: \ oracle \ app \ product \ 11.2.0 \ dbhome_1 \ RDBMS \ ADMIN \ utlxplan.sql, команда выполнения выглядит следующим образом:

Просмотрите эту структуру таблицы:

SQL> desc plan_table;

SQL> desc plan_table

Name Type Nullable Default Comments

STATEMENT_ID VARCHAR2(30) Y

PLAN_ID NUMBER Y

TIMESTAMP DATE Y

REMARKS VARCHAR2(4000) Y

OPERATION VARCHAR2(30) Y

OPTIONS VARCHAR2(255) Y

OBJECT_NODE VARCHAR2(128) Y

OBJECT_OWNER VARCHAR2(30) Y

OBJECT_NAME VARCHAR2(30) Y

OBJECT_ALIAS VARCHAR2(65) Y

OBJECT_INSTANCE INTEGER Y

OBJECT_TYPE VARCHAR2(30) Y

OPTIMIZER VARCHAR2(255) Y

SEARCH_COLUMNS NUMBER Y

PARENT_ID INTEGER Y

DEPTH INTEGER Y

POSITION INTEGER Y

CARDINALITY INTEGER Y

BYTES INTEGER Y

OTHER_TAG VARCHAR2(255) Y

PARTITION_START VARCHAR2(255) Y

PARTITION_STOP VARCHAR2(255) Y

PARTITION_ID INTEGER Y

DISTRIBUTION VARCHAR2(30) Y

CPU_COST INTEGER Y

IO_COST INTEGER Y

TEMP_SPACE INTEGER Y

ACCESS_PREDICATES VARCHAR2(4000) Y

FILTER_PREDICATES VARCHAR2(4000) Y

PROJECTION VARCHAR2(4000) Y

QBLOCK_NAME VARCHAR2(30) Y

OTHER_XML CLOB Y

Затем мы используем эту команду для анализа выполнения оператора SQL:

SQL> explain plan for
2 select count(*) from scott.emp;
Explained

Давайте посмотрим на информацию о плане выполнения инструкции sql в таблице plan_table:

SQL> col id for 999
SQL> col operation for a20
SQL> col options for a20
SQL> col object_name for a20
SQL> select id,operation,options,object_name,options from plan_table;
ID OPERATION OPTIONS OBJECT_NAME OPTIONS
--- -------------------- -------------------- -------------------- --------------------
0 SELECT STATEMENT
1 SORT AGGREGATE AGGREGATE
2 INDEX FULL SCAN PK_EMP FULL SCAN

Мы видим, что это полное сканирование таблицы, указывающее, что это emp.

Что, если мы хотим проанализировать этот sql более глубоко, например, мы хотим узнать объект доступа, потребление ЦП и другую информацию об этом. Затем мы можем включить трассировку SQL.

1. Используйте команду autotrace.

Используйте эту команду для отслеживания операторов SQL и анализа этапов их выполнения, статистической информации, такой как объем данных физического чтения, данных, отсортированных на диске и в памяти.

Поделюсь опытом, который получил за несколько лет оптимизации sql запросов. Большая часть советов касается субд ORACLE.
Если кому статья покажется слишком очевидной, то считайте это заметкой чисто для себя, чтобы не забыть.

1. Ни каких подзапросов, только JOIN
Как я уже писал ранее, если выборка 1 к 1 или надо что-то просуммировать, то ни каких подзапросов, только join.
Стоит заметить, что в большинстве случаев оптимизатор сможет развернуть подзапрос в join, но это может случиться не всегда.

2. Выбор IN или EXISTS ?
На самом деле это сложный выбор и правильное решение можно получить только опытным путем.
Я дам только несколько советов:
* Если в основной выборке много строк, а в подзапросе мало, то ваш выбор IN. Т.к. в этом случае запрос в in выполнится один раз и сразу ограничит большую основную таблицу.
* Если в подзапросе сложный запрос, а в основной выборке относительно мало строк, то ваш выбор EXISTS. В этом случае сложный запрос выполнится не так часто.
* Если и там и там сложно, то это повод изменить логику на джойны.

3. Не забывайте про индексы
Совет для совсем новичков: вешайте индексы на столбцы по которым джойните таблицы.

4. По возможности не используйте OR.
Проведите тесты, возможно UNION выглядит не так элегантно, за то запрос может выполнится значительно быстрей. Причина в том, что в случае OR индексы почти не используются в join.

5. По возможности не используйте WITH в oracle.
Значительно облегчает жизнь, если запрос в with необходимо использовать несколько раз ( с хинтом materialize ) в основной выборке или если число строк в подзапросе не значительно.
Во всех других случаях необходимо использовать прямые подзапросы в from или взаранее подготовленную таблицу с нужными индексами и данными из WITH.
Причина плохой работы WITH в том, что при его джойне не используются ни какие индексы и если данных в нем много, то все встанет. Вторая причина в том, что оптимизатору сложно определить сколько данных нам вернет with и оптимизатор не может построить правильный план запроса.
В большинстве случаев WITH без +materialize все равно будет развернут в основной запрос.

6. Не делайте километровых запросов
Часто в web обратная проблема - это много мелких запросов в цикле и их советуют объединить в один большой. Но тут есть свои ограничения, если у вас запрос множество раз обернутый в from, то внутреннюю(ие) части надо вынести в отдельную выборку, заполнить временную таблицу, навесить индексы, а потом использовать ее в основной выборке. Скорость работы будет значительно выше (в первую очередь из-за сложности построения оптимального плана на большом числе сочетаний таблиц)

7. Используйте KEEP взамен корреляционных подзапросов.
В ORACLE есть очень полезные аналитические функции, которые упростят ваши запросы. Один из них - это KEEP.
KEEP позволит сделать вам сортировку или группировку основной выборки без дополнительно запроса.
Пример: отобрать контрагента для номенклатуры, который раньше остальных был к ней подвязан. У одной номенклатуры может быть несколько поставщиков.
При обычном бы подходе пришлось бы делать корреляционный подзапрос для каждой номенклатуры с выбором минимальной даты.
Но не злоупотребляйте большим числом аналитических функций, особенно если они имеют разные сортировки. Каждая разная сортировка - это новое сканирование окна.

8. Гуляние по выборке вверх-вниз
Менее популярная функция, но не менее полезная. Позволяет смещать текущую строку выборки на N элементов вверх или вниз. Бывает полезно, если необходимо сравнить показатели рядом стоящих строк.
Следующий пример отбирает продажи департаментов отсортированных по дате. К основной выборке добавляются столбцы со следующим и предыдущим значением выручки. Второй параметр - это на сколько строк сместиться, третьи - параметр по-умолчанию, если данные соседа не нашлись. При обычном подходе бы пришлось это делать через логику приложения.

9. Direct Path Read
Установка этой настройки (настройкой или параллельным запросом) - чтение данных напрямую в PGA, минуя буферный кэш. Что укоряет последующие этапы запроса, т.к. не используется UNDO и защелки совместного доступа.

10. Direct IO
Использование прямой записи/чтения с диска без использования буфера файловой системы (файловая система конкретно для СУБД).
* В случае чтения преимущество в использовании буферного кэша БД, замен кэша ФС (кэш бд лучше заточен на работу с sql)
* В случае записи, прямая запись гарантирует, что данные не потеряются в буфере ФС в случае выключения электричества (для redolog всегда использует fsync, в не зависимости от типа ФС)

1. Используйте конкретные имена столбцов после оператора select, вместо «*» – это позволит увеличить быстроту отработки запроса и уменьшению сетевого трафика.

2. Сведите к минимуму использование подзапросов.

Select Column_A From Table_1 Where Column_B = (Select max (Column_B From Table_2) And Column_C = (Select max (Column_C From Table_2) And Column_D = ‘position_2’

выглядит значительно хуже на фоне аналогичного запроса:

Select Column_A From Table_1 Where (Column_B, Column_C) = (Select max (Column_B), max (Column_C) From Table_2)

3. Используйте оператор IN аккуратно, поскольку на практике он имеет низкую производительность и может быть эффективен только при использовании критериев фильтрации в подзапросе.

4. Соединение таблиц в запросе также является критичным: в случае, когда соединение таблиц происходит в правильном порядке, то общее число строк, необходимых к обработке, значительно сократится.

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

5. При соединении таблиц EXIST предпочтительнее distinct (таблицы отношения «один-ко-многим»).

6. Избыточность при работе с SQL – это критичная необходимость, используйте в разделе WHERE как можно больше ограничивающих условий.

Например, если указан

вы сможете вывести результат, где Column_В=425, однако при задании условий

оператор не сможет определить, что Column_A=Column_C.

8. Помните, что одного и того же результата можно добиться разными способами. Например, оператор MINUS выполняется гораздо быстрее, чем запросы с оператором WHERE NOT EXIST. Запрос с данным оператором в самом общем виде выглядит следующим образом:

Select worker_id From workers MINUS Select worker_id From orders

Этот пример показывает все значения worker_id, которые содержаться в таблице workers, не в таблице orders. Другими словами, если бы значение worker_id одновременно присутствовало в таблицах workers и orders, то значение worker_id не вывелось в результат, поскольку нет конкретики, содержание какой именно таблицы вывести как результат отработки запроса.

9. Оформляйте повторяющиеся коды в пользовательскую процедуру. Это может значительно ускорить работу, уменьшить сетевой трафик.

Таким образом, рассмотренные нами моменты работы с SQL операторами и запросами значительно ускоряют работу с СУБД.

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

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

В этой статье изложен многолетний опыт оптимизации SQL-запросов в процессе работы с базами данных Oracle 9i, 10g и 11g. В качестве рабочего инструмента для получения планов запросов мною используется всем известные программные продукты Toad и PLSQL Developer.

Нередко возникают ситуации, когда запрос работает долго, потребляя значительные ресурсы памяти и дисков. Назовем такие запросы неэффективными или ресурсоемкими.
Причины ресурсоемкости запроса могут быть следующие:

плохая статистика по таблицам и индексам запроса;
проблемы с индексами в запросе;
проблемы с хинтами в запросе;
неэффективно построенный запрос;
неправильно настроены параметры инициализации базы данных, отвечающие за производительность запросов.

Программные средства, позволяющие получить планы выполнения запросов, можно разделить на 2 группы:

средства, позволяющие получить предполагаемый план выполнения запроса;
средства, позволяющие получить реальный план выполнения запроса;

К средствам, позволяющим получить предполагаемый план выполнения запроса, относятся Toad, SQL Navigator, PL/SQL Developer и др. Это важный момент, поскольку надо учитывать, что реальный план выполнения может отличаться от того, что показывают эти программные средства. Они выдают ряд показателей ресурсоемкости запроса, среди которых основными являются:

Чем больше значение этих показателей, тем менее эффективен запрос.
Ниже приводиться пример плана выполнения запроса:

полученного в Toad

Из плана видно, что наибольшие значения Cost и Cardinality содержатся во 2-й строке, в которой и надо искать основные проблемы производительности запроса.
Вместе с тем, многолетний опыт оптимизации показывает, что качественный анализ эффективности запроса требует, помимо Cost и Cardinality, рассмотрения других дополнительных показателей:

CPU Cost — процессорная стоимость выполнения;
IO Cost — стоимость ввода-вывода;
Temp Space – показатель использования дискового пространства.

Если дисковое пространство используется (при нехватке оперативной памяти для выполнения запроса, как правило, для проведения сортировок, группировок и т.д.), то с большой вероятностью можно говорить о неэффективности запроса. Указанные дополнительные параметры с соответствующей настройкой можно увидеть в PL/SQL Developer и Toad при их соответствующей настройке. Для PL/SQL Developer в окне с планом выполнения надо выбрать изображение гаечного ключа, войти в окно Preferensec добавить дополнительные параметры в Select Column, после чего и нажать OK. В Toad в плане выполнения по правой кнопке мыши выбирается директива Display Mode, а далее Graphic, после чего появляется дерево, в котором по каждому листу нажатием мышки можно увидеть дополнительные параметры: CPU Cost, IO Cost, Cardinality. Структура плана запроса, указанного выше, в виде дерева приведена ниже.

Предполагаемый план выполнения запроса с Cost и Cardinality можно также получить, выполнив после анализируемого запроса другой запрос, формирующий план выполнения:

Дополнительно в плане выполнения запроса выдается значение SQL_ID запроса, который можно использовать для получения реального плана выполнения запроса с набором как основных (Cost, Cardinality), так и дополнительных показателей через запрос:

Реальный план выполнения запроса и указанный выше перечень характеристик для анализа ресурсоемкого запроса дают динамические представления Oracle: V$SQL_PLAN и V$SQL_PLAN_MONITOR (последнее представление появилось в Oracle 11g).

План выполнения запроса получается из представления Oracle по запросу:

где SQL_ID – это уникальный идентификатор запроса, который может быть получен из разных источников, например, из представления V$SQL:

Трассировочный файл — это еще одно средство получение реального плана выполнения. Это довольно сильное средство диагностики и оптимизации запроса. Для получения трассировочного файла ( в Toad или PL/SQL Developer) запускается PL/SQL блок:

где первая, третья и последняя строки являются стандартными, а во второй строке пишется идентификатор (любые символы), который включается в имя трассировочного файла. Так, если в качестве идентификатора напишем M_2013, то имя трассировочного файла будет включать этот идентификатор и будет иметь вид: oraxxx_xxxxxx_ M_2013.trc. Результат пишется в соответствующую директорию сервера, которая находиться из запроса

Трассировочный файл для удобства чтения расшифровывается утилитой Tkprof (при определенном навыке анализировать можно без расшифровки, в этом случае имеем более детальную информацию).

Ещё одним из средств получения реального плана выполнения запроса с получением рекомендаций по его оптимизации является средство Oracle SQLTUNE.

Для анализа запроса запускается PL/SQL блок (например, в Toad или PL/SQL Developer) , в котором имеются стандартные строки и анализируемый запрос. Для рассматриваемого выше запроса блок PL/SQL примет вид:

Все строки (кроме текста запроса) являются стандартными.

Далее запуск запрос, который выдает на экран текст рекомендаций:

Для работы SQLTUNE необходимо как минимум из под SYSTEM выдать права на работу с SQLTUNE схеме, в которой запускается PL/SQL блок. Например, для выдачи прав на схему HIST выдается GRANT ADVISOR TO HIST;

В результате работы SQLTUNE выдает рекомендации (если Oracle посчитает, что есть что рекомендовать). Рекомендациями могут быть: собрать статистику, построить индекс, запустить команду создания нового эффективного плана и т.д.

Анализ плана выполнения запроса.

Анализ плана выполнения запроса имеет определенную последовательность действий. Рассмотрим на примере плана выполнения запроса из представление V$SQL_PLAN для ранее приведенного запроса

При анализе план просматриваетcя снизу вверх. В процессе просмотра в первую очередь обращается внимание на строки с большими Cost, CPU Cost.
Как видно из плана, резкий скачек этих значений имеется в 4-ой строке. Причиной такого скачка является 5-я строка с INDEX FULL SCAN, указывающая на наличие полного сканирование индекса X_DICTI_NAME таблицы DICTI. С этих строк и надо начинать поиск причины ресурсоемкости запроса. После нахождения строки с большим Cost и CPU Cost продолжается просмотр плана снизу вверх до следующего большого CPU Cost и т.д. При этом, если CPU Cost в строке близок к CPU Cost первой строки (максимальное значение), то найденная строка является определяющей в ресурсоемкости запроса и с ней в первую очередь надо искать причину ресурсоемкости запроса.
Помимо поиска больших Cost и CPU Cost в строках плана следует просматривать первый столбец Operation плана на предмет наличия в нем HASH JOIN. Соединение по HASH JOIN приводит к соединению таблиц в памяти и, казалось бы, более эффективным, чем вложенные соединения NESTED LOOPS. Вместе с тем, HASH JOIN эффективно при наличии таблиц, хотя бы одна из которых помещаются в память БД или при наличии соединения таблиц с низкоселективными индексами. Недостатком этого соединения является то, что при нехватке памяти для таблицы (таблиц) будут задействованы диски, которые существенно затормозят работу запроса. В связи с чем, при наличии высокоселективных индексов, целесообразно посмотреть, а не улучшит ли план выполнения хинт USE_NL, приводящий к соединению по вложенным циклам NESTED LOOPS. Если план будет лучше, то оставить этот хинт. При этом в хинте USE_NL в скобках обязательно должны перечисляться все алиасы таблиц, входящих во фразу FROM, в противном случае может возникнуть дефектный план соединения. Этот хинт может быть усилен хинтами ORDERED и INDEX. Следует обратить так же внимание на MERGE JOIN. При большом CPU Cost в строке с MERGE JOIN стоит проверить действие хинта USE_NL для улучшения эффективности запроса.
Особое внимание в плане следует так же уделить строкам в плане с операциями полного сканирования таблиц и индексов в столбец Operation: FULL — для таблиц и FULL SCAN, FAST FULL SCAN , SKIP SCAN — для индексов. Причинами полного сканирования могут быть проблемы с индексами: отсутствие индексов, неэффективность индексов, неправильное их применение. При небольшом количестве строк в таблице полное сканировании таблицы FULL может быть нормальным явлением и эффективнее использования индексов.
Наличие в столбце Operation операции MERGE JOIN CARTESIAN говорит, что между какими-то таблицами нет полной связки. Эта операция возникает при наличии во фразе From трех и более таблиц, когда отсутствуют связи между какой-то из пар таблиц.
Решением проблемы может быть добавление недостающей связки, иногда помогает использование хинта Ordered.

Оптимизация запроса

После анализа плана выполнения запроса осуществляется его оптимизация.
Оптимизация запроса предполагает удаление причин неэффективности запроса, среди которых наиболее весомыми являются:

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

Неэффективная статистика.

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

Устаревшая статистика. Время последнего сбора статистики определяется значением поля Last_Analyzed для таблиц и индексов, которое находиться из Oracle таблиц ALL_TABLES (DBA_TABLES) и ALL_INDEXES (DBA_INDEXES) соответственно. Oracle ежедневно в определенные часы в рабочие дни и в определенные часы в выходные сам собирает статистику по таблицам. Но для этого DML операции с таблицей должны привести к изменению не менее 10% строк таблицы. Однако, мне приходилось сталкиваться с ситуацией, когда в течение дня таблица неоднократно и существенно меняет число строк или таблица столь большая, что 10% изменений наступает через длительное время. В этом случае приходилось обновлять статистику, используя процедуры сбора статистики внутри пакетов, а ряде случае использовать JOB, запускающийся в определенные часы для анализа и обновления статистики.
Статистика по таблице и индексу (на примере таблицы AGREEMENT и индекса X_AGREEMENT в схеме HIST) обновляется соответственно процедурами:
для таблицы:
для индекса:

где число 10 в процедуре указывает на процент сбора статистики. С учетом времени сбора статистики и числа строк в таблице (индексе) были выработаны рекомендации для таблиц (индексов) по проценту сбора статистики: если число строк более 100 млн. процент сбора устанавливать 2 -5, при числе строк с 10 млн. до 100 млн. процент сбора устанавливать 5-10, менее 10 млн. процент сбора устанавливать 20 -100. При этом, чем выше процент сбора, тем лучше, однако, при этом растет и может быть существенным время сбора статистики.

Для таблиц процент сбора статистики (на примере таблицы AGREEMENT в схеме HIST) вычисляется запросом:

Процент сбора статистики по индексу находиться по запросу

Необходимо пересобрать статистику по таблице или индексу с плохой статистикой.

Замечание. При хорошем значении статистики по таблице может быть неблагополучная статистика по какому-то индексу таблицы, в силу чего целесообразно отслеживать статистику не только таблицы, но и индексов таблицы.

Проблемы с индексами

Проблемы с индексами в плане выполнения проявляются при наличии в столбце Options значений FULL, FULL SCAN, FAST FULL SCAN и SKIP SCAN в силу следующих причин:

Отсутствие нужного индекса. Требуемое действие — создать новый индекс;
Индекс имеется, но он неэффективно построен. Причинами неэффективности индекса могут быть:
— Малая селективность столбца, на котором построен индекс, т.е. в столбце много одинаковых значений, мало уникальных значений. Решение в данной ситуации — убрать индекс из таблицы или столбец, на основе которого построен индекс, ввести в составной индекс.
— Столбец селективный, но он входит в составной индекс, в котором этом столбец не является первым (ведущим) в индексе. Решение – сделать этот столбец ведущим или создать новый индекс, где столбец будет ведущим;
Построен эффективный индекс, но он работает не эффективно в силу следующих причин:
— Индекс заблокирован от использования. Блокируют использование индекса следующие операции над столбцом, по которому используется индекс: SUBSTR, NVL, DECODE, TO_CHAR,TRUNC,TRIM, ||конкатенация, + цифра к цифровому полю и т.д.
Решение – модифицировать запрос, освободиться от блокирующих операций или создать индекс по функции, блокирующей индекс.
— Не собрана или неактуальная статистика по индексу. Решение – собрать статистику по индексу запуском процедуры, указанной выше.
— Имеется хинт, блокирующий работу индекса, например NO_INDEX.
— Неэффективно настроены параметры инициализации базы данных БД (особенно отвечающие за эффективную работу индексов, например, optimizer_index_caching и optimizer_index_cost_adj). По моему опыту использования Oracle 10g и 11g эффективность работы индексов повышалась, если optimizer_index_caching=95 и optimizer_index_cost_adj=1.
Имеются сильные индексы, но они соперничают между собой.
Это происходит тогда, когда в условии where имеется строка, в которой столбец одной таблицы равен столбцу другой таблицы. При этом на обоих столбцах построены сильные или уникальные индексы. Например, в условии Where имеется строка AND A.ISN=B.ISN. При этом оба столбца ISN разных таблиц имеют уникальные индексы. Однако, эффективно может работать индекс только одного столбца (левого или правого в равенстве). Индекс другого столбца, в лучшем случае, даст FAST FULL SCAN. В этой ситуации, чтобы эффективно заработали оба индекса, потребуется вести дополнительное условие для одного из столбцов.
Индекс имеет большой фактор кластеризации CLUSTERING_FACTOR.
По каждому индексу Oracle вычисляет фактор кластеризации (ФК), определяющий число перемещений от одного блока к другому в таблице при выборе индексом строк из таблицы. Минимальное значение ФК равно числу блоков таблицы, максимальное — числу строк в таблице. CLUSTERING_FACTOR определяется по запросу:

Фактор кластеризации для индекса считает во время сбора статистики. Он используется оптимизатором при расчете стоимости индексного доступа к данным таблицы. Большой ФК (особенно близкий к числу строк в таблице) говорит о неэффективном индексе. Таким образом, ФК является характеристикой индекса, а не таблицы. Первое решение при большом ФК является убрать существующий индекс как не эффективный. Второе решение, если данный индекс наиболее часто применяется в запросах и он нужен, то перестроить структуру таблицы таким образом, чтобы строки в блоках таблицы были упорядочены в том же порядке, в котором расположена информация по данным строкам в индексе, т.е. сделать кластерными блоки таблицы, уменьшив таким образом число перемещений от одного блока к другому при работе индекса.

Проблемы с хинтами в запросе

Проблемы с хинтами могут быть следующие:

Неэффективный хинт. Он может привести к существенному снижению производительности. Причины возникновения не эффективности хинтов:
— хинт был написан, когда БД работала на 9-ом Oracle, при переходе на Oracle 10g и выше хинт стал тормозом (это могут быть хинты Rule, Leading и др.). Leading –мощный хинт, но при переходе на другую версию Oracle в некоторых случаях приводит в резкому снижению производительности и перед применение этих хинтов необходимо учитывать вероятность изменения со временем статистики системы и ее объектов (таблиц и индексов), используемых в запросе;
— в хинте USE_NL содержится не полный перечень алиасов;
— в составном хинте используется неправильный порядок следования хинтов, в результате чего хинты блокирую эффективную работу друг. Например, хинт Leading полностью игнорируются при использовании двух или более конфликтующих подсказок Leading или при указании в нем более одной таблицы.
— хинт написан давно, после чего была модификация запроса (например, отсутствует или изменился индекс, указанный в хинте).
В запросе отсутствует хинт, который бы повысил эффективность работы запроса. В ряде случаем наличие хинта повышает эффективность запроса и обеспечивает стабилизацию планов выполнения (например, при изменении статистики).
При создании хинта в запросе есть ряд рекомендаций:
— В хинте INDEX могут быть перечислены несколько индексов. Оптимизатор сам выберет соответствующий индекс. Можно поставить хинт NO_INDEX, если надо заблокировать использование какого-то индекса.
— При наличии Distinct в запросе Distinct ставиться после хинта (т.е. хинт всегда идет после Select).
— Наиболее эффективные и часто используемыми являются хинты: Ordered, Leading, Index , No_Index, Index_FFS, Index_Join, Use_NL, Use_Hash, Use_Merge, First_Rows(n), Parallel, Use_Concat, And_Equal, Hash_Aj и другие. При этом, например, индекс Index_FFS кроме быстрого полного сканирования индекса позволяет ему выполняться параллельно, в силу чего можно получить существенный выигрыш в производительности. Пример такого использования для секционированной таблицы где T-алиас таблицы.
— Изменение параметров инициализации базы данных в пределах запроса позволяет сделать хинт /*+ opt_param('Параметр инициализацци' N) */ , например, /*+ opt_param('optimizer_index_caching' 10) */. Данный хинт используется для проверки производительности работы запроса в случае, когда запрос разрабатывается или тестируется на базе с одним значением параметров инициализации, а работает на базе с другими значениями.

Замечание. В некоторых случаях, когда хинт неэффективный, но заменить его оперативно в запросе не представляется возможным (например, чужая разработка), имеется возможность, не меняя рабочий запрос в программном модуле, заменить хинт (хинты) в запросе, а также в его подзапросах, на эффективный хинт (хинты). Это прием — подмена хинтов (который известен, как использование хранимых шаблонов Stored Outlines). Но такая подмена должна быть временным решением до момента корректировки запроса, поскольку постоянная подмена хинта может привести к некоторому снижению производительности запроса.

Читайте также: