Использование средств автоматической настройки баз данных Oracle9i

2003 г

Дональд K. Бурлесон,
BEI Oracle Consulting

Использование средств автоматической настройки баз данных Oracle9i
(Using the self-tuning features of Oracle9i,
by Donald K. Burleson, BEI Oracle Consulting, Don@Burleson.cc)

Источник: Oracle OpenWorld, San Francisco, December 2001
(http://www.oracle.com/pls/oow/oow_user.show_public?p_event=6&p_type=session&p_id=8851).

Перевод под ред. к.т.н. А.П.Соколова, РДТЕХ. Впервые опубликован в Oracle Magazine RE

Средства динамического распределения памяти СУБД Oracle9i позволяют создавать самонастраивающиеся базы данных. В данной статье рассматривается использование пакета STATSPACK для мониторинга и настройки (в зависимости от потребностей обработки в сервере и базе данных) зон памяти, размеры которых задаются следующими параметрами: sort_area_size (размер области сортировки), large_pool_size (размер большого пула), pga_aggregate_target (максимальная суммарная память PGA), sga_max_size (максимальный размер SGA) и db_cache_size (размер пула буферов). Мы также рассмотрим мониторинг с помощью пакета STATSPACK использования зон памяти и создание интеллектуального механизма для автоматического реконфигурирования Oracle9i в зависимости от текущих потребностей обработки.

Серьезной проблемой в Oracle8i было требование: все выделенные соединения должны использовать области сортировки одинакового размера, задаваемого параметром sort_area_size. В Oracle9i имеется возможность автоматического управления распределением памяти PGA. В Oracle введен новый параметр файла init.ora – pga_aggregate_target. Если он установлен и используются выделенные (dedicated) соединения с Oracle, Oracle9i будет игнорировать все параметры PGA, задаваемые в файле init.ora, включая sort_area_size и sort_area_retained_size (размер памяти, удерживаемой после завершения сортировки). Корпорация Oracle рекомендует устанавливать значение параметра pga_aggregate_target, равное объему памяти, оставшейся свободной в сервере UNIX после запуска экземпляра (минус 20% на другие задачи ОС UNIX). См. рис. 1.

Рис 1. Определение значения параметра pga_aggregate_target в сервере UNIX.

После установки параметра pga_aggregate_target Oracle будет автоматически управлять распределением памяти PGA, основываясь на конкретных потребностях каждого соединения с Oracle. В Oracle9i также можно динамически модифицировать параметр pga_aggregate_target на уровне экземпляра с помощью оператора alter system, поэтому АБД может динамически управлять распределением памяти, доступной Oracle9i.

В Oracle9i появился также еще один новый параметр – workarea_size_policy (политика установки размеров рабочих областей). Если в этом параметре установлено AUTO (автоматический режим), Oracle будет пытаться максимизировать количество рабочих областей, используемых для оптимального (optimal) режима их обработки, а размер других рабочих областей будет пытаться задавать достаточным для однопроходного (one-pass) режима обработки. Если в параметре workarea_size_policy установлено MANUAL (ручной режим), соединениям будет выделяться память в соответствии с установленным значением параметра sort_area_size.

Новые представления Oracle9i для автоматического управления памятью PGA

В Oracle9i появилось несколько новых представлений и новых столбцов в существующих представлениях, которые показывают внутреннее распределение памяти в Oracle9i. Для мониторинга использования памяти выделенными соединениями Oracle9i можно использовать следующие v$-представления:

v$process – в Oracle9i для мониторинга использования памяти PGA добавлено три новых столбца: pga_used_mem (используемая память PGA), pga_alloc_mem (выделенная память PGA) и pga_max_mem (максимальная память, когда-либо выделенная процессу).
v$sysstat – добавлено много новых статистик, включая статистики использования рабочих областей для оптимального, однопроходного и многопроходного (multi-pass) режимов их обработки.
v$pgastat – это новое представление показывает внутренние статистики использования памяти PGA для всех фоновых процессов и выделенных соединений.
v$sql_plan – это существующее представление содержит информацию о планах выполнения всех выполняемых в данное время операторах SQL. Хорошее инструментальное средство по оптимизации производительности для профессионалов, которые должны локализовать неоптимальные операторы SQL.
v$workarea – это новое представление выдает детализированные суммарные статистики использования памяти соединениями с Oracle9i.
v$workarea_active – это новое представление показывает внутреннюю информацию об использовании памяти всеми операторами SQL, выполняемыми в данное время.

Рассмотрим более подробно новые средства Oracle9i и скрипты, которые позволяют разобраться в деталях использования памяти PGA.

Использование представления v$sysstat в Oracle9i

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

Work_area.sql

select
   name profile, 
   cnt, 
   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                             CNT        PERCENTAGE
----------------------------------- ---------- ----------
workarea executions – optimal             5395         95
workarea executions – onepass              284          5
workarea executions – multipass              0          0

АБД может использовать этот запрос для определения, когда нужно динамически изменить значение параметра pga_aggregate_target. В общем, значение pga_aggregate_target нужно увеличивать, если процент количества выполнений в многопроходном режиме (workarea executions – multipass) больше 0, и уменьшать, если процент количества выполнений в оптимальном режиме (workarea executions – optimal) равен 100%.

Использование представления v$pgastat в Oracle9i

Представление v$pgastat содержит суммарные статистики (на уровне экземпляра) использования PGA и работы автоматического диспетчера памяти (automatic memory manager). Для выдачи суммарных статистик для всех соединений с Oracle9i можно использовать следующий скрипт:

check_pga.sql

column name  format a30
column value format 999,999,999

select
   name, 
   value 
from
   v$pgastat
;

Вывод этого запроса может быть примерно следующим:

NAME                                                   VALUE     
------------------------------------------------------ ----------
aggregate PGA auto target                             736,052,224
global memory bound                                        21,200
total expected memory                                     141,144
total PGA inuse                                        22,234,736
total PGA allocated                                    55,327,872
maximum PGA allocated                                  23,970,624
total PGA used for auto workareas                         262,144
maximum PGA used for auto workareas                     7,333,032
total PGA used for manual workareas                             0
maximum PGA used for manual workareas                           0
estimated PGA memory for optimal                          141,395
maximum PGA memory for optimal                        500,123,520
estimated PGA memory for one-pass                         534,144
maximum PGA memory for one-pass                        52,123,520

В этом выводе из v$pgastat мы видим следующие статистики:

Aggregate PGA auto target – суммарный объем памяти, доступной для соединений с Oracle9i. Это значение устанавливается в соответствии со значением параметра pga_aggregate_target в файле init.ora.
Global memory bound – максимальный размер рабочей области. Если значение этой статистики не превышает одного мегабайта, корпорация Oracle рекомендует увеличивать значение параметра pga_aggregate_target.
Total PGA allocated – маркер максимального заполнения (high-water mark) всей памяти PGA в базе данных. По мере увеличения использования PGA значение этой статистики приближается к значению pga_aggregate_target.
Total PGA used for auto workareas – использование памяти всеми соединениями, работающими в режиме автоматического распределения памяти. Помните, не все внутренние процессы могут работать в этом режиме. Например, память, выделяемая для процедур Java и PL/SQL, не учитывается в этой статистике (для определения объема этой памяти и памяти, используемой соединениями, которые не работают в режиме автоматического распределения памяти, нужно значение этой статистики вычесть из значения статистики total PGA allocated).
Estimated PGA memory for optimal/one-pass – оценка объема памяти, требуемой для выполнения операций для всех соединений в оптимальном/однопроходном режимах обработки. Помните, при нехватке памяти Oracle9i будет использовать многопроходной режим обработки. Эта статистика имеет большое значение для мониторинга использования памяти в Oracle9i, и большинство АБД будет увеличивать значение параметра pga_aggregate_target до значения этой статистики.

Расширение представления v$process в Oracle9i

В представление v$process добавлено несколько новых столбцов, показывающих автоматическое выделение процессам памяти PGA, включая столбцы pga_used_mem, pga_alloc_mem и pga_max_mem. Запрос, выдающий значения этих столбцов:

select
   program, 
   pga_used_mem, 
   pga_alloc_mem, 
   pga_max_mem 
from
   v$process;

Вывод этого запроса может быть примерно следующим:

PROGRAM                        PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
------------------------------ ------------ ------------- -----------
PSEUDO                                    0             0           0
oracle@janet (PMON)                  120463        234291      234291
oracle@janet (DBW0)                 1307179       1817295     1817295
oracle@janet (LGWR)                 4343655       4849203     4849203
oracle@janet (CKPT)                  194999        332583      332583
oracle@janet (SMON)                  179923        775311      775323
oracle@janet (RECO)                  129719        242803      242803
oracle@janet (TNS V1-V3)            1400543       1540627     1540915
oracle@janet (P000)                  299599        373791      635959
oracle@janet (P001)                  299599        373791      636007
oracle@janet (TNS V1-V3)            1400543       1540627     1540915
oracle@janet (TNS V1-V3)              22341       1716253     3625241

Здесь мы видим выделенную (pga_alloc_mem), используемую (pga_used_mem) и максимальную (pga_max_mem) память для всех соединений с Oracle. Мы видим запросы памяти для всех фоновых процессов, а также для индивидуальных соединений.

Заметим, запросы памяти конкретными соединениями можно анализировать более детально, соединяя представление v$process с таблицей v$sql_plan.

Использование представления v$sql_workarea_active в Oracle9i

Два новых представления показывают активное пространство рабочих областей: v$sql_workarea и v$sql_workarea_active. Представление v$sql_workarea_active содержит информацию о всех рабочих областях, активных в экземпляре в данный момент. Заметим, небольшие сортировки (меньше 65 535 байтов) из представления исключены.

select
   to_number(decode(SID, 65535, NULL, SID)) sid,
   operation_type              OPERATION,
   trunc(WORK_AREA_SIZE/1024)  WSIZE, 
   trunc(EXPECTED_SIZE/1024)   ESIZE,
   trunc(ACTUAL_MEM_USED/1024) MEM, 
   trunc(MAX_MEM_USED/1024)    "MAX MEM", 
   number_passes               PASS
from
   v$sql_workarea_active
order by
   1,2;

Пример вывода этого запроса:

SID OPERATION             WSIZE     ESIZE       MEM   MAX MEM PASS
--- --------------------- ----- --------- --------- --------- ----
 27 GROUP BY (SORT)          73        73        64        64    0
 44 HASH-JOIN              3148      3147      2437      6342    1
 71 HASH-JOIN             13241     19200     12884     34684    1

Здесь видно, что в сеансе 44 (см. столбец SID) выполняется хеш-соединение (hash-join) и его рабочая область обрабатывается в однопроходном режиме (столбец PASS). В этой рабочей области в данное время используется 2 мегабайта памяти PGA (столбец MEM), а до этого было использовано до 6.5 мегабайтов памяти PGA (столбец MAX MEM).

Это представление очень полезно для анализа текущих операций с рабочими областями, для получения более подробной информации о сеансах это представление можно соединять с представлениями v$process и v$session, используя для этого столбец SID.

Анализ использования памяти для конкретных операторов SQL

В Oracle9i можно получать информацию об использовании памяти вместе с информацией о планах выполнения. Для этого по представлению v$sql нужно сначала определить адрес требуемого оператора. Например, если запрос работает с таблицей NEW_CUSTOMER, для определения адреса можно выполнить следующий запрос:

select
   address
from
   v$sql
where
   sql_text like ‘%NEW_CUSTOMER’;

88BB460C

1 row selected.

Теперь у нас есть адрес и мы можем вставить его в следующий скрипт для извлечения информации о плане выполнения и использовании памяти PGA для данного оператора SQL.

plan_mem.sql

select
   operation, 
   options, 
   object_name                        name,
   trunc(bytes/1024/1024)             "input(MB)",
   trunc(last_memory_used/1024)       last_mem,
   trunc(estimated_optimal_size/1024) opt_mem, 
   trunc(estimated_onepass_size/1024) onepass_mem, 
   decode(optimal_executions, null, null, 
          optimal_executions||'/'||onepass_executions||'/'||
          multipasses_exections)      "O/1/M"
from
   v$sql_plan     p,
   v$sql_workarea w
where
   p.address=w.address(+)
and 
   p.hash_value=w.hash_value(+) 
and 
   p.id=w.operation_id(+) 
and 
   p.address='88BB460C';

Вывод этого скрипта:

OPERATION    OPTIONS  NAME input(MB) LAST_MEM OPT_MEM ONEPASS_MEM O/1/M 
------------ -------- ---- --------- -------- ---------- ---------- ----
SELECT STATE                                                                  
SORT         GROUP BY           4582        8         16         16 26/0/0
HASH JOIN    SEMI               4582     5976       5194       2187 16/0/0
TABLE ACCESS FULL     ORDERS      51                                      
TABLE ACCESS FUL      LINEITEM  1000

Эта информация о плане выполнения и использовании памяти PGA – новое достижение в Oracle9i, которое позволяет АБД получать подробную информацию о внутреннем выполнении операторов SQL.

Переход к самонастраивающейся базе данных Oracle9i

Новые возможности динамического управления SGA в Oracle9i позволяют использовать архитектуру, при которой АБД Oracle может выполнять мониторинг использования памяти в ОС UNIX и реконфигурировать SGA и зоны памяти PGA в зависимости от текущих профилей использования.

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

Сейчас АБД Oracle может динамически перераспределять память.

Изменение конфигурации памяти скриптами ОС UNIX

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

Как уже было отмечено, базы данных в режиме OLTP должны иметь высокое значение параметра db_cache_size, а задачи, интенсивно использующие память, должны иметь высокое значение параметра pga_aggregate_target.

Приведенные ниже скрипты UNIX могут быть использованы для реконфигурирования SGA без остановки экземпляра. В этом примере мы предполагаем, что у нас отдельный сервер Oracle с 8 гигабайтами памяти, 20% которой мы резервируем для UNIX, оставляя 6 гигабайтов для СУБД Oracle и соединений с Oracle. Эти скрипты предназначены для работы в ОС HP/UX или Solaris, в качестве аргумента в них задается $ORACLE_SID.

Скрипт dss_config.ksh будет запускаться каждый вечер в 6:00 для реконфигурирования Oracle для работы в режиме DSS (запуск задач, интенсивно использующих память).

dss_config.ksh

#!/bin/ksh

# First, we must set the environmnt ...
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

$ORACLE_HOME/bin/sqlplus –s /nologin<<!
connect system/manager as sysdba;
alter system set db_cache_size=1500m;
alter system set shared_pool_size=500m;
alter system set pga_aggregate_target=4000m;
exit
!

Скрипт oltp_config.ksh будет запускаться каждое утро в 6:00 для реконфигурирования Oracle для работы в режиме OLTP.

oltp_config.ksh

#!/bin/ksh

# First, we must set the environmnt ...
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

$ORACLE_HOME/bin/sqlplus –s /nologin<<!
connect system/manager as sysdba;
alter system set db_cache_size=4000m;
alter system set shared_pool_size=500m;
alter system set pga_aggregate_target=1500m;
exit
!

Замечание: для планирования этих событий реконфигурирования можно использовать пакет dbms_job.

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

На пути к созданию самонастраивающихся баз данных

Oracle9i развивается в направлении создания полностью самонастраивающейся архитектуры, но АБД Oracle несут ответственность за настройку конфигурации памяти в соответствии с характером ее использования. В общем, для определения времени изменения характеристик работы можно использовать запросы v$-представлений и пакет STATSPACK. Мы видим три подхода к автоматизации настройки:

Обычное планируемое реконфигурирование. Реконфигурирование памяти SGA и PGA полезно для экземпляра, работающего в двух режимах (OLTP и DSS).
Динамическое реконфигурирование на основании анализа трендов. Для прогнозирования времени изменения характеристик работы можно использовать пакет STATSPACK, а для реконфигурирования памяти SGA и PGA – пакет dbms_job.
Динамическое реконфигурирование. Так же как Oracle9i динамически перераспределяет память зоны pga_aggregate_target, АБД Oracle может написать скрипты, которые освобождают неиспользуемую память одних областей и передают ее другим областям

Правила изменения размеров памяти

Существует три условия, влияющие на принятие решения об изменении размеров зон памяти Oracle: одно - для кеша буферов, другое - для разделяемого пула, третье - для памяти PGA:

db_cache_size – мы можем захотеть увеличить размер кеша буферов, если значение коэффициента попаданий в кеш буферов падает ниже какого-то предопределенного порогового значения;
shared_pool_size – высокое значение коэффициентов непопаданий в любой кеш разделяемого пула может сигнализировать о необходимости увеличения размера разделяемого пула;
pga_aggregate_target – мы можем захотеть увеличить размер доступной памяти PGA, если обнаружено большое количество выполнений в режиме многопроходной обработки.

Рассмотрим каждое условие более подробно.

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

если значение статистики “estimated PGA memory for one-pass” (оценка объема памяти, требуемой для выполнения операций в однопроходном режиме обработки) в представлении v$sysstat превышает значение параметра pga_aggregate_target, значение этого параметра можно увеличить;
если значение статистики “workarea executions – multipass” (количество операций обработки в многопроходном режиме) превышает 1%, значение параметра pga_aggregate_target можно увеличить;
вы можете уменьшить значение параметра pga_aggregate_target, если значение статистики “workarea executions – optimal” (количество операций обработки в оптимальном режиме) постоянно равно 100%.

Изменение значения параметра shared_pool_size

По опыту работы с Oracle8 мы знаем, что для определения правильности установки размера разделяемого пула можно использовать несколько запросов. Коэффициент непопаданий в библиотечный кеш (library cache miss ratio), представляющий собой отношение количества перезагрузок библиотечного кеша (library cache reloads) к количеству попаданий (pins), позволяет определить необходимость изменения размеров разделяемого пула.

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

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

rpt_lib_miss.sql

set lines 80;
set pages 999;

column mydate heading 'Yr.  Mo Dy  Hr.' format a16
column c1 heading "execs"    format 9,999,999
column c2 heading "Cache Misses|While Executing"    format 9,999,999
column c3 heading "Library Cache|Miss Ratio"     format 999.99999

break on mydate skip 2;

select 
   to_char(snap_time,'yyyy-mm-dd HH24')  mydate,
   sum(new.pins-old.pins)                c1,
   sum(new.reloads-old.reloads)          c2,
   sum(new.reloads-old.reloads)/
   sum(new.pins-old.pins)                library_cache_miss_ratio
from 
   stats$librarycache old,
   stats$librarycache new,
   stats$snapshot     sn
where
   new.snap_id = sn.snap_id
and
   old.snap_id = new.snap_id-1
and
   old.namespace = new.namespace
group by
   to_char(snap_time,'yyyy-mm-dd HH24')
;

Вывод из скрипта показан ниже. Сам скрипт легко приспособить для оповещения АБД о чрезмерной количестве непопаданий в библиотечный кеш.

                               Cache Misses
Yr.  Mo Dy  Hr.       execs While Executing LIBRARY_CACHE_MISS_RATIO
---------------- ---------- --------------- ------------------------
2001-12-11 10        10,338               3                   .00029
2001-12-12 10       182,477             134                   .00073
2001-12-14 10       190,707             202                   .00106
2001-12-16 10         2,803              11                   .00392

Пакет STATSPACK позволяет выдавать детализированные отчеты о поведении объектов библиотечного кеша. В этом примере ясно видно: нехватка памяти библиотечного кеша наблюдается каждое утро с 9:00 до 10:00. В таком случае мы можем на этот период времени динамически реконфигурировать библиотечный кеш, увеличив его размер (параметр shared_pool_size) за счет уменьшения размера кеша буферов (параметр db_cache_size).

Изменение размера кеша буферов

Следующий ниже отчет STATSPACK показывает, когда значение коэффициента попаданий в кеш буферов падает ниже заданного порогового значения. Это весьма полезно для определения периодов времени, когда выполняются запросы в режиме DSS, так как большое количество полных просмотров больших таблиц приводит к уменьшению коэффициента попаданий в кеш буферов. Этот скрипт также выдает отчет о всех трех буферах данных, включая пулы KEEP (удерживающий) и RECYCLE (рециклирующий), и его можно приспособить для подготовки отчетов об отдельных пулах, так как пул KEEP должен всегда иметь достаточное количество блоков данных для кеширования всех строк таблиц, а пул RECYCLE должен иметь очень низкий коэффициент попадания в кеш. Если значение коэффициента попадания в кеш буферов меньше 90%, можно увеличить значение параметра db_cache_size (db_block_buffers в Oracle8i и более ранних версиях).

***********************************************************
* Когда коэффициент попадания в буфер падает ниже 20%, 
* следует увеличить значение параметра db_cache_size
***********************************************************


yr.  mo dy Hr.   Name    bhr 
------------- --------  -----
2001-01-27 09 DEFAULT    45
2001-01-28 09 RECYCLE    41
2001-01-29 10 DEFAULT    36
2001-01-30 09 DEFAULT    28
2001-02-02 10 DEFAULT    83
2001-02-02 09 RECYCLE    81
2001-02-03 10 DEFAULT    69
2001-02-03 09 DEFAULT    69

Здесь мы видим периоды времени, для которых можно динамически увеличить значение параметра db_cache_size. В данном случае это можно делать каждый день с 8:00 до 10:00 (за счет уменьшения значения параметра pga_aggregate_target).

Использование представления Oracle9i v$db_cache_advice

В Oracle9i появилось новое представление v$db_cache_advice, которое позволяет прогнозировать эффект от увеличения размера кеша буферов. Это представление показывает предполагаемые непопадания в кеш буферов для двенадцати потенциальных размеров кеша буферов в диапазоне от 10% текущего размера до 200% текущего размера.

Эта новая возможность очень похожа на средства Oracle7, используемые для прогнозирования эффекта от увеличения размера кеша буферов. Для этого в Oracle7 использовались представления x$kcbrbh (оценка попаданий в кеш) и x$kcbcbh (оценка непопаданий в кеш).

Так же, как и в Oracle7, для сбора статистик в представлении v$db_cache_advice требуется дополнительная память. Для включения сбора статистик нужно установить в параметре db_cache_advice файла init.ora значение “on” или “ready”. Их можно устанавливать динамически (без остановки экземпляра) оператором alter system.

Предупреждение: если АБД устанавливает dba_cache_advice=on, Oracle для сбора статистик будет использовать страницы разделяемого пула, что может оказать нежелательное влияние на библиотечный кеш. Например, если в параметре db_cache_size установлено 500 Мб, Oracle будет использовать существенный объем памяти разделяемого пула. Чтобы избежать этой проблемы, нужно предварительно в файле init.ora установить db_cache_advice=ready. В таком случае Oracle будет выделять память во время запуска экземпляра.

После включения сбора статистик (dba_cache_advice=on) и достаточно продолжительного времени работы базы данных для выдачи прогноза можно выдать следующий запрос:

column size_for_estimate     
   format 999,999,999,999 
   heading 'Cache Size (m)'
column buffers_for_estimate  
   format 999,999,999 
   heading 'Buffers'
column estd_physical_read_factor 
   format 999.90 
   heading 'Estd Phys|Read Factor'
column estd_physical_reads       
   format 999,999,999 
   heading 'Estd Phys| Reads'

select
   size_for_estimate, 
   buffers_for_estimate,
   estd_physical_read_factor, 
   estd_physical_reads
from
   v$db_cache_advice
where
   name = 'DEFAULT'
and
   block_size  = (SELECT value FROM V$PARAMETER 
                   WHERE name = 'db_block_size')
and
   advice_status = 'ON';

Вывод из скрипта показан ниже. Заметим еще раз, что диапазон значений - от 10% текущего размера кеша буферов до 200% текущего размера.

                                Estd Phys    Estd Phys
 Cache Size (MB)      Buffers Read Factor        Reads
---------------- ------------ ----------- ------------
              30        3,802       18.70  192,317,943 
              é  10% текущего размера
              60        7,604       12.83  131,949,536
              91       11,406        7.38   75,865,861
             121       15,208        4.97   51,111,658
             152       19,010        3.64   37,460,786
             182       22,812        2.50   25,668,196
             212       26,614        1.74   17,850,847
             243       30,416        1.33   13,720,149
             273       34,218        1.13   11,583,180
             304       38,020        1.00   10,282,475 
             é Текущий размер
             334       41,822         .93    9,515,878
             364       45,624         .87    8,909,026
             395       49,426         .83    8,495,039
             424       53,228         .79    8,116,496
             456       57,030         .76    7,824,764
             486       60,832         .74    7,563,180
             517       64,634         .71    7,311,729
             547       68,436         .69    7,104,280
             577       72,238         .67    6,895,122
             608       76,040         .66    6,739,731 
             é 2-й  размер

Здесь при увеличении количества буферов не наблюдается никаких пиковых изменений дискового ввода-вывода и маргинальных трендов. Это очень типично для хранилищ данных, в которых читаются большие таблицы в режиме полного просмотра. Следовательно, нет никаких “оптимальных” значений параметра db_cache_size. Другими словами, Oracle проявляет “ненасытный аппетит” при потреблении буферов данных: чем больше значение параметра db_cache_size, тем меньше будет операций дискового ввода-вывода. [В Руководстве по оптимизации производительности Oracle9i по аналогичному примеру делается более умеренный вывод: увеличение текущего размера кеша не приведет к значительному повышению производительности. – прим. А.П.Соколова.]

Как правило, нужно настраивать всю доступную память сервера, значение параметра db_cache_size следует устанавливать по точке “сокращающихся доходов” (diminishing returns) – точки, после которой увеличение количества буферов блоков данных не приводит к существенному увеличению коэффициента попадания в кеш буферов (рис. 2). Этот подход позволяет АБД Oracle определять оптимальное количество буферов.

Рис. 2. Определение оптимального значения параметра: db_cache_size.

Общее правило увеличения значения параметра db_cache_size простое: если увеличение количества буферов приводит к повышению коэффициента попаданий в кеш и есть свободная память, значение параметра db_cache_size нужно увеличивать. Увеличение количества буферов приводит к увеличению объема требуемой оперативной памяти, но для СУБД не всегда можно использовать всю память машины. Поэтому АБД должен аккуратно оценивать объем доступной памяти и определять оптимальное количество буферов блоков.

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

В более сложных базах данных Oracle9i можно управлять не только количеством буферов блоков, но и размером блоков. Например, некоторые блоки могут быть очень большими, что позволит уменьшить конкуренцию ввода-вывода. Помните: затраты на ввод-вывод блока размером 32К не существенно превышают затраты на ввод-вывод блока размером 4К. Если приложение “кластеризует” записи в блоках базы данных, проектировщик базы данных для минимизации ввода-вывода может принять решение об увеличении размера некоторых блоков данных. Более подробно об использовании блоков данных разного размера см. главу 8 Концепций).

Когда нужно начинать динамическую реконфигурацию

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

Область памяти Условие перезагруженности Условие недозагруженности

Разделяемый пул Непопадания в библиотечный кеш Нет непопаданий

Кеш буферов данных Коэффициент попадания < 90% Коэффициент попадания > 95%

Суммарная память PGA Много многопроходной обработки 100% оптимальной обработки

Таблица 1. Пороговые условия динамического перераспределения памяти.

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

Рис. 3. Типичная конфигурация памяти баз данных Oracle.

Заключение

Средства автоматической настройки Oracle9i предоставляют АБД беспримерную возможность управления размерами зон памяти любых компонентов SGA и PGA. По мере развития Oracle9i будут разрабатываться механизмы автоматической реконфигурации памяти в зависимости от потребностей обработки.

Как автор издательства Oracle Press, я всегда надеюсь на комментарии, информацию от читателей и советы. Не стесняйтесь обращаться ко мне по адресу: Don@Burleson.cc