Использование dynamic sampling с временными таблицами

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

С помощью трассировки 10046 удалось установить наличие подобных приведенному ниже проблемных запросов

INSERT INTO TMP_CALC_XXX_ROWS ...
SELECT /*+ dynamic_sampling(5)*/ ...
FROM TMP_CXX_CALC_PRMS, TMP_CALC_XXX_ROWS 
  WHERE ...


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1   1985.84    1944.19        404    6915999      39854       18197
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2   1985.84    1944.19        404    6915999      39854       18197

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 223     (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
  18197  HASH GROUP BY (cr=6915137 pr=0 pw=0 time=1329164718 us)
  37079   NESTED LOOPS  (cr=6915137 pr=0 pw=0 time=1941458873 us)
  18197    TABLE ACCESS FULL TMP_CXX_CALC_PRMS (cr=210 pr=0 pw=0 time=109235 us)
  37079    INLIST ITERATOR  (cr=6914927 pr=0 pw=0 time=1941202535 us)
  37079     TABLE ACCESS BY INDEX ROWID TMP_CALC_XXX_ROWS (cr=6914927 pr=0 pw=0 time=1940822545 us)
680003693      INDEX RANGE SCAN XIF_TCPR_SET2 (cr=1310251 pr=0 pw=0 time=680812352 us)(object id 1059417) 

Таблицы TMP_CXX_CALC_PRMS и TMP_CALC_XXX_ROWS - обе global temporary, статистика по ним не собирается, поскольку все вычисления - одна транзакция, а таблицы по ходу исполнения неоднократно обновляются (пришлось бы достаточно часто перезапускать процедуры сбора статистики, что на этапе разработки казалось слишком дорогостоящим).
Путем несложных рассуждений, можно сформулировать более оптимальный план для этого запроса, а именно: полный просмотр обеих таблиц с последующим HASH-соединением.
Собственно, проблема состоит в том, что оптимизатор сам почему-то не догадался применить такой план. Поиск причин этого - и есть предмет данной статьи.

Перед тем, как начать свои исследования, я попытался набросать план действий. Вышло примерно следующее:
1) попытаться создать простой test case, который бы воспроизводил проблему;
2) попытаться сузить круг условий, при которых проблема проявляется;
3) трассировать оптимизатор (10053), чтобы понять причины происходящего.

Архив с тестовыми скриптами можно скачать отсюда.
Итак, по порядку.
Во всех скриптах используются одинаковые по структуре таблицы с одинаковым наполнением. Их можно воспроизвести на любой системе.
Скрипт _case1.sql показывает, как влияет агрессивность динамического семплирования на план исполнения. В _case1.log представлены соответствующие результаты: четко видно, что без семплирования, используя дефолтные значения (напомню: статистика отсутствует), оптимизатор генерирует приличный план (LIO=3968), во всех остальных случаях - LIO~240509. Разница очень существенна, да и по планам видно, что имитация проблемы промышленной системы, в общем, удалась.

Следующий эксперимент - _case2.sql и результаты _case2.log: все то же самое, но таблицы обычные, не временные. Здесь картина полностью противоположная: без семплирвоания - крах, с семплированием - очень даже неплохо! Такая ситуация наталкивает на мысль, что динамическое семплирование для временных таблиц работает как-то по-другому.

Идем далее - _case3.sql и _case3.log: здесь попытка сравнить запрос без семплирования с такими же запросами, как в первом експерименте (без семплирования и с оным), но предварительно собрав статистику. Видно, что со статистикой мы получаем более-менее нормальный план. Единственное, что в моем промышленном случае вот так собрать статистику - нет возможности.

Ну и последний эксперимент - без статистики, максимально агрессивное семплирование, с трассировкой оптимизатора: _case4.sql, _case4.log - временная таблица; _case5.sql, _case5.log - постоянная таблица. Соответствующие трассы оптимизатора - _case4_wo_stats.trc и _case5_wo_stats.trc.
Как и следовало ожидать, для временной таблицы - план не оптимален, для постоянной - оптимален.
Здесь, как говорится, начинается развлечение для интеллектуалов - найти разницу в двух трассировочных файлах, чтобы понять причину разного поведения CBO. После нескольких потраченных часов начала вырисовываться картина происходящего. В общем, первая существенная разница видна в том, что Join order[2] секции SM Join (with index on outer) для временной таблицы признан победителем, тогда как для постоянной - эта секция показывает существенно более высокую стоимость 2441, против 885 для временной.
Далее я пытался понять причину такой разницы (остальные способы соединения и доступа больше можно не рассматривать, поскольку очень вероятно, что в них нет ничего интересного). Вернувшись в начало файлов, я обнаружил одну интересную деталь:
1) для временных таблиц была такая запись "** Dynamic sampling could not determine index block count: TT_TEST_CBO1_IDX2";
2) для постоянных таблиц - "** Dynamic sampling updated index stats.: TT_TEST_CBO1_IDX2, blocks=1580".
Проверяя стоимости планов с калькулятором и выясняя откуда взялись стоимости, становится ясно, что для временной таблицы - ввиду указанной выше записи - были использованы дефолтные значения статистики индекса, а именно:

Index Stats::
  Index: TT_TEST_CBO1_IDX2  Col#: 2    (NOT ANALYZED)
    LVLS: 1  #LB: 25  #DK: 100  LB/K: 1.00  DB/K: 1.00  CLUF: 800.00

а для постоянной - все тоже самое

Index Stats::
  Index: TT_TEST_CBO1_IDX2  Col#: 2    (NOT ANALYZED)
    LVLS: 1  #LB: 25  #DK: 100  LB/K: 1.00  DB/K: 1.00  CLUF: 800.00 

но статистика #LB была определена в процессе динамического семплирования, и равна 1580.
Вот откуда и получилась заниженная стоимость доступа по индексу для временной таблицы со всеми вытекающими последствиями!

Для временной таблицы:

SM Join (with index on outer)
  Access Path: index (FullScan)
    Index: TT_TEST_CBO1_IDX2
    resc_io: 826.00  resc_cpu: 7132309
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 826.65  Resp: 826.65  Degree: 1
  Outer table: 
    resc: 826.65  card 33428.00  bytes: 28  deg: 1  resp: 826.65

стоимость = 1+25*1+800*1 = 826!


Для постоянной -

SM Join (with index on outer)
  Access Path: index (FullScan)
    Index: TT_TEST_CBO1_IDX2
    resc_io: 2381.00  resc_cpu: 18206149
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 2382.65  Resp: 2382.65  Degree: 1
  Outer table: 
    resc: 2382.65  card 33428.00  bytes: 28  deg: 1  resp: 2382.65

стоимость = 1+1580*1+800*1 = 2381! (Учитываем округления!)

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

Остается единственный вопрос: почему для временной таблицы динамическое семплирование собирает меньше информации, чем для постоянной?

Литератрура:
Jonathan Lewis. Cost-Based Oracle Fundamentals.
Том Кайт: о выборке, хранении и индексировании

Агрегаты в один проход

Обратился недавно ко мне мой коллега за помощью по настройке запроса. Тот запрос состоял из двух частей: 1) tab2 - соединение 5 таблиц, таких соединений было два, объединенных оператором union, вся эта конструкция предоставляла исходные данные для агрегации; 2) tab1 - "синтетическая" таблица, поставляющая 17 строк - показателей агрегации. Указанные две части соединялись и к результату соединения применялась операция group by.
tab2 - приводится с сокращениями, поскольку в текущем повествовании детали реализации этой части не существенны и не ставится цель оптимизировать ее (это отдельная тема для дискуссии с дизайнером структуры этих таблиц).

with tab1 as
  (select 1 prm, '100' s_code, 0 as s_min, 100 as s_max from dual
    union all
   select 1 prm, '101_150' s_code, 100 as s_min, 150 as s_max from dual
   --и так 17 раз)
select
      s_code, count(pnf) s_cnt, sum(summ) s_sum, avg(summ) s_avg
 from tab1,
     (select summ,...
        from mpayment,pculars prt, pnfile pf, PnfParams pp, PnfParams pp2
       where ...
       union
      select summ,...
        from pslist pl, pculars prt, pnfile, PnfParams pp,PnfParams pp2
       where ...) tab2
 where
      (prm = 1 and  summ > s_min  and  summ <= s_max)
   or (prm = 2 and p_tp = s_code)
   or (prm = 3 and s_code = 'les_min' and summ < 406)
   or (prm = 3 and s_code = 'eq_min' and summ = 406)
   or (prm = 3 and s_code = 'mor_min' and summ > 406)
   or (prm = 4)
   or (prm = 5 and w_st = 1) 
 group by s_code;

Explain plan для данного запроса выглядит так:

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                      |  6539 |   357K|       |  1397K  (1)| 04:39:26 |
|   1 |  HASH GROUP BY                            |                      |  6539 |   357K|       |  1397K  (1)| 04:39:26 |
|   2 |   NESTED LOOPS                            |                      |  6539 |   357K|       |  1397K  (1)| 04:39:26 |
|   3 |    VIEW                                   |                      | 37589 |  1468K|       |   118K  (2)| 00:23:45 |
|   4 |     SORT UNIQUE                           |                      | 37589 |  2973K|  7448K|   118K (22)| 00:23:45 |
|   5 |      UNION-ALL                            |                      |       |       |       |            |          |
|*  6 |       FILTER                              |                      |       |       |       |            |          |
|*  7 |        HASH JOIN OUTER                    |                      | 37588 |  2973K|  2608K| 94545   (3)| 00:18:55 |
|*  8 |         FILTER                            |                      |       |       |       |            |          |
|*  9 |          HASH JOIN OUTER                  |                      | 37588 |  2165K|  1800K| 52872   (3)| 00:10:35 |
|* 10 |           HASH JOIN                       |                      | 37588 |  1358K|  1360K| 11304   (4)| 00:02:16 |
|* 11 |            HASH JOIN                      |                      | 37588 |   917K|  1104K|  6823   (6)| 00:01:22 |
|* 12 |             TABLE ACCESS FULL             | MPAYMENT             | 37588 |   660K|       |  4404   (8)| 00:00:53 |
|* 13 |             TABLE ACCESS FULL             | PNFILE               |   612K|  4188K|       |  1803   (3)| 00:00:22 |
|  14 |            TABLE ACCESS FULL              | PCULARS              |   607K|  7122K|       |  3713   (2)| 00:00:45 |
|* 15 |           TABLE ACCESS FULL               | PNFPARAMS            |   544K|    11M|       | 40595   (3)| 00:08:08 |
|* 16 |         TABLE ACCESS FULL                 | PNFPARAMS            |   584K|    12M|       | 40595   (3)| 00:08:08 |
|* 17 |       FILTER                              |                      |       |       |       |            |          |
|* 18 |        FILTER                             |                      |       |       |       |            |          |
|  19 |         NESTED LOOPS OUTER                |                      |  1516 |   130K|       | 15118   (1)| 00:03:02 |
|* 20 |          FILTER                           |                      |       |       |       |            |          |
|  21 |           NESTED LOOPS OUTER              |                      |  1516 |    97K|       | 10703   (1)| 00:02:09 |
|  22 |            NESTED LOOPS                   |                      |  1516 | 66704 |       |  6289   (1)| 00:01:16 |
|  23 |             MERGE JOIN                    |                      |  1516 | 48512 |       |  3254   (2)| 00:00:40 |
|* 24 |              TABLE ACCESS BY INDEX ROWID  | PNFILE               |   612K|  4188K|       |  1698   (1)| 00:00:21 |
|* 25 |               INDEX FULL SCAN             | XPK_PТFILE           | 30890 |       |       |  1306   (1)| 00:00:16 |
|* 26 |                TABLE ACCESS BY INDEX ROWID| MPAYMENT             |     1 |    18 |       |     7   (0)| 00:00:01 |
|* 27 |                 INDEX RANGE SCAN          | XIF1561_MPAYMENT     |     8 |       |       |     3   (0)| 00:00:01 |
|* 28 |              SORT JOIN                    |                      |  1516 | 37900 |       |  1555   (3)| 00:00:19 |
|* 29 |               TABLE ACCESS FULL           | PSLIST               |  1516 | 37900 |       |  1554   (3)| 00:00:19 |
|  30 |             TABLE ACCESS BY INDEX ROWID   | PCULARS              |     1 |    12 |       |     2   (0)| 00:00:01 |
|* 31 |              INDEX UNIQUE SCAN            | XAK1_PCULARS         |     1 |       |       |     1   (0)| 00:00:01 |
|* 32 |            TABLE ACCESS BY INDEX ROWID    | PNFPARAMS            |     1 |    22 |       |     3   (0)| 00:00:01 |
|* 33 |             INDEX RANGE SCAN              | IDX$$_0EFF0002       |     1 |       |       |     2   (0)| 00:00:01 |
|* 34 |          TABLE ACCESS BY INDEX ROWID      | PNFPARAMS            |     1 |    22 |       |     3   (0)| 00:00:01 |
|* 35 |           INDEX RANGE SCAN                | IDX$$_0EFF0002       |     1 |       |       |     2   (0)| 00:00:01 |
|  36 |        SORT AGGREGATE                     |                      |     1 |    15 |       |            |          |
|* 37 |         TABLE ACCESS BY INDEX ROWID       | PSLIST               |     1 |    15 |       |     4   (0)| 00:00:01 |
|* 38 |          INDEX RANGE SCAN                 | IFK_PNL_PNF          |     1 |       |       |     3   (0)| 00:00:01 |
|* 39 |    VIEW                                   |                      |     1 |    16 |       |    34   (0)| 00:00:01 |
|  40 |     UNION-ALL                             |                      |       |       |       |            |          |
|  41 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  42 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  43 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  44 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  45 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  46 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  47 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  48 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  49 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  50 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  51 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  52 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  53 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  54 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  55 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  56 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
|  57 |      FAST DUAL                            |                      |     1 |       |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------

С 3-го по 38-ой шаги по причинам указанным выше предлагаю не не рассматривать углубленно.

Собственно, зацепкой для дальнейшего поиска был скачок стоимости на шаге 2. Как учил К. Милшап: прилагать усилия нужно в направлении наибольшей отдачи. Поэтому я попытался исследовать именно эту особенность плана. Следующим шагом - собрал статистику времени исполнения, которая должна подтвердить (или опровергнуть) наличие проблемного места в запросе (оставляю только часть плана, которая имеет отношение к дальнейшему изложению):

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY                            |                      |      1 |   6539 |     17 |00:06:00.33 |    4495K|
|   2 |   NESTED LOOPS                            |                      |      1 |   6539 |   2448K|00:05:54.91 |    4495K|
|   3 |    VIEW                                   |                      |      1 |  37589 |    590K|00:02:02.82 |    4495K|
--------------------------
пропущено
--------------------------
|* 39 |    VIEW                                   |                      |    590K|      1 |   2448K|00:03:42.54 |       0 |
|  40 |     UNION-ALL                             |                      |    590K|        |     10M|00:03:13.55 |       0 |
|  41 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
|  42 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
|  43 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
|  44 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
|  45 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
|  46 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
|  47 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
|  48 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
|  49 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
|  50 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
|  51 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
|  52 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
|  53 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
|  54 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
|  55 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
|  56 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.45 |       0 |
|  57 |      FAST DUAL                            |                      |    590K|      1 |    590K|00:00:03.44 |       0 |
----------------------------------------------------------------------------------------------------------------------------

Такой отчет получен после исполнения запроса с использованием подсказки gather_plan_statistics и последующего вызова
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Статистика времени выполнения подтверждает наличие ресурсоемкого шага 2, хотя прогноз оптимизатора был явно преувеличен. Колонка Starts показывает количество запусков соответствующих шагов, очевидно что шаги, связанные с обработкой "синтетической" таблицы достаточно дорогостоящи.

Собранная информация дает повод предположить, что выбраный способ получения агрегатных значений, а именно соединение исходных данных с "синтетической" таблицей с последующей агрегацией, является не эффективным, следовательно от него нужно избавляться.
Сделать это можно достаточно просто: например воспользоваться функцией decode, а в моем случае больше подойдет case expression, так как условия вычисления параметров достаточно сложны. Итак, я предложил своему коллеге переписать запрос вот таким способом:

select 
  -- Первый показатель
  sum(sum_0_100) cnt__0_100,
  sum(s_sum_0_100) sum__0_100,
  avg(s_sum_0_100) avg__0_100,
  -- Второй показатель
  sum(sum_100_150) cnt__100_150,
  sum(s_sum_100_150) sum__100_150,
  avg(s_sum_100_150) avg__100_150,
  -- Третий показатель
  sum(sum_150_200) cnt__150_200,
  sum(s_sum_150_200) sum__150_200,
  avg(s_sum_150_200) avg__150_200
  -- Остальные оставил коллеге реализовать самостоятельно
from (
select 
  case when summ<=100 then 1 else 0 end sum_0_100,
  case when summ<=100 then summ else null end s_sum_0_100,
  case when summ>100 and summ<=150 then 1 else 0 end sum_100_150,
  case when summ>100 and summ<=150 then summ else null end s_sum_100_150,
  case when summ>150 and summ<=200 then 1 else 0 end sum_150_200,
  case when summ>150 and summ<=200 then summ else null end s_sum_150_200
from
(
  select summ,...
    from mpayment, pculars prt, pnfile pf, PnfParams pp, PnfParams pp2
   where ...
   union
  select summ,...
    from pslist pl, pculars prt, pnfile, PnfParams pp, PnfParams pp2
   where ...
);

в итоге план времени выполнения имеет вид

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                         |                  |      1 |      1 |      1 |00:01:28.50 |    1447K|
|   2 |   VIEW                                  |                  |      1 |  37589 |    590K|00:01:26.29 |    1447K|
|   3 |    SORT UNIQUE                          |                  |      1 |  37589 |    590K|00:01:25.11 |    1447K|
|   4 |     UNION-ALL                           |                  |      1 |        |    590K|00:01:21.17 |    1447K|
|*  5 |      FILTER                             |                  |      1 |        |    501K|00:00:42.74 |     421K|
|*  6 |       HASH JOIN OUTER                   |                  |      1 |  37588 |    501K|00:00:41.25 |     421K|
|*  7 |        FILTER                           |                  |      1 |        |    501K|00:00:30.47 |     232K|
|*  8 |         HASH JOIN OUTER                 |                  |      1 |  37588 |    501K|00:00:28.48 |     232K|
|*  9 |          HASH JOIN                      |                  |      1 |  37588 |    501K|00:00:17.50 |   43938 |
|* 10 |           HASH JOIN                     |                  |      1 |  37588 |    501K|00:00:10.25 |   26878 |
|* 11 |            TABLE ACCESS FULL            | MPAYMENT         |      1 |  37588 |    502K|00:00:02.05 |   18797 |
|* 12 |            TABLE ACCESS FULL            | PNFILE           |      1 |    612K|    612K|00:00:01.23 |    8081 |
|  13 |           TABLE ACCESS FULL             | PCULARS          |      1 |    607K|    617K|00:00:01.24 |   17060 |
|* 14 |          TABLE ACCESS FULL              | PNFPARAMS        |      1 |    544K|    555K|00:00:04.81 |     188K|
|* 15 |        TABLE ACCESS FULL                | PNFPARAMS        |      1 |    584K|    555K|00:00:05.02 |     188K|
|* 16 |      FILTER                             |                  |      1 |        |  89790 |00:00:35.03 |    1025K|
|  17 |       HASH GROUP BY                     |                  |      1 |      1 |  90274 |00:00:34.76 |    1025K|
|* 18 |        HASH JOIN                        |                  |      1 |   1430 |  90900 |00:00:33.39 |    1025K|
|* 19 |         FILTER                          |                  |      1 |        |  92035 |00:00:28.88 |    1018K|
|  20 |          NESTED LOOPS OUTER             |                  |      1 |   1426 |  92041 |00:00:28.51 |    1018K|
|* 21 |           FILTER                        |                  |      1 |        |  92035 |00:00:24.55 |     686K|
|  22 |            NESTED LOOPS OUTER           |                  |      1 |   1426 |  92114 |00:00:24.06 |     686K|
|  23 |             NESTED LOOPS                |                  |      1 |   1426 |  92035 |00:00:19.49 |     354K|
|* 24 |              HASH JOIN ANTI             |                  |      1 |   1426 |  92035 |00:00:16.45 |   33816 |
|* 25 |               HASH JOIN                 |                  |      1 |   1516 |    590K|00:00:08.99 |   15001 |
|* 26 |                TABLE ACCESS FULL        | PSLIST           |      1 |   1516 |    595K|00:00:01.19 |    6927 |
|* 27 |                TABLE ACCESS FULL        | PNFILE           |      1 |    612K|    612K|00:00:01.23 |    8074 |
|* 28 |               TABLE ACCESS FULL         | MPAYMENT         |      1 |  37588 |    502K|00:00:01.56 |   18815 |
|  29 |              TABLE ACCESS BY INDEX ROWID| PCULARS          |  92035 |      1 |  92035 |00:00:02.56 |     320K|
|* 30 |               INDEX UNIQUE SCAN         | XAK1_PCULARS     |  92035 |      1 |  92035 |00:00:01.15 |     184K|
|* 31 |             TABLE ACCESS BY INDEX ROWID | PNFPARAMS        |  92035 |      1 |  54328 |00:00:03.84 |     332K|
|* 32 |              INDEX RANGE SCAN           | IDX$$_0EFF0002   |  92035 |      1 |  54629 |00:00:01.75 |     277K|
|* 33 |           TABLE ACCESS BY INDEX ROWID   | PNFPARAMS        |  92035 |      1 |  54255 |00:00:03.48 |     331K|
|* 34 |            INDEX RANGE SCAN             | IDX$$_0EFF0002   |  92035 |      1 |  54566 |00:00:01.55 |     277K|
|* 35 |         TABLE ACCESS FULL               | PSLIST           |      1 |    604K|    604K|00:00:01.21 |    6925 |
----------------------------------------------------------------------------------------------------------------------

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

P.S. Описанные выше упражнения происходили на версии Oracle 10gR2 (10.2.0.1).