Разный план запроса при разном размере ренджа в блоке WHERE

Saperx

Всем доброго дня.
При анализе трассировки столкнулся со странным поведением.
Таблица 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)

Кодер

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

Saperx

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

ghost

странно что HINT не помогает, выложите код с хинтом, который не отрабатывает.

pberezin

а движок СУБД то хоть какой?

Кодер

Цитата:

Статистику собирал сегодня, после того как заметил это странно поведение, ничего не поменялось.

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

pberezin

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

RoustR

Как не странно, но план выполнения запроса на основе индекса может быть хуже, чем 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 есть какая настройка, которая управляет разбором перечислений от количества. Мне кажется я видел что-то подобное в доках. Но ручаться не буду, давно это было.

Kengur

Какой первичный ключ и какой CRMD_ORDER_INDEXHD?

Saperx

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

broker.chelny

А если в запрос передавать значения

Code:

%_hints oracle '&SUBSTITUTE VALUES&'

?

Удав

А почему

Code:

Search Columns: 1

?
Может индекс без поля CLIENT? Тогда это неправильный индекс.

pberezin

а что в нём практически неправильно то (применительно к движку оракла)? Запросы наоборот получаются с хорошими планами, - особенно когда на продуктиве всего один мандант.

Kengur

Удав написал(а):

А почему

Code:

Search Columns: 1

?
Может индекс без поля CLIENT? Тогда это неправильный индекс.

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

Kengur

Советую ознакомиться

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

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

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

Разный план запроса при разном размере ренджа в блоке WHERE

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