задачка оптимизации производительности для продвинутых :-) / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / задачка оптимизации производительности для продвинутых :-)

25 сообщений из 46, страница 1 из 2

все

задачка оптимизации производительности для продвинутых :-)

#39380041

DBAshnik

Участник

Сообщения: 418

Рейтинг: 0 / 0

есть чужая система в которой 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

Участник

Сообщения: 418

Рейтинг: 0 / 0

оно и понятно - все full-scan-ы оказались выигрышнее чем прыгания по индексам.

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

Код: plsql

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

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

DBAshnik,

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

...

Рейтинг:

0 / 0

06.01.2017, 12:53

| Ответить | Цитировать | Написать

задачка оптимизации производительности для продвинутых :-)

#39380045

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

DBAshnik,

ну и еще:
1. планы приводи с 'allstats last', а то статистик-то не видно
2. индексы на таблицах этих покажи

...

Рейтинг:

0 / 0

06.01.2017, 12:56

| Ответить | Цитировать | Написать

задачка оптимизации производительности для продвинутых :-)

#39380049

DBAshnik

Участник

Сообщения: 418

Рейтинг: 0 / 0

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

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

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

Участник

Сообщения: 418

Рейтинг: 0 / 0

xtender,

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

Код: plsql

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

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

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

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

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

Участник

Сообщения: 418

Рейтинг: 0 / 0

Уважаемый xtender,

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

P.S. индексы все плохи ( несмотря на свежую статистику ) - прежде всего из-за низкой селективности.

...

Рейтинг:

0 / 0

06.01.2017, 16:12

| Ответить | Цитировать | Написать

задачка оптимизации производительности для продвинутых :-)

#39380116

DBAshnik

Участник

Сообщения: 418

Рейтинг: 0 / 0

хотя 1-й и 2-й вариант примерно одинаковы. Вот план первого:

Код: plsql

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

Участник

Сообщения: 785

Рейтинг: 0 / 0

DBAshnik,

невооруженным глазом видно что возвращается 250к, с os_balance читаеться 43 M, соответственно там должен быть индексный доступ в os_balance. Сделай правильные индексы там и будет счастье)

...

Рейтинг:

0 / 0

06.01.2017, 16:48

| Ответить | Цитировать | Написать

задачка оптимизации производительности для продвинутых :-)

#39380136

Sergey_Korolev

Участник

Сообщения: 151

Рейтинг: 0 / 0

DBAshnik,

Издревле, в первых сроках письма указывали номер версии и прочие атрибуты существенные для решения проблемы

...

Рейтинг:

0 / 0

06.01.2017, 17:05

| Ответить | Цитировать | Написать

задачка оптимизации производительности для продвинутых :-)

#39380138

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

DBAshnik,

Что-то вы там намудрили с параметрами, должен был бы быть iffs в первых двух вариантах. Вообще, уже бесит, что не дал всю информацию, что я просил. и до сих пор показываешь херню, а не план со статистиками...

...

Рейтинг:

0 / 0

06.01.2017, 17:10

| Ответить | Цитировать | Написать

задачка оптимизации производительности для продвинутых :-)

#39380143

Sergey_Korolev

Участник

Сообщения: 151

Рейтинг: 0 / 0

Sergey_Korolev,

Что там в star_transformation_enabled ?

...

Рейтинг:

0 / 0

06.01.2017, 17:25

| Ответить | Цитировать | Написать

задачка оптимизации производительности для продвинутых :-)

#39380145

Sergey_Korolev

Участник

Сообщения: 151

Рейтинг: 0 / 0

C ака xtender,

это как в известной комедии: "А он не может!". Если бы мог дать требуемую информацию, то и с задачкой давно разобрался :)

...

Рейтинг:

0 / 0

06.01.2017, 17:32

| Ответить | Цитировать | Написать

задачка оптимизации производительности для продвинутых :-)

#39380146

ora601

Участник

Сообщения: 785

Рейтинг: 0 / 0

Sergey_KorolevSergey_Korolev,

Что там в star_transformation_enabled ?

и каким боком она тут ?:)

...

Рейтинг:

0 / 0

06.01.2017, 17:34

| Ответить | Цитировать | Написать

задачка оптимизации производительности для продвинутых :-)

#39380148

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

Sergey_Korolev,

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

...

Рейтинг:

0 / 0

06.01.2017, 17:36

| Ответить | Цитировать | Написать

задачка оптимизации производительности для продвинутых :-)

#39380151

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

скорее всего, у них 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

Участник

Сообщения: 785

Рейтинг: 0 / 0

xtenderчтобы дважды не гоняло hash unique

На тех объемах вряд ли это какой-то bottleneck.

...

Рейтинг:

0 / 0

06.01.2017, 17:53

| Ответить | Цитировать | Написать

задачка оптимизации производительности для продвинутых :-)

#39380157

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

ora601,

В 9-й строке 20077400 делается distinct el1,cmpcode,el6 по всей таблице - это много

...

Рейтинг:

0 / 0

06.01.2017, 17:57

| Ответить | Цитировать | Написать

задачка оптимизации производительности для продвинутых :-)

#39380158

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

К тому же это эвристическая трансформация, а не стоимостная

...

Рейтинг:

0 / 0

06.01.2017, 18:00

| Ответить | Цитировать | Написать

задачка оптимизации производительности для продвинутых :-)

#39380160

ora601

Участник

Сообщения: 785

Рейтинг: 0 / 0

xtender,

Вообще для той джоин кардинальности должен быть nl по индексу, тогда и этой ерунды не будет.

...

Рейтинг:

0 / 0

06.01.2017, 18:05

| Ответить | Цитировать | Написать

задачка оптимизации производительности для продвинутых :-)

#39380161

DBAshnik

Участник

Сообщения: 418

Рейтинг: 0 / 0

xtenderSergey_Korolev,

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

эээ... "сами мы не местные" .... Я даю то, что выплёвывает мне TOAD по кнопке ЕXPLAIN PLAN! Почему этот план для вас не реальный, и что мне нужно сделать, чтоб было со "статистиками и аутлайнами " я, простите великодушно, не знаю. Это из sqlplus-a как-то запускать, да? (я с ним не очень дружу, кстати).
Или же ручками выполнять ЕXPLAIN PLAN и результаты сюда из соотв. таблицы доставать? Подскажите конкретно, плиз! :-)

...

Рейтинг:

0 / 0

06.01.2017, 18:26

| Ответить | Цитировать | Написать

задачка оптимизации производительности для продвинутых :-)

#39380162

ora601

Участник

Сообщения: 785

Рейтинг: 0 / 0

DBAshnik,

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

...

Рейтинг:

0 / 0

06.01.2017, 18:30

| Ответить | Цитировать | Написать

25 сообщений из 46, страница 1 из 2

все

Форумы / Oracle [игнор отключен] [закрыт для гостей] / задачка оптимизации производительности для продвинутых :-)

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&fpage=181&tid=1886674]:	0ms
get settings:	9ms
get forum list:	17ms
check forum access:	4ms
check topic access:	4ms
track hit:	68ms
get topic data:	7ms
get forum data:	2ms
get page messages:	37ms
get tp. blocked users:	1ms
others:	231ms

total:	380ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы