Oracle mechanics

02.08.2010

ORA-01652: unable to extend temp segment

Filed under: CBO,hints,Oracle,temp segment — Игорь Усольцев @ 23:34
Tags: ,

С определённого момента с грустной ошибкой ORA-01652: unable to extend temp segment перестал выполняться тяжёлый запрос, ежесуточно выполняемый в  PL/SQL job’е и формирующий отчётную таблицу с начала времён за весь отчётный период

Можно  в соответствиями с простейшей рекомендацией Oracle увеличить размер временного табличного пространства, но при достаточно большом размере существующего — 10 ГБ это не всегда допустимо и, кроме того, это пагубная практика просто некрасивое решение

Можно пересмотреть логику приложения и вместо еженощного полного перезаполнения вспомогательной таблицы раз в сутки дополнять собранные данные — будет использоваться временный сегмент меньшего размера, однако, потребуется модификация структуры таблиц и отчётных запросов :(

Ну и наконец, можно попробовать модифицировать запрос с целью уменьшения требуемого размера временного сегмента (temporary segment), точнее, с этого нужно начинать ;)

Первоначальный запрос, потребовавший для выполнения более 10 GB временного табличного пространства :(

SQL> insert/*+ APPEND*/  into ALSO_PROGRAM
2  select /*+ ALL_ROWS*/ PROGRAM_ID, OPTION_ID, COUNT_OPTION_ID from (
3  SELECT I.PROGRAM_ID PROGRAM_ID
4  , B1.OPTION_ID OPTION_ID
5  , COUNT(B1.OPTION_ID) COUNT_OPTION_ID
6  , row_number() over (partition by PROGRAM_ID order by COUNT(B1.OPTION_ID) desc) rn
7    FROM ITEM I, OPTION O, BASKET B1, ORDER ORD1,
8        (select ORD.COMPANY_ID, B.OPTION_ID
9         from BASKET B, ORDER ORD
10         where ORD.ID = B.ORDER_ID
11         AND ORD.PAID_UP = 'Y') BORD
12   WHERE ORD1.ID = B1.ORDER_ID
13     AND ORD1.COMPANY_ID = BORD.COMPANY_ID
14     AND O.ID = BORD.OPTION_ID
15     AND ORD1.PAID_UP = 'Y'
16     AND O.ITEM_ID = I.ID
17     AND B1.BUNDLE_OPTION_ID IS NULL
18  GROUP BY I.PROGRAM_ID, B1.OPTION_ID
19  ) where rn <= 5;
insert/*+ APPEND*/ into ALSO_PROGRAM
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Elapsed: 00:04:12.16

Ещё раз (также неудачно) выполнив запрос с подсказкой gather_plan_statistics посмотрим на статистику выполнения запроса, точнее на ту часть статистики, которая была собрана до возникновения ошибки

-------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                      | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------------------------
|   1 |  LOAD AS SELECT                  |                           |      1 |        |      0 |
|*  2 |   VIEW                           |                           |      1 |   1250K|      0 |
|*  3 |    WINDOW SORT PUSHED RANK       |                           |      1 |   1250K|      0 |
|   4 |     HASH GROUP BY                |                           |      1 |   1250K|      0 |
|*  5 |      HASH JOIN                   |                           |      1 |   1250K|    171M|
|*  6 |       INDEX FAST FULL SCAN       | IX_BASKET                 |      1 |   2909K|   2910K|
|*  7 |       HASH JOIN                  |                           |      1 |   1095K|    233M|
|*  8 |        VIEW                      | index$_join$_006          |      1 |    865K|   1569K|
|*  9 |         HASH JOIN                |                           |      1 |        |   1569K|
|* 10 |          HASH JOIN               |                           |      1 |        |   1569K|
|* 11 |           INDEX RANGE SCAN       | IX_ORDER_PAID_UP          |      1 |    865K|   1569K|
|  12 |           INDEX FAST FULL SCAN   | IX_ACCOUNT_ID             |      1 |    865K|   2597K|
|  13 |          INDEX FAST FULL SCAN    | IX_ORDER_DELIVERY         |      1 |    865K|   2597K|
|* 14 |        HASH JOIN                 |                           |      1 |   1000K|   1591K|
|  15 |         INDEX FAST FULL SCAN     | IX_ITEM_PROGRAM_ID        |      1 |  99508 |  99553 |
|* 16 |         HASH JOIN                |                           |      1 |   1000K|   1591K|
|  17 |          INDEX FAST FULL SCAN    | IX_OPTION_ITEM_ID         |      1 |    286K|    286K|
|* 18 |          HASH JOIN               |                           |      1 |   1000K|   1591K|
|* 19 |           VIEW                   | index$_join$_009          |      1 |    865K|   1569K|
|* 20 |            HASH JOIN             |                           |      1 |        |   1569K|
|* 21 |             HASH JOIN            |                           |      1 |        |   1569K|
|* 22 |              INDEX RANGE SCAN    | IX_ORDER_PAID_UP          |      1 |    865K|   1569K|
|  23 |              INDEX FAST FULL SCAN| IX_ACCOUNT_ID             |      1 |    865K|   2597K|
|  24 |             INDEX FAST FULL SCAN | IX_ORDER_DELIVERY         |      1 |    865K|   2597K|
|  25 |           INDEX FAST FULL SCAN   | IX_BASKET_ORDER_OPTION    |      1 |   3002K|   2678K|
-------------------------------------------------------------------------------------------------

Судя по собранной статистике выполнения запроса — ожидаемое E-rows=1250K и полученное количество строк A-rows=171M на шаге 5 (где и произошла ошибка) отличаются более, чем в 100 раз (!) — т.е. план выполнения построен на неточных или неполных данных статистики и, видимо, мог бы быть улучшен оптимизатором Oracle автоматически, но сейчас не об этом, попробуем уменьшить потребность во временном дисковом пространстве для hash операций модифицируя текст запроса:

Вариант №1 Выполнить запрос в два этапа с использованием промежуточной временной таблицы (global temporary table GTT)

SQL> create global temporary table ALSO_PROGRAM2 ON COMMIT PRESERVE ROWS
as
select /*+ ALL_ROWS gather_plan_statistics*/ ORD.COMPANY_ID, B.OPTION_ID
from BASKET B, ORDER ORD
where ORD.ID = B.ORDER_ID
AND ORD.PAID_UP = 'Y';  2    3    4    5    6

Table created.
Elapsed: 00:00:44.12

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                      | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
----------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  LOAD AS SELECT          |                           |      1 |        |       |       |            |          |      1 |00:00:43.06 |
|*  2 |   HASH JOIN              |                           |      1 |   1026K|    27M|    24M| 27940   (1)| 00:05:36 |   1856K|00:00:38.04 |
|*  3 |    VIEW                  | index$_join$_002          |      1 |    887K|    14M|       | 20514   (1)| 00:04:07 |   1616K|00:00:20.56 |
|*  4 |     HASH JOIN            |                           |      1 |        |       |       |            |          |   1616K|00:00:17.32 |
|*  5 |      INDEX RANGE SCAN    | IX_ORDER_PAID_UP          |      1 |    887K|    14M|       | 26768   (1)| 00:05:22 |   1616K|00:00:01.62 |
|   6 |      INDEX FAST FULL SCAN| ORDER_ID_LID_COMID_IDX    |      1 |    887K|    14M|       | 11865   (1)| 00:02:23 |   2664K|00:00:02.66 |
|   7 |    INDEX FAST FULL SCAN  | IX_   BASKET_ORDER_OPTION |      1 |   3078K|    32M|       |  2835   (1)| 00:00:35 |   3079K|00:00:03.08 |
----------------------------------------------------------------------------------------------------------------------------------------------

SQL> select segtype, blocks from gv$tempseg_usage;

SEGTYPE       BLOCKS
--------- ----------
DATA            3968

последний запрос даёт размер сегмента временной таблицы = 3968*8k ~ 32MB

Пробуем выполнить запрос с использованием созданной временной таблицы

SQL> insert/*+ APPEND */ into ALSO_PROGRAM
2  select /*+ ALL_ROWS*/ PROGRAM_ID, OPTION_ID, COUNT_OPTION_ID from (
3  SELECT I.PROGRAM_ID PROGRAM_ID
4  , B1.OPTION_ID OPTION_ID
, COUNT(B1.OPTION_ID) COUNT_OPTION_ID
5    6  , row_number() over (partition by PROGRAM_ID order by COUNT(B1.OPTION_ID) desc) rn
7    FROM ITEM I, OPTION O, BASKET B1, ORDER ORD1, ALSO_PROGRAM2 BORD
8  WHERE ORD1.ID = B1.ORDER_ID
9     AND ORD1.COMPANY_ID = BORD.COMPANY_ID
10  AND O.ID = BORD.OPTION_ID
11     AND ORD1.PAID_UP = 'Y'
12     AND O.ITEM_ID = I.ID
13     AND B1.BUNDLE_OPTION_ID IS NULL
14  GROUP BY I.PROGRAM_ID, B1.OPTION_ID
15  ) where rn <= 5;

Elapsed: 00:36:08.40

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                      | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  LOAD AS SELECT              |                           |      1 |        |       |       |            |          |      1 |00:01:58.68 |
|*  2 |   VIEW                       |                           |      1 |   2605K|   129M|       |   142K  (1)| 00:28:29 |  84802 |00:01:58.25 |
|*  3 |    WINDOW SORT PUSHED RANK   |                           |      1 |   2605K|   188M|   442M|   142K  (1)| 00:28:29 |    109K|00:01:58.04 |
|   4 |     HASH GROUP BY            |                           |      1 |   2605K|   188M|   442M|   142K  (1)| 00:28:29 |   4798K|00:01:46.74 |
|*  5 |      HASH JOIN               |                           |      1 |   2605K|   188M|    71M| 49681   (1)| 00:09:57 |    303M|00:26:01.79 |
|*  6 |       INDEX FAST FULL SCAN   | IX_BASKET                 |      1 |   2982K|    36M|       |  4738   (1)| 00:00:57 |   2983K|00:00:05.98 |
|*  7 |       HASH JOIN              |                           |      1 |   2285K|   137M|    24M| 33282   (1)| 00:06:40 |    258M|00:04:47.41 |
|*  8 |        VIEW                  | index$_join$_006          |      1 |    887K|    14M|       | 20514   (1)| 00:04:07 |   1616K|00:00:20.87 |
|*  9 |         HASH JOIN            |                           |      1 |        |       |       |            |          |   1616K|00:00:17.64 |
|* 10 |          INDEX RANGE SCAN    | IX_ORDER_PAID_UP          |      1 |    887K|    14M|       | 26768   (1)| 00:05:22 |   1616K|00:00:01.62 |
|  11 |          INDEX FAST FULL SCAN| ORDER_ID_LID_COMID_IDX    |      1 |    887K|    14M|       | 11865   (1)| 00:02:23 |   2664K|00:00:02.66 |
|* 12 |        HASH JOIN             |                           |      1 |   2087K|    91M|  9264K|  5808   (1)| 00:01:10 |   1856K|00:00:12.37 |
|* 13 |         HASH JOIN            |                           |      1 |    296K|  5785K|  2200K|   720   (1)| 00:00:09 |    296K|00:00:01.89 |
|  14 |          INDEX FAST FULL SCAN| IX_ITEM_PROGRAM_ID        |      1 |    102K|   998K|       |    66   (0)| 00:00:01 |    102K|00:00:00.10 |
|  15 |          INDEX FAST FULL SCAN| IX_OPTION_ITEM_ID         |      1 |    296K|  2892K|       |   235   (1)| 00:00:03 |    296K|00:00:00.30 |
|  16 |         TABLE ACCESS FULL    | ALSO_PROGRAM2             |      1 |   2087K|    51M|       |   873   (2)| 00:00:11 |   1856K|00:00:01.86 |
-------------------------------------------------------------------------------------------------------------------------------------------------

Время выполнения немаленькое, результат — успешный. Однако размер использованного временного сегмента более 5 GB (при лимите в 10), не радует (:

SQL> select OPERATION_ID, OPERATION_TYPE, ACTIVE_TIME, MAX_TEMPSEG_SIZE, LAST_TEMPSEG_SIZE from gv$sql_workarea
2  where SQL_ID = 'ddxgfk02uja5y' and LAST_TEMPSEG_SIZE > 0
3  /

OPERATION_ID OPERATION_TYPE        ACTIVE_TIME MAX_TEMPSEG_SIZE LAST_TEMPSEG_SIZE
------------ --------------------- ----------- ---------------- -----------------
5            HASH-JOIN              2117382492      5465178112        5465178112

Вариант №2 Можно попробовать использовать SQL конструкцию WITH (subquery factoring) c подсказкой MATERIALIZE для того, чтобы Oracle во время выполнения запроса создал и использовал внутреннюю временную таблицу (т.н. материализация подзапроса) вместо предварительного создания global temporary table

SQL> insert/*+ APPEND */ into ALSO_PROGRAM
2  with bord as(select /*+ MATERIALIZE */ ORD.COMPANY_ID, B.OPTION_ID
4  from BASKET B, ORDER ORD
5  where ORD.ID = B.ORDER_ID
6  AND ORD.PAID_UP = 'Y')
7  select /*+ ALL_ROWS gather_plan_statistics*/ PROGRAM_ID, OPTION_ID, COUNT_OPTION_ID from (
8  SELECT I.PROGRAM_ID PROGRAM_ID
9  , B1.OPTION_ID OPTION_ID
10  , COUNT(B1.OPTION_ID) COUNT_OPTION_ID
11  , row_number() over (partition by PROGRAM_ID order by COUNT(B1.OPTION_ID) desc) rn
12  FROM ITEM I, OPTION O, BASKET B1, ORDER ORD1,
13  BORD
14  WHERE ORD1.ID = B1.ORDER_ID
15  AND ORD1.COMPANY_ID = BORD.COMPANY_ID
16  AND O.ID = BORD.OPTION_ID
17  AND ORD1.PAID_UP = 'Y'
18  AND O.ITEM_ID = I.ID
19  AND B1.BUNDLE_OPTION_ID IS NULL
20  GROUP BY I.PROGRAM_ID, B1.OPTION_ID
21  ) where rn <= 1000;
insert/*+ APPEND gather_plan_statistics*/ into ALSO_PROGRAM
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Elapsed: 00:09:47.80

Несмотря на то, что подсказка сработала (судя по появившейся в плане операции TEMP TABLE TRANSFORMATION) и план создания этой внутренней таблицы совпадает с планом создания global temporary table, общий план запроса изменился и опять потребовалось > 10 GB дискового пространства для очередного hash join (операция c id=14 плана выполнения успела выбрать «только» 125 млн строк):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                        | Starts | E-Rows |E-Temp | Cost (%CPU)| E-Time   | A-Rows |
--------------------------------------------------------------------------------------------------------------------------------
|   1 |  TEMP TABLE TRANSFORMATION    |                             |      1 |        |       |            |          |      0 |
|   2 |   LOAD AS SELECT              |                             |      1 |        |       |            |          |      1 |
|*  3 |    HASH JOIN                  |                             |      1 |   1110K|    26M| 31255   (1)| 00:06:16 |   2021K|
|*  4 |     VIEW                      | index$_join$_003            |      1 |    963K|       | 23128   (1)| 00:04:38 |   1764K|
|*  5 |      HASH JOIN                |                             |      1 |        |       |            |          |   1764K|
|*  6 |       INDEX RANGE SCAN        | IX_ORDER_PAID_UP            |      1 |    963K|       | 32511   (1)| 00:06:31 |   1764K|
|   7 |       INDEX FAST FULL SCAN    | ORDER_ID_LID_COMID_IDX      |      1 |    963K|       | 13636   (1)| 00:02:44 |   2891K|
|   8 |     INDEX FAST FULL SCAN      | IX_BASKET_ORDER_OPTION      |      1 |   3330K|       |  3155   (1)| 00:00:38 |   3332K|
|   9 |   LOAD AS SELECT              |                             |      1 |        |       |            |          |      0 |
|* 10 |    VIEW                       |                             |      1 |   1095K|       | 74435   (1)| 00:14:54 |      0 |
|* 11 |     WINDOW SORT PUSHED RANK   |                             |      1 |   1095K|   151M| 74435   (1)| 00:14:54 |      0 |
|  12 |      HASH GROUP BY            |                             |      1 |   1095K|   151M| 74435   (1)| 00:14:54 |      0 |
|* 13 |       HASH JOIN               |                             |      1 |   1095K|    55M| 42074   (1)| 00:08:25 |      0 |
|* 14 |        HASH JOIN              |                             |      1 |    963K|    26M| 30155   (1)| 00:06:02 |    125M|
|* 15 |         VIEW                  | index$_join$_008            |      1 |    963K|       | 23128   (1)| 00:04:38 |   1764K|
|* 16 |          HASH JOIN            |                             |      1 |        |       |            |          |   1764K|
|* 17 |           INDEX RANGE SCAN    | IX_ORDER_PAID_UP            |      1 |    963K|       | 32511   (1)| 00:06:31 |   1764K|
|  18 |           INDEX FAST FULL SCAN| ORDER_ID_LID_COMID_IDX      |      1 |    963K|       | 13636   (1)| 00:02:44 |   2891K|
|* 19 |         HASH JOIN             |                             |      1 |   1110K|     9M|  3436   (1)| 00:00:42 |    389K|
|* 20 |          HASH JOIN            |                             |      1 |    322K|  2376K|   798   (1)| 00:00:10 |    323K|
|  21 |           INDEX FAST FULL SCAN| IX_ITEM_PROGRAM_ID          |      1 |    110K|       |    72   (2)| 00:00:01 |    110K|
|  22 |           INDEX FAST FULL SCAN| IX_OPTION_ITEM_ID           |      1 |    322K|       |   271   (1)| 00:00:04 |    323K|
|  23 |          VIEW                 |                             |      1 |   1110K|       |   932   (1)| 00:00:12 |    389K|
|  24 |           TABLE ACCESS FULL   | SYS_TEMP_AFD9FFA07_D2678009 |      1 |   1110K|       |   932   (1)| 00:00:12 |    389K|
|* 25 |        INDEX FAST FULL SCAN   | IX_BASKET                   |      0 |   3227K|       |  5343   (1)| 00:01:05 |      0 |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
...
  13 - access("B1"."ORDER_ID"="ORD1"."ID")
  14 - access("ORD1"."COMPANY_ID"="BORD"."COMPANY_ID")
  15 - filter("ORD1"."PAID_UP"=:SYS_B_1)
  16 - access(ROWID=ROWID)
  17 - access("ORD1"."PAID_UP"=:SYS_B_1)
  19 - access("O"."ID"="BORD"."OPTION_ID")
  20 - access("O"."ITEM_ID"="I"."ID")
  25 - filter("B1"."BUNDLE_OPTION_ID" IS NULL)

Судя по секции Predicate Information операция 14 неудачно пыталась произвести hash join больших таблиц наборов данных запроса:  таблицы ORDER ORD1 с фильтром ORD1.PAID_UP = ‘Y’ (операции 15-18) и результата объединения таблиц ITEM, OPTION и «материализованного» подзапроса BORD (операции 19-24) по условию («ORD1″.»COMPANY_ID»=»BORD».»COMPANY_ID»)

Для «облегчения» проблемного hash join‘а нужно либо уменьшить количество строк в наборе данных (что будем считать невозможным), либо попробовать избавиться от тяжелой операции, изменив порядок соединения таблиц

О сравнении использования global temporary table (GTT) и внутренней материализации подзапроса, с использованием subquery factoring (the WITH-subquery):

если CBO решает материализовать WITH-подзапрос, вы получаете временный набор данных (temporary resultset), который нельзя проиндексировать. По мере роста количества данных можно столкнуться с проблемами производительности. GTTs могут быть легко проиндексированы, таким образом поддерживая работу с большими промежуточными наборами данных

Вариант №3 Пробуем выделить ещё один запрос в раздел FROM SQL запроса и использовать беcсмысленную (fake) операцию group by для предотвращения объединения (merge) основного запроса со встроенными во FROM обзорами — inline views, строки 11-16 и 17-23 модифицированного запроса:

SQL> insert /*+ APPEND gather_plan_statistics*/
 2   into ALSO_PROGRAM
 3     select
 4      PROGRAM_ID, OPTION_ID, COUNT_OPTION_ID
 5       from (SELECT /*+ ALL_ROWS */ I.PROGRAM_ID PROGRAM_ID,
 6                    BORD1.OPTION_ID OPTION_ID,
 7                    COUNT(BORD1.OPTION_ID) COUNT_OPTION_ID,
 8                    row_number() over(partition by PROGRAM_ID order by COUNT(BORD1.OPTION_ID) desc) rn
 9               FROM ITEM I,
 10                     OPTION O,
 11                     (select ORD.COMPANY_ID, B.OPTION_ID, B.ROWID
 12                        from BASKET B, ORDER ORD
 13                       where ORD.ID = B.ORDER_ID
 14                         AND ORD.PAID_UP = 'Y'
 15                       group by ORD.COMPANY_ID, B.OPTION_ID, B.ROWID
 16                       ) BORD,
 17                     (select ORD1.COMPANY_ID, B1.OPTION_ID, B1.ROWID
 18                        from BASKET B1, ORDER ORD1
 19                       where ORD1.ID = B1.ORDER_ID
 20                         AND ORD1.PAID_UP = 'Y'
 21                         AND B1.BUNDLE_OPTION_ID IS NULL
 22                       group by ORD1.COMPANY_ID, B1.OPTION_ID, B1.ROWID
 23                       ) BORD1
 24               WHERE BORD.COMPANY_ID = BORD1.COMPANY_ID
 25                 AND BORD.OPTION_ID = O.ID
 26                 AND O.ITEM_ID = I.ID
 27  GROUP BY I.PROGRAM_ID, BORD1.OPTION_ID)
 28  where rn <= 1000;

3388544 rows created.

Elapsed: 00:03:11.56

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('2xa7s8tb8457b','0','ALL ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                      | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  LOAD AS SELECT                 |                           |      1 |        |       |       |            |          |      1 |00:03:07.02 |
|*  2 |   VIEW                          |                           |      1 |   1376K|    68M|       |   127K  (1)| 00:25:25 |   3388K|00:02:51.39 |
|*  3 |    WINDOW SORT PUSHED RANK      |                           |      1 |   1376K|    55M|   137M|   127K  (1)| 00:25:25 |   4928K|00:02:51.08 |
|   4 |     HASH GROUP BY               |                           |      1 |   1376K|    55M|   137M|   127K  (1)| 00:25:25 |   5234K|00:02:31.88 |
|*  5 |      HASH JOIN                  |                           |      1 |   1376K|    55M|    23M| 97315   (1)| 00:19:28 |    331M|00:06:02.81 |
|   6 |       VIEW                      |                           |      1 |   1091K|    11M|       | 47074   (1)| 00:09:25 |   1952K|00:00:18.40 |
|   7 |        SORT GROUP BY            |                           |      1 |   1091K|    43M|   117M| 47074   (1)| 00:09:25 |   1952K|00:00:16.44 |
|*  8 |         HASH JOIN               |                           |      1 |   1091K|    43M|    26M| 35282   (1)| 00:07:04 |   1952K|00:00:12.16 |
|*  9 |          VIEW                   | index$_join$_010          |      1 |    959K|    15M|       | 22999   (1)| 00:04:36 |   1757K|00:00:07.30 |
|* 10 |           HASH JOIN             |                           |      1 |        |       |       |            |          |   1757K|00:00:05.54 |
|* 11 |            INDEX RANGE SCAN     | IX_ORDER_PAID_UP          |      1 |    959K|    15M|       | 32225   (1)| 00:06:27 |   1757K|00:00:01.76 |
|  12 |            INDEX FAST FULL SCAN | ORDER_ID_LID_COMID_IDX    |      1 |    959K|    15M|       | 13548   (1)| 00:02:43 |   2880K|00:00:05.76 |
|* 13 |          INDEX FAST FULL SCAN   | IX_BASKET                 |      1 |   3215K|    76M|       |  5314   (1)| 00:01:04 |   3217K|00:00:06.43 |
|* 14 |       HASH JOIN                 |                           |      1 |   1106K|    32M|     9M| 46790   (1)| 00:09:22 |   2013K|00:00:27.08 |
|* 15 |        HASH JOIN                |                           |      1 |    322K|  6303K|  2376K|   797   (1)| 00:00:10 |    322K|00:00:00.69 |
|  16 |         INDEX FAST FULL SCAN    | IX_ITEM_PROGRAM_ID        |      1 |    110K|  1076K|       |    72   (2)| 00:00:01 |    110K|00:00:00.11 |
|  17 |         INDEX FAST FULL SCAN    | IX_OPTION_ITEM_ID         |      1 |    322K|  3151K|       |   271   (1)| 00:00:04 |    322K|00:00:00.32 |
|  18 |        VIEW                     |                           |      1 |   1106K|    11M|       | 44292   (1)| 00:08:52 |   2013K|00:00:18.67 |
|  19 |         SORT GROUP BY           |                           |      1 |   1106K|    42M|   110M| 44292   (1)| 00:08:52 |   2013K|00:00:16.65 |
|* 20 |          HASH JOIN              |                           |      1 |   1106K|    42M|    26M| 32972   (1)| 00:06:36 |   2013K|00:00:12.21 |
|* 21 |           VIEW                  | index$_join$_007          |      1 |    959K|    15M|       | 22999   (1)| 00:04:36 |   1757K|00:00:06.26 |
|* 22 |            HASH JOIN            |                           |      1 |        |       |       |            |          |   1757K|00:00:04.50 |
|* 23 |             INDEX RANGE SCAN    | IX_ORDER_PAID_UP          |      1 |    959K|    15M|       | 32225   (1)| 00:06:27 |   1757K|00:00:01.76 |
|  24 |             INDEX FAST FULL SCAN| ORDER_ID_LID_COMID_IDX    |      1 |    959K|    15M|       | 13548   (1)| 00:02:43 |   2880K|00:00:02.88 |
|  25 |           INDEX FAST FULL SCAN  | IX_BASKET_ORDER_OPTION    |      1 |   3318K|    72M|       |  3137   (1)| 00:00:38 |   3320K|00:00:03.32 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
...
 5 - access("BORD"."COMPANY_ID"="BORD1"."COMPANY_ID")
...
 14 - access("BORD"."OPTION_ID"="O"."ID")
 15 - access("O"."ITEM_ID"="I"."ID")
...

Запрос, как и было запланировано, выполнился без объединений (complex view merging, см.операции создания inline view — VIEW | SORT GROUP BY в строках 6-7, 18-19 плана), в разы быстрее, с минимальным использованием временного табличного пространства (80 МБ) при выполнении hash group by:

SQL> select OPERATION_ID, OPERATION_TYPE, ACTIVE_TIME, MAX_TEMPSEG_SIZE, LAST_TEMPSEG_SIZE
 2  from gv$sql_workarea where SQL_ID = '2xa7s8tb8457b' where LAST_TEMPSEG_SIZE > 0
 3  /

OPERATION_ID OPERATION_TYPE                           ACTIVE_TIME MAX_TEMPSEG_SIZE LAST_TEMPSEG_SIZE
------------ ---------------------------------------- ----------- ---------------- -----------------
 4 GROUP BY (HASH)                                     187013608         83886080          83886080

Можно попробовать ещё немного оптимизировать/уменьшить стоимость запроса, заменив избыточные е операции группировки подсказками NO_MERGE

SQL> select /*+ ALL_ROWS  gather_plan_statistics */
2   PROGRAM_ID, OPTION_ID, COUNT_OPTION_ID
3    from (SELECT I.PROGRAM_ID PROGRAM_ID,
4                 BORD1.OPTION_ID OPTION_ID,
5                 COUNT(BORD1.OPTION_ID) COUNT_OPTION_ID,
6                 row_number() over(partition by PROGRAM_ID order by COUNT(BORD1.OPTION_ID) desc) rn
7    FROM ITEM I,
8    OPTION O,
9                 (select /*+ NO_MERGE*/
10                   ORD.COMPANY_ID, B.OPTION_ID, B.ROWID
11                    from BASKET B, ORDER ORD
12                   where ORD.ID = B.ORDER_ID
13                     AND ORD.PAID_UP = 'Y'
14                 ) BORD,
15                (select /*+ NO_MERGE*/
16                   ORD1.COMPANY_ID, B1.OPTION_ID, B1.ROWID
17                    from BASKET B1, ORDER ORD1
18                   where ORD1.ID = B1.ORDER_ID
19                     AND ORD1.PAID_UP = 'Y'
20                     AND B1.BUNDLE_OPTION_ID IS NULL
21                 ) BORD1
22           WHERE BORD.COMPANY_ID = BORD1.COMPANY_ID
23             AND BORD.OPTION_ID = O.ID
24             AND O.ITEM_ID = I.ID
25           GROUP BY I.PROGRAM_ID, BORD1.OPTION_ID)
26   where rn <= 1000;
3388557 rows selected.
Elapsed: 00:02:38.50

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Starts | E-Rows |E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Used-Tmp|
-----------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  VIEW                         |                           |      1 |   1091K|       | 94080   (1)| 00:18:49 |   3388K|00:02:27.30 |
|*  2 |   WINDOW SORT PUSHED RANK     |                           |      1 |   1091K|   108M| 94080   (1)| 00:18:49 |   4979K|00:02:22.11 |   99328 |
|   3 |    HASH GROUP BY              |                           |      1 |   1091K|   108M| 94080   (1)| 00:18:49 |   5234K|00:02:10.62 |     111K|
|*  4 |     HASH JOIN                 |                           |      1 |   1091K|    23M| 70496   (1)| 00:14:06 |    331M|00:05:45.70 |         |
|   5 |      VIEW                     |                           |      1 |   1091K|       | 33458   (1)| 00:06:42 |   1952K|00:00:13.34 |
|*  6 |       HASH JOIN               |                           |      1 |   1091K|    26M| 33458   (1)| 00:06:42 |   1952K|00:00:11.39 |         |
|*  7 |        VIEW                   | index$_join$_009          |      1 |    959K|       | 22999   (1)| 00:04:36 |   1757K|00:00:06.22 |
|*  8 |         HASH JOIN             |                           |      1 |        |       |            |          |   1757K|00:00:02.70 |         |
|*  9 |          INDEX RANGE SCAN     | IX_ORDER_PAID_UP          |      1 |    959K|       | 32225   (1)| 00:06:27 |   1757K|00:00:01.76 |
|  10 |          INDEX FAST FULL SCAN | ORDER_ID_LID_COMID_IDX    |      1 |    959K|       | 13548   (1)| 00:02:43 |   2880K|00:00:02.88 |
|* 11 |        INDEX FAST FULL SCAN   | IX_BASKET                 |      1 |   3215K|       |  5314   (1)| 00:01:04 |   3217K|00:00:06.43 |
|* 12 |      HASH JOIN                |                           |      1 |   1106K|     9M| 33588   (1)| 00:06:44 |   2013K|00:00:21.84 |         |
|* 13 |       HASH JOIN               |                           |      1 |    322K|  2376K|   797   (1)| 00:00:10 |    322K|00:00:00.68 |         |
|  14 |        INDEX FAST FULL SCAN   | IX_ITEM_PROGRAM_ID        |      1 |    110K|       |    72   (2)| 00:00:01 |    110K|00:00:00.11 |
|  15 |        INDEX FAST FULL SCAN   | IX_OPTION_ITEM_ID         |      1 |    322K|       |   271   (1)| 00:00:04 |    322K|00:00:00.32 |
|  16 |       VIEW                    |                           |      1 |   1106K|       | 31090   (1)| 00:06:14 |   2013K|00:00:15.54 |
|* 17 |        HASH JOIN              |                           |      1 |   1106K|    26M| 31090   (1)| 00:06:14 |   2013K|00:00:11.51 |         |
|* 18 |         VIEW                  | index$_join$_006          |      1 |    959K|       | 22999   (1)| 00:04:36 |   1757K|00:00:06.13 |
|* 19 |          HASH JOIN            |                           |      1 |        |       |            |          |   1757K|00:00:04.37 |         |
|* 20 |           INDEX RANGE SCAN    | IX_ORDER_PAID_UP          |      1 |    959K|       | 32225   (1)| 00:06:27 |   1757K|00:00:01.76 |
|  21 |           INDEX FAST FULL SCAN| ORDER_ID_LID_COMID_IDX    |      1 |    959K|       | 13548   (1)| 00:02:43 |   2880K|00:00:02.88 |
|  22 |         INDEX FAST FULL SCAN  | IX_BASKET_ORDER_OPTION    |      1 |   3318K|       |  3137   (1)| 00:00:38 |   3320K|00:00:03.32 |
----------------------------------------------------------------------------------------------------------------------------------------------------

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

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: