Oracle собрать статистику по таблице
Зачем вообще нужна эта статистика или любые другие показатели? В ходе разработки часто возникает вопрос, а что же быстрее работает? Эффективно ли расходуются ресурсы базы? Если написать код по другому станет ли лучше?
В данной статье не будет рассказано как анализировать показатели, а будет рассказано как их получить.
Описание компонентов
v$mystat
Представление выводит статистику по последней выполненной транзакции в рамках текущей сессии. Состоит из следующих полей:
v$statname
Справочник с описанием значений статистики, нужен для связи с предыдущим представлением. Состоит из следующих полей:
v$timer
Это простое представление, которое возвращает время в сотых долях секунды, мы будет фиксировать время до и после теста, и затем отнимать одно значение от другого. Состоит из двух полей:
| Поле | Тип | Описание |
|---|---|---|
| hsecs | number | количество времени в сотых долях секунды |
| con_id | number | идентификатор контейнера (для мультиарендной контейнерной базы данных CDB) |
dbms_utility.get_cpu_time
Функция, которая возвращает время работы процессора (CPU) в сотых долях секунды.
select dbms_utility.get_cpu_time from dual; --результат 815Права
Для выполнения представления у пользователя (в моем случае PROD) должен быть доступ на все таблицы, подключаемся к БД с помощью пользователя SYS с ролью SYSDBA и выполняем скрипты ниже:
grant select on v_$statname to prod; grant select on v_$mystat to prod; grant select on v_$timer to prod; grant execute on dbms_utility to prod;Измерение общего и CPU времени на запрос
Логика простая - мы будем записывать значения времени в переменные до выполнения запроса и после, затем вычитать время завершения от времени начала и получать итоговый результат. Вывод результата будет осуществляться с помощью dbms_output.put_line , чтобы данные выводилилсь в SQL Developer перед выполнением скриптов выполним команду включающую вывод, а затем будет логика.
--включаем вывод данных в output set serveroutput on; declare v_t1 number; --время начала v_t2 number; --время завершения v_cpu1 number; --время ЦПУ до v_cpu2 number; --время ЦПУ после v_t_res number; --общее время выполнения v_cpu_res number; --общее время выполнения CPU begin --фиксируем начало --помещаяем общее время и время ЦПУ в переменные select t.hsecs ,dbms_utility.get_cpu_time into v_t1 ,v_cpu1 from v$timer t; --запустим 1000 раз цикл, который обновит дату у первых попавшихся строк for rec in 1..1000 loop update prod.operation set op_date = trunc(sysdate) where rownum = 1 and op_date != trunc(sysdate); end loop; --фиксируем завершение --снова помещаяем общее время и время ЦПУ в переменные select t.hsecs ,dbms_utility.get_cpu_time into v_t2 ,v_cpu2 from v$timer t; --считаем общее время выполнения в сотых долях секунды v_t_res := v_t2 - v_t1; --считаем общее время ЦПУ в сотых долях секунды v_cpu_res := v_cpu2 - v_cpu1; --выводим результат на экран dbms_output.put_line('Общее время выполнения в секундах: '||to_char(v_t_res/100,'0.00')); dbms_output.put_line('Общее время ЦПУ в секундах: '||to_char(v_cpu_res/100,'0.00')); --откатим изменения rollback; end;
Результат:
Статистика
Получение статистики по одному запросу
-
Запускаем нужный скрипт, например, изменим ранее сгенерированные данные:
Создание представления для быстрого доступа к данным
Для быстрого доступа к данным, соединим все ранее представленные скрипты по выводу данных по статистки, времени ЦПУ, общему времени и "упакуем" полученный запрос в представление. Вот как это будет выглядеть:
После создания VIEW, чтобы получить информацию достаточно выполнить простой запрос:
Создание процедуры фиксации статистики
До и после выполнения наших скриптов, нам нужно записывать данные "до" запуска и "после", будем записать результаты с помощью процедуры ниже:
Пояснение отдельных частей кода:
| Строка | Описание |
|---|---|
| p_script_num in char | первый входной параметр, предназначен для определения номера тестируемого скрипта |
| p_run_type in stats_tbl.run_type%type | второй входной параметр, принимает на вход два значения "before" или "after", тут если мы фиксируем статистику до перед выполнением скрипта указываем "before", если после то соответственно "after" |
| inf_type varchar2(15 char), | для указания типа информации т.е. мы умеем собирать статистику, измерять время ЦПУ и общее время, т.е. 3 разных типа информации, в будущем типы будут добавляться |
| pragma autonomous_transaction; | процедура будет транзакцией, внутри основной транзакции, например, в рамках теста мы сделали update строк и в конце нужно будет отменить изменения, но данные по статистике должны сохраниться |
| select row_number() over(partition by name, run_type order by s.id desc) rn | это для удаления дублей от запусков, например запустим два раза процедуру с параметром "after", будут дублироваться данные, эта функция группирует такие строки и проставляет номера, 1-самая актуальная, 2 - старее. |
| or run_type = 'final'||p_script_num | это условие, чтобы не дублировались финальные данные |
| on commit preserve rows; | Временные таблицы хранят данные в течение транзакции или в течение сессии, тут указывается, что хранить нужно в течение сессии |
Запустим программу (если будут ошибки, пишите в комментариях), пока результат мы не увидим, но статистику уже запишем:
--тест скрипта №1 begin --фиксируем начало работы fix_inf_prc(1,'before'); --скрипт №1 for rec in 1..100 loop update operation set op_date = trunc(sysdate) where rownum = 1 and op_date != trunc(sysdate); end loop; --фиксируем завершение работы fix_inf_prc(1,'after'); rollback; end; --тест скрипта №2 begin --фиксируем начало работы fix_inf_prc(2,'before'); --скрипт №2 for rec in 1..1000 --увеличим количесто циклов loop update operation set op_date = trunc(sysdate) where rownum = 1 and op_date != trunc(sysdate); end loop; --фиксируем завершение работы fix_inf_prc(2,'after'); rollback; end;Вывод результатов
Вывод статистики скриптом
Сразу обернем такой селект в VIEW для дальнейшего использования и расширения, также для лучшей читаемости, добавим форматирование для числа, чтобы тысячные доли разделялись запятыми:
Запускаем созданный VIEW, по желанию добавляем фильтры и сортируем:
select * from stats_report_vw where abs(diff) > 1 --фильтр по полю diff and name like '%ime%' --фильтр по полю name order by abs(diff);
Результат:
Вывод статистики через dbms_output
Создадим процедуру stats_report_prc и на вход будем подавать два фильтра, плюс я выведу отдельно от таблицы со статисткой показатели времени и буду выводить их в секундах:
create or replace procedure stats_report_prc (p_diff_count in number default 0, p_like_name in varchar2 default null, p_sort_field_num in number default 4) is begin --выводим статистику dbms_output.put_line(rpad('Name', 30) || lpad('Run1', 16) || lpad('Run2', 16) || lpad('Diff', 16)); for rec in (select s.name, to_char(s.script1, '999G999G999G999') script1, to_char(s.script2, '999G999G999G999') script2, to_char(s.diff, '999G999G999G999') diff from stats_report_vw s where s.name not like '%TIME. %' --исключаем ETIME, CTIME and abs(s.diff) > p_diff_count --фильтр по величине отличий and (p_like_name is null or lower(s.name) like lower('%'|| p_like_name ||'%')) --фильтр по имени order by p_sort_field_num) loop dbms_output.put_line(rpad(rec.name, 30) || lpad(rec.script1, 16) || lpad(rec.script2, 16) || lpad(rec.diff, 16)); end loop; --добавим отступ dbms_output.put_line(chr(9)); --выводим данные о времени dbms_output.put_line(rpad('Name', 30) || lpad('Run1 (sec)', 16) || lpad('Run2 (sec)', 16) || lpad('Diff (sec)', 16)); for rec in (select s.name, rtrim(to_char(s.script1/100, 'FM999999999999990.99999'), '.') script1, rtrim(to_char(s.script2/100, 'FM999999999999990.99999'), '.') script2, rtrim(to_char(s.diff/100, 'FM999999999999990.99999'), '.') diff from stats_report_vw s where s.name like '%TIME. %') --выводим только ETIME, CTIME loop dbms_output.put_line(rpad(rec.name, 30) || lpad(rec.script1, 16) || lpad(rec.script2, 16) || lpad(rec.diff, 16)); end loop; end;Запустим отчет и посмотрим что получилось:
set serveroutput on; begin stats_report_prc(1); -- выдодить где разница больше 1 end;
Результат:
Шесть тысяч слов помогут вам понять статистику Oracle и план выполнения
Источник | JiekeXu Road (ID: JiekeXu_IT)
Свяжитесь с авторизацией для перепечатки | (WeChat ID: xxq1426321293)
Всем привет, я JiekuXu, я очень рад снова встретиться с вами, поделитесь этим сегодня Статистика Oracle и план выполнения. Эта статья была впервые опубликована в общедоступном аккаунте WeChat [JiekeXu Road], пожалуйста, нажмите на синюю букву выше, чтобы подписаться на меня!
Предисловие
Несколько дней назад я получил приглашение от г-на Ян Цзяньжун, автора «Рабочих заметок Oracle DBA» и «MySQL DBA Work Notes» и соучредителя сообщества DBAplus на WeChat, и сказал, что поделится технической, на рабочем месте и идеи в его группе QQ От таких статей я сразу почувствовал искренность и ужас.Мне было честью, что обмен опытом - это тоже процесс обучения, поэтому я с радостью согласился на приглашение Учителя Яна. Думая, что вы также изучаете в последнее время вещи, связанные с оптимизацией, вы можете обобщить и поделиться во время обучения.Если в статье есть другие недостатки, сообщите об этом.
1. Статистика
Статистическая информация в основном описывает тип информации, такой как размер, масштаб и статус распределения данных таблиц и индексов в базе данных. Например, количество строк в таблице, количество блоков, средний размер каждой строки, листовые блоки индекса, количество строк в индексном поле и размер различных значений являются статистическими. Информация. Начиная с Oracle11G, автоматический сбор статистики базы данных был интегрирован в задачи автоматического обслуживания, которые в основном выполняются автоматически по умолчанию, что также в большинстве случаев соответствует требованиям эксплуатации, но также может быть собрано вручную.Поговорим об этом вместе.
Использоватьgather_stats_job Автоматический сбор данных создается автоматически, когда база данных создается и управляется планировщиком. Он собирает статистику для всех объектов в базе данных, для которых отсутствует или устаревшая статистика оптимизатора.
Использоватьdbms_stats Пакет вручную собирает системную статистику.
Просмотр статуса задач автоматического сбора статистики
Имя задачи автоматического сбора статистической информации в oracle 11g - автоматический сбор статистики оптимизатора. Окно времени выполнения по умолчанию для автоматических задач в 11g (введение в временное окно Oracle):
С понедельника по пятницу начинается в 22:00 и заканчивается в 14:00.
Выходные - шесть часов утра и продолжаются 20 часов.
В этот период нагрузка на сервер, как правило, относительно невелика. Ресурсы сервера можно оставить для сбора статистической информации, а сбор статистической информации более ресурсоемкий. Это можно увидеть в следующем утверждении:
Как и выше, задача сбора статистической информации выполняется каждый день, но если нагрузка на сервер базы данных велика, а нагрузка высока ночью, эта задача может не выполняться. Вам нужно скорректировать эту задачу и время. Его можно отключить или изменить, но, как правило, он не отключен. Вам необходимо изменить временное окно на определенное время
- Остановить и запустить отдельную задачу (то есть остановить задачу в определенный день)
Связанные просмотры:
Вот и все, что касается автоматического сбора. Для получения дополнительной информации проверьте официальные документы или Doc ID 1300313.1
How to Create an Own Maintenance Window for Autotask Jobs in 11g (Doc ID1300313.1) . Позволь мне поговорить об этом dbms_stats Связанный с пакетом.
dbms_stats
Пакет DBMS_STATS в основном предоставляет методы для сбора (сбора), удаления (удаления), экспорта (экспорта), импорта (импорта) и изменения (установки) статистической информации. Говоря оdbms_stats Тогда нужно поговорить о команде анализа.
Разница между dbms_stats и анализировать: Dbms_stats - это пакет, используемый для сбора статистики в Oracle9i и более поздних версиях.Хотя команда анализа была всегда доступна, больше не рекомендуется использовать команду анализа для сбора статистики. Вместо этого используйте dbms_stats. Между ними существует большая разница. Dbms_stats может правильно собирать статистику таблицы разделов, что означает, что он может собирать глобальную статистику, в то время как анализ может собирать статистику только объектов нижнего уровня, а затем выводить и суммировать более высокие -уровневые объекты.Статистика: если таблица разделов собирает только статистику разделов, то суммирует статистику всех разделов, чтобы получить статистику на уровне таблицы. По сути, Analyze устарел. Он использовался более семи или восьми лет назад. Oracle и эксперты рекомендуют пакет dbms_stats.
Пакет dbms_stats может собирать статистику о базах данных, словарях данных, индексах, таблицах и т. Д.
dbms_stats.gather_table_ststs параметр
1、 cascade:
true: указывает, что статистика собирается вместе с индексом при подсчете
2、 no_invalidate:
true: после сбора статистики исходный план выполнения не становится недействительным.
false: после сбора статистики исходный план выполнения становится недействительным.
По умолчанию DBMS_STATS.AUTO_INVALIDATE, Oracle решает, когда сделать недействительным план выполнения.
3、 method_opt:
FOR ALL [INDEXED | HIDDEN] COLUMNS[size_clause]
FOR COLUMNS [size clause] column[size_clause] [,column [size_clause]. ]
Когда данные поля распределены неравномерно, создайте гистограмму (гистограмму):
Статистика гистограммы: значения столбца индексного поля для создания статистики
Многоколоночная статистика: статистика создания столбца составного индекса
Статистика выражений: создание статистики по клавишам индекса функций
Примечание. Случай двух приведенных выше строк отличается, при использовании exec его нужно записать в одну строку. Анонимные блоки могут быть записаны в несколько строк. Кроме того, значение Size равно 1-254, но после 12c значение становится 1-2048.
Просмотр статистики:
Примечание. Обычные пользователи могут проверить user_tab_col_statistics, а пользователи DBA могут проверить dba_tab_col_statistics. Конечно, вы также можете использовать dba_tab_statistics для просмотра времени последнего сбора статистической информации.
Вот и все статистические данные, давайте перейдем к сегодняшней теме: План реализации 。
2. План реализации
План выполнения: описание пути доступа или процесса выполнения оператора SQL в базе данных.Oracle через оптимизатор Optimizer (Оптимизатор здесь относится к оптимизатору на основе затрат [Cost Based Optimizer, CBO]) для поиска оптимального плана выполнения для выполнения. Затем мы сначала понимаем, как выполняется следующий SQL: обычно он проходит через три этапа: синтаксический анализ (Parse), выполнение (Execute) и получение (Fetch), которые выполняются различными компонентами Oracle. Подробная информация должна быть быть объясненным из архитектуры Oracle. Я не буду говорить об этом здесь.
rowsource Источник строки: в запросе подходящим набором данных, возвращенным предыдущей операцией, может быть вся таблица или ее часть.Конечно, также можно выполнить операцию соединения для двух таблиц. Что касается жесткого анализа, можно просмотреть мягкий анализПредыдущая статья。
В-третьих, просмотрите план выполнения
Часть SQL выполняется в базе данных и возвращает результат. Что происходит в середине и какие пути были доступны. Это требует проверки плана выполнения. Оптимизатор выберет наиболее разумный и наиболее эффективный метод выполнения, который, по его мнению, является наиболее разумный и эффективный метод выполнения для выполнения возврата SQL.Набор результатов предоставляется клиенту, поэтому давайте взглянем на общие методы проверки плана выполнения, включая, помимо прочего, следующие семь методов.
Возможно, Вам потребуется собрать статистику вручную в тот или иной раз, например когда содержание таблицы изменилось между автоматическими заданиями сбора так, что статистические данные больше не представляют таблицу точно. Это характерно для больших таблиц, которые испытывают изменений больше чем 10 процентов от их размера в 24-часовой период.
Совет из передовой практики: Собирайте статистические данные достаточно часто, чтобы таблицы никогда не измелись более чем на 10 процентов (приблизительно) между периодами сбора статистики. Это может потребовать ручного сбора статистики или дополнительных окон обслуживания.
Статистические данные могут быть вручную собраны или при использовании Enterprise Manager, или с помощью пакета DBMS_STATS. Системная статистика может быть собрана только при использовании пакета DBMS_STATS. Системные статистические данные описывают аппаратные характеристики системы, такие как ввод-вывод, производительность ЦП и использование, для оптимизатора запросов.
Выбор пункта меню Gather Optimizer Statistics запускает мастер, который позволяет Вам выбрать контекст, объекты, опции и расписание для задания, которое соберет статистику оптимизатора. Мастер запускает задание DBMS_STATS.GATHER_ * _ в контексте, который Вы определяете: таблица, схема или база данных. В этом мастере Вы устанавливаете предпочтения значений по умолчанию, используемых пакетом DBMS_STATS, и Вы указываете расписание, чтобы запустить задание в определенное время.
Сбор статистики вручную как повседневная операция не рекомендуется, потому что статистические данные собираются более эффективно и с меньшим воздействием на пользователей во время окон обслуживания. Ручное задание может также быть выполнено, если автоматическое задание перестало работать или было отключено.
Можно также собрать статистику оптимизатора, используя пакет DBMS_STATS непосредственно:
| SQL> EXEC dbms_stats.gather_table_stats('HR','EMPLOYEES'); SQL> SELECT num_rows FROM dba_tables 2 WHERE owner='HR' AND table_name = 'EMPLOYEES'; NUM_ROWS ---------- 214 |
Заметьте, что число строк теперь правильно отражает то, что было в таблице во время, когда статистические данные были собраны. DBMS_STATS также позволяет ручной сбор статистики для всей схемы или даже для целой базы данных.
Системные статистические данные не изменяются, если рабочая нагрузка не изменяется значительно. В результате системные статистические данные не нуждаются в частой корректировке. Процедура DBMS_STATS.GATHER_SYSTEM_STATS соберет системные статистические данные за указанный период, или можно запустить сбор системной статистики и выполнить другой вызов, чтобы прекратить сбор.
Подсказка из передовой практики: Используйте следующую команду, когда Вы создаете базу данных:
SQL> EXEC dbms_stats.gather_system_stats('NOWORKLOAD');
Опция NOWORKLOAD занимает несколько минут (в зависимости от размера базы данных) и получает оценки характеристик ввода-вывода, такие как среднее время поиска чтения и скорость передачи ввода-вывода.
В этой статье изложен многолетний опыт оптимизации 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). Но такая подмена должна быть временным решением до момента корректировки запроса, поскольку постоянная подмена хинта может привести к некоторому снижению производительности запроса.
Читайте также: