Оптимизация плана объяснения Oracle

У меня есть сложный запрос Oracle UNION, который работает нормально. Выполнение запроса требует времени, так как ему приходится просматривать слишком много записей. Мне нужно проанализировать этот запрос и максимально оптимизировать его.

SELECT DISTINCT APP.APPLICATION_ID, APE.ENTITY_ID ENTITY_ID, APE.PARENT_ENTITY_ID, RP.ROLE_ID, RP.ACTION_ID, COALESCE(APE_MLD.ENTITY_NAME, APE.ENTITY_NAME)
ENTITY_NAME, RP.CURRENT_RECORD_STATUS_ID, COALESCE(ACT_MLD.ACTION_NAME, ACT.ACTION_NAME) ACTION_NAME, APE.ENTITY_PATH, APE.ADMIN_TYPE_ID, APE.IS_ADMIN_PAGE,
'RO.CR_NO', APE.ENTITY_CODE
FROM AUTH_APPLICATION_ENTITIES APE
INNER JOIN AUTH_APPLICATIONS APP ON APE.APPLICATION_ID = APP.APPLICATION_ID
LEFT JOIN AUTH_APPLICATION_ENTITIES_MLD APE_MLD ON APE.ENTITY_ID = APE_MLD.ENTITY_ID AND APE_MLD.LANGUAGE_ID = 1
LEFT JOIN AUTH_APPLICATION_ENTITIES PARENT_APE ON APE.PARENT_ENTITY_ID = PARENT_APE.ENTITY_ID
LEFT JOIN AUTH_APPLICATION_ENTITIES_MLD PARENT_APE_MLD ON PARENT_APE.ENTITY_ID = PARENT_APE_MLD.ENTITY_ID AND PARENT_APE_MLD.LANGUAGE_ID = 1
INNER JOIN AUTH_ROLES_PRIVILEGES RP ON APE.ENTITY_ID = RP.ENTITY_ID AND RP.CURRENT_RECORD_STATUS_ID NOT IN (2, 3, 6, 8, 9, 10)
INNER JOIN AUTH_ACTIONS ACT ON RP.ACTION_ID = ACT.ACTION_ID
LEFT JOIN AUTH_ACTIONS_MLD ACT_MLD ON ACT.ACTION_ID = ACT_MLD.ACTION_ID AND ACT_MLD.LANGUAGE_ID = 1
INNER JOIN AUTH_USERS_ROLES RO ON RP.ROLE_ID = RO.ROLE_ID AND RO.CURRENT_RECORD_STATUS_ID NOT IN (2, 3, 6, 8, 9, 10)
WHERE APE.CURRENT_RECORD_STATUS_ID ! = 2
AND RP.CURRENT_RECORD_STATUS_ID ! = 2
AND (APE.APPLICATION_ID = 715 OR 715 = -99)
AND RO.USER_ID = 1255162
AND (RO.ROLE_ID = -99 OR -99 = -99)
AND (APE.IS_ADMIN_PAGE = 0 OR USER_HAS_ADMIN_PRIVILEGES(APE.APPLICATION_ID, APE.ADMIN_TYPE_ID, 1255162) = 1)
UNION
SELECT DISTINCT APP.APPLICATION_ID, APE.ENTITY_ID ENTITY_ID, APE.PARENT_ENTITY_ID, RP.ROLE_ID, RP.ACTION_ID, COALESCE(APE_MLD.ENTITY_NAME,  APE.ENTITY_NAME)
ENTITY_NAME, RP.CURRENT_RECORD_STATUS_ID, COALESCE(ACT_MLD.ACTION_NAME, ACT.ACTION_NAME) ACTION_NAME, APE.ENTITY_PATH, APE.ADMIN_TYPE_ID, APE.IS_ADMIN_PAGE,
'CR_NO' CR_NO, APE.ENTITY_CODE
FROM AUTH_APPLICATION_ENTITIES APE
INNER JOIN AUTH_APPLICATIONS APP ON APE.APPLICATION_ID = APP.APPLICATION_ID
LEFT JOIN AUTH_APPLICATION_ENTITIES_MLD APE_MLD ON APE.ENTITY_ID = APE_MLD.ENTITY_ID AND APE_MLD.LANGUAGE_ID = 1
LEFT JOIN AUTH_APPLICATION_ENTITIES PARENT_APE ON APE.PARENT_ENTITY_ID = PARENT_APE.ENTITY_ID
LEFT JOIN AUTH_APPLICATION_ENTITIES_MLD PARENT_APE_MLD ON PARENT_APE.ENTITY_ID = PARENT_APE_MLD.ENTITY_ID AND PARENT_APE_MLD.LANGUAGE_ID = 1
INNER JOIN AUTH_ROLES_PRIVILEGES RP ON APE.ENTITY_ID = RP.ENTITY_ID AND RP.CURRENT_RECORD_STATUS_ID NOT IN (2, 3, 6, 8, 9, 10)
INNER JOIN AUTH_ACTIONS ACT ON RP.ACTION_ID = ACT.ACTION_ID
LEFT JOIN AUTH_ACTIONS_MLD ACT_MLD ON ACT.ACTION_ID = ACT_MLD.ACTION_ID AND ACT_MLD.LANGUAGE_ID = 1
INNER JOIN AUTH_ROLE_TYPE_ROLES RTO ON RP.ROLE_ID = RTO.ROLE_ID AND RTO.CURRENT_RECORD_STATUS_ID NOT IN (2, 3, 6, 8, 9, 10)
WHERE APE.CURRENT_RECORD_STATUS_ID ! = 2
AND RP.CURRENT_RECORD_STATUS_ID ! = 2
AND (APE.APPLICATION_ID = 715 OR 715 = -99)
AND RTO.ROLE_TYPE_ID IN (SELECT ROLE_TYPE_ID FROM AUTH_USER_TYPES WHERE USER_ID = 1255162)
AND (RTO.ROLE_ID = -99 OR -99 = -99)
AND (APE.IS_ADMIN_PAGE = 0 OR USER_HAS_ADMIN_PRIVILEGES(APE.APPLICATION_ID, APE.ADMIN_TYPE_ID, 1255162) = 1);

Я создал план объяснения, и он дает следующий результат:

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                               |    40 | 11232 |  1485  (63)| 00:00:18 |
|   1 |  SORT UNIQUE                         |                               |    40 | 11232 |  1485  (63)| 00:00:18 |
|   2 |   UNION-ALL                          |                               |       |       |            |          |
|*  3 |    HASH JOIN OUTER                   |                               |    26 |  7228 |   631  (13)| 00:00:08 |
|   4 |     NESTED LOOPS OUTER               |                               |    22 |  5016 |   625  (13)| 00:00:08 |
|   5 |      NESTED LOOPS OUTER              |                               |    22 |  4862 |   625  (13)| 00:00:08 |
|   6 |       NESTED LOOPS                   |                               |    22 |  4774 |   625  (13)| 00:00:08 |
|*  7 |        HASH JOIN OUTER               |                               |    22 |  4686 |   625  (13)| 00:00:08 |
|*  8 |         HASH JOIN                    |                               |    22 |  4246 |   622  (13)| 00:00:08 |
|*  9 |          HASH JOIN                   |                               |    22 |  3872 |   620  (13)| 00:00:08 |
|* 10 |           TABLE ACCESS BY INDEX ROWID| AUTH_APPLICATION_ENTITIES     |    40 |  5920 |     7   (0)| 00:00:01 |
|* 11 |            INDEX RANGE SCAN          | AUTH_AE_IDX_APP               |    49 |       |     1   (0)| 00:00:01 |
|  12 |           NESTED LOOPS               |                               |   597 | 16716 |   612  (13)| 00:00:08 |
|* 13 |            TABLE ACCESS FULL         | AUTH_USERS_ROLES              |     5 |    65 |   607  (13)| 00:00:08 |
|* 14 |            INDEX RANGE SCAN          | AUTH_ROLES_PRIVILEGES_UK1     |   131 |  1965 |     1   (0)| 00:00:01 |
|  15 |          TABLE ACCESS FULL           | AUTH_ACTIONS                  |    11 |   187 |     2   (0)| 00:00:01 |
|* 16 |         TABLE ACCESS FULL            | AUTH_ACTIONS_MLD              |     9 |   180 |     2   (0)| 00:00:01 |
|* 17 |        INDEX UNIQUE SCAN             | PK_SYS_MODULES                |     1 |     4 |     0   (0)| 00:00:01 |
|* 18 |       INDEX UNIQUE SCAN              | PK_SYS_ENTITIES               |     1 |     4 |     0   (0)| 00:00:01 |
|* 19 |      INDEX UNIQUE SCAN               | AUTH_APPLICATION_ENTITIES_UK1 |     1 |     7 |     0   (0)| 00:00:01 |
|* 20 |     TABLE ACCESS FULL                | AUTH_APPLICATION_ENTITIES_MLD |  1262 | 63100 |     5   (0)| 00:00:01 |
|* 21 |    HASH JOIN OUTER                   |                               |    14 |  4004 |   852  (17)| 00:00:11 |
|  22 |     NESTED LOOPS OUTER               |                               |    12 |  2832 |   847  (17)| 00:00:11 |
|  23 |      NESTED LOOPS OUTER              |                               |    12 |  2748 |   847  (17)| 00:00:11 |
|  24 |       NESTED LOOPS                   |                               |    12 |  2700 |   847  (17)| 00:00:11 |
|* 25 |        HASH JOIN OUTER               |                               |    12 |  2652 |   847  (17)| 00:00:11 |
|* 26 |         HASH JOIN                    |                               |    12 |  2412 |   844  (17)| 00:00:11 |
|* 27 |          HASH JOIN                   |                               |    12 |  2208 |   842  (17)| 00:00:11 |
|* 28 |           TABLE ACCESS BY INDEX ROWID| AUTH_APPLICATION_ENTITIES     |    40 |  5920 |     7   (0)| 00:00:01 |
|* 29 |            INDEX RANGE SCAN          | AUTH_AE_IDX_APP               |    49 |       |     1   (0)| 00:00:01 |
|  30 |           NESTED LOOPS               |                               |   308 | 11088 |   834  (17)| 00:00:11 |
|* 31 |            HASH JOIN                 |                               |     3 |    63 |   831  (17)| 00:00:10 |
|* 32 |             TABLE ACCESS FULL        | AUTH_USER_TYPES               |     4 |    40 |   829  (17)| 00:00:10 |
|* 33 |             TABLE ACCESS FULL        | AUTH_ROLE_TYPE_ROLES          |     9 |    99 |     2   (0)| 00:00:01 |
|* 34 |            INDEX RANGE SCAN          | AUTH_ROLES_PRIVILEGES_UK1     |    89 |  1335 |     1   (0)| 00:00:01 |
|  35 |          TABLE ACCESS FULL           | AUTH_ACTIONS                  |    11 |   187 |     2   (0)| 00:00:01 |
|* 36 |         TABLE ACCESS FULL            | AUTH_ACTIONS_MLD              |     9 |   180 |     2   (0)| 00:00:01 |
|* 37 |        INDEX UNIQUE SCAN             | PK_SYS_MODULES                |     1 |     4 |     0   (0)| 00:00:01 |
|* 38 |       INDEX UNIQUE SCAN              | PK_SYS_ENTITIES               |     1 |     4 |     0   (0)| 00:00:01 |
|* 39 |      INDEX UNIQUE SCAN               | AUTH_APPLICATION_ENTITIES_UK1 |     1 |     7 |     0   (0)| 00:00:01 |
|* 40 |     TABLE ACCESS FULL                | AUTH_APPLICATION_ENTITIES_MLD |  1262 | 63100 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------

Мне нужно оптимизировать приведенный выше запрос, прочитав результат плана объяснения. Мне просто нужно знать, как я могу найти заказы JOIN, медленные запросы и т. д. Может ли кто-нибудь помочь мне в этом?

Таким образом, единственная значимая разница между двумя подзапросами заключается в том, что один фильтрует AND RO.USER_ID = 1255162, а другой — AND RTO.ROLE_TYPE_ID IN (SELECT ROLE_TYPE_ID FROM AUTH_USER_TYPES WHERE USER_ID = 1255162). Учитывая, что это тот же USER_ID, кажется удивительным, что нет способа запустить это как один запрос. Но я не знаю вашей модели данных или бизнес-логики, поэтому я не в состоянии ее перепроектировать.

— 21.03.2019 11:29

oracle performance oracle11g oracle10g query-optimization

21.03.2019 10:54

Стоит ли изучать PHP в 2026-2027 годах?

Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...

Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией

В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.

Приемы CSS-макетирования - floats и Flexbox

Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...

Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest

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

Концепция локализации и ее применение в приложениях React ⚡️

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

Пользовательский скаляр GraphQL

Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...

Ответы 1

С этим сложно помочь, мы не знаем, где ваш sql потерял время.

запустите запрос с подсказкой /*+ GATHER_PLAN_STATISTICS */ или измените сеанс с помощью изменить набор сеансов Statistics_level = 'ALL', а затем поместить сюда результат плана с помощью:

ВЫБРАТЬ * ИЗ таблицы (DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

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

21.03.2019 15:37