Что такое план запроса в sql oracle

APPS-ORACLE.RU

PL/SQL Developer — Просмотр плана запроса в окне сессий

10 Июнь 2010 rudev Написать комментарий К комментариям

В окне сессий жмем на изображение ключа (Define Session Queris), в появившемся окне переходим на вкладку Details

SELECT lpad(' ', 2 * level) || pt.operation || ' ' || pt.options || ' ' || pt.object_name "Query Plan", pt.cost, pt.cardinality, pt.bytes, pt.cpu_cost, pt.io_cost, pt.access_predicates, pt.filter_predicates, pt.qblock_name, pt.temp_space FROM (select * from v$sql_plan where sql_id = :sql_id and child_number = :sql_child_number) pt CONNECT BY PRIOR pt.id = pt.parent_id START WITH pt.id = 0;

Похожие записи:

• MySave — Plug-in for PL/SQL Developer
• Регулярные выражения. Удаление последней буквы или цифры в строке.
• SQL Склонение количества ошибок
• Удаление управляющих символов из строки
• Список параметров процедуры SQL запросом

Categories: Others Tags: explain plan, PL/SQL Developer, plan, SQL, план, план запроса

Четыре способа корректировки планов запросов по образцу без изменения кода.

Источник: Russian Oracle User Group with the library and forum options,
http://www.ruoug.org/events/20121206/index.html
[От редакции FORS Magazine: дополнением к данной статье является одноименная презентация И.Деева, представленная им на семинаре Российской группы пользователей Oracle 06.12.2012.] [От редакции FORS Magazine: в процессе обсуждения этой статьи уже после её опубликования обнаружилась небольшая неточность: в скрипте 1.1 выдача привилегий была корректна только для Oracle Database 11g и не применима в версии 10g. В настоящем тексте эта неточность исправлена. К сожалению, исправить также оперативно эту неточность в pgf-форматах этого выпуска журнала не представляется возможным, поэтому в следующем 6-м номере журнала будет опубликовано соответствующее письмо в редакцию нашего автора Ильи Деева.]

Введение

В процессе эксплуатации баз данных иногда возникает необходимость скорректировать планы запросов, не прибегая к изменению исходного кода. Такие средства влияния на план запроса как корректировка статистики, изменение параметров сессии, построение SQL профиля не всегда доступны, не всегда гарантированно ведут к нужному результату, а также могут иметь нежелательные побочные эффекты. Корректировка статистики объектов базы данных и установка параметров в сессии могут повлиять не только на проблемные запросы, но и на другие. Построение SQL профилей возможно только в Oracle Enterprise Edition при использовании платных Diagnostic &Tuning Pack. Однако, даже обладая этими инструментами, к сожалению, не всегда удается получить удовлетворительный результат. В данной заметке описываются способы решения проблемы с помощью метода настройки плана запроса по образцу. После анализа плана проблемного запроса его можно скорректировать с помощью хинтов. Для проверки результата изменений можно выполнить настроенный вариант запроса и по значениям статистики убедиться в том, что при изменении плана запроса проблема будет действительно решена. Особенно важным бывает применение правильного набора хинтов в тех случаях, когда изменение плана критичного запроса нужно выполнить срочно, не дожидаясь исправления кода приложений. Также при этом важна возможность быстрой отмены действия примененных хинтов. Небольшое отступление по поводу хинтов. С одной стороны, набор хинтов – это результат работы оптимизатора. Начиная с Oracle 10.2 список хинтов запроса доступен через вывод плана запроса с помощью пакета DBMS_XPLAN с заданием формата вывода ‘outline’:

select * from table(dbms_xplan.display_cursor(,,'outline'));

С другой стороны, хинты могут использоваться как входная управляющая информация непосредственно в тексте запроса либо, например, в функционале Stored Outlines. Использование хинтов в пользовательском коде может быть обосновано в том случае, когда прежде всего важна стабильность планов выполнения. Возможные недостатки при таком подходе – риск применения неполного списка хинтов, что может привести к изменению планов в другой среде, а также искусственное ограничение свободы действий оптимизатора, потенциально ведущее к невозможности использования других, быть может, более оптимальных планов. В целом, хинты в тексте запроса – это своеобразная смирительная рубашка для оптимизатора, которая иногда бывает необходима. Иногда возникает обратная задача, когда требуется отменить действие ненужных хинтов в тексте некоторых запросов. Получить полный набор хинтов, реализующий необходимый план, довольно просто – для этого достаточно иметь правильно работающую версию запроса либо правильную версию плана запроса в AWR (см. DBA_HIST_SQL_PLAN). Затем необходимо каким-то образом применить эти хинты к исходному проблемному запросу. Далее будет рассмотрено четыре способа, с помощью которых можно это осуществить.

Тестовые данные

Скрипт 0. Создание тестовых данных .

create user test identified by test; grant connect, resource to test; create table test.drop_tbl as select rownum n, 'txt'||rownum txt from dual connect by level 'test', tabname => 'drop_tbl'); end;

Отдельный пользователь, от имени которого будет происходить настройка, создается для того, чтобы показать, какие права нужны для каждого способа настройки. В приведенных ниже скриптах индексный доступ будет меняться на полное сканирование таблицы. Этого искусственного примера будет вполне достаточно для демонстрации возможностей каждого метода. Приведенные скрипты выполнялись в среде PL/SQL Developer 9.0.6, где SQL_ID запроса отличается от SQL_ID того же запроса в SQL*Plus за счет автоматически добавляемого пробела в конце текста запроса. При выполнении приведенных ниже скриптов в среде SQL*Plus или Oracle SQL Developer нужно изменить значения SQL_ID тестовых запросов. В частности, значение SQL_ID ‘4aay3kxc7rddg’ нужно поменять на ‘8acvts5s612z9’, а значение ‘g2u2fv1npc6q5’ — на ‘4uxxdz9wrjhnx’. Для каждого способа настройки приводится также скрипт отказа от настроенного плана. Описание принципов работы применяемых в каждом случае механизмов выходит за рамки этой заметки. И, разумеется, все эксперименты желательно проводить в тестовой среде.

Способ №1. Использование функционала SQL Plan Management. Создание SQL plan baseline.

Способ, который задействует функционал SQL Plan Management, может быть применен в Oracle 11 Enterprise Edition. План настроенного запроса-образца с помощью процедуры dbms_spm.load_plan_from_cursor_cache загружается в качестве плана для настраиваемого запроса, текст которого явно указывается в соответствующем параметре процедуры. При этом создается SQL plan baseline. В примере, представленном ниже, подразумевается, что параметр optimizer_capture_sql_plan_baselines имеет значение false (в этом случае SQL baseline для каждого запроса автоматически не создается). Необходимы следующие привилегии: Скрипт 1.1 Привилегии для выполнения скрипта с использованием SQL Baselines:

grant select on v_$sql to test; grant select on v_$sqlarea to test; grant administer sql management object to test; -- 11g grant create any sql profile to test; -- 10g grant drop any sql profile to test; grant alter any sql profile to test; grant select on dba_sql_plan_baselines to test;

Скрипт 1.2. Пример настройки с использованием SQL plan baseline:

-- conn test -- запрос для настройки (с индексным доступом) select * from drop_tbl where n = 1; -- sql_id = '4aay3kxc7rddg' ('8acvts5s612z9' для SQL Developer) -- запрос-образец select /*+ full(drop_tbl) */ * from drop_tbl where n = 1; -- sql_id = 'g2u2fv1npc6q5' ('4uxxdz9wrjhnx') -- Определяем параметры настраиваемого запроса и запроса-образца select sql_text, sql_id, plan_hash_value from v$sql where sql_text like 'select%drop_tbl%' order by last_active_time; -- загрузка плана настроенного запроса declare l_sql_id_src varchar2(13) :='g2u2fv1npc6q5'; -- sql_id образца l_plan_hash_value_src number := 1871027057; -- plan_hash_value образца l_sql_id_trg varchar2(13) :='4aay3kxc7rddg'; -- sql_id настраиваемого запроса l_sql_text_trg clob; l_res number; begin -- текст запроса для настройки select a.sql_fulltext into l_sql_text_trg from v$sqlarea a where a.sql_id = l_sql_id_trg; -- загрузка плана и создание SQL plan baseline l_res := dbms_spm.load_plans_from_cursor_cache( sql_id => l_sql_id_src, plan_hash_value => l_plan_hash_value_src, sql_text => l_sql_text_trg); dbms_output.put_line(l_res); end; -- проверка плана explain plan for select * from drop_tbl where n = 1; select * from table(dbms_xplan.display(null,null,'basic +note')); -- см. значение SQL plan baseline PLAN_TABLE_OUTPUT Plan hash value: 2545387769 ------------------------------------- | Id | Operation | Name | ------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| drop_tbl| ------------------------------------- Note ----- - SQL plan baseline "SQL_PLAN_3dm7hzprspdufe13b857f" used for this statement -- Проверка применения baseline к запросу (применяется со второго выполнения) select * from drop_tbl where n = 1; select sql_text, sql_id, plan_hash_value, sql_plan_baseline from v$sql where sql_text like 'select%drop_tbl%' order by last_active_time desc; -- Удаление baseline declare l_sql_plan_baseline varchar2(30) := 'SQL_PLAN_3dm7hzprspdufe13b857f'; l_sql_handle varchar2(30); l_res number; begin select sql_handle into l_sql_handle from dba_sql_plan_baselines b where plan_name = l_sql_plan_baseline; l_res := dbms_spm.drop_sql_plan_baseline(sql_handle => l_sql_handle); dbms_output.put_line(l_res); end;

В разделе Note отчета dbms_xplan.display указывается используемый SQL plan baseline. Название SQL plan baseline для запроса отражается также в поле V$SQL.SQL_PLAN_BASELINE. В Oracle 11 SE скрипты выполнятся, однако никакого эффекта на план запроса не окажут. Cледует иметь в виду, что для такого типа запросов, как multitable insert, невозможно создать SQL plan baseline, см., например: http://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/#comment-49382

Способ №2. Создание SQL патчей.

Еще один способ изменения планов запросов без правки исходного кода в Oracle 11g – использование функционала SQL Repair Advisor, а именно – создание SQL патчей, которые предназначены для изменения планов запросов в случае непредвиденных проблем. Принцип использования в нашем случае такой же как и ранее – получение набора хинтов настроенного запроса и создание на их основе SQL патча для проблемного запроса. Для создания SQL патча необходимы права на вызов пакета sys.dbms_sqldiag_internal. Скрипт 2.1. Привилегии для использования SQL патчей

grant execute on dbms_sqldiag_internal to test; grant execute on dbms_sqldiag to test; grant select on v_$sql_plan to test;

Скрипт 2.2 показывает, как можно автоматизировать создание SQL патча:

-- conn test declare l_sql_id_trg char(13) := '4aay3kxc7rddg'; -- запрос для настройки (v$sql.sql_id) l_sql_id_src char(13) := 'g2u2fv1npc6q5'; -- настроенный запрос - источник хинтов: v$sql.sql_id, l_child_number_src number :=0; -- v$sql.child_number l_hints varchar2(32767); l_sql_text clob; begin -- удаление SQL патча, если такой есть dbms_sqldiag.drop_sql_patch(name => 'patch_'||l_sql_id_trg, ignore => true); -- список хинтов настроенного запроса в одной строке select listagg( hint_val,' ') within group (order by rn) into l_hints from (select extractValue(value(d), '/hint') hint_val, rownum rn from (select other_xml from v$sql_plan where sql_id = l_sql_id_src and child_number = l_child_number_src and add_data, table(XMLSequence(XMLType(add_data.other_xml).extract('other_xml/outline_data/hint'))) d); -- текст настраиваемого запроса select sql_fulltext into l_sql_text from v$sql where sql_id = l_sql_id_trg and rownum = 1; -- создание SQL патча sys.dbms_sqldiag_internal.i_create_patch( sql_text => l_sql_text, hint_text => l_hints, name => 'patch_'||l_sql_id_trg); dbms_output.put_line('patch_'||l_sql_id_trg||' was created'); end;

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

-- Просмотр плана запроса с патчем explain plan for select * from drop_tbl where n = 1; select * from table(dbms_xplan.display(format=>'+outline')); Plan hash value: 1871027057 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 12 | 9 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DROP_TBL | 1 | 12 | 9 (0)| 00:00:01 | ------------------------------------------------------------------------------ Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "DROP_TBL"@"SEL$1") OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('11.2.0.2') OPTIMIZER_FEATURES_ENABLE('11.2.0.2') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("N"=1) Note ----- - SQL patch "patch_4aay3kxc7rddg" used for this statement Название SQL патча для запроса отражается в поле V$SQL.SQL_PATCH. Для удаления SQL патча можно выполнить скрипт: begin dbms_sqldiag.drop_sql_patch('patch_4aay3kxc7rddg'); end;

Следует заметить, что используемые в SQL патче хинты должны быть именно в системном виде. При попытке использовать в SQL патче хинт в виде /*+ full(drop_tbl) */ патч будет создан, но план запроса не изменится. SQL Repair Advisor является бесплатной опцией Oracle EE. Однако, функционалом создания SQL патчей удается воспользоваться и в других редакциях Oracle 11g, включая XE.

Способ №3. Настройка с использованием SQL profiles.

Возможность настройки запросов при помощи построения SQL profiles обеспечивается установленными Tuning& Diagnostic Pack (требуется версия Oracle 10 EE или 11 EE). С точки зрения рассматриваемой задачи интересным в использовании SQL profiles является тот факт, что профили можно создавать вручную через импорт. Недокументированная процедура dbms_sqltune.import_sql_profile обычно используется при импорте профиля, созданного ранее на основе списка специальных хинтов, предназначенных для корректировки статистики. Однако, этот же механизм может помочь включить в рассмотрение оптимизатором списка хинтов из плана настроенного запроса-образца. Также этот прием можно использовать при необходимости отмены действия хинтов в тексте проблемного запроса. Для этого достаточно использовать при создании профиля единственный хинт — IGNORE_OPTIM_EMBEDDED_HINTS. Скрипт 3.1. Привилегии для использования профилей.

grant administer sql management object to test; grant select on v_$sql to test; grant select on v_$sql_plan to test;

Скрипт 3.2. Пример использования настройки по образцу с помощью SQL profiles

-- исходный и настроенный запросы select * from drop_tbl where n = 1; -- sql_id: '4aay3kxc7rddg' select /*+ full(drop_tbl) */ * from drop_tbl where n = 1; -- sql_id: 'g2u2fv1npc6q5' -- определение параметров запросов select sql_text, sql_id, child_number from v$sql where sql_text like 'select%drop_tbl%'; -- загрузка хинтов через профиль declare -- SQL для настройки (v$sql.sql_id) l_sql_id_trg char(13) := '4aay3kxc7rddg'; -- Настроенный SQL – источник хинтов (v$sql.sql_id и v$sql.child_number) l_sql_id_src char(13) := 'g2u2fv1npc6q5'; l_child_number_src number :=0; l_hints sys.sqlprof_attr; l_sql_text clob; begin -- новые хинты в настроенном вручную варианте запроса select extractValue(value(d), '/hint') bulk collect into l_hints from (select other_xml from v$sql_plan where sql_id = l_sql_id_src and child_number = l_child_number_src and is not null*/) add_data, table(XMLSequence(XMLType(add_data.other_xml).extract('other_xml/outline_data/hint'))) d; -- исходный проблемный запрос select sql_fulltext into l_sql_text from v$sql where sql_id = l_sql_id_trg and rownum = 1; -- удаляем профиль, если такой был dbms_sqltune.drop_sql_profile('PROF_'||l_sql_id_trg,ignore => TRUE); -- создаем профиль на основе хинтов настроенного варианта dbms_sqltune.import_sql_profile(sql_text => l_sql_text ,profile => l_hints ,category => 'DEFAULT' ,name => 'PROF_'||l_sql_id_trg ,force_match => true); end; -- Убедимся в использовании SQL профиля explain plan for select * from drop_tbl where n = 1; select * from table(dbms_xplan.display(null,null,'basic +note')); Plan hash value: 2545387769 ------------------------------------- | Id | Operation | Name | ------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| DROP_tbl | ------------------------------------- Note ----- - SQL profile "PROF_4aay3kxc7rddg" used for this statement -- Удаление профиля begin dbms_sqltune.drop_sql_profile(name => 'PROF_4aay3kxc7rddg',ignore => true); end;

В разделе Note отчета указано имя используемого SQL profile. В примере используется категория профилей DEFAULT. При необходимости, можно изменить ее наименование. Категория профилей по умолчанию задается параметром sqltune_category:

alter session set sqltune_category = 'TEST'; alter system set sqltune_category = 'PROD' scope = both;

Название SQL профиля для запроса отражается также в поле V$SQL.SQL_PROFILE. Иногда бывает также необходимо отменить действие хинтов, входящих в текст запроса. Скрипт 3.3 Отмена действия хинтов

-- Запрос с хинтом, действие которого нужно отменить select /*+ full(drop_tbl) */ * from drop_tbl where n = 1; -- SQL_ID = 'g2u2fv1npc6q5' -- создание профиля с отменой хинтов declare -- SQL для настройки (v$sql.sql_id) l_sql_id_trg char(13) := 'g2u2fv1npc6q5'; l_hints sys.sqlprof_attr := sys.sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS'); l_sql_text clob; begin -- проблемный запрос select sql_fulltext into l_sql_text from v$sql where sql_id = l_sql_id_trg and rownum = 1; -- удаляем профиль, если такой был dbms_sqltune.drop_sql_profile('PROF_'||l_sql_id_trg,ignore => TRUE); -- создаем профиль на основе хинтов настроенного варианта dbms_sqltune.import_sql_profile(sql_text => l_sql_text ,profile => l_hints ,category => 'DEFAULT' ,name => 'PROF_'||l_sql_id_trg ,force_match => true); end; -- результат explain plan for select /*+ full(drop_tbl) */ * from drop_tbl where n = 1; select * from table(dbms_xplan.display(null,null,'basic +note')); Plan hash value: 3053117682 ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| DROP_TBL | | 2 | INDEX RANGE SCAN | I_DROP_TBL_ID | ----------------------------------------------------- Note ----- - SQL profile "PROF_g2u2fv1npc6q5" used for this statement -- удаление профиля begin dbms_sqltune.drop_sql_profile(name => 'PROF_g2u2fv1npc6q5',ignore => true); end;

Способ №4. Редактирование private outline и создание на его основе public outline.

1. Оптимизация плана проблемного запроса с помощью хинтов
2. Создание private outline для скорректированной версии запроса
3. Создание private outline для исходного запроса, план которого нужно изменить
4. Замена хинтов private outline настраиваемого запроса хинтами private outline настроенного запроса
5. Обновление (refresh) скорректированного private outline для исходного запроса.
6. Создание public outline с нужной категорией на основе измененного private outline для исходного запроса
7. Выключение использования private outline
8. Включение использования public outline с заданием категории outline
9. Проверка работы запроса с public outline

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

Скрипт 4.1. Предоставление привилегий для работы с outlines.

grant create any outline to test; grant drop any outline to test;

Скрипт 4.2 пошагово реализует изложенный выше план.

-- удаление одноименных аутлайнов (при необходимости) -- drop private outline p_outline_src_1; -- drop private outline p_outline_trg_2; -- drop public outline outline_drop_tbl; -- настраиваемый запрос select * from drop_tbl where n = 1; -- корректируем запрос хинтами, добиваясь нужного плана. select /*+ full(drop_tbl) */ * from drop_tbl where n = 1; -- создание айтлайна для скорректированного хинтами запроса create or replace private outline p_outline_trg_2 on select /*+ full(drop_tbl) */ * from drop_tbl where n = 1; -- создание аутлайна для исходного запроса create or replace private outline p_outline_src_1 on select * from drop_tbl where n = 1; -- проверим использование созданного аутлайна alter session set use_private_outlines = true; explain plan for select * from drop_tbl where n = 1; select * from table(dbms_xplan.display(null,null,'basic +note')); -- в конце будет замечание об использовании аутлайна -- наши запросы select sql_id, sql_text from v$sql where sql_text like 'select%drop_tbl%'; -- просмотр содержимого аутлайнов select * from ol$; select * from ol$hints; -- where ol_name = 'P_OUTLINE_SRC_1'; select * from ol$nodes; -- удаление исходных хинтов в первом запросе и прописывание новых, взятых из второго: delete from ol$hints where ol_name = 'P_OUTLINE_SRC_1'; delete from ol$nodes where ol_name = 'P_OUTLINE_SRC_1'; update ol$hints h set ol_name = 'P_OUTLINE_SRC_1' where ol_name = 'P_OUTLINE_TRG_2'; update ol$nodes h set ol_name = 'P_OUTLINE_SRC_1' where ol_name = 'P_OUTLINE_TRG_2'; -- удаляем второй аутлайн, который больше не нужен (необязательно) drop private outline p_outline_trg_2; -- просмотр содержимого аутлайнов после редактирования select * from ol$; select * from ol$hints; select * from ol$nodes; -- обновление скорректированного аутлайна begin dbms_outln_edit.refresh_private_outline('P_OUTLINE_SRC_1'); end; -- создание public outline на основе private outline create or replace public outline outline_drop_tbl from private p_outline_src_1 for category prod; -- выключение использования private аутлайнов в базе alter session set use_private_outlines = false; -- включаем использование public outline, категория prod alter session set use_stored_outlines = prod; -- В плане запроса должен быть FULL SCAN при включенном outline explain plan for select * from drop_tbl where n = 1; select * from table(dbms_xplan.display(null,null,'basic +note')); Plan hash value: 1871027057 -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| DROP_TBL | -------------------------------------- Note ----- - outline "OUTLINE_DROP_TBL" used for this statement -- план запроса с отключенными аутлайнами (используется индекс) alter session set use_stored_outlines = false; explain plan for select * from drop_tbl where n = 1; select * from table(dbms_xplan.display(null,null,'basic +note')); Plan hash value: 3053117682 ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| DROP_TBL | | 2 | INDEX RANGE SCAN | I_DROP_TBL_ID | -----------------------------------------------------

Для использования категории PROD на уровне всего инстанса необходимо выполнить:

alter system set use_stored_outlines = prod;

Таблицы, к которым ведут синонимы OL$, OL$HINTS, OL$NODES в Oracle 10 и Oracle 11 являются временными таблицами, и поскольку их содержимое очищается, необходимо выполнить весь скрипт в одной сессии.

Для удаления настроенного outline нужно выполнить команду:

drop public outline outline_drop_tbl;

Скрипт 4.3 объединяет шаги предыдущего скрипта в один PL/SQL скрипт:

declare i_sql_id_trg varchar2(13) := '4aay3kxc7rddg'; -- проблемный sql_id i_sql_id_src varchar2(13) := 'g2u2fv1npc6q5'; -- настроенный sql_id i_prod_category varchar2(30) := 'PROD'; -- категория outline l_sql_fulltext_trg clob; l_sql_fulltext_src clob; l_result number; function exec_sql(i_sql in clob, i_show_err in number := 0) return number is l_sql varchar2(32767) := substr(i_sql, 1, 32767); begin execute immediate l_sql; -- varchar2 for 10g, clob for 11g return 0; exception when others then if i_show_err = 0 then null; else dbms_output.put_line(sqlerrm); dbms_output.put_line(i_sql); end if; return -1; end; begin -- очистка outlines l_result := exec_sql('drop private outline pr_ol_'||i_sql_id_trg); l_result := exec_sql('drop private outline pr_ol_'||i_sql_id_src); l_result := exec_sql('drop outline ol_'||i_sql_id_trg); -- определение текстов SQL запросов select sql_fulltext into l_sql_fulltext_trg from v$sql where sql_id = i_sql_id_trg and rownum = 1; select sql_fulltext into l_sql_fulltext_src from v$sql where sql_id = i_sql_id_src and rownum = 1; -- создание outlines l_result := exec_sql('create or replace private outline pr_ol_'||i_sql_id_trg ||' on '|| l_sql_fulltext_trg,1); l_result := exec_sql('create or replace private outline pr_ol_'||i_sql_id_src ||' on '|| l_sql_fulltext_src,1); -- смена хинтов outlines delete from ol$hints where ol_name = 'PR_OL_'||upper(i_sql_id_trg); delete from ol$nodes where ol_name = 'PR_OL_'||upper(i_sql_id_src); update ol$hints h set ol_name = 'PR_OL_'||upper(i_sql_id_trg) where ol_name = 'PR_OL_'||upper(i_sql_id_src); update ol$nodes h set ol_name = 'PR_OL_'||upper(i_sql_id_trg) where ol_name = 'PR_OL_'||upper(i_sql_id_src); -- обновление данных outline dbms_outln_edit.refresh_private_outline('PR_OL_'||upper(i_sql_id_trg)); -- создание public outline на основе private outline l_result := exec_sql('create or replace public outline ol_'||upper(i_sql_id_trg)||' from private PR_OL_'||upper(i_sql_id_trg)||' for category '||i_prod_category,1); if l_result = 0 then dbms_output.put_line('Outline OL_'||upper(i_sql_id_trg)||' created for sql_id='||i_sql_id_trg); end if; end;

При проверке работы настроенного запроса нужно не забывать устанавливать категорию для outline, если она не установлена на уровне инстанса.

Большим достоинством этого метода является то, что он подходит для разных версий и редакций Oracle – 9, 10, 11, EE, SE, XE. Недостатком этого способа, помимо большей сложности скрипта, является то, что в 11g функционал Stored Outlines официально объявлен устаревшим. В документации outlines настоятельно рекомендуется заменить на SQL plan baselines.

Выводы

Как было показано, в разных редакциях разных версий Oracle всегда найдется способ исправить план неправильно выполняющегося запроса без изменения кода, для этого имеются достаточно широкие возможности. В перспективе хотелось бы видеть, чтобы функционал SQL Plan Baselines работал не только в Enterprise Edition. Объявление функционала Stored Outlines устаревшим логически требует его замены на функционал SQL Plan Management во всех редакциях Oracle. Это было бы самым удобным вариантом для пользователей.

Таблица 1. Рассмотренные способы настройки и версии Oraсle.

Используемый функционал	Редакции 9i	Редакции 10g	Редакции 11g	Замечания
SQL Plan Baseline	—	—	EE	Простота, стандартный функционал
SQL patch	—	—	EE, SE, XE	Использование недокументированного внутреннего пакета
SQL Profiles	—	EE + Tuning & Diagnostic Packs	EE + Tuning & Diagnostic Packs	Использование недокументированных возможностей
Stored Outlines	EE, SE	EE, SE	EE, SE, XE	Универсальность. При этом — формально устаревший функционал.

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

Ссылки по теме:

1. Oracle® Database Performance Tuning Guide. 15 Using SQL Plan Management: docs.oracle.com/cd/E11882_01/server.112/e16638/optplanmgmt.htm
2. Editing Stored Outlines in Oracle10g and Oracle11g Oracle Metalink, ID 726802.1
3. Заметка из блога команды разработки оптимизатора о SQL патчах: https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a
4. Интересное исследование функционала SQL патчей: http://orastory.wordpress.com/2012/03/06/sql-patch-i/ (см. также следующие части статьи).
5. Блог Kerry Osborne, сообщения, посвященные стабилизации планов запросов: http://kerryosborne.oracle-guy.com/category/oracle/plan-stability/
6. Скрипт настройки c помощью SQL Profile через образец плана в AWR: http://www.sql.ru/forum/actualthread.aspx?tid=983050&pg=2&mid=13466846#13466846

Понимаем план выполнения запроса: анализ, определения и ключевые свойства

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

Понимаем план выполнения запроса: анализ, определения и ключевые свойства обновлено: 22 сентября, 2023 автором: Научные Статьи.Ру

Помощь в написании работы

Введение

План выполнения запроса – это структурированное описание того, как система управления базами данных (СУБД) будет выполнять запрос к базе данных. Он содержит информацию о порядке выполнения операций, используемых индексах, объединениях и других деталях запроса.

Нужна помощь в написании работы?

Мы — биржа профессиональных авторов (преподавателей и доцентов вузов). Наша система гарантирует сдачу работы к сроку без плагиата. Правки вносим бесплатно.

Что такое план выполнения запроса

План выполнения запроса – это структурированное описание того, как система управления базами данных (СУБД) будет выполнять запрос к базе данных. Он представляет собой набор операций, которые СУБД будет выполнять для получения результата запроса.

План выполнения запроса является важным инструментом для оптимизации производительности запросов. Он позволяет анализировать, как СУБД будет выполнять запрос, и идентифицировать возможные узкие места или проблемы производительности.

План выполнения запроса может быть представлен в виде дерева, где каждая операция представляет собой узел, а связи между операциями – это дуги. Каждая операция выполняет определенную операцию над данными, такую как сканирование таблицы, объединение данных или сортировка.

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

Цель анализа плана выполнения запроса

Цель анализа плана выполнения запроса состоит в том, чтобы оценить эффективность выполнения запроса и определить, какие ресурсы будут использоваться при его выполнении. Анализ плана выполнения запроса позволяет оптимизировать запросы и улучшить производительность базы данных.

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

• Какие операции будут выполняться при выполнении запроса?
• Какие таблицы и индексы будут использоваться?
• Какие ресурсы (память, процессорное время, операции ввода-вывода) будут использоваться при выполнении запроса?
• Какие операции могут быть оптимизированы для улучшения производительности?

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

Как получить план выполнения запроса

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

Использование команды EXPLAIN

В большинстве СУБД (систем управления базами данных) существует команда EXPLAIN, которая позволяет получить план выполнения запроса. Например, в MySQL можно использовать команду EXPLAIN перед запросом, чтобы получить план выполнения:

EXPLAIN SELECT * FROM table_name;

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

Использование инструментов администрирования базы данных

Большинство СУБД предоставляют инструменты администрирования, которые позволяют анализировать и получать планы выполнения запросов. Например, в PostgreSQL можно использовать инструмент pgAdmin, который предоставляет графический интерфейс для анализа планов выполнения запросов.

Использование профилировщиков запросов

Некоторые СУБД предоставляют профилировщики запросов, которые позволяют анализировать и получать планы выполнения запросов в режиме реального времени. Например, в Oracle можно использовать инструмент SQL Developer, который предоставляет возможность профилирования запросов и анализа планов выполнения.

Использование инструментов трассировки запросов

Некоторые СУБД предоставляют инструменты трассировки запросов, которые позволяют получить подробную информацию о выполнении запроса, включая план выполнения. Например, в Microsoft SQL Server можно использовать инструмент SQL Server Profiler для трассировки запросов и анализа планов выполнения.

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

Структура плана выполнения запроса

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

Корневой узел

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

Операторы

Операторы представляют собой логические операции, которые выполняются для обработки данных в запросе. Например, оператор SELECT используется для выборки данных из таблицы, оператор JOIN используется для объединения данных из нескольких таблиц, а оператор GROUP BY используется для группировки данных по определенному столбцу.

Физические операции

Физические операции представляют собой конкретные действия, которые выполняются для обработки данных в запросе. Например, операция SCAN используется для сканирования всей таблицы, операция INDEX SEEK используется для поиска данных в индексе, а операция SORT используется для сортировки данных.

Статистика

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

Стоимость

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

Все эти компоненты вместе определяют структуру плана выполнения запроса и помогают понять, как запрос будет выполнен и какие ресурсы будут использованы. Анализ структуры плана выполнения запроса может помочь оптимизировать запросы и улучшить производительность базы данных.

Основные компоненты плана выполнения запроса

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

Операторы доступа к данным

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

Операторы фильтрации

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

Операторы соединения

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

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

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

Операторы агрегации

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

Операторы проекции

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

Все эти компоненты вместе определяют структуру плана выполнения запроса и помогают понять, как запрос будет выполнен и какие ресурсы будут использованы. Анализ структуры плана выполнения запроса может помочь оптимизировать запросы и улучшить производительность базы данных.

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

Анализ стоимости выполнения запроса – это процесс оценки затрат, необходимых для выполнения запроса в базе данных. Он позволяет определить, насколько эффективно будет выполнен запрос и какие ресурсы будут использованы.

Стоимость операций

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

Стоимость доступа к данным

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

Стоимость операций сортировки и группировки

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

Стоимость соединений и объединений

Если запрос включает операции соединения или объединения данных из разных таблиц, то стоимость выполнения запроса будет зависеть от объема данных, которые нужно объединить, а также от доступности индексов для выполнения соединения. Чем больше данных и чем меньше индексов, тем выше будет стоимость выполнения запроса.

Оценка стоимости выполнения запроса

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

В целом, анализ стоимости выполнения запроса позволяет оценить эффективность выполнения запроса и определить, какие ресурсы будут использованы. Это помогает оптимизировать запросы и улучшить производительность базы данных.

Оптимизация плана выполнения запроса

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

Шаги оптимизации плана выполнения запроса:

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

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

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

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

5. Использование подсказок: В некоторых случаях можно использовать подсказки для явного указания оптимального плана выполнения запроса. Подсказки позволяют контролировать поведение оптимизатора запросов и выбирать оптимальный план выполнения.

6. Мониторинг и настройка: После внесения изменений в план выполнения запроса необходимо мониторить его производительность и настраивать параметры базы данных, если это необходимо. Мониторинг позволяет выявить проблемы и внести дополнительные изменения для оптимизации запросов.

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

Примеры анализа плана выполнения запроса

Анализ плана выполнения запроса является важным шагом в оптимизации производительности базы данных. Рассмотрим несколько примеров анализа плана выполнения запроса:

Пример 1: Простой SELECT запрос

Предположим, у нас есть следующий запрос:

SELECT * FROM employees WHERE department = 'IT';

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

1. TABLE ACCESS FULL employees

В данном случае, план выполнения запроса показывает, что для выполнения запроса будет использован полный сканирование таблицы “employees”. Это означает, что все строки таблицы будут просмотрены для поиска строк, удовлетворяющих условию “department = ‘IT’”.

Анализ плана выполнения запроса позволяет нам понять, что данный запрос может быть неэффективным, особенно если таблица “employees” содержит большое количество строк. В таком случае, можно рассмотреть возможность добавления индекса на столбец “department”, чтобы ускорить выполнение запроса.

Пример 2: JOIN запрос

Рассмотрим следующий запрос с использованием оператора JOIN:

SELECT e.employee_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.salary > 50000;

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

1. TABLE ACCESS FULL employees 2. TABLE ACCESS FULL departments 3. HASH JOIN

В данном случае, план выполнения запроса показывает, что для выполнения запроса будет использовано полное сканирование таблицы “employees” и таблицы “departments”. Затем будет выполнено объединение (JOIN) результатов двух таблиц с использованием хэш-соединения.

Анализ плана выполнения запроса позволяет нам понять, что данный запрос может быть неэффективным, особенно если таблицы содержат большое количество строк. В таком случае, можно рассмотреть возможность добавления индексов на столбцы “department_id” в обеих таблицах, чтобы ускорить выполнение запроса.

Пример 3: Использование индекса

Рассмотрим следующий запрос с использованием индекса:

SELECT * FROM employees WHERE employee_id = 100;

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

1. INDEX UNIQUE SCAN employees_pk

В данном случае, план выполнения запроса показывает, что для выполнения запроса будет использован индекс “employees_pk” для поиска строки с указанным значением “employee_id”.

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

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

Сравнительная таблица планов выполнения запроса

Компонент	Описание	Пример
Операторы доступа	Определяют, какие данные будут использоваться в запросе и как они будут получены	Использование индекса для поиска данных в таблице
Операторы соединения	Определяют, какие таблицы будут объединены в запросе и как они будут связаны	Объединение таблиц по общему столбцу
Операторы фильтрации	Определяют условия, которым должны соответствовать данные, чтобы быть включенными в результат запроса	Выборка только тех строк, где значение столбца больше определенного значения
Операторы сортировки	Определяют порядок, в котором данные будут отображаться в результате запроса	Сортировка данных по возрастанию или убыванию значения столбца
Операторы группировки	Определяют, как данные будут группироваться в результате запроса	Группировка данных по значению определенного столбца
Операторы агрегации	Определяют, какие агрегатные функции будут применены к данным в результате запроса	Вычисление суммы или среднего значения столбца

Заключение

В данной лекции мы рассмотрели понятие плана выполнения запроса в базах данных. План выполнения запроса представляет собой оптимальную последовательность операций, которые выполняются для получения результата запроса. Анализ плана выполнения запроса позволяет определить эффективность выполнения запроса и выявить возможности для его оптимизации. Мы изучили структуру плана выполнения запроса и основные компоненты, такие как операторы доступа к данным, операторы объединения и операторы сортировки. Также мы рассмотрели анализ стоимости выполнения запроса и методы оптимизации плана выполнения запроса. Надеюсь, эта лекция помогла вам лучше понять планы выполнения запросов и их роль в оптимизации работы с базами данных.

Понимаем план выполнения запроса: анализ, определения и ключевые свойства обновлено: 22 сентября, 2023 автором: Научные Статьи.Ру

aguppi

Cost — стоимость, совокупная стоимость строки плана — это время, потраченное на одноблочные чтения, плюс время, потраченное на многоблочные чтения, плюс необходимое процессорное время, и всё это делённое на время, необходимое для выполнения одноблочного чтения.
Таким образом, стоимость — это суммарное прогнозируемое время выполнения оператора, выраженное в единицах времени выполнения одноблочного чтения.

Card — кардинальность, количество записей, которое будет сгенерировано каждой строкой плана.

Bytes — байт, общий объём данных, который будет сгенерирован каждой строкой плана.

План запроса (общая информация)

1й способ
В схеме учётной записи, выполняющей данную команду, должна быть таблица PLAN_TABLE, которая создаётся скриптом @$ORACLE_HOME/rdbms/admin/utlxplan.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql;

Чтобы узнать в командной строке по какому плану база будет выполнять запрос:
select * from test_table where name=’test’;

Нужно:
sql> explain plan for select * from test_table where name=’test’;

Посмотреть план:
sql> select * from table(DBMS_XPLAN.DISPLAY);

Так же можно посмотреть план из plan_table с помощью sql запроса.
Только необходимо задать STATEMENT_ID, что бы как то различать планы если их несколько в таблице plan_table:

sql> explain plan set STATEMENT_ID=’test_1′ for select * from test_table where name=’test’;

— посмотреть план:
SELECT LPAD(‘ ‘, 2 * (level — 1)) ||
DECODE (level,1,NULL,level-1 || ‘.’ || pt.position || ‘ ‘) ||
INITCAP(pt.operation) ||
DECODE(pt.options,NULL,»,’ (‘ || INITCAP(pt.options) || ‘)’) plan,
pt.object_name,
pt.object_type,
pt.bytes,
pt.cost,
pt.partition_start,
pt.partition_stop
FROM plan_table pt
START WITH pt.id = 0
AND pt.statement_id = ‘test_1’
CONNECT BY PRIOR pt.id = pt.parent_id
AND pt.statement_id = ‘test_1’;

2й способ (в sqlplus)
Чтобы посмотреть план выполнения из sqlplus нужно установить autotrace

sql> set autotrace on
sql> set autotrace off

Ещё возможны опции установки:

SET AUTOTRACE ON EXPLAIN
The AUTOTRACE report shows only the optimizer execution path.

SET AUTOTRACE ON STATISTICS
The AUTOTRACE report shows only the SQL statement execution statistics.

SET AUTOTRACE TRACEONLY
Similar to SET AUTOTRACE ON, but suppresses the printing of the user’s query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.

План запроса (дополнительно)

Планы запросов можно вытянуть из V$SQL_PLAN.
V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.

Вытянуть план запроса по его SQL_ID.
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(‘&SQL_ID’, 0 ));

— например
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(‘ck4na28rqh00d’, 0 ));

— нарисует:
SQL_ID ck4na28rqh00d, child number 0
————————————-
SELECT count(*) FROM TSS_TRANSACTION WHERE entry_dt < TO_DATE('20120830
07:57:08′, ‘YYYYMMDD hh24:mi:ss’)

Plan hash value: 804789899