|
Целью данной статьи является попытка
разобраться как работает замечательный
механизм автоматического управления PGA
памятью для серверных процессов в случае
dedicated режима. Версия Oracle 9.2, OS Solaris. Оставить
комментарии можно здесь
Этот механизм был введен в 9i. Понятно
почему - память, которую захватил серверный
процесс в процессе свой работы обратно не
возвращается в ОС до завершения сессии (см. здесь).
Таким образом, если у нас много сессий, даже
если в данный момент не выполняют
сортировок но они были раньше, памяти в ОС
нет. Поэтому же и выставление большого
значения для параметров sort_area_size (скажем в
10mb) на уровне экземпляра было опасно -
мог начаться swapping.
Дальше больше. Совсем не просто
подсчитать сколько памяти занимают
серверные процессы в данный момент (см. здесь). Т.е.
реально оценить можно ли увеличивать _area_size
параметры или нет.
Теперь стоит Вам выставить два параметра,
pga_aggregate_target (может меняться от 10mb до 400gb) и
workarea_size_policy (auto) как больше беспокоиться ни о
чем не надо. Oracle сделает все за Вас. Ну
почти все. Вот мы и посмотрим что и как он
сделает.
Рассмотрим, что же он сделает. Для начала
определимся с терминами: PGA - Program Global Area
находится в приватной памяти процесса. Она
содержит глобальные переменные, структуры
данных и управляющую информацию серверного
процесса. Когда исполняется очередной
курсор, новая область создается в PGA
серверного процесса.
Раз теперь PGA память общая то она где-то
должна находится. Мне не удалось найти
никаких упоминаний физической реализации.
Поэтому предположения: вроде бы
единственные механизм общей памяти у Oracle
была shared_memory. Серверный процесс и так к ней
присоединен, что можно видеть с помощью pmap -x.
Тогда можно предположить что теперь он
получает память не сам выделяя ее с помощью
системных вызовов malloc, а запрашивая
свободную память в shared_memory. Какай-то фоновый
процесс (?) обеспечивает выделение новых
объемов shared_memory все в том же сегменте что и
SGA.
Посмотрим как это работает со стороны ОС.
Конфигурация такова:
Физической памяти - 4 Гб.
pga_aggregate_target 524 288 000
sga_max_size 1 561 417 960
workarea_size_policy = auto
В момент времени между 6 и 7 часами
запускает batch процесс, пожирающий много
памяти. Наблюдаем за
SELECT * FROM V$PGASTAT;
во времени. На графике Oracle PGA Usage видно, что
процесс попросил около 50Mb (total PGA in use), в
ответ на что, Oracle запросил у ОС примерно 250Mb.
(единицы измерения на графике - байты).
В тоже время операционная система была
вынуждена отдать те же 250Mb (см. до 6:30) потом
еще 250Mb (еденицы измерения на графике kb)
Первый вывод который можно сделать:
память захватывается у ОС с упреждением.
Долгое чтение документации привело только
к фразе в Note:147806.1
Pitfall: The automatic tuning can often waste PGA memory because more memory is
allocated than needed.
Что на мой взгляд и видно на картинке.
Второй вывод, память возвращается в ОС,
что очень важно для нас.
Видимо, пока не достигнута граница maximum PGA
allocated идет захват памяти на 'всякий случай'.
Надо заметить, что сама граница maximum PGA
allocated не жесткая. Декларируется, что если
потребуется - будет захвачено и больше. Верю.
Есть еще занятное ограничение. Память
выделяема для одного SQL оператора
ограничена формулой min(5% PGA_AGGREGATE_TARGET, 100MB) для
обычных операций и формулой 30% PGA_AGGREGATE_TARGET/DOP
для параллельных операций (DOP=степень
параллелизма).
Теперь, как же оценить насколько
оптимально настроена PGA ?
Документация и Note:223730.1 дают нам следующие
формулы:
- Для OLTP систем
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 20%
- For DSS systems
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 50%
При моих 4Gb для OLTP это дает (4*100/80) *20/100 = 640 mb.
У меня, как написано выше стоит чуть меньше.
Посмотрим кто прав.
Возьмем statspack, раздел PGA Memory Advisory
Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
63 0.1 122,152.1 201,061.7 38.0 12
125 0.3 122,152.1 136,644.7 47.0 5
250 0.5 122,152.1 122,653.6 50.0 5
375 0.8 122,152.1 114,467.5 52.0 0
500 1.0 122,152.1 111,755.5 52.0 0
600 1.2 122,152.1 48,577.9 72.0 0
700 1.4 122,152.1 47,452.2 72.0 0
800 1.6 122,152.1 46,093.1 73.0 0
900 1.8 122,152.1 45,292.4 73.0 0
1,000 2.0 122,152.1 44,214.2 73.0 0
1,500 3.0 122,152.1 38,329.0 76.0 0
2,000 4.0 122,152.1 35,853.5 77.0 0
3,000 6.0 122,152.1 35,853.5 77.0 0
4,000 8.0 122,152.1 35,853.5 77.0 0
-------------------------------------------------------------
Видно, что PGA Cache Hit резко увеличивается
после установки pga_aggregate_target = 600 mb. Далее,
увеличение не такое сильно. Итого, стоит
использовать значение чуть больше 600 Mb.
Следующая картинка дает нам
представление о том, как исполнялись
сортировки (и т.п.). Все запросы на
использование памяти, разбиты на участки (в
зависимости от величины запроса). Так
запросы величиной от 64 до 128кб выполнялись
492 раза из них в 486 случаях сразу хватило
памяти (optimal execeution), 6 раз потребовался
дополнительный проход по данным. Запросы
величиной от 32 до 64 mb выполнялись 123 раза, из
них 9 раз они поместились в память, 108 раз
потребовался дополнительный проход по
данным, 6 раз таким проходов было много (multi-pass
execution). Я не нашел подтверждения или
опровержения, что one-pass и multi-pass проходы
требуют работы с диском. Но наверно это так.
PGA Aggr Target Histogram for DB: OLAP9 Instance: OLAP9 Snaps: 1 -98
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
16K 32K 184,412 184,412 0 0
32K 64K 1,564 1,564 0 0
64K 128K 492 486 6 0
128K 256K 483 483 0 0
256K 512K 224 224 0 0
512K 1024K 22,596 22,595 1 0
1M 2M 360 358 2 0
2M 4M 258 256 2 0
4M 8M 282 254 28 0
8M 16M 205 200 5 0
16M 32M 106 47 59 0
32M 64M 123 9 108 6
64M 128M 44 1 39 4
128M 256M 13 0 13 0
256M 512M 14 0 2 12
512M 1024M 3 0 1 2
-------------------------------------------------------------
Полное объяснение см. Note 223730.1
Заключение
Как мне кажется, что автоматическое
управление памятью, как и все
автоматическое, неплохо справляется с
большей частью рутинной работы. И конечно в
момент сортировки на 1gb это работает не
эффективно. Поэтому, мне кажется, что для
сессий, которые явно выполняют очень
большие сортировки следует выставлять
WORKAREA_SIZE_POLICY в manual на уровне сессии и
дальше задавать *_area_size вручную. Надеюсь, что
у Вас таких запросов не так много.
Ссылки:
- Документация: Как
конфигурировать Автоматическое
управление памятью
- Oracle
9i Automatic PGA Memory Management
- Monitoring
PGA Memory Usage
- Activating,
Monitoring, and Tuning Automatic PGA Memory Management via
PGA_AGGREGATE_TARGET Under Oracle 9i
|
