Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье мы рассмотрим, как мы можем получить план выполнения SQL в Oracle.
При настройке производительности план выполнения незаменим, так как он позволяет понять операции, выполняемые базой данных при выполнении определенного SQL-запроса.
Каков план выполнения SQL
Как я объяснил в этой статье , SQL описал Что а не то Как , и это одна из основных причин, по которой SQL был самым популярным способом запроса базы данных на протяжении более 40 лет.
При выполнении SQL-запроса база данных сначала проанализирует его и сгенерирует AST (Абстрактное синтаксическое дерево) для входящего запроса. Анализатор также проверяет синтаксис SQL-запроса и проверяет, существуют ли ресурсы базы данных, на которые ссылаются. Анализатор может переписать входящий запрос, чтобы оптимизировать его выполнение, не влияя на логику выборки результирующего набора.
Сгенерированное дерево запросов отправляется оптимизатору, которому необходимо определить наилучший способ извлечения данных, необходимых для текущего SQL-запроса. Таким образом, оптимизатор создает план выполнения, представляющий собой список операций, которые необходимо выполнить для удовлетворения требований SQL – запроса.
Полученный план выполнения отправляется Исполнителю, который запускает его и отправляет полученные данные обратно клиенту базы данных.
Этапы обработки и выполнения SQL можно визуализировать на следующей диаграмме.
Предполагаемый план выполнения SQL
Предполагаемый план выполнения генерируется оптимизатором без выполнения SQL-запроса. Вы можете сгенерировать предполагаемый план выполнения из любого клиента SQL, используя ОБЪЯСНИТЬ ПЛАН ДЛЯ или вы можете использовать Oracle SQL Developer для этой задачи.
ОБЪЯСНИТЕ ПЛАН ДЛЯ
При использовании Oracle, если вы добавляете команду ОБЪЯСНИТЬ ПЛАН ДЛЯ к заданному SQL-запросу, база данных сохранит предполагаемый план выполнения в соответствующей таблице PLAN_TABLE :
EXPLAIN PLAN FOR
SELECT p.id
FROM post p
WHERE EXISTS (
SELECT 1
FROM post_comment pc
WHERE
pc.post_id = p.id AND
pc.review = 'Bingo'
)
ORDER BY p.title
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY
Чтобы просмотреть предполагаемый план выполнения, вам необходимо использовать DBMS_XPLAN.DISPLAY , как показано в следующем примере:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'))
Параметр ВСЕ +СХЕМА форматирование позволяет получить более подробную информацию о предполагаемом плане выполнения, чем при использовании параметра форматирования по умолчанию.
Предполагаемый план будет выглядеть примерно так:
Plan hash value: 3657219412
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55 | 29590 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 55 | 29590 | 4 (25)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK | | 55 | 57750 | 4 (25)| 00:00:01 |
| 3 | NESTED LOOPS SEMI | | 55 | 57750 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | POST | 500 | 256K| 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| POST_COMMENT | 6 | 3150 | 0 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | POST_REVIEW_IDX | 55 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3 / from$_subquery$_003@SEL$3
2 - SEL$5DA710D3
4 - SEL$5DA710D3 / P@SEL$1
5 - SEL$5DA710D3 / PC@SEL$2
6 - SEL$5DA710D3 / PC@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_NL(@"SEL$5DA710D3" "PC"@"SEL$2")
LEADING(@"SEL$5DA710D3" "P"@"SEL$1" "PC"@"SEL$2")
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5DA710D3" "PC"@"SEL$2")
INDEX_RS_ASC(@"SEL$5DA710D3" "PC"@"SEL$2" ("POST_COMMENT"."REVIEW"))
FULL(@"SEL$5DA710D3" "P"@"SEL$1")
NO_ACCESS(@"SEL$3" "from$_subquery$_003"@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE_LEAF(@"SEL$3")
UNNEST(@"SEL$2")
OUTLINE_LEAF(@"SEL$5DA710D3")
ALL_ROWS
DB_VERSION('18.1.0')
OPTIMIZER_FEATURES_ENABLE('18.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE WHEN (20>=0) THEN 20 ELSE 0
END +10 AND "from$_subquery$_003"."rowlimit_$$_rownumber">20)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "P"."TITLE")<=CASE WHEN (20>=0) THEN 20 ELSE 0 END
+10)
5 - filter("PC"."POST_ID"="P"."ID")
6 - access("PC"."REVIEW"='Bingo')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=60) "from$_subquery$_003"."ID"[NUMBER,22],
"from$_subquery$_003"."rowlimit_$$_rownumber"[NUMBER,22]
2 - (#keys=1; rowset=60) "P"."TITLE"[VARCHAR2,1020], "P"."ID"[NUMBER,22], ROW_NUMBER() OVER (
ORDER BY "P"."TITLE")[22]
3 - (#keys=0) "P"."ID"[NUMBER,22], "P"."TITLE"[VARCHAR2,1020]
4 - "P"."ID"[NUMBER,22], "P"."TITLE"[VARCHAR2,1020]
6 - "PC".ROWID[ROWID,10]
Обратите внимание, что некоторые столбцы были удалены, чтобы
Разработчик Oracle SQL
Если у вас установлен SQL Developer, вы можете легко получить расчетный план выполнения для любого SQL-запроса без необходимости добавлять ПЛАН ОБЪЯСНЕНИЯ ДЛЯ команды :
Фактический план выполнения SQL
Фактический план выполнения SQL генерируется оптимизатором при выполнении SQL-запроса. Таким образом, в отличие от предполагаемого плана выполнения, вам необходимо выполнить SQL-запрос, чтобы получить его фактический план выполнения.
Фактический план не должен существенно отличаться от расчетного, если табличная статистика была должным образом собрана базовой реляционной базой данных.
Подсказка запроса GATHER_PLAN_STATISTICS
Чтобы указать Oracle хранить фактический план выполнения для данного SQL-запроса, вы можете использовать подсказку GATHER_PLAN_STATISTICS запрос:
SELECT /*+ GATHER_PLAN_STATISTICS */
p.id
FROM post p
WHERE EXISTS (
SELECT 1
FROM post_comment pc
WHERE
pc.post_id = p.id AND
pc.review = 'Bingo'
)
ORDER BY p.title
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY
Для визуализации фактического плана выполнения вы можете использовать DBMS_XPLAN.DISPLAY_CURSOR To visualize the actual execution plan, you can use DBMS_XPLAN.DISPLAY_CURSOR
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE'))
И фактический план будет выглядеть так:
Plan hash value: 3657219412
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 10 |00:00:00.01 | 1390 | | | |
|* 1 | VIEW | | 1 | 73 | 39274 | 4 (25)| 00:00:01 | 10 |00:00:00.01 | 1390 | | | |
|* 2 | WINDOW SORT PUSHED RANK | | 1 | 73 | 76650 | 4 (25)| 00:00:01 | 30 |00:00:00.01 | 1390 | 4096 | 4096 | 4096 (0)|
| 3 | NESTED LOOPS SEMI | | 1 | 73 | 76650 | 3 (0)| 00:00:01 | 73 |00:00:00.01 | 1390 | | | |
| 4 | TABLE ACCESS FULL | POST | 1 | 500 | 256K| 3 (0)| 00:00:01 | 500 |00:00:00.01 | 7 | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| POST_COMMENT | 500 | 11 | 5775 | 0 (0)| | 73 |00:00:00.01 | 1383 | | | |
|* 6 | INDEX RANGE SCAN | POST_REVIEW_IDX | 500 | 73 | | 0 (0)| | 33872 |00:00:00.01 | 8 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3 / from$_subquery$_003@SEL$3
2 - SEL$5DA710D3
4 - SEL$5DA710D3 / P@SEL$1
5 - SEL$5DA710D3 / PC@SEL$2
6 - SEL$5DA710D3 / PC@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
NO_ACCESS(@"SEL$3" "from$_subquery$_003"@"SEL$3")
FULL(@"SEL$5DA710D3" "P"@"SEL$1")
INDEX_RS_ASC(@"SEL$5DA710D3" "PC"@"SEL$2" ("POST_COMMENT"."REVIEW"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5DA710D3" "PC"@"SEL$2")
LEADING(@"SEL$5DA710D3" "P"@"SEL$1" "PC"@"SEL$2")
USE_NL(@"SEL$5DA710D3" "PC"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE WHEN (20>=0) THEN 20 ELSE 0 END +10 AND "from$_subquery$_003"."rowlimit_$$_rownumber">20))
2 - filter(ROW_NUMBER() OVER ( ORDER BY "P"."TITLE")<=CASE WHEN (20>=0) THEN 20 ELSE 0 END +10)
5 - filter("PC"."POST_ID"="P"."ID")
6 - access("PC"."REVIEW"='Bingo')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=60) "from$_subquery$_003"."ID"[NUMBER,22], "from$_subquery$_003"."rowlimit_$$_rownumber"[NUMBER,22]
2 - (#keys=1; rowset=60) "P"."TITLE"[VARCHAR2,1020], "P"."ID"[NUMBER,22], ROW_NUMBER() OVER ( ORDER BY "P"."TITLE")[22]
3 - "P"."ID"[NUMBER,22], "P"."TITLE"[VARCHAR2,1020]
4 - "P"."ID"[NUMBER,22], "P"."TITLE"[VARCHAR2,1020]
6 - "PC".ROWID[ROWID,10]
Вы также можете использовать подсказку GATHER_PLAN_STATISTICS запроса для проверки плана выполнения SQL, связанного с данным запросом JPQL или API критериев.
Для получения более подробной информации об этой теме ознакомьтесь с этой статьей .
Включите СТАТИСТИКУ на уровне подключения к БД
Если вы хотите получить планы выполнения для всех запросов, созданных в рамках данного сеанса, вы можете установить параметр STATISTICS_LEVEL сеанса в ВСЕ :
ALTER SESSION SET STATISTICS_LEVEL='ALL'
Это будет иметь тот же эффект, что и установка подсказки GATHER_PLAN_STATISTICS запроса для каждого запроса на выполнение. Таким образом, как и в случае с подсказкой GATHER_PLAN_STATISTICS запроса, вы можете использовать DBMS_XPLAN.DISPLAY_CURSOR для просмотра фактического плана выполнения.
Вам следует сбросить параметр STATISTICS_LEVEL в режим по умолчанию, как только вы закончите сбор интересующих вас планов выполнения. Это очень важно, особенно если вы используете пул соединений, а в соединениях с базой данных отказано.
ALTER SESSION SET STATISTICS_LEVEL='TYPICAL'
Вывод
Знание разницы между расчетным и фактическим планом выполнения SQL очень важно при использовании системы реляционных баз данных.
Если вы хотите ускорить определенный SQL-запрос, вам необходимо проверить план выполнения, чтобы определить, что именно вызывает медленное выполнение запроса. Без плана выполнения SQL невозможно определить, что база данных может делать под капотом при выполнении данного SQL-запроса.