Кто-нибудь где-нибудь натыкался на ноты с действительно хорошим и глубоким объяснением как работает динамик_самплинг?
У нас на некотором запросе оракл самопроизвольно включает dynamic_sampling level = 5, при этом по непонятной причине ошибается в кардиналити в десятки и сотни раз, хотя анализа 128 случайных блоков в данном случае должно быть более чем достаточно для верной оценки.
Возможно ли что дело в том, что табличка интервально партиционирована, и почти все нужные строки для данного запроса лежат в последней партиции ( end_time = 2100-й год ), а он выбирает для анализа случайные партиции из 1048575 виртуальных партиций ( хотя реальных там всего лишь несколько десятков)?

Например, вопросы, влияет ли stale stats на уровень динамик сэмплинга?
Делается ли он по всем партициям, или только по тем, которые в pstart .. pstop? Или только по stale?
В случае параллельных запросов - делается ли dynamic_sampling один раз для всех?

Версия 11.2.0.4.


[quot xtender]Valergrad,
Хотя подозреваю, что у тебя просто параллельное выполнение и optimizer_dynamic_sampling дефолтный:
From Oracle Database 11g Release 2 onwards, the optimizer automatically decides if dynamic sampling will be useful and what dynamic sampling level will be used for SQL statements executed in parallel. This decision is based on size of the tables in the statement and the complexity of the predicates.

Да, так и есть. И все же - кто-нибудь выяснил КАК Оракл это решает? Хотя это даже не самое интересное. Каким образом он получает такую неправильную кардиналити динамик сэмплингом - вот что самое интересное. 1 строка вместо почти миллиона...

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

Насколько я знаю, каждый параллельный слейв делает свой собственный парс:

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=238524328272102&id=751588.1&_afrWindowMode=0&_adf.ctrl-state=agewqgqrp_4

И если есть такое:
автор"However, since they have a different plan signature,"

то вдруг там каждый еще делает и хард парс??

xtenderпросто глянь его запросы в своей же сессии и увидишь, ну и с 10053

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

По вашему совету снял трассу 10053, нашел что конкретно сэмплируется. Стало еще более удивительней.

Вот запрос из трассы который dyn_sampl:

авторselect /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */
nvl (sum (c1), 0), nvl (sum (c2), 0)
from (select /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("CR") FULL("CR") NO_PARALLEL_INDEX("CR") */
1 as c1, case when "CR"."RXM" is not null then 1 else 0 end as c2
from "MY_SCHEMA"."MY_USER" sample block (0.005477, 1) seed (1) "CR") samplesub;

Он возвращает 0 строк, что и рушит весь план.

Дело не в хинтах ( что бы они ни значили ), даже такое возвращает 0 строк:

авторselect 1 as c1, case when "CR"."RXM" is not null then 1 else 0 end as c2 from "MY_SCHEMA"."MY_USER" sample block (0.005477, 1) seed (1) "CR";


При этом вот это возвращает около 500 строк:

авторselect * from "MY_SCHEMA"."MY_USER" sample block (0.005477, 1) seed (1) "CR";

Это что? Баг, резалт-кэш или еще какая-то гадость?

Ага, стало понятней.
Для этого запроса используется индекс по RXM, а он в силу своего малого размера - получается с процентом sample block ( 0.005477 ) меньше одного бока ( 0,54 блока ), что и дает результат 0 строк.

Таким образом осталось разобраться буквально в одной вещи - отчего процент у динамик сэмплинга такой маленький?

Я предположил что процент должен рассчитываться по такой схеме.
В таблице по dba_tab_statistics 91294 блока, это 100%.
Должны мы взять 128 блоков, это X%.
Следовательно X = 128*100/91294 = 0.14020

В то время как наша цифра ( 0.005477 ) в 25 с лишний раз меньше. Это как-то связано с мультиблочными чтением?

xtender,

92800 - почти то же самое.

[quot orac_list]ValergradЭто можно отключить через "_fix_control"='7452863:OFF'

На уровне сессии? Некрасиво как-то...На уровне базы? Опасно.
Пока что я убрал параллельность у запроса - на производительность не влияет, а стабильность за счет отсутсвия всяких сэмплингов возрастает. Но это выглядит как явная недоработка оракла - ставить такой низкий процент sample block для такого маленького индекса.
Как это дело рассчитывается - так и не нашел.

xtenderValergrad,

а ты попробовал 21239293 ? т.е. alter system set optimizer_dynamic_sampling=2?

Так у нас уже 2 :)

xtender,

Да, 2 на уровне системы стоит ( поставили кажется после какой-то дискуссии).
Весь трейс прислать не могу - мало ли что там есть. Но вот собственно один из кусков на котором падает кардиналити, это я уже максимально упростил случай для воспроизведения ( здесь dynamic_sampling был левела 4):

Valergrad,

Глядя на трассу начинает немного просняться. Что в этой трассе понятно?

Понятна цифра:
block cnt. table stat. : 84739 - это прям ровно столько сколькое есть в DBA_TAB_STATISTICS.
Что такое "block cnt. for sampling" и откуда эта цифра 104142 - непонятно пока ( это не сумма блоков по партициям и не сумма в dba_segments - проверил).
Далее становится ясно что было выбрано 16 случайных партиций и посчитана сумма блоков в них, что дает цифру в partition subset block cnt. : 28242
Отсюда получается процент:
select 16 * 100 / 28242 from dual; как раз дает нам необходимые 0.056653.