Кратко о сортировке в оракле прочитайте здесь. А теперь о параметрах инициализации влияющих на сортировку.

SORT_AREA_SIZE

Этот параметр определяет размер памяти, выделяемый каждому пользовательскому процессу на сортировку. Память выделяется процессу только тогда, когда возникает потребность в сортировке (!), а не при рождении процесса. Область сортировки выделяется динамически,порциями, пока не достигнет совокупного размера sort_area_size. Если же этого не достаточно, то используется temporary tablespace (дисковая сортировка). Таким образом, максимальный объем памяти, используемый ораклом на сортировки, будет составлять sort_area_size* число одновременно выполняющихся сортировок. То есть, если нет сортировок – никакая память не резервируется. Когда сортировка завершена, а строки еще не возвращены, область сортировки освобождается, кроме размера равного SORT_AREA_RETAINED_SIZE. Когда последняя строка возвращена, освобождается и этот кусок памяти. То есть, для сеанса ничего не резервируется на потом. В любой момент времени для каждого процесса пользователя существует только одна область памяти, которая определяется sort_area_size. Минимальное значение этого параметра - шесть блоков базы данных. Значение по умолчанию – 65536.

SORT_AREA_RETAINED_SIZE

Задает максимальный размер памяти, динамически запрашиваемой у UGA на завершающую фазу (слияние) процедуры сортировки, выполняемой с привлечением дисковой памяти. По завершению процедуры сортировки эта память возвращается UGA (а не операционной системе) и для последующих сортировок процесс ничего из этой памяти не оставляет.

«Нужно, правда, учесть, что число одновременно выполняемых сортировок может оказаться выше числа активных сеансов работы с БД, так как план отработки некоторых запросов может быть составлен так, что одновременно будут активны сразу две сортировки: одна – из фазы слияния, выдающая строки из своего источника строк и вторая – из фазы сортировки, принимающая строки для своего источника строк. То есть, максимально потребный отдельному сеансу размер памяти для сортировки может оказаться равным sort_area_size + sort_area_retained_size» - цитата из статьи .

Значение SORT_AREA_RETAINED_SIZE должен принимать значение равное величине двух блоков базы данных и более, но не превышать значение SORT_AREA_SIZE. По умолчанию sort_area_size_retained равно sort_area_size.

PGA_AGGREGATE_TARGET

Начиная с ORACLE9i, появился параметр PGA_AGGREGATE_TARGET (суммарная память PGA). В связи с этим ORACLE не рекомендует использовать параметры SORT_AREA_SIZE и SORT_AREA_RETAINED_SIZE, если ваша база данных сконфигурирована на режим DEDICATED. Теперь можно, включив автоматическое управление (workarea_size_policy=auto) доверить управление PGA самому ораклу. Все прочие параметры (*_area_size) управляющие размерами рабочих областей игнорируются. Корпорация Oracle рекомендует устанавливать значение параметра pga_aggregate_target, равное объему памяти, оставшейся свободной в сервере UNIX после запуска экземпляра (минус 20% на другие задачи ОС UNIX).

Сервер базы данных автоматически некоторым интеллектуальным способом распределяет эту память между различными активными запросами так, чтобы обеспечить максимальную производительность и наиболее эффективно использовать память. Кроме того, СУБД Oracle9i может сама адаптироваться к изменению рабочей нагрузки, таким образом, ресурсы используются эффективно независимо от нагрузки на систему. Объем памяти PGA, доступной экземпляру, можно изменять динамически, изменяя значение параметра PGA_AGGREGATE_TARGET. Работу по настройке параметра PGA_AGGREGATE_TARGET значительно упрощает представление – предсказатель V$PGA_TARGET_ADVICE.

Для баз данных, работающих в режиме разделяемых (shared) соединений по-прежнему действуют параметры sort_area_size, sort_area_retained_size и др., а pga_aggregate_target игнорируется.

Рекомендации по изменению значения pga_aggregate_target

Выполним запрос

select name, value from v$sysstat where name like 'workarea executions %'

Имеет смысл изменять значение параметра при условии:

• увеличить значение pga_aggregate_target можно, если значение статистики “workarea executions – multipass” (количество операций обработки в многопроходном режиме) превышает 1%,
• уменьшить значение pga_aggregate_target можно, если значение статистики “workarea executions – optimal” (количество операций обработки в оптимальном режиме) всегда равно 100%.

WORKAREA_SIZE_POLICY

Это параметр задает способ управления размерами рабочих областей. Может принимать значения AUTO или MANUAL. Если задан параметр PGA_AGGREGATE_TARGET, то WORKAREA_SIZE_POLICY по умолчанию принимает значение AUTO, в противном случае значение по умолчанию - MANUAL.

Если параметр установлен в AUTO, то размеры рабочих областей определяются автоматически (по какому-то внутреннему оракловскому алгоритму используя собранные статистические данные об использовании памяти экземпляром) исходя из значения PGA_AGGREGATE_TARGET, учитывая потребность конкретной операции. Очевидно, что AUTO возможно лишь при определении параметра PGA_AGGREGATE_TARGET.

Если параметр установлен в MANUAL, то рабочие области нужно определять вручную с помощью параметров *_area_size (SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE и т.д.)

Доверимся ораклу?