Для начала устроим небольшую проверку.

Вопрос: какой размер памяти резервирует каждый процесс Oracle для сортировки данных ?

A.   sort_area_size
B.   sort_area_retained_size
C.   sort_area_size + sort_area_retained_size
D.   Нисколько не резервирует
E.   Ничто из предлагаемых ответов

Наиболее частый ответ – A, хотя B тоже встречается часто. Некоторые умные люди выбирают C, однако правильный ответ – D ! Утверждение о том, что каждому процессу для работы требуется особая память размером в sort_area_size – миф. Пока процесс не начнет первую сортировку, он не резервирует ни байта памяти. Миф также то, что после каждой сортировки у процесса остается зарезервировано для последующих сортировок пространство размером sort_area_retained_size. Для последующих сортировок процесс ничего не резервирует.

Области для сортировок выделяются динамически в пространстве UGA. По мере необходимости они увеличиваются маленькими приращениями, пока не достигнут совокупного размера sort_area_size. Параметр sort_area_retained_size определяет количество памяти, которая будет зарезервирована для выполнения окончательной фазы слияния при выполнении сортировки с участием диска. Как только последняя строка ответа вычитана, вся память сортировки немедленно возвращается в кучу UGA, а большая ее часть следом и сразу же возвращается в кучу основного (родительского) процесса (в кучу PGA или, в случае MTS-соединения, SGA). Освободившаяся/неиспользуемая память PGA быстро попадет в распоряжение ОС в результате активности процедур работы с памятью компьютера. Итак, максимальное количество памяти, потребной экземпляру СУБД Oracle для сортировки, будет всего sort_area_size, помноженное на примерное число одновременно выполняющихся сортировок. То есть специально для сортировок никакой памяти в Oracle заранее не “отъедается”.

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

Многие администраторы ведут себя весьма осторожно с выставлением параметра sort_area_size, ориентируясь в основном на умеренные дисковые сортировки, по одной простой причине: они боятся, что большое значение этого параметра приведет к перерасходу памяти. На самом же деле рост требований к физической памяти определяется ростом значения этого параметра, помноженным на пиковое число одновременных дисковых сортировок. Это пиковое число одновременных сортировок, когда-либо выполнявшихся за время работы экземпляра СУБД, можно посмотреть в поле MAX_UTILIZATION для ресурса sort_segment_locks в таблице V$RESOURCE_LIMIT (или же для ресурса temporary_table_locks, если речь идет о сохраняемом временном, “permanent temporary”, табличном пространстве). Сценарий disk_sorts.sql фирмы Ixora позволяет получить число дисковых сортировок, их средний размер и пиковое число одновременно выполнявшихся дисковых сортировок. Эти значения во многом могут обосновать вашу корректировку параметра sort_area_size. Вот пример:

DISK_SORTS AVERAGE_SIZE PEAK_CONCURRENT

---------- ------------ ---------------
       232        4914K               2

Здесь экземпляр СУБД за время работы выполнил 232 дисковые сортировки со средним размером сортируемых массивов чуть меньше 5 Мбайт, причем в этих случаях имелась по крайней мере двукратная пиковая одновременная активность. Теперь если администратор решит увеличить sort_area_size с 1Мб до 5Мб, то он добъется экономии времени ввода/вывода 1,1 Гб данных за счет перерасхода почти 8 Мб ОЗУ на время сортировки. (Учтите, что увеличение sort_area_size практически не дает выигрыша на больших сортировках. Целью является сокращение числа умеренных дисковых сортировок).