Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Знатокам оптимизатора. Объясните почему так ?  [new]
ничего_не_понимаю.
Guest
Oracle 11g

Почему так оптимизатор себя ведет, по частям все правильно
и по логике запроса конечный запрос, состоящий из двух частей
тоже должен также оценить.
так нет же... В чем загвоздка ?

Что ему так сносит крышу в оценке на простой, практически тривиальной ситуации ????

статистика по таблицам собрана. все поля not null

Сам запрос:
SELECT count(*) FROM large_tbl a WHERE
   (a.code1,a.code2) in  
   ( SELECT code1 , code2 FROM small_tbl WHERE name = 'Name_A' AND (условие2) ) 
and (a.code1='AAA') and (a.code2='BBB')


Сначала смотрим части запроса
Все правильно оценивает по отдельности

1. Такой запрос в отдельности - правильно оценивает (1 строка возвращается0
SELECT code1, code2 FROM small_tbl WHERE name = 'Name_A' and (условие2) and (code1='AAA') and (code2='BBB') 
------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |     1 |
|*  1 |     TABLE ACCESS BY INDEX ROWID| small_tbl             |     1 |
|*  2 |      INDEX RANGE SCAN          | i_small1              |     1 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(условие2)
   2 - access("name"='Name_A' and (code1='AAA') and (code2='BBB'))


2. Другая часть запроса - тоже правильно оценивает (~13млн строк)
SELECT count(*) FROM large_tbl WHERE (code1='AAA') and (code2='BBB')
--------------------------------------------------------
| Id  | Operation         | Name               | Rows  |
--------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |     1 |
|   1 |  SORT AGGREGATE   |                    |     1 |
|*  2 |   INDEX RANGE SCAN| i_large1           |    13M|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("code2"='AAA' AND "code2"='BBB')


3. Но все вместе теперь - неправильно, резко урезает оценку итоговую
(хотя по факту - получается по-прежнему 13млн строк)
SELECT count(*) FROM large_tbl a WHERE
   (a.code1,a.code2) in  
   ( SELECT code1 , code2 FROM small_tbl WHERE name = 'Name_A' AND (условие2) ) 
and (a.code1='AAA') and (a.code2='BBB')
------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |     1 |
|   1 |  SORT AGGREGATE                |                       |     1 |
|   2 |   NESTED LOOPS                 |                       |   270K|
|   3 |    SORT UNIQUE                 |                       |     1 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| small_tbl             |     1 |
|*  5 |      INDEX RANGE SCAN          | i_small1              |     1 |
|*  6 |    INDEX RANGE SCAN            | i_large1              |  2128K|
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(условие2)
   5 - access("name"='Name_A' AND "code1"='AAA' AND "code2"='BBB')
   6 - access("A".code2"="code2" AND "A"."code1"="code1")
       filter("A"."code1"='AAA' AND "A"."code2"='BBB') 

Почему оценка вдруг по index range i_large1 падает сразу в 6 раз (а если промониторить реальное выполнение - там естественно будет при проходе по-прежнему 13млн) ?

причем если условие (and (a.code1='AAA') and (a.code2='BBB')
просто внести в подзапрос, убрав снаружи - то правильно оценивает (13 млн строк)

или если в подзапрос добавить hint /*+ NO_UNNEST */ - тоже правильная оценка

Почему важно - т.к. из-за этого, при дальнейшем усложнении запроса оптимизатор все дальше отрывается от реальности и становится совсем нехорошо.
14 мар 16, 21:51    [18930916]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизатора. Объясните почему так ?  [new]
AlexFF__|
Member

Откуда:
Сообщений: 2855
ничего_не_понимаю.,

Тебя действительно удивляет, что если
SELECT count(*) FROM large_tbl WHERE (code1='AAA') and (code2='BBB')

читает 13 М, то добавление предикатов уменьшает выборку?
6 - access("A".code2"="code2" AND "A"."code1"="code1")
       filter("A"."code1"='AAA' AND "A"."code2"='BBB') 

Посмотри 10053, там будет четко показано по шагам, как считатся селективность.
14 мар 16, 22:28    [18931054]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизатора. Объясните почему так ?  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
ничего_не_понимаю.
SELECT count(*) FROM large_tbl a WHERE
   (a.code1,a.code2) in  
   ( SELECT code1 , code2 FROM small_tbl WHERE name = 'Name_A' AND (условие2) ) 
and (a.code1='AAA') and (a.code2='BBB')
Во-первых, не понимаю зачем так писать, если code1 и code1 и так на входе...
во-вторых, помимо того что уже указал AlexFF__|, нужно еще учитывать что селективность этих условий с этими конкретными литералами и селективность джойна ("A".code2"="code2" AND "A"."code1"="code1") может существенно отличаться из-за гистограм.
в-третьих, если уж ломаете логику оптимизатору, то добавьте просто NO_UNNEST и не парьтесь...хрен его знает какие вы там еще адские условия понавтыкаете...
14 мар 16, 23:15    [18931189]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизатора. Объясните почему так ?  [new]
тынц_
Guest
AlexFF__|
если
SELECT count(*) FROM large_tbl WHERE (code1='AAA') and (code2='BBB')

читает 13 М, то добавление предикатов уменьшает выборку?
6 - access("A".code2"="code2" AND "A"."code1"="code1")
       filter("A"."code1"='AAA' AND "A"."code2"='BBB') 

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

Думаю, это из-за того, что на самом деле оптимизатор "бздит" по подзапросу со small_tbl и думает, а вдруг подзапрос них.не вернет? Вероятность между 0 и 1 он не может показать в виде 0.2 и показывает 1.
И поэтому на всякий случай с коэф-том уменьшает оценку следующему шагу. Хотя да, не слишком логично наверно )

Тестик что ли соорудить на досуге ...
15 мар 16, 09:01    [18931684]     Ответить | Цитировать Сообщить модератору
 Re: Знатокам оптимизатора. Объясните почему так ?  [new]
ничего_не_понимаю.
Guest
xtender
ничего_не_понимаю.
SELECT count(*) FROM large_tbl a WHERE
   (a.code1,a.code2) in  
   ( SELECT code1 , code2 FROM small_tbl WHERE name = 'Name_A' AND (условие2) ) 
and (a.code1='AAA') and (a.code2='BBB')
Во-первых, не понимаю зачем так писать, если code1 и code1 и так на входе..

так приложение написано. проверка по in - как база, а в зависимости от ситуаций разные доп.условия динамически добавляются, ну вот иногда и появляются такие конструкции, из-за которых плохеет. Почему оптимизатор только в этом запутывается неясно, ведь догадывается же он (по плану видно), что внутри получается конструкция вида
 ... in  ( SELECT code1 , code2 FROM small_tbl WHERE name = 'Name_A' AND (условие2) and (code1='AAA') and (code2='BBB')
-т.е. фактически все должно свестись к проверке - есть ли что-то в подзапросе, и если не ноль, тогда проход по large_tbl по этим 13млн, удовлетворяющих условию (a.code1='AAA') and (a.code2='BBB').
Ну почему он такой тупой ? Можно еще понять ситуации, которые от распределения зависят, от логики. Но здесь-то проще простого все.
16 мар 16, 21:23    [18940563]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить