Что такое план запроса в sql oracle
Перейти к содержимому

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

Анализ запроса SQL

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

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

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

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

  • About Oracle
  • Legal Notices
  • Terms of Use
  • Your Privacy Rights

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

Исполнение любого SQL предложения в Oracle извлекает так называемый «план исполнения». Этот план исполнения запроса является описанием того, как Oracle будет осуществялть выборку данных, согласно исполняемому SQL предложению. План представляет собой дерево, которое содержит порядок шагов и связь между ними.

Базовые правила плана исполнения:

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

Приведем пример плана исполнения:

Если следовать перечисленным выше правилам, то:

  • Операция 0 — корень дерева, и у нее один потомок, операция 1
  • У операции 1 два потомка — операции 2 и 4
  • У операции 2 один потомок — операция 3

Если представить это графически, то получиться примерно так:

Если прочитать это дерево, то получим следующее, в порядке выполнения: Для выполнения операции 1, необходимо выполнить операции 2 и 4. Операция 2 выполняется первой. Во время выполнения операции 2, необходимо выполнить операцию 3. Для выполнения операции 4, необходимо выполнить операцию 2.

  • Операция 3 получает доступ к таблице DEPT, используя INDEX UNIQUE SCAN и предоставляет ROWID операции 2
  • Операция 2 возвращает все строки из таблицы DEPT для операции 1
  • Операция 1 выполняет операцию 4, для каждой строки, которую вернула операция 2
  • Операция 4 выполняет полностью просматривает таблицу (TABLE ACCESS FULL) и применяет фильтр E.DEPNO=10 и возвращает строки для операции 1
  • Операция 1 возвращает финальный результат в операцию 0

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

  • Каталог программ Oracle
  • Задать вопрос ONLine по покупке программ в интернет магазине ITShop

Oracle mechanics

При анализе конкретного SQL запроса полезно выяснить, какой план выполнения в действительности используется Oracle, т.к. результат выполнения команды explain plan (в т.ч. при установке опции SQL*Plus AUTOTRACE ON EXPLAIN — см., например, EXPLAIN PLAN Restrictions), даёт только оценку возможного плана выполнения и может существенно отличаться от плана запроса, который будет в действительности использован при выполнении запроса

1. Из обзора V$SQL_PLAN, например, так:

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

План выполнения уникально определяется комбинациями значений (HASH_VALUE, ADDRESS, CHILD_NUMBER) либо (SQL_ID, CHILD_NUMBER) из V$SQL
QBLOCK_NAME – название блока запроса, используется в некоторых хинтах оптимизатором в outline section плана выполнения

2. Используя пакет DBMS_XPLAN (источником данных о плане является тот же обзор V$SQL_PLAN) сразу после выполнения запроса:

SQL> select sysdate from dual; . SQL> select * from table(dbms_xplan.display_cursor(null,null,'BASIC LAST')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------- Plan hash value: 1388734953 --------------------------------- | Id | Operation | Name | --------------------------------- | 0 | SELECT STATEMENT | | | 1 | FAST DUAL | | ---------------------------------

, либо по значениям (v$sql.sql_id, v$sql.child_number):

SELECT * FROM TABLE(dbms_xplan.display_cursor(':sql_id', ':child_number', 'ADVANCED ALLSTATS LAST'));

Функции пакета не показывают запросов, в тексте которых содержится выражение ‘%DBMS_XPLAN%’ в любом регистре — см. Не работает DBMS_XPLAN.DISPLAY_CURSOR

3. из трейс файлов (event 10053, 10132):

alter session set events '10053 trace name context forever'; SELECT/*+ gather_plan_statistics*/ . ; alter session set events '10053 trace name context off';

4. Из репозитория AWR:

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.temp_space, pt.access_predicates, pt.filter_predicates, pt.qblock_name FROM (select * from dba_hist_sql_plan where sql_id = ':sql_id' and plan_hash_value = :plan_hash_value) pt CONNECT BY PRIOR pt.id = pt.parent_id START WITH pt.id = 0

— вплоть до 11.2 включительно значения access_predicates и filter_predicates в dba_hist_sql_plan, к сожалению, не сохраняются — Bug 7493519: ACCESS_PREDICATES AND FILTER_PREDICATES MISSING IN DBA_SQL_PLAN_HIST

5. Начиная с 11.2, где добавлено поле PLAN_PARENT_ID, из обзора V$SQL_PLAN_MONITOR, если запрос мониторится:

SELECT lpad(' ', 2 * level) || pt.plan_operation || ' ' || pt.plan_options || ' ' || pt.plan_object_name "Query Plan", pt.plan_cost, pt.plan_cardinality, pt.plan_bytes, pt.plan_cpu_cost, pt.plan_io_cost, pt.plan_temp_space, pt.starts, pt.output_rows, pt.workarea_mem, pt.workarea_tempseg, pt.physical_read_requests, pt.physical_write_requests FROM (select * from v$sql_plan_monitor pt where sql_id = ':sql_id' -- and sql_plan_hash_value = :sql_plan_hash_value -- and sid = :sid -- and sql_exec_id = :sql_exec_id and status = 'EXECUTING') pt CONNECT BY PRIOR pt.plan_line_id = pt.plan_parent_id START WITH pt.plan_line_id = 0

6. Начиная с 11.2 можно получить трейс оптимизатора с планом выполнения с помощью процедуры dbms_sqldiag.dump_trace

-------------------------------- dump_trace --------------------------------- -- NAME: -- dump_trace - Dump Optimizer Trace -- -- DESCRIPTION: -- This procedure dumps the optimizer or compiler trace for a give SQL -- statement identified by a SQL ID and an optional child number. -- -- PARAMETERS: -- p_sql_id (IN) - identifier of the statement in the cursor cache -- p_child_number (IN) - child number -- p_component (IN) - component name -- Valid values are Optimizer and Compiler -- The default is Optimizer -- p_file_id (IN) - [trace] file identifier

— однако, поскольку в этом случае для формирования трейса будет выполнен повторный разбор (hard parse) модифицированного комментарием запроса (с несовпадающим sql_id), сформированный трейс (и план) могут отличаться от актуальных

SQL> select * from (select sql_id, child_number from V$SQL where is_resolved_adaptive_plan = 'Y') where rownum < 2; SQL_ID CHILD_NUMBER ------------- ------------ 34m67p601800n 0 SQL>WITH display_map AS 2 (SELECT X.* 3 FROM v$sql_plan, 4 XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml ) COLUMNS 5 op NUMBER PATH '@op', -- operation 6 dis NUMBER PATH '@dis', -- display 7 par NUMBER PATH '@par', -- parent 8 prt NUMBER PATH '@prt', -- ? 9 dep NUMBER PATH '@dep', -- depth 10 skp NUMBER PATH '@skp' ) -- skip 11 AS X 12 WHERE sql_id = '&&sql_id' 13 AND child_number = &&sql_child 14 AND other_xml IS NOT NULL 15 ) 16 SELECT 17 -- new ID, depth, parent etc from display_map 18 NVL(m.dis, 0) AS new_id, 19 m.par AS new_parent, 20 m.dep AS new_depth, 21 -- plan formatting, as usual 22 lpad(' ',m.dep*1,' ') || sp.operation || ' ' || sp.OPTIONS AS operation, 23 sp.object#, 24 sp.object_name, 25 sp.object_alias, 26 sp.object_type 27 FROM v$sql_plan sp 28 LEFT OUTER JOIN display_map m 29 ON (sp.id = m.op) 30 WHERE sp.sql_Id = '&&sql_id' 31 AND sp.child_number = &&sql_child 32 AND NVL(m.skp,0) <> 1 33 ORDER BY NVL(dis,0) 34 / &sql_id: 34m67p601800n &sql_child: 0 NEW_ID NEW_PARENT NEW_DEPTH OPERATION OBJECT# OBJECT_NAME OBJECT_ALIAS OBJECT_TYPE ------ ---------- ---------- ------------------------ ---------- ------------ -------------------- --------------- 0 SELECT STATEMENT 1 0 1 SORT AGGREGATE 2 1 2 NESTED LOOPS 3 2 3 INDEX RANGE SCAN 2259404 I_OBJ1 OBJ_TAB#4@innerQuery INDEX (UNIQUE) 4 2 3 TABLE ACCESS CLUSTER 2259418 TAB$ TAB#3@innerQuery CLUSTER 5 4 4 INDEX UNIQUE SCAN 2259410 I_OBJ# TAB#3@innerQuery INDEX (CLUSTER)
Plan hash value

определяется набором и последовательностью операций с объектами бд (определяемыми только по имени, без указания схемы) при выполнения запроса, например:

11.2.0.3.@SCOTT SQL> select /*+ full(EMP)*/ * from emp where deptno = 20 or deptno = 21; Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 --*** -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7 | 266 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 7 | 266 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DEPTNO"=20 OR "DEPTNO"=21) -- первоначальное условие SQL> select /*+ full(EMP)*/ * from emp where deptno = 20; Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -- *** -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 190 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 5 | 190 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DEPTNO"=20) -- условие изменилось SQL> -- тот же запрос к таблице пользователя SYS SQL> select /*+ full(EMP)*/ * from SYS.emp where deptno = 20; Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -- *** -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 435 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 5 | 435 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DEPTNO"=20)

Условия (filter / access predicates), расчётные величины (стоимость, объём данных, время выполнения), информация о партициях, принадлежность объектов на значение Plan hash value не влияют — см. Randolf Geist. PLAN_HASH_VALUE — How equal (and stable?) are your execution plans — part 1

Значения связанных переменных и констант, используемые в запросе

Кроме SQL трейса (event 10046, level 4/12), можно найти в обзоре v$sql_bind_capture, например так:

select c.child_address, c.address, c.name, c.DATATYPE_STRING, DECODE(c.WAS_CAPTURED, 'YES', c.value_string, '['||c.value_string||']:NOT_CAPTURED') bind_var from v$sql a, v$sql_bind_capture c where c.child_address=a.child_address and c.hash_value = a.HASH_VALUE and a.SQL_ID = ':my_sql_id';

Обзор v$sql_bind_capture содержит «… по одному из значений связанных переменных (bind values used for the bind variable), использованных во время одного из последних выполнений соответствующего (идентифицируемого по child_address, hash_value) SQL запроса. Значения связанных переменных не всегда попадают в этот обзор*. В обзор попадают значения связанных переменных только если связанные переменные имеют простой тип (LONG, LOB, и ADT типы данных исключаются) и связанные переменные используются во фразах WHERE или HAVING запроса.»

*) Не при всяком/каждом выполнении запроса значения связанных переменных попадают в этот обзор. Алгоритм отбора неясен, однако в некоторых известных случаях в обзор v$sql_bind_capture попадали значения связанных переменных, при которых определённый запрос выполнялся очень быстро, в то время как значения, при которых запрос был медленным не попадали. В то же время отчёты ADDM показывали, что большая часть выполнений этого запроса происходит медленно. Найти «медленные» значения помогла трассировка со стороны приложения

Начиная с версии Oracle 11g R2 значения связанных переменных для запросов, попадающих под Oracle Real-Time SQL Monitoring (параллельное выполнение, либо потребление >= 5 секунд CPU time или I/O time, либо использование подсказки /*+ MONITOR*/) можно найти в поле BINDS_XML обзора V$SQL_MONITOR:

select * from xmltable ('/binds/bind' passing (select xmltype(binds_xml) as xmlval from v$sql_monitor where binds_xml is not null and key = :key and sql_id = ':sql_id') columns "Name" varchar2(30) path '@name', "Position" varchar2(10) path '@pos', "Type" varchar2(30) path '@dtystr', "MaxLength" varchar2(30) path '@maxlen', "Value" varchar2(1000) PATH '/bind') /
Подсказки плана выполнения, используемые CBO при выполнении запроса (outline section)
select substr(extractvalue(value(d), '/hint'), 1, 100) as outline_hints from xmltable('/*/outline_data/hint' passing (select xmltype(other_xml) as xmlval from v$sql_plan where sql_id = ':sql_id' and child_number = :child_number and plan_hash_value = :plan_hash_value and other_xml is not null) ) d /
Методы Oracle для модификации и фиксации существующих планов выполнения
Илья Деев. Четыре способа корректировки планов запросов по образцу без изменения кода
Видимость элементов SPM (SQL Profiles, SQL Plan Baselines, SQL Patches) в контейнерной архитектуре CDB/PDB
-- connect to PDB pdb1 SQL> @inst INST_ID INSTANCE_NAME SERVICE CON_NAME HOST_NAME VERSION PLATFORM_NAME DATABASE_STATUS ------- ------------- -------- --------- --------- ---------- -------------------- --------------- 1* CDBTEST1 TEST PDB1 db1.host 19.0.0.0.0 Linux x86 64-bit ACTIVE 2 CDBTEST2 TEST PDB1 db2.host 19.0.0.0.0 Linux x86 64-bit ACTIVE SQL> @spm_check4sql_id a4f1j81444dpa no rows selected. -- connect to CDB$ROOT SQL> @inst INST_ID INSTANCE_NAME SERVICE CON_NAME HOST_NAME VERSION PLATFORM_NAME DATABASE_STATUS ------- ------------- -------- --------- --------- ---------- -------------------- --------------- 1 CDBTEST1 CDBTEST CDB$ROOT db1.host 19.0.0.0.0 Linux x86 64-bit ACTIVE 2* CDBTEST2 CDBTEST CDB$ROOT db2.host 19.0.0.0.0 Linux x86 64-bit ACTIVE SQL> @spm_check4sql_id a4f1j81444dpa SPM_TYPE SQL_HANDLE PATCH_NAME ORIGIN VERSION CREATED LAST_MODIF ----------------- --------------------- ------------- -------------- ---------- ------------------- ---------- SQL Profile SQL_ee42334da1925580 ADMIN-18414 MANUAL-LOAD 19.0.0.0.0 30.11.2020 03:54:57 30.11.2020 --SHARING METADATA only SQL> select distinct object_type, sharing from cdb_objects where object_name in ('SQLOBJ$DATA','SQLOBJ$PLAN','SQLOBJ$'); OBJECT_TYPE SHARING ----------------------- ------------------ TABLE METADATA LINK
SQL Profiles

«В отличие от Stored Outlines, подсказки, используемые в SQL Profiles не пытаются диктовать механизм испольнения запроса (операции доступа к данным) напрямую. Вместо этого они устанавливают арифметические корректирующие коэффициенты для оптимизатора…»

т.е. SQL Profiles не пытается влиять на план выполнения запроса «напрямую», подсказывая способы доступа к объектам БД (использовать ли индекс — index range scan или FULL TABLE SCAN). Этот инструмент с помощью специальных хинтов (вычисленных на основании статистики реального выполнения запроса, используя технологию cardinality feedback) предоставляет CBO коэффициенты для расчётов и выбора «откорректированного жизнью» плана выполнения. Коэффициенты эти, конечно же, со временем могут стать неактуальными и может потребоваться повторное выполнение процедур подготовки SQL Profiles

DECLARE my_sql_id VARCHAR2(13) := :sqlid; my_task_name VARCHAR2(30); BEGIN begin DBMS_SQLTUNE.DROP_TUNING_TASK(my_sql_id); exception when others then NULL; end; my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => my_sql_id, -- sql_text => 'SELECT . WHERE order_id = :order_id', bind_list => sql_binds(anydata.ConvertNumber(124), anydata.ConvertDate(to_date('07/28/2016 18', 'mm/dd/yyyy hh24')), anydata.ConvertDate(to_date('07/28/2016 21', 'mm/dd/yyyy hh24'))), scope => 'COMPREHENSIVE', time_limit => 600, task_name => my_sql_id, description => 'SQL analysis for SQL_ID=' || my_sql_id); DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => my_task_name); END;
DECLARE my_sql_id VARCHAR2(30) := :sql_id; my_task_name VARCHAR2(30); BEGIN begin DBMS_SQLTUNE.DROP_TUNING_TASK(my_sql_id); exception when others then NULL; end; my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_id => my_sql_id, --sql_text => 'select . ', --if SQL not in shared pool-- scope => 'COMPREHENSIVE', time_limit => 600, task_name => my_sql_id, description => 'SQL analysis for SQL_ID=' || my_sql_id); DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => my_task_name); END; SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( :sql_id ) FROM DUAL; . ------------------------------------------------------------------------------- There are no recommendations to improve the statement. ------------------------------------------------------------------------------- begin dbms_sqltune.accept_sql_profile(task_name => :sql_id, replace => TRUE, force_match => true); end;

Перечень уже применяемых в профилях хинтов можно увидеть в документально неупоминаемом обзоре SYS.DBMSHSXP_SQL_PROFILE_ATTR:

select attr_number, attr_value as cbo_hint from DBMSHSXP_SQL_PROFILE_ATTR where profile_name = :SQL_PROFILE_NAME;

Перечень рекомендованных хинтов после выполнения DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => :sql_tuning_task_name ); — из обсуждения в блоге J .Lewis’а:

select rat.attr1 from sys.wri$_adv_tasks tsk, sys.wri$_adv_rationale rat where tsk.name = :sql_tuning_task_name and rat.task_id = tsk.id;

В 12c формат хранения рекомендованных хинтов после выполнения DBMS_SQLTUNE.EXECUTE_TUNING_TASK изменился:

19.10 @ SQL> select distinct trim(dbms_lob.substr(rat.attr5, 512)) as hints 2 from sys.wri$_adv_tasks tsk, sys.wri$_adv_rationale rat 3 where tsk.name = '&sql_id' 4 and tsk.advisor_id = 4 5 and rat.task_id = tsk.id 6 and rat.type = 'RECOMMENDATION' 7 / HINTS --------------------------------------------------------------------------- . OPTIMIZER_FEATURES_ENABLE('10.2.0.5') OPT_ESTIMATE(@"SEL$3E895F4D", TABLE, "OU3"@"SEL$2", SCALE_ROWS=3.863396857) IGNORE_OPTIM_EMBEDDED_HINTS OPT_ESTIMATE(@"SEL$3E895F4D", INDEX_SKIP_SCAN, "OU3"@"SEL$2", "XX_ORGANIZATION_INFORM_X1", SCALE_ROWS=2.034735684) OPT_ESTIMATE(@"SEL$3E895F4D", NLJ_INDEX_FILTER, "BSV"@"SEL$10", ("GL"@"SEL$9", "OU1"@"SEL$7", "B"@"SEL$6", "T"@"SEL$6", "O4"@"SEL$4", "O3"@"SEL$4", "O2"@"SEL$4", "OTL"@"SEL$4", "O"@"SEL$4", "OU3"@"SEL$2", "TU"@"SEL$2", "H"@"SEL$1", "L"@"SEL$1", "LL"@"SEL$1", "D"@"SEL$1", "A"@"SEL$1"), "GL_LEDGER_NORM_SEG_VALS_N1", SCALE_ROWS=0.2353031369) OPT_ESTIMATE(@"SEL$3E895F4D", INDEX_SCAN, "O4"@"SEL$4", "ORGANIZATION_INFORMATIO_IX1", SCALE_ROWS=8.735670601)

, так же в 12c для получения списка подсказок созданного SQL Profile используется:

SELECT extractvalue(value(d), '/hint/text()') as outline_hints from sys.DBMSHSXP_SQL_PROFILE_ATTR CROSS JOIN XMLTABLE('/outline_data/hint' PASSING xmltype(comp_data)) d where profile_name = '&sql_profile_name'
12.1.0.2.@ SQL> SELECT extractvalue(value(d), '/hint/text()') as outline_hints 2 from sys.sqlobj$data od 3 join sys.sqlobj$ o using (signature, category, obj_type, plan_id) 4 CROSS JOIN XMLTABLE('/outline_data/hint' PASSING xmltype(comp_data)) d 5 where obj_type = 1 6 and o.name = '&sql_profile_name' 7 / Enter value for sql_profile_name: SYS_SQLPROF_01517b91a8c9000c old 6: and o.name = '&sql_profile_name' new 6: and o.name = 'SYS_SQLPROF_01517b91a8c9000c' OUTLINE_HINTS ------------------------------------------------------------------------------------------- .

показывает хинты типа OPT_ESTIMATE(@»SEL$2″, TABLE, «C»@»SEL$2», SCALE_ROWS=106.8828149), что значит multiply row count by 106.88… для таблицы «C» из блока запроса ( QBLOCK_NAME) @»SEL$2″ — значения этих синонимов можно найти в файлах 10053 trace или в обзоре V$SQL_PLAN

«It is important to note that the SQL Profile does not freeze the execution plan of a SQL statement, as done by stored outlines. As tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile. The information stored in it continues to be relevant even as the data distribution or access path of the corresponding statement change. However, over a long period of time, its content can become outdated and would have to be regenerated. This can be done by running Automatic SQL Tuning again on the same statement to regenerate the SQL Profile.»

Наличие SQL Profile (в отличие от SQL Patch) не препятствует выполнению DBMS_SQL Tuning Task, при этом рекомендуется выполнение dbms_sqltune.accept_sql_profile(replace => TRUE), в рез-тате содержимое старого SQL Profile 32df7yqy71mj0_3210411775 (созданного для фиксации конкретного плана) подменяется на привычные OPT_ESTIMATE:

SQL> @spm4 32df7yqy71mj0 SPM_TYPE SQL_HANDLE PATCH_NAME ORIGIN VERSION CREATED LAST_MODIFIED LAST_EXECUTED LAST_VERIFIED ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE SPM_SIGNATURE SQL_EXACT_SIGNATURE SQL_FORCE_SIGNATURE DBMS_SQLTUNE_SIGNATURE CATEGORY CATEGORY_AUX ----------------- ------------------------------ ------------------------------ -------------- ---------- ------------------- ------------------- ------------------- ------------------- ------- -------- ----- ---------- --------- --------------------- --------------------- --------------------- ---------------------- ------------------------------ ------------------------------ SQL Profile SQL_004c4ca3efeaf3d9 32df7yqy71mj0_3210411775 MANUAL-LOAD 19.0.0.0.0 30.03.2018 12:48:03 08.08.2023 19:21:58 YES NO NO YES NO 21476365218542553 21476365218542553 21476365218542553 21476365218542553 DEFAULT DEFAULT SQL> @sql_profile_hints 32df7yqy71mj0_3210411775 HINT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- OPT_ESTIMATE(@"SEL$3BE7D86C", TABLE, "WUR"@"SEL$6", SCALE_ROWS=7.680387496) OPT_ESTIMATE(@"SEL$3BE7D86C", JOIN, ("WUR"@"SEL$6", "VW_NSO_1"@"SEL$3BE7D86C"), SCALE_ROWS=0.3740765821) . COLUMN_STATS("XTR"."XTR_PRO_PARAM", "PARAM_NAME", scale, length=19 distinct=30 nulls=0) TABLE_STATS("XTR"."XTR_PRO_PARAM", scale, blocks=5 rows=30) 48 rows selected
Хранимые шаблоны / stored outlines

Доступны, начиная с версии Oracle 9i

CREATE OR REPLACE OUTLINE OUTLINE_GOOD ON SELECT /*+ index(A) */ * FROM TABLE A >>>>>>>>>>>> This is the query here with the outlines which produces good/acceptable execution plan WHERE OR REPLACE OUTLINE OUTLINE_BAD ON SELECT * FROM TABLE A WHERE OUTLN.OL$HINTS SET OL_NAME = DECODE(OL_NAME, 'OUTLINE_BAD', 'OUTLINE_GOOD', 'OUTLINE_GOOD', 'OUTLINE_BAD') WHERE OL_NAME IN ('OUTLINE_BAD', 'OUTLINE_GOOD'); COMMIT;

Параметр управления use_stored_oulines | use_private_outlines не является инилизационным. При постоянном применении Oracle рекомендует устанавливать этот «параметр» системным триггером after startup on database — How to Enable USE_STORED_OUTLINES Permanently [ID 560331.1] (либо пользовательским after logon on database). Проверить текущее состояние переменной на уровне системы/сессии можно утилитой oradebug — How to check the category of a Stored Outline:

SQL> oradebug dumpvar sga sgauso SQL> oradebug dumpvar uga ugauso
SQL PLAN BASELINES / SQL plan management (SPM)

Рекомендованный к применению метод управления планами выполнения, начиная с Oracle 11g

Идея: план выполнения из library cache, sql set или промежуточной таблицы (stage table) в составе набора подсказок и параметров оптимизатора сохраняется в системных таблицах (sys.sqlobj$data) для дальнейшего использования оптимизатором при построении плана очередного выполнения запроса

baseline = набор подсказок, применяемых на определённом, не первом этапе генерации плана выполнения.

SQL> grant ADMINISTER SQL MANAGEMENT OBJECT to :username; Grant succeeded.

Пример неавтоматического создания baseline для запроса, находящегося в shared pool SGA:

11.2.0.3.@SQL> SELECT USERENV('CLIENT_INFO') FROM SYS.DUAL; USERENV('CLIENT_INFO') ---------------------- 1 row selected. 11.2.0.3.@SQL> select child_number, sql_id, plan_hash_value from v$sql where sql_text like 'SELECT USERENV(''CLIENT_INFO'') FROM SYS.DUAL%'; CHILD_NUMBER SQL_ID PLAN_HASH_VALUE ------------ ------------- --------------- 0 9qs3v6bgb0xcu 1388734953 1 row selected. 11.2.0.3.@SQL> declare res number; 2 begin 3 res := dbms_spm.load_plans_from_cursor_cache(sql_id => '9qs3v6bgb0xcu', plan_hash_value => '1388734953' ); 4 end; 5 / PL/SQL procedure successfully completed. 11.2.0.3.@SQL> select * from dba_sql_plan_baselines where sql_text like '%CLIENT_INFO%'; SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION CREATED ENA ACC FIX REP AUT OPTIMIZER_COST MODULE EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES END_OF_FETCH_COUNT --------------------- --------------------------------------------- ------------------------------ ----------- ---------- ---------- --- --- --- --- --- -------------- ------------ ---------- ------------ -------- ----------- ---------- ------------- -------------- ------- ------------------ SQL_f666908ec8ea1293 SELECT USERENV('CLIENT_INFO') FROM SYS.DUAL SQL_PLAN_gctnhjv4fn4nm125daea2 MANUAL-LOAD 11.2.0.3.0 13-MAR-12 YES YES NO YES YES 2 sqlplus.exe 1 1039 1000 0 0 0 1 1 1 1 row selected.

Созданный baseline, как и sql patsh (ещё один элемент технологии SPM — Sql Plan Management) будут применяться к запросам после нормализации SQL текста: исключения пробелов, переводов строки и нормализации регистра кроме регистра текстовых констант (literals), например, только что созданный baseline успешно применяется к запросу отличным sql_id:

11.2.0.3.@SQL> select userenv ( 'CLIENT_INFO' ) 2 from 3 sys.dual; PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID cgzygkgjhdz7a, child number 0 ------------------------------------- . Plan hash value: 1388734953 . Note ----- - SQL plan baseline SQL_PLAN_gctnhjv4fn4nm125daea2 used for this statement

Типичные операции с baseline:

declare n pls_integer; baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR; begin -- -- Создание Baseline из плана / курсора, находящегося в Shared Pool -- n := dbms_spm.load_plans_from_cursor_cache(sql_id => 'c21dh0yfus13y', plan_hash_value => 733896097); -- -- Создание нового (дополнительного) Baseline для запроса / существующего Baseline sql_handle='SQL_1b55d727681aae5f' -- и курсора sql_id='998cduxz1rdqy', plan_hash_value=530064972, например для фиксации другого плана с помощью подсказок -- n := dbms_spm.load_plans_from_cursor_cache(sql_id => '998cduxz1rdqy', plan_hash_value => 530064972, sql_handle => 'SQL_1b55d727681aae5f'); -- -- создание Baseline из исторического плана / курсора, находящегося в AWR -- DBMS_SQLTUNE.CREATE_SQLSET('SQLSET_c21dh0yfus13y'); open baseline_ref_cursor for select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( begin_snap => 43820, -- начальный снапшот для поиска end_snap => 43832, -- финальный снапшот basic_filter => 'sql_id='||CHR(39)||'c21dh0yfus13y'||CHR(39)||' and plan_hash_value = 733896097', -- условие выбора attribute_list => 'ALL')) p; DBMS_SQLTUNE.LOAD_SQLSET('SQLSET_c21dh0yfus13y', baseline_ref_cursor); close baseline_ref_cursor; n := dbms_spm.load_plans_from_sqlset( sqlset_name => 'SQLSET_c21dh0yfus13y', sqlset_owner => USER, fixed => 'YES', enabled => 'YES'); DBMS_SQLTUNE.drop_sqlset('SQLSET_c21dh0yfus13y'); -- -- перемещение Baseline с использованием промежуточной таблицы -- DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'SPM_STAGE1', tablespace_name => 'USERS'); n := DBMS_SPM.PACK_STGTAB_BASELINE(table_name => 'SPM_STAGE1', enabled => 'yes', creator => 'APPS', sql_handle => 'SQL_04252731506dfa3b',plan_name => 'SQL_PLAN_089976586vyjv74c630de'); -- перенос таблицы в целевую бд (expdp/impdp, dblink) n := DBMS_SPM.UNPACK_STGTAB_BASELINE(table_name => 'SPM_STAGE1', fixed => 'yes', sql_handle => 'SQL_04252731506dfa3b',plan_name => 'SQL_PLAN_089976586vyjv74c630de'); -- -- модификация / управление Baseline -- n := DBMS_SPM.alter_sql_plan_baseline('SQL_04252731506dfa3b','SQL_PLAN_089976586vyjv74c630de','fixed','yes'); n := DBMS_SPM.alter_sql_plan_baseline('SQL_04252731506dfa3b','SQL_PLAN_089976586vyjv74c630de','autopurge','no'); n := DBMS_SPM.alter_sql_plan_baseline('SQL_04252731506dfa3b','SQL_PLAN_089976586vyjv74c630de','description','Fixed plan for task#333222'); -- -- удаление Baseline -- n := dbms_spm.drop_sql_plan_baseline('SQL_04252731506dfa3b', 'SQL_PLAN_267kw3nnjs02604fdefe5'); end; SQL> -- список подсказок, составляющих Baseline, из промежуточной / stage таблицы SQL> select substr(extractvalue(value(d), '/hint'), 1, 200) as outline_hints 2 from xmltable('/outline_data/hint' passing 3 (select xmltype(comp_data) as xmlval 4 from SPM_STAGE1 5 where sql_handle = 'SQL_04252731506dfa3b' 6 and obj_name = 'SQL_PLAN_089976586vyjv74c630de')) d 7 / OUTLINE_HINTS ------------------------------------- IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') ALL_ROWS OUTLINE_LEAF(@"SEL$5") . SQL> -- получение плана, сохранённого в Baseline SQL> SELECT * FROM TABLE(dbms_xplan.display_sql_plan_baseline('SQL_04252731506dfa3b','SQL_PLAN_089976586vyjv74c630de','all +predicate -outline -alias -projection'));
11.2.0.3.@ SQL> @bl_hints SQL_PLAN_acg49cdw0088v4085ecd2 OUTLINE_HINTS ------------------------------------------------------------------- IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') .

Начиная с Oracle 11g фиксация планов выполнения запросов с помощью baseline может быть затруднена / проблематична из-за влияния технологий Cardinality Feedback / Bind Aware Cursor Sharing

Пример загрузки архивного плана из репозитория AWR, с последующим добавлением подсказок и установкой аттрибутов DBA_SQL_PLAN_BASELINES.AUTOPURGE и FIXED: Сложный запрос, использование Baseline и Bind-Aware Cursor Sharing

Владимир Пржиялковский. Как обязать СУБД применять к запросам конкретные приемлемые планы
HOW TO LOAD SQL PLANS INTO SPM FROM AWR [ID 789888.1] — восстановление из истории «хорошего» плана выполнения
Loading Hinted Execution Plans into SQL Plan Baseline. [ID 787692.1] — ручная модификация плана выполнения существующего baseline
Jonathan Lewis. Fake Baselines — пример создания sql baseline для запроса с планом выполнения другого запроса из Shared Pool за одно выполнение процедуры dbms_spm.load_plans_from_cursor_cache

New baselines are generated automatically:
• for statement that already have Baselines created (when new plans are parsed by the optimizer, as non accepted) –it is not capture!
• when creating a SQL Profile on a statement that has Baseline (as accepted)

SQL Patch

Метод добавления подсказок к запросу без изменения текста приложения — Using SQL Patch to add hints to a packaged application:

SQL> -- добавление хинта по sql_id | создание SQL Patch SQL> begin 2 for reco in (select sql_fulltext from v$sqlarea where sql_id = 'f6cas59kcsb27') 3 loop 4 dbms_sqldiag_internal.i_create_patch( 5 sql_text => reco.sql_fulltext, 6 hint_text => 'no_bind_aware opt_param(''_optimizer_use_feedback'' ''false'')', 7 name => 'NO_CF_NO_BACS'); 8 end loop; 9 end; 10 / PL/SQL procedure successfully completed SQL> -- Список SQL> select sys.dbms_sqltune_util0.sqltext_to_sqlid(sql_text || chr(0)) sql_id, 2 sp.name, 3 sp.category, 4 sp.sql_text, 5 to_char(sp.created,'dd.mm.yyyy hh24:mi:ss') as created, 6 to_char(sp.last_modified,'dd.mm.yyyy hh24:mi:ss') as last_modified, 7 sp.description, 8 sp.status, 9 sp.force_matching 10 from dba_sql_patches sp 11 / SQL_ID NAME CATEGORY SQL_TEXT CREATED LAST_MODIFIED DESCRIPTION STATUS FORCE_MATCHING ------------- -------------- ---------- --------- ------------------- ------------------- ----------- -------- -------------- f6cas59kcsb27 NO_CF_NO_BACS DEFAULT SELECT. 05.03.2012 15:49:28 05.03.2012 15:49:28 ENABLED NO SQL> -- просмотр содержимого SQL Patch SQL> select substr(extractvalue(value(d), '/hint'), 1, 512) as sql_patch_hints 2 from xmltable('/outline_data/hint' passing 3 (select xmltype(comp_data) as xmlval 4 from sys.sqlobj$data od, sys.sqlobj$ o 5 where od.obj_type = 3 6 and (o.name = 'NO_CF_NO_BACS' and o.obj_type = 3) 7 and o.signature = od.signature 8 and comp_data is not null)) d 9 / SQL_PATCH_HINTS ---------------------------------------------------------- no_bind_aware opt_param('_optimizer_use_feedback' 'false') SQL> -- Факт использования SQL Patch отражается: SQL> -- - в описании дочернего курсора SQL> select child_number, plan_hash_value, SQL_PATCH from V$SQL where sql_id = 'bkx799gtk6r0h'; CHILD_NUMBER PLAN_HASH_VALUE SQL_PATCH ------------ --------------- --------------------- 0 1384833288 NO INDEX 4 OUTER JOIN 1 2051684057 NO INDEX 5 OUTER JOIN SQL> -- - в описании плана выполнения, в столбце V$SQL_PLAN.OTHER_XML: SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('f6cas59kcsb27')); . Note ----- - SQL patch "NO INDEX 4 OUTER JOIN" used for this statement SQL> -- - в трейсе оптимизатора: Content of other_xml column =========================== db_version : 11.2.0.3 parse_schema : APPS plan_hash : 2051684057 plan_hash_2 : 1417133226 sql_patch : NO INDEX 4 OUTER JOIN -- название патча . Outline Data: -- в виде хинтов в секции Outline SQL> -- удаление SQL Patch SQL> exec dbms_sqldiag.drop_sql_patch('NO INDEX 4 OUTER JOIN'); PL/SQL procedure successfully completed.

SQL Patch обладает некоторыми особенностями, например, отсутствием контроля содержимого и ограничением длины патча, создаваемого с помощью DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH, однако полноформатный «длинный» патч может быть создан процедурой SYS.DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE

Использование подсказок TABLE_STATS / INDEX_STATS / COLUMN_STATS

Szymon Skorupinski описывает способ/идею управления планом через модификацию статистики таблиц/индексов/столбцов на уровне запроса, при наличии «статистики хорошего выполнения» в DBA_TAB_STATISTICS / DBA_IND_STATISTICS / DBA_TAB_COL_STATISTICS либо в экспортированных таблицах:

-- stat views version select 'table_stats(' || owner || '.' || table_name || ', scale, rows=' || num_rows || ' blocks=' || blocks || ')' hints from dba_tab_statistics where table_name = 'APPTABLE' and owner = 'APPUSER' union all select 'index_stats(' || owner || '.' || table_name || ', ' || index_name || ', scale, index_rows=' || num_rows || ' blocks=' || leaf_blocks || ' keys=' || distinct_keys || ' clustering_factor=' || clustering_factor || ')' hints from dba_ind_statistics where table_name = 'APPTABLE' and owner = 'APPUSER' union all select 'column_stats(' || owner || '.' || table_name || ', ' || column_name || ', scale, length=' || avg_col_len || ' distinct=' || num_distinct || ' nulls=' || num_nulls || ' min=' || low_value || ' max=' || high_value || ')' hints from dba_tab_col_statistics where table_name = 'APPTABLE' and owner = 'APPUSER'; -- stattable version select 'table_stats(' || c5 || '.' || c1 || ', scale, rows=' || n1 || ' blocks=' || n2 || ')' hints from myuser.mystatstab where statid = 'NEW_STATS' and type = 'T' union all select 'index_stats(' || c5 || '.' || c4 || ', ' || c1 || ', scale, index_rows=' || n1 || ' blocks=' || n2 || ' keys=' || n3 || ' clustering_factor=' || n6 || ')' hints from myuser.mystatstab where statid = 'NEW_STATS' and type = 'I' union all select 'column_stats(' || c5 || '.' || c1 || ', ' || c4 || ', scale, length=' || n8 || ' distinct=' || n3 || ' nulls=' || n5 || ' min=' || r1 || ' max=' || r2 || ')' hints from ( select distinct c1, c4, c5, n3, n5, n8, r1, r2 from myuser.mystatstab where statid = 'NEW_STATS' and type = 'C');
Механизмы/технологии, используемые Oracle для автоматизированного улучшения планов выполнения
bind peeking

Использование значений связанных переменных при построении плана выполнения запроса, включая значения «настоящих» связанных переменных и «псевдо» связанных переменных, формируемых Oracle в процессе преобразовании запроса при использовании параметра CURSOR_SHARING=SIMILAR.
430208.1 Bind Peeking By Example
По умолчанию включён, начиная с Oracle 9.2, контролирующий параметр _OPTIM_PEEK_USER_BINDS. Называется причиной различий реальных планов выполнения запросов и планов, формируемых командами Autotrace и Explain Plan:
353670.1 Autotrace And Explain Plan Give Different Plans Than Row Source Trace

dynamic sampling

Оценка избирательности условий запроса (predicate selectivity) и статистики таблиц и индексов непосредственно во времявыполнения запроса. См. описание параметра optimizer_dynamic_sampling

Level (уровень) В каких случаях будет использован Dynamic Sampling Sample size (в блоках)
0 Отключает dynamic sampling N/A
1 Как минимум одна непартиционированная таблица запроса не имеет статистики 32
2 (default) Одна или более таблиц запроса не имеют статистики 64
3 Все запросы, удовлетворяющие условиям уровня 2
+ любые запросы, использующие выражения в условиях запроса (where clause predicates), например: Where substr(CUSTLASTNAME,1,3) or Where a + b =5
64
4 Все запросы, удовлетворяющие условиям уровня 3
+ любые запросы, содержащие сложные условия (complex predicates) с операторами OR или AND между множественными условиями для одной и той же таблицы
64
5 Все запросы, удовлетворяющие условиям уровня 4 128
6 Все запросы, удовлетворяющие условиям уровня 4 256
7 Все запросы, удовлетворяющие условиям уровня 4 512
8 Все запросы, удовлетворяющие условиям уровня 4 1024
9 Все запросы, удовлетворяющие условиям уровня 4 4086
10 Любые запросы Все блоки

«Начиная с Oracle 11gR, для параллельно выполняемых запросов оптимизатор автоматически принимает решение о применении и уровне dynamic sampling в зависимости от размеров таблиц и сложности условий в запросе. В случае если [на уровне системы/сессии/запроса] значение OPTIMIZER_DYNAMIC_SAMPLING установлено в значение, отличное от значения по умолчанию [2], dynamic sampling будет выполнять согласно установленному значению параметра. Факт использования dynamic sampling отражается в секции Note плана выполнения запроса»

При этом уровень dynamic sampling изменяется на уровне всего запроса и, например, может применяться к таблицам, обрабатываемым непараллельно, и не применяться к таблицам с параллельным доступом.

Bind-Aware Cursor Sharing

Начиная с Oracle 11.1 для запросов с использованием связанных переменных поддерживается генерация и нахождение в Shared Pool нескольких актуальных курсоров с различными планами выполнения. Разные курсоры применяются для наборов связанных переменных с различной статистикой выполнения запроса

Комплексная технология, состоит из 2-х последовательно применяемых функционалов Adaptive Cursor Sharing (ACS) и Extended Cursor Sharing (ECS). Adaptive Cursor Sharing для определения количества обрабатываемых на этапах выполнения запроса данных использует технологию Cardinality Feedback

Cardinality Feedback

Доступная с Oracle 11gR2 технология, использующая статистику исполнения запроса (rowsource statistics) для однократной модификации плана выполнения запроса, не использующего связанных переменных

Statistics Feedback

технология, продолжающая и развивающая Cardinality Feedback (CF) в версии 12c. Кроме дежурных исправлений / улучшений, данные Statistics Feedback в отличие от CF доступны в обзоре V$SQL_REDIRECTION в виде хинтов OPT_ESTIMATE, и сохраняются в Shared Pool в течение «времени жизни» курсора:

12.1.0.1.@ SQL> select /***/ * from v$sql_redirection; -- тест на системных обзорах 711 rows selected. -- объективная реальность Elapsed: 00:00:00.07 Execution Plan ---------------------------------------------------------- Plan hash value: 1688674190 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 153 | 0 (0)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 153 | 0 (0)| 00:00:01 | -- и ошибочный расчёт Rows=1 |* 2 | FIXED TABLE FULL| X$KGLCURSOR_CHILD | 1 | 111 | 0 (0)| 00:00:01 | | 3 | FIXED TABLE FULL| X$KKSSRD | 1076 | 45192 | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------- SQL> -- Статистика выполнения запроса отражается в виде SQL> select sql_id, child_number, hint_id, hint_text 2 from v$sql_reoptimization_hints 3 where sql_id in 4 (select sql_id 5 from v$sql 6 where sql_text like 'select /***/ * from v$sql_redirection%') 7 / SQL_ID CHILD_NUMBER HINT_ID HINT_TEXT ------------- ------------ ---------- ------------------------------------------------------------------------------ 9z4x0qx7dz63y 0 1 OPT_ESTIMATE (@"SEL$88122447" JOIN ("R"@"SEL$4" "C"@"SEL$4") ROWS=711.000000 ) SQL> -- эта подсказка при следующем выполнении будет добавлена непосредственно в текст запроса во время разбора (трейс 10053) в виде SQL> -- SQL:******* UNPARSED QUERY IS ******* SQL> -- SELECT /*+ OPT_ESTIMATE (@"SEL$88122447" JOIN ("R"@"SEL$4" "C"@"SEL$4") ROWS=711.000000 ) */ . SQL> -- что кроме непосредственного влияния хинта стимулирует генерацию нового дочернего курсора (hard parse) SQL> select /***/ * from v$sql_redirection; -- следующее выполнение 711 rows selected. SQL> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------- SQL_ID 9z4x0qx7dz63y, child number 1 -- с новым дочерним курсором ------------------------------------- select /***/ * from v$sql_redirection Plan hash value: 1688674190 -- на этот раз план не изменился ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| |* 1 | HASH JOIN | | 711 | 106K| 0 (0)| -- однако уточнена JOIN CARDINALITY Rows=711 |* 2 | FIXED TABLE FULL| X$KGLCURSOR_CHILD | 1 | 111 | 0 (0)| | 3 | FIXED TABLE FULL| X$KKSSRD | 1076 | 45192 | 0 (0)| ---------------------------------------------------------------------------- Note ----- - statistics feedback used for this statement -- флажок SQL> @shared_cu 9z4x0qx7dz63y PLAN_HASH_VALUE OPTIMIZER_COST CHILD SHAREABLE USE_FEEDBACK_STATS OPTIMIZER_STATS REASON1 --------------- -------------- ------ ---------- ------------------ --------------- ---------------------------------- 1688674190 1 0 N Y N Auto Reoptimization Mismatch(1) | -- появившаяся в 12c причина 1688674190 1 1 Y N N SQL> -- SQL> -- Statistics Feedback легко комбинируется с другой технологией 12c - Adaptive Plan SQL> select sql_id, child_number from v$sql_plan where other_xml like '%adaptive%cardinality%' and rownum < 2 2 / SQL_ID CHILD_NUMBER ------------- ------------ 2yn1z1cjm51wd 2 -- найденный в Library Cache курсор с признаками совместного применения технологий SQL>select * from table(dbms_xplan.display_cursor('2yn1z1cjm51wd', 2)) 2 / PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 2yn1z1cjm51wd, child number 2 ------------------------------------- select t.*, o.*, m.comments from sys.dba_tables t, . Note ----- - statistics feedback used for this statement -- подтверждение - this is an adaptive plan -- --//--
«Ручные» методы настройки запросов и влияния на план выполнения
Cardinality feedback

« Гипотеза (conjecture): CBO прекрасно находит наилучший план (access plan) для запроса в том случае, если имеет возможность точно оценить количество строк (CARDINALITIES) источников данных (ROW SOURCES), участвующих в плане выполнения»

В отличие от других методик, которые зачастую стараются заставить оптимизатор выбрать определённые операции доступа к данным в плане выполнения (access plan), этот метод настройки (tuning by cardinality feedback) ищет несоответствия между оценкой и реальными количеством строк, получаемых при выполнении плана (execution plan) и старается выяснить причины, приводящие CBO к ошибкам в оценках и выбору (предположительно) неоптимального плана выполнения. Как только причины найдены, следующей задачей является нахождение пути исправления причин ошибок в оценках, чтобы и в итоге предоставить возможность оптимизатору проделать свою работу заново, доверяя ему найти лучший план на основе исправленных, более точных оценок.
Таким образом эта методология не противоречит методике, используемой при подготовке SQL profiles с использованием пакета DBMS_SQLTUNE. SQL profiles дают CBO корректирующие коэффициенты для исправления [ошибочных] оценок количества строк источников данных (row source cardinality), в то время как метод TCF (tuning by cardinality feedback) стремится в первую очередь обеспечить оптимизатор более точной информацией для того, чтобы выполняемые оценки (row source cardinality estimates) приводили к более точным результатам.

Cardinality feedback — заметка J.Lewis, там же презентация Michelle Deng, в которой в качестве метода обеспечения оптимизатора более точной информацией об объектах бд используется механизм dynamic sampling

Коэффициенты, используемые CBO для расчётов в отсутствии статистики по объектам бд (т.е. по умолчанию)

В отсутствие необходимой для вычислений статисттики объектов бд (таблиц, столбцов, индексов) CBO использует набор постоянных величин и коэффициентов (hard-coded in Oracle) … Приведены наиболее свежие известные значения констант …

Особенности Oracle Real-Time SQL Monitoring
-- +-----------------------------------| -- query supplied by Yasin Baskan -- SQL> desc x$qksxa_reason -- Name Type -- -------------- ---------------| -- ADDR RAW(8) -- INDX NUMBER -- INST_ID NUMBER -- CON_ID NUMBER -- QKSXAREASONS VARCHAR2(255) -- +-----------------------------------| col qksxareasons format a100 col indx format 999 19.5@ SQL> SELECT 2 indx 3 ,qksxareasons 4 FROM 5 x$qksxa_reason 6 WHERE 7 qksxareasons like '%DOP downgrade%'; INDX QKSXAREASONS ---------- ------------------------------------------------------- 352 DOP downgrade due to adaptive DOP 353 DOP downgrade due to resource manager max DOP 354 DOP downgrade due to insufficient number of processes 355 DOP downgrade because slaves failed to join
19.5@ SQL> select SID, sql_id, sql_exec_id, sql_exec_start, otherstat_2_value, 2 case otherstat_2_value 3 when 352 then 'DOP downgrade due to adaptive DOP' 4 when 353 then 'DOP downgrade due to resource manager max DOP' 5 when 354 then 'DOP downgrade due to insufficient number of processes' 6 when 355 then 'DOP downgrade because slaves failed to join' 7 end reason_for_downgrade 8 from GV$SQL_PLAN_MONITOR where otherstat_2_value in (352,353,354,355); SID SQL_ID SQL_EXEC_ID SQL_EXEC_START OTHERSTAT_2_VALUE REASON_FOR_DOWNGRADE ---------- ------------- ----------- -------------------- ----------------- ----------------------------------------------------- 1833 a21b0wuncw9t8 33559174 08.04.2021 16:21:24 355 DOP downgrade because slaves failed to join 3756 bwyr4vfj3kjhj 16777222 08.04.2021 16:20:11 354 DOP downgrade due to insufficient number of processes 3756 chy1ny1wsca08 16777220 08.04.2021 16:14:22 354 DOP downgrade due to insufficient number of processes 3756 037h00br1wx0z 16777221 08.04.2021 16:20:10 354 DOP downgrade due to insufficient number of processes 3756 037h00br1wx0z 16777222 08.04.2021 16:20:56 354 DOP downgrade due to insufficient number of processes 2235 4duhbg8d8uzup 16777692 08.04.2021 16:07:46 355 DOP downgrade because slaves failed to join 3756 bwyr4vfj3kjhj 16777223 08.04.2021 16:20:56 354 DOP downgrade due to insufficient number of processes 2828 4duhbg8d8uzup 16777693 08.04.2021 16:07:49 354 DOP downgrade due to insufficient number of processes 3756 chy1ny1wsca08 16777219 08.04.2021 16:13:27 354 DOP downgrade due to insufficient number of processes 3091 3qp3htnxp6wnw 16777228 08.04.2021 16:18:08 355 DOP downgrade because slaves failed to join 10 rows selected
Optimizer Bug Fixes With Disabled Fix Control In 19c (Doc ID 2702770.1)

Disabled Optimizer Fixes In 19C Having Fix Control Option:

Bug Description DBRU Containing the Fix
28345522 Long Parse Time for Query with Multiple Tables in Join Order 19.3
22149010 Optimizer chooses HASH JOIN RIGHT SEMI over HASH UNIQUE with fix 13704562 19.3
25167306 Serial Direct Path Read not Used for Recursive Calls 19.3
29450812 OR Expansion rejected for query using WINDOW functions 19.5
28965084 Correction to Functional Based Index Replacements during INLIST Processing 19.5
28776811 Push Predicate (Join Predicate Push-Down) Not Happening despite being Optimal 19.5
29132869 ORA-918 Happens on Dynamic Sampling Recursive Query with Adaptive plan 19.5
28498976 Cost Based OR Expansion avoided for DMLs 19.5
29687220 SQL uses Index Fast Full Scan over Unique Scan for Empty Tables with Statistics set 19.7
30232638 Incorrect Cardinality Feedback when Cursor is closed 19.7
29304314 Cost Based OR Expansion rejection when # of Subqueries Exceeding threshold 19.8
28776431 Query with Large OR list Can Crash the Instance 19.8
29930457 High Parse Time with B-tree Bitmap Plans 19.8
27261477 Adaptive dynamic sampling issues in Parallel Execution 19.9
30347410 Slow Exchange Table Subpartition 19.9
30222669 High Version Count For Parallel Query with ‘Session Specific Cursor Session Mismatch(2)’ 19.10
30786641 VIEW PUSH PREDICATE Not Happening Through Outer Join Lateral View (VW_LAT_%) 19.10
30998035 Incorrect Selectivity Of Index Skip Scan With Virtual Column Replacement 19.10
29302565 Performance Regression for Queries Using ALL_TAB_STATISTICS in 19c 19.10
30979701 Bad Plan Due to Wrong Selectivity on NVL Predicate 19.11
30617002 OR Expansion Not Happening in Coalesced Query Block (OR/AND EXISTS) 19.11
30207519 With Clause SQLs Undergo MATERIALIZE Even After Transformations to Subquery 19.11
29867728 Regression in Join Predicate Push-Down (JPPD) To Set Query Blocks (UNION ALL) 19.11
29385774 Cost Based OR Expansion Rejected For SQLs from 12.2 Onwards 19.11
28414968 OR Expansion Not Happening Despite Cost Based OR Expansion Being Enabled 19.11
20922160 Bad Plan Due to Low Cardinality Estimation When Using NVL on Join Predicate 19.11
28708585 Bloom Filter Is Not Pushed Inside a View Beyond the Table Queue 19.12
31821701 Wrong Outer Join Selectivity of Multi-column Statistics 19.12
32107621 Wrong Cardinality Estimate for Multi-column Semi-Join & Full Outer Join 19.12
30142527 Subquery Unnest Plan Not Chosen due to Wrong Costing 19.12
32408640 CELL Offloading not happening for SQL statements using Full Table Scan with First K Rows (FKR) 19.13
31360214 Sub-optimal plan with Scalar Subquery Unnesting with table function (Collection Iterator) 19.13
33325981 Subquery Unnesting Not Happening Due to Some Restrictions 19.13
32212062 DBMS_REDEFINITION Not Using ONLINE for Index Rebuild 19.14 latest
32508585 Performance Impact with Hash Join when Adaptive Parallel Distribution Method is used 19.14 latest
31912834 MERGE STATEMENTS FAILING WITH ORA-600 [QKSDMLMRGVIEWDESTFRO_5] 19.14 latest
33145153 Cost Based Query Transformation Not Allowed if SQL statement has Pseudo-column involved 19.14 latest

To enable multiple fix control in a single command, refer below:

SQL> alter system set "_FIX_CONTROL"='28345522:ON', '22149010:ON', '25167306:ON', '29450812:ON', '28965084:ON', '28776811:ON', '29132869:ON', '28498976:ON', '29687220:ON', '30232638:ON', '29304314:ON', '28776431:ON', '29930457:ON', '27261477:ON', '30347410:ON', '30222669:ON', '30786641:ON', '30998035:ON', '29302565:ON', '30979701:ON', '30617002:ON', '30207519:ON', '29867728:ON', '29385774:ON', '28414968:ON', '20922160:ON', '28708585:ON', '31821701:ON', '32107621:ON', '30142527:ON', '32408640:ON', '31360214:ON', '33325981:ON', '32212062:ON', '32508585:ON', '31912834:ON', '33145153:ON' scope=both;

Оптимизация SQL-запросов в Oracle

Всем привет. Меня зовут Михаил Потапов, я — главный системный аналитик компании «Ростелеком Информационные Технологии». В компании занимаюсь разработкой отчетности для сегмента B2B и проектированием хранилища данных, на базе которого эта отчетность функционирует. Работоспособность каждого отчета напрямую зависит от корректно выстроенных SQL-запросов к базе данных Oracle, поскольку при работе с большими объемами данных скорость выполнения запросов может существенно снижаться. Снижение скорости сильно затрудняет работу с отчетами для конечного пользователя, а в некоторых случаях и вовсе делает ее невозможной.

В этой статье мы рассмотрим основные принципы оптимизации запросов в Oracle SQL, которые помогут ускорить работу с базой данных и повысить эффективность работы. Сразу отмечу, что статья рассчитана на junior и middle-специалистов, которые пишут сложные запросы к базе данных, работают с большими объемами данных и при этом ранее с вопросом оптимизации не сталкивались. Статья не содержит подробное руководство к действию, но описывает базовые основы «культуры кода», соблюдение которых позволит снизить нагрузку на БД и даст возможность более эффективно извлекать из нее данные.

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

1. Минимизация использования DISTINCT

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

2. Минимизация использования ORDER BY

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

  • путем экспорта в excel и последующим использованием встроенных фильтров (если дальнейшая работа с данными предстоит именно в этом инструменте);
  • путем сортировки BI-инструменте, если скрипт будет использован там.
3. Использование партиций

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

При обращении к таблице, в которой есть партиции, обязательно требуется ставить фильтр на партиционированное поле (в условие WHERE или фильтр JOIN’a). При этом не используйте приведение даты к другому формату, поскольку в этом случае произойдет сканирование всей таблицы вместо конкретной партиции.

Например, при обращении к таблице SALES, партиционированной по полю SALE_DT, запрос будет выглядеть следующим образом:

SELECT * FROM SALES WHERE TO_CHAR(SALE_DT, 'YYYY') >= '2022';

SELECT * FROM SALES WHERE SALE_DT >= TO_DATE('01-01-2022', 'DD-MM-YYYY');
4. Выборка необходимых полей в SELECT

При обращении к таблице вписывайте в выборку только те поля, которые действительно необходимы. Не используйте символ «*» для вызова всей полей из таблицы, это увеличивает время выполнения запроса/подзапроса.

SELECT * FROM SALES;

SELECT SALE_ID, SALE_DT FROM SALES;
5. Соединение таблиц (JOIN’s) по ключам

5.1. Не используйте соединение по неравенству полей.

SELECT s.SALE_ID FROM SALES s LEFT JOIN ORDERS o ON o.SALE_ID != s.SALE_ID;

5.2. Не используйте преобразование данных в ключах соединения таблиц.

SELECT * FROM SALES s LEFT JOIN ORDERS o ON TO_NUMBER(o.SALE_ID) = s.SALE_ID;

SELECT * FROM SALES s LEFT JOIN ORDERS o ON o.SALE_ID = s.SALE_ID;

5.3. Минимизируйте использование в качестве ключей атрибутов, связи по которым выполняются по принципу «многие-ко-многим».

5.4. Не используйте соединение по текстовым полям (с типом VARCHAR2 / CLOB / LONG).

5.5. Не используйте оператор OR в условии соединения таблиц. При необходимости связать одновременно по условию из разных таблиц лучше сделать 2 отдельных join’a, и затем связать их результат в select’е.

SELECT o.order_name FROM SALES s LEFT JOIN JOBS j on j.job_id = s.job_id LEFT JOIN ORDERS o ON (o.SALE_ID = s.SALE_ID OR o.job_id = j.job_id);

SELECT nvl(o1.order_name, o2.order_name) as order_name FROM SALES s LEFT JOIN JOBS j on j.job_id = s.job_id LEFT JOIN ORDERS o1 ON o1.SALE_ID = s.SALE_ID LEFT JOIN ORDERS o2 ON o2.job_id = j.job_id;
6. Правильный выбор операторов

6.1. Вместо использования оператора IN для проверки наличия значения в списке, можно использовать оператор EXISTS. Оператор EXISTS останавливается при первом совпадении, в то время как оператор «IN» выполняет полное сканирование списка значений.

SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments);

SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id);

6.2. Не используйте оператор LIKE там, где можно использовать точное определение значения через равенство либо использовать справочник для определения по идентификаторам.

SELECT * FROM employees where department_name like '%Тестир%';

SELECT * FROM departments where department_name = 'Тестирование'; --ИЛИ-- SELECT * FROM departments where department_id = 1;

6.3. Не используйте оператор UNION, если не требуется удаление дубликатов при соединении таблиц. Достаточно использовать UNION ALL.

SELECT sale_num FROM sales UNION SELECT sale_num FROM sale_services;

SELECT sale_num FROM sales UNION ALL SELECT sale_num FROM sale_services;
7. Использование CTE

Общая таблица выражений, или Common Table Expression (CTE) — это временная именованная подзапросная таблица, которая может быть использована внутри основного запроса или другой CTE. Она предоставляет более читабельный и модульный способ написания сложных запросов.

CTE может быть использован для оптимизации запросов в Oracle по нескольким причинам:

  • Улучшение читабельности: CTE позволяет разбить сложный запрос на более мелкие логические блоки, что делает его более понятным и легким для поддержки и отладки. Кроме того, CTE может быть именована, что дает возможность повторно использовать ее в других частях запроса.
  • Уменьшение повторения кода: Если одна и та же логика используется в нескольких частях запроса, то ее можно вынести в CTE и использовать ее в разных частях запроса. Это позволяет избежать дублирования кода и упрощает его поддержку.
  • Улучшение производительности: Использование CTE может помочь оптимизатору запросов принять лучшие решения о плане выполнения запроса. Оптимизатор может использовать информацию о CTE для принятия решений о порядке выполнения операций и выборе оптимальных индексов.
  • Рекурсивные запросы: CTE также может использоваться для написания рекурсивных запросов, которые требуют выполнения запроса на основе его собственных результатов. Это особенно полезно для работы с иерархическими данными, такими как деревья или графы.

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

Пример использования и синтаксис:

WITH sales_summary AS ( SELECT product_id, SUM(quantity) AS total_quantity FROM sales WHERE sale_date >= TRUNC(SYSDATE, 'MM') -- начало текущего месяца GROUP BY product_id ) SELECT p.product_name, s.total_quantity FROM sales_summary s JOIN products p ON p.product_id = s.product_id ORDER BY s.total_quantity DESC; -- то же самое, но через подзапросы: SELECT p.product_name, s.total_quantity FROM ( SELECT product_id, SUM(quantity) AS total_quantity FROM sales WHERE sale_date >= TRUNC(SYSDATE, 'MM') -- начало текущего месяца GROUP BY product_id) s JOIN products p ON p.product_id = s.product_id ORDER BY s.total_quantity DESC;
8. Использование материализованных представлений

Материализованные представления (Materialized Views или MV) – это представления, которые хранятся как физические таблицы и могут автоматически обновляться при изменении данных. Использование индексированных представлений может значительно улучшить производительность запросов, особенно для запросов с большими объемами данных.

CREATE MATERIALIZED VIEW mv_employee_names BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT first_name, last_name FROM employees; SELECT * FROM mv_employee_names WHERE first_name = 'John';

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

9. Минимизация использования множественных вложенных подзапросов

Множественные вложенные подзапросы могут быть плохой практикой из-за следующих причин:

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

Как вариант, вложенные подзапросы можно легко заменить на CTE.

SELECT sale_num FROM ( SELECT DISTINCT sale_num, sale_dt FROM ( SELECT * FROM sales where sale_num is not null)); -- тот же запрос, но с использованием CTE: WITH stg1 as ( SELECT * FROM sales where sale_num is not null) ,stg2 as ( SELECT DISTINCT sale_num, sale_dt FROM stg1) SELECT sale_num FROM stg2;
10. Использование подсказок HINT

Подсказки HINT позволяют указывать оптимизатору базы данных конкретные методы выполнения запросов. Например, можно указать оптимизатору использовать определенный тип JOIN или выбрать определенный индекс.

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

SELECT /+ PARALLEL(employees, 4) / * FROM employees;

10.2. Можно указать оптимизатору использовать конкретный индекс или объединение.

SELECT /+ INDEX(employees idx_employees_name) / * FROM employees WHERE first_name = 'John' AND last_name = 'Doe';

10.3. Можно использовать множество других подсказок, среди которых:

  • /+FULL(table_name) / — указывает оптимизатору использовать полный сканирование таблицы вместо индексного сканирования.
  • /+ORDERED/ — указывает оптимизатору сохранять порядок соединения таблиц, как указано в запросе.
  • /+USE_HASH(table_name) / — указывает оптимизатору использовать хэш-соединение для выполнения запроса.
  • /+LEADING(table_name) / — указывает оптимизатору начать соединение таблиц с указанной таблицы.
  • /+NO_MERGE/ — указывает оптимизатору не объединять несколько операций в одну.
  • /+NO_EXPAND/ — указывает оптимизатору не использовать расширение представлений для выполнения запроса.
  • /+OPT_PARAM(parameter value) / — позволяет установить значение параметра оптимизации запроса.

Однако следует быть осторожным при использовании подсказок HINT, поскольку неправильное использование может привести к нежелательным результатам.

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

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

Существует несколько типов индексов в Oracle:

  1. B-Tree (Balanced Tree) индексы: это наиболее распространенный тип индексов в Oracle. Они используют структуру дерева для организации данных и обеспечивают быстрый поиск по значениям столбца. B-Tree индексы подходят для равенственных и диапазонных запросов.
/*Чтобы найти сотрудника по имени и фамилии, мы можем создать индекс на колонке "first_name" и "last_name". Это позволит оптимизатору быстро найти соответствующие записи:*/ CREATE INDEX idx_emp_fio ON EMPLOYEES (first_name, last_name); SELECT * FROM EMPLOYEES WHERE first_name = 'John' AND last_name = 'Doe';
  1. Bitmap индексы: этот тип индексов используется для оптимизации выполнения запросов, содержащих условия сравнения наличия или отсутствия значений в столбцах. Bitmap индексы создают битовую карту, где каждый бит соответствует значению в индексируемом столбце.
CREATE BITMAP INDEX idx_emp_gender ON EMPLOYEES (gender);
  1. Функциональные индексы: они создаются на основе выражений или функций, применяемых к столбцам таблицы. Функциональные индексы позволяют ускорить выполнение запросов, содержащих условия поиска, основанные на значениях, полученных из выражений или функций.
CREATE INDEX idx_emp_fio ON EMPLOYEES (UPPER(first_name) || ' ' || UPPER(last_name));
  1. Партиционированные индексы: позволяют создавать индексы на отдельных фрагментах таблицы, называемых разделами. Это позволяет ускорить поиск данных в больших таблицах, разбивая их на более мелкие части и создавая индексы на каждой из них.
CREATE INDEX idx_emp_dep ON employees (department) PARTITION BY RANGE (salary) ( PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (5000), PARTITION p3 VALUES LESS THAN (MAXVALUE) );
  1. Уникальные индексы: они гарантируют уникальность значений в индексируемом столбце или комбинации столбцов. Уникальные индексы позволяют быстро проверять наличие дубликатов и обеспечивают целостность данных.
CREATE UNIQUE INDEX idx_emp_mail ON EMPLOYEES (email);

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

12. Выбор типа JOIN при соединении таблиц

12.1. Не используйте CROSS JOIN, поскольку он возвращает декартово произведение строк из обеих таблиц.

SELECT s.SALE_NUM, o.ORDER_ID FROM sales s, orders o;

SELECT s.SALE_NUM, o.ORDER_ID FROM sales s JOIN orders o ON s.sale_id = o.sale_id; -- ИЛИ SELECT s.SALE_NUM, o.ORDER_ID FROM sales s, orders o where s.sale_id = o.sale_id;

12.2. Не используйте LEFT JOIN в тех случаях, где достаточно использования INNER (например, в подзапросах или CTE).

12.3. Не используйте FULL JOIN, если в этом нет крайней необходимости. В отличие от LEFT, INNER и RIGHT, где хэш-таблица строится для одной таблицы, при FULL JOIN хэш-таблица строится сразу для двух, что увеличивает время выполнения запроса.

Что на выходе?

Время выполнения сложного запроса в БД в худшем случае не должно превышать пары минут.

Важно! Время выполнения запроса оценивается строго при выгрузке всего объема данных, а не первых 50 строк, как это работает, например, в Oracle SQL Developer. Чтобы выгрузить весь объем, достаточно кликнуть в любую ячейку таблицы и нажать комбинацию CTRL + A («выбрать всё»).

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

Анализ плана запроса

План запроса в Oracle SQL представляет собой информацию о том, как Oracle планирует выполнить запрос и какие операции будут использоваться. Чтение плана запроса помогает понять, какой тип JOIN и какие индексы используются, а также оценить производительность запроса.

Построение плана запроса (рассмотрим на примере ПО Oracle SQL Developer) вызывается через F10 или путем выбора соответствующей кнопки на панели (курсор при этом должен стоять на запросе, без выделения его отдельных частей):

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

1. Operation (Тип операции) — указывает на тип операции, выполняемой в плане запроса, такой как TABLE ACCESS, INDEX SCAN, JOIN и т.д.

2. Object_name (Имя объекта) — указывает на таблицы/индексы/представления, которые используются в плане запроса. Можно увидеть, какие таблицы сканируются полностью (FULL), какие используются с использованием индексов (INDEX), а также какие таблицы объединяются (JOIN).

3. Options (Параметры) — указывает на конкретное действие при выполнении запроса для каждого типа операции.

4. Cardinality (Кардинальность, или количество строк) — относится к оценке количества строк, которые будут обработаны или возвращены в результате выполнения запроса. Чем больше значение этого показателя, тем менее эффективен запрос. Кардинальность может быть оценена для каждой операции в плане запроса и используется оптимизатором для выбора наиболее эффективного плана выполнения запроса. Неправильная оценка кардинальности может привести к неправильному выбору плана выполнения и, как следствие, к плохой производительности запроса. Оптимизатор Oracle использует статистику таблиц и индексов, а также информацию о распределении данных, чтобы оценить кардинальность.

5. Cost (оценка стоимости) указывает на оценку стоимости выполнения каждой операции в плане запроса. Более низкая стоимость обычно означает более эффективное выполнение операции.

Как прочитать?

При анализе план просматриваетcя снизу вверх и от самого вложенного процесса (т.е. справа налево).

1. В процессе просмотра в первую очередь обращается внимание на строки с большим значением COST и CARDINALITY.

2. Помимо поиска больших COST и CARDINALITY в строках плана следует просматривать столбец OPERATION плана на предмет наличия в нем HASH JOIN. Соединение по HASH JOIN приводит к соединению таблиц в памяти и, казалось бы, более эффективным, чем вложенные соединения NESTED LOOP. Недостатком этого соединения является то, что при нехватке памяти для таблицы (таблиц) будут задействованы диски, которые существенно затормозят работу запроса.

3. Особое внимание в плане следует так же уделить строкам в плане с операциями полного сканирования таблиц и индексов в столбец OPTIONS: FULL — для таблиц и FULL SCAN, FAST FULL SCAN , SKIP SCAN — для индексов. Причинами полного сканирования могут быть проблемы с индексами: отсутствие индексов, неэффективность индексов, блокировка (например, при применении строковой функции на индексированном поле). При небольшом количестве строк в таблице полное сканировании таблицы FULL может быть нормальным явлением и эффективнее использования индексов.

4. Наличие в столбцах OPERATION и OPTIONS параметра MERGE JOIN CARTESIAN говорит, что между какими-то таблицами нет полной связки. Эта операция возникает при использовании CROSS JOIN, что крайне не рекомендуется делать.

Физические JOIN’s

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

1. MERGE JOIN — используется для объединения двух таблиц на основе сортированных столбцов. Он основывается на принципе «разделяй и властвуй», где каждая таблица разделяется на отсортированные блоки данных, а затем эти блоки объединяются. MERGE JOIN эффективен при выполнении JOIN операций на больших наборах данных, особенно если обе таблицы отсортированы по соответствующим столбцам. Он также может использоваться при JOIN операциях на неключевых столбцах.

Однако MERGE JOIN требует предварительной сортировки данных, что может занять время и ресурсы. Поэтому он может быть менее эффективным, если данные не отсортированы или если требуется сортировка большого объема данных.

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

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

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

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

Доп.информация

Красивые анимации взяты с сайта https://bertwagner.com/, там же есть ссылки на видео (на английском), где подробно объясняется про физические соединения таблиц.

P.s. это вторая версия статьи, с исправленными примерами и дополнениями.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *