VDomHP-UX 11.31 IA
Oracle 10.2.0.5

Постоянно слетают планы запросов в которых участвуют темповые таблицы.
У некоторых запросов по 50 планов!! Хинты с самплингом не помогают.
Обычно туда что-то вставляют insert-ом с select-ом а потом клеят с другими таблицами и т.д.
При этом в плане по темповой таблице практически всегда FTS.
Это нормально, так как обычно они у нас содержат 10-100 строк (бывает и больше но редко, предугадать невозможно).

До этого был Sles 10 IA-64 и Oracle 10.2.0.5 и такого явления не было.

Это более или менее объяснимая ситуация, странность же следующая :
Проблему удалось решить обойти созданием джоба который каждые 5 минут собирает статистику по самым горячим темповым таблицам.
Точнее по темповым таблицам которые участвуют в топовых запросах.

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

Вот именно этот момент мне абсолютно не понятен.

Есть у кого то идеи почему такое происходит?

в Oracle есть еще такая штука, как hot objects (см. dbms_shared_pool.markhot)
необъяснимо большое количество планов было замечено при переводе объекта в эту категорию (это не баг, а фича)

andrey_anonymousVDomА ведь каждая сессия имеет свой независимый от других набор данных, а значит и свою статистику.
Вот именно этот момент мне абсолютно не понятен.
Как ни удивительно, но прямой связи между актуальностью статистики и производительностью запросов нет.
Статистика лишь должна наводить оптимизатор на правильные мысли, помогая принимать выгодные решения.
Если система стабильна (в плане изменений) и работает в стабильном окружении (установившиеся потоки данных) - то статистику можно и вообще не собирать :)
Поэтому собираеть ее non-stop - просто греть атмосферу, расплачиваясь за собственную лень и некомпетентность общим снижением производительности системы.

Хинт: dbms_stats.set%

выглядит даже как-то нелепо, настолько категорично. или вы просто не имели с range based partitioning, когда каждый новый день - новая партиция, и данных не пара сотен тысяч, а сотни миллионов строк. или просто пара миллионов

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

DВАdbpatchв таких условиях неправильная статистика порой чревата полным отказом системы - в смысле запросы начинают выполняться бесконечно долго, и вероятность такого - чуть менее, чем половина случаев.

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

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

пока стабильного решения этой проблемы нам найти не удалось. вернее стабилизировали в 10.2 несколько лет назад, методом копирования статистики с прошлого дня и блокировки пересбора оной, но в 11.2 оптимизатор нет нет и уйдет в штопор

пока мужественно пытаемся прикрутить interval partitioning + dynamic sampling, впечатления очень противоречивые

оно конечно понятно, что задача нетиповая, но ....

DВАdbpatchпока стабильного решения этой проблемы нам найти не удалось. вернее стабилизировали в 10.2 несколько лет назад, методом копирования статистики с прошлого дня и блокировки пересбора оной, но в 11.2 оптимизатор нет нет и уйдет в штопор

пока мужественно пытаемся прикрутить interval partitioning + dynamic sampling, впечатления очень противоречивые

оно конечно понятно, что задача нетиповая, но ....
очень даже типовая )
в 10 версии успешно решаемая вашим методом, а вот с 11 то гистограммы картинку подпортят, то кардиналити фидбак, а в 12 придут еще и адаптивные курсоры )
Я по-возможности пока отключаю всю эту хрень, есть надежда на адаптивные курсоры и расширенную статистику, но пока видимо реализация оставляет желать лучшего

гистограммы и в 10.2 портили
а вот про Cardinality Feedback мужики-то и не знали! надо тщательнее New Features изучать, спасибо за наводку.
кстати из последних стабилизаций - CREATE VIEW table_pq SELECT * FROM (SELECT /*+ PARALLEL(t,4) FULL(t) */ FROM table t)

не панацея, но хоть как-то позволят не срываться в merge join и nested loop на ad-hoc запросах, вместо hj.
осталось только популяризировать у пользователей понимание, где им нужно ожидать и применять индексный доступ, а где "быстрый" :)

xtenderDВА,

Cardinality feedback c 12.1 появился) а адаптивные планы даже оракл рекомендует выключать) гистограммы, кстати, я в таких случаях тоже правил тем же джобом, если они нужны

чо?

https://blogs.oracle.com/optimizer/cardinality-feedback

DВАdbpatch,
хитрый ход ))

ну, ты же понимаешь, это не от хорошей жизни, в смысле это не техническое решение, это больше борьба с человеческим фактором.
пользователи у нас люди хорошие, и Java знают отлично, но почему-то вопрос посмотреть explain plan у 99% популяции - непреодолимая задача, а вписать хинты... что такое хинты?

DВАdbpatchпропущено...


ну, ты же понимаешь, это не от хорошей жизни, в смысле это не техническое решение, это больше борьба с человеческим фактором.
пользователи у нас люди хорошие, и Java знают отлично, но почему-то вопрос посмотреть explain plan у 99% популяции - непреодолимая задача, а вписать хинты... что такое хинты?

жертвы ООП )))

скорее веры в стабильные и добрые черные ящики :)
хотя их тоже можно понять - ну как так, вот вчера и в течение месяца все работало отлично, а сегодня нет!

значит это ты все сам лично поломал, давай чини, разбирайся!

пояснять им, что CBO это во сама себе по дизайну такая штука, что из лучших побуждений может ломаться просто потому что
сегодня чуть другие данные - в их представление о мире не входит - ни в одной другой программисткой среде таких поведений софта
обычно не наблюдается, дескать ты нас обманываешь, а вчера просто взял и индекс небось удалил (!) и оно теперь тормозит

да даже сама фраза "давайте удалим индексы, оно быстрее заработает" - уже в головах вызывает диссонанс (учили же в ВУЗе что индексы это хорошо!)

потому ... вносим стабильность, удваиваем ВВП !

VDomdbpatchdynamic sampling - не помогает
увы, да

VDomВопрос в следующем - как влияет сбор статистики на темповой таблице в одной сессии на планы запросов в других сессиях использующих эту таблицу?

Вот в этом состоит загадка. Этого я не понимаю, это противоречит документации и логике.
а можно ссылку на упопянутое место в документации?