Как получить фактический план выполнения запроса Oracle SQL с помощью подсказок по запросу Hibernate

Автор оригинала: Vlad Mihalcea.

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

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

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

Сначала выполняется анализ инструкции и создается дерево запросов. При анализе SQL-инструкции на основе строк база данных проверяет структуру инструкции и отклоняет ее, если SQL-запрос неверно сформирован.

Затем дерево запросов используется для построения Плана выполнения, который представляет собой алгоритм, используемый для извлечения данных. Компонент базы данных, который выбирает оптимальный план выполнения, называется CBO (Оптимизатор на основе затрат).

CBO реляционной базы данных может выбирать из различных типов соединений (например, Соединения с вложенными циклами , Хэш-соединения , объединения слиянием ), порядок соединения таблиц, какие индексы использовать или более целесообразно ли последовательное сканирование. Решение основано на стоимости (процессор, ввод-вывод, память) каждой конкретной операции, включенной в План выполнения. В конце концов, база данных выберет План выполнения с наименьшими затратами.

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

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

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

Для Oracle мы можем использовать пакет DBMS_XPLAN для отображения плана выполнения данного SQL-запроса.

Лукас Эдер приводит очень хорошую статью о получении плана выполнения для данной инструкции SQL.

В принципе, нам нужно:

Используйте подсказку GATHER_PLAN_STATISTICS SQL-запрос Oracle при выполнении инструкции, план которой мы хотим визуализировать.
Мы можем использовать таблицу статистики v$sql для поиска нашего запроса.

Предполагая, что у нас есть следующий SQL-запрос:

List postIds = entityManager
.createNativeQuery(
    "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 ")
.setFirstResult(pageStart)
.setMaxResults(pageSize)
.getResultList();

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

По этой причине нам необходимо предоставить следующие подсказки для запроса, используя Hibernate org.hibernate.запрос.Запрос setQueryHint метод:

List postIds = entityManager
.createNativeQuery(
    "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 ")
.setFirstResult(pageStart)
.setMaxResults(pageSize)
.addQueryHint("GATHER_PLAN_STATISTICS")
.addQueryHint("POST_WITH_BINGO_COMMENTS")
.getResultList();

Подсказка GATHER_PLAN_STATISTICS запроса предназначена для получения количества расчетных и фактических строк для каждого источника строк, в то время как POST_WITH_BINGO_COMMENTS – это пользовательская подсказка, которую мы использовали для поиска плана для этого конкретного SQL-запроса.

Чтобы получить план выполнения, нам нужно выполнить следующий SQL – запрос:

List executionPlanLines = entityManager
.createNativeQuery(
    "SELECT p.* " +
    "FROM v$sql s, TABLE ( " +
    "  dbms_xplan.display_cursor ( " +
    "    s.sql_id, s.child_number, 'ALLSTATS LAST' " +
    "  ) " +
    ") p " +
    "WHERE s.sql_text LIKE '%POST_WITH_BINGO_COMMENTS%'")
.getResultList();

LOGGER.info(
    "Execution plan: \n{}", 
    executionPlanLines
    .stream()
    .collect(
        Collectors.joining("\n")
    )
);

И мы получим фактический план выполнения:

SQL_ID  cxktfd882hu65, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS, POST_WITH_BINGO_COMMENTS */ * from 
( select row_.*, rownum rownum_ from ( 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 ) row_ where rownum <= :1 ) where rownum_ > :2
 
Plan hash value: 284760697
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Starts | E-Rows | A-Rows | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |      1 |        |     10 |      14 |       |       |          |
|*  1 |  VIEW                    |              |      1 |     30 |     10 |      14 |       |       |          |
|*  2 |   COUNT STOPKEY          |              |      1 |        |     30 |      14 |       |       |          |
|   3 |    VIEW                  |              |      1 |     62 |     30 |      14 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY|              |      1 |     62 |     30 |      14 | 73728 | 73728 |          |
|*  5 |      HASH JOIN RIGHT SEMI|              |      1 |     62 |     62 |      14 |  1452K|  1452K| 1287K (0)|
|*  6 |       TABLE ACCESS FULL  | POST_COMMENT |      1 |     62 |     62 |       7 |       |       |          |
|   7 |       TABLE ACCESS FULL  | POST         |      1 |    500 |    500 |       7 |       |       |          |
-----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("ROWNUM_">:2)
   2 - filter(ROWNUM<=:1)
   4 - filter(ROWNUM<=:1)
   5 - access("PC"."POST_ID"="P"."ID")
   6 - filter("PC"."REVIEW"='Bingo')
 
Note
-----
   - dynamic sampling used for this statement (level=2)

Круто, правда?

Другой вариант-использовать подсказку запроса для передачи GATHER_PLAN_STATISTICS Подсказка Oracle при использовании комментария SQL для нашего POST_WITH_BINGO_COMMENTS Комментария идентификации SQL:

List summaries = entityManager.createNativeQuery(
    "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 ")
.setFirstResult(pageStart)
.setMaxResults(pageSize)
.unwrap(org.hibernate.query.Query.class)
.addQueryHint("GATHER_PLAN_STATISTICS")
.setComment("POST_WITH_BINGO_COMMENTS")
.getResultList();

LOGGER.info(
    "Execution plan: \n{}", 
    executionPlanLines
    .stream()
    .collect(
        Collectors.joining("\n")
    )
);

Чтобы это сработало, нам нужно активировать комментарии на уровне SQL со следующим свойством конфигурации Hibernate:

Теперь Hibernate создаст следующую инструкцию SQL:

/* POST_WITH_BINGO_COMMENTS */
SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM
  (SELECT row_.*, rownum rownum_
   FROM
     (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) row_
   WHERE rownum <= 30)
WHERE rownum_ > 20

И план выполнения будет выполнен просто отлично:

SQL_ID  75yys0009b8fa, child number 0
-------------------------------------
/* POST_WITH_BINGO_COMMENTS */ select /*+ GATHER_PLAN_STATISTICS */ * 
from ( select row_.*, rownum rownum_ from ( 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 ) row_ where rownum <= :1 ) where 
rownum_ > :2
 
Plan hash value: 284760697
 
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |      1 |        |     10 |00:00:00.01 |      14 |       |       |          |
|*  1 |  VIEW                    |              |      1 |     30 |     10 |00:00:00.01 |      14 |       |       |          |
|*  2 |   COUNT STOPKEY          |              |      1 |        |     30 |00:00:00.01 |      14 |       |       |          |
|   3 |    VIEW                  |              |      1 |     52 |     30 |00:00:00.01 |      14 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY|              |      1 |     52 |     30 |00:00:00.01 |      14 | 73728 | 73728 |          |
|*  5 |      HASH JOIN RIGHT SEMI|              |      1 |     52 |     52 |00:00:00.01 |      14 |  1452K|  1452K| 1237K (0)|
|*  6 |       TABLE ACCESS FULL  | POST_COMMENT |      1 |     52 |     52 |00:00:00.01 |       7 |       |       |          |
|   7 |       TABLE ACCESS FULL  | POST         |      1 |    500 |    500 |00:00:00.01 |       7 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM_>:2)
   2 - filter(ROWNUM<=:1)
   4 - filter(ROWNUM<=:1)
   5 - access(PC.POST_ID=P.ID)
   6 - filter(PC.REVIEW='Bingo')
 
Note
-----
   - dynamic sampling used for this statement (level=2)

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

Функция подсказки запроса Hibernate оказывается очень удобной при получении плана выполнения Oracle, и она одинаково работает как для собственных запросов SQL, так и для запросов JPQL.

Читайте ещё по теме: