Спасибо АнатоЛой.
Будем "копать" в направлении, указанных Вами.
Но в голове пока не укладывается, да и опыта не хватает как такое может быть ("тормоз" на запросе).
Отобрать энное кол-во строк, а из них выбрать одну min (date). Время должно уйти только на сканирование строк в диапазоне.

select min (date) from (select ... into temp) менее 1 сек
SELECT {+AVOID_INDEX (plat paym__ix_reg_date}) min (date) менее 1 сек
SELECT {+INDEX (plat paym__ix_reg_paym_dt)} min (date) менее 1 сек
select min (date) 55 минут
SELECT {+INDEX (plat paym__ix_reg_date)} min (date) 55 минут

GVF112GVF.
oncheck -pl выдает следующие уровни
Level 0
Level 1
Level 2
Level 3

Размер страницы 16К

Индексы по полям reg_date, reg_paym_dt разнесены в разные dbspace, данные в эти dbspace не входят

Спасибо АнатоЛой за ответ.
Решение проблемы по времени не критично, т.к. обходные варианты найдены (хинты или через темповую таблицу)

автор1. Вопрос: "Как так могло случиться?"
Ответ: Туча вариантов. Вы действительно готовы все их сейчас обсуждать? Или ожидаете, что кто-то готов вам их просто так рассказать?!


2. Вопрос: "Почему запрос, где сервер выбрал индекс по reg_date (а не по reg_paym_dt), работает на 4 порядка дольше?...
"Отобрать энное кол-во строк, а из них выбрать одну min (date). Время должно уйти только на сканирование строк в диапазоне."

Ответ:
Потому что в условиях фильтрации нет ограничений по полю reg_date.
min у вас по reg_date, а фильтр у вас по reg_paym_dt.
И при выбранном индексе по reg_date сервер для получения ответа на вопрос вычитывает ВСЮ таблицу.
Кстати: Может у вас reg_date - это reg_paym_dt без времени - так сервер про это не знает :).

1. Не готов и не ожидаю.
2. У меня последний вопрос. Зачем серверу вычитывать всю таблицу, когда я просил его выбрать минимальную дату (пусть и не полю, указанному в условии) из некоторого диапазона выбранных строк.
Раньше он так и работал.
Также. При задании ограничений в 2-3 часа запрос не сканирует всю таблицу.
Прошу «ногами не бить». Если есть предположения, ответьте.

ЗЫ. Тип полей reg_date и reg_paym_dt DATETIME YEAR TO SECOND.

Спасибо ОГРОМНОЕ. Ваш первый ответ (за 01/07/15) открыл мне много того, о чем я представления не имел.

авторПредположения:

Первое:
раньше вы тоже пробовали запускать запрос с фильтром за целый день и больше? Если да, использовали ли вы раньше фильтр за последние дни?

Второе:
не поменялся ли принцип сбора статистики: который был раньше, и который вы имеете сейчас.

Третье:
понимаете ли вы, что просто "UPDATE STATISTICS HIGH" :
а) это не идеальная статистика, особенно для вашего случая;
б) имеет ещё параметры;

Четвёртое:
вы можете заставить сервер работать лучше, если не будете ему сами указывать индексы,
а сможете ему "рассказать", как "связаны" reg_date и reg_paym_dt.
Ведь они как-то связаны, правда?
Ну, например,:
а) reg_date >=reg_paym_dt
б) reg_paym_dt >=reg_date
в) ABS(reg_paym_dt - reg_date) < 1 месяца
г) ...
Пятое: не поменялся ли заметно объём БД с того момента, как "раньше оно так и работало".

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

Седьмое и последнее: определитесь что из списка для вас важнее понять:
- как оно так случилось: раньше хорошо, теперь плохо;
- почему оно так работает, как работает сейчас;
- как сделать, чтобы работало хорошо.

Первое.
запрос запускается только за день, даты в основном (95%) предыдущий месяц. Запросов может быть 1-31
Второе.
Работает крон 1 раз в неделю (update statistics medium from table ..)
HIGHT запустил в ручную 1 только раз скриптом, что выдал Server Studio (сначала LOW, Hight,Medium)
Третье.
Понимаю. Буду досконально разбираться.

Четвертое.
p.reg_paym_dt >= p.reg_date. Запустил запрос с этим условием. Лучше не стало.





Пятое.
Сама БД растет (с начала года на 4 Гб), но таблица по которой делается выборка, практически не меняется. С одним но. Данные вставляются сотнями тысяч и сотнями тысяч удаляются. Кол-во строк на любой день может как прибавиться, так и убавиться.

Шестое.
Нет. Ежедневно копируется onconfig и добавляется к архиву (сравнил, параметры за последние 2 месяца не менялись).

Седьмое.
- как оно так случилось: раньше хорошо, теперь плохо;


А можно еще вопрос, не относящийся к этому топику?

Есть таблица, rowsize 5498. Она находится в dbspce, у которой pagesize равна 4096. Как будут 2 строки заполнять страницу. Одна полная и остаток + одна полная и остаток
или
две полных и остатки заполнят одну страницу?
Извините за назойливость.

авторСергей Б

Первое:
раньше вы тоже пробовали запускать запрос с фильтром за целый день и больше? Если да, использовали ли вы раньше фильтр за последние дни?


запрос запускается только за день, даты в основном (95%) предыдущий месяц. Запросов может быть 1-31

А остальные 5%? Можете расшифровать смысл реквизитов запроса и результата запроса/запросов? [/quote]

Смысл таков.
Если между организациями не идут остатки, то по запросу создается отчет, в который входят все документы с датой исполнения с .. по .. за каждый день. В шапку отчета включается дата min(reg_date). По поводу дат, reg_date - дата регистрации, reg_date_dt дата исполнения. Почти всегда эти даты равны, но могут иметь расхождения. Например: пришла транзакция, по которой необходимо проверить корректность реквизитов. Для этого делается запрос на сервер, который гарантирует корректность (еще одна организация). А этот сервер «лежит/выстроилась большая очередь».


автор Уточните,
- HIGH вручную вы запустили ДО первых проблем с запросом или после?
- зачем запускали?

Update statistics запускал после появления проблемы.
Т.к. Update statistics по этой таблице запускается раз в неделю в режиме medium, решил пересобрать статистику (а вдруг из-за этого)


автор2. При выполнении запроса поначалу можно его запускать с хинтом
{+EXPLAIN, AVOID_EXECUTE} (см. тут). План запроса будет выбран, но сам запрос не будет запущен. Очень экономит время при проверке гипотез "а как поведёт себя вот такой запрос в реальности" :).

Спасибо. Про это не знал.


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

Я выясняю причину случившегося ТОЛЬКО ДЛЯ СЕБЯ (кстати, от Ваших ответов я много нового для себя открыл). А приложение давно подправлено и работает быстро.




авторПятое: не поменялся ли заметно объём БД с того момента, как "раньше оно так и работало".

Сама БД растет (с начала года на 4 Гб), но таблица по которой делается выборка, практически не меняется. С одним но. Данные вставляются сотнями тысяч и сотнями тысяч удаляются. Кол-во строк на любой день может как прибавиться, так и убавиться.

Это существенное уточнение. Я так понимаю, такое удаление и вставка происходит разы в день.
А статистика ПОСЛЕ таких вставок и удалений обновляется сразу?
И какие характеристики пакетов на удаление и вставку? А-ля, "перезалей все записи у которых reg_paym_dt = сегодня или вчера?

Данные вставляются в эту таблицу постоянно с периодичностью 5 минут. Ночью запускается процедура, которая просматривает записи на сервере А по такой же таблице за прошедший день и если такая запись есть на сервере «Б» (это наш случай), то делает апдейт всей строки. Если строки нет, делает вставку.
Удаление. Выбираются данные за один день - «текущий день» минус энное количество дней. Данные копируются на сервер «В», а скопированные строки удаляются. Статистика по этой таблице выполняется один раз в неделю в режиме МЕДИУМ.



авторИ теперь всё-таки по поводу

- как сделать, чтобы работало хорошо.
Итак, у вас есть 95% запросов, для которых из имеющихся индексов индекс по (reg_paym_dt) - идеал.
Используйте сознательно хинт для этих 95% - и не парьтесь. :)

Но если:
- есть возможность добавлять индексы;
- есть осознание, что новый индекс сам по себе доп.нагрузка, но минусов от добавления конкретного индекса меньше чем плюсов;
- есть желание узнать новое и доскональнее попрактиковаться в вопросах оптимизации,
то предлагаю следующие варианты;
+ Варианты А. Используем знания, про которые мы знаем, что вы знаете :)



+ Варианты Б. Используем знания, про которые мы НЕ знаем, знаете ли вы :)




Варианты B против вариантов А улучшит реальное выполнение приблизительно в (8+8)/(4+8)=1,3 раза за счёт размера ключа индекса.

Кроме того, если поля recv_code и/или pm_state добавляют селективности запросам (заметно уменьшают количество строк в массиве X), можно было бы и их добавить в индексы...

Про вариант Б. Его я не знал, спасибо.
Вариант А1 не помог.
Вариант А2 и Б - пробую
АнатоЛой, а есть предположения, почему все так стало?