1 ноября 2000 г.

Using Oracle Database Auditing to Tune Performance, Joe Johnson

Джой Джонсон

Источник: http://www.oracle.com/oramag/oracle/99-Nov/69dba.html

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

Когда большинство администраторов баз данных думают об аудите, обычно в голову лезут образы коварных ночных хакеров или шпионской корпоративной разведки. Сервер базы данных Oracle имеет много полезных возможностей аудита, позволяющих обнаруживать и предотвращать бесчестную деятельность, но эти механизмы могут быть также очень полезны для настройки. Они могут предоставить достоверную информацию о реальном функционировании системы и помочь администратору базы данных принять правильные решения по настройке. В этой статье обсуждаются некоторые способы использования аудита для настройки структуры основной памяти базы данных, системной глобальной области (SGA). Настройка компонентов SGA, в частности, разделяемого пула (shared pool) и кэша буферов базы данных (database-buffer cache), может воздасться сторицей.

SGA состоит из несколько структур, назначенных для управления всеми данными и контрольной информацией, которые охватывают весь экземпляр Oracle:

При опросе и чтении таблиц процессами базы данных, мало используемые элементы выталкиваются из этих двух структур, чтобы освободить место для новых, что осуществляется по алгоритму замещения наиболее давнего по использованию элемента (LRU). Повторное использование блока в памяти предпочтительнее, чем чтение его с диска, поскольку это во много раз быстрее, и большинство усилий по настройке, связанных с SGA, включают попытки максимально увеличить этот эффект.

Одним из подходов по настройке разделяемого пула является закрепление (pin) в памяти наиболее часто используемых PL/SQL-объектов для того, чтобы механизм LRU не удалил бы их преждевременно. Однако, выяснение того, какой объект является “часто используемым”, может быть весьма затруднительным, особенно если исходный код недоступен. То же относится и к кэшу буферов базы данных: лучше держать в памяти блоки часто используемых таблиц и индексов и выталкивать те, к которым обращаются редко. И вновь, определение таблиц и индексов, которые часто используются, представляет собой нелегкую задачу. Возможности аудита Oracle могут помочь определить часто используемые предложения и блоки, потом эту информацию можно использовать для закрепления таких предложений и блоков в SGA, что улучшит производительность.

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

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

/*Для библиотечного кэша: */

SELECT (SUM(PINS - RELOADS)) / SUM(PINS)

"Library Cache Hit Ratio"

FROM V$LIBRARYCACHE;

/* Для кэша словаря данных */

SELECT (SUM(GETS-GETMISSES-USAGE-FIXED))/SUM(GETS)

"Dict.Cache Hit Ratio"

FROM V$ROWCACHE;

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

SELECT * FROM V$SGASTAT WHERE NAME = 'free memory';

Именно для этой последней рекомендации может быть полезна информация, извлеченная с помощью возможностей аудита. Даже если приложение не использует свои собственные PL/SQL-объекты, в качестве варианта настройки все же стоит рассмотреть закрепление встроенных PL/SQL-пакетов. Следующий код вычисляет коэффициент коэффициент попаданияия для кэша буферов базы данных:

select

(1-(physical.value/(block_gets.value

+consist_gets.value)) )

"DB Buffer Cache Hit Ratio"

from v$sysstat physical,

v$sysstat block_gets,v$sysstat consist_gets

where physical.name = 'physical reads'

and block_gets.name = 'db block gets'

and consist_gets.name = 'consistent gets';

Как правило, коэффициент попадания для кэша буферов базы данных ниже 90%, но для OLTP систем – выше. Если коэффициент попадания вашей системы существенно ниже, можно использовать следующие рекомендации для его улучшения:

Обычно для настройки разделяемого пула и буфера базы данных используют утилиты, такие как UTLBSTAT.SQL и UTLESTAT.SQL и результирующий отчет REPORT.TXT, а также регистрацию PL/SQL-модулей при трассировании. Эти методы хорошо описаны в курсе Oracle Education's Performance Tuning Workshop и в нескольких книгах издательств Oracle Press и O'Reilly & Associates.

Однако, настройка производительности, основанная на информации из V$-представлений может быть затруднительной, и это связано с самой природой этих представлений. V$-представления – это динамические представления, отображающие статистику выполнения с момента запуска экземпляра, что означает, что информация может устаревать с течением времени, отображая статистику для событий, давно прошедших. Кроме того, все V$-представления очищаются при остановке экземпляра, что делает затруднительным длительное наблюдение – особенно в организациях, выполняющих ночное резервирование остановленной базы данных. Поэтому, используя V$-представления, можно остаться совсем без архива (если остановка БД выполняется каждую ночь) или, напротив, получить слишком много архивных данных, чтобы они были полезны (если БД никогда не останавливается). Хотя отчет REPORT.TXT может собирать статистику из V$ представлений за определенный период времени, он по-прежнему страдает от недостатка конкретной информации о том, к каким именно секторам и PL/SQL объектам выполнялись обращения в это время. (Смотри статью "Настройка дискового ввода/вывода в Oracle8" о том, как изменить эти скрипты и использовать их для настройки дискового ввода/вывода.)

Наблюдение, основанное на аудите, предоставляет ряд явных преимуществ при настройке SGA:

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

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

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

База данных хранит записи аудита в таблице аудита AUD$ (владельцем которой является SYS) и затем строит несколько DBA_ представлений на основе AUD$-таблицы для упрощения запросов. С точки зрения настройки нас интересует только одно представление, DBA_AUDIT_TRAIL.Листинг 1 содержит SQL запрос к DBA_AUDIT_TRAIL, а Таблица 1 отображает результат.

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

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

Например, предположим, что вы желаете закрепить пакет CALC_FICA пользователя PAYROLL, потому что к нему обращались более 200 раз за контролируемый период. Для закрепления пакета необходим пакет DBMS_SHARED_POOL. (Если в базе данных этот пакет не установлен, его можно создать, используя скрипт DBMSPOOL.SQL, находящийся в каталоге $ORACLE_HOME/rdbms/admin.) Этот скрипт нужно запустить один раз, присоединившись к БД, как пользователь SYS. Убедившись, что пакет DBMS_SHARED_POOL существует, используйте следующий синтаксис для закрепления:

SQL> ALTER PACKAGE PAYROLL.CALC_FICA COMPILE;

SQL> EXECUTE SYS.DBMS_SHARED_POOL.KEEP

('PAYROLL.CALC_FICA','P');

В этом случае P обозначает пакет. Другими допустимыми параметрами являются C для курсоров, R для триггеров и Q для последовательностей. Скомпилируйте пакет, перед тем как закреплять его, чтобы он находился в памяти перед закреплением. Аналогичную технологию можно использовать для закрепления в памяти часто используемых триггеров. Листинг 2 содержит SQL-скрипт, который автоматически закрепляет все PL/SQL-пакеты и триггеры, которые выполнялись более 300 раз; значение 300 можно изменить во фразе HAVING скрипта на значение более соответствующее вашим конкретным требованиям.

Убедитесь, что база данных успешно закрепила пакеты, используя следующий запрос:

SQL> SELECT OWNER, NAME, TYPE

FROM V$DB_OBJECT_CACHE

WHERE KEPT='YES';

Раз система закрепила пакет или триггер, механизм LRU никогда не удалит его из разделяемого пула, даже если администратор баз данных выполнит команду ALTER SYSTEM FLUSH SHARED_POOL. Однако система удаляет закрепленные объекты при остановке базы данных, поэтому необходимо вновь закрепить их, используя вышеуказанный синтаксис при каждом последующем запуске. Позаботьтесь о том, чтобы не переполнить общий пул закрепленными объектами: если вы закрепили большое количество PL/SQL-объектов, следите за уменьшением коэфициента попадания разделяемого пула и соответственно увеличивайте размер пула.

Закрепление таблиц и индексов в кэше буферов базы данных. Работая с Oracle8, можно использовать буферные пулы для закрепления часто используемых блоков таблиц и индексов в кэше буферов базы данных. Каждая SGA имеет по крайней мере один буферный пул – пул по умолчанию (default pool) – в кэше буферов базы данных. Вы можете также создать два других пула – хранения (keep) и повторно используемый (recycle) – в кэше буферов базы данных для хранения буферов данных, к которым обращаются соответственно часто и редко. Каждый из этих пулов управляется механизмом, называемым защелкой LRU (LRU latch), которая преграждает доступ к структурам кэша буферов и “старит” (age) в кэше буферы для LRU.

Данные аудита показывают, какие таблицы и индексы часто использовались в период наблюдения; именно эти таблицы нужно помещать в пул хранения. Перед использованием этих двух пулов, нужно установить несколько параметров в файле init.ora, как показано в Таблице 2.

Заметьте, что третий пул (по умолчанию) включает буферы, отсутствующие в пулах хранения и повторного использования. Его размер неявно определяется таким способом, а не через параметры файла init.ora. (Можно также определить только пул хранения, только пул повторного использования или ни один из них, оставляя все для пула по умолчанию).

DB_BLOCK_SIZE = 8192

DB_BLOCK_BUFFERS = 6000

DB_BLOCK_LRU_LATCHES = 6

BUFFER_POOL_KEEP = (BUFFERS:2000, LRU_LATCHES:1)

BUFFER_POOL_RECYCLE= (BUFFERS:1000, LRU_LATCHES:2)

В этом примере размер блока составляет 8K, а кэш буферов состоит из 6,000 копий этих 8-килобайтных блоков, пул хранения занимает 2,000 из 6,000 буферов кэша, повторно используемый пул - 1,000 из 6,000 буферов кэша. Таким образом, на пул по умолчанию остается 3,000 буферов.

В этом случае, для управления доступом к кэшу буферов базы данных используется шесть LRU-защелок: одна управляет буферами хранимого пула, две управляют буферами пула повторного использования, и оставшиеся четыре [так у автора – прим.ред.] управляют буферами пула по умолчанию. Максимальное допустимое количество разделяемого числа защелок (DB_BLOCK_ LRU_LATCHES) должно быть меньше, чем

Суммарное число защелок LRU, назначенных буферным пулам, не может превышать общего количества защелок LRU, минимальное количество буферов, которое можно назначить каждому буферному пулу, составляет 50, умноженное на количество защелок. Следовательно, попытка стартовать базу с параметром

BUFFER_POOL_KEEP= (BUFFERS:1000,LRU_LATCHES:30)

завершится сообщением об ошибке:

ORA-00378: buffer pools cannot be created as specified
“ORA-00378 буферный пул не может быть создан, как указано”,

поскольку это нарушает правило отношения буферов к защелкам, как 50:1.

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

SQL> ALTER TABLE payroll.withholding

STORAGE (BUFFER_POOL KEEP);

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

SQL> ALTER TABLE payroll.dependents

STORAGE (BUFFER_POOL RECYCLE);

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

select name,

(1-(physical_reads/(db_block_gets+consistent_gets)))

"Buffer Pool Hit Ratio"

from v$buffer_pool_statistics

where db_block_gets + consistent_gets != 0;

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

Поскольку механизм аудита постоянно записывает каждый доступ к каждому контролируемому объекту, журнал аудита может со временем существенно разрастись. В частности, необходимо контролировать основную журнальную таблицу—AUD$, которая хранится в табличном пространстве SYSTEM, чтобы предотвратить проблемы с памятью. Журналом аудита можно управлять либо:

Листинг 4 содержит пример скрипта, который копирует журнальные данные в архивную таблицу и затем очищает таблицу SYS.AUD$. Вообще-то АБД не должен удалять данные ни из какой из таблиц словаря данных в схеме SYS. Однако АБД с привилегиями SYS может безопасно удалить данные из таблицы AUD$ для того, чтобы управлять размером журнала аудита.

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

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

ТАБЛИЦА 1: Результаты запроса представления DBA_AUDIT_TRAIL.

ТАБЛИЦА 2: Параметры буферного кэша пула базы данных

Следующая статья: Включайте возможности аудита Oracle

Джой Джонсон (jjohnson@gr.com) - Oracle Certified Professional по администрированию баз данны. Он имеет более, чем шестилетним опытом работы с базами данных Oracle. В настоящее время он является главным администратором баз данных корпорации Greenbrier & Russel в Милуоки (Milwaukee). Он также работает главным инструктором в сертифицированных образовательных центрах Greenbrier & Russel .

Автор благодарит Филица Догана, главного продукт-менеджера Oracle Database Administration за реферирование этой статьи.

ЛИСТИНГ 1

Этот скрипт показывает, как часто происходили обращения к

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

set echo on

set feedback on

set pages 40

set verify on

column "Владелец объекта" format a30

column "Имя объекта" format a30

column "Тип объекта" format a15

column "Количество обращений" format 9,999,999,999

spool audit_count.lst

select a.owner "Владелец объекта",

a.obj_name "Имя объекта",

o.object_type "Тип объекта",

count(*) "Количество обращений"

from dba_audit_trail a, dba_objects o

where a.owner=o.owner

and a.obj_name=o.object_name

group by a.owner, a.obj_name, o.object_type

order by 1, 3, 4;

spool off

ЛИСТИНГ 2

Этот скрипт генерирует SQL операторы для автоматического закрепления

часто используемых объектов PL/SQL, обнаруженных в контролируемый период.

set echo off

set feedback off

set pages 0

set verify off

spool pin_pkg.tmp

select distinct

'ALTER '||o.object_type||' '||a.owner||'.'|| a.obj_name||' COMPILE;',

'EXECUTE DBMS_SHARED_POOL.KEEP('||''''||a.owner||'.'||a.obj_name||''''||','||''''||'P'||''''||');'

from dba_audit_trail a, dba_objects o

where a.owner=o.owner

and a.obj_name=o.object_name

and o.object_type = 'PACKAGE'

group by o.object_type, a.owner, a.obj_name

having count(*) > 300;

spool off

spool pin_tgr.tmp

select distinct

'ALTER '||o.object_type||' '||a.owner||'.'|| a.obj_name||' COMPILE;',

'EXECUTE DBMS_SHARED_POOL.KEEP('||''''||a.owner||'.'||a.obj_name||''''||','||''''||'R'||''''||');'

from dba_audit_trail a, dba_objects o

where a.owner=o.owner

and a.obj_name=o.object_name

and o.object_type = 'TRIGGER'

group by o.object_type, a.owner, a.obj_name

having count(*) > 300;

spool off

set echo on

set feedback on

set pages 40

set verify on

spool pin_pkg_tgr.lst

@pin_pkg.tmp

@pin_tgr.tmp

spool off

ЛИСТИНГ 3

Этот скрипт генерирует SQL операторы для автоматического закрепления

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

set echo off

set feedback off

set pages 0

set verify off

spool keep_tabs.tmp

select distinct

'ALTER '||o.object_type||' '||a.owner||'.'|| a.obj_name||chr(10)||

'STORAGE (BUFFER_POOL KEEP);'

from dba_audit_trail a, dba_objects o

where a.owner=o.owner

and a.obj_name=o.object_name

and o.object_type = 'TABLE'

group by o.object_type, a.owner, a.obj_name

having count(*) > 300;

spool off

spool keep_idxs.tmp

select distinct

'ALTER '||o.object_type||' '||a.owner||'.'|| a.obj_name||chr(10)||

'STORAGE (BUFFER_POOL KEEP);'

from dba_audit_trail a, dba_objects o

where a.owner=o.owner

and a.obj_name=o.object_name

and o.object_type = 'INDEX'

group by o.object_type, a.owner, a.obj_name

having count(*) > 300;

spool off

set echo on

set feedback on

set pages 40

set verify on

spool keep_tabs_idxs.lst

@keep_tabs.tmp

@keep_idxs.tmp

spool off

ЛИСТИНГ 4

Этот скрипт архивирует и затем очищает журнал аудита.

set echo off

set feedback off

set pages 0

set verify off

spool audit_history.tmp

select 'create table audit_history_'||to_char(sysdate,'mmddyy')||' as

select a.owner "OWNER", a.obj_name "OBJECT_NAME",

o.object_type "OBJECT_TYPE", count(*) "NUM_ACCESSES"

from dba_audit_trail a, dba_objects o

where a.owner=o.owner

and a.obj_name=o.object_name

group by a.owner, a.obj_name, o.object_type;'

from dual;

spool off

set echo on

set feedback on

set pages 40

set verify on

spool audit_history.lst

@audit_history.tmp

truncate table sys.aud$;

spool off