PL/SQL и память экземпляров базы данных Oracle

PL/SQL и память экземпляров базы данных Oracle

Экономно используя машинные ресурсы (включая память и процессор), 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 Гбайт на сеанс, тогда как с общим сервером можно вручную назначить пулу любой нужный размер.

Исправить положение можно несколькими способами. Если вы являетесь разработчиком приложения, попробуйте сократить использование общей памяти. Некоторые возможные действия (приблизительно в порядке применения):

  1. Внесите изменения в код и обеспечьте совместное использование максимального количества команд SQL.
  2. Сократите размер или количество коллекций, хранящихся в памяти.
  3. Сократите объем кода приложения в памяти.
  4. Измените настройки уровня базы данных и/или купите дополнительную память для сервера.

Пункты 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 — попробуйте перевести «нарушителей» в режим выделенного сервера.

 

Вас заинтересует / Intresting for you:

Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 1362 просмотров Rasen Fasenger Mon, 22 Oct 2018, 04:44:08
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 1909 просмотров sepia Sun, 08 Jul 2018, 07:33:47
Использование записей (records...
Использование записей (records... 1982 просмотров Алексей Вятский Thu, 05 Jul 2018, 07:49:43
Символьные функции и аргументы...
Символьные функции и аргументы... 2909 просмотров Анатолий Wed, 23 May 2018, 18:54:01

Comments on PL/SQL и память экземпляров базы данных Oracle

Будьте первым прокомментировавшим
Пожалуйста, авторизуйтесь, для комментирования