Настройка памяти базы данных Oracle: буферы, кэши, PGA

Стас Белков

Стас Белков

Автор статьи. Известный специалист в мире IT. Консультант по продуктам и решениям Oracle. Практикующий программист и администратор баз данных. Подробнее.

Как настроить память в базе данных Oracle: разделяемый пула, кэш буферов и PGAХорошо известным фактом о производительности системы является то, что выборка данных, хранящихся в памяти, производится гораздо быстрее, чем извлечение данных с дискового устройства хранения. Зная об этом, база данных Oracle старается удерживать как можно больше из недавно запрашивавшихся данных в своей области SGA. Помимо данных, для быстрого доступа в памяти кэшируется еще прошедший синтаксический анализ SQL-код и необходимая информация из словаря данных. Настраивать выделяемый Oracle объем памяти можно довольно легко, изменяя параметр инициализации MEMORY_TARGET.

Между конфигурацией памяти и использованием этой памяти приложением существует двусторонняя связь. Правильный размер выделяемой памяти зависит от природы приложения, количества пользователей и размера транзакций. Если памяти не достаточно, приложению придется выполнять отнимающие много времени дисковые операции ввода-вывода. Однако само приложение тоже может излишне потреблять память и тогда выдача ему большего количества памяти не будет правильной стратегией.


Оглавление статьи[Показать]


Администратору базы данных ни в коем случае не следует настраивать память и ее размер отдельно. Это чревато неправильным выбором из-за устранения симптомов, а не причины того, что выглядит так, будто бы не хватает памяти. Администраторы баз данных часто склонны выделять как можно больше памяти разделяемому пулу в надежде на то, что это поможет устранить проблему. Однако иногда это только усугубляет дело. Поэтому лучше управлять базой данных с использованием необходимого небольшого объема памяти и не более. Система всегда может использовать свободную память, чтобы не происходило страничного обмена и подкачки. Спады в производительности, возникающие из-за выполнения подкачки страниц, в большинстве систем перевешивают преимущества, получаемые в результате выделения большего объема памяти для области SGA.

 

Настройка разделяемого пула

В производственной базе данных разделяемый пул (shared pool) будет требовать больше всего внимания из-за оказания непосредственного воздействия на производительность системы. Разделяемый пул представляет собой компонент SGA, в котором удерживаются практически все необходимые элементы для выполнения SQL-операторов и PL/SQL-программ. Помимо программного кода в разделяемом пуле также кэшируется информация из словаря данных, к которой Oracle приходится часто обращаться по ходу выполнения программ.

Правильная настройка разделяемого пула приводит к значительным улучшениям в производительности, а неправильная — к появлению проблем следующего рода:

  • увеличение числа случаев соперничества за получение защелок, с вытекающим из этого повышением спроса на ресурсы ЦП;
  • увеличение количества операций ввода-вывода из-за отсутствия пригодных для выполнения версий SQL-операторов в разделяемом пуле;
  • увеличение использования ЦП из-за ненужного синтаксического анализа SQL-кода.

В целом повышение связанных с разделяемым пулом и прочих ожиданий, наблюдаемое во время серьезного замедления в работе производственной базы данных, является результатом применения SQL-кода, в котором не используются переменные связывания (о важности переменных связывания более подробно рассказывается позже в этой заметке).

С увеличением числа пользователей растет и спрос на память разделяемого пула и защелки, которые представляют собой внутренние блокировки в областях памяти.

Чрезмерное количество защелок может приводить к повышению времени ожидания и замедлению времени отклика. Иногда может даже казаться, что вся база данных вообще зависает.

Разделяемый пул состоит из двух основных областей: библиотечного кэша и кэша словаря данных. Выделять или уменьшать объем памяти отдельно для только какого-то одного из этих компонентов нельзя. В случае увеличения объема памяти для разделяемого пула в общем, размер обоих этих компонентов будет увеличиваться до какой-то степени, определяемой самой Oracle. Аналогичным образом, в случае уменьшения объема памяти для всего разделяемого пула в общем, размер обоих этих компонентов будет уменьшаться. Теперь давайте рассмотрим этих два важных компонента разделяемого пула более детально.

 

Библиотечный кэш

В библиотечном кэше (library cache) содержатся проанализированные и признанные пригодными для выполнения версии SQL- и PL/SQL-кода. Все SQL-операторы проходят во время обработки перечислены ниже этапы.

  • Синтаксический анализ (или разбор), во время которого осуществляется проверка на предмет правильности SQL-операторов с синтаксической и с семантической точки зрения и на предмет наличия объектных привилегий, необходимых для выполнения указанных в них действий.
  • Оптимизация, во время которой оптимизатор Oracle вычисляет, как можно обработать оператор с наименьшими затратами, после проведения анализа нескольких альтернативных вариантов.
  • Выполнение, во время которого Oracle использует оптимизированный физический план выполнения для выполнения указанного в SQL-операторе действия.
  • Выборка, через которую проходят только те операторы SELECT, которые требуют от Oracle возврата каких-нибудь строк (для операторов не запросного типа этот этап является необязательным).

Синтаксический анализ представляет собой ресурсоемкую операцию, и потому если приложению нужно выполнять один и тот же SQL-оператор не один раз, наличие проанализированной версии в памяти будет уменьшать состязание за получение защелок и ресурсов ЦП, ввода-вывода и памяти. При первом подвергании оператора синтаксическому анализу Oracle создает так называемое дерево синтаксического анализа. Этап оптимизации является необходимым только для первого выполнения SQL-оператора. После оптимизации оператора SQL информация о наилучшем для него пути доступа инкапсулируется в так называемый план доступа. Перед первым выполнением оператора дерево синтаксического разбора и план доступа сохраняются в библиотечном кэше. Благодаря этому, при последующих вызовах того же оператора ему требуется проходить только через последний этап — выполнение, — что позволяет избегать накладных расходов, связанных с этапами синтаксического анализа и оптимизации, при условии, что Oracle удается найти дерево синтаксического разбора и план доступа в библиотечном кэше. Конечно, если оператор представляет собой SQL-запрос, последним этапом будет выполнение операции выборки.

Библиотечный кэш, будучи ограниченным по размеру, избавляется от старых SQL- операторов, когда для новых перестает хватать места. Использовать проанализированный оператор повторно для множества выполнений можно, только если SQL-оператор и проанализированный оператор являются идентичными. Два SQL-оператора считаются идентичными, если в них используется совершенно одинаковый код, регистр символов и пробелы в том числе. Объясняется это тем, что при сравнении нового оператора с существующими операторами в библиотечном кэше Oracle использует простые операции сравнения строк. Кроме того, любые имеющиеся переменные связывания должны обязательно совпадать по типу данных и размеру. Ниже приводится пара примеров, показывающих, насколько разборчиво будет вести себя Oracle при выяснении того, являются ли два SQL-оператора идентичными.

В первом примере операторы не будут считаться идентичными из-за наличия во втором операторе лишнего пробела: 

SELECT * FROM employees;
SELECT * FROM employees;

В следующем примере операторы не будут считаться идентичными из-за использования во втором операторе другого регистра символов в имени таблицы Employees. Обе версии employees представляют собой литералы, потому что отличаются друг от друга только символами: 

SELECT * FROM employees;
SELECT * FROM Employees;

А теперь давайте предположим, что пользователи в базе данных выполняют три таких SQL-оператора:

SELECT * FROM persons WHERE person_id = 10
SELECT * FROM persons WHERE person_id = 999
SELECT * FROM persons WHERE person_id = 6666 

Oracle будет использовать для этих трех операторов разные планы выполнения, несмотря на то, что они являются идентичными во всех отношениях, кроме значения person_id. Каждый из них будет анализироваться и выполняться отдельно так, будто бы они выглядят совершенно по-разному. Из-за того, что эти три оператора все-таки, по сути, очень похожи, такой подход к их обработке, следовательно, будет неэффективным. Нетрудно представить, что в случае выполнения сотен тысяч подобных операторов в течение дня будет тратиться масса ресурсов базы данных, и запросы будут выполняться очень медленно. Переменные связывания позволяют использовать SQL-операторы повторно, делая их “идентичными” и, следовательно, пригодными для применения одного и того же плана выполнения.

Например, если брать три показанных выше оператора, то заставить Oracle считать их идентичными и, таким образом, использовать для них один, а не три отдельных плана выполнения, можно путем связывания значений person_id (10, 99 и 6666) с переменной :var

SELECT * FROM persons WHERE person_id = :var

За счет применения переменных связывания можно очень значительно повышать скорость выполнения запросов, а чуть позже в этой главе, в разделе “Использование параметра CURSOR_SHARING (для замены литералов)” будет объясняться, как “заставить” Oracle использовать переменные связывания даже тогда, когда приложение их не использует.

 

Кэш словаря данных

Кэш словаря данных (dictionary cache), как уже упоминалось ранее, кэширует информацию словаря данных. Он гораздо меньше библиотечного кэша и для увеличения или уменьшения его размера необходимо соответствующим образом изменить размер разделяемого пула. Если библиотечный кэш сконфигурирован правильно, скорее всего, с кэшем словаря данных тоже будет все в порядке. Получить представление об эффективности кэша словаря данных можно с помощью следующего запроса:

SELECT (sum(gets - getmisses - fixed)) / SUM(gets)
2 "data dictionary hit ratio" from v$rowcache;
data dictionary hit ratio
-------------------------
.936781093

Обычно лучше добиваться, чтобы коэффициент попаданий в этот кэш достигал 95–99%, хотя Oracle иногда, похоже, считает, что вполне достаточно, чтобы он достигал и 85%. Увеличивать этот коэффициент можно путем увеличения для экземпляра размера разделяемого пула.

 

Полный и частичный синтаксический анализ

В предыдущей главе речь шла о том, что весь SQL-код проходит через этапы синтаксического анализа, оптимизации и выполнения. При поступлении оператора от приложения Oracle сначала проверяет, не существует ли проанализированная версия этого оператора. Если существует, тогда Oracle проводит частичный синтаксический анализ, или разбор (soft parse), подразумевающий просто обращение в библиотечный кэш. Если же на этапе синтаксического анализа или выполнения Oracle не удается найти проанализированную или пригодную для выполнения версию кода в разделяемом пуле, проводится полный синтаксический анализ, или разбор (hard parse), подразумевающий выполнение повторной загрузки SQL-оператора в разделяемый пул и подвергание его полному анализу.

В частности, во время полного синтаксического анализа Oracle выполняет проверку оператора на предмет правильности с синтаксической и семантической точки зрения, удостоверяется в наличии необходимых объектных и системных привилегий, генерирует оптимальный план выполнения и, наконец, загружает его в библиотечный кэш. Полный синтаксический анализ подразумевает использование гораздо большего количества ресурсов ЦП и является неэффективным по сравнению с частичным синтаксическим анализом, который основан на повторном использовании проанализированных ранее версий операторов. Полный синтаксический анализ предполагает построение всего дерева синтаксического разбора с нуля и потому представляет собой более ресурсоемкую операцию. Помимо более интенсивного потребления ресурсов ЦП полный синтаксический анализ сопровождается еще и большим количеством событий получения защелки, что может увеличивать время отклика при запросе. Идеальным вариантом считается, когда оператор анализируется один раз и потом выполняется много раз. В противном случае Oracle приходится производить полный синтаксический анализ.


Внимание! Высокий коэффициент по выполнению полного синтаксического анализа чреват возникновением серьезных проблем с производительностью, поэтому очень важно своевременно снижать его.


Во время частичного синтаксического анализа Oracle просто отыскивает в библиотечном кэше идентичный оператор и использует его повторно. Важный этап оптимизации SQL-оператора при частичном синтаксическом анализе полностью пропускается. Никакого анализа в таком случае (в отличие от того, когда имеет место полный синтаксический анализ) не производится, поскольку выполняется хеширование нового оператора и сравнение его хеш-значения с хеш-значениями похожих операторов в библиотечном кэше. Во время частичного синтаксического разбора Oracle выполняет проверку только на предмет наличия необходимых привилегий. Например, даже в случае присутствия идентичного оператора в библиотечном кэше, выполнение SQL-оператора может не состоятся, если на этапе (частичного) синтаксического анализа Oracle определит, что не хватает необходимых привилегий. В Oracle рекомендуют считать коэффициент по выполнению полного синтаксического анализа, составляющий свыше 100 раз в секунду, чрезмерным.

 

Использование SQL Trace и TKPROF для изучения информации, касающейся синтаксического анализа

В блогах уже рассказывалось о том, как использовать утилиты SQL Trace и TKPROF для трассировки выполнения SQL-операторов. Одним из наиболее полезных среди предоставляемых утилитой SQL Trace фрагментов информации является информация, касающаяся выполнения в запросе полного и частичного синтаксического анализа. Ниже приведен простой пример, демонстрирующий получение такой информации для любого запроса.

1. Активизируйте средство трассировки в сеансе следующей командой: 

ALTER SESSION SET SQL_TRACE=TRUE;
Session altered.

Для гарантии отсутствия проанализированных ранее запросов сбросьте содержимое разделяемого пула, что приведет к удалению всех SQL-операторов из библиотечного кэша: 

ALTER SYSTEM FLUSH SHARED_POOL;
System altered.

2. Воспользуйтесь следующим запросом для создания трассировки в каталоге дампа пользователя:

SELECT * FROM comp_orgs WHERE created_date > SYSDATE-5;

 

Получаем следующий вывод SQL Trace в файле вывода:

PARSING IN CURSOR #1 len=63 dep=0 uid=21 oct=3
lid=21 tim=1326831345 hv=71548308
SELECT * FROM comp_orgs WHERE created_date > SYSDATE-:"SYS_B_0"
END OF STMT
PARSE #1:c=4,e=4,p=0,cr=57,cu=3,mis=1,r=0,dep=0,og=0,tim=1326831345

Обратите внимание на то, что mis=1 свидетельствует о выполнении полного синтаксического анализа из-за отсутствия данного SQL-оператора в библиотечном кэше.

3. Далее примените немного другую версию предыдущего запроса. Вывод будет выглядеть так же, но Oracle не будет использовать проанализированную ранее версию из-за того, что операторы в шаге 2 и 3 не идентичны. 

SELECT * FROM comp_orgs WHERE created_date > (SYSDATE -5);

В частности, соответствующий вывод SQL Trace будет выглядеть так:

PARSING IN CURSOR #1 len=77 dep=0 uid=21 oct=3 lid=21 tim=1326833972
SELECT /* A Hint */ * FROM comp_orgs WHERE
/* Подсказка */
created_date > SYSDATE-:"SYS_B_0"
END OF STMT
PARSE #1:c=1,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1326833972 

Опять-таки, mis=1 свидетельствует о выполнении полного синтаксического анализа из-за отсутствия нужного оператора в библиотечном кэше. В этом нет ничего удивительного, поскольку данный оператор не идентичен тому, что применялся ранее и, следовательно, должен анализироваться с нуля.

4. Снова воспользуйтесь исходным запросом. Теперь Oracle проведет только частичный синтаксический анализ из-за совпадения оператора здесь с тем, что применялся в шаге 2. Вывод SQL Trace будет выглядеть так:

PARSING IN CURSOR #1 len=63 dep=0 uid=21 oct=3 lid=21 tim=1326834357
SELECT * FROM comp_orgs WHERE created_date > SYSDATE-:"SYS_B_0"
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1326834357

Оператор в шаге 4 во всех отношениях совпадает с оператором в шаге 2, поэтому Oracle просто использует повторно уже проанализированную версию. Следовательно, mis=0 свидетельствует о выполнении не полного, а лишь частичного синтаксического анализа, который обходится гораздо дешевле в плане потребления ресурсов.

Если теперь посмотреть на вывод TKPROF, то по SQL-операторам из шагов 2 и 4 (которые являются идентичными) в нем будет содержаться такая информация:

************************************************************************
SELECT * FROM comp_orgs WHERE created_date > SYSDATE - 5
call    count  cpu    elapsed     disk   query  current   rows
------- ------ ------ -------- ------- ------- -------- ------
Parse   2      0.03   0.01           0       1        3      0
Execute 2      0.00   0.00           0       0        0      0
Fetch   4      0.07   0.10         156     166       24     10
total   8      0.10   0.11         156     167       27     10
Misses in library cache during parse: 1
************************************************************************

Нетрудно заметить, что при выполнении оператора в первый раз имела место одна неудачная попытка обращения (miss) в библиотечный кэш. Во второй раз полный синтаксический анализ не выполнялся, и потому никаких неудачных попыток обращения в библиотечный кэш не было.

Измерение эффективности библиотечного кэша

Для выяснения правильности размера библиотечного кэша можно использовать простые коэффициенты. Представление словаря данных V$LIBRARYCACHE дает всю необходимую для этого информацию. В листинге 1 показана структура этого представления.


 

DESC V$LIBRARYCACHE
Name                              Null?    Type
--------------------------------- -------- ------------
NAMESPACE                                  VARCHAR2(15)
GETS                                       NUMBER
GETHITS                                    NUMBER
GETHITRATIO                                NUMBER
PINS                                       NUMBER
PINHITS                                    NUMBER
PINHITRATIO                                NUMBER
RELOADS                                    NUMBER
INVALIDATIONS                              NUMBER
DLM_LOCK_REQUESTS                          NUMBER
DLM_PIN_REQUESTS                           NUMBER
DLM_PIN_RELEASES                           NUMBER
DLM_INVALIDATION_REQUESTS                  NUMBER
DLM_INVALIDATIONS                          NUMBER


Получать информацию о коэффициенте попаданий в библиотечный кэш можно с помощью следующего запроса к этому представлению:

SELECT SUM(pinhits)/sum(pins) Library_cache_hit_ratio
2 FROM V$LIBRARYCACHE;
LIBRARY_CACHE_HIT_RATIO
------------------------
.993928013
 

В данном примере вывод этого запроса показывает, что коэффициент попаданий у библиотечного кэша в текущий момент составляет свыше 99%, что считается хорошим показателем. Однако чрезмерно полагаться на показатели по коэффициенту попаданий в библиотечный кэш и кэши буферов все же не стоит. Этот коэффициент может составлять 99,99%, но из-за значительных ожиданий, причиняемых событиями наподобие чрезмерного выполнения синтаксического анализа, база данных все равно будет работать медленно. Поэтому лучше всегда следить за возникающими в системе событиями ожидания и не полагаться слепо только на высокие показатели по коэффициенту попаданий.

В листинге 2 показано, как определять количество повторных загрузок (realoads) и удачных закреплений (pinhits) различных операторов в библиотечном кэше.


 

SELECT namespace, pins, pinhits, reloads
2 FROM V$LIBRARYCACHE
3 ORDER BY namespace;
NAMESPACE            PINS    PINHITS RELOADS
------------------ ------ ---------- -------
BODY                   25         12       0
CLUSTER               248        239       0
INDEX                  31          0       0
JAVA DATA               6          4       0
JAVA RESOURCE           2          1       0
JAVA SOURCE             0          0       0
OBJECT                  0          0       0
PIPE                    0          0       0
SQL AREA           390039     389465      14
TABLE/PROCEDURE      3532       1992       0
TRIGGER                 5          3       0
11 rows selected.


Если в столбце REALOADS представления V$LIBRARYCACHE отображаются большие значения, это означает, что много SQL-операторов загружается в библиотечный кэш повторно после истечения их срока хранения. В таком случае можно пробовать увеличить размер разделяемого пула, но это может все равно не помочь, если приложение имеет большой размер, если показатель по количеству выполнений является слишком высоким или если в приложении не применяются переменные связывания. Если операторы не являются совершенно идентичными и/или если в них используются константы, а не переменные связывания, операции полного синтаксического анализа будут выполняться гораздо чаще, а эти операции по своей природе обходятся гораздо дороже с точки зрения потребления ресурсов. Вынуждать пригодные для выполнения SQL-операторы оставаться в представляющем библиотечный кэш компоненте разделяемого пула можно за счет применения пакета DBMS_SHARED_POOL, поставляемого Oracle. В состав этого пакета входят процедуры KEEP и UNKEEP, которые позволяют, соответственно, удерживать и освобождать объекты в разделяемом пуле.

Для определения того, какое количество объектов в памяти библиотечного кэша в текущий момент используется в разделяемом пуле и какое количество из них можно освободить, служит представление V$LIBRARY_CACHE_MEMORY. С помощью представления V$SHARED_POOL_ADVICE можно выяснить, насколько меньше времени будет тратиться на выполнение синтаксического анализа при различных размерах разделяемого пула.

 

Оптимизация использования библиотечного кэша

Для того чтобы области библиотечного кэша использовались более эффективно, можно сконфигурировать несколько важных параметров инициализации. О некоторых из этих параметров более подробно рассказывается в следующих разделах.

 

Использование параметра CURSOR_SHARING (для замены литералов)

Под оптимизацией использования библиотечного кэша главным образом подразумевается обеспечение возможности повторного применения анализировавшегося или выполнявшегося ранее кода. Одним из простейших способов для обеспечения такой возможности является использование в SQL-коде вместо литеральных операторов переменных связывания (bind variables). Переменные связывания напоминают собой метки-заполнители: они позволяют связывать данные приложения с SQL-оператором. Применение переменных связывания дает Oracle возможность использовать операторы повторно тогда, когда единственным, что отличает их друг от друга, являются значения во входных переменных. Переменные связывания позволяют использовать повторно находящиеся в кэше проанализированные версии запроса и тем самым увеличивать скорость работы приложения. Ниже приведен пример применения переменных связывания. В этом примере переменная связывания получает числовой тип: 

VARIABLE bindvar NUMBER;
BEGIN
2 :bindvar :=7900;
3 END;
4 /
PL/SQL procedure successfully completed.

После создания такой переменной можно использовать следующий SQL-оператор:
SELECT ename FROM scott.emp WHERE empid = :bindvar;
ENAME
JAMES

Этот оператор можно будет выполняться множество раз с указанием разных значений для переменной связывания. Он будет анализироваться только один раз, а выполняться — много. В отличие от случая с указанием литерального значения для столбца emp_id (например, 7499), Oracle будет просто повторно использовать тот же самый план выполнения, который был создан в первый раз, вместо того, чтобы создавать отдельный план для каждого подобного оператора. Это значительно сократит количество операций полного синтаксического анализа (и событий получения защелки), а также существенно снизит объем потребляемых ресурсов ЦП и время, затрачиваемое на извлечение данных. Например, для всех показанных ниже операторов Oracle сможет использовать проанализированную версию запроса с переменной связывания внутри:

SELECT ename FROM scott.emp WHERE empid = 7499;
SELECT ename FROM scott.emp WHERE empid = 7788;
SELECT ename FROM scott.emp WHERE empid = 7902;

К сожалению, в большинстве приложений чаще всего применяются литеральные значения, а не переменные связывания. Эту проблему можно немного облегчить, установив следующий параметр инициализации: 

CURSOR_SHARING=FORCE

или же так:

CURSOR_SHARING=SIMILAR 

По умолчанию для параметра CURSOR_SHARING устанавливается значение EXACT, указывающее, что только идентичные во всех отношениях операторы могут применяться для повторного выполнения. Два других значения — FORCE и SIMILAR — позволяют Oracle использовать операторы повторно даже тогда, когда они не являются идентичными во всех отношения.

Например, если два оператора являются идентичными во всех отношениях и отличаются только литеральными значениями в некоторых переменных, установка для параметра CURSOR SHARING значения FORCE (CURSOR SHARING=FORCE) позволит Oracle использовать повторно те проанализированные версии SQL-операторов, которые содержатся в ее библиотечном кэше. В таком случае Oracle будет заменять литеральные значения значениями связывания, чтобы операторы становились идентичными. Применение варианта CURSOR_SHARING=FORCE принуждает Oracle использовать переменные связывания во всех обстоятельствах, а применение варианта CURSOR SHARING=SIMILAR — только тогда, когда Oracle считает, что это не скажется негативным образом на оптимизации. Компания Oracle рекомендует использовать вариант CURSOR_SHARING=SIMILAR, а не вариант CURSOR_SHARING=FORCE, из-за возможного оказания последним негативного влияния на планы выполнения. Однако в реальности преимущества, предоставляемые параметром CURSOR_SHARING=FORCE, значительно перевешивают его любые возможные негативные последствия для планов выполнения. При обнаружении, что в базе данных из-за неиспользования переменных связывания очень часто выполняется полный синтаксический анализ, можно очень сильно повысить производительность, просто перейдя с предлагаемого по умолчанию варианта CURSOR_SHARING=EXACT на CURSOR_SHARING=FORCE. Значение этого параметра можно изменять как статически внутри файла init.ora или SPFILE, так и динамически с помощью оператора ALTER SYSTEM (который работает на уровне всей системы) либо оператора ALTER SESSION (работающего на уровне только конкретного сеанса).

Разрешая пользователям разделение операторов, отличающихся только значением констант, параметр CURSOR_SHARING тем самым позволяет базе данных Oracle легко подстраиваться под большое количество пользователей, применяющих похожие, но не идентичные SQL-операторы. Это важное новшество появилось еще в версии Oracle 8i.

 

Определение сеансов с большим количеством операций полного синтаксического анализа

В листинге 3 показан запрос, посредством которого можно выяснять, сколько операций полного синтаксического анализа и сколько выполнений произошло с момента запуска экземпляра, а также идентификатор сеанса пользователя, который выполнял SQL-операторы.


 

SELECT s.sid, s.value "Hard Parses",
2 t.value "Executions Count"
3 FROM v$sesstat s, v$sesstat t
4 WHERE s.sid=t.sid
5 AND s.statistic#=(select statistic#
6 FROM v$statname where name='parse count (hard)')
7 AND t.statistic#=(select statistic#
8 FROM v$statname where name='execute count')
9 AND s.value>0
10* ORDER BY 2 desc;
       SID  Hard Parses  Executions Count
----------  -----------  ----------------
      1696        70750           3638104
      1750        12188            262881
      1759         3555           5895488
      1757         3265           2758185
      1694         1579           2389953
. . .


 

Использование параметра CURSOR_SPACE_FOR_TIME

По умолчанию курсоры могут освобождаться даже тогда, когда курсоры приложений еще не были закрыты. Это увеличивает накладные расходы, поскольку вынуждает Oracle проверять, был ли курсор удален (flushed) из библиотечного кэша. Управлять тем, должно ли происходить подобное освобождение курсоров, позволяет параметр CURSOR_SPACE_FOR_TIME, для которого по умолчанию устанавливается значение FALSE. За счет установки для него значения TRUE можно делать так, чтобы предназначенные для приложения курсоры не могли освобождаться, пока они все остаются открытыми в приложении. Для получения подобного поведения потребуется изменить этот параметр инициализации в файле init.ora следующим образом: 

CURSOR_SPACE_FOR_TIME=TRUE

Совет. При желании установить этот параметр, необходимо обязательно удостовериться, что в разделяемом пуле есть много свободного места, потому что установка этого параметра в TRUE приведет к использованию большего количества памяти в разделяемом пуле для сохранения курсоров в библиотечном кэше.


 

Использование параметра SESSION_CACHED_CURSORS

В идеале все анализируемые операторы приложения должны размещаться в отдельных курсорах, для того, чтобы при возникновении необходимости в выполнении нового оператора, все, что ему требовалось сделать — это выбрать проанализированный оператор и изменить значение переменных. В случае повторного использования приложением одного курсора с различными SQL-операторами ему все равно придется “оплачивать стоимость” частичного синтаксического анализа. После открытия курсора в первый раз Oracle будет подвергать оператор синтаксическому анализу, после чего сможет применять эту проанализированную версию в будущем. Такая стратегия является гораздо лучше создания курсора заново при каждом выполнения базой данных того же самого SQL-оператора. При наличии возможности кэшировать все курсоры, контекст на стороне сервера будет удерживаться даже при закрытии клиентами курсоров или их повторном использовании для новых SQL-операторов.

В ситуациях, когда пользователи постоянно подвергают синтаксическому анализу одни и те же операторы, что часто происходит в приложениях на базе Oracle Forms при переключении пользователями между различными формами, очень выгодно применять параметр SESSION_CACHED_CURSORS. Этот параметр гарантирует, что в случае выполнения для любого курсора более трех запросов на проведение синтаксического анализа, эти запросы будут автоматически помещаться в кэш курсоров сеанса и, следовательно, избавлять новые запросы на проведение синтаксического анализа того же самого оператора от связанных накладных расходов. Использование параметра инициализации SESSION_CACHED_CURSORS и установка для него высокого значения делает процесс обработки запросов более эффективным. Хотя операции частичного синтаксического анализа и обходятся дешевле операций полного синтаксического анализа, за счет установки для этого параметра высокого значения можно сокращать и даже их количество.

Принуждать Oracle помещать в кэш курсоры можно как статическим образом за счет установки параметра SESSION_CACHED_CURSORS в файле параметров инициализации, так и динамически за счет применения следующей команды ALTER SESSION

ALTER SESSION SET SESSION_CACHED_CURSORS = value;

Проверять, насколько хорошо работает установленное для параметра SESSION_CACHED_CURSORS значение, можно посредством представления V$SYSSTAT. Если коэффициент обращений в кэш курсоров сеанса является низким по сравнению с общим количество операций синтаксического анализа в сеансе, тогда значение SESSION_CACHED_CURSORS следует увеличить.

Идеальным вариантом считается, когда SQL-оператор подвергается в сеансе синтаксическому анализу только один раз, а выполняется — много. Более подробную информацию о переменных привязки, разделении курсоров и других связанных с этим вопросах, можно найти в официальном документе Oracle Efficient use of bind variables, cursor_sharing и related cursor parameters (Эффективное использование переменных связывания, технологии разделения курсоров и имеющих к этому отношение параметров курсоров), доступном по адресу http://otn.oracle.com/deploy/performance/pdf/cursor.pdf.

Настраиваем память базы данных Oracle: пулы, кэш буферов, PGA, анализ 

Синтаксический анализ и масштабирование приложений

Когда количество пользователей продолжает расти, в некоторых системах начинают возникать проблемы. В частности, во многих системах из-за попыток подстроиться под постоянно увеличивающееся количество пользователей значительно снижается скорость работы. Поэтому когда количество пользователей растет, необходимо обращать особое внимание на выполнение в системе ненужных операций синтаксического анализа. Частое проведение синтаксического анализа влечет за собой возникновение состязаний за получение защелок, что тормозит работу системы. Ниже перечислены кое-какие рекомендации, суммирующие все обсуждавшиеся выше моменты касательно библиотечного кэша, синтаксического анализа и использования специальных параметров инициализации.

  • Во-первых (что является стандартным правилом), нужно стараться подавать как можно больше кода в виде хранимого кода, т.е. в виде пакетов, процедур и функций, чтобы не иметь проблем, вызываемых нерегламентированными (ad hoc) SQL-запросами. Применение нерегламентированных SQL-операторов может привносить хаос в работу библиотечного кэша и является неэффективным способом использования больших приложений с большим количеством пользователей. Применение хранимого кода, со своей стороны, гарантирует идентичность SQL- операторов и, следовательно, возможность их повторного использования, которая улучшает масштабируемость приложений.
  • Во-вторых, нужно понижать количество операций полного синтаксического анализа, потому что они могут обходиться довольно дорого в плане потребления ресурсов. Одним из способов превращения операции полного синтаксического анализа в операцию частичного синтаксического анализа является использование переменных связывания. Уменьшение количества операций полного синтаксического анализа сокращает степень состязаний за получение защелок в разделяемом пуле.
  • В-третьих, если переменные связывания не применяются в системе, можно использовать параметр CURSOR_SHARING=FORCE для принудительного разделения SQL-операторов, которые отличаются только значением литералов.
  • В-четвертых, следует обращать внимание на количество операций частичного синтаксического анализа, а не на показатель их стоимости на единицу, который у них всегда является гораздо ниже, чем у операций полного синтаксического анализа. Большое количество операций частичного синтаксического анализа увеличивает состязания за защелки в библиотечном кэше и может приводить к замедлению работы базы данных. Здесь главное запомнить, что лучше избегать излишних операций частичного синтаксического анализа, которые в конечном итоге всегда обязательно что-то будут стоить.
  • В-пятых, нужно применять параметр инициализации SESSION_CACHED_CURSORS для обеспечения возможности повторного использования курсоров в сеансе. Тогда при поступлении повторных запросов на выполнение синтаксического анализа SQL-оператора, Oracle будет помещать курсор для этого оператора в кэш курсоров сеанса. А такое поведение, как было показано раньше, сокращает количество операций частичного синтаксического анализа. Значение для этого параметра нужно устанавливать среднее между значением параметра инициализации OPEN_CURSORS и количеством используемых в сеансе курсоров.
  • В-шестых, следует применять параметр CURSOR_SPACE_FOR_TIME (и устанавливать его в TRUE) для предотвращения раннего освобождения курсоров. Если использование большего объема памяти не представляет проблемы, применение этого параметра позволит улучшить степень масштабируемости приложения.
  • В-седьмых, лучше сокращать количество входов и выходов пользователей из сеанса. Это может негативно сказываться на масштабируемости из-за того, что с аутентификацией пользователя, проверкой привилегий и тому подобным связано больше накладных расходов, что, следовательно, приводит к большей трате времени и ресурсов. Более того, пользователи могут тратить больше времени на осуществление входа в систему, чем на выполнение своих SQL-операторов. Кроме того, частое осуществление выхода и входа обратно может приводить к возникновению состязаний за ресурсы веб-сервера и другие подобные ресурсы и, следовательно, увеличивать затрачиваемое на это время.
  • В-восьмых, нужно также обязательно обеспечивать для приложений возможность разделения сеансов. В случае разделения только SQL-операторов показатели по количеству операций полного синтаксического анализа будут уменьшаться, но показатели по количеству операций частичного синтаксического анализа могут все равно оставаться высокими. Если приложение способно поддерживать постоянное соединение с сервером Oracle, ему совершенно не обязательно выполнять повторную операцию частичного синтаксического анализа для повторного использования кода.

 

Установка размера для разделяемого пула

Наилучшим способом установки размера для разделяемого пула в Oracle Database 11g является предоставление Oracle возможности делать все самостоятельно за счет использования параметра инициализации MEMORY_TARGET и автоматизации управления областью SGA. Для параметра SGA_TARGET первоначально можно устанавливать значение, близкое тому общему объему, который бы выделялся для SGA при ручном режиме управления. Что касается параметра MEMORY_TARGET, то инструкции по установке первоначального значения можно найти вот в этом блоге.

 

Закрепление объектов в разделяемом пуле

Как уже объяснялось, при необходимости повторно подвергать объекты кода полному синтаксическому анализу и выполнению, производительность базы данных будет постепенно ухудшаться. Поэтому нужно обязательно следить за тем, чтобы как можно больше кода оставалось в памяти; тогда скомпилированный код сможет просто выполняться повторно. Избегать повторной загрузки объектов в библиотечный кэш можно за счет их закрепления (pinning) в этом кэше с помощью пакета DBMS_SHARED_POOL. (Библиотечный кэш, как упоминалось ранее, является компонентом разделяемого пула.) В листинге 4 показано, как определять объекты, подлежащие закреплению в библиотечном кэше (и, соответственно, в разделяемом пуле).


 

SELECT type, COUNT(*) OBJECTS,
2 SUM(DECODE(KEPT,'YES',1,0)) KEPT,
3 SUM(loads) - count(*) reloads
4 FROM V$DB_OBJECT_CACHE
5 GROUP BY type
6* ORDER BY objects DESC;
TYPE                            OBJECTS       KEPT    RELOADS
---------------------------- ---------- ---------- ----------
CURSOR                            41143          0     136621
NOT LOADED                        37522          0      54213
TABLE                               758         24     133742
PUB_SUB                             404          0        135
SYNONYM                             381          0       7704
JAVA CLASS                          297        296        317
VIEW                                181          0      11586
INVALID TYPE                        139         48         11
PACKAGE                             137          0       8352
TRIGGER                             136          0       8515
PACKAGE BODY                        121          0        218
SEQUENCE                             81          0       3015
INDEX                                61          7          0
PROCEDURE                            41          0        219
FUNCTION                             35          0        825
NON-EXISTENT                         31          0       1915
TYPE                                 13          0       1416
CLUSTER                              10          6          6
TYPE BODY                             3          0          5
LIBRARY                               2          0         99
RSRC CONSUMER GROUP                   2          0          0
QUEUE                                 2          0         96
JAVA SHARED DATA                      1          1          0
JAVA SOURCE                           1          0          0
24 rows selected.


Если показатель по количеству перезагрузок является высоком, как в выводе, приведенном в листинге 4, необходимо с помощью следующей команды сделать так, чтобы объекты закреплялись в разделяемом пуле: 

EXECUTE SYS.DBMS_SHARED_POOL.KEEP(object_name,object_type);

Для сначала закрепления пакета в разделяемом пуле, а потом его удаления, если необходимо, можно использовать следующие операторы:

EXECUTE SYS.DBMS_SHARED_POOL.KEEP(NEW_EMP.PKG, PACKAGE);
EXECUTE SYS.DBMS_SHARED_POOL.UNKEEP(NEW_EMP.PKG,PACKAGE); 

Разумеется, в случае останова и перезапуска базы данных закрепленные объекты в разделяемом пуле оставаться не будут. Именно поэтому большинство администраторов баз данных предпочитают использовать для всех подлежащих закреплению в разделяемом пуле объектов сценарии и настраивать их так, чтобы они выполнялись сразу же после каждого запуска базы данных. Например, автор сам закрепляет подобным образом все пакеты, поставляемые Oracle пакеты PL/SQL в том числе.

Ниже приведен пример, который дает преставление об общем объеме памяти, занимаемом большим количеством пакетов в системе автора. В частности, первый запрос в этом примере показывает, сколько всего пакетов используется в базе данных автора: 

SELECTCOUNT(*)
2 FROM V$DB_OBJECT_CACHE
3* WHERE type='PACKAGE';
COUNT(*)
---------------
167

Второй запрос показывает, сколько в общем памяти требуется для закрепления всех пакетов автора в разделяемом пуле:

SELECT SUM(sharable_mem)
2 FROM V$DB_OBJECT_CACHE
3* WHERE type='PACKAGE';
SUM(SHARABLE_MEM)
-----------------
4771127

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

 

Настройка кэша буферов

Когда пользователи запрашивают данные, Oracle считывает их с дисков (по блокам) и сохраняет в кэше буферов, чтобы иметь возможность легко получать к ним доступ, если потребуется. С уменьшением необходимости в данных Oracle постепенно удаляет их из кэша буферов и тем самым высвобождает место под более новые данные. Обратите внимание на то, что в некоторых операциях кэш буферов (SGA) не используется; вместо этого считывание выполняется прямо из области PGA. Примерами таких операций могут служить операции сортировки и операции параллельного чтения.

 

Установка размера для кэша буферов

Как и для разделяемого пула, для управления размером кэша буферов лучше всего выбирать вариант автоматического управления SGA. В случае выбора варианта управления SGA вручную подбирать размер для кэша буферов можно методом проб и ошибок. В таком случае нужно сначала назначать пулу первоначальный объем памяти и затем наблюдать за коэффициентом попаданий в кэш буферов для выяснения того, насколько часто приложение извлекает данные из памяти, а не с диска. Терминология, применяемая для вычисления коэффициента попаданий в кэш, иногда может казаться немного запутанной. Ключевые термины, значение которых нужно понимать обязательно, объясняются ниже.

  • physical reads (операции физического чтения). Под этими операциями подразумеваются операции считывания блоков данных Oracle с диска. Выполнение чтения данных с диска обходится гораздо дороже чтения данных из памяти Oracle. Поэтому при выполнении запроса Oracle всегда сначала пытается извлечь данные не с диска, а из памяти, а точнее — из кэша буферов базы данных.
  • db block gets (операции получения блоков базы данных). Под этими операциями подразумеваются операции чтения содержимого кэша буферов с целью извлечения блоков в текущем режиме. Чаще всего они происходят во время модификации данных, когда Oracle требуется гарантия того, что обновляется самая недавняя версия блока. То есть когда база данных Oracle находит требуемые данные в кэше буферов базы данных, она проверяет, являются ли данные в блоках актуальными. Если пользователь изменил данные в кэше буферов, но еще не зафиксировал эти изменения, новые запросы к этим же данным не могут отражать подобных временных изменений. Если же данные в блоках буферов являются актуальными, операция извлечения каждого такого блока считается операцией db block get.
  • consistent gets (операции согласованного чтения). Под этими операциями подразумеваются операции чтения содержимого кэша буферов с целью извлечения блоков в согласованном режиме. Они могут включать в себя считывание сегментов отката для соблюдения принципа согласованного чтения (о котором более подробно рассказывалось в этом блоге). В случае обнаружения, что с момента начала операции чтения какой-то другой сеанс обновил данные в этом блоке, Oracle применяет новую информацию из сегментов отката.
  • logical reads (операции логического чтения). Эти операции происходят всякий раз, когда Oracle удается удовлетворить запрос на получение данных путем их считывания из кэша буферов базы данных, и, следовательно, включают в себя как операции db block gets, так и операции consistent gets.
  • buffer gets (операции получения буферов). Под этими операциями подразумеваются операции извлечения ряда буферов из кэша буферов базы данных. Их значение совпадает со значением описывавшихся выше операций logical reads.

Ниже показана формула для вычисления коэффициента попаданий в кэш: 

1 - ('physical reads cache') /
('consistent gets from cache' + 'db block gets from cache')

Следующий запрос можно использовать для получения текущих значений по всем трем необходимым статистическим показателям кэша буферов:

SELECT name, value FROM v$sysstat
     WHERE where name IN ('physical reads cache',
                          'consistent gets from cache',
                          'db block gets from cache');
NAME                              VALUE
--------------------------- -----------
db block gets from cache      103264732
consistent gets from cache   5924585423
physical reads cache           50572618
3 rows selected.
 

Приведенный ниже подсчет, основанный на статистических данных, которые были получены в предыдущем коде из представления V$SYSSTAT, показывает, что коэффициент попаданий в кэш буфера в базе данных автора составляет чуть больше 91%: 

1 - (505726180)/(103264732 + 5924585494) = .916101734

По формуле для вычисления коэффициента попаданий в кэш буферов видно, что чем ниже процент операций physical reads по отношению к общему количеству операций logical reads, тем выше процент попаданий в кэш буферов.

Вдобавок для получения коэффициента попаданий в кэш буферов можно использовать представление V$BUFFER_POOL_STATISTICS, которое отображает список всех имеющихся у экземпляра пулов буферов: 

SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 - (PHYSICAL_READS/(DB_BLOCK_GETS + CONSISTENT_GETS)) "HitRatio"
FROM V$BUFFER_POOL_STATISTICS;
NAME     PHYSICAL_READS   DB_BLOCK_GETS   CONSISTENT_GETS    HitRatio
-------  ---------------  --------------  -----------------  ----------
DEFAULT  50587859         103275634       5924671178         .991607779

Кроме того, еще также можно использовать предлагаемый в Database Control компонент Memory Advisor (Советник по настройке размера структур памяти) для получения рекомендаций касательно оптимального размера для кэша буферов. Эти рекомендации приводится в графическом формате с отображением получаемого компромисса между увеличением SGA и сокращением времени БД. С помощью представления V$DB_CACHE_ADVICE, например, можно легко узнать, насколько требуется увеличить кэш буферов, чтобы снизить показатель по операциям физического ввода-вывода на определенное количество (для настройки размера SGA_TARGET следует применять представление V$SGA_TARGET_ADVICE). По сути, вывод представления V$DB_CACHE_ADVICE показывает, насколько можно увеличить память кэша буферов, прежде чем преимущества в плане сокращения (подсчитанного) количества операций физического чтения станут незначительными. Memory Advisor имитирует коэффициенты не попаданий в кэш буферов для кэшей различных размеров и тем самым может избавлять от выделения лишней памяти в тщетной попытке снизить количество операций physical reads в системе.

Блоки Oracle, используемые во время полного сканирования больших таблиц, хранятся в кэше буферов гораздо меньше, чем те, которые применяются во время полного сканирования небольших таблиц либо индексного доступа. Oracle может решить оставить в кэше буферов только какую-нибудь часть большой таблицы, чтобы избегать возникновения необходимости в сбросе всего содержимого кэша буферов. Это означает, что в случае выполнения нескольких операций полного сканирования больших таблиц, коэффициент попаданий в кэш буферов будет получаться низким искусственно. То есть, если в приложении по какой-то причине часто имеют место операции полного сканирования таблиц, увеличение размера кэша буферов не будет приводить к улучшению производительности. Некоторые администраторы баз данных рьяно пытаются добиваться высокого коэффициента попаданий в кэш, составляющего, например, 99% или около того. Однако высокий коэффициент попаданий в кэш буферов совершенно не гарантирует того, что у приложения также будут высокие показатели времени отклика и производительности. При наличии большого количества операций полного сканирования таблиц или базы данных скорее типа хранилища данных, чем системы OLTP, коэффициент попаданий в кэш буферов может быть меньше 100%, и ничего плохо в этом нет. Если база данных состоит из неэффективного SQL-кода, в ней будет выполняться чрезмерно большое количество операций logical reads, дающих хороший показатель по коэффициенту попаданий в кэш буферов (например, 99,99%), но это совершенно не означает эффективную работу базы данных. Детальнее об этом можно прочитать в статье Кэрри Милсэпа (Cary Millsap), которая называется Why a 99%+ Database Buffer Cache Hit Ratio Is Not Ok (Почему составляющий 99% и выше показатель по коэффициенту попаданий в кэш может быть плохим) и доступна по адресу http://www.hotsos.com/e-library/abstract.php?id=6.

 

Использование нескольких пулов для кэша буферов

Выделять всю память кэша буферов одному пулу вовсе не обязательно. Как было показано в этой статье, можно использовать три отдельных пула: удерживающий буферный пул (keep buffer pool), рециклирующий буферный пул (recycle buffer pool) и принятый по умолчанию буферный пул (default buffer pool). Хотя применять удерживающий и принятый по умолчанию пулы не обязательно, лучше сконфигурировать все три пула, чтобы иметь возможность назначать им объекты на основании поддерживаемых ими схем доступа. В целом при использовании нескольких буферных пулов нужно следовать таким основным правилам.

  • Использовать рециклирующий пул для больших объектов, к которым нечасто получается доступ. Совершенно не нужно, чтобы такие объекты занимали много лишнего места в принятом по умолчанию пуле.
  • Применять удерживающий пул для небольших объектов, которые необходимо постоянно иметь в памяти.
  • Oracle автоматически использует принятый по умолчанию пул для всех объектов, которые не были назначены ни рециклирующему, ни удерживающему пулу.

Начиная с версии 8.1, в Oracle применяется механизм подсчета обращений (touch count) для подсчета количества раз, которое к объекту в кэше буферов получается доступ. Этот помогающий в управлении кэшем буферов алгоритм подсчета обращений немного отличается от традиционного модифицированного алгоритма LRU, который раньше применялся в Oracle для управления кэшем. При каждом получении доступа к буферу количество обращений увеличивается. Низкий показатель по количеству обращений свидетельствует о том, что блок нечасто используется повторно и, следовательно, понапрасну занимает место в кэше буферов базы данных. Большие объекты, которые имеют низкий показатель по количеству обращений, но занимают приличную часть места в кэше буферов, можно считать идеальными кандидатами на помещение в рециклирующий пул. В листинге 5 приведен запрос, показывающий, как выяснить объекты с низким показателем количества обращений. О количестве обращений в таблице x$bh, владельцем которой является пользователь SYS, свидетельствует значение в столбце TCH.


 

SELECT
2 obj object,
3 count(1) buffers,
4 (count(1)/totsize) * 100 percent_cache
5 FROMx$bh,
6 (select value totsize
7 FROM v$parameter
8 WHERE name ='db_block_buffers')
9 WHERE tch=1
10 OR (tch = 0 and lru_flag <10)
11 GROUP BY obj, totsize
12* HAVING (count(1)/totsize) * 100 > 5
    OBJECT  BUFFERS  PERCENT_CACHE
----------  -------  -------------
      1386    14288     5.95333333
      1412    12616     5.25666667
    613114    22459     9.35791667


Вывод приведенного выше запроса показывает, что три объекта, каждый из которых имеет низкий показатель по количеству обращений, занимают около 20% всего места в кэше буферов. Очевидно, что они являются прекрасными кандидатами на помещение в рециклирующий буферный пул. По сути, это позволит ограничить количество буферов, которое нечасто используемые блоки из этих трех таблиц смогут занимать в кэше буферов.

Посредством следующего запроса к представлению DBA_OBJECTS можно получить имена этих объектов: 

SELECT object_name FROM DBA_OBJECTS
2 WHERE object_id IN (1386,1412,613114);
OBJECT_NAME
-----------------
EMPLOYEES
EMPLOYEE_HISTORY
FINANCE_RECS

Далее эти три объекта остается только назначить рециклирующему буферному пулу. Похожими критериями можно пользоваться и при принятии решения о том, какие объекты следует помещать в удерживающий буферный пул. Предположим, что требуется закрепить в удерживающем пуле все объекты, которые занимают как минимум 25 буферов, и показатель по количеству обращений у которых в среднем составляет больше 5. В листинге 6 показан запрос, который можно выполнить в таком случае от имени пользователя SYS.


 

SELECT obj object,
2 count(1) buffers,
3 AVG(tch) average_touch_count
4 FROM x$bh
5 WHERE lru_flag = 8
6 GROUP BY obj
7 HAVING avg(tch) > 5
8* AND count(1) > 25;
    OBJECT    BUFFERS  AVERAGE_TOUCH_COUNT
---------- ---------- --------------------
   1349785         36                   67
4294967295         87            57.137931


Опять-таки, получить затем имена объектов, которые являются кандидатами на помещение в удерживающий буферный пул, можно запросом к представлению
DBA_OBJECTS.

Далее на простом примере показано, как назначать объекты конкретному буферному пулу (удерживающему и рециклирующему). Сначала необходимо сконфигурировать сами удерживающий и рециклирующий пулы в базе данные с помощью следующего набора параметров инициализации: 

DB_CACHE_SIZE=256MB
DB_KEEP_CACHE_SIZE=16MB
DB_RECYCLE_CACHE_SIZE=16MB

В этом примере размер удерживающего и рециклирующего пула составляет по 16 Мбайт у каждого. После создания удерживающего и рециклирующего пула им можно легко назначать объекты. Изначально все таблицы находятся в принятом по умолчанию кэше буферов, куда они помещаются автоматически, если только в операторе создания объекта не указывается помещать их в другое место.

Для назначения любой таблице или индексу конкретного типа кэша буферов можно использовать оператор ALTER TABLE. Например, назначить две следующих таблицы удерживающему и рециклирующему кэшу буферов можно так: 

ALTER TABLE test1 STORAGE (buffer_pool keep);
Table altered.
ALTER TABLE test2 STORAGE (buffer_pool recycle);
Table altered.


На заметку! Более детально об управлении буферами на основании показателей количества обращений можно узнать в интересной статье Крейга Шалахамера (Craig. A. Shallahamer) под названием All About Oracle’s Touch-Count Data Block Buffer Algorithm (Все об алгоритме буферизации блоков данных в Oracle на основании показателей по количеству обращений), которая доступна для загрузки по адресу http://resources.orapub.com/product_p/tc.htm.


 

Настройка большого пула, пула потоков и пула Java

В разделяемых серверных системах для предоставления памяти для сеанса, облегчения параллельного выполнения операций с буферами сообщений и выполнения резервного копирования с применением буферов дискового ввода-вывода в качестве дополнительного компонента SGA в основном используется большой пул (large pool). В Oracle рекомендуют использовать большой пул в случае применения разделяемых серверных процессов, чтобы иметь возможность удерживать фрагментацию данных в разделяемом пуле на низком уровне. В случае использования конфигураций с разделяемыми серверами большой пул должен быть сконфигурирован обязательно. Конфигурировать пул потоков (streams pool) имеет смысл только в случае применения технологии Oracle Streams, а заниматься настройкой пула Java — только в случае использования сложных Java-приложений.


На заметку! Устанавливать размер для большого пула следует на основании количества активных одновременных сеансов в среде с разделяемыми серверами. Следует запомнить, что в случае использования конфигурации с разделяемыми серверами и не указания большого пула, Oracle будет выделять память для разделяемых сеансов за пределами разделяемого пула.


 

Настройка памяти PGA

Каждому серверному процессу, обслуживающему клиента, выделяется область приватной памяти, называемая областью PGA, большая часть из которой посвящается интенсивным по потреблению памяти задачам вроде группировки (GROUP BY), упорядочения (ORDER BY), обобщения (ROLLUP) и хеш-соединения (HASH JOIN). Область PGA представляет собой неразделяемую область памяти, которая создается Oracle при запуске серверного процесса и автоматически освобождается по завершении данного сеанса. Операции наподобие сортировки в памяти и построения хеш-таблицы нуждаются в специализированных рабочих областях. Память, выделяемая под PGA, влияет на размер таких рабочий областей, предназначенных для выполнения специальных задач, таких как сортировка, и на то, насколько быстро система может их завершать. В следующих подразделах речь пойдет о выборе оптимального объема памяти для PGA.

 

Автоматическое управление памятью PGA

С точки зрения администратора баз данных в управлении выделением памяти PGA нет ничего сложного. Можно устанавливать пару базовых параметров и позволить Oracle автоматически управлять выделением памяти для отдельных рабочих областей. Перед этим потребуется сделать две вещи. Во-первых, нужно указывать в параметре PGA_AGGREGATE_TARGET лимит объема выделяемой памяти, и, во-вторых, необходимо с помощью представления V$PGA_TARGET_ADVICE настроить значение целевого объекта. Более подробно о решении обеих задач рассказываться в следующих подразделах.

 

Использование параметра PGA_AGGREGATE_TARGET

Параметр PGA_AGGREGATE_TARGET в файле init.ora задает максимальный лимит по общему объему, выделяемой для PGA памяти. В Oracle рекомендуют пользоваться следующими советами при указании значения для этого параметра.

В базе данных OLTP размер целевого объекта (TARGET) должен составлять от 16 до 20 процентов общего объема выделяемой Oracle памяти.

В базе данных DSS размер целевого объекта должен составлять от 40 до 70 процентов общего объема выделяемой Oracle памяти.

Перечисленные выше советы являются не более чем просто советами. Лучше всего определять оптимальное значение для параметра PGA_AGGREGATE_TARGET с использованием представления V$PGA_TARGET_ADVICE или V$PGASTAT; как именно — объясняется в следующем разделе.

 

Использование представления V$PGA_TARGET_ADVICE

После выделения первоначального объема памяти для области PGA можно использовать представление V$PGA_TARGET_ADVICE и настраивать ее размер более точно. Oracle заполняет это представление результатами имитаций различных рабочих нагрузок при разных размерах PGA. После этого к нему можно выполнять запрос показанным ниже образом: 

SELECT ROUND(pga_target_for_estimate/1024/1024) target_mb,
2 estd_pga_cache_hit_percentage cache_hit_perc,
3 estd overalloc_count
4* FROM V$PGA_TARGET_ADVICE;

На основе полученных из представления V$PGA_TARGET_ADVICE оценок потом можно легко выбирать для PGA оптимальный уровень объема памяти.

 

Установка значения для параметра PGA_AGGREGATE_TARGET

Необходимо запомнить, что объем памяти, предоставляемый через параметр PGA_AGGREGATE_TARGET, определяет эффективность операций сортировки и хеширования в базе данных. При наличии большого количества пользователей, выполняющих сложные операции сортировки или хеширования, значение параметра PGA_AGGREGATE_TARGET должно обязательно быть высоким. В случае указания для параметра SGA_TARGET, скажем, 2 Гбайт, экземпляр будет забирать эти 2 Гбайт из общей памяти ОС сразу же после его запуска. PGA_AGGREGATE_TARGET, однако, является просто целевым объектом. Поэтому Oracle не будет забирать всю назначаемую PGA_AGGREGATE_TARGET память при запуске экземпляра. Значение PGA_AGGREGATE_TARGET служит верхней границей в общей приватной или рабочей памяти, которую экземпляр может выделять всем сеансам вместе.

Идеальным вариантом для выполнения сортировок является их проведение полностью в памяти. Операция сортировки, которую Oracle реализует полностью в памяти, считается оптимальной. В случае установки для параметра PGA_AGGREGATE_TARGET слишком низкого значения, некоторые из данных сортировки будут записываться прямо на диск (во временное табличное пространство), потому что операции сортировки будут слишком большими, чтобы умещаться в памяти. Операция сортировки, при которой на диск вытесняется только часть данных, называется однопроходной сортировкой (onepass sort). Если экземпляру приходится выполнять большую часть сортировки на диске, а не в памяти, время отклика будет большим. К счастью, при наличии достаточного количества доступной памяти, проблемы, связанные с недостаточным размером памяти PGA (т.е. низким значением PGA_TARGET) можно легко отслеживать и устранять.

Анализировать использование PGA в базе данных можно с помощью приведенного ниже запроса. Столбец value показывает, сколько памяти (в байтах) в текущий момент используется различными пользователями. 

SELECT
2 s.value,s.sid,a.username
3 FROM
4 V$SESSTAT S, V$STATNAME N, V$SESSION A
5 WHERE
6 n.STATISTIC# = s.STATISTIC# and
7 name = 'session pga memory'
8 AND s.sid=a.sid
9* ORDER BY s.value;
     VALUE     SID  USERNAME
---------- -------  ---------
   5561632    1129  BSCOTT
   5578688    1748  VALAPATI
   5627168     878  DHULSE
   5775296     815  MFRIBERG
   5954848    1145  KWHITAKE
   5971904    1182  TMEDCOFF . . .

Важным показателем эффективности параметра PGA_TARGET является “коэффициент попаданий” в кэш, отображающийся в последней строке показанного ниже запроса к представлению V$PGASTAT:

 

SELECT * FROM V$PGASTAT;
NAME                                       VALUE    UNIT
-------------------------------------  ---------  ------
aggregate PGA target parameter          49999872   bytes
aggregate PGA auto target                4194304   bytes
global memory bound                      2499584   bytes
total PGA inuse                         67717120   bytes
total PGA allocated                    161992704   bytes
maximum PGA allocated                  244343808   bytes
total freeable PGA memory               16121856   bytes
PGA memory freed back to OS           6269370368   bytes
total PGA used for auto workarea               0   bytes
maximum PGA used for auto                6843392   bytes
total PGA used for manual workareas            0   bytes
maximum PGA used for manual workareas     530432   bytes
over allocation count                    1146281   bytes
processed                             4.4043E+10   bytes
extra bytes read/written              7744561152   bytes
cache hit percentage                       85.04 percent
16 rows selected.

В этом примере процент попаданий в кэш (PGA) составляет более 85%, что является достаточно хорошим показателем для приложения OLTP или хранилища данных. На самом деле, при наличии большой базы данных типа хранилища может вполне удовлетворить и более низкий процент попаданий в кэш PGA.

Анализировать эффективность PGA можно также путем выполнения запроса к представлению V$SQL_WORKAREA_HISTOGRAM. В этом представлении содержится информация о ряде рабочих областей, функционирующих с оптимальным, однопроходным (onepass) и многопроходным (multipass) размером памяти. Все эти рабочие области делятся на группы, оптимальное требование у которых варьируется от 0 до 1 Кбайт, от 1 до 2 Кбайт, от 2 до 4 Кбайт, и т.д. В листинге 7 показан пример выполнения запроса к представлению V$SQL_WORKAREA_HISTOGRAM.


 

SELECT
2 low_optimal_size/1024 "Low (K)",
3 (high_optimal_size + 1)/1024 "High (K)",
4 optimal_executions "Optimal",
5 onepass_executions "1-Pass",
6 multipasses_executions ">1 Pass"
7 FROM v$sql_workarea_histogram
8* WHERE total_executions <> 0;
   Low (K)    High (K)     Optimal      1-Pass      >1 Pass
----------  ----------  ----------  ----------  -----------
         2           4     7820241           0            0
        32          64           0           2            0
        64         128        9011           1            0
       128         256        4064          14            0 
       256         512        3782          13            0
       512        1024       18479          58            4
      1024        2048        3818          53            0
      2048        4096          79         241           67
      4096        8192           1         457           26
      8192       16384           0          11           44
     16384       32768           3           1            2
     32768       65536           0           2            0
     65536      131072           0           0            1
    131072      262144           0           0            1
14 rows selected.



В данном экземпляре подавляющее большинство сортировок было выполнено оптимальным образом и лишь несколько — однопроходным. Именно поэтому коэффициент попаданий в кэш PGA и составлял 85% в предыдущем примере. Ниже приведен пример экземпляра, испытывающего проблемы, о которых свидетельствует наличие значительного количества сортировок в одно- и многопроходной (>1 Pass) группе. В таком случае большинство клиентов будет жаловаться на то, что база данных работает слишком медленно.

Обратите внимание, что запрос выглядит точно так же, как и в предыдущем примере, а вывод — следующим образом:

   Low (K)   High (K)    Optimal     1-Pass    >1 Pass
---------- ---------- ---------- ---------- ----------
         2          4          2          3          0
         4          8          2          7          5
         8         16     129866          3         19
        16         32       1288         21          3
        64        128          2        180         61
       128        256          6          2         44
       256        512         44          0         16
       512       1024       1063          0         35
      1024       2048      31069         11         12
      2048       4096          0          0         18
      8192      16384        986         22          0
     16384      32768          0          0          2

Нетрудно заметить, что в этом примере присутствует значительное количество многопроходных сортировок и можно поспорить, что коэффициент попаданий в кэш будет низким, не более 70%. К счастью, все, что требуется сделать для того, чтобы повысить скорость работы данного экземпляра — это увеличить значение параметра PGA_AGGREGATE_TARGET, как показано ниже:

ALTER SYSTEM SET pga_aggregate_target=500000000;
System altered.

Новое представление V$PROCESS_MEMORY позволяет просматривать данные по динамическому использованию памяти PGA процессами Oracle и отображает показатели по использованию PGA каждым процессом в виде категорий типа Java, PL/SQL, OLAP и SQL. Ниже приведен пример выполнения к этому представлению простого запроса:

SELECT pid, category, allocated, used from v$process_memory;
PID  CATEGORY   ALLOCATED   USED
---- --------- ----------  -----
22   PL/SQL          2068    136
22   Other         360367
27   SQL            23908  15120
. . .

Представление V$PROCESS можно использовать для мониторинга за использованием PGA на уровне отдельных процессов. Если на сервере заканчивается память, удобно узнать, нельзя ли освободить какую-то часть памяти PGA для других целей. Ниже приведен запрос, посредством которого можно просматривать объем выделенной, используемой и свободной памяти SGA для каждого процесса, который в текущий момент подключен к экземпляру: 

SELECT program, pga_used_mem, pga_alloc_mem,
pga_freeable_mem,pga_max_mem V$PROCESS;

Следующий SQL-оператор служит для быстрого подсчета пропорционального соотношения рабочих областей после запуска экземпляра Oracle с точки зрения того, каким образом в них используется память PGA — оптимальным, однопроходным или многопроходным:

SELECT name PROFILE, cnt COUNT,
DECODE(total, 0, 0, ROUND(cnt*100/total)) PERCENTAGE
FROM (SELECT name, value cnt, (sum(value) over ()) total
FROM V$SYSSTAT
WHERE name like 'workarea exec%');
PROFILE                             COUNT  PERCENTAGE
------------------------------- ---------  ----------
workarea executions - optimal     7859595         100
workarea executions - onepass         853           0
workarea executions - multipass       145           0

В этом примере коэффициент попаданий в кэш PGA для оптимальных выполнений составляет 100%, что, конечно же, замечательно. Раньше администраторам баз данных Oracle приходилось тратить гораздо больше внимания на настройку компонента памяти SGA, поскольку настраивать память PGA в том формате, который доступен сегодня, стало возможно относительно недавно. Администраторам баз данных, отвечающим за работу приложений, в которых часто требуется выполнять сложные и объемные операции хеширования и сортировки, настоятельно рекомендуется внимательно следить за производительностью PGA. В настройке PGA нет ничего сложного, а результаты хорошей настройки PGA приводят к значительным улучшениям в производительности.

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

Кэши, копии  и управление памя...
Кэши, копии и управление памя... 3728 просмотров Дэн Wed, 03 Jan 2018, 17:03:54
Создание БД Oracle 12c с макси...
Создание БД Oracle 12c с макси... 2926 просмотров Андрей Васенин Mon, 20 Aug 2018, 13:43:20
Мониторинг Oracle через метрик...
Мониторинг Oracle через метрик... 3301 просмотров sepia Tue, 21 Nov 2017, 13:18:05
Оптимизация обработки запросов...
Оптимизация обработки запросов... 11115 просмотров Алексей Вятский Fri, 24 Nov 2017, 05:57:09
Войдите чтобы комментировать

MaxNiko аватар
MaxNiko ответил в теме #9105 07 июль 2018 15:45
Огромное спасибо за столь всеобъемлющую статью! Очень ценная инфа по настройке библиотечного кэша!))