Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Запрос и план. Идеи по улучшению  [new]
Trumen
Member

Откуда:
Сообщений: 56
Привет всем,

Клиент просит разобраться с меденным выполнением запроса. До этого не приходилось часто работать с оптимизацией запросов. Вот сам запроси и его план.

SELECT   R.CLAIM_ID,
         PROD_SRC.PROMO_WID,
         R.SUBMIT_FOR_ORG,
         PROD_SRC.PRODUCT_WID,
         R.RESOLUTION_ID,
         R.DT,
         R.SUBMIT_BY_ORG,
         R.RESOLUTION_TYPE,
         R.GROSS_AMOUNT,
         R.GST_AMOUNT,
         R.NET_AMOUNT,
         R.STATUS,
         R.DEAL_TYPE,
         R.COMMENTS,
         R.RES_PEND_APPR_NET_GST,
         R.RES_UNRESOLVED_NET_GST,
         R.RES_RESOLVED_NET_GST,
         R.CREATED,
         R.CREATED_BY,
         R.LAST_UPD,
         R.LAST_UPD_BY,
         R.CLAIM_AMOUNT,
         R.CUST_CLAIM_ID,
         R.GL_BUCKET,
         R.SOURCE_SYSTEM,
         R.RES_START_DT,
         R.RES_END_DT,
         PROD_SRC.SRC_CODE
  FROM   WC_RESOLUTION_FS R,
         W_PLAN_PROMO_D PROM,
         (  SELECT   PROMO.ROW_WID AS PROMO_WID,
                     VOL.PRODUCT_WID AS PRODUCT_WID,
                     VOL.PRODUCT_SOURCE_SYSTEM AS SRC_CODE
              FROM   W_PLAN_PROMO_D PROMO,
                     WC_PLANNED_VOLUMES_F VOL,
                     WC_BUSINESS_DATE_D DT,
                     WC_ACTUAL_VOLS_SHIP_F SHIP
             WHERE       VOL.ORG_WID = SHIP.ORG_WID(+)
                     AND VOL.PRODUCT_WID = SHIP.PRODUCT_WID(+)
                     AND VOL.DT_WID = SHIP.DT_WID(+)
                     AND PROMO.X_PLANNING_DATA_TYPE = 'Shipment'
                     AND VOL.DT_WID <= DT.BUSINESS_DT_WID
                     AND PROMO.X_ORG_WID = VOL.ORG_WID
                     AND PROMO.ROW_WID = VOL.PROMO_WID
                     AND VOL.PRODUCT_SOURCE_SYSTEM = SHIP.PRODUCT_SOURCE_SYSTEM
                     AND VOL.PROMO_TYPE IN ('TTI', 'TTB', 'TTV')
                     AND PROMO.PROMO_STATUS IN
                              ('Planned',
                               'Line Manager Submitted',
                               'Price Update',
                               'Approved',
                               'Completed')
          GROUP BY   PROMO.ROW_WID,
                     VOL.PRODUCT_WID,
                     VOL.PRODUCT_SOURCE_SYSTEM) PROD_SRC
 WHERE       PROD_SRC.SRC_CODE = R.SOURCE_SYSTEM
         AND PROM.X_PROMO_PROD_CAT_ROW_ID = R.PRODUCT_ID
         AND PROM.X_INTEGRATION_ID_PROMO = R.PROMO_ID
         AND PROD_SRC.PROMO_WID = PROM.ROW_WID
         AND PROM.X_SPEND_REASON = 'Trade Terms'


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2525984878

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                         |    83 | 26477 | 79313   (2)| 00:07:40 |       |       |
|   1 |  PX COORDINATOR                              |                         |       |       |            |          |       |       |
|   2 |   PX SEND QC (RANDOM)                        | :TQ10005                |    83 | 26477 | 79313   (2)| 00:07:40 |       |       |
|   3 |    HASH GROUP BY                             |                         |    83 | 26477 | 79313   (2)| 00:07:40 |       |       |
|   4 |     PX RECEIVE                               |                         |    83 | 26477 | 79312   (2)| 00:07:40 |       |       |
|   5 |      PX SEND HASH                            | :TQ10004                |    83 | 26477 | 79312   (2)| 00:07:40 |       |       |
|*  6 |       HASH JOIN                              |                         |    83 | 26477 | 79312   (2)| 00:07:40 |       |       |
|   7 |        PX RECEIVE                            |                         |    96 | 12960 | 77708   (2)| 00:07:31 |       |       |
|   8 |         PX SEND HASH                         | :TQ10003                |    96 | 12960 | 77708   (2)| 00:07:31 |       |       |
|*  9 |          HASH JOIN                           |                         |    96 | 12960 | 77708   (2)| 00:07:31 |       |       |
|  10 |           PX RECEIVE                         |                         |    14 |   420 |   775   (1)| 00:00:05 |       |       |
|  11 |            PX SEND BROADCAST LOCAL           | :TQ10002                |    14 |   420 |   775   (1)| 00:00:05 |       |       |
|* 12 |             TABLE ACCESS BY LOCAL INDEX ROWID| WC_PLANNED_VOLUMES_F    |    14 |   420 |   775   (1)| 00:00:05 |       |       |
|  13 |              NESTED LOOPS                    |                         | 27005 |  3032K| 77270   (2)| 00:07:28 |       |       |
|  14 |               NESTED LOOPS                   |                         |  1934 |   160K|  4243   (1)| 00:00:25 |       |       |
|  15 |                MERGE JOIN CARTESIAN          |                         |  1934 |   100K|  2306   (1)| 00:00:14 |       |       |
|  16 |                 SORT JOIN                    |                         |       |       |            |          |       |       |
|  17 |                  PX BLOCK ITERATOR           |                         |     1 |     6 |     2   (0)| 00:00:01 |       |       |
|  18 |                   TABLE ACCESS FULL          | WC_BUSINESS_DATE_D      |     1 |     6 |     2   (0)| 00:00:01 |       |       |
|  19 |                 BUFFER SORT                  |                         |  1934 | 90898 |  2304   (1)| 00:00:14 |       |       |
|  20 |                  BUFFER SORT                 |                         |       |       |            |          |       |       |
|  21 |                   PX RECEIVE                 |                         |  1934 | 90898 |  2302   (1)| 00:00:14 |       |       |
|  22 |                    PX SEND BROADCAST         | :TQ10000                |  1934 | 90898 |  2302   (1)| 00:00:14 |       |       |
|* 23 |                     TABLE ACCESS FULL        | W_PLAN_PROMO_D          |  1934 | 90898 |  2302   (1)| 00:00:14 |       |       |
|* 24 |                TABLE ACCESS BY INDEX ROWID   | W_PLAN_PROMO_D          |     1 |    32 |     1   (0)| 00:00:01 |       |       |
|* 25 |                 INDEX UNIQUE SCAN            | W_PLAN_PROMO_D_P1       |     1 |       |     0   (0)| 00:00:01 |       |       |
|  26 |               PARTITION RANGE ITERATOR       |                         | 90896 |       |    21   (0)| 00:00:01 |     1 |   KEY |
|* 27 |                INDEX RANGE SCAN              | WC_PLANNED_VOLUMES_F_M2 | 90896 |       |    21   (0)| 00:00:01 |     1 |   KEY |
|  28 |           PX BLOCK ITERATOR                  |                         |   687K|    13M|   435   (3)| 00:00:03 |     1 |    37 |
|  29 |            TABLE ACCESS FULL                 | WC_ACTUAL_VOLS_SHIP_F   |   687K|    13M|   435   (3)| 00:00:03 |     1 |    37 |
|  30 |        BUFFER SORT                           |                         |       |       |            |          |       |       |
|  31 |         PX RECEIVE                           |                         |   209K|    36M|  1603   (2)| 00:00:10 |       |       |
|  32 |          PX SEND HASH                        | :TQ10001                |   209K|    36M|  1603   (2)| 00:00:10 |       |       |
|  33 |           TABLE ACCESS FULL                  | WC_RESOLUTION_FS        |   209K|    36M|  1603   (2)| 00:00:10 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------

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

   6 - access("VOL"."PRODUCT_SOURCE_SYSTEM"="R"."SOURCE_SYSTEM" AND "PROM"."X_PROMO_PROD_CAT_ROW_ID"="R"."PRODUCT_ID" AND
              "PROM"."X_INTEGRATION_ID_PROMO"="R"."PROMO_ID")
   9 - access("VOL"."DT_WID"="SHIP"."DT_WID" AND "VOL"."ORG_WID"="SHIP"."ORG_WID" AND "VOL"."PRODUCT_WID"="SHIP"."PRODUCT_WID"
              AND "VOL"."PRODUCT_SOURCE_SYSTEM"="SHIP"."PRODUCT_SOURCE_SYSTEM")
  12 - filter(("VOL"."PROMO_TYPE"='TTB' OR "VOL"."PROMO_TYPE"='TTI' OR "VOL"."PROMO_TYPE"='TTV') AND
              "VOL"."DT_WID"<="DT"."BUSINESS_DT_WID" AND "PROMO"."X_ORG_WID"="VOL"."ORG_WID")
  23 - filter("PROM"."X_SPEND_REASON"='Trade Terms')
  24 - filter("PROMO"."X_PLANNING_DATA_TYPE"='Shipment' AND ("PROMO"."PROMO_STATUS"='Approved' OR
              "PROMO"."PROMO_STATUS"='Completed' OR "PROMO"."PROMO_STATUS"='Line Manager Submitted' OR "PROMO"."PROMO_STATUS"='Planned' OR
              "PROMO"."PROMO_STATUS"='Price Update'))
  25 - access("PROMO"."ROW_WID"="PROM"."ROW_WID")
  27 - access("PROMO"."ROW_WID"="VOL"."PROMO_WID")

56 rows selected.



Пока не не вижу каких то проблем в запросе. Только я не понял для чего нужен Group By в подзапросе. Ваши идеи? Есть ли проблема в плане или лучше сконцентрироватьсяна на чем то другом ?
29 июн 11, 06:56    [10890402]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
В WC_BUSINESS_DATE_D одна запись?
29 июн 11, 07:03    [10890406]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
Trumen
Member

Откуда:
Сообщений: 56
wurdu,

Да, только одна. Дата
29 июн 11, 07:55    [10890438]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Если версия >= 10g, то выполняешь запрос с хинтом /*+ gather_plan_statistics */ - SELECT /*+ gather_plan_statistics */ R.CLAIM_ID, ...,
а затем показываешь план через select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')), либо вместо первого параметра подставишь sql_id из этого запроса, который найдешь в v$sql.
29 июн 11, 08:04    [10890443]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Хотя с параллелизмом ничего хорошего там не будет. Надо no_parallel(...) еще для каждой таблицы.
29 июн 11, 08:18    [10890459]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
Trumen
Member

Откуда:
Сообщений: 56
wurdu,

Я попросил, что бы trace врубили во время выполнения. Сам то я доступа не имею к системе и приходится только запрашивать.

Будет разница между trace файом во время исполнения и запроса с хинтом?
29 июн 11, 08:20    [10890463]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
Trumen
Member

Откуда:
Сообщений: 56
Trumen,

Да, версия 10g.
29 июн 11, 08:21    [10890465]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
Trumen
Member

Откуда:
Сообщений: 56
wurdu
Хотя с параллелизмом ничего хорошего там не будет. Надо no_parallel(...) еще для каждой таблицы.


Как с раз в этом и проблема. Аппликейшн наращивает параллелизм, думаю что быстрее все будет выполняться. Ну и уперлись час в факт, что весь процесс (не только этот запрос) работает 20 часов на лучшем железе с большим уровнем параллеизма. Раньше работал всего 8 часов. Да еще Async IO не включают.

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class 
PX Deq Credit: send blkd 313,486 23,788 76 34.4 Other 
db file sequential read 1,113,864 17,021 15 24.6 User I/O 
CPU time   6,700   9.7   
log file parallel write 15,691 771 49 1.1 System I/O 
log file sync 13,602 328 24 .5 Commit 
29 июн 11, 08:24    [10890478]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Trumen
Будет разница между trace файом во время исполнения и запроса с хинтом?
Ну быстрее наверное разобраться, чем 20 часов ждать. Меня смущает выбор NDEX RANGE SCAN WC_PLANNED_VOLUMES_F_M2 и низкая стоимость, возможно, конечно, у этого индекса идеальный clustering factor, но я бы проверил настройки оптимизатора, например, вот так:
select sql_id, name from v$sql_optimizer_env where sql_id = '...'
, где sql_id берется из v$sql для этого запроса. Также можно быстро руками проверить предположения оптимизатора. Например, для select count(1) from W_PLAN_PROMO_D where X_SPEND_REASON='Trade Terms'. Там точно где-то 1934 строк, а не принципиально больше?
29 июн 11, 08:41    [10890529]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
События ожидания во время выполнения запроса также интересны. Ну в трассировке это будет.
29 июн 11, 09:08    [10890575]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
Trumen
Member

Откуда:
Сообщений: 56
wurdu,

Спасибо.

Трейс попробую врубить. Повторно запустить запрос не получиться по причине бюрократизма и волокиты.

Так же узнал что статистика не была собрана по таблицам и запрошу собрать ее тоже.

Статы собираются
29 июн 11, 09:57    [10890782]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 500
Trumen
Только я не понял для чего нужен Group By в подзапросе. Ваши идеи?

Для уникальности.

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

Trumen
wurdu,

Я попросил, что бы trace врубили во время выполнения. Сам то я доступа не имею к системе и приходится только запрашивать.

Надо не просто trace врубить, а врубить его на QC и все слейвы, а потом всю пачку трейсов обработать с помощью trcess. Ну или смотреть по отдельности тоже может понадобиться.


Исходя из того что ты представил я бы обратил внимание, что оптимизатор считает самой дорогой операцией (из нее вся стоимость запроса состоит) следующую:

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                         |    83 | 26477 | 79313   (2)| 00:07:40 |       |       |
...
|* 12 |             TABLE ACCESS BY LOCAL INDEX ROWID| WC_PLANNED_VOLUMES_F    |    14 |   420 |   775   (1)| 00:00:05 |       |       |
|  13 |              NESTED LOOPS                    |                         | 27005 |  3032K| 77270   (2)| 00:07:28 |       |       |
|  14 |               NESTED LOOPS                   |                         |  1934 |   160K|  4243   (1)| 00:00:25 |       |       |
...
|  26 |               PARTITION RANGE ITERATOR       |                         | 90896 |       |    21   (0)| 00:00:01 |     1 |   KEY |
|* 27 |                INDEX RANGE SCAN              | WC_PLANNED_VOLUMES_F_M2 | 90896 |       |    21   (0)| 00:00:01 |     1 |   KEY |
...

Индекс WC_PLANNED_VOLUMES_F_M2 локальный и за раз сканируются сразу несколько партиций индекса.


Trumen
Как с раз в этом и проблема. Аппликейшн наращивает параллелизм, думаю что быстрее все будет выполняться. Ну и уперлись час в факт, что весь процесс (не только этот запрос) работает 20 часов на лучшем железе с большим уровнем параллеизма. Раньше работал всего 8 часов. Да еще Async IO не включают.

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class 
PX Deq Credit: send blkd 313,486 23,788 76 34.4 Other 
db file sequential read 1,113,864 17,021 15 24.6 User I/O 
CPU time   6,700   9.7   
log file parallel write 15,691 771 49 1.1 System I/O 
log file sync 13,602 328 24 .5 Commit 

Здесь нет 20 часов даже если бы это была одна сессия.
Исходя из ожиданий для меня картина выглядит так, что 17K секунд выполнялся дисковый I/O, в это время другие слейвы ждали пока выполнится этот I/O.
Но т.к. это взято судя по всему из AWR это средняя температура.


Если тебе быстрее запрос выполнить, чем получить результаты трассировок и runtime статистик, то результат такого запроса был бы полезен
SELECT count(*),
       count(case
                 when VOL.DT_WID <= DT.BUSINESS_DT_WID AND
                      PROMO.X_ORG_WID = VOL.ORG_WID AND
                      VOL.PROMO_TYPE IN ('TTI', 'TTB', 'TTV')
                 then 1
             end)
  FROM W_PLAN_PROMO_D       PROMO,
       WC_PLANNED_VOLUMES_F VOL,
       WC_BUSINESS_DATE_D   DT
 WHERE PROMO.X_PLANNING_DATA_TYPE = 'Shipment' 
   AND PROMO.ROW_WID = VOL.PROMO_WID 
   AND VOL.PRODUCT_SOURCE_SYSTEM = SHIP.PRODUCT_SOURCE_SYSTEM 
   AND PROMO.PROMO_STATUS IN ('Planned',
                              'Line Manager Submitted',
                              'Price Update',
                              'Approved',
                              'Completed')


Сколько всего строк и блоков в таблице WC_PLANNED_VOLUMES_F?
Она партиционирована по DT_WID?

Покажи план первоначального запроса с хинтом
--+ full(vol)
29 июн 11, 10:21    [10890917]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
Trumen
Member

Откуда:
Сообщений: 56
wurdu
Trumen
Будет разница между trace файом во время исполнения и запроса с хинтом?
Ну быстрее наверное разобраться, чем 20 часов ждать. Меня смущает выбор NDEX RANGE SCAN WC_PLANNED_VOLUMES_F_M2 и низкая стоимость, возможно, конечно, у этого индекса идеальный clustering factor, но я бы проверил настройки оптимизатора, например, вот так:
select sql_id, name from v$sql_optimizer_env where sql_id = '...'
, где sql_id берется из v$sql для этого запроса. Также можно быстро руками проверить предположения оптимизатора. Например, для select count(1) from W_PLAN_PROMO_D where X_SPEND_REASON='Trade Terms'. Там точно где-то 1934 строк, а не принципиально больше?


Нет, там меньше. Всего 1734. Статистика подвела
29 июн 11, 10:33    [10891000]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Trumen
wurdu
пропущено...
Ну быстрее наверное разобраться, чем 20 часов ждать. Меня смущает выбор NDEX RANGE SCAN WC_PLANNED_VOLUMES_F_M2 и низкая стоимость, возможно, конечно, у этого индекса идеальный clustering factor, но я бы проверил настройки оптимизатора, например, вот так:
select sql_id, name from v$sql_optimizer_env where sql_id = '...'
, где sql_id берется из v$sql для этого запроса. Также можно быстро руками проверить предположения оптимизатора. Например, для select count(1) from W_PLAN_PROMO_D where X_SPEND_REASON='Trade Terms'. Там точно где-то 1934 строк, а не принципиально больше?


Нет, там меньше. Всего 1734. Статистика подвела
Ну такая разница в статистике не принципиальна.
29 июн 11, 10:34    [10891012]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
Trumen
Member

Откуда:
Сообщений: 56
Alexander Anokhin
Trumen
Только я не понял для чего нужен Group By в подзапросе. Ваши идеи?

Для уникальности.

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

Trumen
wurdu,

Я попросил, что бы trace врубили во время выполнения. Сам то я доступа не имею к системе и приходится только запрашивать.

Надо не просто trace врубить, а врубить его на QC и все слейвы, а потом всю пачку трейсов обработать с помощью trcess. Ну или смотреть по отдельности тоже может понадобиться.


Исходя из того что ты представил я бы обратил внимание, что оптимизатор считает самой дорогой операцией (из нее вся стоимость запроса состоит) следующую:

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                         |    83 | 26477 | 79313   (2)| 00:07:40 |       |       |
...
|* 12 |             TABLE ACCESS BY LOCAL INDEX ROWID| WC_PLANNED_VOLUMES_F    |    14 |   420 |   775   (1)| 00:00:05 |       |       |
|  13 |              NESTED LOOPS                    |                         | 27005 |  3032K| 77270   (2)| 00:07:28 |       |       |
|  14 |               NESTED LOOPS                   |                         |  1934 |   160K|  4243   (1)| 00:00:25 |       |       |
...
|  26 |               PARTITION RANGE ITERATOR       |                         | 90896 |       |    21   (0)| 00:00:01 |     1 |   KEY |
|* 27 |                INDEX RANGE SCAN              | WC_PLANNED_VOLUMES_F_M2 | 90896 |       |    21   (0)| 00:00:01 |     1 |   KEY |
...

Индекс WC_PLANNED_VOLUMES_F_M2 локальный и за раз сканируются сразу несколько партиций индекса.


Trumen
Как с раз в этом и проблема. Аппликейшн наращивает параллелизм, думаю что быстрее все будет выполняться. Ну и уперлись час в факт, что весь процесс (не только этот запрос) работает 20 часов на лучшем железе с большим уровнем параллеизма. Раньше работал всего 8 часов. Да еще Async IO не включают.

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class 
PX Deq Credit: send blkd 313,486 23,788 76 34.4 Other 
db file sequential read 1,113,864 17,021 15 24.6 User I/O 
CPU time   6,700   9.7   
log file parallel write 15,691 771 49 1.1 System I/O 
log file sync 13,602 328 24 .5 Commit 

Здесь нет 20 часов даже если бы это была одна сессия.
Исходя из ожиданий для меня картина выглядит так, что 17K секунд выполнялся дисковый I/O, в это время другие слейвы ждали пока выполнится этот I/O.
Но т.к. это взято судя по всему из AWR это средняя температура.


Если тебе быстрее запрос выполнить, чем получить результаты трассировок и runtime статистик, то результат такого запроса был бы полезен
SELECT count(*),
       count(case
                 when VOL.DT_WID <= DT.BUSINESS_DT_WID AND
                      PROMO.X_ORG_WID = VOL.ORG_WID AND
                      VOL.PROMO_TYPE IN ('TTI', 'TTB', 'TTV')
                 then 1
             end)
  FROM W_PLAN_PROMO_D       PROMO,
       WC_PLANNED_VOLUMES_F VOL,
       WC_BUSINESS_DATE_D   DT
 WHERE PROMO.X_PLANNING_DATA_TYPE = 'Shipment' 
   AND PROMO.ROW_WID = VOL.PROMO_WID 
   AND VOL.PRODUCT_SOURCE_SYSTEM = SHIP.PRODUCT_SOURCE_SYSTEM 
   AND PROMO.PROMO_STATUS IN ('Planned',
                              'Line Manager Submitted',
                              'Price Update',
                              'Approved',
                              'Completed')


Сколько всего строк и блоков в таблице WC_PLANNED_VOLUMES_F?
Она партиционирована по DT_WID?

Покажи план первоначального запроса с хинтом
--+ full(vol)


C сожалению не имею доступа с системе. Могу тока ждать результатов трейса по всем слейвам.

Вообще то это трейс с тестовой системы. Он хоть и не очень быстр (причиной так же может быть не оптимальное системы ввода вывода), но он хоть работает. На боевой системе он валиться из-за того, что TEMP умирает быстро. Что хреново, планов нормальных и статистики нет. Тока кусочек плана в Jpeg с жабы и он совсем другой. Куча Join-ов что я думаю и была причина заполнения TEMP.

Час в планах оптимизировать этот план на тестовой машине с данными с прода, сохранить outline и скопировать в прод.
29 июн 11, 10:50    [10891115]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
Репозиторий суппозиториев
Guest
Возможно, вот это условие:
 
AND PROD_SRC.PROMO_WID = PROM.ROW_WID
AND PROM.X_SPEND_REASON = 'Trade Terms'
можно внусти внутрь вложенного запроса. Подозреваю, что для каждого row_wid будет единственный x_spend_reason. Тогда внутреннюю выборку можно отфильтровать по 'Trade terms' - получим меньше записей, избежим вот этого тяжелого нестед лупа.
|  13 |              NESTED LOOPS                    |                         | 27005 |  3032K| 77270   (2)| 00:07:28 |       |       |
|  14 |               NESTED LOOPS                   |                         |  1934 |   160K|  4243   (1)| 00:00:25 |       |       |
|  15 |                MERGE JOIN CARTESIAN          |                         |  1934 |   100K|  2306   (1)| 00:00:14 |       |       |
|  16 |                 SORT JOIN                    |                         |       |       |            |          |       |       |
|  17 |                  PX BLOCK ITERATOR           |                         |     1 |     6 |     2   (0)| 00:00:01 |       |       |
|  18 |                   TABLE ACCESS FULL          | WC_BUSINESS_DATE_D      |     1 |     6 |     2   (0)| 00:00:01 |       |       |
|  19 |                 BUFFER SORT                  |                         |  1934 | 90898 |  2304   (1)| 00:00:14 |       |       |
|  20 |                  BUFFER SORT                 |                         |       |       |            |          |       |       |
|  21 |                   PX RECEIVE                 |                         |  1934 | 90898 |  2302   (1)| 00:00:14 |       |       |
|  22 |                    PX SEND BROADCAST         | :TQ10000                |  1934 | 90898 |  2302   (1)| 00:00:14 |       |       |
|* 23 |                     TABLE ACCESS FULL        | W_PLAN_PROMO_D          |  1934 | 90898 |  2302   (1)| 00:00:14 |       |       |
|* 24 |                TABLE ACCESS BY INDEX ROWID   | W_PLAN_PROMO_D          |     1 |    32 |     1   (0)| 00:00:01 |       |       |
|* 25 |                 INDEX UNIQUE SCAN            | W_PLAN_PROMO_D_P1       |     1 |       |     0   (0)| 00:00:01 |       |       |
Не ручаюсь, что это "вундервафля" - не зная структуры данных могу только предполагать.
29 июн 11, 10:58    [10891165]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 500
Репозиторий суппозиториев
Возможно, вот это условие:
 
AND PROD_SRC.PROMO_WID = PROM.ROW_WID
AND PROM.X_SPEND_REASON = 'Trade Terms'
можно внусти внутрь вложенного запроса. Подозреваю, что для каждого row_wid будет единственный x_spend_reason. Тогда внутреннюю выборку можно отфильтровать по 'Trade terms' - получим меньше записей, избежим вот этого тяжелого нестед лупа.
|  13 |              NESTED LOOPS                    |                         | 27005 |  3032K| 77270   (2)| 00:07:28 |       |       |
|  14 |               NESTED LOOPS                   |                         |  1934 |   160K|  4243   (1)| 00:00:25 |       |       |
|  15 |                MERGE JOIN CARTESIAN          |                         |  1934 |   100K|  2306   (1)| 00:00:14 |       |       |
|  16 |                 SORT JOIN                    |                         |       |       |            |          |       |       |
|  17 |                  PX BLOCK ITERATOR           |                         |     1 |     6 |     2   (0)| 00:00:01 |       |       |
|  18 |                   TABLE ACCESS FULL          | WC_BUSINESS_DATE_D      |     1 |     6 |     2   (0)| 00:00:01 |       |       |
|  19 |                 BUFFER SORT                  |                         |  1934 | 90898 |  2304   (1)| 00:00:14 |       |       |
|  20 |                  BUFFER SORT                 |                         |       |       |            |          |       |       |
|  21 |                   PX RECEIVE                 |                         |  1934 | 90898 |  2302   (1)| 00:00:14 |       |       |
|  22 |                    PX SEND BROADCAST         | :TQ10000                |  1934 | 90898 |  2302   (1)| 00:00:14 |       |       |
|* 23 |                     TABLE ACCESS FULL        | W_PLAN_PROMO_D          |  1934 | 90898 |  2302   (1)| 00:00:14 |       |       |
|* 24 |                TABLE ACCESS BY INDEX ROWID   | W_PLAN_PROMO_D          |     1 |    32 |     1   (0)| 00:00:01 |       |       |
|* 25 |                 INDEX UNIQUE SCAN            | W_PLAN_PROMO_D_P1       |     1 |       |     0   (0)| 00:00:01 |       |       |
Не ручаюсь, что это "вундервафля" - не зная структуры данных могу только предполагать.


Это уже произошло в первоначальном запросе.
Выполнение начинается с соединения W_PLAN_PROMO_D & WC_BUSINESS_DATE_D
Условие
 AND PROM.X_SPEND_REASON = 'Trade Terms'
применяется там же (шаг 23)
29 июн 11, 11:20    [10891325]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 500
Было бы очень полезно знать больше о партиционировании таблицы. Она партиционирована по DT_WID, верно?
Какие интервалы, сколько партиций?
29 июн 11, 11:38    [10891456]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 500
таблицы WC_PLANNED_VOLUMES_F
29 июн 11, 11:39    [10891469]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Раз какая-то проблема показать параметры оптимизатора из v$sql_optimizer_env, покажи хотя бы из V$SYS_OPTIMIZER_ENV, вдруг совпадают...
29 июн 11, 13:37    [10892710]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
Trumen
Member

Откуда:
Сообщений: 56
Alexander Anokhin
таблицы WC_PLANNED_VOLUMES_F


Да, таблицы партицирована по DT_WID.
30 июн 11, 06:11    [10896716]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
Trumen
Member

Откуда:
Сообщений: 56
wurdu
Раз какая-то проблема показать параметры оптимизатора из v$sql_optimizer_env, покажи хотя бы из V$SYS_OPTIMIZER_ENV, вдруг совпадают...


К сожалению вся инфа уже обновилась, так как был запущен процесс повторно. Трейсы так и не включили, но сказали, что запрос выполнилася за 20 минут, вместо 1:10.

Жду планы и awr репорты.
30 июн 11, 06:13    [10896717]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Trumen
wurdu
Раз какая-то проблема показать параметры оптимизатора из v$sql_optimizer_env, покажи хотя бы из V$SYS_OPTIMIZER_ENV, вдруг совпадают...


К сожалению вся инфа уже обновилась, так как был запущен процесс повторно. Трейсы так и не включили, но сказали, что запрос выполнилася за 20 минут, вместо 1:10.

Жду планы и awr репорты.
От перезапуска эти параметры не меняются.
30 июн 11, 07:23    [10896771]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
Trumen
Member

Откуда:
Сообщений: 56
wurdu,

Я имею ввиду, что sql_id такого уже нет
30 июн 11, 08:21    [10896837]     Ответить | Цитировать Сообщить модератору
 Re: Запрос и план. Идеи по улучшению  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Trumen
wurdu,

Я имею ввиду, что sql_id такого уже нет
В V$SYS_OPTIMIZER_ENV нет sql_id, не факт конечно, что для этого sql отдельно что-то не выставляется, но все же.
30 июн 11, 08:24    [10896842]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить