CftClub.ru
Клуб специалистов ЦФТ-Банк

План запроса
На страницу 1, 2  След.
 
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Oracle DBA
Предыдущая тема :: Следующая тема  
Автор Сообщение
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 пишет:
помог сбор статистики вот таким вот способом

А как раньше собирали?
Показать сообщения:   
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Oracle DBA Часовой пояс: GMT + 3
На страницу 1, 2  След.
Страница 1 из 2

 
Перейти:  
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Рейтинг@Mail.ru