ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / задачка оптимизации производительности для продвинутых :-)
46 сообщений из 46, показаны все 2 страниц
  все  
задачка оптимизации производительности для продвинутых :-)
    #39380041
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
есть чужая система в которой SQL-запросы нам НИКАК НЕ ИЗМЕНИТЬ! Можно рулить только наклеиванием хинтов (по технологии SQL-Patches) ну или индексами - на ДБА-уровне.

В запросе фигурируют три теблицы и две вьюхи:

--огромная таблица:
select count(*) from DACODB.OS_BALANCE T1 -- --> 51.569.529

--маленикая таблица (примерно 1,6% от большой):
select count(*) from CODADB.os_element -- 833.494
и пару вьюшек на её основе (просто отфильтровывают из CODADB.os_element, других таблиц в них нет!)
--View T2: select count(*) from DACODB.OS_EL6_ELEMENT -- 163.339
--View T3: select count(*) from DACODB.OS_EL1_ELEMENT -- 121.489

Пока моё "руление" хинтами уменьшило время ответа с изначального варианта (без хинтов!) с 3,5 минуты на 1 минуту примерно:

Код: 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.
select /*+LEADING(T1 T2 T3) full(T1) full(T2) full(T3) */
     distinct T1."CMPCODE" "c1"   
from "DACODB"."OS_BALANCE" T1,
     "DACODB"."OS_EL6_ELEMENT" T2,
     "DACODB"."OS_EL1_ELEMENT" T3 
where T1."CMPCODE"=T2."EL6_CMPCODE" and T1."EL6"=T2."EL6_CODE" and T1."CMPCODE"=T3."EL1_CMPCODE" and T1."EL1"=T3."EL1_CODE" and
      T1."EL3"<>'0200' and   
      T1."EL4"<>'3997' and  
      T2."EL6_CODE"<>'07777000' 


Plan hash value: 2292248160
 
--------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   119 | 11543 |       |  3031K  (1)| 02:51:23 |
|   1 |  HASH UNIQUE         |             |   119 | 11543 |       |  3031K  (1)| 02:51:23 |
|*  2 |   HASH JOIN          |             |   250K|    23M|  4576K|  3031K  (1)| 02:51:23 |
|*  3 |    TABLE ACCESS FULL | OS_ELEMENT |   120K|  3165K|       | 90754   (1)| 00:05:08 |
|*  4 |    HASH JOIN         |             |  2309K|   154M|  2281M|  2853K  (1)| 02:41:20 |
|*  5 |     TABLE ACCESS FULL| OS_BALANCE |    43M|  1783M|       |  1689K  (1)| 01:35:30 |
|*  6 |     TABLE ACCESS FULL| OS_ELEMENT |   163K|  4309K|       | 90756   (1)| 00:05:08 |
--------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$5428C7F1
   3 - SEL$5428C7F1 / OS_ELEMENT@SEL$3
   5 - SEL$5428C7F1 / T1@SEL$1
   6 - SEL$5428C7F1 / OS_ELEMENT@SEL$2
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("T1"."CMPCODE"="CMPCODE" AND "T1"."EL1"="CODE")
   3 - filter("ELMLEVEL"=1)
   4 - access("T1"."CMPCODE"="CMPCODE" AND "T1"."EL6"="CODE")
   5 - filter("T1"."EL3"<>U'0200' AND "T1"."EL4"<>U'3997' AND 
              "T1"."EL6"<>U'07777000')
   6 - filter("ELMLEVEL"=6 AND "CODE"<>U'07777000')
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) "T1"."CMPCODE"[NVARCHAR2,24]
   2 - (#keys=2) "T1"."CMPCODE"[NVARCHAR2,24]
   3 - "CMPCODE"[NVARCHAR2,24], "CODE"[NVARCHAR2,144]
   4 - (#keys=2) "T1"."CMPCODE"[NVARCHAR2,24], "T1"."EL1"[NVARCHAR2,144]
   5 - "T1"."CMPCODE"[NVARCHAR2,24], "T1"."EL1"[NVARCHAR2,144], 
       "T1"."EL6"[NVARCHAR2,144]
   6 - "CMPCODE"[NVARCHAR2,24], "CODE"[NVARCHAR2,144]
...
Рейтинг: 0 / 0
06.01.2017, 12:46
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380043
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
оно и понятно - все full-scan-ы оказались выигрышнее чем прыгания по индексам.

Но вот что я не понимаю и не могу добиться, чтоб Оракл взял Nested Loop Join вместо Hash Join (по моему опыту это почти всегда имеет смысл, когда join-щиеся таблицы сильно не равны по величине!). Оракл хотя и переделывает план с учётом хинтов usе_nl, но NL таки не берёт, оставляя Hash-Join:

Код: 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.
select /*+LEADING(T1 T2 T3) full(T1) full(T2) full(T3)  use_nl(T1 T2) use_nl(T1 T3)*/
  distinct T1."CMPCODE" "c1"   
from "DACODB"."OS_BALANCE" T1,
     "DACODB"."OS_EL6_ELEMENT" T2,
     "DACODB"."OS_EL1_ELEMENT" T3 
where T1."CMPCODE"=T2."EL6_CMPCODE" and T1."EL6"=T2."EL6_CODE" and  T1."CMPCODE"=T3."EL1_CMPCODE" and      T1."EL1"=T3."EL1_CODE" and
      T1."EL3"<>'0200' and  
      T1."EL4"<>'3997' and  
      T2."EL6_CODE"<>'07777000' --:"SYS_B_11"

--*************
Plan hash value: 2568810396
 
--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |   119 | 10829 |       |  3034K  (1)| 02:51:34 |
|   1 |  HASH UNIQUE           |                 |   119 | 10829 |       |  3034K  (1)| 02:51:34 |
|*  2 |   HASH JOIN            |                 |   251K|    21M|  4224K|  3034K  (1)| 02:51:33 |
|   3 |    VIEW                | VW_DTP_858AEE64 |   120K|  2813K|       | 93431   (1)| 00:05:17 |
|   4 |     HASH UNIQUE        |                 |   120K|  3165K|  4720K| 93431   (1)| 00:05:17 |
|*  5 |      TABLE ACCESS FULL | OS_ELEMENT     |   120K|  3165K|       | 90754   (1)| 00:05:08 |
|*  6 |    HASH JOIN           |                 |  2319K|   148M|  2281M|  2857K  (1)| 02:41:32 |
|*  7 |     TABLE ACCESS FULL  | OS_BALANCE     |    43M|  1783M|       |  1689K  (1)| 01:35:30 |
|   8 |     VIEW               | VW_DTP_B6D36048 |   163K|  3830K|       | 94402   (1)| 00:05:21 |
|   9 |      HASH UNIQUE       |                 |   163K|  4309K|  6424K| 94402   (1)| 00:05:21 |
|* 10 |       TABLE ACCESS FULL| OS_ELEMENT     |   163K|  4309K|       | 90756   (1)| 00:05:08 |
--------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$FEC22511
   3 - SEL$08B05C1D / VW_DTP_858AEE64@SEL$858AEE64
   4 - SEL$08B05C1D
   5 - SEL$08B05C1D / OS_ELEMENT@SEL$3
   7 - SEL$FEC22511 / T1@SEL$1
   8 - SEL$5032BEB0 / VW_DTP_B6D36048@SEL$B6D36048
   9 - SEL$5032BEB0
  10 - SEL$5032BEB0 / OS_ELEMENT@SEL$2
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("T1"."CMPCODE"="ITEM_2" AND "T1"."EL1"="ITEM_1")
   5 - filter("ELMLEVEL"=1)
   6 - access("T1"."CMPCODE"="ITEM_2" AND "T1"."EL6"="ITEM_1")
   7 - filter("T1"."EL3"<>U'0200' AND "T1"."EL4"<>U'3997')
  10 - filter("ELMLEVEL"=6 AND "CODE"<>U'07777000')
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) "T1"."CMPCODE"[NVARCHAR2,24]
   2 - (#keys=2) "T1"."CMPCODE"[NVARCHAR2,24]
   3 - "ITEM_1"[NVARCHAR2,144], "ITEM_2"[NVARCHAR2,24]
   4 - (#keys=2) "CODE"[NVARCHAR2,144], "CMPCODE"[NVARCHAR2,24]
   5 - "CMPCODE"[NVARCHAR2,24], "CODE"[NVARCHAR2,144]
   6 - (#keys=2) "T1"."CMPCODE"[NVARCHAR2,24], "T1"."EL1"[NVARCHAR2,144]
   7 - "T1"."CMPCODE"[NVARCHAR2,24], "T1"."EL1"[NVARCHAR2,144], "T1"."EL6"[NVARCHAR2,144]
   8 - "ITEM_1"[NVARCHAR2,144], "ITEM_2"[NVARCHAR2,24]
   9 - (#keys=2) "CODE"[NVARCHAR2,144], "CMPCODE"[NVARCHAR2,24]
  10 - "CMPCODE"[NVARCHAR2,24], "CODE"[NVARCHAR2,144]



есть ли какие-то методы его заставить взять NL?
Ещё какие-то идеи, чтобы ещё разогнать запрос??
...
Рейтинг: 0 / 0
06.01.2017, 12:52
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380044
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
DBAshnik,

ты план показывай с +outlines или просто формат 'advanced' ставь или код вьюшек покажи - нужны внутренние алиасы
...
Рейтинг: 0 / 0
06.01.2017, 12:53
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380045
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
DBAshnik,

ну и еще:
1. планы приводи с 'allstats last', а то статистик-то не видно
2. индексы на таблицах этих покажи
...
Рейтинг: 0 / 0
06.01.2017, 12:56
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380049
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
xtenderDBAshnik,

ты план показывай с +outlines или просто формат 'advanced' ставь или код вьюшек покажи - нужны внутренние алиасы

код вьюшек?? я ж говорю, там только фильтры. Ну пожалуйста:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
CREATE OR REPLACE FORCE VIEW DACODB.OS_EL6_ELEMENT AS 
select
  cmpcode el6_cmpcode,
  code el6_code,
  ... ещё стопиццот полей....
  longname el6_longname
 from OS_element
where elmlevel=6;

CREATE OR REPLACE FORCE VIEW DACODB.OS_EL1_ELEMENT AS 
select
  cmpcode el1_cmpcode,
  code el1_code,
  ... ещё стопиццот полей....  
  longname el1_longname
 from OS_element
where elmlevel=1;
...
Рейтинг: 0 / 0
06.01.2017, 13:02
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380055
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
DBAshnikесть ли какие-то методы его заставить взять NL?вообще забавно - ты ставишь хинт чтобы фулсканило OS_BALANCE при этом хочешь NL - нафига? 163.339 фулсканов хочешь? :) твои же хинты не работают потому что вьюхи у тебя мерджатся и после трансформации твои хинты уже невалидны, т.к. нет никаких T2 и T3.

Как простейший вариант попробуй так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select /*+ leading(T2 T1 T3) no_merge(t2) no_merge(t3) use_nl(T1) use_hash(T3) swap_join_inputs(T3) */
  distinct T1."CMPCODE" "c1"   
from "DACODB"."OS_BALANCE" T1,
     "DACODB"."OS_EL6_ELEMENT" T2,
     "DACODB"."OS_EL1_ELEMENT" T3 
where T1."CMPCODE"=T2."EL6_CMPCODE" and T1."EL6"=T2."EL6_CODE" and  T1."CMPCODE"=T3."EL1_CMPCODE" and      T1."EL1"=T3."EL1_CODE" and
      T1."EL3"<>'0200' and  
      T1."EL4"<>'3997' and  
      T2."EL6_CODE"<>'07777000' --:"SYS_B_11"
...
Рейтинг: 0 / 0
06.01.2017, 13:17
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380069
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
xtender,

респектище тебе!!! Разогнал до 37 сек! Так я и чуял, что можно как-то разогнать, но не понимал как! Спасибо за науку! ( особенно за новый для меня хинт: swap_join_inputs )

Код: 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.
Plan hash value: 825003594
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |   119 |  8925 |       |  3934K  (1)| 03:42:25 |
|   1 |  HASH UNIQUE           |                    |   119 |  8925 |       |  3934K  (1)| 03:42:25 |
|*  2 |   HASH JOIN            |                    |   250K|    17M|  4224K|  3934K  (1)| 03:42:25 |
|   3 |    VIEW                | OS_EL1_ELEMENT    |   120K|  2813K|       |  5620   (1)| 00:00:20 |
|*  4 |     INDEX SKIP SCAN    | OS_ELEMENT_INDEX1 |   120K|  3165K|       |  5620   (1)| 00:00:20 |
|*  5 |    HASH JOIN           |                    |  2309K|   112M|  5752K|  3861K  (1)| 03:38:19 |
|   6 |     VIEW               | OS_EL6_ELEMENT    |   163K|  3830K|       |  5624   (1)| 00:00:20 |
|*  7 |      INDEX SKIP SCAN   | OS_ELEMENT_INDEX1 |   163K|  4309K|       |  5624   (1)| 00:00:20 |
|   8 |     VIEW               | VW_DTP_BA8ECEFB    |    43M|  1120M|       |  3092K  (1)| 02:54:52 |
|   9 |      HASH UNIQUE       |                    |    43M|  1783M|  2498M|  3092K  (1)| 02:54:52 |
|* 10 |       TABLE ACCESS FULL| OS_BALANCE        |    43M|  1783M|       |  1689K  (1)| 01:35:30 |
-----------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$D3CBBBEF
   3 - SEL$3        / T3@SEL$1
   4 - SEL$3        / OS_ELEMENT@SEL$3
   6 - SEL$2        / T2@SEL$1
   7 - SEL$2        / OS_ELEMENT@SEL$2
   8 - SEL$8304605A / VW_DTP_BA8ECEFB@SEL$BA8ECEFB
   9 - SEL$8304605A
  10 - SEL$8304605A / T1@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ITEM_2"="T3"."EL1_CMPCODE" AND "ITEM_1"="T3"."EL1_CODE")
   4 - access("ELMLEVEL"=1)
       filter("ELMLEVEL"=1)
   5 - access("ITEM_2"="T2"."EL6_CMPCODE" AND "ITEM_3"="T2"."EL6_CODE")
   7 - access("ELMLEVEL"=6)
       filter("ELMLEVEL"=6 AND "CODE"<>U'07777000')
  10 - filter("T1"."EL3"<>U'0200' AND "T1"."EL4"<>U'3997' AND "T1"."EL6"<>U'07777000')
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) "ITEM_2"[NVARCHAR2,24]
   2 - (#keys=2) "ITEM_2"[NVARCHAR2,24]
   3 - "T3"."EL1_CMPCODE"[NVARCHAR2,24], "T3"."EL1_CODE"[NVARCHAR2,144]
   4 - "CMPCODE"[NVARCHAR2,24], "CODE"[NVARCHAR2,144]
   5 - (#keys=2) "ITEM_2"[NVARCHAR2,24], "ITEM_1"[NVARCHAR2,144]
   6 - "T2"."EL6_CMPCODE"[NVARCHAR2,24], "T2"."EL6_CODE"[NVARCHAR2,144]
   7 - "CMPCODE"[NVARCHAR2,24], "CODE"[NVARCHAR2,144]
   8 - "ITEM_1"[NVARCHAR2,144], "ITEM_2"[NVARCHAR2,24], "ITEM_3"[NVARCHAR2,144]
   9 - (#keys=3) "T1"."EL1"[NVARCHAR2,144], "T1"."CMPCODE"[NVARCHAR2,24], 
       "T1"."EL6"[NVARCHAR2,144]
  10 - "T1"."CMPCODE"[NVARCHAR2,24], "T1"."EL1"[NVARCHAR2,144], "T1"."EL6"[NVARCHAR2,144]
...
Рейтинг: 0 / 0
06.01.2017, 13:54
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380079
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
DBAshnik,


попробуй еще так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
select /*+
 leading(T2 T1 T3)
 no_merge(t2)
 no_merge(t3)
 use_nl(T1)
 use_hash(T3)
 swap_join_inputs(T3)
 opt_param('_optimizer_skip_scan_enabled' 'false')
 */
  distinct T1."CMPCODE" "c1"   
from "DACODB"."OS_BALANCE" T1,
     "DACODB"."OS_EL6_ELEMENT" T2,
     "DACODB"."OS_EL1_ELEMENT" T3 
where T1."CMPCODE"=T2."EL6_CMPCODE" and T1."EL6"=T2."EL6_CODE" and  T1."CMPCODE"=T3."EL1_CMPCODE" and      T1."EL1"=T3."EL1_CODE" and
      T1."EL3"<>'0200' and  
      T1."EL4"<>'3997' and  
      T2."EL6_CODE"<>'07777000' --:"SYS_B_11"

или так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
select /*+
   leading(T2 T1 T3)
   no_merge(t2)
   no_merge(t3)
   use_nl(T1)
   use_hash(T3)
   swap_join_inputs(T3)
   index_ffs(@SEL$2 OS_ELEMENT@SEL$2 OS_ELEMENT_INDEX1)
   index_ffs(@SEL$3 OS_ELEMENT@SEL$3 OS_ELEMENT_INDEX1)
 */
  distinct T1."CMPCODE" "c1"   
from "DACODB"."OS_BALANCE" T1,
     "DACODB"."OS_EL6_ELEMENT" T2,
     "DACODB"."OS_EL1_ELEMENT" T3 
where T1."CMPCODE"=T2."EL6_CMPCODE" and T1."EL6"=T2."EL6_CODE" and  T1."CMPCODE"=T3."EL1_CMPCODE" and      T1."EL1"=T3."EL1_CODE" and
      T1."EL3"<>'0200' and  
      T1."EL4"<>'3997' and  
      T2."EL6_CODE"<>'07777000' --:"SYS_B_11"
...
Рейтинг: 0 / 0
06.01.2017, 14:21
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380081
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
DBAshnik,

Про индексы ты, кстати, ничего так и не сказал, как и про статистики выполнения - нифига не понятно, откуда сколько строк по каким предикатам возвращается. Какие индексы есть на OS_BALANCE?
Попробуй еще так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
select /*+
   leading(T2 T3 T1)
   no_merge(t2)
   no_merge(t3)
   use_hash(T2 T3)
--   use_nl(t1)
   index_ffs(@SEL$2 OS_ELEMENT@SEL$2 OS_ELEMENT_INDEX1)
   index_ffs(@SEL$3 OS_ELEMENT@SEL$3 OS_ELEMENT_INDEX1)
 */
  distinct T1."CMPCODE" "c1"   
from "DACODB"."OS_BALANCE" T1,
     "DACODB"."OS_EL6_ELEMENT" T2,
     "DACODB"."OS_EL1_ELEMENT" T3 
where 
      T2."EL6_CMPCODE" = T3."EL1_CMPCODE" and
      T1."CMPCODE"=T2."EL6_CMPCODE" and T1."EL6"=T2."EL6_CODE" and  
      T1."CMPCODE"=T3."EL1_CMPCODE" and T1."EL1"=T3."EL1_CODE" and
      T1."EL3"<>'0200' and  
      T1."EL4"<>'3997' and  
      T2."EL6_CODE"<>'07777000' --:"SYS_B_11"
...
Рейтинг: 0 / 0
06.01.2017, 14:28
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380112
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
Уважаемый xtender,

3 твоих крайних варианта выдают такие времена выполнения, соответственно:
--
33 секунды
31 секунда
> 5 минут

P.S. индексы все плохи ( несмотря на свежую статистику ) - прежде всего из-за низкой селективности.
...
Рейтинг: 0 / 0
06.01.2017, 16:12
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380116
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
хотя 1-й и 2-й вариант примерно одинаковы. Вот план первого:

Код: 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.
Plan hash value: 2704949917
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |   119 |  8925 |       |  3934K  (1)| 03:42:26 |
|   1 |  HASH UNIQUE           |                    |   119 |  8925 |       |  3934K  (1)| 03:42:26 |
|*  2 |   HASH JOIN            |                    |   250K|    17M|  4224K|  3934K  (1)| 03:42:26 |
|   3 |    VIEW                | OS_EL1_ELEMENT    |   120K|  2813K|       |  5663   (2)| 00:00:20 |
|*  4 |     INDEX FULL SCAN    | OS_ELEMENT_INDEX1 |   120K|  3165K|       |  5663   (2)| 00:00:20 |
|*  5 |    HASH JOIN           |                    |  2309K|   112M|  5752K|  3861K  (1)| 03:38:19 |
|   6 |     VIEW               | OS_EL6_ELEMENT    |   163K|  3830K|       |  5665   (2)| 00:00:20 |
|*  7 |      INDEX FULL SCAN   | OS_ELEMENT_INDEX1 |   163K|  4309K|       |  5665   (2)| 00:00:20 |
|   8 |     VIEW               | VW_DTP_BA8ECEFB    |    43M|  1120M|       |  3092K  (1)| 02:54:52 |
|   9 |      HASH UNIQUE       |                    |    43M|  1783M|  2498M|  3092K  (1)| 02:54:52 |
|* 10 |       TABLE ACCESS FULL| OS_BALANCE        |    43M|  1783M|       |  1689K  (1)| 01:35:30 |
-----------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$D3CBBBEF
   3 - SEL$3        / T3@SEL$1
   4 - SEL$3        / OS_ELEMENT@SEL$3
   6 - SEL$2        / T2@SEL$1
   7 - SEL$2        / OS_ELEMENT@SEL$2
   8 - SEL$8304605A / VW_DTP_BA8ECEFB@SEL$BA8ECEFB
   9 - SEL$8304605A
  10 - SEL$8304605A / T1@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ITEM_2"="T3"."EL1_CMPCODE" AND "ITEM_1"="T3"."EL1_CODE")
   4 - access("ELMLEVEL"=1)
       filter("ELMLEVEL"=1)
   5 - access("ITEM_2"="T2"."EL6_CMPCODE" AND "ITEM_3"="T2"."EL6_CODE")
   7 - access("ELMLEVEL"=6)
       filter("ELMLEVEL"=6 AND "CODE"<>U'07777000')
  10 - filter("T1"."EL3"<>U'0200' AND "T1"."EL4"<>U'3997' AND "T1"."EL6"<>U'07777000')
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) "ITEM_2"[NVARCHAR2,24]
   2 - (#keys=2) "ITEM_2"[NVARCHAR2,24]
   3 - "T3"."EL1_CMPCODE"[NVARCHAR2,24], "T3"."EL1_CODE"[NVARCHAR2,144]
   4 - "CMPCODE"[NVARCHAR2,24], "CODE"[NVARCHAR2,144]
   5 - (#keys=2) "ITEM_2"[NVARCHAR2,24], "ITEM_1"[NVARCHAR2,144]
   6 - "T2"."EL6_CMPCODE"[NVARCHAR2,24], "T2"."EL6_CODE"[NVARCHAR2,144]
   7 - "CMPCODE"[NVARCHAR2,24], "CODE"[NVARCHAR2,144]
   8 - "ITEM_1"[NVARCHAR2,144], "ITEM_2"[NVARCHAR2,24], "ITEM_3"[NVARCHAR2,144]
   9 - (#keys=3) "T1"."EL1"[NVARCHAR2,144], "T1"."CMPCODE"[NVARCHAR2,24], 
       "T1"."EL6"[NVARCHAR2,144]
  10 - "T1"."CMPCODE"[NVARCHAR2,24], "T1"."EL1"[NVARCHAR2,144], "T1"."EL6"[NVARCHAR2,144]
...
Рейтинг: 0 / 0
06.01.2017, 16:24
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380126
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
DBAshnik,

невооруженным глазом видно что возвращается 250к, с os_balance читаеться 43 M, соответственно там должен быть индексный доступ в os_balance. Сделай правильные индексы там и будет счастье)
...
Рейтинг: 0 / 0
06.01.2017, 16:48
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380136
Фотография Sergey_Korolev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey_Korolev
Участник
DBAshnik,

Издревле, в первых сроках письма указывали номер версии и прочие атрибуты существенные для решения проблемы
...
Рейтинг: 0 / 0
06.01.2017, 17:05
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380138
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
DBAshnik,

Что-то вы там намудрили с параметрами, должен был бы быть iffs в первых двух вариантах. Вообще, уже бесит, что не дал всю информацию, что я просил. и до сих пор показываешь херню, а не план со статистиками...
...
Рейтинг: 0 / 0
06.01.2017, 17:10
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380143
Фотография Sergey_Korolev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey_Korolev
Участник
Sergey_Korolev,

Что там в star_transformation_enabled ?
...
Рейтинг: 0 / 0
06.01.2017, 17:25
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380145
Фотография Sergey_Korolev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey_Korolev
Участник
C ака xtender,

это как в известной комедии: "А он не может!". Если бы мог дать требуемую информацию, то и с задачкой давно разобрался :)
...
Рейтинг: 0 / 0
06.01.2017, 17:32
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380146
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
Sergey_KorolevSergey_Korolev,

Что там в star_transformation_enabled ?

и каким боком она тут ?:)
...
Рейтинг: 0 / 0
06.01.2017, 17:34
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380148
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Sergey_Korolev,

ну как минимум дать список существующих индексов и показать реальный план со статистиками и аутлайнами умеют все...
...
Рейтинг: 0 / 0
06.01.2017, 17:36
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380151
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
скорее всего, у них optimizer_index_cost_adj сильно занижен, поэтому надо пытаться с opt_param('optimizer_index_cost_adj' 100) ну и попробовать distinct-placement отключить, чтобы дважды не гоняло hash unique, т.к. подозреваю что набор (cmpcode,el1,e6) будет практически уникальным, т.е. добавить еще opt_param('_optimizer_distinct_placement' 'false')
...
Рейтинг: 0 / 0
06.01.2017, 17:48
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380154
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
xtenderчтобы дважды не гоняло hash unique

На тех объемах вряд ли это какой-то bottleneck.
...
Рейтинг: 0 / 0
06.01.2017, 17:53
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380157
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
ora601,

В 9-й строке 20077400 делается distinct el1,cmpcode,el6 по всей таблице - это много
...
Рейтинг: 0 / 0
06.01.2017, 17:57
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380158
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
К тому же это эвристическая трансформация, а не стоимостная
...
Рейтинг: 0 / 0
06.01.2017, 18:00
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380160
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
xtender,

Вообще для той джоин кардинальности должен быть nl по индексу, тогда и этой ерунды не будет.
...
Рейтинг: 0 / 0
06.01.2017, 18:05
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380161
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
xtenderSergey_Korolev,

ну как минимум дать список существующих индексов и показать реальный план со статистиками и аутлайнами умеют все...

эээ... "сами мы не местные" .... Я даю то, что выплёвывает мне TOAD по кнопке ЕXPLAIN PLAN! Почему этот план для вас не реальный, и что мне нужно сделать, чтоб было со "статистиками и аутлайнами " я, простите великодушно, не знаю. Это из sqlplus-a как-то запускать, да? (я с ним не очень дружу, кстати).
Или же ручками выполнять ЕXPLAIN PLAN и результаты сюда из соотв. таблицы доставать? Подскажите конкретно, плиз! :-)
...
Рейтинг: 0 / 0
06.01.2017, 18:26
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380162
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
DBAshnik,

https://oracle-base.com/articles/9i/dbms_xplan#gather_plan_statistics_hint
...
Рейтинг: 0 / 0
06.01.2017, 18:30
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380180
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
ora601DBAshnik,

https://oracle-base.com/articles/9i/dbms_xplan#gather_plan_statistics_hint

Cпасибо! Ну как смог так вытянул, не судите строго: :-)

Код: 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.
...
117 rows selected.

PLAN_TABLE_OUTPUT                                                                                                                 
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bs8pkhz34m1uk, child number 0                                                                                             
-------------------------------------                                                                                             
select  /*+    GATHER_PLAN_STATISTICS    leading(T2 T1 T3)                                                                        
no_merge(t2)    no_merge(t3)    use_nl(T1)    use_hash(T3)                                                                        
swap_join_inputs(T3)    index_ffs(@SEL$2 OS_ELEMENT@SEL$2                                                                         
OS_ELEMENT_INDEX1)    index_ffs(@SEL$3 OS_ELEMENT@SEL$3                                                                           
OS_ELEMENT_INDEX1)  */    distinct T1."CMPCODE" "c1"  from                                                                        
"DACODB"."OS_BALANCE" T1,      "DACODB"."OS_EL6_ELEMENT" T2,                                                                    
"DACODB"."OS_EL1_ELEMENT" T3  where T1."CMPCODE"=T2."EL6_CMPCODE" and                                                            
T1."EL6"=T2."EL6_CODE" and  T1."CMPCODE"=T3."EL1_CMPCODE" and                                                                     
T1."EL1"=T3."EL1_CODE" and       T1."EL3"<>:"SYS_B_0" and                                                                         
--:"SYS_B_09" and       T1."EL4"<>:"SYS_B_1" and -- :"SYS_B_10" and                                                               
  T2."EL6_CODE"<>:"SYS_B_2"                                                                                                       
                                                                                                                                  
Plan hash value: 825003594                                                                                                        
                                                                                                                                  
----------------------------------------------------------------------------------------------------------------------------------
---------                                                                                                                         
                                                                                                                                  
| Id  | Operation              | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | U
sed-Mem |                                                                                                                         

PLAN_TABLE_OUTPUT                                                                                                                 
----------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                  
----------------------------------------------------------------------------------------------------------------------------------
---------                                                                                                                         
                                                                                                                                  
|   0 | SELECT STATEMENT       |                    |      1 |        |    117 |00:00:38.44 |    1186K|   1174K|       |       |  
        |                                                                                                                         
                                                                                                                                  
|   1 |  HASH UNIQUE           |                    |      1 |    119 |    117 |00:00:38.44 |    1186K|   1174K|    21M|  4264K| 1
362K (0)|                                                                                                                         
                                                                                                                                  
|*  2 |   HASH JOIN            |                    |      1 |    250K|    402K|00:00:38.51 |    1186K|   1174K|  8988K|  3467K| 8
790K (0)|                                                                                                                         
                                                                                                                                  
|   3 |    VIEW                | OS_EL1_ELEMENT    |      1 |    120K|    121K|00:00:00.31 |    5552 |      0 |       |       |  
        |                                                                                                                         
                                                                                                                                  
|*  4 |     INDEX SKIP SCAN    | OS_ELEMENT_INDEX1 |      1 |    120K|    121K|00:00:00.28 |    5552 |      0 |       |       |  
        |                                                                                                                         
                                                                                                                                  
|*  5 |    HASH JOIN           |                    |      1 |   2309K|    402K|00:00:37.90 |    1180K|   1174K|    12M|  3196K|  
 12M (0)|                                                                                                                         

PLAN_TABLE_OUTPUT                                                                                                                 
----------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                  
|   6 |     VIEW               | OS_EL6_ELEMENT    |      1 |    163K|    163K|00:00:00.32 |    5552 |      0 |       |       |  
        |                                                                                                                         
                                                                                                                                  
|*  7 |      INDEX SKIP SCAN   | OS_ELEMENT_INDEX1 |      1 |    163K|    163K|00:00:00.28 |    5552 |      0 |       |       |  
        |                                                                                                                         
                                                                                                                                  
|   8 |     VIEW               | VW_DTP_BA8ECEFB    |      1 |     43M|    402K|00:00:37.39 |    1174K|   1174K|       |       |  
        |                                                                                                                         
                                                                                                                                  
|   9 |      HASH UNIQUE       |                    |      1 |     43M|    402K|00:00:37.30 |    1174K|   1174K|   113M|  9373K|  
101M (0)|                                                                                                                         
                                                                                                                                  
|* 10 |       TABLE ACCESS FULL| OS_BALANCE        |      1 |     43M|     46M|00:00:25.38 |    1174K|   1174K|       |       |  
        |                                                                                                                         
                                                                                                                                  
----------------------------------------------------------------------------------------------------------------------------------
---------                                                                                                                         
                                                                                                                                  
                                                                                                                                  
Predicate Information (identified by operation id):                                                                               

PLAN_TABLE_OUTPUT                                                                                                                 
----------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------                                                                               
                                                                                                                                  
   2 - access("ITEM_2"="T3"."EL1_CMPCODE" AND "ITEM_1"="T3"."EL1_CODE")                                                           
   4 - access("ELMLEVEL"=1)                                                                                                       
       filter("ELMLEVEL"=1)                                                                                                       
   5 - access("ITEM_2"="T2"."EL6_CMPCODE" AND "ITEM_3"="T2"."EL6_CODE")                                                           
   7 - access("ELMLEVEL"=6)                                                                                                       
       filter(("ELMLEVEL"=6 AND "CODE"<>SYS_OP_C2C(:SYS_B_2)))                                                                    
  10 - filter(("T1"."EL3"<>SYS_OP_C2C(:SYS_B_0) AND "T1"."EL4"<>SYS_OP_C2C(:SYS_B_1) AND "T1"."EL6"<>SYS_OP_C2C(:SYS_B_2)))       
                                                                                                                                  

43 rows selected.
...
Рейтинг: 0 / 0
06.01.2017, 20:00
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380205
Фотография Sergey_Korolev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey_Korolev
Участник
DBAshnik,

2. индексы на таблицах этих покажи
...
Рейтинг: 0 / 0
06.01.2017, 22:44
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380208
Фотография Sergey_Korolev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey_Korolev
Участник
DBAshnik,

Пришли результат

Код: sql
1.
2.
3.
4.
5.
 SELECT COUNT(DISTINCT(T1."CMPCODE")) CMPCODE,
        COUNT(DISTINCT(T1."EL6")) EL6,
        COUNT(DISTINCT(T1."EL1")) EL1,
        COUNT(DISTINCT(T1."EL1")) EL1  
  FROM "DACODB"."OS_BALANCE" T1
...
Рейтинг: 0 / 0
06.01.2017, 23:07
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380233
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
DBAshnik,

ну наконец-то, сразу многое стало понятным:
 
Код: 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.
117 rows selected.

PLAN_TABLE_OUTPUT                                                                                                                 
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bs8pkhz34m1uk, child number 0                                                                                             
-------------------------------------                                                                                             
select  /*+    GATHER_PLAN_STATISTICS    leading(T2 T1 T3)                                                                        
no_merge(t2)    no_merge(t3)    use_nl(T1)    use_hash(T3)                                                                        
swap_join_inputs(T3)    index_ffs(@SEL$2 OS_ELEMENT@SEL$2                                                                         
OS_ELEMENT_INDEX1)    index_ffs(@SEL$3 OS_ELEMENT@SEL$3                                                                           
OS_ELEMENT_INDEX1)  */    distinct T1."CMPCODE" "c1"  from                                                                        
"DACODB"."OS_BALANCE" T1,      "DACODB"."OS_EL6_ELEMENT" T2,                                                                    
"DACODB"."OS_EL1_ELEMENT" T3  where T1."CMPCODE"=T2."EL6_CMPCODE" and                                                            
T1."EL6"=T2."EL6_CODE" and  T1."CMPCODE"=T3."EL1_CMPCODE" and                                                                     
T1."EL1"=T3."EL1_CODE" and       T1."EL3"<>:"SYS_B_0" and                                                                         
--:"SYS_B_09" and       T1."EL4"<>:"SYS_B_1" and -- :"SYS_B_10" and                                                               
  T2."EL6_CODE"<>:"SYS_B_2"                                                                                                       
                                                                                                                                  
Plan hash value: 825003594                                                                                                        
                                                                                                                                  
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |      1 |        |    117 |00:00:38.44 |    1186K|   1174K|       |       |          |
|   1 |  HASH UNIQUE           |                    |      1 |    119 |    117 |00:00:38.44 |    1186K|   1174K|    21M|  4264K| 1362K (0)|
|*  2 |   HASH JOIN            |                    |      1 |    250K|    402K|00:00:38.51 |    1186K|   1174K|  8988K|  3467K| 8790K (0)|
|   3 |    VIEW                | OS_EL1_ELEMENT     |      1 |    120K|    121K|00:00:00.31 |    5552 |      0 |       |       |          |
|*  4 |     INDEX SKIP SCAN    | OS_ELEMENT_INDEX1  |      1 |    120K|    121K|00:00:00.28 |    5552 |      0 |       |       |          |
|*  5 |    HASH JOIN           |                    |      1 |   2309K|    402K|00:00:37.90 |    1180K|   1174K|    12M|  3196K|   12M (0)|
|   6 |     VIEW               | OS_EL6_ELEMENT     |      1 |    163K|    163K|00:00:00.32 |    5552 |      0 |       |       |          |
|*  7 |      INDEX SKIP SCAN   | OS_ELEMENT_INDEX1  |      1 |    163K|    163K|00:00:00.28 |    5552 |      0 |       |       |          |
|   8 |     VIEW               | VW_DTP_BA8ECEFB    |      1 |     43M|    402K|00:00:37.39 |    1174K|   1174K|       |       |          |
|   9 |      HASH UNIQUE       |                    |      1 |     43M|    402K|00:00:37.30 |    1174K|   1174K|   113M|  9373K|  101M (0)|
|* 10 |       TABLE ACCESS FULL| OS_BALANCE         |      1 |     43M|     46M|00:00:25.38 |    1174K|   1174K|       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                  
Predicate Information (identified by operation id):                                                                               
----------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                  
   2 - access("ITEM_2"="T3"."EL1_CMPCODE" AND "ITEM_1"="T3"."EL1_CODE")                                                           
   4 - access("ELMLEVEL"=1)                                                                                                       
       filter("ELMLEVEL"=1)                                                                                                       
   5 - access("ITEM_2"="T2"."EL6_CMPCODE" AND "ITEM_3"="T2"."EL6_CODE")                                                           
   7 - access("ELMLEVEL"=6)                                                                                                       
       filter(("ELMLEVEL"=6 AND "CODE"<>SYS_OP_C2C(:SYS_B_2)))                                                                    
  10 - filter(("T1"."EL3"<>SYS_OP_C2C(:SYS_B_0) AND "T1"."EL4"<>SYS_OP_C2C(:SYS_B_1) AND "T1"."EL6"<>SYS_OP_C2C(:SYS_B_2)))

Теперь понятно, что под условия подходят практически все записи таблицы - 46M из 51.5М и, скорее всего, возвращаются практически все возможные CMPCODE (подозреваю, что отфильтровывается всего от 2 до 5 CMPCODE). Без query rewrite в принципе это план практически оптимален, т.к. индексами тут уже не помочь и можно только оптимизировать заменой ISS на IFFS - но это не факт, что поможет т.к. индекс в кэше, а IFFS заставит читать, да и в любом случае это экономия на спичках - на них всего ~1 секунда уходит.
А вот с query rewrite можно очень хорошо оптимизнуть, думаю до 1-2 секунд:
1. надо заменить запрос с помощью query rewrite на:
1.1 если есть табличка со всеми CMPCODE (можешь глянуть есть ли внешний ключ у OS_BALANCE с CMPCODE и на какую таблицу он смотрит):
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
select
   cmpcode
from os_cmpcodes
where exists (
         select/*+ no_unnest no_merge leading(t2 t3 t1) use_nl(t3 t1)*/ null 
         from OS_BALANCE T1,
              OS_EL6_ELEMENT T2,
              OS_EL1_ELEMENT T3 
         where 
          os_cmpcodes = T2."EL1_CMPCODE" and
          T1."CMPCODE"=T2."EL6_CMPCODE" and T1."EL6"=T2."EL6_CODE" and  
          T1."CMPCODE"=T3."EL1_CMPCODE" and T1."EL1"=T3."EL1_CODE" and
          T1."EL3"<>'0200' and  
          T1."EL4"<>'3997' and  
          T2."EL6_CODE"<>'07777000' --:"SYS_B_11"
      )


1.2 Если нет такой таблицы, то использовать OS_EL1_ELEMENT как ведущую:
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
with os_cmpcodes as (select distinct EL1_CMPCODE as cmpcode from OS_EL1_ELEMENT)
select
   cmpcode
from os_cmpcodes
where exists (
         select/*+ no_unnest no_merge leading(e1 t1 e6) use_nl(t1 e6)*/ null 
         from OS_EL1_ELEMENT e1,
              OS_EL6_ELEMENT e6,
              OS_BALANCE T1
         where
          os_cmpcodes.cmpcode = e1."EL1_CMPCODE" and
          T1."CMPCODE"=e1."EL1_CMPCODE" and T1."EL1"=e1."EL1_CODE" and
          T1."CMPCODE"=e6."EL6_CMPCODE" and T1."EL6"=e6."EL6_CODE" and
          T1."EL3"<>'0200' and
          T1."EL4"<>'3997' and
          T1."EL6"<>'07777000' --:"SYS_B_11"
      )


2. Нужен хоть какой-нибудь индекс на OS_BALANCE, в котором CMPCODE стоит первым.
...
Рейтинг: 0 / 0
07.01.2017, 03:13
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380948
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
xtender! Я не люблю цитировать самого себя, но 6 января 17 (16:12) я уже говорил, что:
автор"индексы все плохи ( несмотря на свежую статистику ) - прежде всего из-за низкой селективности. "(c)

авторА вот с query rewrite можно очень хорошо оптимизнуть, думаю до 1-2 секунд:
до 2х это вряд-ли, так как твой лучший вариант уже даёт 0.33 Sek примерно! :-)

Но на будущее "query rewrite" - это очень интересная опция! Правда для меня (как и других дбшников тут) это абсолютно пока на практике незнакомая штука. И даже не ясно имеем ли мы на право её юзать. Вообще-то у нас EE и в заметках там "еxtra cost option" не замечен, но с этим лицензированием фич вечно такая неразбериха!
( https://docs.oracle.com/cd/E11882_01/license.112/e47877/editions.htm#DBLIC116 )

автор(можешь глянуть есть ли внешний ключ у OS_BALANCE
нету такого.
...
Рейтинг: 0 / 0
09.01.2017, 16:26
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380949
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
DBAshnik твой лучший вариант уже даёт 0.33 Sek примерно! :-)
упс. Сорри! Это я чё-то не проснулся ещё после праздников то! (и спутал с другим SQL-запросом) ;-)
33 Sek конечно же!!! )))
...
Рейтинг: 0 / 0
09.01.2017, 16:34
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39380972
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
xtender !
твой крайний вариант "with os_cmpcodes as (select distinct EL1_CMPCODE as cmpcode from OS_EL1_ELEMENT)" летает вообще! Выполняется за одну секунду - нереально круто!!!

Но разве мне не нужно иметь mv чтобы юзать rewrite query? (сорри, ещё не вчитался как-следует в мат.часть)
...
Рейтинг: 0 / 0
09.01.2017, 17:14
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39381035
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
DBAshnik,

можно юзать dbms_translation framework
...
Рейтинг: 0 / 0
09.01.2017, 19:05
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39381054
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
ora601DBAshnik,

можно юзать dbms_translation framework

cпасибо! Буду изучать! Поскольку с query rewrite я вообще не понимаю, как пока это сделать! Пока что понял, что как-то так:
Код: sql
1.
SELECT /*+ REWRITE (sum_sales_pscat_week_mv) */  ...


Но думаю, что без создания mv-шек тут не обойтись. (что нам в чужой базе вообще вряд-ли позволят....) :-(
...
Рейтинг: 0 / 0
09.01.2017, 19:33
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39381061
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
DBAshnikora601DBAshnik,

можно юзать dbms_translation framework

cпасибо! Буду изучать! Поскольку с query rewrite я вообще не понимаю, как пока это сделать! Пока что понял, что как-то так:
Код: sql
1.
SELECT /*+ REWRITE (sum_sales_pscat_week_mv) */  ...


Но думаю, что без создания mv-шек тут не обойтись. (что нам в чужой базе вообще вряд-ли позволят....) :-(


Только мне кажется я немного поторопился с ним, потому что у Вас явно что то ниже 12 с R1 ?
...
Рейтинг: 0 / 0
09.01.2017, 19:40
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39381066
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
ora601DBAshnik,

можно юзать dbms_translation framework

у нас кстати нет такого пакета! Может он в Оракл только в 12с? ( у нас то везде 11g и в этом году 12с ещё не будет)
...
Рейтинг: 0 / 0
09.01.2017, 19:43
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39381069
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
да, похоже только с 12с:
https://blogs.oracle.com/imc/entry/sql_translation_framework
...
Рейтинг: 0 / 0
09.01.2017, 19:48
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39381073
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
DBAshnik,

вот готовый пример: 15903763
Но боюсь вам придется отказаться от cursor_sharing=force для этого запроса
...
Рейтинг: 0 / 0
09.01.2017, 20:13
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39381377
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
xtenderDBAshnik,

вот готовый пример: 15903763
Но боюсь вам придется отказаться от cursor_sharing=force для этого запроса

в смысле "для этого запроса"? Для всего инстанца этот параметр (в v$parameter) изменить? (ну это нам, конечно, никто не позволит!!!)

А что там с query rewrite? (там без MVs вообще никак? или есть варианты?)
...
Рейтинг: 0 / 0
10.01.2017, 12:39
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39381421
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
DBAshnik,

это переписывание запроса, мв там не причем.
...
Рейтинг: 0 / 0
10.01.2017, 13:13
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39381444
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
ora601DBAshnik,

это переписывание запроса, мв там не причем.

xtender написал: автор1. надо заменить запрос с помощью query rewrite на:
Опа! Ну и как это делается без м.в.????
...
Рейтинг: 0 / 0
10.01.2017, 13:35
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39381510
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
вот тут есть вроде про DBMS_ADVANCED_REWRITE и cursor_sharing=
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3696883368520#56945400346165461

а что если его только на уровне сессии пересаживать на:
Код: sql
1.
alter session set cursor_sharing=similar;


??
...
Рейтинг: 0 / 0
10.01.2017, 14:21
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39381842
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
решил тут поиграться (на тестовой дб) таки с QUERY REWRITE + mv (вдруг нам её таки разрешат юзать!)

создал базовую вьюху:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
CREATE MATERIALIZED VIEW DACODB.mv_ViTstMV 
ENABLE QUERY REWRITE
AS
select 
distinct T1.CMPCODE c1,   
      T1.EL1 c2,
      T2.EL6_CODE c3,
      T3.EL1_SNAME c4
from DACODB.OS_BALANCE T1, DACODB.OS_EL6_ELEMENT T2,  DACODB.OS_EL1_ELEMENT T3 
where T1.CMPCODE=T2.EL6_CMPCODE and T1.EL6=T2.EL6_CODE and  T1.CMPCODE=T3.EL1_CMPCODE and T1.EL1=T3.EL1_CODE;



когда делаю запрос 1 в 1 один с базовым, всё летает (и, разумеется, expain plan показывает, что MV юзается!)
авторselect
distinct T1.CMPCODE c1,
T1.EL1 c2,
T2.EL6_CODE c3,
T3.EL1_SNAME c4
from DACODB.OS_BALANCE T1, DACODB.OS_EL6_ELEMENT T2, DACODB.OS_EL1_ELEMENT T3
where T1.CMPCODE=T2.EL6_CMPCODE and T1.EL6=T2.EL6_CODE and T1.CMPCODE=T3.EL1_CMPCODE and T1.EL1=T3.EL1_CODE



но стоит добавить предикат, который всего-то должен только отфильтровывать что-то из базы - как MV уже не доступна (и это видно в explain plan !)
distinct T1.CMPCODE c1,
T1.EL1 c2,
T2.EL6_CODE c3,
T3.EL1_SNAME c4
from DACODB.OS_BALANCE T1, DACODB.OS_EL6_ELEMENT T2, DACODB.OS_EL1_ELEMENT T3
where T1.CMPCODE=T2.EL6_CMPCODE and T1.EL6=T2.EL6_CODE and T1.CMPCODE=T3.EL1_CMPCODE and T1.EL1=T3.EL1_CODE
and T1.EL3<>'0200'

даже хинт не помог:
Код: sql
1.
select /*+ REWRITE(mv_ViTstMV )*/


глупый Оракл не втыкает, что можно взять mv! Или это я глупый и что-то не знаю ещё?
...
Рейтинг: 0 / 0
10.01.2017, 20:28
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39381853
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
DBAshnikora601DBAshnik,

это переписывание запроса, мв там не причем.

xtender написал: автор1. надо заменить запрос с помощью query rewrite на:
Опа! Ну и как это делается без м.в.????я же даже ссылку привёл... Не надо никаких mv.
...
Рейтинг: 0 / 0
10.01.2017, 20:42
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39382676
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
xtender я же даже ссылку привёл... Не надо никаких mv.

xtender, cпасибо большое. Я ссылку учёл. Но ты же сам писал:
авторНо боюсь вам придется отказаться от cursor_sharing=force для этого запроса

cкорее всего нам не позволят изменить cursor_sharing даже на уровне сессии! А вот что позволят юзать не очень большие(!) MVs - на это больше шансов! Да и нам хотелось бы овладеть этой технологией, не только для решения данной проблемы, но и подобных ей в будущем (на разных _чужих_! системах!)
...
Рейтинг: 0 / 0
11.01.2017, 19:49
| Ответить | Цитировать | Написать  
задачка оптимизации производительности для продвинутых :-)
    #39382678
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
DBAshnik,

попробуйте создать sql_patch для этого запроса с хинтом cursor_sharing_exact: https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a
...
Рейтинг: 0 / 0
11.01.2017, 19:53
| Ответить | Цитировать | Написать  
46 сообщений из 46, показаны все 2 страниц
  все  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / задачка оптимизации производительности для продвинутых :-)
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=52&tid=1886674]:
 0ms
get settings:
 11ms
get forum list:
 11ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 19ms
get topic data:
 6ms
get forum data:
 2ms
get page messages:
 45ms
get tp. blocked users:
 1ms
others: 237ms
total:  336ms
созд. / загр.: 336ms
x
x
Закрыть


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