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

Откуда: МИНСК
Сообщений: 1273
Добрый день уважаемый All
Oracle 11g ( Клиент на W7 (XP))

Есть запрос к-й работал раньше ок (5-20 сек) теперь
он работает больше 5 мин. (на тех же данных) после чего я его снимаю
Все работает на ВЫДЕЛЕННОМ сервере (Никого кроме меня в этот момент там нет)

select b.*
, agr.id agr_id
from
(
select a.*
, MI.id mis_id -- , iss_key
, L.id rating_type_id
from
(
Select 'IR' v1 , iss_key, iss1 rating_value , to_date ( iss_date , 'YYYYMMDD') rating_date , '' rating_class
from a01_mood a
where iss1 is not null
union all
... таких 7 штук
) a
-- LEFT join moodys_issuers MI
join moodys_issuers MI
on a.iss_key = MI.issuer_number
join
(
select l.id , cl_value
from code_lookups L
join code_sets S
on L.SET_ID = S.ID and l.ver = 1
where set_short_code = 'MDYRT'
) L
on l.cl_value = v1 -- 'IR' -- !!!
) B
-- left join agent_ratings Agr -- left
join agent_ratings Agr -- left
on Agr.Rating = B.rating_value

Подзапрос возвращает 23 000 записей
Опытным путем вижу что если добавиить к любоому из 2-х последних джойнам Left - все работате ок 15 сек
Единственную причину по к-й запрос стал работать медленней я вижу
- стало меньше своб. места (по идее настройки сервера не менялись хотя гарантии тут нет)
Индекс по полям подзапроса сделать нельзя , индексы на таблицы по Agr.Rating (или MI.issuer_number) не помогают

Пока выкрутился left join agent_ratings и добавлением условия :
where
exists
( select 1
from agent_ratings Agr
where Agr.Rating = B.rating_value
)
15 сек

привожу план медленного запроса без этого условия и left join :
(из пл-скл девелопера)

SELECT STATEMENT, GOAL = ALL_ROWS 109 1 356 11479735 2
HASH JOIN 109 1 356 11479735 2
NESTED LOOPS 103 1 314 963558 2
MERGE JOIN CARTESIAN 5 1 107 22604 1
NESTED LOOPS 3 1 55 15483 1
TABLE ACCESS BY INDEX ROWID B0 CODE_SETS 1 1 23 8361 1
INDEX UNIQUE SCAN B0 SET_UK_01 0 1 1050 1
TABLE ACCESS FULL B0 MOODYS_ISSUERS 2 1 32 7121 1
BUFFER SORT 3 1 52 15483 1
TABLE ACCESS FULL B0 AGENT_RATINGS 2 1 52 7121 1
VIEW B0 98 1 207 940954 2
UNION ALL PUSHED PREDICATE
TABLE ACCESS BY INDEX ROWID B0 A01_MOOD 14 9 702 123862 1
INDEX RANGE SCAN B0 ITMP_A01_MOOD 1 41 16771 1
TABLE ACCESS BY INDEX ROWID B0 A01_MOOD 14 9 702 134422 1
INDEX RANGE SCAN B0 ITMP_A01_MOOD 1 41 16771 1
TABLE ACCESS BY INDEX ROWID B0 A01_MOOD 14 5 390 129142 1
INDEX RANGE SCAN B0 ITMP_A01_MOOD 1 41 16771 1
TABLE ACCESS BY INDEX ROWID B0 A01_MOOD 14 96 23040 121222 1
INDEX RANGE SCAN B0 ITMP_A01_MOOD 1 41 16771 1
TABLE ACCESS BY INDEX ROWID B0 A01_MOOD 14 34 8160 140582 1
INDEX RANGE SCAN B0 ITMP_A01_MOOD 1 41 16771 1
TABLE ACCESS BY INDEX ROWID B0 A01_MOOD 14 55 4290 148502 1
INDEX RANGE SCAN B0 ITMP_A01_MOOD 1 41 16771 1
TABLE ACCESS BY INDEX ROWID B0 A01_MOOD 14 22 1716 143222 1
INDEX RANGE SCAN B0 ITMP_A01_MOOD 1 41 16771 1
TABLE ACCESS FULL B0 CODE_LOOKUPS 5 699 29358 344219 1


Может есть еще какие-нибудь идеи как ускорить запрос ?
(Хинты не хотелось бы использовать - но как временная мера тоже можно)
Если кто-то подскажет как найти узкое место в этом плане - тоже было бы отлично.
8 авг 11, 16:35    [11086997]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение запроса  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Гулин Федор
Может есть еще какие-нибудь идеи

Да. Для начала дать план runtime по обоим вариантам запроса в нормально читаемом виде (stff dbms_xplan.display_cursor) и начать использовать теги для оформления текста.
8 авг 11, 17:18    [11087274]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение запроса  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Гулин Федор,

Ну и посмотреть на причину MERGE JOIN CARTESIAN
8 авг 11, 17:18    [11087280]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение запроса  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1273
env,
Спасибо за ответ

поискал по ссылкам :
select /*+ gather_plan_statistics */ ... ; -- Ваш запрос
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); -- покажите нам результат

у меня последний скл возвращает
NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 2

так медленный запрос просто вис - я не мог таким образом собрать

Сегодня все вдруг стало работать
смотрю план запроса изменился - я его копирю с пл-скл девелопера

SELECT STATEMENT, GOAL = ALL_ROWS 798 1 343 98687153 10
HASH JOIN 798 1 343 98687153 10
HASH JOIN 729 1 315 83905004 9
TABLE ACCESS FULL B0 AGENT_RATINGS 5 645 14835 215129 1
HASH JOIN 724 321 93732 73459118 9
NESTED LOOPS 6 8 520 352580 1
TABLE ACCESS BY INDEX ROWID B0 CODE_SETS 1 1 23 8361 1
INDEX UNIQUE SCAN B0 SET_UK_01 0 1 1050 1
TABLE ACCESS FULL B0 CODE_LOOKUPS 5 8 336 344219 1
VIEW B0 717 23054 5233258 60698030 9
UNION-ALL
TABLE ACCESS FULL B0 A01_MOOD 102 750 12750 6028313 2
TABLE ACCESS FULL B0 A01_MOOD 102 889 15113 8646613 2
TABLE ACCESS FULL B0 A01_MOOD 102 581 9296 7332693 2
TABLE ACCESS FULL B0 A01_MOOD 102 9975 758100 5540473 2
TABLE ACCESS FULL B0 A01_MOOD 102 3577 139503 10079673 2
TABLE ACCESS FULL B0 A01_MOOD 103 5312 164672 12222433 2
TABLE ACCESS FULL B0 A01_MOOD 103 1970 39400 10847833 2
TABLE ACCESS FULL B0 MOODYS_ISSUERS 68 10898 305144 3590291 1


MERGE JOIN CARTESIAN взяло и исчезло
я создавал индекс - но потом удалил его
отчего мог измениться план запроса ?
от того что была собрана статистика ?
Или еще по какой причине ?

-- план runtime по обоим вариантам запроса в нормально читаемом виде
можно ткнуть точно как это сделать ?
9 авг 11, 20:16    [11093936]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение запроса  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1273
explain plan for SQL

select * from table(dbms_xplan.display);

Последний план в формат. виде
3 --------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 --------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 343 | 798 (1)| 00:00:10 |
7 |* 1 | HASH JOIN | | 1 | 343 | 798 (1)| 00:00:10 |
8 |* 2 | HASH JOIN | | 1 | 315 | 729 (1)| 00:00:09 |
9 | 3 | TABLE ACCESS FULL | AGENT_RATINGS | 645 | 14835 | 5 (0)| 00:00:01 |
10 |* 4 | HASH JOIN | | 321 | 93732 | 724 (1)| 00:00:09 |
11 | 5 | NESTED LOOPS | | 8 | 520 | 6 (0)| 00:00:01 |
12 | 6 | TABLE ACCESS BY INDEX ROWID| CODE_SETS | 1 | 23 | 1 (0)| 00:00:01 |
13 |* 7 | INDEX UNIQUE SCAN | SET_UK_01 | 1 | | 0 (0)| 00:00:01 |
14 |* 8 | TABLE ACCESS FULL | CODE_LOOKUPS | 8 | 336 | 5 (0)| 00:00:01 |
15 | 9 | VIEW | | 23054 | 5110K| 717 (1)| 00:00:09 |
16 | 10 | UNION-ALL | | | | | |
17 |* 11 | TABLE ACCESS FULL | A01_MOOD | 750 | 12750 | 102 (0)| 00:00:02 |
18 |* 12 | TABLE ACCESS FULL | A01_MOOD | 889 | 15113 | 102 (0)| 00:00:02 |
19 |* 13 | TABLE ACCESS FULL | A01_MOOD | 581 | 9296 | 102 (0)| 00:00:02 |
20 |* 14 | TABLE ACCESS FULL | A01_MOOD | 9975 | 740K| 102 (0)| 00:00:02 |
21 |* 15 | TABLE ACCESS FULL | A01_MOOD | 3577 | 136K| 102 (0)| 00:00:02 |
22 |* 16 | TABLE ACCESS FULL | A01_MOOD | 5312 | 160K| 103 (1)| 00:00:02 |
23 |* 17 | TABLE ACCESS FULL | A01_MOOD | 1970 | 39400 | 103 (1)| 00:00:02 |
24 | 18 | TABLE ACCESS FULL | MOODYS_ISSUERS | 10898 | 297K| 68 (0)| 00:00:01 |
25 --------------------------------------------------------------------------------------------------
26
27 Predicate Information (identified by operation id):
28 ---------------------------------------------------
29
30 1 - access("A"."ISS_KEY"="MI"."ISSUER_NUMBER")
31 2 - access("AGR"."RATING"="A"."RATING_VALUE")
32 4 - access("L"."CL_VALUE"="A"."V1")
33 7 - access("S"."SET_SHORT_CODE"='MDYRT')
34 8 - filter("L"."VER"=1 AND "L"."SET_ID"="S"."ID")
35 11 - filter("ISS1" IS NOT NULL)
36 12 - filter("DOM1" IS NOT NULL)
37 13 - filter("FC1" IS NOT NULL)
38 14 - filter("LT1" IS NOT NULL)
39 15 - filter("SH1" IS NOT NULL)
40 16 - filter("ESTIM1" IS NOT NULL)
41 17 - filter("CORP1" IS NOT NULL)

Вопрос почему он изменился (я на сервере один)?
9 авг 11, 20:24    [11093954]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение запроса  [new]
Flok
Member

Откуда:
Сообщений: 258
ты ж статистику актуальную собрал, отчего тут удивляться?
10 авг 11, 00:27    [11094715]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение запроса  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Гулин Федор,

После сбора статистики оптимизатор "узнал", что в MOODYS_ISSUERS и AGENT_RATINGS больше 1-й строки. А значит перемножать их и накладывать фильтр на результат уже не выгодно.
10 авг 11, 10:08    [11095387]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение запроса  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1273
env,
но ведь раньше то работало
и без сбора статиситики

потом перестало

мне эти скрипты отдавать надо их будут ранить другие люди
и еще возможно под линукс (я тут не силен вообще)

Переформулируем вопрос
как избежать таких вещей ?

Я понимаю что универс. рецепта быть не может
но уже пару раз встречался с ситуацией - у меня запросы раобтают ок
на заказчика машине - виснет намертво
10 авг 11, 13:28    [11097413]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение запроса  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Гулин Федор
их будут ранить другие люди

хорошо если только ранить, а то ведь и убить могут
10 авг 11, 14:09    [11097821]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение запроса  [new]
Flok
Member

Откуда:
Сообщений: 258
Гулин Федор
env,
но ведь раньше то работало
и без сбора статиситики

потом перестало

мне эти скрипты отдавать надо их будут ранить другие люди
и еще возможно под линукс (я тут не силен вообще)

Переформулируем вопрос
как избежать таких вещей ?

Я понимаю что универс. рецепта быть не может
но уже пару раз встречался с ситуацией - у меня запросы раобтают ок
на заказчика машине - виснет намертво


ну, как вариант - использовать outlines.
создал его тут, с текущей "хорошей" статистикой и экспортом-импортом туда вставил.
ну, это при условии, что там запрос идентичный по синтаксису и распределение данных со временем практически не меняется, иначе оптимальный план здесь и сейчас выльется в тормознутый запрос на продакшне
10 авг 11, 14:24    [11097986]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение запроса  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1273
env,
думаю смысл ясен

я просто опишу схему :
я пишу скрипты под винду и проверяю их под скл-плюс
скрипты накатываются через самописную утилиту под линукс - к которой у меня нет доступа
сразу на много схем ( порядка 15)
для тестирования и девелопмента

уже пару раз случалось что запрос который работал - вдруг раз и переставал
т.е я могу переписать запрос - указав через скобки явно порядок джойнов - что вообщем и делаю
ничего на стороне сервера я подкрутить не могу как класс
статистику не имеет смысл собирать по этой же причине

вот я и ищу способы борьбы с такими явлениями
а удивляет меня это потому что объемы данных вообщем совсем невелики пока

и вот откуда MERGE JOIN CARTESIAN могло вылезти мне не ясно абсолютно
10 авг 11, 19:31    [11100279]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение запроса  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Гулин Федор,

Из отсутствия статистики по кол-ву строк в таблице и излишнего ума оптимизатора.
Если есть две "однострочных" таблицы, то их можно просто "перемножить" и наложить фильтр поверх.
11 авг 11, 11:29    [11102474]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение запроса  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Гулин Федор,

Если у вас нет возможности собирать статистику - смотрите в сторону outlines или хинта dynamic_samples
11 авг 11, 11:31    [11102486]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение запроса  [new]
Igor Korolyov
Member

Откуда: Гомель, Беларусь
Сообщений: 2512
Если это не одиночный запрос, а нечто выполняющееся многократно, то рано или поздно статистика по таблицам будет собрана - она вообщето в 11-ке автоматически собирается. При том я не вижу никаких препятствий к тому чтобы ЯВНО поставить в твой скрипт вызов DBMS_STATS и принудительно собрать статистику по нужным объектам - (в предположении что это реально решает проблему - это можно проверить у себя, принудительно "стирая", заново "собирая" и даже подсовывая "левую" статистику по задействованным объектам).

А под линуксом будет клиент (да и сам сервер) работать, или под виндой - в данном случае IMHO совершенно непринципиально.
11 авг 11, 20:06    [11106426]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить