Хорошо известным фактом о производительности системы является то, что выборка данных, хранящихся в памяти, производится гораздо быстрее, чем извлечение данных с дискового устройства хранения. Зная об этом, база данных 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.
Синтаксический анализ и масштабирование приложений
Когда количество пользователей продолжает расти, в некоторых системах начинают возникать проблемы. В частности, во многих системах из-за попыток подстроиться под постоянно увеличивающееся количество пользователей значительно снижается скорость работы. Поэтому когда количество пользователей растет, необходимо обращать особое внимание на выполнение в системе ненужных операций синтаксического анализа. Частое проведение синтаксического анализа влечет за собой возникновение состязаний за получение защелок, что тормозит работу системы. Ниже перечислены кое-какие рекомендации, суммирующие все обсуждавшиеся выше моменты касательно библиотечного кэша, синтаксического анализа и использования специальных параметров инициализации.
- Во-первых (что является стандартным правилом), нужно стараться подавать как можно больше кода в виде хранимого кода, т.е. в виде пакетов, процедур и функций, чтобы не иметь проблем, вызываемых нерегламентированными (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 приводят к значительным улучшениям в производительности.