Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 ХЕЛП: замедлился запрос  [new]
dmitriy22
Member

Откуда:
Сообщений: 111
Доброе время суток!

у меня есть sql запрос, возможно громоздкий, но раньше работал не долго, а сейчас практически висит
 SELECT  1                                                          AS IDENTITY_CODE ,
    E.N_TRANSACTION_SYS_ID                                          AS ID_NUMBER ,
    LAST_DAY ( TRUNC ( D_ACCOUNTING_DATE, 'MM' ) )                  AS AS_OF_DATE ,
    NVL ( e.V_CCY_CD, 'RUR' )                                       AS ISO_CURRENCY_CD ,
    1                                                               AS RECORD_COUNT ,
    N_TRAN_DEBIT_AMOUNT                                             AS VOLUME ,
    NVL(N_TRAN_DEBIT_AMOUNT_RUR, N_TRAN_DEBIT_AMOUNT)               AS cost ,
    SUBSTR(E.V_ACCOUNT_NUM_DEBIT,LENGTH(E.N_LEGAL_ENTITY_CD)                                                                                                                                                                                                                                                 +2,20) AS DBT_ACCOUNT ,
    SUBSTR(E.V_ACCOUNT_NUM_CREDIT,LENGTH(E.n_legal_entity_cd)                                                                                                                                                                                                                                                +2,20) AS CRD_ACCOUNT ,
                                                                                                                                                                                                                                                                                                             - 1    AS PRODUCT_ID ,
    NVL( DECODE( N , 1, OFSAA1.MIS_RS5_PKG.GET_MGR_BY_RS5_ORG_UNIT_ID(RS_D.V_RS5_CODE, E.N_LEGAL_ENTITY_CD, LAST_DAY ( TRUNC ( D_ACCOUNTING_DATE, 'MM' ) )) , 2 , OFSAA1.MIS_RS5_PKG.GET_MGR_BY_RS5_ORG_UNIT_ID(RS_C.V_RS5_CODE, E.N_LEGAL_ENTITY_CD, LAST_DAY ( TRUNC ( D_ACCOUNTING_DATE, 'MM' ) )) ),     - 1)   AS ORG_UNIT_ID ,
    NVL( DECODE( N , 1, OFSAA1.MIS_RS5_PKG.GET_MGR_BY_RS5_COMMON_COA_ID(RS_D.V_RS5_CODE, E.N_LEGAL_ENTITY_CD, LAST_DAY ( TRUNC ( D_ACCOUNTING_DATE, 'MM' ) )) , 2 , OFSAA1.MIS_RS5_PKG.GET_MGR_BY_RS5_COMMON_COA_ID(RS_C.V_RS5_CODE, E.N_LEGAL_ENTITY_CD, LAST_DAY ( TRUNC ( D_ACCOUNTING_DATE, 'MM' ) )) ), - 1)   AS COMMON_COA_ID ,
    NVL( DECODE( N , 1, GL_D.O2LS_GL_ACCOUNT_ID , 2, GL_C.O2LS_GL_ACCOUNT_ID ),                                                                                                                                                                                                                              - 1)   AS GL_ACCOUNT_ID ,
    NVL( DECODE( N , 1, OFSAA1.MIS_RS5_PKG.GET_MGR_BY_RS5_MANAGER_ID(RS_D.V_RS5_CODE, E.N_LEGAL_ENTITY_CD, LAST_DAY ( TRUNC ( D_ACCOUNTING_DATE, 'MM' ) )) , 2 , OFSAA1.MIS_RS5_PKG.GET_MGR_BY_RS5_MANAGER_ID(RS_C.V_RS5_CODE, E.N_LEGAL_ENTITY_CD, LAST_DAY ( TRUNC ( D_ACCOUNTING_DATE, 'MM' ) )) ),       - 1)   AS MANAGER_ID ,
                                                                                                                                                                                                                                                                                                             - 1    AS STRATEGY_ID ,
    E.N_LEGAL_ENTITY_CD                                                                                                                                                                                                                                                                                             AS BANK_ID ,
    1                                                                                                                                                                                                                                                                                                               AS IDENTITY_CODE_CHG ,
    SUBSTR(E.V_ACCOUNT_NUM_DEBIT,LENGTH(E.N_LEGAL_ENTITY_CD) +2,5)                                                                                                                                                                                                                                                  AS DEBIT_BAL_ACCOUNT ,
    SUBSTR(E.V_ACCOUNT_NUM_CREDIT,LENGTH(E.n_legal_entity_cd)+2,5)                                                                                                                                                                                                                                                  AS CRD_BALANCE_ACCOUNT ,
    E.V_TRANSACTION_DESC                                                                                                                                                                                                                                                                                            AS DESCRIPTION ,
    RS_D.V_RS5_CODE                                                                                                                                                                                                                                                                                                 AS DBT_CLIENT_CODE ,
    RS_C.V_RS5_CODE                                                                                                                                                                                                                                                                                                 AS CRD_CLIENT_CODE ,
    SUBSTR(E.V_ACCOUNT_NUM_DEBIT,LENGTH(E.N_LEGAL_ENTITY_CD) +15,4)                                                                                                                                                                                                                                                 AS DBT_PL_SYMBOL ,
    SUBSTR(E.V_ACCOUNT_NUM_CREDIT,LENGTH(E.N_LEGAL_ENTITY_CD)+15,4)                                                                                                                                                                                                                                                 AS CRD_PL_SYMBOL ,
    SUBSTR(E.V_ACCOUNT_NUM_DEBIT,LENGTH(E.N_LEGAL_ENTITY_CD) +20,2)                                                                                                                                                                                                                                                 AS DBT_COA_CODE ,
    SUBSTR(E.V_ACCOUNT_NUM_CREDIT,LENGTH(E.n_legal_entity_cd)+20,2)                                                                                                                                                                                                                                                 AS CRD_COA_CODE ,
    DECODE(N,1,'Y',2,'N')                                                                                                                                                                                                                                                                                           AS REVERS_TXN_FLG ,
    TO_NUMBER ( TO_CHAR ( D_ACCOUNTING_DATE, 'DD' ) )                                                                                                                                                                                                                                                               AS POSTING_DAY_OF_MONTH ,
    SUBSTR(E.V_ACCOUNT_NUM_DEBIT,LENGTH(E.N_LEGAL_ENTITY_CD) +19,3)                                                                                                                                                                                                                                                 AS DBT_LAST_3 ,
    SUBSTR(E.V_ACCOUNT_NUM_CREDIT,LENGTH(E.N_LEGAL_ENTITY_CD)+19,3)                                                                                                                                                                                                                                                 AS CRD_LAST_3 ,
    SUBSTR(E.V_ACCOUNT_NUM_DEBIT,LENGTH(E.N_LEGAL_ENTITY_CD) +17,5)                                                                                                                                                                                                                                                 AS DBT_LAST_5 ,
    SUBSTR(E.V_ACCOUNT_NUM_CREDIT,LENGTH(E.n_legal_entity_cd)+17,5)                                                                                                                                                                                                                                                 AS CRD_LAST_5
  FROM FSDW.FSW_CON_ACCOUNT_ENTRY E
  JOIN FSDW.FSW_CON_ACCOUNT A_D
  ON ( E.V_ACCOUNT_NUM_DEBIT = A_D.V_ACCOUNT_NUM )
  JOIN FSDW.FSW_CON_ACCOUNT A_C
  ON ( E.V_ACCOUNT_NUM_CREDIT = A_C.V_ACCOUNT_NUM )
  CROSS JOIN
    (SELECT LEVEL N FROM DUAL CONNECT BY LEVEL <=2
    ) N
  LEFT JOIN FSDW.FSW_CON_ACCT_PTY_RELATIONSHIP C_D
  ON ( C_D.V_ACCOUNT_NUM = E.V_ACCOUNT_NUM_DEBIT )
  LEFT JOIN FSDW.FSW_PTY_PTY_LEGAL_MAP RS_D
  ON ( RS_D.V_PARTY_ID       = C_D.V_PARTY_ID
  AND RS_D.N_LEGAL_ENTITY_CD = E.N_LEGAL_ENTITY_CD
  AND E.D_ACCOUNTING_DATE BETWEEN RS_D.D_FROM_DATE AND RS_D.D_TO_DATE )
  LEFT JOIN FSDW.FSW_CON_ACCT_PTY_RELATIONSHIP C_C
  ON ( C_C.V_ACCOUNT_NUM = E.V_ACCOUNT_NUM_CREDIT )
  LEFT JOIN FSDW.FSW_PTY_PTY_LEGAL_MAP RS_C
  ON ( RS_C.V_PARTY_ID       = C_C.V_PARTY_ID
  AND RS_C.N_LEGAL_ENTITY_CD = E.N_LEGAL_ENTITY_CD
  AND E.D_ACCOUNTING_DATE BETWEEN RS_C.D_FROM_DATE AND RS_C.D_TO_DATE )
  LEFT JOIN FSI_OSV_TO_LS_SETUP GL_D
  ON GL_D.O2LS_BALANCE_ACNT_NO             = SUBSTR ( A_D.V_ACCOUNT_20_NUM, 1, 5 )
  AND SUBSTR(GL_D.O2LS_PL_SYMBOL_NO, 2, 4) = SUBSTR ( A_D.V_ACCOUNT_20_NUM, 14, 4 )
  AND LAST_DAY ( TRUNC ( D_ACCOUNTING_DATE, 'MM' ) ) BETWEEN GL_D.O2LS_START_DATE AND GL_D.O2LS_END_DATE
  LEFT JOIN FSI_OSV_TO_LS_SETUP GL_C
  ON GL_C.O2LS_BALANCE_ACNT_NO             = SUBSTR ( A_C.V_ACCOUNT_20_NUM, 1, 5 )
  AND SUBSTR(GL_C.O2LS_PL_SYMBOL_NO, 2, 4) = SUBSTR ( A_C.V_ACCOUNT_20_NUM, 14, 4 )
  AND LAST_DAY ( TRUNC ( D_ACCOUNTING_DATE, 'MM' ) ) BETWEEN GL_C.O2LS_START_DATE AND GL_C.O2LS_END_DATE


вот отчет sql_monitirng
Global Stats
=========================================================
| Elapsed |   Cpu   |    IO    | Buffer | Write | Write |
| Time(s) | Time(s) | Waits(s) |  Gets  | Reqs  | Bytes |
=========================================================
|     655 |      24 |      632 |   143K |  5900 |   1GB |
=========================================================

SQL Plan Monitoring Details (Plan Hash Value=1534040605)
=================================================================================================================================================================================================================================
| Id   |                 Operation                  |             Name              |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Write | Write | Mem | Temp | Activity |       Activity Detail        | Progress |
|      |                                            |                               | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs  | Bytes |     |      |   (%)    |         (# samples)          |          |
=================================================================================================================================================================================================================================
|    0 | SELECT STATEMENT                           |                               |         |       |           |        |     1 |          |       |       |     |      |          |                              |          |
|    1 |   HASH JOIN RIGHT OUTER                    |                               |    260M |   44M |         1 |    +36 |     1 |        0 |       |       |  1M |      |          |                              |          |
|    2 |    TABLE ACCESS FULL                       | FSI_OSV_TO_LS_SETUP           |    1645 |     4 |         1 |    +36 |     1 |     1696 |       |       |     |      |          |                              |          |
|    3 |    HASH JOIN RIGHT OUTER                   |                               |    260M |   44M |         1 |    +36 |     1 |        0 |       |       |  1M |      |          |                              |          |
|    4 |     TABLE ACCESS FULL                      | FSI_OSV_TO_LS_SETUP           |    1645 |     4 |         1 |    +36 |     1 |     1696 |       |       |     |      |          |                              |          |
|    5 |     HASH JOIN RIGHT OUTER                  |                               |    260M |   44M |       283 |     +1 |     1 |        0 |  3033 | 711MB | 82M | 956M |    43.46 | Cpu (12)                     |          |
|      |                                            |                               |         |       |           |        |       |          |       |       |     |      |          | direct path write temp (267) |          |
|    6 |      TABLE ACCESS FULL                     | FSW_PTY_PTY_LEGAL_MAP         |     17M | 25533 |       248 |    +36 |     1 |      17M |       |       |     |      |     0.62 | Cpu (3)                      |          |
|      |                                            |                               |         |       |           |        |       |          |       |       |     |      |          | resmgr:cpu quantum (1)       |          |
|    7 |      VIEW                                  |                               |    260M |   30M |           |        |     1 |          |       |       |     |      |          |                              |          |
| -> 8 |       HASH JOIN RIGHT OUTER                |                               |    260M |   30M |       369 |   +284 |     1 |        0 |  2365 | 554MB | 82M | 653M |    55.92 | Cpu (5)                      |          |
|      |                                            |                               |         |       |           |        |       |          |       |       |     |      |          | direct path write temp (354) |          |
| -> 9 |        TABLE ACCESS FULL                   | FSW_PTY_PTY_LEGAL_MAP         |     17M | 25533 |       370 |   +283 |     1 |      11M |       |       |     |      |          |                              |      70% |
|   10 |        HASH JOIN RIGHT OUTER               |                               |    260M |   19M |           |        |       |          |       |       |     |      |          |                              |          |
|   11 |         TABLE ACCESS FULL                  | FSW_CON_ACCT_PTY_RELATIONSHIP |      2M |  1702 |           |        |       |          |       |       |     |      |          |                              |          |
|   12 |         VIEW                               |                               |    260M |   10M |           |        |       |          |       |       |     |      |          |                              |          |
|   13 |          HASH JOIN RIGHT OUTER             |                               |    260M |   10M |           |        |       |          |       |       |     |      |          |                              |          |
|   14 |           TABLE ACCESS FULL                | FSW_CON_ACCT_PTY_RELATIONSHIP |      2M |  1702 |           |        |       |          |       |       |     |      |          |                              |          |
|   15 |           HASH JOIN                        |                               |    260M |    7M |           |        |       |          |       |       |     |      |          |                              |          |
|   16 |            TABLE ACCESS FULL               | FSW_CON_ACCOUNT               |     57M | 86437 |           |        |       |          |       |       |     |      |          |                              |          |
|   17 |            HASH JOIN                       |                               |    260M |    4M |           |        |       |          |       |       |     |      |          |                              |          |
|   18 |             MERGE JOIN CARTESIAN           |                               |     57M | 86439 |           |        |       |          |       |       |     |      |          |                              |          |
|   19 |              VIEW                          |                               |       1 |     2 |           |        |       |          |       |       |     |      |          |                              |          |
|   20 |               CONNECT BY WITHOUT FILTERING |                               |         |       |           |        |       |          |       |       |     |      |          |                              |          |
|   21 |                FAST DUAL                   |                               |       1 |     2 |           |        |       |          |       |       |     |      |          |                              |          |
|   22 |              BUFFER SORT                   |                               |     57M | 86439 |           |        |       |          |       |       |     |      |          |                              |          |
|   23 |               TABLE ACCESS FULL            | FSW_CON_ACCOUNT               |     57M | 86437 |           |        |       |          |       |       |     |      |          |                              |          |
|   24 |             PARTITION RANGE ALL            |                               |    260M |    1M |           |        |       |          |       |       |     |      |          |                              |          |
|   25 |              TABLE ACCESS FULL             | FSW_CON_ACCOUNT_ENTRY         |    260M |    1M |           |        |       |          |       |       |     |      |          |                              |          |
=================================================================================================================================================================================================================================


вот AWR отчет
Plan hash value: 1534040605                                                                                                                        
                                                                                                                                                    
 -------------------------------------------------------------------------------------------------------------------------------------------------- 
 | Id  | Operation                                | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop | 
 -------------------------------------------------------------------------------------------------------------------------------------------------- 
 |   0 | SELECT STATEMENT                         |                               |       |       |       |    43M(100)|          |       |       | 
 |   1 |  HASH JOIN RIGHT OUTER                   |                               |   260M|   344G|       |    43M  (1)|170:37:45 |       |       | 
 |   2 |   TABLE ACCESS FULL                      | FSI_OSV_TO_LS_SETUP           |  1645 | 52640 |       |     4   (0)| 00:00:01 |       |       | 
 |   3 |   HASH JOIN RIGHT OUTER                  |                               |   260M|   336G|       |    43M  (1)|170:37:32 |       |       | 
 |   4 |    TABLE ACCESS FULL                     | FSI_OSV_TO_LS_SETUP           |  1645 | 52640 |       |     4   (0)| 00:00:01 |       |       | 
 |   5 |    HASH JOIN RIGHT OUTER                 |                               |   260M|   328G|   881M|    43M  (1)|170:37:19 |       |       | 
 |   6 |     TABLE ACCESS FULL                    | FSW_PTY_PTY_LEGAL_MAP         |    16M|   692M|       | 25533   (1)| 00:05:58 |       |       | 
 |   7 |     VIEW                                 |                               |   260M|   317G|       |    29M  (1)|115:49:53 |       |       | 
 |   8 |      HASH JOIN RIGHT OUTER               |                               |   260M|   241G|   881M|    29M  (1)|115:49:53 |       |       | 
 |   9 |       TABLE ACCESS FULL                  | FSW_PTY_PTY_LEGAL_MAP         |    16M|   692M|       | 25533   (1)| 00:05:58 |       |       | 
 |  10 |       HASH JOIN RIGHT OUTER              |                               |   260M|   231G|    81M|    19M  (1)| 75:47:55 |       |       | 
 |  11 |        TABLE ACCESS FULL                 | FSW_CON_ACCT_PTY_RELATIONSHIP |  1528K|    64M|       |  1702   (1)| 00:00:24 |       |       | 
 |  12 |        VIEW                              |                               |   260M|   220G|       |  9721K  (1)| 37:48:19 |       |       | 
 |  13 |         HASH JOIN RIGHT OUTER            |                               |   260M|    79G|    81M|  9721K  (1)| 37:48:19 |       |       | 
 |  14 |          TABLE ACCESS FULL               | FSW_CON_ACCT_PTY_RELATIONSHIP |  1528K|    64M|       |  1702   (1)| 00:00:24 |       |       | 
 |  15 |          HASH JOIN                       |                               |   260M|    68G|  2971M|  6587K  (1)| 25:37:03 |       |       | 
 |  16 |           TABLE ACCESS FULL              | FSW_CON_ACCOUNT               |    56M|  2323M|       | 86437   (1)| 00:20:11 |       |       | 
 |  17 |           HASH JOIN                      |                               |   260M|    58G|  3674M|  3702K  (1)| 14:23:50 |       |       | 
 |  18 |            MERGE JOIN CARTESIAN          |                               |    56M|  3025M|       | 86439   (1)| 00:20:11 |       |       | 
 |  19 |             VIEW                         |                               |     1 |    13 |       |     2   (0)| 00:00:01 |       |       | 
 |  20 |              CONNECT BY WITHOUT FILTERING|                               |       |       |       |            |          |       |       | 
 |  21 |               FAST DUAL                  |                               |     1 |       |       |     2   (0)| 00:00:01 |       |       | 
 |  22 |             BUFFER SORT                  |                               |    56M|  2323M|       | 86439   (1)| 00:20:11 |       |       | 
 |  23 |              TABLE ACCESS FULL           | FSW_CON_ACCOUNT               |    56M|  2323M|       | 86437   (1)| 00:20:11 |       |       | 
 |  24 |            PARTITION RANGE ALL           |                               |   260M|    44G|       |  1381K  (1)| 05:22:18 |     1 |    19 | 
 |  25 |             TABLE ACCESS FULL            | FSW_CON_ACCOUNT_ENTRY         |   260M|    44G|       |  1381K  (1)| 05:22:18 |     1 |    19 | 
 -------------------------------------------------------------------------------------------------------------------------------------------------- 


merge cartisan есть, но откуда он берется ? из cross join ?
но он возвращает всего 2 строчки: 1 и 2 или это он тормозит ?

я не понимаю ((( и сейчас этот процесс увеличился, но из-за чего так резко ?

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

что мне еще можно посмотреть ?
8 апр 13, 00:23    [14149009]     Ответить | Цитировать Сообщить модератору
 Re: ХЕЛП: замедлился запрос  [new]
stdio
Member

Откуда:
Сообщений: 4524
а индексов у тебя вообще нет? совсем-совсем?
8 апр 13, 00:44    [14149070]     Ответить | Цитировать Сообщить модератору
 Re: ХЕЛП: замедлился запрос  [new]
dmitriy22
Member

Откуда:
Сообщений: 111
stdio, есть и практически на все таблицы, а что это обязательно должно в плане отображаться ?
8 апр 13, 00:58    [14149106]     Ответить | Цитировать Сообщить модератору
 Re: ХЕЛП: замедлился запрос  [new]
dmitriy22
Member

Откуда:
Сообщений: 111
у меня появилась идея...возможно это из-за PARTITION RANGE ALL т.к. таблица FSDW.FSW_CON_ACCOUNT_ENTRY партицированна, а сам запрос используется в качестве вьюхи и в выборке стоит условие типа
where as_of_date='03/31/2013'

но оптимизатор почему-то все равно берет PARTITION RANGE ALL ???? из-за чего

сейчас указал в самом селекте, который я описал выше, услолвие
where E.D_ACCOUNTING_DATE>=to_date('01.03.2013','DD.MM.YYYY')
и запрос стал отрабатывать за секунды....почему так ????
8 апр 13, 01:24    [14149160]     Ответить | Цитировать Сообщить модератору
 Re: ХЕЛП: замедлился запрос  [new]
ten
Member

Откуда: Екатеринбург
Сообщений: 1672
dmitriy22,
Неяное преобразование типов.
8 апр 13, 10:32    [14149852]     Ответить | Цитировать Сообщить модератору
 Re: ХЕЛП: замедлился запрос  [new]
JaRo
Member

Откуда:
Сообщений: 1659
Ну так а Вы сами посмотрите, разве у Вас as_of_date - это точно E.D_ACCOUNTING_DATE?.. Вот и Оракл приводит совсем к другому условию, которое мешает ему обращаться к партиции.

dmitriy22
merge cartisan есть, но откуда он берется ? из cross join ?
Ну да, из него: cross join - это ведь декартово произведение и есть; он не обязательно реализуется через MERGE JOIN CARTESIAN, но очень часто.
8 апр 13, 10:40    [14149907]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить