ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Выполнение запроса на разных серверах
8 сообщений из 8, страница 1 из 1
Выполнение запроса на разных серверах
    #39856157
Фотография Владимир СА
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир СА
Участник
Нужна помощь в анализе, почему один и тот же запрос на разных серверах выполняется за ЗНАЧИТЕЛЬНО разное время.
И так имеются 3 сервера; Прод, ПредПрод и Тест.
Параметры для сравнения отражены на рисунке.
Если нужны AWR и планы запроса на серверах, готов выложить.
...
Рейтинг: 0 / 0
02.09.2019, 07:48
| Ответить | Цитировать | Написать  
Выполнение запроса на разных серверах
    #39856158
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Владимир САЕсли http://www.bugtraq.ru/forum/faq/general/smart-questions.html] RTFM

P.S. Как можно быть настолько тупым в формулировании проблем?
...
Рейтинг: 0 / 0
02.09.2019, 07:53
| Ответить | Цитировать | Написать  
Выполнение запроса на разных серверах
    #39856161
Фотография Владимир СА
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир СА
Участник
 
Запрос
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
WITH
    TL_ORG
    AS
        (  SELECT T.ID
                      TITLE_ID,
                  MAX (T.YEAR)
                      TL_YEAR,
                  MAX (T.TITLE_NUMBER)
                      TITLE_NUMBER,
                  CASE MAX (T.TITLE_TYPE_ID)
                      WHEN 3 THEN MAX (T.TITLE_NUMBER)
                      ELSE MAX (TP.PIR_NUMBER)
                  END
                      TITLE_PIR_NUMBER,
                  MAX (T.TITLE_NAME) || ' ' || MAX (T.TITLE_ADDRESS)
                      TITLE_FULL_NAME,
                  MAX (ORG.SHORT_NAME)
                      KEEP (DENSE_RANK LAST ORDER BY TSO.IS_MAIN)
                      SHORT_NAME,
                  MAX (ORG.FULL_NAME)
                      KEEP (DENSE_RANK LAST ORDER BY TSO.IS_MAIN)
                      FULL_NAME,
                  TSO.ORGANIZATION_ROLE_TYPE_ID
                      ORGANIZATION_ROLE_TYPE_ID,
                  MAX (CC.NAME)
                      GOV_PROGRAM_NAME,
                  CASE MAX (T.TITLE_TYPE_ID)
                      WHEN 1 THEN 'Н'
                      WHEN 2 THEN 'П'
                      WHEN 3 THEN 'ПИР'
                      WHEN 4 THEN 'О'
                      WHEN 16 THEN 'С'
                      ELSE NULL
                  END
                      TITLE_TYPE,
                  MAX (TT.START_DATE)
                      TL_TERM_START_DT,
                  MAX (TT.END_DATE)
                      TL_TERM_END_DT
             FROM TITLE T
                  JOIN TITLE_DETAIL TD ON TD.ID = T.ID AND TD.ISACTIVE = 'Y'
                  LEFT JOIN TITLE_PIR TP ON T.ID = TP.TITLE_ID
                  JOIN TITLE_TERM TT
                      ON     TT.TITLE_ID = T.ID
                         AND TT.TITLE_TERM_TYPE_ID =
                             CASE
                                 WHEN T.TITLE_TYPE_ID IN (1, 2) THEN 1
                                 ELSE 2
                             END
                  JOIN TITLE_SUBJECT_ORG TSO
                      ON     TSO.TITLE_ID = T.ID
                         AND TSO.ORGANIZATION_ROLE_TYPE_ID IN (2, 3)
                  JOIN ORGANIZATION ORG ON ORG.ID = TSO.ORGANIZATION_ID
                  JOIN TITLE_COSTS_CLASSIFIER TCC ON TCC.TITLE_ID = T.ID
                  JOIN COSTS_CLASSIFIER CC
                      ON     CC.ID = TCC.COSTS_CLASSIFIER_ID
                         AND CC.COSTS_CLASSIFIER_ID IS NULL
            WHERE T.ID IN (SELECT TITLE_ID FROM TITLE_LIST_BY_ID)
         GROUP BY T.ID, TSO.ORGANIZATION_ROLE_TYPE_ID),
    TL_ORG_PV
    AS
        (SELECT TITLE_ID,
                TL_YEAR,
                TITLE_PIR_NUMBER,
                TITLE_NUMBER,
                TITLE_FULL_NAME,
                GV_CUST_SHN,
                CUST_SHN,
                GV_CUST_FN,
                CUST_FN,
                GOV_PROGRAM_NAME,
                TITLE_TYPE,
                TL_TERM_START_DT,
                TL_TERM_END_DT
           FROM TL_ORG
                PIVOT (MAX (SHORT_NAME) AS SHN, MAX (FULL_NAME) AS FN
                      FOR ORGANIZATION_ROLE_TYPE_ID
                      IN (2 AS GV_CUST, 3 AS CUST))),
    TL_BV
    AS
        (  SELECT TL_ORG_PV.TITLE_ID,
                  FS.ID                             FS_ID,
                  BUILD_INDICATOR_VALUE_TYPE_ID     BV_TYPE_ID,
                  MAX (GV_CUST_SHN)                 GV_CUST_SHN,
                  MAX (CUST_SHN)                    CUST_SHN,
                  MAX (GV_CUST_FN)                  GV_CUST_FN,
                  MAX (CUST_FN)                     CUST_FN,
                  MAX (GOV_PROGRAM_NAME)            GOV_PROGRAM_NAME,
                  MAX (TITLE_PIR_NUMBER)            TITLE_PIR_NUMBER,
                  MAX (TITLE_NUMBER)                TITLE_NUMBER,
                  MAX (TITLE_TYPE)                  TITLE_TYPE,
                  MAX (TL_TERM_START_DT)            TL_TERM_START_DT,
                  MAX (TL_TERM_END_DT)              TL_TERM_END_DT,
                  MAX (TITLE_FULL_NAME)             TITLE_FULL_NAME,
                  MAX (FS.NAME)                     FS_NAME,
                  SUM (B_VL.VALUE)                  BVL
             FROM TL_ORG_PV
                  JOIN BUILD_INDICATOR BI
                      ON     BI.TITLE_ID = TL_ORG_PV.TITLE_ID
                         AND BI.BUILD_INDICATOR_TYPE_ID IN (2, 3)
                  JOIN BUILD_INDICATOR_CLASSIFIER B_CLS
                      ON B_CLS.ID = BI.BUILD_INDICATOR_CLASSIFIER_ID
                  JOIN FINANCING_SOURCE FS ON FS.ID = B_CLS.FINANCING_SOURCE_ID
                  LEFT JOIN BUILD_INDICATOR_VALUE B_VL
                      ON B_VL.BUILD_INDICATOR_ID = BI.ID
            WHERE     BI.BUILD_INDICATOR_TYPE_ID = 3
                  AND (   B_VL.BUILD_INDICATOR_VALUE_TYPE_ID = 3
                       OR (    B_VL.BUILD_INDICATOR_VALUE_TYPE_ID = 4
                           AND B_VL.YEAR = TL_ORG_PV.TL_YEAR)
                       OR (    B_VL.BUILD_INDICATOR_VALUE_TYPE_ID = 1
                           AND B_VL.YEAR = TL_ORG_PV.TL_YEAR
                           AND B_VL.QUARTER_NUMBER IS NULL))
         GROUP BY TL_ORG_PV.TITLE_ID, FS.ID, BUILD_INDICATOR_VALUE_TYPE_ID),
    TL_BV_P
    AS
        (SELECT TITLE_ID,
                FS_ID,
                GV_CUST_SHN,
                CUST_SHN,
                GV_CUST_FN,
                CUST_FN,
                GOV_PROGRAM_NAME,
                TITLE_PIR_NUMBER,
                TITLE_NUMBER,
                TITLE_TYPE,
                TL_TERM_START_DT,
                TL_TERM_END_DT,
                TITLE_FULL_NAME,
                FS_NAME,
                CONTRACT_PRICE,
                COMPLETE_ON,
                NVL (CONTRACT_PRICE, 0) - NVL (COMPLETE_ON, 0)
                    CONSTRUCTION_END,
                FINANCING_LIMITS
           FROM TL_BV
                PIVOT (MAX (BVL)
                      FOR BV_TYPE_ID
                      IN (1 AS FINANCING_LIMITS,
                         3 AS CONTRACT_PRICE,
                         4 AS COMPLETE_ON))),
    TL_FCT
    AS
        (  SELECT TL.ID                                                         TITLE_ID,
                  FCLS.FINANCING_SOURCE_ID                                      FS_ID,
                  MAX (FVL.VALUE)
                  KEEP (DENSE_RANK LAST ORDER BY FITM.ITEM_DATE NULLS FIRST)    FCT_VL
             FROM TITLE TL
                  JOIN TITLE_DETAIL TD ON TD.ID = TL.ID AND TD.ISACTIVE = 'Y'
                  JOIN TITLE_FACT_INDICATOR TFI ON TFI.TITLE_ID = TL.ID
                  JOIN FACT_INDICATOR FI
                      ON     FI.ID = TFI.FACT_INDICATOR_ID
                         AND FI.FACT_INDICATOR_TYPE_ID = 1
                  LEFT JOIN FACT_INDICATOR_ITEM FITM
                      ON     FITM.FACT_INDICATOR_ID = FI.ID
                         AND FITM.FACT_INDICATOR_VALUE_TYPE_ID = 4
                         AND FITM.ITEM_DATE >= TRUNC ( :B1, 'YEAR')
                         AND TRUNC (FITM.ITEM_DATE) <= :B1
                  LEFT JOIN FACT_INDICATOR_CLASSIFIER FCLS
                      ON FCLS.ID = FITM.FACT_INDICATOR_CLASSIFIER_ID
                  LEFT JOIN BUDGET_CLASSIFIER BD
                      ON FCLS.BUDGET_CLASSIFIER_ID = BD.ID
                  LEFT JOIN ECONOMICAL_CLASSIFIER EC
                      ON FCLS.ECONOMICAL_CLASSIFIER_ID = EC.ID
                  LEFT JOIN FACT_INDICATOR_VALUE FVL
                      ON FVL.FACT_INDICATOR_ITEM_ID = FITM.ID
            WHERE     TL.ID IN (SELECT TITLE_ID FROM TITLE_LIST_BY_ID)
                  AND EXISTS
                          (SELECT 1
                             FROM BUILD_INDICATOR BI
                                  JOIN BUILD_INDICATOR_CLASSIFIER BCLS
                                      ON BCLS.ID =
                                         BI.BUILD_INDICATOR_CLASSIFIER_ID
                                  JOIN BUDGET_CLASSIFIER BBD
                                      ON BBD.ID = BCLS.BUDGET_CLASSIFIER_ID
                            WHERE     BI.TITLE_ID = TL.ID
                                  AND BI.BUILD_INDICATOR_TYPE_ID = 3
                                  AND BCLS.FINANCING_SOURCE_ID =
                                      FCLS.FINANCING_SOURCE_ID
                                  AND BCLS.ECONOMICAL_CLASSIFIER_ID =
                                      FCLS.ECONOMICAL_CLASSIFIER_ID
                                  AND BD.SECTION = BBD.SECTION
                                  AND BD.SUBSECTION = BBD.SUBSECTION
                                  AND BD.PROGRAM = BBD.PROGRAM
                                  AND BD.SUBPROGRAM = BBD.SUBPROGRAM
                                  AND BD.ACTION = BBD.ACTION
                                  AND BD.EXPENCE_KIND = BBD.EXPENCE_KIND)
         GROUP BY TL.ID,
                  FCLS.FINANCING_SOURCE_ID,
                     BD.SECTION
                  || BD.SUBSECTION
                  || '  '
                  || BD.PROGRAM
                  || '  '
                  || BD.SUBPROGRAM
                  || '  '
                  || BD.ACTION
                  || '  '
                  || BD.EXPENCE_KIND,
                  EC.CODE),
    TL_FCT1
    AS
        (  SELECT TITLE_ID, FS_ID, SUM (FCT_VL) TREASURY_DATA
             FROM TL_FCT
         GROUP BY TITLE_ID, FS_ID),
    TL_FCT2_3
    AS
        (  SELECT TFI.TITLE_ID                                                    TITLE_ID,
                  FCLS.FINANCING_SOURCE_ID                                        FS_ID,
                  FI.FACT_INDICATOR_TYPE_ID                                       FACT_IND_TYPE_ID,
                  SUM (FVL.VALUE)
                  KEEP (DENSE_RANK LAST ORDER BY FVL.MONTH_NUMBER NULLS FIRST)    FCT_VL
             FROM TITLE_FACT_INDICATOR TFI
                  JOIN FACT_INDICATOR FI
                      ON     FI.ID = TFI.FACT_INDICATOR_ID
                         AND FI.FACT_INDICATOR_TYPE_ID IN (2, 3)
                  LEFT JOIN FACT_INDICATOR_ITEM FITM
                      ON     FITM.FACT_INDICATOR_ID = FI.ID
                         AND FITM.FACT_INDICATOR_VALUE_TYPE_ID = 1
                  LEFT JOIN FACT_INDICATOR_VALUE FVL
                      ON FVL.FACT_INDICATOR_ITEM_ID = FITM.ID
                  LEFT JOIN FACT_INDICATOR_CLASSIFIER FCLS
                      ON FCLS.ID = FITM.FACT_INDICATOR_CLASSIFIER_ID
            WHERE     FVL.MONTH_NUMBER <= EXTRACT (MONTH FROM :B1)
                  AND TFI.TITLE_ID IN (SELECT DISTINCT TITLE_ID
                                         FROM TL_FCT1)
         GROUP BY TFI.TITLE_ID,
                  FCLS.FINANCING_SOURCE_ID,
                  FI.FACT_INDICATOR_TYPE_ID),
    TL_FCT2_3_P
    AS
        (SELECT TITLE_ID,
                FS_ID,
                CUSTOMER_DATA,
                MOSGORSTAT_DATA
           FROM TL_FCT2_3
                PIVOT (MAX (FCT_VL)
                      FOR FACT_IND_TYPE_ID
                      IN (2 AS CUSTOMER_DATA, 3 AS MOSGORSTAT_DATA))),
    TL_FCT_RES
    AS
        (SELECT NVL (TL_FCT1.TITLE_ID, TL_FCT2_3_P.TITLE_ID)     TITLE_ID,
                NVL (TL_FCT1.FS_ID, TL_FCT2_3_P.FS_ID)           FS_ID,
                TREASURY_DATA,
                CUSTOMER_DATA,
                MOSGORSTAT_DATA
           FROM TL_FCT1
                FULL JOIN TL_FCT2_3_P
                    ON     TL_FCT1.TITLE_ID = TL_FCT2_3_P.TITLE_ID
                       AND TL_FCT1.FS_ID = TL_FCT2_3_P.FS_ID)
  SELECT GV_CUST_FN           GOV_CUSTOMER_NAME,
         GOV_PROGRAM_NAME,
         CUST_FN              CUSTOMER_NAME,
         TITLE_PIR_NUMBER,
         TITLE_NUMBER,
         TITLE_TYPE,
         TL_TERM_START_DT     TITLE_TERM_START_DATE,
         TL_TERM_END_DT       TITLE_TERM_END_DATE,
         TITLE_FULL_NAME,
         FS_NAME              FINANCING_SOURCE_NAME,
         CONTRACT_PRICE,
         COMPLETE_ON,
         CONSTRUCTION_END,
         FINANCING_LIMITS,
         TREASURY_DATA,
         CUSTOMER_DATA,
         MOSGORSTAT_DATA
    FROM TL_BV_P
         LEFT JOIN TL_FCT_RES
             ON     TL_BV_P.TITLE_ID = TL_FCT_RES.TITLE_ID
                AND TL_BV_P.FS_ID = TL_FCT_RES.FS_ID
ORDER BY GV_CUST_SHN,
         GOV_PROGRAM_NAME,
         CUST_SHN,
         TITLE_NUMBER,
         FS_NAME
;

...
Рейтинг: 0 / 0
02.09.2019, 08:01
| Ответить | Цитировать | Написать  
Выполнение запроса на разных серверах
    #39856163
Фотография Владимир СА
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир СА
Участник
 
План с Прода
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
Plan hash value: 3756200411
 
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                                |       |       |       |   107K(100)|          |
|   1 |  TEMP TABLE TRANSFORMATION                        |                                |       |       |       |            |          |
|   2 |   LOAD AS SELECT                                  |                                |       |       |       |            |          |
|   3 |    SORT GROUP BY NOSORT                           |                                |  1967 | 43274 |       | 66079   (1)| 00:13:13 |
|   4 |     VIEW                                          |                                |  1967 | 43274 |       | 66079   (1)| 00:13:13 |
|   5 |      SORT GROUP BY                                |                                |  1967 |   211K|       | 66079   (1)| 00:13:13 |
|*  6 |       HASH JOIN SEMI                              |                                |  1967 |   211K|       | 66078   (1)| 00:13:13 |
|*  7 |        HASH JOIN RIGHT SEMI                       |                                |  3738 |   288K|       | 49197   (1)| 00:09:51 |
|   8 |         TABLE ACCESS FULL                         | TITLE_LIST_BY_ID               |  2275 | 29575 |       |     3   (0)| 00:00:01 |
|   9 |         VIEW                                      |                                |   901K|    56M|       | 49191   (1)| 00:09:51 |
|* 10 |          HASH JOIN RIGHT OUTER                    |                                |   901K|    54M|    14M| 49191   (1)| 00:09:51 |
|  11 |           TABLE ACCESS FULL                       | FACT_INDICATOR_VALUE           |   659K|  7086K|       |  1110   (1)| 00:00:14 |
|  12 |           VIEW                                    |                                |   548K|    27M|       | 45696   (1)| 00:09:09 |
|* 13 |            HASH JOIN RIGHT OUTER                  |                                |   548K|    29M|       | 45696   (1)| 00:09:09 |
|  14 |             TABLE ACCESS FULL                     | ECONOMICAL_CLASSIFIER          |    31 |   279 |       |     4   (0)| 00:00:01 |
|  15 |             VIEW                                  |                                |   548K|    25M|       | 45691   (1)| 00:09:09 |
|* 16 |              HASH JOIN RIGHT OUTER                |                                |   548K|    30M|       | 45691   (1)| 00:09:09 |
|  17 |               TABLE ACCESS FULL                   | BUDGET_CLASSIFIER              |  1335 | 33375 |       |    23   (0)| 00:00:01 |
|  18 |               VIEW                                |                                |   548K|    17M|       | 45666   (1)| 00:09:08 |
|* 19 |                HASH JOIN RIGHT OUTER              |                                |   548K|    22M|       | 45666   (1)| 00:09:08 |
|  20 |                 TABLE ACCESS FULL                 | FACT_INDICATOR_CLASSIFIER      | 55891 |   927K|       |   140   (1)| 00:00:02 |
|  21 |                 VIEW                              |                                |   548K|    13M|       | 45524   (1)| 00:09:07 |
|* 22 |                  HASH JOIN RIGHT OUTER            |                                |   548K|    19M|       | 45524   (1)| 00:09:07 |
|* 23 |                   TABLE ACCESS FULL               | FACT_INDICATOR_ITEM            |     2 |    54 |       |  1287   (1)| 00:00:16 |
|  24 |                   VIEW                            |                                |   548K|  5892K|       | 44235   (1)| 00:08:51 |
|* 25 |                    HASH JOIN                      |                                |   548K|     9M|       | 44235   (1)| 00:08:51 |
|* 26 |                     TABLE ACCESS FULL             | FACT_INDICATOR                 | 21559 |   168K|       |    72   (2)| 00:00:01 |
|  27 |                     VIEW                          |                                |  2895K|    30M|       | 44155   (1)| 00:08:50 |
|* 28 |                      HASH JOIN                    |                                |  2895K|    44M|    10M| 44155   (1)| 00:08:50 |
|  29 |                       VIEW                        |                                |   584K|  3422K|       | 38848   (1)| 00:07:47 |
|* 30 |                        HASH JOIN                  |                                |   584K|  8557K|    10M| 38848   (1)| 00:07:47 |
|  31 |                         INDEX FAST FULL SCAN      | TITLE_PK                       |   584K|  3422K|       |   344   (1)| 00:00:05 |
|  32 |                         VIEW                      | TITLE_DETAIL                   |   584K|  5134K|       | 37419   (1)| 00:07:30 |
|  33 |                          VIEW                     |                                |   584K|    10M|       | 37419   (1)| 00:07:30 |
|* 34 |                           FILTER                  |                                |       |       |       |            |          |
|* 35 |                            HASH JOIN OUTER        |                                |   584K|  9698K|    11M| 37419   (1)| 00:07:30 |
|  36 |                             TABLE ACCESS FULL     | TITLE                          |   584K|  4563K|       | 10004   (1)| 00:02:01 |
|  37 |                             VIEW                  |                                |   560K|  4924K|       | 26300   (1)| 00:05:16 |
|  38 |                              VIEW                 |                                |   560K|  3283K|       | 26300   (1)| 00:05:16 |
|* 39 |                               HASH JOIN           |                                |   560K|    29M|    19M| 26300   (1)| 00:05:16 |
|  40 |                                VIEW               |                                |   560K|    13M|       | 14150   (1)| 00:02:50 |
|  41 |                                 HASH GROUP BY     |                                |   560K|    13M|    21M| 14150   (1)| 00:02:50 |
|* 42 |                                  TABLE ACCESS FULL| TITLE                          |   560K|    13M|       | 10004   (1)| 00:02:01 |
|  43 |                                TABLE ACCESS FULL  | TITLE                          |   584K|    16M|       | 10004   (1)| 00:02:01 |
|  44 |                       TABLE ACCESS FULL           | TITLE_FACT_INDICATOR           |  2920K|    27M|       |  1751   (1)| 00:00:22 |
|  45 |        VIEW                                       | VW_SQ_2                        |   736K|    21M|       | 16879   (1)| 00:03:23 |
|  46 |         VIEW                                      |                                |   736K|    30M|       | 16879   (1)| 00:03:23 |
|* 47 |          HASH JOIN                                |                                |   736K|    36M|       | 16879   (1)| 00:03:23 |
|  48 |           TABLE ACCESS FULL                       | BUDGET_CLASSIFIER              |  1335 | 33375 |       |    23   (0)| 00:00:01 |
|  49 |           VIEW                                    |                                |   736K|    18M|       | 16854   (1)| 00:03:23 |
|* 50 |            HASH JOIN                              |                                |   736K|    22M|    19M| 16854   (1)| 00:03:23 |
|* 51 |             TABLE ACCESS FULL                     | BUILD_INDICATOR                |   736K|    11M|       |  9751   (1)| 00:01:58 |
|  52 |             TABLE ACCESS FULL                     | BUILD_INDICATOR_CLASSIFIER     |  2343K|    35M|       |  3007   (1)| 00:00:37 |
|  53 |   SORT ORDER BY                                   |                                |  1185 |  4311K|  4752K| 41038   (1)| 00:08:13 |
|* 54 |    HASH JOIN RIGHT OUTER                          |                                |  1185 |  4311K|       | 40115   (1)| 00:08:02 |
|  55 |     VIEW                                          |                                |  1261 | 81965 |       |  4381   (2)| 00:00:53 |
|  56 |      VIEW                                         | VW_FOJ_0                       |  1261 |   112K|       |  4381   (2)| 00:00:53 |
|* 57 |       HASH JOIN FULL OUTER                        |                                |  1261 |   112K|       |  4381   (2)| 00:00:53 |
|  58 |        VIEW                                       |                                |  1191 | 46449 |       |     3   (0)| 00:00:01 |
|  59 |         TABLE ACCESS FULL                         | SYS_TEMP_0FD9D7B05_58753D40    |  1191 | 26202 |       |     3   (0)| 00:00:01 |
|  60 |        VIEW                                       |                                |  1261 | 65572 |       |  4378   (2)| 00:00:53 |
|  61 |         HASH GROUP BY PIVOT                       |                                |  1261 | 44135 |       |  4378   (2)| 00:00:53 |
|  62 |          VIEW                                     |                                |  1261 | 44135 |       |  4378   (2)| 00:00:53 |
|  63 |           SORT GROUP BY                           |                                |  1261 | 64311 |       |  4378   (2)| 00:00:53 |
|* 64 |            HASH JOIN RIGHT SEMI                   |                                |  1261 | 64311 |       |  4377   (2)| 00:00:53 |
|  65 |             VIEW                                  | VW_NSO_1                       |  1191 |  7146 |       |     3   (0)| 00:00:01 |
|  66 |              VIEW                                 |                                |  1191 | 15483 |       |     3   (0)| 00:00:01 |
|  67 |               TABLE ACCESS FULL                   | SYS_TEMP_0FD9D7B05_58753D40    |  1191 | 26202 |       |     3   (0)| 00:00:01 |
|  68 |             VIEW                                  |                                |   623K|    26M|       |  4372   (2)| 00:00:53 |
|* 69 |              HASH JOIN                            |                                |   623K|    34M|       |  4372   (2)| 00:00:53 |
|* 70 |               HASH JOIN                           |                                | 24312 |  1139K|       |  2612   (1)| 00:00:32 |
|* 71 |                HASH JOIN OUTER                    |                                | 22352 |   763K|       |  1491   (1)| 00:00:18 |
|* 72 |                 HASH JOIN                         |                                | 22352 |   589K|       |  1351   (1)| 00:00:17 |
|* 73 |                  TABLE ACCESS FULL                | FACT_INDICATOR_ITEM            | 22352 |   414K|       |  1279   (1)| 00:00:16 |
|* 74 |                  TABLE ACCESS FULL                | FACT_INDICATOR                 | 44857 |   350K|       |    72   (2)| 00:00:01 |
|  75 |                 TABLE ACCESS FULL                 | FACT_INDICATOR_CLASSIFIER      | 55891 |   436K|       |   139   (0)| 00:00:02 |
|* 76 |                TABLE ACCESS FULL                  | FACT_INDICATOR_VALUE           |   160K|  2035K|       |  1120   (2)| 00:00:14 |
|  77 |               TABLE ACCESS FULL                   | TITLE_FACT_INDICATOR           |  2920K|    27M|       |  1751   (1)| 00:00:22 |
|  78 |     VIEW                                          |                                |  1185 |  4236K|       | 35734   (1)| 00:07:09 |
|  79 |      HASH GROUP BY PIVOT                          |                                |  1185 |  4206K|  4752K| 35734   (1)| 00:07:09 |
|  80 |       VIEW                                        |                                |  1185 |  4206K|       | 34835   (1)| 00:06:59 |
|  81 |        HASH GROUP BY                              |                                |  1185 |  4183K|  4752K| 34835   (1)| 00:06:59 |
|* 82 |         HASH JOIN                                 |                                |  1185 |  4183K|       | 33938   (1)| 00:06:48 |
|  83 |          TABLE ACCESS FULL                        | FINANCING_SOURCE               |    45 |  3780 |       |     3   (0)| 00:00:01 |
|* 84 |          FILTER                                   |                                |       |       |       |            |          |
|  85 |           NESTED LOOPS OUTER                      |                                |  1159 |  3996K|       | 33935   (1)| 00:06:48 |
|  86 |            NESTED LOOPS                           |                                |  1228 |  4212K|       | 30262   (1)| 00:06:04 |
|  87 |             VIEW                                  |                                |  1565 |  5353K|       | 27131   (1)| 00:05:26 |
|  88 |              HASH GROUP BY PIVOT                  |                                |  1565 |  4418K|  6272K| 27131   (1)| 00:05:26 |
|  89 |               VIEW                                | VM_NWVW_3                      |  1565 |  4418K|       | 26183   (1)| 00:05:15 |
|  90 |                SORT GROUP BY                      |                                |  1565 |   825K|       | 26183   (1)| 00:05:15 |
|* 91 |                 HASH JOIN                         |                                |  1565 |   825K|       | 26182   (1)| 00:05:15 |
|  92 |                  TABLE ACCESS FULL                | ORGANIZATION                   |  1042 |    98K|       |    10   (0)| 00:00:01 |
|  93 |                  NESTED LOOPS                     |                                |  1577 |   682K|       | 26172   (1)| 00:05:15 |
|  94 |                   NESTED LOOPS                    |                                |   775 |   323K|       | 23847   (1)| 00:04:47 |
|* 95 |                    FILTER                         |                                |       |       |       |            |          |
|  96 |                     NESTED LOOPS OUTER            |                                |   482 |   184K|       | 21918   (1)| 00:04:24 |
|  97 |                      NESTED LOOPS                 |                                |   482 |   180K|       | 20954   (1)| 00:04:12 |
|* 98 |                       HASH JOIN                   |                                |   486 |   171K|       | 19496   (1)| 00:03:54 |
|* 99 |                        TABLE ACCESS FULL          | COSTS_CLASSIFIER               |    21 |  1470 |       |     4   (0)| 00:00:01 |
|*100 |                        HASH JOIN                  |                                |  5810 |  1651K|       | 19492   (1)| 00:03:54 |
|*101 |                         HASH JOIN RIGHT SEMI      |                                |  2275 |   622K|       | 18299   (1)| 00:03:40 |
| 102 |                          TABLE ACCESS FULL        | TITLE_LIST_BY_ID               |  2275 | 29575 |       |     3   (0)| 00:00:01 |
|*103 |                          HASH JOIN RIGHT OUTER    |                                |   584K|   148M|    10M| 18294   (1)| 00:03:40 |
| 104 |                           TABLE ACCESS FULL       | TITLE_PIR                      |   456K|  4908K|       |   386   (1)| 00:00:05 |
| 105 |                           TABLE ACCESS FULL       | TITLE                          |   584K|   142M|       | 10004   (1)| 00:02:01 |
| 106 |                         TABLE ACCESS FULL         | TITLE_COSTS_CLASSIFIER         |  1496K|    15M|       |  1189   (1)| 00:00:15 |
|*107 |                       TABLE ACCESS BY INDEX ROWID | TITLE_TERM                     |     1 |    23 |       |     3   (0)| 00:00:01 |
|*108 |                        INDEX RANGE SCAN           | IDX_TITLE_TERM_TTL_ID          |     2 |       |       |     2   (0)| 00:00:01 |
| 109 |                      VIEW PUSHED PREDICATE        |                                |     1 |     9 |       |     2   (0)| 00:00:01 |
| 110 |                       NESTED LOOPS                |                                |     1 |    55 |       | 14153   (1)| 00:02:50 |
| 111 |                        TABLE ACCESS BY INDEX ROWID| TITLE                          |     1 |    30 |       |     3   (0)| 00:00:01 |
|*112 |                         INDEX UNIQUE SCAN         | TITLE_PK                       |     1 |       |       |     2   (0)| 00:00:01 |
|*113 |                        VIEW                       |                                |     1 |    25 |       | 14150   (1)| 00:02:50 |
| 114 |                         SORT GROUP BY             |                                |   560K|    13M|    21M| 14150   (1)| 00:02:50 |
|*115 |                          TABLE ACCESS FULL        | TITLE                          |   560K|    13M|       | 10004   (1)| 00:02:01 |
|*116 |                    TABLE ACCESS BY INDEX ROWID    | BUILD_INDICATOR                |     2 |    68 |       |     4   (0)| 00:00:01 |
|*117 |                     INDEX RANGE SCAN              | IDX_BUILD_INDICATOR_TITLE_ID   |     7 |       |       |     2   (0)| 00:00:01 |
|*118 |                   TABLE ACCESS BY INDEX ROWID     | TITLE_SUBJECT_ORG              |     2 |    32 |       |     3   (0)| 00:00:01 |
|*119 |                    INDEX RANGE SCAN               | IDX_TITLE_SUBJECT_ORG_TITLE_OM |     4 |       |       |     2   (0)| 00:00:01 |
|*120 |             TABLE ACCESS BY INDEX ROWID           | BUILD_INDICATOR_CLASSIFIER     |     1 |    10 |       |     2   (0)| 00:00:01 |
|*121 |              INDEX UNIQUE SCAN                    | BUILD_INDICATOR_CLASSIFIER_PK  |     1 |       |       |     1   (0)| 00:00:01 |
| 122 |            TABLE ACCESS BY INDEX ROWID            | BUILD_INDICATOR_VALUE          |    10 |   180 |       |     3   (0)| 00:00:01 |
|*123 |             INDEX RANGE SCAN                      | IDX_BIV_ID__BI_ID              |    10 |       |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("ITEM_0"="TL"."ID" AND "ITEM_1"="FCLS"."FINANCING_SOURCE_ID" AND "ITEM_2"="FCLS"."ECONOMICAL_CLASSIFIER_ID" AND 
              "BD"."SECTION"="ITEM_3" AND "BD"."SUBSECTION"="ITEM_4" AND "BD"."PROGRAM"="ITEM_5" AND "BD"."SUBPROGRAM"="ITEM_6" AND 
              "BD"."ACTION"="ITEM_7" AND "BD"."EXPENCE_KIND"="ITEM_8")
   7 - access("TL"."ID"="TITLE_ID")
  10 - access("FVL"."FACT_INDICATOR_ITEM_ID"="FITM"."ID")
  13 - access("FCLS"."ECONOMICAL_CLASSIFIER_ID"="EC"."ID")
  16 - access("FCLS"."BUDGET_CLASSIFIER_ID"="BD"."ID")
  19 - access("FCLS"."ID"="FITM"."FACT_INDICATOR_CLASSIFIER_ID")
  22 - access("FITM"."FACT_INDICATOR_ID"="FI"."ID")
  23 - filter(("FITM"."FACT_INDICATOR_VALUE_TYPE_ID"=4 AND "FITM"."ITEM_DATE">=TRUNC(:B1,'fmyear') AND 
              TRUNC(INTERNAL_FUNCTION("FITM"."ITEM_DATE"))<=:B1))
  25 - access("FI"."ID"="TFI"."FACT_INDICATOR_ID")
  26 - filter("FI"."FACT_INDICATOR_TYPE_ID"=1)
  28 - access("TFI"."TITLE_ID"="TL"."ID")
  30 - access("TD"."ID"="TL"."ID")
  34 - filter(CASE  WHEN ("H"."ISACTIVE"=1 AND "TT"."DELETE_DATE" IS NULL) THEN 'Y' ELSE 'N' END ='Y')
  35 - access("H"."ID"="TT"."ID")
  39 - access("T"."ENTITY_ID"="G"."ENTITY_ID" AND "T"."STAGE_ID"="G"."STAGE_ID" AND "T"."YEAR"="G"."YEAR" AND 
              "T"."CREATE_DATE"="G"."CREATE_DATE")
  42 - filter("T"."DELETE_DATE" IS NULL)
  47 - access("BBD"."ID"="BCLS"."BUDGET_CLASSIFIER_ID")
  50 - access("BCLS"."ID"="BI"."BUILD_INDICATOR_CLASSIFIER_ID")
  51 - filter(("BI"."BUILD_INDICATOR_TYPE_ID"=3 AND "BI"."BUILD_INDICATOR_CLASSIFIER_ID" IS NOT NULL))
  54 - access("TL_BV_P"."FS_ID"="TL_FCT_RES"."FS_ID" AND "TL_BV_P"."TITLE_ID"="TL_FCT_RES"."TITLE_ID")
  57 - access("TL_FCT1"."TITLE_ID"="TL_FCT2_3_P"."TITLE_ID" AND "TL_FCT1"."FS_ID"="TL_FCT2_3_P"."FS_ID")
  64 - access("TFI"."TITLE_ID"="TITLE_ID")
  69 - access("FI"."ID"="TFI"."FACT_INDICATOR_ID")
  70 - access("FVL"."FACT_INDICATOR_ITEM_ID"="FITM"."ID")
  71 - access("FCLS"."ID"="FITM"."FACT_INDICATOR_CLASSIFIER_ID")
  72 - access("FITM"."FACT_INDICATOR_ID"="FI"."ID")
  73 - filter("FITM"."FACT_INDICATOR_VALUE_TYPE_ID"=1)
  74 - filter(("FI"."FACT_INDICATOR_TYPE_ID"=2 OR "FI"."FACT_INDICATOR_TYPE_ID"=3))
  76 - filter("FVL"."MONTH_NUMBER"<=EXTRACT(MONTH FROM :B1))
  82 - access("FS"."ID"="B_CLS"."FINANCING_SOURCE_ID")
  84 - filter(("B_VL"."BUILD_INDICATOR_VALUE_TYPE_ID"=3 OR ("B_VL"."BUILD_INDICATOR_VALUE_TYPE_ID"=4 AND 
              "B_VL"."YEAR"="from$_subquery$_021"."TL_YEAR" AND "B_VL"."YEAR" IS NOT NULL) OR ("B_VL"."BUILD_INDICATOR_VALUE_TYPE_ID"=1 AND 
              "B_VL"."YEAR"="from$_subquery$_021"."TL_YEAR" AND "B_VL"."QUARTER_NUMBER" IS NULL AND "B_VL"."YEAR" IS NOT NULL)))
  91 - access("ORG"."ID"="TSO"."ORGANIZATION_ID")
  95 - filter(CASE  WHEN ("H"."ISACTIVE"=1 AND "DELETE_DATE" IS NULL) THEN 'Y' ELSE 'N' END ='Y')
  98 - access("CC"."ID"="TCC"."COSTS_CLASSIFIER_ID")
  99 - filter("CC"."COSTS_CLASSIFIER_ID" IS NULL)
 100 - access("TCC"."TITLE_ID"="T"."ID")
 101 - access("T"."ID"="TITLE_ID")
 103 - access("T"."ID"="TP"."TITLE_ID")
 107 - filter("TT"."TITLE_TERM_TYPE_ID"=CASE "T"."TITLE_TYPE_ID" WHEN 1 THEN 1 WHEN 2 THEN 1 ELSE 2 END )
 108 - access("TT"."TITLE_ID"="T"."ID")
 112 - access("T"."ID"="T"."ID")
 113 - filter(("T"."ENTITY_ID"="G"."ENTITY_ID" AND "T"."STAGE_ID"="G"."STAGE_ID" AND "T"."YEAR"="G"."YEAR" AND 
              "T"."CREATE_DATE"="G"."CREATE_DATE"))
 115 - filter("T"."DELETE_DATE" IS NULL)
 116 - filter("BI"."BUILD_INDICATOR_TYPE_ID"=3)
 117 - access("BI"."TITLE_ID"="T"."ID")
 118 - filter(("TSO"."ORGANIZATION_ROLE_TYPE_ID"=2 OR "TSO"."ORGANIZATION_ROLE_TYPE_ID"=3))
 119 - access("TSO"."TITLE_ID"="T"."ID")
 120 - filter("B_CLS"."FINANCING_SOURCE_ID" IS NOT NULL)
 121 - access("B_CLS"."ID"="BI"."BUILD_INDICATOR_CLASSIFIER_ID")
 123 - access("B_VL"."BUILD_INDICATOR_ID"="from$_subquery$_021"."ID")



 
План с ПредПрода
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
Plan hash value: 3756200411
 
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                                |       |       |       |   107K(100)|          |
|   1 |  TEMP TABLE TRANSFORMATION                        |                                |       |       |       |            |          |
|   2 |   LOAD AS SELECT                                  |                                |       |       |       |            |          |
|   3 |    SORT GROUP BY NOSORT                           |                                |  1967 | 43274 |       | 66079   (1)| 00:13:13 |
|   4 |     VIEW                                          |                                |  1967 | 43274 |       | 66079   (1)| 00:13:13 |
|   5 |      SORT GROUP BY                                |                                |  1967 |   211K|       | 66079   (1)| 00:13:13 |
|*  6 |       HASH JOIN SEMI                              |                                |  1967 |   211K|       | 66078   (1)| 00:13:13 |
|*  7 |        HASH JOIN RIGHT SEMI                       |                                |  3738 |   288K|       | 49197   (1)| 00:09:51 |
|   8 |         TABLE ACCESS FULL                         | TITLE_LIST_BY_ID               |  2275 | 29575 |       |     3   (0)| 00:00:01 |
|   9 |         VIEW                                      |                                |   901K|    56M|       | 49191   (1)| 00:09:51 |
|* 10 |          HASH JOIN RIGHT OUTER                    |                                |   901K|    54M|    14M| 49191   (1)| 00:09:51 |
|  11 |           TABLE ACCESS FULL                       | FACT_INDICATOR_VALUE           |   659K|  7086K|       |  1110   (1)| 00:00:14 |
|  12 |           VIEW                                    |                                |   548K|    27M|       | 45696   (1)| 00:09:09 |
|* 13 |            HASH JOIN RIGHT OUTER                  |                                |   548K|    29M|       | 45696   (1)| 00:09:09 |
|  14 |             TABLE ACCESS FULL                     | ECONOMICAL_CLASSIFIER          |    31 |   279 |       |     4   (0)| 00:00:01 |
|  15 |             VIEW                                  |                                |   548K|    25M|       | 45691   (1)| 00:09:09 |
|* 16 |              HASH JOIN RIGHT OUTER                |                                |   548K|    30M|       | 45691   (1)| 00:09:09 |
|  17 |               TABLE ACCESS FULL                   | BUDGET_CLASSIFIER              |  1335 | 33375 |       |    23   (0)| 00:00:01 |
|  18 |               VIEW                                |                                |   548K|    17M|       | 45666   (1)| 00:09:08 |
|* 19 |                HASH JOIN RIGHT OUTER              |                                |   548K|    22M|       | 45666   (1)| 00:09:08 |
|  20 |                 TABLE ACCESS FULL                 | FACT_INDICATOR_CLASSIFIER      | 55891 |   927K|       |   140   (1)| 00:00:02 |
|  21 |                 VIEW                              |                                |   548K|    13M|       | 45524   (1)| 00:09:07 |
|* 22 |                  HASH JOIN RIGHT OUTER            |                                |   548K|    19M|       | 45524   (1)| 00:09:07 |
|* 23 |                   TABLE ACCESS FULL               | FACT_INDICATOR_ITEM            |     2 |    54 |       |  1287   (1)| 00:00:16 |
|  24 |                   VIEW                            |                                |   548K|  5892K|       | 44235   (1)| 00:08:51 |
|* 25 |                    HASH JOIN                      |                                |   548K|     9M|       | 44235   (1)| 00:08:51 |
|* 26 |                     TABLE ACCESS FULL             | FACT_INDICATOR                 | 21559 |   168K|       |    72   (2)| 00:00:01 |
|  27 |                     VIEW                          |                                |  2895K|    30M|       | 44155   (1)| 00:08:50 |
|* 28 |                      HASH JOIN                    |                                |  2895K|    44M|    10M| 44155   (1)| 00:08:50 |
|  29 |                       VIEW                        |                                |   584K|  3422K|       | 38848   (1)| 00:07:47 |
|* 30 |                        HASH JOIN                  |                                |   584K|  8557K|    10M| 38848   (1)| 00:07:47 |
|  31 |                         INDEX FAST FULL SCAN      | TITLE_PK                       |   584K|  3422K|       |   344   (1)| 00:00:05 |
|  32 |                         VIEW                      | TITLE_DETAIL                   |   584K|  5134K|       | 37419   (1)| 00:07:30 |
|  33 |                          VIEW                     |                                |   584K|    10M|       | 37419   (1)| 00:07:30 |
|* 34 |                           FILTER                  |                                |       |       |       |            |          |
|* 35 |                            HASH JOIN OUTER        |                                |   584K|  9698K|    11M| 37419   (1)| 00:07:30 |
|  36 |                             TABLE ACCESS FULL     | TITLE                          |   584K|  4563K|       | 10004   (1)| 00:02:01 |
|  37 |                             VIEW                  |                                |   560K|  4924K|       | 26300   (1)| 00:05:16 |
|  38 |                              VIEW                 |                                |   560K|  3283K|       | 26300   (1)| 00:05:16 |
|* 39 |                               HASH JOIN           |                                |   560K|    29M|    19M| 26300   (1)| 00:05:16 |
|  40 |                                VIEW               |                                |   560K|    13M|       | 14150   (1)| 00:02:50 |
|  41 |                                 HASH GROUP BY     |                                |   560K|    13M|    21M| 14150   (1)| 00:02:50 |
|* 42 |                                  TABLE ACCESS FULL| TITLE                          |   560K|    13M|       | 10004   (1)| 00:02:01 |
|  43 |                                TABLE ACCESS FULL  | TITLE                          |   584K|    16M|       | 10004   (1)| 00:02:01 |
|  44 |                       TABLE ACCESS FULL           | TITLE_FACT_INDICATOR           |  2920K|    27M|       |  1751   (1)| 00:00:22 |
|  45 |        VIEW                                       | VW_SQ_2                        |   736K|    21M|       | 16879   (1)| 00:03:23 |
|  46 |         VIEW                                      |                                |   736K|    30M|       | 16879   (1)| 00:03:23 |
|* 47 |          HASH JOIN                                |                                |   736K|    36M|       | 16879   (1)| 00:03:23 |
|  48 |           TABLE ACCESS FULL                       | BUDGET_CLASSIFIER              |  1335 | 33375 |       |    23   (0)| 00:00:01 |
|  49 |           VIEW                                    |                                |   736K|    18M|       | 16854   (1)| 00:03:23 |
|* 50 |            HASH JOIN                              |                                |   736K|    22M|    19M| 16854   (1)| 00:03:23 |
|* 51 |             TABLE ACCESS FULL                     | BUILD_INDICATOR                |   736K|    11M|       |  9751   (1)| 00:01:58 |
|  52 |             TABLE ACCESS FULL                     | BUILD_INDICATOR_CLASSIFIER     |  2343K|    35M|       |  3007   (1)| 00:00:37 |
|  53 |   SORT ORDER BY                                   |                                |  1185 |  4311K|  4752K| 41038   (1)| 00:08:13 |
|* 54 |    HASH JOIN RIGHT OUTER                          |                                |  1185 |  4311K|       | 40115   (1)| 00:08:02 |
|  55 |     VIEW                                          |                                |  1261 | 81965 |       |  4381   (2)| 00:00:53 |
|  56 |      VIEW                                         | VW_FOJ_0                       |  1261 |   112K|       |  4381   (2)| 00:00:53 |
|* 57 |       HASH JOIN FULL OUTER                        |                                |  1261 |   112K|       |  4381   (2)| 00:00:53 |
|  58 |        VIEW                                       |                                |  1191 | 46449 |       |     3   (0)| 00:00:01 |
|  59 |         TABLE ACCESS FULL                         | SYS_TEMP_0FD9D7B05_58753D40    |  1191 | 26202 |       |     3   (0)| 00:00:01 |
|  60 |        VIEW                                       |                                |  1261 | 65572 |       |  4378   (2)| 00:00:53 |
|  61 |         HASH GROUP BY PIVOT                       |                                |  1261 | 44135 |       |  4378   (2)| 00:00:53 |
|  62 |          VIEW                                     |                                |  1261 | 44135 |       |  4378   (2)| 00:00:53 |
|  63 |           SORT GROUP BY                           |                                |  1261 | 64311 |       |  4378   (2)| 00:00:53 |
|* 64 |            HASH JOIN RIGHT SEMI                   |                                |  1261 | 64311 |       |  4377   (2)| 00:00:53 |
|  65 |             VIEW                                  | VW_NSO_1                       |  1191 |  7146 |       |     3   (0)| 00:00:01 |
|  66 |              VIEW                                 |                                |  1191 | 15483 |       |     3   (0)| 00:00:01 |
|  67 |               TABLE ACCESS FULL                   | SYS_TEMP_0FD9D7B05_58753D40    |  1191 | 26202 |       |     3   (0)| 00:00:01 |
|  68 |             VIEW                                  |                                |   623K|    26M|       |  4372   (2)| 00:00:53 |
|* 69 |              HASH JOIN                            |                                |   623K|    34M|       |  4372   (2)| 00:00:53 |
|* 70 |               HASH JOIN                           |                                | 24312 |  1139K|       |  2612   (1)| 00:00:32 |
|* 71 |                HASH JOIN OUTER                    |                                | 22352 |   763K|       |  1491   (1)| 00:00:18 |
|* 72 |                 HASH JOIN                         |                                | 22352 |   589K|       |  1351   (1)| 00:00:17 |
|* 73 |                  TABLE ACCESS FULL                | FACT_INDICATOR_ITEM            | 22352 |   414K|       |  1279   (1)| 00:00:16 |
|* 74 |                  TABLE ACCESS FULL                | FACT_INDICATOR                 | 44857 |   350K|       |    72   (2)| 00:00:01 |
|  75 |                 TABLE ACCESS FULL                 | FACT_INDICATOR_CLASSIFIER      | 55891 |   436K|       |   139   (0)| 00:00:02 |
|* 76 |                TABLE ACCESS FULL                  | FACT_INDICATOR_VALUE           |   160K|  2035K|       |  1120   (2)| 00:00:14 |
|  77 |               TABLE ACCESS FULL                   | TITLE_FACT_INDICATOR           |  2920K|    27M|       |  1751   (1)| 00:00:22 |
|  78 |     VIEW                                          |                                |  1185 |  4236K|       | 35734   (1)| 00:07:09 |
|  79 |      HASH GROUP BY PIVOT                          |                                |  1185 |  4206K|  4752K| 35734   (1)| 00:07:09 |
|  80 |       VIEW                                        |                                |  1185 |  4206K|       | 34835   (1)| 00:06:59 |
|  81 |        HASH GROUP BY                              |                                |  1185 |  4183K|  4752K| 34835   (1)| 00:06:59 |
|* 82 |         HASH JOIN                                 |                                |  1185 |  4183K|       | 33938   (1)| 00:06:48 |
|  83 |          TABLE ACCESS FULL                        | FINANCING_SOURCE               |    45 |  3780 |       |     3   (0)| 00:00:01 |
|* 84 |          FILTER                                   |                                |       |       |       |            |          |
|  85 |           NESTED LOOPS OUTER                      |                                |  1159 |  3996K|       | 33935   (1)| 00:06:48 |
|  86 |            NESTED LOOPS                           |                                |  1228 |  4212K|       | 30262   (1)| 00:06:04 |
|  87 |             VIEW                                  |                                |  1565 |  5353K|       | 27131   (1)| 00:05:26 |
|  88 |              HASH GROUP BY PIVOT                  |                                |  1565 |  4418K|  6272K| 27131   (1)| 00:05:26 |
|  89 |               VIEW                                | VM_NWVW_3                      |  1565 |  4418K|       | 26183   (1)| 00:05:15 |
|  90 |                SORT GROUP BY                      |                                |  1565 |   825K|       | 26183   (1)| 00:05:15 |
|* 91 |                 HASH JOIN                         |                                |  1565 |   825K|       | 26182   (1)| 00:05:15 |
|  92 |                  TABLE ACCESS FULL                | ORGANIZATION                   |  1042 |    98K|       |    10   (0)| 00:00:01 |
|  93 |                  NESTED LOOPS                     |                                |  1577 |   682K|       | 26172   (1)| 00:05:15 |
|  94 |                   NESTED LOOPS                    |                                |   775 |   323K|       | 23847   (1)| 00:04:47 |
|* 95 |                    FILTER                         |                                |       |       |       |            |          |
|  96 |                     NESTED LOOPS OUTER            |                                |   482 |   184K|       | 21918   (1)| 00:04:24 |
|  97 |                      NESTED LOOPS                 |                                |   482 |   180K|       | 20954   (1)| 00:04:12 |
|* 98 |                       HASH JOIN                   |                                |   486 |   171K|       | 19496   (1)| 00:03:54 |
|* 99 |                        TABLE ACCESS FULL          | COSTS_CLASSIFIER               |    21 |  1470 |       |     4   (0)| 00:00:01 |
|*100 |                        HASH JOIN                  |                                |  5810 |  1651K|       | 19492   (1)| 00:03:54 |
|*101 |                         HASH JOIN RIGHT SEMI      |                                |  2275 |   622K|       | 18299   (1)| 00:03:40 |
| 102 |                          TABLE ACCESS FULL        | TITLE_LIST_BY_ID               |  2275 | 29575 |       |     3   (0)| 00:00:01 |
|*103 |                          HASH JOIN RIGHT OUTER    |                                |   584K|   148M|    10M| 18294   (1)| 00:03:40 |
| 104 |                           TABLE ACCESS FULL       | TITLE_PIR                      |   456K|  4908K|       |   386   (1)| 00:00:05 |
| 105 |                           TABLE ACCESS FULL       | TITLE                          |   584K|   142M|       | 10004   (1)| 00:02:01 |
| 106 |                         TABLE ACCESS FULL         | TITLE_COSTS_CLASSIFIER         |  1496K|    15M|       |  1189   (1)| 00:00:15 |
|*107 |                       TABLE ACCESS BY INDEX ROWID | TITLE_TERM                     |     1 |    23 |       |     3   (0)| 00:00:01 |
|*108 |                        INDEX RANGE SCAN           | IDX_TITLE_TERM_TTL_ID          |     2 |       |       |     2   (0)| 00:00:01 |
| 109 |                      VIEW PUSHED PREDICATE        |                                |     1 |     9 |       |     2   (0)| 00:00:01 |
| 110 |                       NESTED LOOPS                |                                |     1 |    55 |       | 14153   (1)| 00:02:50 |
| 111 |                        TABLE ACCESS BY INDEX ROWID| TITLE                          |     1 |    30 |       |     3   (0)| 00:00:01 |
|*112 |                         INDEX UNIQUE SCAN         | TITLE_PK                       |     1 |       |       |     2   (0)| 00:00:01 |
|*113 |                        VIEW                       |                                |     1 |    25 |       | 14150   (1)| 00:02:50 |
| 114 |                         SORT GROUP BY             |                                |   560K|    13M|    21M| 14150   (1)| 00:02:50 |
|*115 |                          TABLE ACCESS FULL        | TITLE                          |   560K|    13M|       | 10004   (1)| 00:02:01 |
|*116 |                    TABLE ACCESS BY INDEX ROWID    | BUILD_INDICATOR                |     2 |    68 |       |     4   (0)| 00:00:01 |
|*117 |                     INDEX RANGE SCAN              | IDX_BUILD_INDICATOR_TITLE_ID   |     7 |       |       |     2   (0)| 00:00:01 |
|*118 |                   TABLE ACCESS BY INDEX ROWID     | TITLE_SUBJECT_ORG              |     2 |    32 |       |     3   (0)| 00:00:01 |
|*119 |                    INDEX RANGE SCAN               | IDX_TITLE_SUBJECT_ORG_TITLE_OM |     4 |       |       |     2   (0)| 00:00:01 |
|*120 |             TABLE ACCESS BY INDEX ROWID           | BUILD_INDICATOR_CLASSIFIER     |     1 |    10 |       |     2   (0)| 00:00:01 |
|*121 |              INDEX UNIQUE SCAN                    | BUILD_INDICATOR_CLASSIFIER_PK  |     1 |       |       |     1   (0)| 00:00:01 |
| 122 |            TABLE ACCESS BY INDEX ROWID            | BUILD_INDICATOR_VALUE          |    10 |   180 |       |     3   (0)| 00:00:01 |
|*123 |             INDEX RANGE SCAN                      | IDX_BIV_ID__BI_ID              |    10 |       |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("ITEM_0"="TL"."ID" AND "ITEM_1"="FCLS"."FINANCING_SOURCE_ID" AND "ITEM_2"="FCLS"."ECONOMICAL_CLASSIFIER_ID" AND 
              "BD"."SECTION"="ITEM_3" AND "BD"."SUBSECTION"="ITEM_4" AND "BD"."PROGRAM"="ITEM_5" AND "BD"."SUBPROGRAM"="ITEM_6" AND 
              "BD"."ACTION"="ITEM_7" AND "BD"."EXPENCE_KIND"="ITEM_8")
   7 - access("TL"."ID"="TITLE_ID")
  10 - access("FVL"."FACT_INDICATOR_ITEM_ID"="FITM"."ID")
  13 - access("FCLS"."ECONOMICAL_CLASSIFIER_ID"="EC"."ID")
  16 - access("FCLS"."BUDGET_CLASSIFIER_ID"="BD"."ID")
  19 - access("FCLS"."ID"="FITM"."FACT_INDICATOR_CLASSIFIER_ID")
  22 - access("FITM"."FACT_INDICATOR_ID"="FI"."ID")
  23 - filter(("FITM"."FACT_INDICATOR_VALUE_TYPE_ID"=4 AND "FITM"."ITEM_DATE">=TRUNC(:B1,'fmyear') AND 
              TRUNC(INTERNAL_FUNCTION("FITM"."ITEM_DATE"))<=:B1))
  25 - access("FI"."ID"="TFI"."FACT_INDICATOR_ID")
  26 - filter("FI"."FACT_INDICATOR_TYPE_ID"=1)
  28 - access("TFI"."TITLE_ID"="TL"."ID")
  30 - access("TD"."ID"="TL"."ID")
  34 - filter(CASE  WHEN ("H"."ISACTIVE"=1 AND "TT"."DELETE_DATE" IS NULL) THEN 'Y' ELSE 'N' END ='Y')
  35 - access("H"."ID"="TT"."ID")
  39 - access("T"."ENTITY_ID"="G"."ENTITY_ID" AND "T"."STAGE_ID"="G"."STAGE_ID" AND "T"."YEAR"="G"."YEAR" AND 
              "T"."CREATE_DATE"="G"."CREATE_DATE")
  42 - filter("T"."DELETE_DATE" IS NULL)
  47 - access("BBD"."ID"="BCLS"."BUDGET_CLASSIFIER_ID")
  50 - access("BCLS"."ID"="BI"."BUILD_INDICATOR_CLASSIFIER_ID")
  51 - filter(("BI"."BUILD_INDICATOR_TYPE_ID"=3 AND "BI"."BUILD_INDICATOR_CLASSIFIER_ID" IS NOT NULL))
  54 - access("TL_BV_P"."FS_ID"="TL_FCT_RES"."FS_ID" AND "TL_BV_P"."TITLE_ID"="TL_FCT_RES"."TITLE_ID")
  57 - access("TL_FCT1"."TITLE_ID"="TL_FCT2_3_P"."TITLE_ID" AND "TL_FCT1"."FS_ID"="TL_FCT2_3_P"."FS_ID")
  64 - access("TFI"."TITLE_ID"="TITLE_ID")
  69 - access("FI"."ID"="TFI"."FACT_INDICATOR_ID")
  70 - access("FVL"."FACT_INDICATOR_ITEM_ID"="FITM"."ID")
  71 - access("FCLS"."ID"="FITM"."FACT_INDICATOR_CLASSIFIER_ID")
  72 - access("FITM"."FACT_INDICATOR_ID"="FI"."ID")
  73 - filter("FITM"."FACT_INDICATOR_VALUE_TYPE_ID"=1)
  74 - filter(("FI"."FACT_INDICATOR_TYPE_ID"=2 OR "FI"."FACT_INDICATOR_TYPE_ID"=3))
  76 - filter("FVL"."MONTH_NUMBER"<=EXTRACT(MONTH FROM :B1))
  82 - access("FS"."ID"="B_CLS"."FINANCING_SOURCE_ID")
  84 - filter(("B_VL"."BUILD_INDICATOR_VALUE_TYPE_ID"=3 OR ("B_VL"."BUILD_INDICATOR_VALUE_TYPE_ID"=4 AND 
              "B_VL"."YEAR"="from$_subquery$_021"."TL_YEAR" AND "B_VL"."YEAR" IS NOT NULL) OR ("B_VL"."BUILD_INDICATOR_VALUE_TYPE_ID"=1 AND 
              "B_VL"."YEAR"="from$_subquery$_021"."TL_YEAR" AND "B_VL"."QUARTER_NUMBER" IS NULL AND "B_VL"."YEAR" IS NOT NULL)))
  91 - access("ORG"."ID"="TSO"."ORGANIZATION_ID")
  95 - filter(CASE  WHEN ("H"."ISACTIVE"=1 AND "DELETE_DATE" IS NULL) THEN 'Y' ELSE 'N' END ='Y')
  98 - access("CC"."ID"="TCC"."COSTS_CLASSIFIER_ID")
  99 - filter("CC"."COSTS_CLASSIFIER_ID" IS NULL)
 100 - access("TCC"."TITLE_ID"="T"."ID")
 101 - access("T"."ID"="TITLE_ID")
 103 - access("T"."ID"="TP"."TITLE_ID")
 107 - filter("TT"."TITLE_TERM_TYPE_ID"=CASE "T"."TITLE_TYPE_ID" WHEN 1 THEN 1 WHEN 2 THEN 1 ELSE 2 END )
 108 - access("TT"."TITLE_ID"="T"."ID")
 112 - access("T"."ID"="T"."ID")
 113 - filter(("T"."ENTITY_ID"="G"."ENTITY_ID" AND "T"."STAGE_ID"="G"."STAGE_ID" AND "T"."YEAR"="G"."YEAR" AND 
              "T"."CREATE_DATE"="G"."CREATE_DATE"))
 115 - filter("T"."DELETE_DATE" IS NULL)
 116 - filter("BI"."BUILD_INDICATOR_TYPE_ID"=3)
 117 - access("BI"."TITLE_ID"="T"."ID")
 118 - filter(("TSO"."ORGANIZATION_ROLE_TYPE_ID"=2 OR "TSO"."ORGANIZATION_ROLE_TYPE_ID"=3))
 119 - access("TSO"."TITLE_ID"="T"."ID")
 120 - filter("B_CLS"."FINANCING_SOURCE_ID" IS NOT NULL)
 121 - access("B_CLS"."ID"="BI"."BUILD_INDICATOR_CLASSIFIER_ID")
 123 - access("B_VL"."BUILD_INDICATOR_ID"="from$_subquery$_021"."ID")



 
План с Теста
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
Plan hash value: 3756200411
 
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                                |       |       |       |   107K(100)|          |
|   1 |  TEMP TABLE TRANSFORMATION                        |                                |       |       |       |            |          |
|   2 |   LOAD AS SELECT                                  |                                |       |       |       |            |          |
|   3 |    SORT GROUP BY NOSORT                           |                                |  1967 | 43274 |       | 66079   (1)| 00:13:13 |
|   4 |     VIEW                                          |                                |  1967 | 43274 |       | 66079   (1)| 00:13:13 |
|   5 |      SORT GROUP BY                                |                                |  1967 |   211K|       | 66079   (1)| 00:13:13 |
|*  6 |       HASH JOIN SEMI                              |                                |  1967 |   211K|       | 66078   (1)| 00:13:13 |
|*  7 |        HASH JOIN RIGHT SEMI                       |                                |  3738 |   288K|       | 49197   (1)| 00:09:51 |
|   8 |         TABLE ACCESS FULL                         | TITLE_LIST_BY_ID               |  2275 | 29575 |       |     3   (0)| 00:00:01 |
|   9 |         VIEW                                      |                                |   901K|    56M|       | 49191   (1)| 00:09:51 |
|* 10 |          HASH JOIN RIGHT OUTER                    |                                |   901K|    54M|    14M| 49191   (1)| 00:09:51 |
|  11 |           TABLE ACCESS FULL                       | FACT_INDICATOR_VALUE           |   659K|  7086K|       |  1110   (1)| 00:00:14 |
|  12 |           VIEW                                    |                                |   548K|    27M|       | 45696   (1)| 00:09:09 |
|* 13 |            HASH JOIN RIGHT OUTER                  |                                |   548K|    29M|       | 45696   (1)| 00:09:09 |
|  14 |             TABLE ACCESS FULL                     | ECONOMICAL_CLASSIFIER          |    31 |   279 |       |     4   (0)| 00:00:01 |
|  15 |             VIEW                                  |                                |   548K|    25M|       | 45691   (1)| 00:09:09 |
|* 16 |              HASH JOIN RIGHT OUTER                |                                |   548K|    30M|       | 45691   (1)| 00:09:09 |
|  17 |               TABLE ACCESS FULL                   | BUDGET_CLASSIFIER              |  1335 | 33375 |       |    23   (0)| 00:00:01 |
|  18 |               VIEW                                |                                |   548K|    17M|       | 45666   (1)| 00:09:08 |
|* 19 |                HASH JOIN RIGHT OUTER              |                                |   548K|    22M|       | 45666   (1)| 00:09:08 |
|  20 |                 TABLE ACCESS FULL                 | FACT_INDICATOR_CLASSIFIER      | 55891 |   927K|       |   140   (1)| 00:00:02 |
|  21 |                 VIEW                              |                                |   548K|    13M|       | 45524   (1)| 00:09:07 |
|* 22 |                  HASH JOIN RIGHT OUTER            |                                |   548K|    19M|       | 45524   (1)| 00:09:07 |
|* 23 |                   TABLE ACCESS FULL               | FACT_INDICATOR_ITEM            |     2 |    54 |       |  1287   (1)| 00:00:16 |
|  24 |                   VIEW                            |                                |   548K|  5892K|       | 44235   (1)| 00:08:51 |
|* 25 |                    HASH JOIN                      |                                |   548K|     9M|       | 44235   (1)| 00:08:51 |
|* 26 |                     TABLE ACCESS FULL             | FACT_INDICATOR                 | 21559 |   168K|       |    72   (2)| 00:00:01 |
|  27 |                     VIEW                          |                                |  2895K|    30M|       | 44155   (1)| 00:08:50 |
|* 28 |                      HASH JOIN                    |                                |  2895K|    44M|    10M| 44155   (1)| 00:08:50 |
|  29 |                       VIEW                        |                                |   584K|  3422K|       | 38848   (1)| 00:07:47 |
|* 30 |                        HASH JOIN                  |                                |   584K|  8557K|    10M| 38848   (1)| 00:07:47 |
|  31 |                         INDEX FAST FULL SCAN      | TITLE_PK                       |   584K|  3422K|       |   344   (1)| 00:00:05 |
|  32 |                         VIEW                      | TITLE_DETAIL                   |   584K|  5134K|       | 37419   (1)| 00:07:30 |
|  33 |                          VIEW                     |                                |   584K|    10M|       | 37419   (1)| 00:07:30 |
|* 34 |                           FILTER                  |                                |       |       |       |            |          |
|* 35 |                            HASH JOIN OUTER        |                                |   584K|  9698K|    11M| 37419   (1)| 00:07:30 |
|  36 |                             TABLE ACCESS FULL     | TITLE                          |   584K|  4563K|       | 10004   (1)| 00:02:01 |
|  37 |                             VIEW                  |                                |   560K|  4924K|       | 26300   (1)| 00:05:16 |
|  38 |                              VIEW                 |                                |   560K|  3283K|       | 26300   (1)| 00:05:16 |
|* 39 |                               HASH JOIN           |                                |   560K|    29M|    19M| 26300   (1)| 00:05:16 |
|  40 |                                VIEW               |                                |   560K|    13M|       | 14150   (1)| 00:02:50 |
|  41 |                                 HASH GROUP BY     |                                |   560K|    13M|    21M| 14150   (1)| 00:02:50 |
|* 42 |                                  TABLE ACCESS FULL| TITLE                          |   560K|    13M|       | 10004   (1)| 00:02:01 |
|  43 |                                TABLE ACCESS FULL  | TITLE                          |   584K|    16M|       | 10004   (1)| 00:02:01 |
|  44 |                       TABLE ACCESS FULL           | TITLE_FACT_INDICATOR           |  2920K|    27M|       |  1751   (1)| 00:00:22 |
|  45 |        VIEW                                       | VW_SQ_2                        |   736K|    21M|       | 16879   (1)| 00:03:23 |
|  46 |         VIEW                                      |                                |   736K|    30M|       | 16879   (1)| 00:03:23 |
|* 47 |          HASH JOIN                                |                                |   736K|    36M|       | 16879   (1)| 00:03:23 |
|  48 |           TABLE ACCESS FULL                       | BUDGET_CLASSIFIER              |  1335 | 33375 |       |    23   (0)| 00:00:01 |
|  49 |           VIEW                                    |                                |   736K|    18M|       | 16854   (1)| 00:03:23 |
|* 50 |            HASH JOIN                              |                                |   736K|    22M|    19M| 16854   (1)| 00:03:23 |
|* 51 |             TABLE ACCESS FULL                     | BUILD_INDICATOR                |   736K|    11M|       |  9751   (1)| 00:01:58 |
|  52 |             TABLE ACCESS FULL                     | BUILD_INDICATOR_CLASSIFIER     |  2343K|    35M|       |  3007   (1)| 00:00:37 |
|  53 |   SORT ORDER BY                                   |                                |  1185 |  4311K|  4752K| 41038   (1)| 00:08:13 |
|* 54 |    HASH JOIN RIGHT OUTER                          |                                |  1185 |  4311K|       | 40115   (1)| 00:08:02 |
|  55 |     VIEW                                          |                                |  1261 | 81965 |       |  4381   (2)| 00:00:53 |
|  56 |      VIEW                                         | VW_FOJ_0                       |  1261 |   112K|       |  4381   (2)| 00:00:53 |
|* 57 |       HASH JOIN FULL OUTER                        |                                |  1261 |   112K|       |  4381   (2)| 00:00:53 |
|  58 |        VIEW                                       |                                |  1191 | 46449 |       |     3   (0)| 00:00:01 |
|  59 |         TABLE ACCESS FULL                         | SYS_TEMP_0FD9D7B05_58753D40    |  1191 | 26202 |       |     3   (0)| 00:00:01 |
|  60 |        VIEW                                       |                                |  1261 | 65572 |       |  4378   (2)| 00:00:53 |
|  61 |         HASH GROUP BY PIVOT                       |                                |  1261 | 44135 |       |  4378   (2)| 00:00:53 |
|  62 |          VIEW                                     |                                |  1261 | 44135 |       |  4378   (2)| 00:00:53 |
|  63 |           SORT GROUP BY                           |                                |  1261 | 64311 |       |  4378   (2)| 00:00:53 |
|* 64 |            HASH JOIN RIGHT SEMI                   |                                |  1261 | 64311 |       |  4377   (2)| 00:00:53 |
|  65 |             VIEW                                  | VW_NSO_1                       |  1191 |  7146 |       |     3   (0)| 00:00:01 |
|  66 |              VIEW                                 |                                |  1191 | 15483 |       |     3   (0)| 00:00:01 |
|  67 |               TABLE ACCESS FULL                   | SYS_TEMP_0FD9D7B05_58753D40    |  1191 | 26202 |       |     3   (0)| 00:00:01 |
|  68 |             VIEW                                  |                                |   623K|    26M|       |  4372   (2)| 00:00:53 |
|* 69 |              HASH JOIN                            |                                |   623K|    34M|       |  4372   (2)| 00:00:53 |
|* 70 |               HASH JOIN                           |                                | 24312 |  1139K|       |  2612   (1)| 00:00:32 |
|* 71 |                HASH JOIN OUTER                    |                                | 22352 |   763K|       |  1491   (1)| 00:00:18 |
|* 72 |                 HASH JOIN                         |                                | 22352 |   589K|       |  1351   (1)| 00:00:17 |
|* 73 |                  TABLE ACCESS FULL                | FACT_INDICATOR_ITEM            | 22352 |   414K|       |  1279   (1)| 00:00:16 |
|* 74 |                  TABLE ACCESS FULL                | FACT_INDICATOR                 | 44857 |   350K|       |    72   (2)| 00:00:01 |
|  75 |                 TABLE ACCESS FULL                 | FACT_INDICATOR_CLASSIFIER      | 55891 |   436K|       |   139   (0)| 00:00:02 |
|* 76 |                TABLE ACCESS FULL                  | FACT_INDICATOR_VALUE           |   160K|  2035K|       |  1120   (2)| 00:00:14 |
|  77 |               TABLE ACCESS FULL                   | TITLE_FACT_INDICATOR           |  2920K|    27M|       |  1751   (1)| 00:00:22 |
|  78 |     VIEW                                          |                                |  1185 |  4236K|       | 35734   (1)| 00:07:09 |
|  79 |      HASH GROUP BY PIVOT                          |                                |  1185 |  4206K|  4752K| 35734   (1)| 00:07:09 |
|  80 |       VIEW                                        |                                |  1185 |  4206K|       | 34835   (1)| 00:06:59 |
|  81 |        HASH GROUP BY                              |                                |  1185 |  4183K|  4752K| 34835   (1)| 00:06:59 |
|* 82 |         HASH JOIN                                 |                                |  1185 |  4183K|       | 33938   (1)| 00:06:48 |
|  83 |          TABLE ACCESS FULL                        | FINANCING_SOURCE               |    45 |  3780 |       |     3   (0)| 00:00:01 |
|* 84 |          FILTER                                   |                                |       |       |       |            |          |
|  85 |           NESTED LOOPS OUTER                      |                                |  1159 |  3996K|       | 33935   (1)| 00:06:48 |
|  86 |            NESTED LOOPS                           |                                |  1228 |  4212K|       | 30262   (1)| 00:06:04 |
|  87 |             VIEW                                  |                                |  1565 |  5353K|       | 27131   (1)| 00:05:26 |
|  88 |              HASH GROUP BY PIVOT                  |                                |  1565 |  4418K|  6272K| 27131   (1)| 00:05:26 |
|  89 |               VIEW                                | VM_NWVW_3                      |  1565 |  4418K|       | 26183   (1)| 00:05:15 |
|  90 |                SORT GROUP BY                      |                                |  1565 |   825K|       | 26183   (1)| 00:05:15 |
|* 91 |                 HASH JOIN                         |                                |  1565 |   825K|       | 26182   (1)| 00:05:15 |
|  92 |                  TABLE ACCESS FULL                | ORGANIZATION                   |  1042 |    98K|       |    10   (0)| 00:00:01 |
|  93 |                  NESTED LOOPS                     |                                |  1577 |   682K|       | 26172   (1)| 00:05:15 |
|  94 |                   NESTED LOOPS                    |                                |   775 |   323K|       | 23847   (1)| 00:04:47 |
|* 95 |                    FILTER                         |                                |       |       |       |            |          |
|  96 |                     NESTED LOOPS OUTER            |                                |   482 |   184K|       | 21918   (1)| 00:04:24 |
|  97 |                      NESTED LOOPS                 |                                |   482 |   180K|       | 20954   (1)| 00:04:12 |
|* 98 |                       HASH JOIN                   |                                |   486 |   171K|       | 19496   (1)| 00:03:54 |
|* 99 |                        TABLE ACCESS FULL          | COSTS_CLASSIFIER               |    21 |  1470 |       |     4   (0)| 00:00:01 |
|*100 |                        HASH JOIN                  |                                |  5810 |  1651K|       | 19492   (1)| 00:03:54 |
|*101 |                         HASH JOIN RIGHT SEMI      |                                |  2275 |   622K|       | 18299   (1)| 00:03:40 |
| 102 |                          TABLE ACCESS FULL        | TITLE_LIST_BY_ID               |  2275 | 29575 |       |     3   (0)| 00:00:01 |
|*103 |                          HASH JOIN RIGHT OUTER    |                                |   584K|   148M|    10M| 18294   (1)| 00:03:40 |
| 104 |                           TABLE ACCESS FULL       | TITLE_PIR                      |   456K|  4908K|       |   386   (1)| 00:00:05 |
| 105 |                           TABLE ACCESS FULL       | TITLE                          |   584K|   142M|       | 10004   (1)| 00:02:01 |
| 106 |                         TABLE ACCESS FULL         | TITLE_COSTS_CLASSIFIER         |  1496K|    15M|       |  1189   (1)| 00:00:15 |
|*107 |                       TABLE ACCESS BY INDEX ROWID | TITLE_TERM                     |     1 |    23 |       |     3   (0)| 00:00:01 |
|*108 |                        INDEX RANGE SCAN           | IDX_TITLE_TERM_TTL_ID          |     2 |       |       |     2   (0)| 00:00:01 |
| 109 |                      VIEW PUSHED PREDICATE        |                                |     1 |     9 |       |     2   (0)| 00:00:01 |
| 110 |                       NESTED LOOPS                |                                |     1 |    55 |       | 14153   (1)| 00:02:50 |
| 111 |                        TABLE ACCESS BY INDEX ROWID| TITLE                          |     1 |    30 |       |     3   (0)| 00:00:01 |
|*112 |                         INDEX UNIQUE SCAN         | TITLE_PK                       |     1 |       |       |     2   (0)| 00:00:01 |
|*113 |                        VIEW                       |                                |     1 |    25 |       | 14150   (1)| 00:02:50 |
| 114 |                         SORT GROUP BY             |                                |   560K|    13M|    21M| 14150   (1)| 00:02:50 |
|*115 |                          TABLE ACCESS FULL        | TITLE                          |   560K|    13M|       | 10004   (1)| 00:02:01 |
|*116 |                    TABLE ACCESS BY INDEX ROWID    | BUILD_INDICATOR                |     2 |    68 |       |     4   (0)| 00:00:01 |
|*117 |                     INDEX RANGE SCAN              | IDX_BUILD_INDICATOR_TITLE_ID   |     7 |       |       |     2   (0)| 00:00:01 |
|*118 |                   TABLE ACCESS BY INDEX ROWID     | TITLE_SUBJECT_ORG              |     2 |    32 |       |     3   (0)| 00:00:01 |
|*119 |                    INDEX RANGE SCAN               | IDX_TITLE_SUBJECT_ORG_TITLE_OM |     4 |       |       |     2   (0)| 00:00:01 |
|*120 |             TABLE ACCESS BY INDEX ROWID           | BUILD_INDICATOR_CLASSIFIER     |     1 |    10 |       |     2   (0)| 00:00:01 |
|*121 |              INDEX UNIQUE SCAN                    | BUILD_INDICATOR_CLASSIFIER_PK  |     1 |       |       |     1   (0)| 00:00:01 |
| 122 |            TABLE ACCESS BY INDEX ROWID            | BUILD_INDICATOR_VALUE          |    10 |   180 |       |     3   (0)| 00:00:01 |
|*123 |             INDEX RANGE SCAN                      | IDX_BIV_ID__BI_ID              |    10 |       |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("ITEM_0"="TL"."ID" AND "ITEM_1"="FCLS"."FINANCING_SOURCE_ID" AND "ITEM_2"="FCLS"."ECONOMICAL_CLASSIFIER_ID" AND 
              "BD"."SECTION"="ITEM_3" AND "BD"."SUBSECTION"="ITEM_4" AND "BD"."PROGRAM"="ITEM_5" AND "BD"."SUBPROGRAM"="ITEM_6" AND 
              "BD"."ACTION"="ITEM_7" AND "BD"."EXPENCE_KIND"="ITEM_8")
   7 - access("TL"."ID"="TITLE_ID")
  10 - access("FVL"."FACT_INDICATOR_ITEM_ID"="FITM"."ID")
  13 - access("FCLS"."ECONOMICAL_CLASSIFIER_ID"="EC"."ID")
  16 - access("FCLS"."BUDGET_CLASSIFIER_ID"="BD"."ID")
  19 - access("FCLS"."ID"="FITM"."FACT_INDICATOR_CLASSIFIER_ID")
  22 - access("FITM"."FACT_INDICATOR_ID"="FI"."ID")
  23 - filter(("FITM"."FACT_INDICATOR_VALUE_TYPE_ID"=4 AND "FITM"."ITEM_DATE">=TRUNC(:B1,'fmyear') AND 
              TRUNC(INTERNAL_FUNCTION("FITM"."ITEM_DATE"))<=:B1))
  25 - access("FI"."ID"="TFI"."FACT_INDICATOR_ID")
  26 - filter("FI"."FACT_INDICATOR_TYPE_ID"=1)
  28 - access("TFI"."TITLE_ID"="TL"."ID")
  30 - access("TD"."ID"="TL"."ID")
  34 - filter(CASE  WHEN ("H"."ISACTIVE"=1 AND "TT"."DELETE_DATE" IS NULL) THEN 'Y' ELSE 'N' END ='Y')
  35 - access("H"."ID"="TT"."ID")
  39 - access("T"."ENTITY_ID"="G"."ENTITY_ID" AND "T"."STAGE_ID"="G"."STAGE_ID" AND "T"."YEAR"="G"."YEAR" AND 
              "T"."CREATE_DATE"="G"."CREATE_DATE")
  42 - filter("T"."DELETE_DATE" IS NULL)
  47 - access("BBD"."ID"="BCLS"."BUDGET_CLASSIFIER_ID")
  50 - access("BCLS"."ID"="BI"."BUILD_INDICATOR_CLASSIFIER_ID")
  51 - filter(("BI"."BUILD_INDICATOR_TYPE_ID"=3 AND "BI"."BUILD_INDICATOR_CLASSIFIER_ID" IS NOT NULL))
  54 - access("TL_BV_P"."FS_ID"="TL_FCT_RES"."FS_ID" AND "TL_BV_P"."TITLE_ID"="TL_FCT_RES"."TITLE_ID")
  57 - access("TL_FCT1"."TITLE_ID"="TL_FCT2_3_P"."TITLE_ID" AND "TL_FCT1"."FS_ID"="TL_FCT2_3_P"."FS_ID")
  64 - access("TFI"."TITLE_ID"="TITLE_ID")
  69 - access("FI"."ID"="TFI"."FACT_INDICATOR_ID")
  70 - access("FVL"."FACT_INDICATOR_ITEM_ID"="FITM"."ID")
  71 - access("FCLS"."ID"="FITM"."FACT_INDICATOR_CLASSIFIER_ID")
  72 - access("FITM"."FACT_INDICATOR_ID"="FI"."ID")
  73 - filter("FITM"."FACT_INDICATOR_VALUE_TYPE_ID"=1)
  74 - filter(("FI"."FACT_INDICATOR_TYPE_ID"=2 OR "FI"."FACT_INDICATOR_TYPE_ID"=3))
  76 - filter("FVL"."MONTH_NUMBER"<=EXTRACT(MONTH FROM :B1))
  82 - access("FS"."ID"="B_CLS"."FINANCING_SOURCE_ID")
  84 - filter(("B_VL"."BUILD_INDICATOR_VALUE_TYPE_ID"=3 OR ("B_VL"."BUILD_INDICATOR_VALUE_TYPE_ID"=4 AND 
              "B_VL"."YEAR"="from$_subquery$_021"."TL_YEAR" AND "B_VL"."YEAR" IS NOT NULL) OR ("B_VL"."BUILD_INDICATOR_VALUE_TYPE_ID"=1 AND 
              "B_VL"."YEAR"="from$_subquery$_021"."TL_YEAR" AND "B_VL"."QUARTER_NUMBER" IS NULL AND "B_VL"."YEAR" IS NOT NULL)))
  91 - access("ORG"."ID"="TSO"."ORGANIZATION_ID")
  95 - filter(CASE  WHEN ("H"."ISACTIVE"=1 AND "DELETE_DATE" IS NULL) THEN 'Y' ELSE 'N' END ='Y')
  98 - access("CC"."ID"="TCC"."COSTS_CLASSIFIER_ID")
  99 - filter("CC"."COSTS_CLASSIFIER_ID" IS NULL)
 100 - access("TCC"."TITLE_ID"="T"."ID")
 101 - access("T"."ID"="TITLE_ID")
 103 - access("T"."ID"="TP"."TITLE_ID")
 107 - filter("TT"."TITLE_TERM_TYPE_ID"=CASE "T"."TITLE_TYPE_ID" WHEN 1 THEN 1 WHEN 2 THEN 1 ELSE 2 END )
 108 - access("TT"."TITLE_ID"="T"."ID")
 112 - access("T"."ID"="T"."ID")
 113 - filter(("T"."ENTITY_ID"="G"."ENTITY_ID" AND "T"."STAGE_ID"="G"."STAGE_ID" AND "T"."YEAR"="G"."YEAR" AND 
              "T"."CREATE_DATE"="G"."CREATE_DATE"))
 115 - filter("T"."DELETE_DATE" IS NULL)
 116 - filter("BI"."BUILD_INDICATOR_TYPE_ID"=3)
 117 - access("BI"."TITLE_ID"="T"."ID")
 118 - filter(("TSO"."ORGANIZATION_ROLE_TYPE_ID"=2 OR "TSO"."ORGANIZATION_ROLE_TYPE_ID"=3))
 119 - access("TSO"."TITLE_ID"="T"."ID")
 120 - filter("B_CLS"."FINANCING_SOURCE_ID" IS NOT NULL)
 121 - access("B_CLS"."ID"="BI"."BUILD_INDICATOR_CLASSIFIER_ID")
 123 - access("B_VL"."BUILD_INDICATOR_ID"="from$_subquery$_021"."ID")

...
Рейтинг: 0 / 0
02.09.2019, 08:07
| Ответить | Цитировать | Написать  
Выполнение запроса на разных серверах
    #39856164
Фотография Владимир СА
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир СА
Участник
AWR Прод
...
Рейтинг: 0 / 0
02.09.2019, 08:14
| Ответить | Цитировать | Написать  
Выполнение запроса на разных серверах
    #39856165
Фотография Владимир СА
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир СА
Участник
AWR ПредПрод
...
Рейтинг: 0 / 0
02.09.2019, 08:14
| Ответить | Цитировать | Написать  
Выполнение запроса на разных серверах
    #39856166
Фотография Владимир СА
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир СА
Участник
AWR Тест
...
Рейтинг: 0 / 0
02.09.2019, 08:14
| Ответить | Цитировать | Написать  
Выполнение запроса на разных серверах
    #39856222
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Владимир СА,

не надо траблшутинг одного запроса начинать с awr. Лучше выкладывайте отчет sqldb360 для этого запроса: https://github.com/sqldb360/sqldb360
...
Рейтинг: 0 / 0
02.09.2019, 11:43
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Выполнение запроса на разных серверах
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39856163&tid=1882135]:
 0ms
get settings:
 10ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 33ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 53ms
get tp. blocked users:
 1ms
others: 108ms
total:  243ms
созд. / загр.: 243ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]