Optimizer=CHOOSE

← →
{bas} © (2002-11-28 15:35) [0]

Мастера, помогите разобратся со статистикой и индексами...
Есть для всех таблиц нужные индексы, собрал сатистику по всем таблицам как написано в доке (analyze table <table_name> compute statistics) и все равно этот оракул без хинтов не хочет понимать, что нужно юзать индексы...
Даже при таком легком запросе он делает FullScan двух таблиц:
SELECT * FROM t1, t2 where t1.id = t2.id and t1.fk = 10
на t1.id, t2.id и t1.fk есть индексы

ПОМОГИТЕ..........А то уже давано с этим борюсь, но все никак...

← →
petr_v_a © (2002-11-28 15:46) [1]

А по каким хинтам он начинает индекс использовать?
А таблицы большие? может, FTS выгоднее, т.к. по чтениям помещается в db_multiblock_read_count ( или около этого :)

← →
Sly © (2002-11-28 16:09) [2]

А распределение данных t1.fk, t2.id хорошее?
Так ли надо использовать Select *?

← →
{bas} © (2002-11-28 16:11) [3]

>>А по каким хинтам он начинает индекс использовать?
по хинтам /*+ INDEX()*/
>>А таблицы большие?
около 20т. записей
>>может, FTS выгоднее, т.к. по чтениям помещается в db_multiblock_read_count ( или около этого :)
что такое FTS??

Кстати поставил Optimizer_Mode = RULE, он естественно поднял все индексы, но остается вопрос: Если надо выбрать из индексов какой юзать сначала у разных таблиц, то все равно нужна статистика???

← →
{bas} © (2002-11-28 16:21) [4]

>>Sly © (28.11.02 16:09)
А как надо его использовать, если мне нужные данные из t2(по связи с t1), и что бы поле из t1 НАПРИМЕР равнялось 10?????

← →
Sly © (2002-11-28 16:45) [5]

Стоимостный оптимизатор, по собранной статистике решает, что FULL TABLE SCAN (FTS), выгоднее чем (UNIQUE?) INDEX SCAN + TABLE ACCESS BY ROWID.
Причины могут быть следующие:
1. Плохое распределение данных, например t2.fk в 50% строк принимает значение 10, а 50% значение 20, тогда по операциям ввода вывода FTS может оказаться более выгодно (см. petr_v_a © (28.11.02 15:46))
2. (Может быть не применимо) Ты указал SELECT * и оптимизатор, решил что, раз уж все равно тащить все данные из таблиц, то дешевле сразу ее полностью просканировать. Следует учесть, что если указать в SELECT только колонки из таблиц, то он возьмет значения из индексов, не лазая в таблицу. Т.е лучше указать данные, которые действительно нужно получить.

Кстати, 20 тыс записей – это в одной таблице, а во второй?

← →
petr_v_a © (2002-11-28 17:08) [6]

В плане запроса посмотрите, правильно ли оптимизатор прикидывает cardinality.
Если неправильно, либо плохая статистика, либо неравномерное распределение значений -> используйте гистограммы
Если правильно, значит, либо оптимизатов все делает правильно, либо почему-то Full Table Scan считает выгоднее ошибочно -> вопрос к админу ( db_multiblock_read_count, hash_area_size, sort_area_size, optimizer_index_cost_adj и т.д. и т.п )
Но по результатам исследования кофейной гущи :) можно предположить, что поле fk имеет очень мало различных значений относительно общего кол-ва записей, и FTS действительно выгоднее - Oracle умеет его делать очень хорошо

← →
{bas} © (2002-11-28 17:58) [7]

sort_area_size = 33554432
остальные по уолчанию...

На самом деле я делаю на много сложнее запросы, но и там Оракул не хочет использовать индексы даже если я выбираю ОПРЕДЕЛЕННЫЕ поля из таблиц

неужели лучше сделать FTS чем по индексу искать, кстати fk=10, всего несколько записей(к примеру)

← →
{bas} © (2002-11-28 18:07) [8]

И вообще, хочу сказать, что с этим "ХОРОШИМ" FTS у меня все жутко тормозит

← →
petr_v_a © (2002-11-28 18:23) [9]

А зачем sort_area_size 32 мегабайта? :)

Давайте определимся - Вы разработчик или администратор( пусть вынужденный :)?

>неужели лучше сделать FTS...
Оптимайзер считает, что лучше. Предыдущие вопросы остаются в силе - правильно ли прикидываются cardinality, насколько равномерно распределено fk, и каково отношение num_distinct_rows/num_rows, сколько там null`ов

← →
{bas} © (2002-11-28 18:52) [10]

cardinality я не нашел в плане запроса(я работаю с TOAD, а там этого нет),
fk(у меня очень динамичный программный комплекс) =
5 - от 0 до 3
10 - всегда от 0 до 10
50 - %1
20 - все остальное

num_distinct_rows/num_rows = 1
null - вообще нет

← →
{bas} © (2002-11-28 18:53) [11]

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

← →
Sly © (2002-11-28 19:06) [12]

Похоже, действительно на проблему с распределением, а вот такой запросец, какой результат покажет?
SELECT m / c
FROM (SELECT MAX (cnt) m
FROM (SELECT t1.fk, COUNT (1) cnt
FROM t1
GROUP BY t1.fk)),
(SELECT COUNT (*) c
FROM t1)

← →
petr_v_a © (2002-11-28 19:32) [13]

Если по совместительству администратор, то почему sort_area_size 32 мегабайта, а не 48? Не сочтите за наезд, просто хочу, чтоб Вы задумались и избежали кучи геморроя. Впрочем, это вопрос не этого форума.

>кстати fk=10, всего несколько записей(к примеру)

А оптимайзер откуда это знает? По статистике он видит, что на 20 тыс записей 4 различных значения fk => условию fk=10 удовлетворяет 5 тыс записей из 20 тыс => FTS - то что надо! :)

Чтоб вышеупомянутое чудо интеллектуальной техники :) так не считало, при анализе надо строить гистограммы

← →
{bas} © (2002-11-28 19:52) [14]

Sly © (28.11.02 19:06)
ну я же сказал, что во сновном fk=20, так и получилось: .991150442

petr_v_a © (28.11.02 19:32)
как строить гистограмы(какой пакет и проц.)??

← →
{bas} © (2002-11-28 19:57) [15]

petr_v_a © (28.11.02 19:32)
а не скажите оптимальные параметры этих настроек(например, как у вас):
db_multiblock_read_count,
hash_area_size,
sort_area_size,
optimizer_index_cost_adj и т.д. и т.п

← →
petr_v_a © (2002-11-28 20:02) [16]

команда analyze и пакет dbms_stat
правда, при использовании bind-переменных про гистограммы оно :) забудет
За подробностями отсылаю к документации
Удачи :)

← →
petr_v_a © (2002-11-28 21:28) [17]

> {bas} © (28.11.02 19:57)
А не подскажите, как оптимально писать программы на Delphi? :)

1. Изучите архитектуру сервера
2. Поймите назначение этих параметров, поведение каких подсистем они задают
3. Поймите, что Вы хотите от системы
4. Сформулируйте для себя, что Вас в поведении системы не устраивает ( это Вы в данном случае сделали четко )
4. Решите какие из них стоит трогать и как

На все это уйдет от 1-го до 100 лет :) , но оно того стоит, да и альтернативный выход только один - попасть в орешник с вопросом типа {bas} © (28.11.02 19:57) ((:
Про неочевидные вещи ( т.е. те, которые не описаны в документации ) спрашивайте.
Удачи :)

← →
vyalkov (2002-11-29 06:38) [18]

Не занимайся садомазохизмом.
Для начала удали всю статистику, которую ты имел смелость неосторожно собрать (сделать analyze).
Выстави OPTIMIZER MODE по умолчанию.
Все - занавес. Оптимизатор теперь сам разберется, какие индексы использовать.
IMHO, лучше вообще не использовать analyze.

← →
Sergey13 © (2002-11-29 09:11) [19]

2vyalkov (29.11.02 06:38)
Как ты крут!!! 8-)
>Для начала удали всю статистику, которую ты имел смелость неосторожно собрать (сделать analyze).
Действительно, за такое нахальство оракловой поддержки лишать надо. 8-)
>Выстави OPTIMIZER MODE по умолчанию.
И какой он по твоему. По моему(смотрю в доку по 8.0.4) это CHOOSE. Но самое интересное, что при выполнении предыдущего твоего совета будет работать RULE.
>Все - занавес.
Согласен. 8-)
>IMHO, лучше вообще не использовать analyze.
Я предлагаю еще мораторий на EXPLAINE PLAN. 8-)

← →
petr_v_a © (2002-11-29 09:58) [20]

Документацию не читать, в базу не заходить, пользователей уволить, ружья с собой не брать, из автобуса не выходить :))

← →
Sly © (2002-11-29 10:17) [21]

>petr_v_a © (29.11.02 09:58)

>Документацию не читать, в базу не заходить, пользователей уволить, ружья с собой >не брать, из автобуса не выходить :))
Забыл: Oracle не ставить.

← →
petr_v_a © (2002-11-29 10:21) [22]

Sly © (29.11.02 10:17)
> Забыл: Oracle не ставить
Сам упадет :)

>>petr_v_a © (28.11.02 21:28)
Я просто просил совета в каком направлении двигатся конкретно в данном вопросе и совсем не хотел, что бы кто-то делал за меня мою работу. Я не просто с бухты барахты спросил : а у меня все тормозит, не пришлете ли свой Oracle, м.б. с ним у меня все б. нормально работать - я достаточно много перелопатил доки, в которой написано, что после ANALYZE TABLE у вас б. статистика и ее б. юзать оптимизатор Оракула(чего не произошло поэтому и задал вопрос). Как всегда не сказали ничего конкретного - в каком направлении двигатся(только понял, что надо юзать не просто ANALYZE, а dbms_stat, хотя я думаю, что от этого ничего не изменится).
И в итоге, спасибо за отсылку ко ВСЕЙ доке(по моим подсчетам около 400М текста с картинками)

описание analyze - в SQL Reference
описание dbms_stat - PL/SQL supplied packages reference

Использовать dbms_stat вместо analyze рекомендует техподдержка, хотя analyze проще в использовании

Возможную ( на 99% ) причину проблемы я объяснил, метод решения ( строить гистограммы ) - тоже.

Отсылка ко ВСЕЙ доке - а ниче не поделаешь, прочитать ее надо действительно всю, а некоторые разделы типа Concepts, SQL Reference, Designing and Turning for Performance я лично читал раза три. В любом случае в ней надо ориентироваться, и, честно говоря, я не думал что предложение посмотреть описание конкретной команды и конкретного пакета окажется "отсылкой ко всей доке".

Если я Вам вышлю свой init.ora файл, легче Вам не станет, настройки "как у Вас" не подойдут - это моя система со своими требованиями и своим железом, а у Вас - Ваша

Обидеть не хотел

2{bas} © (29.11.02 10:36)
>что после ANALYZE TABLE у вас б. статистика и ее б. юзать оптимизатор Оракула(чего не произошло поэтому и задал вопрос).
А почему ты в этом сомневаешься? Фулскан имеешь - так это не всегда плохо. А может надо индексы пересмотреть? Чего ты хочешь то?
А вот например на вопрос о твоем sort_area_size ты так и не ответил - зачем он тебе такой? 32М это очень (неоправдано) много. На каждую сессию у тебя выдается по 32м памяти для сортировок. А если у тебя еще sort_area_retained_size стоит по умолчанию =sort_area_size - то вообще труба. У тебя всего памяти сколько? У тебя может свопинг идет страшенный из за этого - отсюда тормоза. Сколько сессий одновременно работает?

Sergey13 © (29.11.02 11:20)
Поэтому я и поросил у petr_v_a © прислать свой init.ora файл, что бы сравнить и сделать выводы(о чем читать)

>Sergey13 © (29.11.02 11:20)
>На каждую сессию у тебя выдается по 32м памяти для сортировок

Ну прям сразу-то не выделяется, только при первой сортировке, но все равно значение, ессно, не совсем адекватное. Гораздо хуже другое:
на определение стоимости hash join ( который подразумевает FTS ) и, соответственно, выбор между ним и nested loops ( который подразумевает индекс ) очень сильно влияет параметр hash_area_size, который по умолчанию равен sort_area_size*2

>{bas} © (28.11.02 17:58)
> ...но и там Оракул не хочет использовать индексы...
А на фиг ему использовать, если сказано, стоимость hash join - "копейки"?
К изначальной ситуации ( с fk=10 и "кривым" распределением ) это уже отношения не имеет, просто еще один аргумент все-таки ориентироваться в документации и не обижаться

2petr_v_a © (29.11.02 13:20)
> Ну прям сразу-то не выделяется, только при первой сортировке
Покажи мне сессию без сортировок. 8-) Разве что системные процессы.
2{bas} © (29.11.02 12:39)
~70*32M = ~2240M только на сортировку!!!(а еще система, буфера...). При 500М физической памяти имеем 1.7Г в свопе - это очень грубый подсчет, но дает представление о порядке чисел. Вот ты и имеешь тормоза. Наверное когда что-то типа OEM на серваке запускаешь - можно чаю попить сходить? 8-) Поставь sort_area_size=1М и sort_area_retained_size=64К ну или около того (поиграйся - посмотри по статистике сколько будет сортировок на диске). А для больших сортировок есть темповое таблспейс.

petr_v_a © (29.11.02 13:20)
Я не обижаюсь, просто я действительно читал(но наверное мало) и не понимал момента о статистике. Спасибо за помощь.
Сейчас я кстати собрал статистику с помощью, dmbs_stats и в плане запроса я увидел далгожданные индексы

> Sergey13 © (29.11.02 13:42)

Sort_area_size - предел, до которого можно выделять память под сортировку,при превышении начинается использование temp, а выделяется-то она по мере необходимости. После окончания сортировки она отдается системе, за исключением Sort_area_retained_size байт.

На этом дискуссию предлагаю в данной ветке прервать, какая-то она уже не по профилю форума получается, а автор удовлетворен :)

> {bas} ©
В общем-то я начал чуть ёрничать ( за что извиняюсь ), не потому что Вы "мало читали" или не "не понимали" ( человека, который ВСЕ прочитал и ВСЕ ПРОЧИТАННОЕ понял, скорее всего, не существует) , а потому, что обиделись ( во всяком случае, мне так показалось ) на предложение прочитать описание КОНКРЕТНОЙ команды
В любом случае, я надеюсь, интересно было всем, несмотря на проскочившую было напряженность :)

Optimizer=CHOOSE Найти похожие ветки