Текущее время: Чт, окт 17 2019, 16:45

Часовой пояс: UTC + 4 часа


Правила форума


ВНИМАНИЕ!

Вопросы по SAP Query и Quick View - сюда



Начать новую тему Ответить на тему  [ Сообщений: 15 ] 
Автор Сообщение
 Заголовок сообщения: Разный план запроса при разном размере ренджа в блоке WHERE
СообщениеДобавлено: Чт, сен 19 2019, 14:27 
Специалист
Специалист

Зарегистрирован:
Пн, мар 12 2012, 10:38
Сообщения: 160
Всем доброго дня.
При анализе трассировки столкнулся со странным поведением.
Таблица CRMD_ORDER_INDEX, мандантозависимая, в ней есть поле header, не ключевое, к нему создан индекс.
Code:
SELECT 'I' AS sign, 'EQ' AS option, guid AS low
  FROM crmd_orderadm_h
  INTO CORRESPONDING FIELDS OF TABLE @lr_guid
  UP TO n ROWS.

SELECT header object_type partner_no
  FROM crmd_order_index
  INTO CORRESPONDING FIELDS OF TABLE lt_order_index
  WHERE header IN lr_guid.


Если n < 9(т.е. в рендже до 9 записей), то всё логично, выборка идет с помощью индекса
Code:
SELECT STATEMENT ( Estimated Costs = 10 , Estimated #Rows = 108 )

       3 INLIST ITERATOR

           2 TABLE ACCESS BY INDEX ROWID CRMD_ORDER_INDEX
             ( Estim. Costs = 9 , Estim. #Rows = 108 )
             Estim. CPU-Costs = 111 794 Estim. IO-Costs = 9
             Filter Predicates

               1 INDEX RANGE SCAN CRMD_ORDER_INDEXHD
                 ( Estim. Costs = 2 , Estim. #Rows = 108 )
                 Search Columns: 1
                 Estim. CPU-Costs = 55 594 Estim. IO-Costs = 2
                 Access Predicates

Если 10 < n < 19, то всё становится интересней
Code:

SELECT STATEMENT ( Estimated Costs = 124 199 , Estimated #Rows = 228 )

       5 CONCATENATION

           1 TABLE ACCESS FULL CRMD_ORDER_INDEX
             ( Estim. Costs = 124 181 , Estim. #Rows = 12 )
             Estim. CPU-Costs = 16 710 615 017 Estim. IO-Costs = 123 968
             Filter Predicates
           4 INLIST ITERATOR

               3 TABLE ACCESS BY INDEX ROWID CRMD_ORDER_INDEX
                 ( Estim. Costs = 18 , Estim. #Rows = 216 )
                 Estim. CPU-Costs = 235 124 Estim. IO-Costs = 18
                 Filter Predicates

                   2 INDEX RANGE SCAN CRMD_ORDER_INDEXHD
                     ( Estim. Costs = 4 , Estim. #Rows = 216 )
                     Search Columns: 1
                     Estim. CPU-Costs = 124 149 Estim. IO-Costs = 4
                     Access Predicates Filter Predicates

Если n > 20, то нафиг индекс, фигачим фул сканом
Code:
SELECT STATEMENT ( Estimated Costs = 125 703 , Estimated #Rows = 240 )

       1 TABLE ACCESS FULL CRMD_ORDER_INDEX
         ( Estim. Costs = 125 703 , Estim. #Rows = 240 )
         Estim. CPU-Costs = 136 285 141 032 Estim. IO-Costs = 123 968
         Filter Predicates

Структура запрос во всех случаях получается одинаковая, только разве что количество параметров в IN отличается. Вопрос: с чем связано такое поведение?
Code:
SELECT
  "HEADER","OBJECT_TYPE","PARTNER_NO"
FROM
  "CRMD_ORDER_INDEX"
WHERE
  "CLIENT"=:A0 AND "HEADER" IN
  (:A1,:A2,:A3,:A4,:A5,:A6,:A7,:A8,:A9,:A10,:A11,:A12,:A13,:A14,:A15,:A16,:A17,:A18,:A19,:A20)


Принять этот ответ
Вернуться к началу
 Профиль Отправить email  
 
 Заголовок сообщения: Re: Разный план запроса при разном размере ренджа в блоке WHERE
СообщениеДобавлено: Чт, сен 19 2019, 16:08 
Почетный гуру
Почетный гуру
Аватара пользователя

Зарегистрирован:
Пн, мар 28 2005, 16:38
Сообщения: 1159
Так работает оптимизатор запросов к СУБД. На основании данных статы БД (она должна собираться с регулярностью и точностью, которые выбираются в соответствии с размером таблы и ее ростом) и условий выбора в WHERE, он прикидывает "какой план запроса обеспечивает максимальную оптимальность выполнения при данных параметрах". Далее собственно этот план и будет задействован при выборе данных.

_________________
Там, где я рос, единственным развлечением было запоминать число «π».(С) Н. Стивенсон


Принять этот ответ
Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Разный план запроса при разном размере ренджа в блоке WHERE
СообщениеДобавлено: Чт, сен 19 2019, 16:27 
Специалист
Специалист

Зарегистрирован:
Пн, мар 12 2012, 10:38
Сообщения: 160
Статистику собирал сегодня, после того как заметил это странно поведение, ничего не поменялось.
Last statistics date 19.09.2019 12:24
Данные более не обновлялись, т.к. система специально выведена из ландшафта для тестов. Т.е. между этими запросами никаких изменений в таблицу не было внесено. Да и все равно не понятно, почему так резко меняется поведение запроса при вводе 10 guid'ов вместо 9... Самое обидное, что не получается даже явно через хинт заставить БД использовать индекс, всё равно лупит фул скан.


Принять этот ответ
Вернуться к началу
 Профиль Отправить email  
 
 Заголовок сообщения: Re: Разный план запроса при разном размере ренджа в блоке WHERE
СообщениеДобавлено: Чт, сен 19 2019, 17:04 
Менеджер
Менеджер
Аватара пользователя

Зарегистрирован:
Пт, янв 18 2008, 11:34
Сообщения: 711
Откуда: Tashkent -> Astana -> Moscow
Пол: Мужской
странно что HINT не помогает, выложите код с хинтом, который не отрабатывает.

_________________
Пользователь не знает, чего он хочет, пока не увидит то, что он получил. (Э. Йодан)


Принять этот ответ
Вернуться к началу
 Профиль Отправить email  
 
 Заголовок сообщения: Re: Разный план запроса при разном размере ренджа в блоке WHERE
СообщениеДобавлено: Чт, сен 19 2019, 17:14 
Старший специалист
Старший специалист

Зарегистрирован:
Чт, мар 29 2007, 12:51
Сообщения: 295
Откуда: Yugorsk.RU
Пол: Мужской
а движок СУБД то хоть какой?


Принять этот ответ
Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Разный план запроса при разном размере ренджа в блоке WHERE
СообщениеДобавлено: Чт, сен 19 2019, 18:50 
Почетный гуру
Почетный гуру
Аватара пользователя

Зарегистрирован:
Пн, мар 28 2005, 16:38
Сообщения: 1159
Цитата:
Статистику собирал сегодня, после того как заметил это странно поведение, ничего не поменялось.

Значит она нормальная и по мнению оптимайзера так лучше. Почему лучше - надо долго копать по оракловым форумам. Причины могут быть самые разные. Но иногда ответ может быть и в самой стате или данных мониторинга БД. Т.е. там надо смотреть как данные лежат. какая фрагментация у таблы и индекса. Это лучше с базисом смотреть. Возможно, стоит сделать ребъюлд индекса, если вы уверены, что индекс должен работать быстрее чем то, что предлагает оптимайзер.
То что хинт не помогает - очень странно. Проверьте корректность его написания. Как альтернатива - попробовать в st04 ( если есть права) написать аналогичный запрос на чистом оракле с его хинтами и посмотреть планы запроса

_________________
Там, где я рос, единственным развлечением было запоминать число «π».(С) Н. Стивенсон


Принять этот ответ
Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Разный план запроса при разном размере ренджа в блоке WHERE
СообщениеДобавлено: Пт, сен 20 2019, 08:17 
Старший специалист
Старший специалист

Зарегистрирован:
Чт, мар 29 2007, 12:51
Сообщения: 295
Откуда: Yugorsk.RU
Пол: Мужской
[quote][/Estim. CPU-Costs = 235 124 Estim. IO-Costs = 18quote]
имхо, если cost поиска по индексу растёт уже при N>10, то скорее таблица очень сегментирована, а поля в составе индекса крайне неоптимально составлены. Потому и скатывается в full table уже при среднем объёме выборки. Тут как вариант пошринкать таблицу, или сконструировать альтернативный индекс, поставив в нём header первым полем (ну например header + object_type вообще без mandt).


Принять этот ответ
Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Разный план запроса при разном размере ренджа в блоке WHERE
СообщениеДобавлено: Пт, сен 20 2019, 08:24 
Гуру-эксперт
Гуру-эксперт

Зарегистрирован:
Пт, сен 07 2007, 08:53
Сообщения: 1273
Как не странно, но план выполнения запроса на основе индекса может быть хуже, чем TABLE FULL SCAN. Это связано с тем, что при работы с индексом мы несем дополнительные затраты на зачитывание блоков данных индекса, это первое. Второе, даже блоки данных самих таблиц могут зачитываться несколько раз за время выполнения запроса. Например, в одном блоке данных лежат записи из начала таблицы (по индексу), и из конца таблицы. Блок будет зачитан и помещен в буфер для работы с первой записью по индексу. Ко времени обращения к записи из конца таблицы, блок из буфера уже может уйти, поэтому потребуется еще раз его зачитывать.

Оптимизатор выполнения запросов оценивает селективность запросов. Селективность запроса обозначает кол-во записей, который вернет запрос. Запрос возвращает одну запись - высокая селективность, половину таблицы - низкая селективность. Самая высокая селективность - запрос по первичному ключу.
Кроме того, оптимизатор оценивает селективность условия запроса по значению. Именно для этих целей собирается статистика и строятся гистограммы распределения значений. Если мы задаем условие по значению поля, которое содержится в скажем в 50% записей в таблице, то селективность этого условия низкая. Если даже по такому полю есть индекс, он использоваться не будет - мы и так зачитаем практически все блоки данных таблицы, а с индексом - еще и блоки данных индекса.

Теперь вернемся к запросу
Code:
SELECT
  "HEADER","OBJECT_TYPE","PARTNER_NO"
FROM
  "CRMD_ORDER_INDEX"
WHERE
  "CLIENT"=:A0 AND "HEADER" IN
  (:A1,:A2,:A3,:A4,:A5,:A6,:A7,:A8,:A9,:A10,:A11,:A12,:A13,:A14,:A15,:A16,:A17,:A18,:A19,:A20)

Вот что здесь может определить оптимизатор? Тут нет конкретных значений, строить план на основе конкретных значений не получиться. Тем не менее, скорее всего можно определить, что селективность выборка по полю CLIENT будет очень низкая. По полю HEADER идет перечисление. Оптимизатор каким-то образом прогнозирует кол-во возвращаемых записей, и на основе этой информации решает что проще использовать TABLE FULL SCAN.
Кстати, обратите внимание, что прогнозируемая IO-Costs во втором и третьем случае одинаковая. То есть, оптимизатор не видит ухудшения операций чтения от перехода на TABLE FULL SCAN.

Цитата:
почему так резко меняется поведение запроса при вводе 10 guid'ов вместо 9.

Вроде в самом ORACLE есть какая настройка, которая управляет разбором перечислений от количества. Мне кажется я видел что-то подобное в доках. Но ручаться не буду, давно это было.


Принять этот ответ
Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Разный план запроса при разном размере ренджа в блоке WHERE
СообщениеДобавлено: Пт, сен 20 2019, 13:26 
Почетный гуру
Почетный гуру
Аватара пользователя

Зарегистрирован:
Чт, дек 20 2007, 19:21
Сообщения: 1368
Какой первичный ключ и какой CRMD_ORDER_INDEXHD?

_________________
я твой сап эфай внедрял
BAdI-позитив


Принять этот ответ
Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Разный план запроса при разном размере ренджа в блоке WHERE
СообщениеДобавлено: Вс, сен 22 2019, 21:50 
Специалист
Специалист

Зарегистрирован:
Пн, мар 12 2012, 10:38
Сообщения: 160
RoustR написал(а):
Кроме того, оптимизатор оценивает селективность условия запроса по значению. Именно для этих целей собирается статистика и строятся гистограммы распределения значений. Если мы задаем условие по значению поля, которое содержится в скажем в 50% записей в таблице, то селективность этого условия низкая. Если даже по такому полю есть индекс, он использоваться не будет - мы и так зачитаем практически все блоки данных таблицы, а с индексом - еще и блоки данных индекса.

В таблице 100к записей по первичному ключу.
Селективность по header
Distinct values 13.5к
1 - 10 записей 11к
11 - 100 записей 2.5к
т.е. при 10 гуидах селективность просаживается в худшем случае на 100 записей, это точно не стоит смены метода с индекса на фул тейбл скан.
RoustR написал(а):
Вот что здесь может определить оптимизатор? Тут нет конкретных значений, строить план на основе конкретных значений не получиться. Тем не менее, скорее всего можно определить, что селективность выборка по полю CLIENT будет очень низкая. По полю HEADER идет перечисление. Оптимизатор каким-то образом прогнозирует кол-во возвращаемых записей, и на основе этой информации решает что проще использовать TABLE FULL SCAN.
Кстати, обратите внимание, что прогнозируемая IO-Costs во втором и третьем случае одинаковая. То есть, оптимизатор не видит ухудшения операций чтения от перехода на TABLE FULL SCAN.

Пробовал запускать с хинтом Substitute literals, результат тот же

Структура таблицы https://www.se80.co.uk/sap-crm-tables/c ... _index.htm


Принять этот ответ
Вернуться к началу
 Профиль Отправить email  
 
 Заголовок сообщения: Re: Разный план запроса при разном размере ренджа в блоке WHERE
СообщениеДобавлено: Пн, сен 23 2019, 11:47 
Младший специалист
Младший специалист

Зарегистрирован:
Чт, дек 16 2010, 16:23
Сообщения: 97
Откуда: Набережные Челны
Пол: Мужской
А если в запрос передавать значения
Code:
%_hints oracle '&SUBSTITUTE VALUES&'
?


Принять этот ответ
Вернуться к началу
 Профиль Отправить email  
 
 Заголовок сообщения: Re: Разный план запроса при разном размере ренджа в блоке WHERE
СообщениеДобавлено: Ср, сен 25 2019, 01:32 
Почетный гуру
Почетный гуру
Аватара пользователя

Зарегистрирован:
Чт, окт 06 2005, 17:44
Сообщения: 2952
Откуда: Москва
А почему
Code:
Search Columns: 1
?
Может индекс без поля CLIENT? Тогда это неправильный индекс.

_________________
С уважением,
Удав.


Принять этот ответ
Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Разный план запроса при разном размере ренджа в блоке WHERE
СообщениеДобавлено: Ср, сен 25 2019, 09:03 
Старший специалист
Старший специалист

Зарегистрирован:
Чт, мар 29 2007, 12:51
Сообщения: 295
Откуда: Yugorsk.RU
Пол: Мужской
а что в нём практически неправильно то (применительно к движку оракла)? Запросы наоборот получаются с хорошими планами, - особенно когда на продуктиве всего один мандант.


Принять этот ответ
Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Разный план запроса при разном размере ренджа в блоке WHERE
СообщениеДобавлено: Ср, сен 25 2019, 10:48 
Почетный гуру
Почетный гуру
Аватара пользователя

Зарегистрирован:
Чт, дек 20 2007, 19:21
Сообщения: 1368
Удав написал(а):
А почему
Code:
Search Columns: 1
?
Может индекс без поля CLIENT? Тогда это неправильный индекс.


Есть такая нота 912620 p.16 которая говорит что рекомендация устарела.

_________________
я твой сап эфай внедрял
BAdI-позитив


Принять этот ответ
Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Разный план запроса при разном размере ренджа в блоке WHERE
СообщениеДобавлено: Чт, сен 26 2019, 13:15 
Почетный гуру
Почетный гуру
Аватара пользователя

Зарегистрирован:
Чт, дек 20 2007, 19:21
Сообщения: 1368
Советую ознакомиться

1527039 - Enhancements in CRM Reporting Framework
1734252 - Performance Optimization for Order Search

все поля в запросе в индекс добавляешь и будет счастье

_________________
я твой сап эфай внедрял
BAdI-позитив


Принять этот ответ
Вернуться к началу
 Профиль  
 
Показать сообщения за:  Поле сортировки  
Начать новую тему Ответить на тему  [ Сообщений: 15 ] 

Часовой пояс: UTC + 4 часа


Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей


Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете добавлять вложения

Найти:
Перейти:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
Русская поддержка phpBB