Оператор запроса занимает довольно много времени при выполнении на оракуле

Проблема

Агрегация происходит слишком поздно в плане выполнения. Планы с идентификаторами 4 и 5 генерируют 13 миллиардов строк и составляют 95 % времени выполнения. Oracle ошибочно полагает, что количество строк будет меньше, и что более ранние агрегации должны быть объединены вместе.

Идентификаторы плана с 6 по 40 представляют первую половину встроенного представления до UNION. В этой части запроса есть два DISTINCT, но для этой части плана выполнения нет типов операций агрегирования. Oracle ошибочно считает, что лучше сначала соединить все и выполнить одно SORT UNIQUE, чем выполнять несколько SORT UNIQUE или HASH GROUP BY и объединять результаты.

Воспроизведите проблему

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

Сначала создайте две простые таблицы. Каждый имеет 100 тыс. строк. TEST1 имеет числа от 1 до 100 000. TEST2 содержит 100 000 строк, но только одно число. Чтобы искусственно составить плохой план, статистика на TEST2 собирается слишком рано. Оптимизатор думает, что в TEST2 есть только одна строка, а на самом деле их 100 000.

drop table test1 purge;
drop table test2 purge;

create table test1(a number);
create table test2(a number);

insert into test1 select level from dual connect by level <= 100000;
insert into test2 values (1);
commit;

begin
    dbms_stats.gather_table_stats(user, 'test1');
    dbms_stats.gather_table_stats(user, 'test2');
end;
/

insert into test2 select 1 from dual connect by level <= 100000;
commit;

Приведенный ниже пример запроса извлекает все отдельные TEST1.A, где A также находится в отдельном TEST2.A.

По умолчанию, используя искусственно плохую статистику, Oracle сначала объединяет таблицы, а затем выполняет операции HASH GROUP BY и HASH UNIQUE. Это плохой план, он объединяет все 100 тысяч значений из TEST2. Было бы лучше сначала выполнить HASH GROUP BY, а затем присоединиться только к 1 строке из этой таблицы.

explain plan for
select distinct a from test1 where a in (select a from test2 group by a);

select * from table(dbms_xplan.display(format => 'outline'));

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |     8 |    79   (2)| 00:00:01 |
|   1 |  HASH UNIQUE         |       |     1 |     8 |    79   (2)| 00:00:01 |
|   2 |   HASH GROUP BY      |       |     1 |     8 |    79   (2)| 00:00:01 |
|*  3 |    HASH JOIN         |       |     1 |     8 |    79   (2)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| TEST2 |     1 |     3 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| TEST1 |   100K|   488K|    76   (2)| 00:00:01 |
------------------------------------------------------------------------------

Возможное решение №1: подсказки

К сожалению, нет официальных подсказок, позволяющих контролировать, когда и где происходит сортировка и группировка. Поэкспериментировав с параметром формата outline, я смог найти несколько потенциально полезных советов: USE_HASH_AGGREGATION, OUTLINE_LEAF и PLACE_DISTINCT. (Эти подсказки действительно сложны — причина, по которой я использовал group by вместо другого distinct в своем образце, заключается в том, что у меня было так много проблем с подсказкой PLACE_DISTINCT!)

Используя эти недокументированные подсказки, можно построить лучший план. Результаты TEST2 сразу же проходят через HASH GROUP BY, как и должны. Это похоже на план, который был бы составлен, если бы статистика была точной.

explain plan for
select /*+ USE_HASH_AGGREGATION(@"SEL$5DA710D3") OUTLINE_LEAF(@"SEL$683B0107") */
distinct a from test1 where a in (select a from test2 group by a);

select * from table(dbms_xplan.display(format => 'outline alias'));

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |     8 |    79   (2)| 00:00:01 |
|   1 |  HASH UNIQUE          |          |     1 |     8 |    79   (2)| 00:00:01 |
|*  2 |   HASH JOIN SEMI      |          |     1 |     8 |    79   (2)| 00:00:01 |
|   3 |    VIEW               | VW_NSO_1 |     1 |     3 |     3   (0)| 00:00:01 |
|   4 |     HASH GROUP BY     |          |     1 |     3 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| TEST2    |     1 |     3 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL  | TEST1    |   100K|   488K|    76   (2)| 00:00:01 |
----------------------------------------------------------------------------------

Возможное решение 2. Форсируйте план с помощью ROWNUM.

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

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

explain plan for
select distinct a from test1 where a in
(
    --Extra level only because we only want to project one column.
    --It's syntactically required, but the optimizer throws out this inline view.
    select a
    from
    (
        --The ROWNUM forces everything in this inline view to happen separately.
        select a, rownum
        from
        (
            select a from test2 group by a
        )
    )
);

select * from table(dbms_xplan.display(format => 'outline alias'));

---------------------------------------------------------------------------------
| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |     1 |     8 |    79   (2)| 00:00:01 |
|   1 |  HASH UNIQUE            |       |     1 |     8 |    79   (2)| 00:00:01 |
|*  2 |   HASH JOIN SEMI        |       |     1 |     8 |    79   (2)| 00:00:01 |
|   3 |    VIEW                 |       |     1 |     3 |     3   (0)| 00:00:01 |
|   4 |     COUNT               |       |       |       |            |          |
|   5 |      VIEW               |       |     1 |     3 |     3   (0)| 00:00:01 |
|   6 |       HASH GROUP BY     |       |     1 |     3 |     3   (0)| 00:00:01 |
|   7 |        TABLE ACCESS FULL| TEST2 |     1 |     3 |     3   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL    | TEST1 |   100K|   488K|    76   (2)| 00:00:01 |
---------------------------------------------------------------------------------

Возможное решение 3. Исправьте оценки количества элементов и надейтесь на лучшее.

Если предполагаемое количество строк точное, план почти всегда хорош. Когда оценки строк далеки друг от друга, найдите первую часть плана выполнения, где кардинальность неверна. Для этого плана это план ID 36. E-Rows и A-Rows отключены на порядок:

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
...
|* 36 |             TABLE ACCESS FULL             | POS_CUST_XREF               |      1 |     54 |    579 |00:00:00.01 |     131 |      0 |       |       |          |

Шаг 36 имеет сложный предикат, который включает SYSDATE.

  36 - filter(("M"."SG_BILL_TO_CUST_NO" IS NOT NULL AND ("M"."INDIRECT_DISTI"='Y' OR "M"."CATEGORY_TYPE" LIKE 'DIRECT%') AND "M"."ACTIVE_IND"<>'N' AND
              TRUNC(INTERNAL_FUNCTION("M"."ARCHIVE_DATE"))>TRUNC(SYSDATE@!)))

Даже с современной статистикой это состояние будет трудно предсказать. Динамическая выборка может помочь. Попробуйте повторно запустить запрос с такой подсказкой верхнего уровня:

SELECT /*+ dynamic_sampling(6) */ ...

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

Отвлекающий маневр

Существует множество потенциальных улучшений любого умеренно сложного оператора SQL. В комментариях и ответах есть несколько хороших идей. Но при настройке всегда необходимо фокусироваться на том, что работает медленнее, а не на том, что легче всего исправить. Звучит очевидно, но в эту ловушку очень легко попасть. Вот почему я попросил вас использовать /*+ gather_plan_statistics*/, и именно поэтому мой ответ сосредоточен только на частях плана с большим фактическим временем.

Например, в своем предыдущем комментарии я предложил посмотреть на NESTED LOOPS, где ROWS=1. Теперь, когда у нас есть фактическое время, мы знаем, что это предложение бесполезно. (Хотя в целом вы все равно должны скептически относиться к плану с большими таблицами, но ROWS=1.)

Прежде чем переписывать запрос, не могли бы вы сообщить план и время выполнения этого запроса?

Последующие действия:

1. Добавление параллельной подсказки

2. Добавление предложения раздела в select

Подсказка precompute_subquery будет извлекать текст подзапроса из раздела подзапроса, запускать его отдельно (перед запуском основного запроса) в контексте рекурсивного вызова, извлекать результаты и передавать их в условие «фильтра» основного запроса в виде списка условий ИЛИ. Я думаю, это называется развертыванием подзапроса и отличается от разбора блока запроса, который используется для распределенных запросов. Я видел его использование в запросах OLAP.

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

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

SELECT
      DISTINCT GROUP_DIST_NUMBER,
             BEGIN_DATE,
             PRICE_DROP_DATE
FROM
      (SELECT                       /*+ PARALLEL (P,4) PARALLEL (L,4)*/
            DISTINCT
            G.GROUP_DIST_NUMBER,
            TO_DATE ( :B2,
                    'DD-MON-YYYY' )
                BEGIN_DATE,
            TO_DATE ( :B2,
                    'DD-MON-YYYY' )
                PRICE_DROP_DATE
       FROM
            POS_DISTI_GROUP G,
            POS_CUST_XREF M,
            S_CPT_SEQ_NO C,
            PP_STD_PRICE P,
            S_CPT_AUDIT A,
            RPT_PRODUCT_VALUE_LEVEL L
       WHERE
                G.END_DATE > TO_DATE ( :B2,
                                  'DD-MON-YYYY' )
            AND G.GROUP_DIST_NUMBER = M.DIST_NUMBER
            AND M.SG_BILL_TO_CUST_NO = A.BILL_TO_CUST_NO
            AND A.START_DATE <= TO_DATE ( :B2,
                                    'DD-MON-YYYY' )
            AND A.END_DATE >= TO_DATE ( :B2,
                                   'DD-MON-YYYY' )
            AND L.PROD_VALUE = P.PROD_VALUE
            AND L.PROD_LEVEL = P.PROD_LEVEL
            AND C.CPT_PRICE_CODE IN
                    (SELECT                    /*+  PARALLEL (P1,4)*/
                          DISTINCT C1.CPT_PRICE_CODE
                    FROM
                          PP_STD_PRICE P1,
                          S_CPT_PRICE_CODE C1,
                          S_CPT_SEQ_NO S1
                    WHERE
                             P1.STDP_ID = :B1
                          AND C1.CPT_PRICE_CAT LIKE 'NB%'
                          AND C1.CPT_PRICE_CODE = S1.CPT_PRICE_CODE
                          AND S1.PRICE_PROTECTABLE = 'Y')
            AND C.CPT_PRICE_CODE = P.CUST_PRICE_TYPE
            AND P.STDP_ID = :B1
            AND A.CUST_PRICE_TYPE = C.CPT_BILL_CODE
            AND M.ACTIVE_IND != 'N'
            AND ( M.CATEGORY_TYPE LIKE 'DIRECT%'
                OR M.INDIRECT_DISTI = 'Y' )
            AND TRUNC ( M.ARCHIVE_DATE ) > TRUNC ( SYSDATE )
       UNION
       SELECT                      /*+  PARALLEL (P,4) PARALLEL (L,4) */
            G.GROUP_DIST_NUMBER,
            P.BEGIN_DATE,
            MIN ( INVT.PRICE_DROP_DATE ) PRICE_DROP_DATE
       FROM
            POS_DISTI_GROUP G,
            POS_CUST_XREF M,
            PP_DEBIT_AUTHORIZATION P,
            RPT_PRODUCT_VALUE_LEVEL L,
            POS_PP_INVENTORY PARTITION ("F2011_Q2") INVT
       WHERE
                G.END_DATE > TO_DATE ( :B2,
                                  'DD-MON-YYYY' )
            AND G.GROUP_DIST_NUMBER = M.DIST_NUMBER
            AND M.ACTIVE_IND != 'N'
            AND ( M.CATEGORY_TYPE LIKE 'DIRECT%'
                OR M.INDIRECT_DISTI = 'Y' )
            AND G.DIST_NUMBER = P.DIST_NUMBER
            AND L.PROD_VALUE = P.PROD_VALUE
            AND L.PROD_LEVEL = P.PROD_LEVEL
            AND P.BEGIN_DATE >= TO_DATE ( :B2,
                                    'DD-MON-YYYY' )
                            - 6
            AND P.BEGIN_DATE <= TO_DATE ( :B2,
                                    'DD-MON-YYYY' )
            AND INVT.DIST_NUMBER = G.GROUP_DIST_NUMBER
            AND INVT.STMODEL = L.MOD_DESC
            AND INVT.PPCF_SHOW_DATE = P.BEGIN_DATE
            AND P.PRICE_TYPE = 'I'
            AND ( P.POS_PROCESSED_FLAG IS NULL
                OR P.POS_PROCESSED_FLAG != 'C' )
            AND P.POS_PP_FLAG = 'Y'
            AND TRUNC ( M.ARCHIVE_DATE ) > TRUNC ( SYSDATE )
       GROUP BY
            G.GROUP_DIST_NUMBER,
            P.BEGIN_DATE);

ОБЪЯСНИТЬ ПЛАН

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Name                        | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                             |   101 |  2525 |  8500  (17)|       |       |        |      |            |
|   1 |  PX COORDINATOR                                   |                             |       |       |            |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)                             | :TQ10005                    |   101 |  2525 |  8500  (17)|       |       |  Q1,05 | P->S | QC (RAND)  |
|   3 |    VIEW                                           |                             |   101 |  2525 |  8500  (17)|       |       |  Q1,05 | PCWP |            |
|   4 |     SORT UNIQUE                                   |                             |   101 | 18291 |  8500  (82)|       |       |  Q1,05 | PCWP |            |
|   5 |      PX RECEIVE                                   |                             |       |       |            |       |       |  Q1,05 | PCWP |            |
|   6 |       PX SEND HASH                                | :TQ10004                    |       |       |            |       |       |  Q1,04 | P->P | HASH       |
|   7 |        BUFFER SORT                                |                             |   101 |  2525 |            |       |       |  Q1,04 | PCWP |            |
|   8 |         UNION-ALL                                 |                             |       |       |            |       |       |  Q1,04 | PCWP |            |
|   9 |          BUFFER SORT                              |                             |       |       |            |       |       |  Q1,04 | PCWC |            |
|  10 |           PX RECEIVE                              |                             |       |       |            |       |       |  Q1,04 | PCWP |            |
|  11 |            PX SEND ROUND-ROBIN                    | :TQ10001                    |       |       |            |       |       |        | S->P | RND-ROBIN  |
|  12 |             MERGE JOIN CARTESIAN                  |                             |    10M|  1737M|  1635   (5)|       |       |        |      |            |
|* 13 |              HASH JOIN                            |                             |  2439 |   419K|   322   (3)|       |       |        |      |            |
|* 14 |               TABLE ACCESS FULL                   | POS_DISTI_GROUP             |   100 |  1800 |     5   (0)|       |       |        |      |            |
|* 15 |               HASH JOIN                           |                             |  2452 |   378K|   317   (3)|       |       |        |      |            |
|* 16 |                TABLE ACCESS FULL                  | S_CPT_SEQ_NO                |   651 |  5208 |     5   (0)|       |       |        |      |            |
|  17 |                NESTED LOOPS                       |                             |   580 | 87000 |   311   (2)|       |       |        |      |            |
|  18 |                 NESTED LOOPS                      |                             |    34 |  4658 |   131   (4)|       |       |        |      |            |
|* 19 |                  HASH JOIN                        |                             |     1 |    97 |   109   (5)|       |       |        |      |            |
|* 20 |                   HASH JOIN                       |                             |     9 |   774 |   107   (5)|       |       |        |      |            |
|* 21 |                    HASH JOIN                      |                             |     2 |   146 |   101   (4)|       |       |        |      |            |
|* 22 |                     TABLE ACCESS FULL             | POS_CUST_XREF               |    54 |  2268 |    25   (4)|       |       |        |      |            |
|* 23 |                     TABLE ACCESS FULL             | S_CPT_AUDIT                 |    68 |  2108 |    76   (4)|       |       |        |      |            |
|  24 |                    TABLE ACCESS FULL              | S_CPT_SEQ_NO                |  1301 | 16913 |     5   (0)|       |       |        |      |            |
|* 25 |                   TABLE ACCESS FULL               | S_CPT_PRICE_CODE            |    21 |   231 |     2   (0)|       |       |        |      |            |
|* 26 |                  INDEX RANGE SCAN                 | UK_PP_STD_PRICE_STDP_ID     |    26 |  1040 |    22   (0)|       |       |        |      |            |
|* 27 |                 VIEW                              | RPT_PRODUCT_VALUE_LEVEL     |     1 |    13 |     5   (0)|       |       |        |      |            |
|  28 |                  UNION ALL PUSHED PREDICATE       |                             |       |       |            |       |       |        |      |            |
|  29 |                   NESTED LOOPS OUTER              |                             |     1 |    46 |     2   (0)|       |       |        |      |            |
|  30 |                    NESTED LOOPS OUTER             |                             |     1 |    42 |     2   (0)|       |       |        |      |            |
|  31 |                     TABLE ACCESS BY INDEX ROWID   | SMA_STMODEL                 |     1 |    28 |     2   (0)|       |       |        |      |            |
|* 32 |                      INDEX UNIQUE SCAN            | PK_SMA_STMODEL              |     1 |       |     1   (0)|       |       |        |      |            |
|* 33 |                     INDEX UNIQUE SCAN             | PK_SEAEGO_PRODUCT_HIERARCHY |   298 |  4172 |     0   (0)|       |       |        |      |            |
|* 34 |                    INDEX UNIQUE SCAN              | PK_S_CAP_GROUP              |     2 |     8 |     0   (0)|       |       |        |      |            |
|  35 |                   NESTED LOOPS OUTER              |                             |     1 |    77 |     2   (0)|       |       |        |      |            |
|  36 |                    NESTED LOOPS OUTER             |                             |     1 |    73 |     2   (0)|       |       |        |      |            |
|  37 |                     NESTED LOOPS                  |                             |     1 |    59 |     2   (0)|       |       |        |      |            |
|  38 |                      NESTED LOOPS OUTER           |                             |     1 |    31 |     2   (0)|       |       |        |      |            |
|  39 |                       NESTED LOOPS OUTER          |                             |     1 |    27 |     2   (0)|       |       |        |      |            |
|  40 |                        TABLE ACCESS BY INDEX ROWID| SMA_PRODUCTMODEL            |     1 |    23 |     2   (0)|       |       |        |      |            |
|* 41 |                         INDEX UNIQUE SCAN         | PK_SMA_PRODUCTMODEL         |     1 |       |     1   (0)|       |       |        |      |            |
|* 42 |                        INDEX UNIQUE SCAN          | PK_S_FAMILY                 |  1366 |  5464 |     0   (0)|       |       |        |      |            |
|* 43 |                       INDEX UNIQUE SCAN           | PK_F_MODPRODMGR             |    22 |    88 |     0   (0)|       |       |        |      |            |
|  44 |                      TABLE ACCESS BY INDEX ROWID  | SMA_STMODEL                 | 13965 |   381K|     1   (0)|       |       |        |      |            |
|* 45 |                       INDEX UNIQUE SCAN           | PK_SMA_STMODEL              |     1 |       |     0   (0)|       |       |        |      |            |
|* 46 |                     INDEX UNIQUE SCAN             | PK_SEAEGO_PRODUCT_HIERARCHY |   298 |  4172 |     0   (0)|       |       |        |      |            |
|* 47 |                    INDEX UNIQUE SCAN              | PK_S_CAP_GROUP              |     2 |     8 |     0   (0)|       |       |        |      |            |
|* 48 |                   INDEX UNIQUE SCAN               | IDX_RPT_PROD_MV_PROD_NO     |     1 |    12 |     1   (0)|       |       |        |      |            |
|  49 |              BUFFER SORT                          |                             |  4128 | 20640 |  1629   (5)|       |       |        |      |            |
|* 50 |               INDEX RANGE SCAN                    | UK_PP_STD_PRICE_STDP_ID     |  4128 | 20640 |    23   (0)|       |       |        |      |            |
|  51 |          HASH GROUP BY                            |                             |     1 |   191 |  5578   (2)|       |       |  Q1,04 | PCWP |            |
|  52 |           PX RECEIVE                              |                             |     1 |   191 |  5578   (2)|       |       |  Q1,04 | PCWP |            |
|  53 |            PX SEND HASH                           | :TQ10003                    |     1 |   191 |  5578   (2)|       |       |  Q1,03 | P->P | HASH       |
|  54 |             HASH GROUP BY                         |                             |     1 |   191 |  5578   (2)|       |       |  Q1,03 | PCWP |            |
|* 55 |              FILTER                               |                             |       |       |            |       |       |  Q1,03 | PCWC |            |
|  56 |               NESTED LOOPS                        |                             |     1 |   191 |  5575   (2)|       |       |  Q1,03 | PCWP |            |
|  57 |                NESTED LOOPS                       |                             |     7 |  1176 |  5562   (2)|       |       |  Q1,03 | PCWP |            |
|* 58 |                 HASH JOIN                         |                             |    74 |  8584 |  1347   (3)|       |       |  Q1,03 | PCWP |            |
|  59 |                  BUFFER SORT                      |                             |       |       |            |       |       |  Q1,03 | PCWC |            |
|  60 |                   PX RECEIVE                      |                             |    60 |  3780 |    31   (7)|       |       |  Q1,03 | PCWP |            |
|  61 |                    PX SEND HASH                   | :TQ10000                    |    60 |  3780 |    31   (7)|       |       |        | S->P | HASH       |
|* 62 |                     HASH JOIN                     |                             |    60 |  3780 |    31   (7)|       |       |        |      |            |
|* 63 |                      TABLE ACCESS FULL            | POS_CUST_XREF               |    60 |  2100 |    25   (4)|       |       |        |      |            |
|* 64 |                      TABLE ACCESS FULL            | POS_DISTI_GROUP             |   100 |  2800 |     5   (0)|       |       |        |      |            |
|  65 |                  PX RECEIVE                       |                             |   345 | 18285 |  1316   (2)|       |       |  Q1,03 | PCWP |            |
|  66 |                   PX SEND HASH                    | :TQ10002                    |   345 | 18285 |  1316   (2)|       |       |  Q1,02 | P->P | HASH       |
|  67 |                    PX BLOCK ITERATOR              |                             |   345 | 18285 |  1316   (2)|       |       |  Q1,02 | PCWC |            |
|* 68 |                     TABLE ACCESS FULL             | PP_DEBIT_AUTHORIZATION      |   345 | 18285 |  1316   (2)|       |       |  Q1,02 | PCWP |            |
|  69 |                 PARTITION RANGE ALL               |                             |     1 |    52 |   205   (1)|     1 |    33 |  Q1,03 | PCWP |            |
|* 70 |                  INDEX RANGE SCAN                 | POS_PP_INVENTORY_PK         |     1 |    52 |   205   (1)|     1 |    33 |  Q1,03 | PCWP |            |
|* 71 |                VIEW                               | RPT_PRODUCT_VALUE_LEVEL     |     1 |    23 |     7   (0)|       |       |  Q1,03 | PCWP |            |
|  72 |                 UNION ALL PUSHED PREDICATE        |                             |       |       |            |       |       |  Q1,03 | PCWP |            |
|* 73 |                  FILTER                           |                             |       |       |            |       |       |  Q1,03 | PCWP |            |
|  74 |                   NESTED LOOPS OUTER              |                             |     1 |    46 |     2   (0)|       |       |  Q1,03 | PCWP |            |
|  75 |                    NESTED LOOPS OUTER             |                             |     1 |    42 |     2   (0)|       |       |  Q1,03 | PCWP |            |
|  76 |                     TABLE ACCESS BY INDEX ROWID   | SMA_STMODEL                 |     1 |    28 |     2   (0)|       |       |  Q1,03 | PCWP |            |
|* 77 |                      INDEX UNIQUE SCAN            | PK_SMA_STMODEL              |     1 |       |     1   (0)|       |       |  Q1,03 | PCWP |            |
|* 78 |                     INDEX UNIQUE SCAN             | PK_SEAEGO_PRODUCT_HIERARCHY |   298 |  4172 |     0   (0)|       |       |  Q1,03 | PCWP |            |
|* 79 |                    INDEX UNIQUE SCAN              | PK_S_CAP_GROUP              |     2 |     8 |     0   (0)|       |       |  Q1,03 | PCWP |            |
|  80 |                  NESTED LOOPS OUTER               |                             |     1 |    77 |     3   (0)|       |       |  Q1,03 | PCWP |            |
|  81 |                   NESTED LOOPS OUTER              |                             |     1 |    73 |     3   (0)|       |       |  Q1,03 | PCWP |            |
|  82 |                    NESTED LOOPS OUTER             |                             |     1 |    69 |     3   (0)|       |       |  Q1,03 | PCWP |            |
|  83 |                     NESTED LOOPS OUTER            |                             |     1 |    65 |     3   (0)|       |       |  Q1,03 | PCWP |            |
|  84 |                      NESTED LOOPS                 |                             |     1 |    51 |     3   (0)|       |       |  Q1,03 | PCWP |            |
|* 85 |                       TABLE ACCESS BY INDEX ROWID | SMA_PRODUCTMODEL            |     1 |    23 |     2   (0)|       |       |  Q1,03 | PCWP |            |
|* 86 |                        INDEX UNIQUE SCAN          | PK_SMA_PRODUCTMODEL         |     1 |       |     1   (0)|       |       |  Q1,03 | PCWP |            |
|  87 |                       TABLE ACCESS BY INDEX ROWID | SMA_STMODEL                 |     1 |    28 |     1   (0)|       |       |  Q1,03 | PCWP |            |
|* 88 |                        INDEX UNIQUE SCAN          | PK_SMA_STMODEL              |     1 |       |     0   (0)|       |       |  Q1,03 | PCWP |            |
|* 89 |                      INDEX UNIQUE SCAN            | PK_SEAEGO_PRODUCT_HIERARCHY |   298 |  4172 |     0   (0)|       |       |  Q1,03 | PCWP |            |
|* 90 |                     INDEX UNIQUE SCAN             | PK_S_FAMILY                 |  1366 |  5464 |     0   (0)|       |       |  Q1,03 | PCWP |            |
|* 91 |                    INDEX UNIQUE SCAN              | PK_S_CAP_GROUP              |     2 |     8 |     0   (0)|       |       |  Q1,03 | PCWP |            |
|* 92 |                   INDEX UNIQUE SCAN               | PK_F_MODPRODMGR             |    22 |    88 |     0   (0)|       |       |  Q1,03 | PCWP |            |
|* 93 |                  MAT_VIEW ACCESS BY INDEX ROWID   | RPT_PROD_MV                 |     1 |    24 |     2   (0)|       |       |  Q1,03 | PCWP |            |
|* 94 |                   INDEX UNIQUE SCAN               | IDX_RPT_PROD_MV_PROD_NO     |     1 |       |     1   (0)|       |       |  Q1,03 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  13 - access("G"."GROUP_DIST_NUMBER"="M"."DIST_NUMBER")
  14 - filter("G"."END_DATE">TO_DATE(:B2,'DD-MON-YYYY'))
  15 - access("C1"."CPT_PRICE_CODE"="S1"."CPT_PRICE_CODE")
  16 - filter("S1"."PRICE_PROTECTABLE"='Y')
  19 - access("C"."CPT_PRICE_CODE"="C1"."CPT_PRICE_CODE")
  20 - access("A"."CUST_PRICE_TYPE"="C"."CPT_BILL_CODE")
  21 - access("M"."SG_BILL_TO_CUST_NO"="A"."BILL_TO_CUST_NO")
  22 - filter("M"."SG_BILL_TO_CUST_NO" IS NOT NULL AND ("M"."INDIRECT_DISTI"='Y' OR "M"."CATEGORY_TYPE" LIKE 'DIRECT%') AND "M"."ACTIVE_IND"<>'N' AND 
              TRUNC(INTERNAL_FUNCTION("M"."ARCHIVE_DATE"))>TRUNC(SYSDATE@!))
  23 - filter("A"."START_DATE"<=TO_DATE(:B2,'DD-MON-YYYY') AND "A"."END_DATE">=TO_DATE(:B2,'DD-MON-YYYY'))
  25 - filter("C1"."CPT_PRICE_CAT" LIKE 'NB%')
  26 - access("P"."STDP_ID"=TO_NUMBER(:B1) AND "C"."CPT_PRICE_CODE"="P"."CUST_PRICE_TYPE")
       filter("C"."CPT_PRICE_CODE"="P"."CUST_PRICE_TYPE")
  27 - filter("L"."PROD_LEVEL"="P"."PROD_LEVEL")
  32 - access("ST"."MOD_DESC"="P"."PROD_VALUE")
  33 - access("ST"."MARKETING_NAME"="PH"."MARKETING_NAME"(+))
  34 - access("ST"."MOD_CAPACITY_FORMATTED"="SCG"."MOD_CAPACITY_FORMATTED"(+))
  41 - access("PM"."MODEL"="P"."PROD_VALUE")
  42 - access("SF"."FAMILY"(+)=SUBSTRB("PM"."MODEL",1,3))
  43 - access("PM"."DESIGN_APPLICATION"="DA"."DESIGN_APPLICATION"(+))
  45 - access("PM"."MOD_DESC"="ST"."MOD_DESC")
  46 - access("ST"."MARKETING_NAME"="PH"."MARKETING_NAME"(+))
  47 - access("ST"."MOD_CAPACITY_FORMATTED"="SCG"."MOD_CAPACITY_FORMATTED"(+))
  48 - access("PROD_NO"="P"."PROD_VALUE")
  50 - access("P1"."STDP_ID"=TO_NUMBER(:B1))
  55 - filter(TO_DATE(:B2,'DD-MON-YYYY')-6<=TO_DATE(:B2,'DD-MON-YYYY'))
  58 - access("G"."DIST_NUMBER"="P"."DIST_NUMBER")
  62 - access("G"."GROUP_DIST_NUMBER"="M"."DIST_NUMBER")
  63 - filter(("M"."INDIRECT_DISTI"='Y' OR "M"."CATEGORY_TYPE" LIKE 'DIRECT%') AND "M"."ACTIVE_IND"<>'N' AND 
              TRUNC(INTERNAL_FUNCTION("M"."ARCHIVE_DATE"))>TRUNC(SYSDATE@!))
  64 - filter("G"."END_DATE">TO_DATE(:B2,'DD-MON-YYYY'))
  68 - filter("P"."PRICE_TYPE"='I' AND "P"."POS_PP_FLAG"='Y' AND ("P"."POS_PROCESSED_FLAG"<>'C' OR "P"."POS_PROCESSED_FLAG" IS NULL) AND 
              "P"."BEGIN_DATE"<=TO_DATE(:B2,'DD-MON-YYYY') AND "P"."BEGIN_DATE">=TO_DATE(:B2,'DD-MON-YYYY')-6)
  70 - access("INVT"."DIST_NUMBER"="G"."GROUP_DIST_NUMBER" AND "INVT"."PPCF_SHOW_DATE"="P"."BEGIN_DATE")
       filter("INVT"."PPCF_SHOW_DATE"<=TO_DATE(:B2,'DD-MON-YYYY') AND "INVT"."PPCF_SHOW_DATE">=TO_DATE(:B2,'DD-MON-YYYY')-6 AND 
              "INVT"."PPCF_SHOW_DATE"="P"."BEGIN_DATE")
  71 - filter("L"."PROD_LEVEL"="P"."PROD_LEVEL")
  73 - filter("P"."PROD_VALUE"="INVT"."STMODEL")
  77 - access("ST"."MOD_DESC"="P"."PROD_VALUE")
  78 - access("ST"."MARKETING_NAME"="PH"."MARKETING_NAME"(+))
  79 - access("ST"."MOD_CAPACITY_FORMATTED"="SCG"."MOD_CAPACITY_FORMATTED"(+))
  85 - filter("PM"."MOD_DESC"="INVT"."STMODEL")
  86 - access("PM"."MODEL"="P"."PROD_VALUE")
  88 - access("ST"."MOD_DESC"="INVT"."STMODEL")
  89 - access("ST"."MARKETING_NAME"="PH"."MARKETING_NAME"(+))
  90 - access("SF"."FAMILY"(+)=SUBSTRB("PM"."MODEL",1,3))
  91 - access("ST"."MOD_CAPACITY_FORMATTED"="SCG"."MOD_CAPACITY_FORMATTED"(+))
  92 - access("PM"."DESIGN_APPLICATION"="DA"."DESIGN_APPLICATION"(+))
  93 - filter("MOD_DESC"="INVT"."STMODEL")
  94 - access("PROD_NO"="P"."PROD_VALUE")

Note
-----
   - 'PLAN_TABLE' is old version

PS: Вам удалось получить время работы? Также я изменяю запрос, пожалуйста, проверьте это и сообщите нам результат. В идеале первый запрос должен быть быстрее вашего, а второй (заменивший первый) должен быть лучше первого. Не забудьте хранить их отдельно с примечаниями по производительности.

просто предположите, но похоже, что ваше утверждение не использует индекс в столбце archive_date
попробуйте использовать

AND M.ARCHIVE_DATE > TRUNC (SYSDATE) + 1 - 1/24/60/60

вместо

AND TRUNC (M.ARCHIVE_DATE) > TRUNC (SYSDATE)

также вы должны опубликовать все описания таблиц под вопросом

SELECT                       /*+ PARALLEL (P,4) PARALLEL (L,4)*/
            DISTINCT
            G.GROUP_DIST_NUMBER,
            TO_DATE ( :B2,
                    'DD-MON-YYYY' )
                BEGIN_DATE,
            TO_DATE ( :B2,
                    'DD-MON-YYYY' )
                PRICE_DROP_DATE
       FROM
            POS_DISTI_GROUP G,
            POS_CUST_XREF M,
            S_CPT_SEQ_NO C,
            PP_STD_PRICE P,
            S_CPT_AUDIT A,
            RPT_PRODUCT_VALUE_LEVEL L
       WHERE 1=0 -- switched off
                G.END_DATE > TO_DATE ( :B2,
                                  'DD-MON-YYYY' )
            AND G.GROUP_DIST_NUMBER = M.DIST_NUMBER
            AND M.SG_BILL_TO_CUST_NO = A.BILL_TO_CUST_NO
            AND A.START_DATE <= TO_DATE ( :B2,
                                    'DD-MON-YYYY' )
            AND A.END_DATE >= TO_DATE ( :B2,
                                   'DD-MON-YYYY' )
            AND L.PROD_VALUE = P.PROD_VALUE
            AND L.PROD_LEVEL = P.PROD_LEVEL
            AND C.CPT_PRICE_CODE IN
                    (SELECT                    /*+  PARALLEL (P1,4)*/
                          DISTINCT C1.CPT_PRICE_CODE
                    FROM
                          PP_STD_PRICE P1,
                          S_CPT_PRICE_CODE C1,
                          S_CPT_SEQ_NO S1
                    WHERE
                             P1.STDP_ID = :B1
                          AND C1.CPT_PRICE_CAT LIKE 'NB%'
                          AND C1.CPT_PRICE_CODE = S1.CPT_PRICE_CODE
                          AND S1.PRICE_PROTECTABLE = 'Y')
            AND C.CPT_PRICE_CODE = P.CUST_PRICE_TYPE
            AND P.STDP_ID = :B1
            AND A.CUST_PRICE_TYPE = C.CPT_BILL_CODE
            AND M.ACTIVE_IND != 'N'
            AND ( M.CATEGORY_TYPE LIKE 'DIRECT%'
                OR M.INDIRECT_DISTI = 'Y' )
            AND TRUNC ( M.ARCHIVE_DATE ) > TRUNC ( SYSDATE )
       UNION
       SELECT                      /*+  PARALLEL (P,4) PARALLEL (L,4) */
            G.GROUP_DIST_NUMBER,
            P.BEGIN_DATE,
            MIN ( INVT.PRICE_DROP_DATE ) PRICE_DROP_DATE
       FROM
            POS_DISTI_GROUP G,
            POS_CUST_XREF M,
            PP_DEBIT_AUTHORIZATION P,
            RPT_PRODUCT_VALUE_LEVEL L,
            POS_PP_INVENTORY PARTITION ("F2011_Q2") INVT
       WHERE 1=0 -- switched off
                G.END_DATE > TO_DATE ( :B2,
                                  'DD-MON-YYYY' )
            AND G.GROUP_DIST_NUMBER = M.DIST_NUMBER
            AND M.ACTIVE_IND != 'N'
            AND ( M.CATEGORY_TYPE LIKE 'DIRECT%'
                OR M.INDIRECT_DISTI = 'Y' )
            AND G.DIST_NUMBER = P.DIST_NUMBER
            AND L.PROD_VALUE = P.PROD_VALUE
            AND L.PROD_LEVEL = P.PROD_LEVEL
            AND P.BEGIN_DATE >= TO_DATE ( :B2,
                                    'DD-MON-YYYY' )
                            - 6
            AND P.BEGIN_DATE <= TO_DATE ( :B2,
                                    'DD-MON-YYYY' )
            AND INVT.DIST_NUMBER = G.GROUP_DIST_NUMBER
            AND INVT.STMODEL = L.MOD_DESC
            AND INVT.PPCF_SHOW_DATE = P.BEGIN_DATE
            AND P.PRICE_TYPE = 'I'
            AND ( P.POS_PROCESSED_FLAG IS NULL
                OR P.POS_PROCESSED_FLAG != 'C' )
            AND P.POS_PP_FLAG = 'Y'
            AND TRUNC ( M.ARCHIVE_DATE ) > TRUNC ( SYSDATE )
       GROUP BY
            G.GROUP_DIST_NUMBER,
            P.BEGIN_DATE

Результат OP:

После попытки выключить и включить здесь результат 1. Выключить 1-е место, где условие

2.Выключить 2-е условие

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