План запроса
На страницу 1, 2 След.
|
| Предыдущая тема :: Следующая тема |
| Автор |
Сообщение |
prog
Эксперт
Вступление в Клуб: 03.03.2008
|
 Пт Апр 15, 2011 07:48 План запроса |
 |
Полезность: Нет оценки
|
имеем запрос
| Код: | SELECT A1.ID
FROM ibs.Z#DOCUMENT A2, ibs.Z#DOCUM_RC A1
WHERE A1.C_MAIN_DOC = A2.ID
AND (A2.C_DOCUMENT_DATE = to_date('04/04/2011','DD/MM/YYYY') AND
SUBSTR(LPAD(A2.C_DOCUMENT_NUM, 6, '0'), (-6)) =
SUBSTR(LPAD('133461', 6, '0'), (-6)) AND A1.C_SUMMA = :B1 AND
A1.C_ISO = 'RUB' AND A1.C_PAYER#ACC = :B2
AND A1.C_RECEIVER#ACC = :B3) |
План запроса
Plan
SELECT STATEMENT ALL_ROWSCost: 16 Bytes: 86 Cardinality: 1
5 NESTED LOOPS Cost: 16 Bytes: 86 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE IBS.Z#DOCUM_RC Cost: 15 Bytes: 65 Cardinality: 1
1 INDEX RANGE SCAN INDEX IBS.IDX_Z#DOCUM_RC_SUMMA Cost: 1 Cardinality: 191
4 TABLE ACCESS BY INDEX ROWID TABLE IBS.Z#DOCUMENT Cost: 1 Bytes: 21 Cardinality: 1
3 INDEX UNIQUE SCAN INDEX (UNIQUE) IBS.PK_Z#DOCUMENT_ID Cost: 1 Cardinality: 1
Запрос медленно выполняется если в :B1 часто встречающееся значение, например 100.
Если отключить использование индекса IDX_Z#DOCUM_RC_SUMMA например вот так
| Код: |
SUBSTR(LPAD('133461', 6, '0'), (-6)) AND A1.C_SUMMA+0 = :B1
|
План запроса изменятся вот так
Plan
SELECT STATEMENT ALL_ROWSCost: 69 Bytes: 86 Cardinality: 1
5 NESTED LOOPS Cost: 69 Bytes: 86 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE IBS.Z#DOCUMENT Cost: 62 Bytes: 2 K Cardinality: 85
1 INDEX RANGE SCAN INDEX IBS.Z#DOCUMENT_DATE Cost: 5 Cardinality: 8 K
4 TABLE ACCESS BY INDEX ROWID TABLE IBS.Z#DOCUM_RC Cost: 1 Bytes: 65 Cardinality: 1
3 INDEX UNIQUE SCAN INDEX (UNIQUE) IBS.IDX_Z#DOCUM_RC_MAIN_DOC Cost: 1 Cardinality: 1
и выполняется в разы быстрее при часто встречающихся значениях B1.
Теперь вопрос:
Почему оптимизатор может не замечать характера распределения данных в индексе IDX_Z#DOCUM_RC_SUMMA и упорно использовать его в данном запросе? |
|
 |
timochev
Эксперт
Вступление в Клуб: 02.07.2007
|
| Пт Апр 15, 2011 09:30 |
|
Полезность: Нет оценки
|
У нас такая же фигня.
До сих пор не понимаю:
1. это Oracle так устроен (т.е. перекладывает определение плана запроса на разработчика)
2. или Oracle плохо настроен. В этом случае вопрос - что надо сделать, чтоб все заработало. |
|
|
.СергейПанин
Участник - экстремал
Вступление в Клуб: 24.06.2008
|
| Пт Апр 15, 2011 09:46 |
|
Полезность: Нет оценки
|
дык это вроде отношения конкретно к ораклу имеет слабое, это ж вроде теория БД - мол если значение часто встречается (более 20%) то индекс лучше не использовать. ну и соответственно что бы отключить индекс сделали неявное преобразование.
если вопрос в том как сделать что бы оракл сам анализировал выборку и отключал индекс - наверное никак |
|
|
timochev
Эксперт
Вступление в Клуб: 02.07.2007
|
| Пт Апр 15, 2011 09:54 |
|
Полезность: Нет оценки
|
| .СергейПанин пишет: | дык это вроде отношения конкретно к ораклу имеет слабое, это ж вроде теория БД - мол если значение часто встречается (более 20%) то индекс лучше не использовать. ну и соответственно что бы отключить индекс сделали неявное преобразование.
если вопрос в том как сделать что бы оракл сам анализировал выборку и отключал индекс - наверное никак |
т.е. Вы считаете, что имеет место п.1, а не п.2?
неужели Oracle не может понять полезность индексов и правильно оценить стоимость запроса? |
|
|
.СергейПанин
Участник - экстремал
Вступление в Клуб: 24.06.2008
|
| Пт Апр 15, 2011 10:35 |
|
Полезность: Нет оценки
|
| timochev пишет: | | неужели Oracle не может понять полезность индексов и правильно оценить стоимость запроса? |
без статистики я думаю нет.
хотя если актуальная статистика, оракл должен, в конкретном случае, сам оключить индекс... как то так. |
|
|
prog
Эксперт
Вступление в Клуб: 03.03.2008
|
| Пт Апр 15, 2011 11:16 |
|
Полезность: Нет оценки
|
Думаю, что я никого не обижу если процитирую ответ поддержки тут:
| Цитата: | Именно из соображений универсальности не следует менять запрос.
То, что вы предложили не является универсальным решением. Отсутствует информация как такой запрос поведет себя на данных в других банках.
Тесты на наших схемах показали, что время выполнения с индексом по сумме меньше, чем с индексом по дате (со значением суммы 10 - самым частым значением в нашей БД).
Построение плана в одном из банков показало, что оптимизатор выбрал план по дате документа (без модификации запроса).
Исходя из вышесказанного можно сделать вывод, что для данного запроса оптимизатор выбирает план выполнения исходя из характера и структуры распределения данных в БД (а именно в таблицах Z#DOCUM_RC и Z#MAIN_DOCUM).
Поэтому ограничение возможности использования того или иного индекса в данном запросе может привести к непредсказуемому падению производительности как в других банках, так и в вашем при изменении распределения данных.
Еще раз обращаю ваше внимание, что ваш запрос не является несоответствием.
|
|
|
|
prog
Эксперт
Вступление в Клуб: 03.03.2008
|
| Пт Апр 15, 2011 11:20 |
|
Полезность: Нет оценки
|
| .СергейПанин пишет: |
без статистики я думаю нет.
хотя если актуальная статистика, оракл должен, в конкретном случае, сам оключить индекс... как то так. |
Статистика актуальная.
Вот некоторые параметры БД
plsql_optimize_level 2
optimizer_features_enable 10.2.0.4
optimizer_mode ALL_ROWS
optimizer_index_cost_adj 100
optimizer_index_caching 0
optimizer_dynamic_sampling 2
optimizer_secure_view_merging TRUE |
|
|
timochev
Эксперт
Вступление в Клуб: 02.07.2007
|
| Пт Апр 15, 2011 16:57 |
|
Полезность: 1
|
посмотрел план запроса из 1-го поста на нашей базе
| Код: | SELECT A1.ID
FROM ibs.Z#DOCUMENT A2, ibs.Z#DOCUM_RC A1
WHERE A1.C_MAIN_DOC = A2.ID
AND (A2.C_DOCUMENT_DATE = to_date('04/04/2011','DD/MM/YYYY') AND
SUBSTR(LPAD(A2.C_DOCUMENT_NUM, 6, '0'), (-6)) =
SUBSTR(LPAD('133461', 6, '0'), (-6)) AND A1.C_SUMMA = :B1 AND
A1.C_ISO = 'RUB' AND A1.C_PAYER#ACC = :B2
AND A1.C_RECEIVER#ACC = :B3) |
план хороший:
| Цитата: | SELECT STATEMENT, GOAL = CHOOSE 2 1 85 14746 2
NESTED LOOPS 2 1 85 14746 2
TABLE ACCESS BY INDEX ROWID IBS Z#DOCUMENT 1 3 60 13129 1
INDEX RANGE SCAN IBS Z#DOCUMENT_DATE 1 251 2574 1
TABLE ACCESS BY INDEX ROWID IBS Z#DOCUM_RC 1 1 65 539 1
INDEX UNIQUE SCAN IBS IDX_Z#DOCUM_RC_MAIN_DOC 1 1 271 1 |
параметры у нас такие же:
| prog пишет: | plsql_optimize_level 2
optimizer_features_enable 10.2.0.4
optimizer_mode ALL_ROWS
optimizer_index_cost_adj 100
optimizer_index_caching 0
optimizer_dynamic_sampling 2
optimizer_secure_view_merging TRUE |
А как анализируете? Какой процент записей? |
|
|
Serj
Профи
Вступление в Клуб: 02.08.2007
|
| Сб Апр 16, 2011 08:25 Re: План запроса |
|
Полезность: Нет оценки
|
| prog пишет: |
IBS.IDX_Z#DOCUM_RC_SUMMA Cost: 1 Cardinality: 191
4 TABLE ACCESS BY INDEX ROWID TABLE IBS.Z#DOCUMENT Cost: 1 Bytes: 21 Cardinality: 1
3 INDEX UNIQUE SCAN INDEX (UNIQUE) IBS.PK_Z#DOCUMENT_ID Cost: 1 Cardinality: 1
Запрос медленно выполняется если в :B1 часто встречающееся значение, например 100.
.....
и выполняется в разы быстрее при часто встречающихся значениях B1.
|
- а cursor_sharing какое значение имеет ? - может выбираться не оптимальный (для данного значения переменной связывания) распарсенный курсор из shared_pool - на тесте можно проверить попробовать запрос , глянуть план - если "тяжелый" то почистить shared_pool и посмотреть снова. Фактор кластеризации индекса может иметь значение при выборе нужного индекса для доступа к данным , в 10.2.0.4 замечено некорректное поведение оптимизатора при соотношении фактора кластеризации к количеству строк в таблице> 0,7.
Кстати, параметры типа optimizer_index_cost_adj,optimizer_index_caching и т.д. искривляют представление оптимизатора- заставляют принимать не всегда верные рещения. |
|
|
prog
Эксперт
Вступление в Клуб: 03.03.2008
|
| Пн Апр 18, 2011 09:56 |
|
Полезность: Нет оценки
|
cursor_sharing = EXACT.
Информация по индексам
INDEX_NAME IDX_Z#DOCUM_RC_SUMMA
DISTINCT_KEYS 7514
NUM_ROWS 1623691
BLOCKS 259884
SELECTIVITY_RATIO 0,005
CLUSTERING_FACTOR 1564503
CLUSTERING_FACTOR/NUM_ROWS 0,964
INDEX_NAME Z#DOCUMENT_DATE
DISTINCT_KEYS 1766
NUM_ROWS 16246026
BLOCKS 321711
SELECTIVITY_RATIO 0,0001
CLUSTERING_FACTOR 1351166
CLUSTERING_FACTOR/NUM_ROWS 0,083 |
|
|
Serj
Профи
Вступление в Клуб: 02.08.2007
|
| Пн Апр 18, 2011 13:37 |
|
Полезность: Нет оценки
|
| prog пишет: | cursor_sharing = EXACT.
Информация по индексам
INDEX_NAME IDX_Z#DOCUM_RC_SUMMA
CLUSTERING_FACTOR/NUM_ROWS 0,964
INDEX_NAME Z#DOCUMENT_DATE
CLUSTERING_FACTOR/NUM_ROWS 0,083 |
cursor_sharing = SIMILAR можно попробовать, что касается индексов может попробовать сделать весьма не рекомендуемую вещь - вставив hint в курсор -сказать оптимизатору использовать именно Z#DOCUMENT_DATE, хотя может лучше поиграться на тесте с cursor_sharing. |
|
|
prog
Эксперт
Вступление в Клуб: 03.03.2008
|
| Пн Апр 18, 2011 14:12 |
|
Полезность: Нет оценки
|
вот кусок кода из пакета с проблемным запросом:
| Код: | declare
cursor c_obj is
select a1.id
from Z#DOCUMENT a2, Z#DOCUM_RC a1
where a1.C_MAIN_DOC=a2.id
and (a2.C_DOCUMENT_DATE = P_DOC_DATE and SUBSTR(LPAD(a2.C_DOCUMENT_NUM,6,'0'),(-6)) = SUBSTR(LPAD(plp$P_DOC_NUM,6,'0'),(-6)) and a1.C_SUMMA+0 = plp$P_DOC_SUM and a1.C_ISO = P_DOC_CUR and a1.C_PAYER#ACC = P_ACC_DT and a1.C_RECEIVER#ACC = P_ACC_KT);
begin
DOC_RC := NULL;
for plp$c_obj in c_obj loop
DOC_RC := plp$c_obj.id; exit;
end loop;
if DOC_RC is NULL then raise rtl.NO_DATA_FOUND; end if;
end; |
Фактические значения в переменные подставляются только во время выполнения запроса. Насколько я понимаю, параметр cursor_sharing актуален только только для случаев, когда связанные переменные не используются, например
| Код: |
select * from emp where empno=1234
и
select * from emp where empno=5678 |
|
|
|
prog
Эксперт
Вступление в Клуб: 03.03.2008
|
| Пн Апр 18, 2011 15:02 |
|
Полезность: Нет оценки
|
помог сбор статистики вот таким вот способом
| Код: | begin
dbms_stats.gather_table_stats(ownname=>'IBS',tabname=>'Z#DOCUMENT',
partname=>NULL,estimate_percent=>15,method_opt=>'FOR ALL INDEXED COLUMNS',cascade=>TRUE);
end;
begin
dbms_stats.gather_table_stats(ownname=>'IBS',tabname=>'Z#DOCUM_RC',
partname=>NULL,estimate_percent=>15,method_opt=>'FOR ALL INDEXED COLUMNS',cascade=>TRUE);
end; |
|
|
|
Serj
Профи
Вступление в Клуб: 02.08.2007
|
| Вт Апр 19, 2011 05:28 |
|
Полезность: 1
|
| prog пишет: | Насколько я понимаю, параметр cursor_sharing актуален только только для случаев, когда связанные переменные не используются, например
| Код: |
select * from emp where empno=1234
и
select * from emp where empno=5678 |
|
- не совсем, связанная переменная при cursor_sharing=similar "тестируется" на релевантность плана выполнения, если для конкретного значения переменной связывания генерируются различные планы(в одном значении например индексный доступ , при другом значении например другой индекс или фуллскан ) то переменная становится unsafe и ее значение добавляется к сигнатуре курсора, для дальнейшего повторного выполнения similar курсоров необходимо не только иметь схожий по конструкции курсор но и такое же значение переменной связывания. Cursor_sharing весьма полезная вещь, советую протестировать его значения EXACT & SIMILAR на тесте, у нас например результат использования SIMILAR весьма положительный. Интересно, а как раньше собиралась статистика ?- если через GATHER_STATS_JOB - что появился в 10-ке то он мягко говоря, не всегда успевает собрать статистику по сильно "разъехавшейся" в течении рабочего дня таблице - пример табличка очереди проводок в РБС GC.PROV$QUE. |
|
|
timochev
Эксперт
Вступление в Клуб: 02.07.2007
|
| Вт Апр 19, 2011 09:40 |
|
Полезность: Нет оценки
|
| prog пишет: | | помог сбор статистики вот таким вот способом |
А как раньше собирали? |
|
|
|
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
|
|
О проекте
Поиск
Отзывы
Регистрация
Вход
