Автор: Киселев Андрей
Тема: Особенности размещения SQL WORK AREAS в памяти при использовании режима SHARED SERVERS в ORACLE 10-11g
Источник: Russian Oracle User Group
Номер документа: 5.1
Дата публикации: 15.06.2009
Последнее изменение: 15.06.2009

О важности буковок или в поисках SQL Work Area.

В этой заметке хочется рассмотреть некоторые аспекты размещения компонент “Private SQL area“ в режиме “SHARED SERVER“ и изменения, произошедшие с ними начиная с версии Oracle 10g. Согласно документации “Private SQL area“ состоит из двух частей.
Фиксированная область, - освобождается только после закрытия курсора и содержит постоянные данные курсоров, ссылку на используемый план запроса, переменные связывания и т.п.
Область времени выполнения (run-time), - содержит информацию для текущего выполнения запроса, в том числе наиболее интересные с точки зрения возможных затрат памяти “SQL work area“ (области сортировки, hash-таблицы и т.п. ).

C “глубокой древности” интересующимся известно, что при использовании “SHARED SERVER“ или как он назывался ранее “Multy Thread Server“ большая часть информации “Private SQL area”, в том числе run-time области памяти находятся в области UGA размещаемой в SGA. Это вполне естественно, поскольку указанная информация должна храниться между вызовами (call), а отдельные вызовы (parse, execute, fetch, …), при работе в режиме “SHARED SERVER“, могут обрабатываться разными обслуживающими процессами Oracle и соответственно она не может храниться в области PGA обрабатывающих процессов, а должна быть в памяти разделяемой между процессами.

Такая картина мира крепко засела в головах (особенно в моей) и широко распространена в литературе по Oracle и материалах для сертификации, в том числе для версий 10g и 11g. Ниже приведены выдержки из довольно популярных книг посвященных подготовке к экзаменам OCP по 10g и 11g:

До версии 10g для “SHARED SERVER“ размеры “SQL work areas” управлялись только в “ручном” режиме при помощи настроечных параметров *_area_size, а автоматическое управление памятью PGA (Automatic PGA Memory Management) действовало только для режима “DEDICATED SERVER“. Начиная с версии 10g нам сообщили, что автоматическое управление памятью PGA распространяется и на “SHARED SERVER“ и используемые им “SQL work areas”

Распространение автоматического управления памятью и параметра PGA_AGGREGATE_TARGET на “ SHARED SERVER“ вызвал у меня некоторый дискомфорт в понимании механизмов Oracle введенных в 10g, поскольку управление размером областей находящихся в SGA через параметр, отвечающий за суммарный желаемый размер PGA областей, выглядит странно и неестественно.

После обсуждений и попыток разъяснения коллегам готовящимися к сдаче экзаменов OCP 11 g, что и где хранится, в разных режимах работы сервера, я наконец понял, что надо все таки попытаться устранить нестыковки и в собственной голове. Лень заставила меня начать с простейшего, я стал искать в сети, нет ли готовых разъяснений по моим сомнениям от людей уже победивших лень. В результате поисков я наткнулся на заметку Александра Фаткулина, посвященную изменению поведения Oracle в режиме “Shared Server“ после перехода c 9i на 10g (http://www.pythian.com/news/524/shared-servers-and-automatic-workarea-management#more-524).

В своей статье Александр показывает, что при включенном автоматическом управлении памятью, если для обработки запроса необходимо выделение “sql work area“ (SORT,HASH и т.п.), Oracle удерживает обслуживающий shared процесс за сессией выполняющей курсор, до тех пор пока курсор не будет прочитан до конца или закрыт. В результате Oracle 10g может потребовать конфигурации значительно большего числа shared servers, чем 9i, для удержания той же нагрузки. При переходе к ручному управлению памятью рабочих областей Oracle 10g возвращается к поведению 9i, т.е. обрабатывающий сервер возвращается в пул после окончания текущего вызова.

Наиболее вероятной причиной такого поведения, на мой взгляд, является хранение в PGA обслуживающего процесса данных, необходимых для обработки курсора и имеющих срок жизни до завершения исполнения запроса .

После сопоставления этих 2 фактов:
- управление памятью рабочих областей через PGA_AGGREGATE_TARGET в режиме “SHARED SERVER“;
- необходимость удерживать информацию, хранящуюся в области PGA обрабатывающего процесса до окончания выполнения запроса;
напрашивается вывод, что данными необходимыми для обработки запроса является run-time область курсора, вернее её части “sql work areas” , которые имеют как раз искомое время жизни и не переезжают в SGA как ранее, а хранятся в PGA обслуживающего процесса и поэтому совершенно естественным образом управляются через PGA_AGGREGATE_TARGET.

Теперь хочется сделать то, что надо было сделать изначально, обратиться к документации. Что же она нам говорит по данному вопросу? Собственно фрагмент описывающий область хранения “private SQL area” не изменился при переходе от 9i к 10g и далее к 11g: “if a session is connected through a shared server, part of the private SQL area is kept in the SGA”.

Часть интересующей нас области переходит в SGA. Под это описание подходят оба рассматриваемых варианта, разница в том, какая именно часть “private SQL area” будет содержаться в SGA.

Но если чуть внимательнее сопоставить документацию 9i и 10g можно заметить появившуюся разницу в таблицах описывающих различия распределения памяти в DEDICATED и SHARED режиме:

9i:

10g:

Видно, что для 10g изменилось место хранения “part of the runtime area for Select statements”, она теперь действительно находится в PGA. В документации все по честному, надо было просто внимательно прочитать и обратить внимание на изменение хотя и одной, но очень важной буковки! :)

Некоторые выводы из вышеизложенного (рассматривается работа в режиме “SHARED SERVER“):
1) При работе в режиме автоматического управления памятью PGA в 10g “SQL work area“ хранятся в PGA, а не SGA, это надо учитывать при распределении памяти и разборе случаев с нехваткой памяти.
2) При автоматическом управлением памятью PGA на 10g может потребоваться большее число обслуживающих процессов чем на более ранних версиях или надо использовать ручной режим управления памятью.
3) Читаем документацию внимательно, а не как я!
4) Если вам предстоит сдавать экзамены OCA, OCP стоит забыть, что я тут написал и придерживаться мнения, что для “shared server“ часть “private sql area“ переезжающая в SGA включает все что находилось в PGA за исключением пространства стека ( “includes all of what would have been in a PGA with the exception of the session’s stack space”).