DS_NONPDQ_QUERY_MEM - Параметр впервые появился в версии 9.40.xC4. Позволяет настраивать объем памяти для сессии отведенные на сортировки (сессия не должна использовать PDQ). До этого под сортировки в памяти отводилось 128Кб на сессию, в результате чего если сортировка была больше, то она шла на диск. При большом числе сортировок в сессиях это не самым лучшим образом сказывается на производительности сервера. Память выделяется в виртуальном сегменте разделяемой памяти.
Для определения потребности в установке данного параметра выше 128Кб необходимо провести мониторинг сессий использующих сортировки в не-PDQ окружении. Чем больше сортировок на диске, тем выше вероятность того что необходимо использовать данный параметр, чтобы убрать сортировки с диска в память. Минимальное значение 128Кб, максимальное 25% от DS_TOTAL_MEMORY

Итак, ставим для сессии PDQPRIORITY=0 и начинаем эксперимент. Цель - убрать сортировки с диска в память настройкой параметра. Запросы были проведены несколько раз (в том числе в при разной последовательности изменения DS_NONPDQ_QUERY_MEM), чтобы проверить повторяемость результатов. Сервер тестовый, других сессий не запускалось.
Сервер IDS 10.00.UC4, параметр можно менять без перезагрузки.

В качестве экспериментальных были выбраны две таблицы к которым запускался запрос:

select t1.id, t1.f1, t2.f2 from
t1_nonpdq t1 left join t2_nonpdq t2 on t1.f1 = t2.f1
order by 1,2,3


Можно выбрать и любой другой запрос, который сортирует данные.

Для определения сортировок на диске, а также максимального дискового пространства для сортировок запускаем запрос к базе sysmaster


set isolation to dirty read;
select sid, seqscans, total_sorts, dsksorts, max_sortdiskspace from syssesprof where sid = sid_сессии из которой выполняется предыдущий запрос.


Значения, которые говорят о том насколько эффективно действует параметр DS_NONPDQ_QUERY_MEM

dsksorts - это значение позволит обнаружить куда ушла сортировка (на диск или в память если 0)
max_sortdiskspace - это значение показывает насколько большой была сортировка на диске

В процессе эксперимента меняется параметр DS_NONPDQ_QUERY_MEM (в онлайн с помощью onmode -wm ), после чего сессия с запросом вызывающим сортировку подключается заново, чтобы этот параметр на нее подействовал.

Замечание. При первом запуске запроса для экспериментальной сессии менялись только значения seqscans, total_sorts, а dsksorts и max_sortdiskspace оставались в нуле (непонятно почему? данные в syssesprof попадают не сразу?). При повторном запуске эти значения становились такими как показано ниже.
При этом значения seqscans, total_sorts, dsksorts накапливаются для сессии при каждом последующем запросе, а параметр max_sortdiskspace не накапливается, он показывает объем на диске для сортировки.


select t1.id, t1.f1, t2.f2 from
t1_nonpdq t1 left join t2_nonpdq t2 on t1.f1 = t2.f1
order by 1,2,3



DS_NONPDQ_QUERY_MEM sid seqscans total_sorts dsksorts max_sortdiskspace comment128 KB145311120256 KB16631686512 KB18531672 <<< Ура!!!768 KB20531700 <<< ??? снова больше на диске1024 KB31531952 <<< ???1280 KB3353112041536 KB3553 0 0 <<< YES!!! Теперь сортировка целиком проводится в памяти 2048 KB375300 <<< YES!!! Теперь сортировка целиком проводится в памяти


Какая то странная зависимость получается. Сначала объем сортировок на диске падает, а затем вновь растет, чтобы при определенном значении полностью перейти в память. Я то считал что будет почти линейная зависимость.

Можно также посмотреть число записей на диск для временного пространства

сбросил внутреннюю статистику чтобы можно было сравнить без вычитаний onstat -D

запускаю сессию с запросом
DS_NONPDQ_QUERY_MEM: 128 KB

40233870 2 2 0 3153 3865 /mnt/inf_slr_tst/infx10/data/tempdbs

поменял DS_NONPDQ, снова сбросил статистику, запустил сессию с запросом
DS_NONPDQ_QUERY_MEM: 2048 KB

40233870 2 2 0 717 668 /mnt/inf_slr_tst/infx10/data/tempdbs

Как видно из вывода onstat -D число чтений/записей резко упало (чтений в 4 раза, записей почти в 6 раз). Результат воспроизводится.

Память для DS_NONPDQ_QUERY_MEM выделяется динамически как и некоторые другие пулы в виртуальном сегменте. Это значит что пока не потребуется большая сортировка, пул для сессии выделен не будет. Пул выделяется для каждой сессии требующей большой сортировки.
Все это можно посмотреть (с помощью той же onstat -g mem при запущенных сессиях с сортировками).

План запроса и стоимость посмотрю позже.
А насчет быстродействия это же очевидно: быстрее работает сортировка в памяти чем на дисках.
Померял время для запроса (проведено по три измерения, перед измерением проведен такой же запрос чтобы данные загрузились в буфера) с помощью time :

DS_NONPDQ_QUERY_MEM Среднее время выполнения

128Кб 4 сек

1536Кб 2. 5 сек

В системах с большим числом сортировок этот параметр вполне может дать существенный прирост производительности. Насчет линейной зависимости (теоретически предполагал что будет линейная) результаты как раз говорят что она нелинейная. Гарантию не дам :)
Опять же простой запрос с сортировкой по неск. полям (без join) выдал разницу по времени выполнения от 6 сек при 1536Кб до 17 сек при 128Кб. А если подобных запросов много?
Опять же темп юзают и в некоторых других случаях, в результате уменьшившаяся нагрузка на темп скажется в лучшую сторону и на других сессиях.