Экономно используя машинные ресурсы (включая память и процессор), Oracle может поддерживать десятки тысяч одновременно работающих пользователей базы данных. Технологии управления памятью Oracle в последние годы стали довольно сложными и освоить их бывает непросто. И хотя средства автоматизации, появившиеся в последних версиях, основательно упрощают работу администраторов баз данных, разработчики PL/SQL
должны понимать основные принципы управления памятью (особенно в том, что касается курсоров и пакетных переменных), чтобы избежать непроизводительных расходов памяти.
SGA, PGA и UGA
При взаимодействии клиентской программы (скажем, SQL*Plus
или SQL Developer
) с базой данных используются три структуры памяти.
SGA (System Global Area)
SGA
представляет собой группу взаимосвязанных структур памяти, или компонентов SGA
, содержащих данные и управляющую информацию по одному экземпляру Oracle Database
. SGA
совместно используется всеми серверными и фоновыми процессами. Примеры данных, хранящихся в SGA
, — кэшированные блоки данных и общие области SQL
.
PGA (Process Global Area)
PGA
— область памяти, содержащая данные и управляющую информацию, которые монопольно используются каждым серверным процессом. Oracle
Database
создает PGA
при запуске процесса Oracle
. Для каждого серверного и фонового процесса создается отдельная область PGA
. Совокупность отдельных областей PGA
называется областью PGA
экземпляра. В параметрах инициализации базы данных задается размер области PGA
экземпляра, но не отдельных PGA
.
UGA (User Global Area)
Данные, которые должны сохраняться между обращениями к базе данных в ходе сеанса (пакетные переменные, приватные области SQL
и т. д.), помещаются в UGA
(User Global Area
). По сути UGA
используется для сохранения состояния сеанса. Местонахождение UGA
в памяти зависит от выбранного способа подключения к базе данных:
- Выделенный сервер. Для каждого сеанса Oracle создает выделенный серверный процесс. Такая конфигурация целесообразна при больших нагрузках, например при интенсивных вычислениях или долго выполняющихся запросах к базе данных.
UGA
помещается вPGA
, потому что другим серверным процессам не нужно обращаться к этой области. - Общий сервер. Обращения к базе данных ставятся в очередь к группе общих серверных процессов, которые могут обслуживать обращения от любых сеансов. Такая конфигурация хорошо подходит для сотен параллельных сеансов, генерирующих короткие обращения с относительно большим временем бездействия.
UGA
размещается в областиSGA
, чтобы данные были доступны для любого из общих серверных процессов.
Общий объем PGA
существенно зависит от вида операций, выполняемых сервером для приложения. Например, для пакетов PL/SQL
, заполняющих большие коллекции, может потребоваться большой объем памяти UGA
.
Если приложение работает с общими серверами, пользовательским процессам приходится ждать своей очереди на обслуживание. Если ваш пользовательский процесс запускает долго выполняющиеся блоки PL/SQL
или команды SQL
, администратор базы данных должен либо настроить сервер с большим количеством теневых процессов, либо запустить эти сеансы на выделенном сервере.
А теперь давайте разберемся, как выглядит память с точки зрения выполняемой программы.
Курсоры и память Oracle
Возможно, вы уже написали сотни программ, которые объявляют и открывают курсоры, выбирают из них строки, а затем снова закрывают. Ни SQL
, ни PL/SQL
не могут работать без использования курсоров; многие операторы неявно выполняют рекурсивные вызовы, которые открывают дополнительные курсоры. А поскольку каждый курсор, явный или неявный, занимает память сервера базы данных, процесс оптимизации настройки Oracle
часто подразумевает и сокращение количества необходимых приложению курсоров.
Хотя этот раздел посвящен управлению памятью, помните, что это всего лишь один из аспектов оптимизации базы данных; возможно, вам удастся повысить общую производительность, увеличивая количество курсоров.
Oracle
ассоциирует курсоры с анонимными блоками PL/SQL
практически так же, как с командами SQL
. Например, при обработке первого вызова в текущем сеансе пользователя Oracle
открывает в памяти UGA
область («приватную» область SQL
), в которой размещается информация, специфическая для этого вызова.
При выполнении команды SQL
или блока PL/SQL
сервер сначала проверяет, нет ли в содержимом библиотечного кэша готового представления этого кода. Если такая общая область PL/SQL
будет найдена, исполняющее ядро связывает ее с приватной областью SQL
. Если такой области не существует, Oracle
выполняет разбор команды или блока. (Кроме того, Oracle
подготавливает и кэширует план выполнения анонимных блоков PL/SQL
, включающий вызов ядра PL/SQL
для интерпретации байт-кода.)
Oracle
интерпретирует простейшие блоки PL/SQL
(блоки, вызывающие подпрограммы, и блоки, не содержащие интегрированных команд SQL
), используя только ту память, которая выделена для главного курсора. Если программа содержит вызовы PL/SQL
или вызовы SQL
, Oracle
требуются дополнительные приватные области в памяти UGA
. PL/SQL
управляет ими от имени вашего приложения.
Мы подходим к важному факту, касающемуся работы с курсорами: существуют два способа закрытия курсора. Если это делается программно, то дальнейшее использование курсора в приложении без повторного открытия становится невозможным. Программное закрытие курсора выполняется командой CLOSE
:
CLOSE имя_курсора;
или при автоматическом закрытии неявного курсора. Однако PL/SQL
не сразу освобождает память, связанную с этим курсором, — курсор продолжает существовать как объект базы данных, чтобы избежать его обработки при возможном повторном открытии, как это часто случается. Заглянув в представление V$OPEN_CURSOR
, вы увидите, что выполнение операции CLOSE
не уменьшает количество открытых курсоров сеанса; в версии 11.1 также можно осуществить выборку из нового столбца CURSOR_TYPE
для получения дополнительной информации о курсоре.
PL/SQL
поддерживает собственный «сеансовый кэш курсоров», то есть сам решает, когда какой курсор необходимо освободить. Максимальное количество курсоров в том кэше задается инициализированным параметром OPEN_CURSORS
. Выбор курсора для освобождения памяти производится на основании алгоритма LRU
(Least Recently Used
— «дольше всех не использовавшийся»). Однако внутренний алгоритм PL/SQL
работает оптимально только в том случае, если все курсоры закрываются сразу же после завершения выборки данных. Итак, запомните: курсоры, явно открываемые в программе, следует явно закрывать сразу же после завершения использования.
Внутренний алгоритм работает оптимально только в том случае, если ваши программы закрывают курсоры сразу же после завершения выборки. Итак, запомните:
Если программа явно открывает курсор, всегда явно закрывайте его сразу же после завершения работы (но не ранее)!
У программиста имеется возможность вмешаться в стандартное поведение Oracle. Конечно, для закрытия всех курсоров сеанса можно завершить сам сеанс! Существуют и другие, менее радикальные способы:
- сброс состояния пакета (см. ниже «Большие коллекции в
PL/SQL
»); - низкоуровневое управление поведением курсоров с помощью пакета
DBMS_SQL
(правда, выигрыш от использования данного подхода может быть значительно меньше, чем потери от снижения производительности и усложнения программирования).
Советы по экономии памяти
Разобравшись с теорией, мы переходим к практическим советам, которыми вы сможете воспользоваться в повседневной работе. Также обратите внимание на более общие рекомендации по оптимизации программ (или общие советы по оптимизации кода PL/SQL здесь). Кроме того, полезно иметь возможность измерить объем памяти, используемой сеансом в любой момент времени, из кода приложения. Для этого можно выдать запрос к различным представлениям V$
. Пакет plsql_memory
(см. файл plsql_memory.pkg
на сайте книги) поможет вам в этом.
Совместное использование команд
База данных способна предоставить программам совместный доступ к откомпилированным версиям команд SQL
и анонимным блокам даже в том случае, если они получены от разных сеансов и разных пользователей. Оптимизатор определяет план выполнения во время разбора, поэтому факторы, влияющие на разбор (в том числе и настройки оптимизатора), повлияют на совместное использование команд SQL
. Чтобы система могла обеспечить совместный доступ к командам SQL
, необходимо придерживаться нескольких основных правил:
- Значения переменных должны задаваться посредством переменных привязки, а не в виде литералов, чтобы текст инструкций оставался неизменным. Сами переменные привязки должны иметь соответствующие имена и типы данных.
- Правила регистра символов и форматирования в исходном коде должны точно совпадать. Если вы выполняете одни и те же программы, это произойдет автоматически. «Одноразовые» команды могут не совпадать с командами из программ на 100%.
- Ссылки на объекты базы данных должны разрешаться как ссылки на один и тот же объект.
- Для
SQL
параметры базы данных, влияющие на работу оптимизатора запросовSQL
, должны совпадать. Например, в вызывающих сеансах необходимо задавать одинаковый критерий оптимизации (ALL_ROWS
илиFIRST_ROWS
). - Вызывающие сеансы должны поддерживать одни и те же национальные языки (
National Language Support
,NLS
).
Мы не станем останавливаться на двух последних правилах; конкретные причины, препятствующие совместному использованию команд SQL
, можно найти в представлении V$SQL_SHARED_CURSOR
. Сейчас нас интересует в первую очередь влияние первых трех правил для программ PL/SQL
.
Первое правило (привязка) настолько критично, что ему посвящен отдельный подраздел. Второе правило (регистр и форматирование) является хорошо известным условием совместного использования инструкций. Текст должен точно совпадать, потому что база данных вычисляет по нему хеш-код для поиска и блокировки объекта в кэше библиотек. Несмотря на то что PL/SQL
обычно игнорирует регистр, следующие три блока не воспринимаются как одинаковые:
BEGIN NULL; END;
begin null; end;
BEGIN NULL; END;
Для них генерируются разные хеш-коды, поэтому инструкции считаются разными — на логическом уровне они эквиваленты, но на физическом уровне они различаются. Однако если все ваши анонимные блоки коротки, а все «настоящие программы» реализованы в виде хранимого кода (например, в виде пакетов), вероятность ошибочно подавить их совместное использование значительно меньше.
Реализуйте код SQL
и PL/SQL
в виде хранимых программ. Анонимные блоки должны быть как можно короче, в общем случае — состоять из единственного вызова хранимой программы. Кроме того, можно дать еще одну рекомендацию, относящуюся к SQL
: чтобы обеспечить возможность совместного использования SQL
-инструкций, помещайте их в программы, которые вызываются из разных мест приложения. Это избавит вас от необходимости по нескольку раз писать одни и те же инструкции.
Третье правило гласит, что внешние ссылки (на таблицы, процедуры и т. д.) должны разрешаться как ссылки на один и тот же объект. Допустим, мы с пользователем Scott
подключились к Oracle
, и оба запустили такой блок:
BEGIN
XYZ;
END;
Решение Oracle
о том, следует ли вам обоим предоставить возможность использования кэшируемой формы данного блока, зависит от того, ссылается ли имя «xyz» на одну и ту же хранимую процедуру. Если пользователь Scott
определил синоним xyz, указывающий на нашу копию процедуры, то Oracle
разрешит совместный доступ к анонимному блоку. При наличии независимых копий процедуры каждый будет работать со своим блоком. И даже если обе копии процедуры xyz будут абсолютно идентичны, Oracle
будет кэшировать их как разные объекты. Аналогичным образом как разные объекты кэшируются идентичные триггеры разных таблиц. Из сказанного можно сделать вывод: избегайте создания одинаковых копий таблиц и программ под разными учетными записями. Согласно общепринятому мнению, для экономии памяти нужно отделить программный код, общий для нескольких программ (и в особенности триггеров), и реализовать его в отдельном вызове. Иначе говоря, одна учетная запись базы данных назначается владельцем программ PL/SQL
, а другим пользователям, которым эти программы нужны, предоставляются привилегии EXECUTE
. Хотя эта практика очень хорошо влияет на удобство сопровождения, вряд ли она обеспечит реальную экономию памяти. Более того, для каждой вызывающей стороны создается новый объект с дополнительными затратами памяти в несколько килобайтов за сеанс. Конечно, значительные затраты памяти будут наблюдаться только при очень большом количестве пользователей.
У устоявшихся стереотипов есть еще одна проблема, проявляющаяся в среде с множеством пользователей, одновременно выполняющих одну программу PL/SQL
. При вызове общего кода необходим механизм установления и снятия блокировки библиотечного кэша, который может привести к задержкам. В таких случаях дублирование кода может быть предпочтительным, потому что оно предотвращает установление лишних блокировок и сокращает риск снижения производительности.
Но давайте вернемся к первому правилу, касающемуся переменных привязки.
Переменные привязки
В среде Oracle
переменной привязки называется входная переменная в команде, значение которой передается из среды вызывающей стороны. Переменные привязки играют особенно важную роль в совместном использовании команд SQL
независимо от источника инструкций: PL/SQL
, Java
, SQL*Plus
или OCI
. Переменные привязки упрощают масштабирование приложений, помогают бороться с внедрением кода (code injection
) и способствуют совместному использованию команд SQL
.
Чтобы две инструкции считались идентичными, использованные в них переменные привязки должны совпадать по имени, типу данных и максимальной длине. Например, следующие две инструкции идентичными не считаются:
SELECT col FROM tab1 WHERE col = :bind1;
SELECT col FROM tab1 WHERE col = :bind_1;
Однако это требование относится лишь к тексту инструкции, воспринимаемому ядром SQL
. Как упоминалось ранее, PL/SQL
переформулирует статические инструкции SQL
еще до того, как SQL
их увидит. Пример:
FUNCTION plsql_bookcount (author IN VARCHAR2)
RETURN NUMBER
IS
title_pattern VARCHAR2(10) := '%PL/SQL%';
l_count NUMBER;
BEGIN
SELECT COUNT(*) INTO l_count
FROM books
WHERE title LIKE title_pattern
AND author = plsql_bookcount.author;
RETURN l_count;
END;
После выполнения plsql_bookcount
представление V$SQLAREA
в Oraclellg
показывает, что PL/SQL
переформулировал запрос в следующем виде:
SELECT COUNT(*) FROM BOOKS WHERE TITLE LIKE :B2 AND AUTHOR = :B1
Параметр author
и локальная переменная titlepattern
заменены переменными привязки :B1 и :B2. Таким образом, в статическом коде SQL
вам не нужно беспокоиться о соответствии имен переменных привязки; PL/SQL
заменяет имя переменной сгенерированным именем переменной привязки.
Автоматическое введение переменных привязки в PL/SQL
распространяется на переменные, используемые в предложениях WHERE
и VALUES
статических инструкций INSERT
, UPDATE
, MERGE
, DELETE
и, конечно, SELECT
.
Дополнительные эксперименты показали, что изменение максимальной длины переменной PL/SQL
не приводило к появлению дополнительной инструкции в области SQL
, но с изменением типа данных переменной такая инструкция появляется. Впрочем, я не прошу верить мне на слово; при наличии необходимых привилегий вы можете провести собственные эксперименты и определить, действительно ли команды SQL
совместно используются в соответствии с вашими планами. Загляните в представление V$SQLAREA
. Результат выборки для приведенного выше кода:
SQL> SELECT executions, sql_text
2 FROM v$sqlarea
3 WHERE sql_text like 'SELECT COUNT(*) FROM BOOKS%'
EXECUTIONS SQL_TEXT
---------- --------------------------------------------------
1 SELECT COUNT(*) FROM BOOKS WHERE TITLE LIKE :B2
AND AUTHOR = :B1
Если PL/SQL
настолько умен, значит, можно не беспокоиться о переменных привязки? Не торопитесь: хотя PL/SQL
автоматически осуществляет привязку переменных в статических SQL
-инструкциях, эта возможность недоступна в динамическом SQL
. Неаккуратное программирование легко приведет к формированию команд с литералами. Пример:
FUNCTION count_recent_records (tablename_in IN VARCHAR2,
since_in IN VARCHAR2)
RETURN PLS_INTEGER
AS
l_count PLS_INTEGER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '
|| DBMS_ASSERT.SIMPLE_SQL_NAME(tablename_in)
|| ' WHERE lastupdate > TO_DATE('
|| DBMS_ASSERT.ENQUOTE_LITERAL(since_in)
|| ', ''YYYYMMDD'')'
INTO l_count;
RETURN l_count;
END;
При его выполнении динамически строятся команды следующего вида::
SELECT COUNT(*) FROM tabname WHERE lastupdate > TO_DATE('20090315','YYYYMMDD')
Повторные вызовы с разными аргументами since_in
могут привести к генерированию большого количества инструкций, которые вряд ли будут использоваться совместно:
SELECT COUNT(*) FROM tabname WHERE lastupdate > TO_DATE('20090105','YYYYMMDD')
SELECT COUNT(*) FROM tabname WHERE lastupdate > TO_DATE('20080704','YYYYMMDD')
SELECT COUNT(*) FROM tabname WHERE lastupdate > TO_DATE('20090101','YYYYMMDD')
Естественно, это приводит к весьма расточительному расходованию памяти и других серверных ресурсов.
ИСПОЛЬЗОВАНИЕ DBMS_ASSERT ДЛЯ БОРЬБЫ С ВНЕДРЕНИЕМ КОДА
Что это за вызовы DBMS_ASSERT
в примере с переменными привязки? Динамический SQL
, который использует данные, непосредственно вводимые пользователем, следует проверить, прежде чем без оглядки выполнять их. Вызов DBMS_ASSERT
помогает убедиться в том, что код получает именно те данные, которые он рассчитывает получить. Если попытаться вызвать функцию count_recent_records
для «странного» имени таблицы вида «books where 1=1;--»
, DBMS_ASSERT
выдаст исключение и остановит программу еще до того, как она успеет причинить вред. DBMS_ASSERT.SIMPLE_SQL_NAME
гарантирует, что входные данные соответствуют критериям действительного имени SQL. DBMS_ASSERT.ENQUOTE_LITERAL
заключает входные данные в кавычки и проверяет, что они не содержат встроенных кавычек. Полное описание DBMS_ASSERT
приведено в документации Oracle PL/SQL Packages and Types Reference
.
Если переписать эту же функцию с использованием переменной привязки, получится:
FUNCTION count_recent_records (tablename_in IN VARCHAR2,
since_in IN VARCHAR2)
RETURN PLS_INTEGER
AS
count_l PLS_INTEGER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '
|| DBMS_ASSERT.SIMPLE_SQL_NAME(tablename_in)
|| ' WHERE lastupdate > :thedate'
INTO count_l
USING TO_DATE(since_in,'YYYYMMDD');
RETURN count_l;
END;
Компилятор SQL
получит инструкции следующего вида:
SELECT COUNT(*) FROM tabname WHERE lastupdate > :thedate
Вторая версия не только проще и понятнее, но и обеспечивает значительно лучшую производительность при повторных вызовах с одинаковыми значениями аргумента tablename_in
при разных значениях since_in
.
Oracle
также поддерживает параметр инициализации CURS0R_SHARING
, который может предоставить некоторые преимущества в приложениях с большим объемом кода без переменных привязки. Присваивая этому параметру значение FORCE
или SIMILAR
, можно потребовать, чтобы база данных заменяла литералы SQL
(полностью или частично) переменными привязки, предотвращая затраты на разбор. К сожалению, это одна из тех возможностей, которые в теории работают лучше, чем на практике.
С другой стороны, если вы будете внимательно относиться к использованию полноценных переменных привязки в динамическом коде SQL
, ваши усилия будут вознаграждены во время выполнения (только не забудьте оставить параметру CURS0R_SHARING
значение по умолчанию EXACT
).
Даже если вам удастся добиться некоторого повышения производительности за счет использования CURSOR_SHARING
, рассматривайте этот способ как полумеру. По эффективности его даже нельзя сравнивать с полноценными переменными привязки, к тому же он может порождать ряд непредвиденных и нежелательных побочных эффектов. Если вам приходится использовать эту возможность из-за аномалий программных продуктов (часто сторонних), делайте это только до тех пор, пока не удастся модифицировать код для перехода на полноценные переменные привязки. Также учтите, что соответствующую настройку можно включить на уровне сеанса при помощи триггера LOGON
.
Пакеты и эффективное использование памяти
При извлечении байт-кода хранимой программы PL/SQL
читается вся программа. Речь идет не только о процедурах и функциях, но и о пакетах базы данных. Иначе говоря, нельзя сделать так, чтобы считывалась лишь часть пакета, — при обращении к любому элементу, даже к одной-единственной переменной, в библиотечный кэш загружается весь откомпилированный код пакета. Поэтому деление кода на меньшее количество пакетов большего размера приводит к меньшим затратам памяти (и дискового пространства), чем с множеством мелких пакетов. Таким образом, логическая группировка элементов пакета полезна не только с точки зрения архитектуры, но и с точки зрения производительности системы.
Поскольку Oracle
считывает в память весь пакет, объединять в пакеты следует только функционально связанные элементы, то есть такие, которые с большой вероятностью будут вызываться в одном сеансе.
Большие коллекции в PL/SQL
Совместное использование объектов — прекрасное решение, но допустимо оно далеко не для всех объектов программы. Даже если несколько пользователей выполняют одну и ту же программу, принадлежащую одной схеме Oracle, каждый сеанс имеет собственную область памяти, в которой содержатся специфические для этого вызова данные — значения локальных и пакетных переменных, константы и курсоры. И пытаться организовать совместное использование этих данных, относящихся к конкретному сеансу, бессмысленно. Наиболее типичные проблемы возникают при работе с коллекциями (о коллекциях подробно рассказывалось в этой статье). Предположим, ассоциативный массив PL/SQL
объявляется в программе следующим образом:
DECLARE
TYPE number_tab_t IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
number_tab number_tab_t;
empty_tab number_tab_t;
В массив включается большое количество элементов:
FOR i IN 1..100000
LOOP
number_tab(i) := i;
END LOOP;
Все эти элементы должны где-то храниться. По приведенным выше правилам, память для этого массива будет выделена в глобальной области пользователя UGA
, если он объявлен на уровне пакета, или в глобальной области вызова CGA
, если это данные анонимного блока, процедуры или функции верхнего уровня. В любом случае для работы с большой коллекцией потребуется очень большой объем памяти.
Возникает вопрос, как освободить эту память после завершения работы с коллекцией?
Нужно выполнить одну из двух команд:
number_tab.DELETE;
или
number_tab := empty_tab;
В любом из этих случаев Oracle
освободит память в своем списке свободных объектов. Это означает, что память для хранения переменной пакета будет возвращена в динамический пул состояния сеанса, а память переменной уровня вызова — в CGA
. То же произойдет и в том случае, если переменная, объявленная как коллекция, выйдет за пределы области видимости. Например, если коллекция будет объявлена в отдельной процедуре, Oracle
освободит занимаемую ею память сразу после завершения работы процедуры. В любом случае такая память не будет доступна ни другим сеансам, ни текущему сеансу, если ему понадобится память из области CGA
. Если в последующих операциях DML
будет, допустим, выполняться сортировка большого объема данных, приложению может потребоваться огромный объем памяти. И только после завершения сеанса память будет полностью освобождена и вернется в родительский динамический пул (кучу).
Следует подчеркнуть, что для системы управления виртуальной памятью не составит труда управлять файлом подкачки большого объема, особенно если процесс удерживает в своем адресном пространстве большое количество неактивной виртуальной памяти. Эта неактивная память занимает только место на жестком диске, а не реальную память. Однако иногда бывает нежелательно заполнять страничное пространство, поэтому будет лучше, если Oracle
освободит память. Для таких случаев существует специальная процедура «уборки мусора» с очень простым синтаксисом:
DBMS_SESSION.FREE_UNUSED_USER_MEMORY;
Эта встроенная процедура найдет большую часть памяти UGA
, не используемой переменными программы, и вернет ее в родительскую кучу: при наличии выделенного сервера — в PGA
, а в случае общего сервера — в SGA
.
Я основательно протестировал процесс освобождения занимаемой коллекциями памяти в разных ситуациях, в частности при использовании ассоциативных массивов и вложенных таблиц для выделенного и общего сервера, анонимных блоков и данных пакетов. В результате были сделаны следующие выводы:
- Для освобождения занимаемой памяти недостаточно присвоить вложенной таблице или массиву
VARRAY
значениеNULL
. Нужно либо вызвать метод коллекцииDELETE
, либо присвоить коллекции другую пустую, но инициализированную коллекцию, либо подождать, пока она выйдет из области видимости. - Чтобы освободить память и вернуть ее в родительскую кучу, используйте процедуру
dbms_session.free_unused_user_memory
, когда ваша программа заполнила одну или несколько больших таблицPL/SQL
, пометила их как неиспользуемые, и скорее всего, ей не придется выделять крупные блоки памяти для аналогичных операций. - В режиме общего сервера ошибки нехватки памяти обычно происходят чаще, чем в режиме выделенного сервера, поскольку область
UGA
выделяется из системной глобальной областиSGA
, имеющей ограниченный размер. Как указано в разделе «Что делать при нехватке памяти» (см. далее), при этом может произойти ошибкаORA-04031
. - В режиме общего сервера освободить память, которую занимают таблицы
PL/SQL
, невозможно (если только таблица не объявлена на уровне пакета).
На практике объем памяти, занимаемой коллекцией элементов типа NUMBER
, не зависит от того, содержат ли элементы значения NULL
или, допустим, 38-значные числа. А вот для значений типа VARCHAR2
, объявленных с длиной более 30 символов, Oracle
, похоже, выделяет память динамически.
При заполнении ассоциативного массива в режиме выделенного сервера массив, содержащий миллион значений типа NUMBER
, занимает около 38 Мбайт. И даже если элементы массива имеют тип BOOLEAN
, Oracle9i
использует для него почти 15 Мбайт памяти. Умножьте это значение на количество пользователей, например на 100, — результат будет огромным, особенно если выгрузка памяти на диск нежелательна по соображениям производительности.
Чтобы узнать, сколько памяти UGA
и PGA
использует текущий сеанс, выполните запрос следующего вида:
SELECT n.name, ROUND(m.value/1024) kbytes
FROM V$STATNAME n, V$MYSTAT m
WHERE n.statistic# = m.statistic#
AND n.name LIKE 'session%memory%'
(Для чтения представлений из этого примера недостаточно привилегий по умолчанию.) Запрос выдаст текущие и максимальные затраты памяти в сеансе.
Если вы хотите освободить память, используемую пакетными коллекциями без завершения сеанса, вызовите одну из двух встроенных процедур.
-
DBMS_SESSION.RESET_PACKAGE
— освобождает всю память, выделенную для хранения информации о состоянии пакета. В результате все переменные пакета получают значения по умолчанию. Для пакетов эта встроенная процедура делает больше, чем пакетFREE_UNUSED_USER_MEMORY
, поскольку она не обращает внимания на то, используется память или нет. -
dbms_session.modify_package_state
(флаги операцийin pls_integer
) — в параметре флаги операций можно задать одну из двух констант:DBMS_SESSlON.free_all_
resources или DBMS_SESSION.reinitialize
. Использование первой приводит к тому же эффекту, что и применение процедурыRESET_PACKAGE
. Вторая константа восстанавливает переменные состояния, присваивая им значения по умолчанию, но не освобождает и не воссоздает данные пакета с нуля. Кроме того, она программно закрывает открытые курсоры и не очищает их кэш. Если в вашей ситуации это приемлемо, используйте вторую константу, потому что по скорости она превосходит полный сброс пакета.
Операции BULK COLLECT...LIMIT
BULK
-операции повышают эффективность обработки данных, но вы должны следить за тем, чтобы затраты памяти оставались умеренными, а коллекции не росли до слишком больших размеров. При выборке BULK COLLECT
в коллекцию по умолчанию загружаются все строки. При больших объемах данных коллекция получается слишком большой. В таких случаях на помощь приходит конструкция LIMIT
.
В ходе тестирования выяснилось, что она сокращает затраты памяти, что было ожидаемо, но оказалось, что оно еще и ускоряет работу программ. В следующем примере используется тестовая таблица с миллионом строк. Чтобы получить надежные данные для сравнения, программа сначала была выполнена для предварительного заполнения кэша, а затем она была запущена снова после повторного подключения к базе данных. Для вывода информации об использовании памяти использовались средства пакета plsql_memory
(см. файл plsql_memory.pkg
на сайте книги):
DECLARE
-- Подготовка коллекций
TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE nametab IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;
TYPE tstab IS TABLE OF TIMESTAMP INDEX BY PLS_INTEGER;
CURSOR test_c IS
SELECT hi_card_nbr,hi_card_str ,hi_card_ts
FROM data_test
;
nbrs numtab;
txt nametab;
tstamps tstab;
counter number;
strt number;
fnsh number;BEGIN
plsql_memory.start_analysis; -- Инициализация вывода данных о затратах памяти
strt := dbms_utility.get_time; -- Сохранение начального времени
OPEN test_c;
LOOP
FETCH test_c BULK COLLECT INTO nbrs,txt,tstamps LIMIT 10000;
EXIT WHEN nbrs.COUNT = 0;
FOR i IN 1..nbrs.COUNT LOOP
counter := counter + i; -- Обработка данных
END LOOP;
END LOOP;
plsql_memory.show_memory_usage;
CLOSE test_c;
fnsh := dbms_utility.get_time;
-- Преобразование сотых долей секунды в миллисекунды
DBMS_OUTPUT.PUT_LINE('Run time = '||(fnsh-strt)*10||' ms');
END;
/
Результаты, которые я получил:
Change in UGA memory: 394272 (Current = 2459840)
Change in PGA memory: 1638400 (Current = 5807624)
Run time = 1530 ms
Мы видим, что с пределом в 10 000 записей затраты памяти PGA
выросли на 1 638 400 байт. При повторном выводе информации после завершения блока PL/SQL
большая часть этой памяти (хотя и не вся) освобождается:
EXEC plsql_memory.show_memory_usage;
Change in UGA memory: 0 (Current =2394352)
Change in PGA memory: -458752 (Current = 3907080)
Затем было проведено повторное тестирование без LIMIT
, чтобы все строки таблицы загружались за один раз:
Change in UGA memory: 0 (Current = 1366000)
Change in PGA memory: 18153472 (Current = 22519304)
Как видите, без LIMIT
используется намного больше памяти. Итак, я настоятельно рекомендую включать LIMIT
в окончательную версию ваших приложений.
Сохранение состояния объектов
Обычно Oracle
сохраняет до окончания сеанса значения констант уровня пакета, переменных и состояние курсора в области UGA
. С переменными, объявленными в разделе объявлений отдельного модуля, дело обстоит иначе. Поскольку их область видимости ограничена модулем, по завершении его работы занимаемая данными память освобождается. С этого момента их самих больше не существует.
Кроме отключения от сервера базы данных, потеря состояния пакета может происходить еще по нескольким причинам:
- программа была перекомпилирована или по иной причине стала недействительной с точки зрения базы данных;
- в текущем сеансе была выполнена встроенная процедура
DBMS_SESSI0N. RESET_PACKAGE
; - в код программы добавлена директива компилятора
SERIALLY_REUSABLE
(см. здесь), предписывающаяOracle
сохранять параметры состояния только на время вызова, а не в течение всего сеанса; - в программе используется веб-шлюз в режиме по умолчанию, в котором параметры сеанса каждого клиента не сохраняются;
- в клиентский сеанс передается такая ошибка, как
ORA-04069
(не удается удалить или заменить библиотеку с зависимой таблицей).
С учетом этих ограничений структуры данных пакета могут действовать в среде PL/ SQL
как глобальные. Иначе говоря, они могут использоваться программами PL/SQL
, выполняемыми в одном сеансе, для обмена данными.
С точки зрения архитектуры приложения глобальные данные делятся на два вида: открытые и приватные.
- Открытые данные. Структура данных, объявленная в спецификации пакета, является глобальной открытой структурой данных. Доступ к ней имеют любые программы и пользователи с привилегией
EXECUTE
. Программы даже могут присваивать произвольные значения пакетным переменным, которые не объявлены как константы. Как известно, открытые глобальные данные являются потенциальной причиной многих ошибок: их удобно объявлять, но применение их «на скорую руку» порождает неструктурированный код с опасными побочными эффектами. - Приватные данные. Приватные глобальные структуры данных не вызывают столько проблем. В спецификации пакета они отсутствуют и извне на них ссылаться нельзя. Эти данные предназначены для использования только внутри пакета и только его элементами.
Пакетные данные глобальны только в рамках одного сеанса или подключения к базе данных. Они не используются совместно несколькими сеансами. Если вам потребуется организовать совместный доступ к данным между сеансами, для этого существуют другие средства: пакет DBMS_PIPE package
, Oracle Advanced Queuing
, пакет UTL_TCP
... не говоря уже о таблицах базы данных!
Что делать при нехватке памяти
Предположим, вы работаете с базой данных; все идет прекрасно, выполняется множество команд SQL
и PL/SQL
, и вдруг как гром с ясного неба: ошибка ORA-04031
, не удается выделить n байт общей памяти. Такая ошибка чаще встречается в режиме общего сервера с его повышенным расходом памяти UGA
общего сервера. В режиме выделенного сервера база данных обычно может получить больше виртуальной памяти от операционной системы, но и в этом случае может возникнуть аналогичная ошибка ORA-04030
. Собственно, с выделенным сервером максимальный объем памяти составляет примерно 4 Гбайт на сеанс, тогда как с общим сервером можно вручную назначить пулу любой нужный размер.
Исправить положение можно несколькими способами. Если вы являетесь разработчиком приложения, попробуйте сократить использование общей памяти. Некоторые возможные действия (приблизительно в порядке применения):
- Внесите изменения в код и обеспечьте совместное использование максимального количества команд SQL.
- Сократите размер или количество коллекций, хранящихся в памяти.
- Сократите объем кода приложения в памяти.
- Измените настройки уровня базы данных и/или купите дополнительную память для сервера.
Пункты 1 и 2 уже были рассмотрены; рассмотрим пункт 3. Как оценить размер исходного кода после того, как он будет загружен в память? И как сократить его?
Прежде чем запускать программу PL/SQL
, база данных должна загрузить в память весь свой байт-код. Чтобы узнать, сколько места занимает программный объект в общем пуле, попросите своего администратора базы данных выполнить встроенную процедуру DBMS_SHARED_POOL. SIZES
, которая перечисляет все объекты с размером больше заданного. Следующий пример выводит информацию о затратах памяти, необходимых объектам в общем пуле сразу же после запуска базы данных1:
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> EXEC DBMS_SHARED_POOL.sizes(minsize => 125)
SIZE(K) KEPT NAME
------- ------ ---------------------------------------------------------------
433 SYS.STANDARD (PACKAGE)
364 SYS.DBMS_RCVMAN (PACKAGE BODY)
249 SYSMAN.MGMT_JOB_ENGINE (PACKAGE BODY)
224 SYS.DBMS_RCVMAN (PACKAGE)
221 SYS.DBMS_STATS_INTERNAL (PACKAGE)
220 SYS.DBMS_BACKUP_RESTORE (PACKAGE)
125 MERGE INTO cache_stats_1$ D USING (select * from table(dbms_sta
ts_internal.format_cache_rows(CURSOR((select dataobj# o, st
atistic# stat, nvl(value, 0) val from gv$segstat where stat
istic# in (0, 3, 5) and obj# > 0 and inst_id = 1) union all
(select obj# o, 7 stat,nvl(sum(num_buf), 0) val from x$kcb
oqh x where inst_id = 1 group by obj#) order by o))) wh
(20B5C934,3478682418) (CURSOR)
Условие minsize
=> 125 означает «выводить только объекты с размером 125 Кбайт и выше». Из выходных данных видно, что больше всего общей памяти (433 Кбайт) занимает пакет STANDARD
[1] [2].
Если вы хотите избавиться от ошибок 4031 или 4030, знать объем памяти, используемой программами, необходимо, но не достаточно; также нужно знать размер общего пула и объем памяти, занимаемой «воссоздаваемыми» объектами — то есть объектами, которые могут устаревать, вытесняться из памяти и загружаться повторно при необходимости. Некоторые из этих сведений трудно получить от базы данных; вам может потребоваться знание таинственных представлений X$. Тем не менее версии 9.2.0.5 и выше автоматически генерируют дамп кучи в каталоге USER_DUMP_DEST
при возникновении ошибки 4031. Посмотрите, что можно извлечь из полученной информации, или просто передайте ее в техническую поддержку Oracle. Также попробуйте определить, не содержит ли приложение большой объем неразделяемого кода, который логичнее было бы сделать общим, потому что это может оказать большое влияние на затраты памяти.
[1] Почему столбцы данных неправильно выравниваются по своим заголовкам? Вероятно, из-за серьезных ограничений DBMS_OUTPUT
. Если вас это не устраивает, напишите собственную реализацию (запросите данные из V$SQLAREA
после выполнения пакета).
[2] Старые версии DBMS_SHARED_POOL.SIZES
содержали ошибку, из-за которой выводимые результаты были завышены приблизительно на 2.3%. Пакет Oracle
ошибочно вычислял размер в килобайтах делением на 1000 вместо 1024.
Программы PL/SQL
, откомпилированные в низкоуровневый код, компонуются в общие библиотечные файлы, но база данных все равно выделяет некоторую память для их выполнения. Привилегированный пользователь может использовать средства операционной системы (такие, как pmap в Solaris
) для изменения объема памяти, занимаемой за пределами базы данных.
Теперь обратимся к шагу 4 — настройке базы данных или приобретению дополнительной памяти. Компетентный администратор базы данных знает, как настроить общий пул при помощи следующих параметров:
-
SHARED_POOL_SlZE
— байты, зарезервированные для общего пула. -
DB_CACHE_SIZE
— байты памяти, зарезервированные для хранения строк данных из базы данных (возможно, вам придется сократить это значение для увеличения размера общего пула). -
LARGE_POOL_SlZE
— байты памяти, зарезервированные для необязательного блока, в котором хранится областьUGA
подключений к общему серверу (предотвращает конкуренцию за использование общего пула со стороны переменной частиUGA
). -
3AVA_POOL_SlZE
— байты, используемые менеджером памятиJava
. -
STREAMS_POOL_SIZE
— байты, используемые технологиейOracle
Streams
. -
SGA_TARGET
— размер областиSGA
, из которой база данных будет автоматически выделять упоминавшиеся выше кэш и пул (ненулевое количество байтов). -
PGA_AGGREGATE_TARGET
— общий объем памяти, используемой всеми серверными процессами в экземпляре. Обычно равен объему серверной памяти, доступной для базы данных, за вычетом размераSGA
. -
PGA_AGGREGATE_LIMIT
(появился вOracle Database 12c
) — задает ограничение агрегатной памятиPGA
, потребляемой экземпляром. При превышении лимита вызовы сеансов, использующие больше всего памяти, будут отменены. Параллельные запросы будут рассматривается как одно целое. Если суммарное использование памятиPGA
по-прежнему превышает лимит, то сеансы с наибольшими затратами памяти завершаются. Описанные действия не применяются к процессамSYS
и критичным фоновым процессам.
Вы также можете попросить своего администратора обеспечить принудительное хранение в памяти программ PL/SQL
, последовательностей, таблиц или курсоров при помощи процедуры DBMS_SHARED_POOL.KEEP
[1].
Например, следующий блок требует, чтобы база данных зафиксировала пакет STANDARD
в памяти:
BEGIN
DBMS_SHARED_POOL.KEEP('SYS.STANDARD');
END;
Фиксировать в памяти особенно полезно большие программы, которые выполняются относительно редко. Без фиксации частично откомпилированный код с большой вероятностью будет вытеснен из-за долгого бездействия, и при повторном вызове его загрузка может привести к вытеснению из пула множества меньших объектов (и соответствующим потерям производительности).
Возможно, следующий совет покажется очевидным, но если у небольшого подмножества пользователей или приложений возникают ошибки ORA-04031
— попробуйте перевести «нарушителей» в режим выделенного сервера.