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

Откуда:
Сообщений: 703
Есть таблица в упрощенном виде можно описать как
create table hierarchy ( parent number, child number, direct_parent number)
в которой задается некоторая иерархия объектов в виде дерева.
Фактически, это стандартное плоское представление дерева: т.е. в таблице каждой связи родитель - потомок соответствует одна строка таблицы. Под потомками здесь подразумеваются не только непосредственные потомки, но и все потомки потомков и т.д. Также объект сам является своим потомком.

Для чего это сделано - чтобы например извлечь всех потомков данного объекта можно было бы просто:
select child from hierarchy 
where parent = :p1;

безо всяких иерархических запросов, которые работают очень долго. Получить корень объекта ( тоже частая задача ) тоже просто:
select parent from hierarchy 
where child = :p1
and direct_parent is null;

При наличии индексов по parent и child обе задачи решаются очень хорошо.
Но есть проблема в оценке оптимизатором кардиналити. Предположим, у нас в hierarchy
1 млн. строк, листов в иерархии - около 200 тысяч. Это означает, что в среднем у каждого листа около 4х потомков ( строк 5, одна из них является петлей ). Понятно, что есть объекты, у которых около 100 тысяч потомков ( близкие к корням ), а у большинства объектов только 1 потомок - он сам. Понятно, что запросы, которые получают всех потомков объекта, часто выполняют для объектов ближе к листам, и редко для корневых объектов.
Оптимизатор, тем не менее у этого запроса кардиналити оценивает как

select child from hierarchy 
where parent = :p1;

Plan
SELECT STATEMENT ALL_ROWS
Cost: 3 Bytes: 1.598 Cardinality: 94 CPU Cost: 36.432 IO Cost: 3 Time: 1

Во всех запросах которые используют этот подзапрос, такая переоценка кардиналити в 20 раз мешает оптимизатору выбрать верные планы, приходится поправлять хинтами. Но хочется решить проблему, а не ее следствия.
Соответственно, вопросы:

1. Как он считает кардиналити?
2. Можно ли заставить его считать ее по-другому?

Ответ: "читай Льюиса", хоть и верен по сути, но сейчас нет времени, на новогодних праздниках обязательно почитаю, а проблему хочется решить сейчас.
2 дек 10, 13:15    [9874870]     Ответить | Цитировать Сообщить модератору
 Re: Поправить кардиналити.  [new]
PaulEr
Member

Откуда:
Сообщений: 3794
Valergrad,

По таблице и индексам сбор статистики делали? 10053 трейс снимите, увидите, как идет расчет cardinality.
2 дек 10, 13:21    [9874924]     Ответить | Цитировать Сообщить модератору
 Re: Поправить кардиналити.  [new]
Valergrad
Member

Откуда:
Сообщений: 703
PaulEr
Valergrad,

По таблице и индексам сбор статистики делали? 10053 трейс снимите, увидите, как идет расчет cardinality.


Да, статистика собирается, смотрел гистограммы, в них все верно.
10053 сниму попозже, ни разу не делал.
2 дек 10, 13:26    [9874964]     Ответить | Цитировать Сообщить модератору
 Re: Поправить кардиналити.  [new]
PaulEr
Member

Откуда:
Сообщений: 3794
Valergrad,

А как собираете? Можете показать скрипт?
2 дек 10, 13:30    [9875008]     Ответить | Цитировать Сообщить модератору
 Re: Поправить кардиналити.  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Т.е. в зависимости от :p1 cardinality меняется от 100000 до 1. Логично попробовать собрать гистограмму по parent и использовать литерал вместо bind переменной. Если хочется зафиксировать какую-то определенную cardinality, то воспользоваться хинтом cardinality или opt_estimate.
2 дек 10, 13:30    [9875022]     Ответить | Цитировать Сообщить модератору
 Re: Поправить кардиналити.  [new]
Valergrad
Member

Откуда:
Сообщений: 703
wurdu
Т.е. в зависимости от :p1 cardinality меняется от 100000 до 1. Логично попробовать собрать гистограмму по parent и использовать литерал вместо bind переменной. Если хочется зафиксировать какую-то определенную cardinality, то воспользоваться хинтом cardinality или opt_estimate.


1. Гистограмма собирается.
2. Использовать литерал не получится - эта таблица применяется с другими подзапросами, джойнами и т.д.
3. Фиксировать кардиналити во всех запросах использующих эту таблицу - это как хардкод, который делать не хочется.
4. Хочется решить проблему, а не ее следствия.
2 дек 10, 13:43    [9875156]     Ответить | Цитировать Сообщить модератору
 Re: Поправить кардиналити.  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Valergrad
wurdu
Т.е. в зависимости от :p1 cardinality меняется от 100000 до 1. Логично попробовать собрать гистограмму по parent и использовать литерал вместо bind переменной. Если хочется зафиксировать какую-то определенную cardinality, то воспользоваться хинтом cardinality или opt_estimate.


1. Гистограмма собирается.
2. Использовать литерал не получится - эта таблица применяется с другими подзапросами, джойнами и т.д.
3. Фиксировать кардиналити во всех запросах использующих эту таблицу - это как хардкод, который делать не хочется.
4. Хочется решить проблему, а не ее следствия.
Насколько я понимаю, разница в 20 раз это между 5 и 94. Для большинства случаев этот достаточно хорошая точность (разница всего на 89), и в большинстве случаев на план такая погрешность существенного влияния не оказывает (понятно, что бывают специфичные случаи). Если используются bind переменные, то гистограммы часто вредны. В данном случае можно убрать гистограмму и руками поменять статистику num_distinct до значения приводящему к нужной cardinality. Но не мешало бы в начале убедиться, что эта погрешность приводит к массовым изменениям планов и не проще прохинтовать конкретный проблемный запрос.
2 дек 10, 14:02    [9875355]     Ответить | Цитировать Сообщить модератору
 Re: Поправить кардиналити.  [new]
Valergrad
Member

Откуда:
Сообщений: 703
"В данном случае можно убрать гистограмму и руками поменять статистику num_distinct до значения приводящему к нужной cardinality."

Так он потом при подсчете статистики опять поменяет num_distinct на правильное. Или вы предлагаете не считать статистику по ней? Таблица-то меняется.
Мне интересно всего лишь понять причину, почему сardinality неправильная.

"и не проще прохинтовать конкретный проблемный запрос."

нет, таких запросов очень много. И таблиц с подобной структурой не одна.

"разница на 89"

при перемножении cardinality ( при всяких джойнах ), существенно то, что разница в 20 раз.
Предположим мы приджойним табличку сюда, с селективностью 20. Мы получим итоговую кардиналити 2000, вместо реальной 100. Если теперь приджойнить еще какую-нибудь иерархическую таблицу сюда, мы получаем 200 тысяч кардиналити, вместо реальной 500. После чего оптимизатор, уже совсем другие планы выбирает.
Это и является причиной того, что все запросы обращающиеся к этой, и подобным иерархическим таблицам у нас все хинтованы-перехинтованы вусмерть. И от чего хотелось бы уйти :)
2 дек 10, 14:25    [9875537]     Ответить | Цитировать Сообщить модератору
 Re: Поправить кардиналити.  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Valergrad
"В данном случае можно убрать гистограмму и руками поменять статистику num_distinct до значения приводящему к нужной cardinality."

Так он потом при подсчете статистики опять поменяет num_distinct на правильное. Или вы предлагаете не считать статистику по ней? Таблица-то меняется.
Мне интересно всего лишь понять причину, почему сardinality неправильная.

"и не проще прохинтовать конкретный проблемный запрос."

нет, таких запросов очень много. И таблиц с подобной структурой не одна.

"разница на 89"

при перемножении cardinality ( при всяких джойнах ), существенно то, что разница в 20 раз.
Предположим мы приджойним табличку сюда, с селективностью 20. Мы получим итоговую кардиналити 2000, вместо реальной 100. Если теперь приджойнить еще какую-нибудь иерархическую таблицу сюда, мы получаем 200 тысяч кардиналити, вместо реальной 500. После чего оптимизатор, уже совсем другие планы выбирает.
Это и является причиной того, что все запросы обращающиеся к этой, и подобным иерархическим таблицам у нас все хинтованы-перехинтованы вусмерть. И от чего хотелось бы уйти :)
cardinality без гистограммы рассчитывается по формуле cardinality = num_rows / num_distinct. В случае с frequency гистограммой и литералом возможна достаточно точная cardinality, но в даном случае не думаю что frequency гистограмма возможна, поэтому остается height balanced и надеяться что при bind peeking при парсинге значение переменной позволит получить похожую кардинальность. Поэтому проще сделать фэйковую статистику/гистограмму. Политика сбора статистики зависит от конкретного случая. У меня есть таблицы с зафиксированной статистикой, несмотря на то что фактически данные меняются, есть таблицы на котороых статистика собирается, а затем выставляются нужные значения для нужных параметров. Цель - получение статистик приводящих к оптимальным планам, а не просто "актуальных" статистик.
2 дек 10, 14:47    [9875707]     Ответить | Цитировать Сообщить модератору
 Re: Поправить кардиналити.  [new]
RA\/EN
Member

Откуда:
Сообщений: 3658
Никак это не заставишь разрулить за тебя Оракл (литерал - не выход).
Если хочешь избежать IRS на больших объемах - придется доработать запросы, чтоб они были двухвариантными, и за малое число логридов определяли, что здесь у нас "слон". Как вариант - union all + matview, содержащее "многодетных", либо признак "многодетный" непосредственно в табличке, либо признак "расстояние от корня" (="потенциально многодетный").
Так можно обойтись 3-4 (или 8-4) лишними логридами.

Но вообще лучше, конечно, "знать" заранее, что мы хотим получить.
Глистограмка вряд ли поможет (как надежное решение).
2 дек 10, 14:57    [9875791]     Ответить | Цитировать Сообщить модератору
 Re: Поправить кардиналити.  [new]
Valergrad
Member

Откуда:
Сообщений: 703
втф Логриды?
2 дек 10, 15:10    [9875898]     Ответить | Цитировать Сообщить модератору
 Re: Поправить кардиналити.  [new]
PaulEr
Member

Откуда:
Сообщений: 3794
Valergrad
втф Логриды?


Логические чтения.
2 дек 10, 15:14    [9875929]     Ответить | Цитировать Сообщить модератору
 Re: Поправить кардиналити.  [new]
Valergrad
Member

Откуда:
Сообщений: 703
"У меня есть таблицы с зафиксированной статистикой, несмотря на то что фактически данные меняются, есть таблицы на котороых статистика собирается, а затем выставляются нужные значения для нужных параметров. Цель - получение статистик приводящих к оптимальным планам, а не просто "актуальных" статистик"

Спасибо, возможно так и придется сделать. Отключить гистограмму по этому столбцу.
2 дек 10, 16:42    [9876703]     Ответить | Цитировать Сообщить модератору
 Re: Поправить кардиналити.  [new]
Valergrad
Member

Откуда:
Сообщений: 703
Попробовал, удалил гистограмму. ( delete statistics & analyze table )
Кардиналити увеличилась до 6 тысяч 0_0.
Похоже все-таки придется на выходных разбираться с тем, как считается селективность.
3 дек 10, 17:04    [9883640]     Ответить | Цитировать Сообщить модератору
 Re: Поправить кардиналити.  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Valergrad
Попробовал, удалил гистограмму. ( delete statistics & analyze table )
Кардиналити увеличилась до 6 тысяч 0_0.
Похоже все-таки придется на выходных разбираться с тем, как считается селективность.
Гистограмма удаляется и статистика собирается с помощью dbms_stats.gather_table_stats(... fo columns ... size 1. Ты хочешь сказать что теперь кардинальность не по этой формуле считается cardinality = num_rows / num_distinct ? Я собственно поэтому и сказал, что убираем гистограмму и меняем num_distinct, чтобы привести cardinality к нужному значению.
3 дек 10, 17:18    [9883745]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить