Средства динамического распределения памяти СУБД Oracle 9i позволяют создавать самонастраивающиеся базы данных. В данной статье рассматривается использование пакета STATSPACK для мониторинга и настройки (в зависимости от потребностей обработки в сервере и базе данных) зон памяти, размеры которых задаются следующими параметрами: sort_area_size (размер области сортировки), large_pool_size (размер большого пула), pga_aggregate_target (максимальная суммарная память PGA), sga_max_size (максимальный размер SGA) и db_cache_size (размер пула буферов). Мы также рассмотрим мониторинг с помощью пакета STATSPACK использования зон памяти и создание интеллектуального механизма для автоматического реконфигурирования Oracle 9i в зависимости от текущих потребностей обработки.
Серьезной проблемой в Oracle8i было требование: все выделенные соединения должны использовать области сортировки одинакового размера, задаваемого параметром sort_area_size. В Oracle 9i имеется возможность автоматического управления распределением памяти PGA. В Oracle введен новый параметр файла init.ora - pga_aggregate_target. Если он установлен и используются выделенные (dedicated) соединения с Oracle, Oracle 9i будет игнорировать все параметры 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. В Oracle 9i также можно динамически модифицировать параметр pga_aggregate_target на уровне экземпляра с помощью оператора alter system, поэтому АБД может динамически управлять распределением памяти, доступной Oracle 9i.
В Oracle 9i появился также еще один новый параметр - workarea_size_policy (политика установки размеров рабочих областей). Если в этом параметре установлено AUTO (автоматический режим), Oracle будет пытаться максимизировать количество рабочих областей, используемых для оптимального (optimal) режима их обработки, а размер других рабочих областей будет пытаться задавать достаточным для однопроходного (one-pass) режима обработки. Если в параметре workarea_size_policy установлено MANUAL (ручной режим), соединениям будет выделяться память в соответствии с установленным значением параметра sort_area_size.
Новые представления Oracle 9i для автоматического управления памятью PGA
В Oracle 9i появилось несколько новых представлений и новых столбцов в существующих представлениях, которые показывают внутреннее распределение памяти в Oracle 9i. Для мониторинга использования памяти выделенными соединениями Oracle 9i можно использовать следующие v$ -представления:
- v$process - в Oracle 9i для мониторинга использования памяти 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 - это новое представление выдает детализированные суммарные статистики использования памяти соединениями с Oracle 9i.
- v$workarea_active - это новое представление показывает внутреннюю информацию об использовании памяти всеми операторами SQL, выполняемыми в данное время.
Рассмотрим более подробно новые средства Oracle 9i и скрипты, которые позволяют разобраться в деталях использования памяти PGA.
Использование представления v$sysstat в Oracle 9i
Следующий запрос выдает общее количество и проценты количества выполнений в трех режимах (оптимальном, однопроходном и многопроходном) начиная с запуска экземпляра.
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 в Oracle 9i
Представление v$pgastat содержит суммарные статистики (на уровне экземпляра) использования PGA и работы автоматического диспетчера памяти (automatic memory manager). Для выдачи суммарных статистик для всех соединений с Oracle 9i можно использовать следующий скрипт:
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 - суммарный объем памяти, доступной для соединений с Oracle 9i. Это значение устанавливается в соответствии со значением параметра 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 - оценка объема памяти, требуемой для выполнения операций для всех соединений в оптимальном/однопроходном режимах обработки. Помните, при нехватке памяти Oracle 9i будет использовать многопроходной режим обработки. Эта статистика имеет большое значение для мониторинга использования памяти в Oracle 9i , и большинство АБД будет увеличивать значение параметра pga_aggregate_target до значения этой статистики.
Расширение представления v$process в Oracle 9i
В представление 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 в Oracle 9i
Два новых представления показывают активное пространство рабочих областей: 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
В Oracle 9i можно получать информацию об использовании памяти вместе с информацией о планах выполнения. Для этого по представлению 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 - новое достижение в Oracle 9i , которое позволяет АБД получать подробную информацию о внутреннем выполнении операторов SQL.
Переход к самонастраивающейся базе данных Oracle 9i
Новые возможности динамического управления SGA в Oracle 9i позволяют использовать архитектуру, при которой АБД Oracle может выполнять мониторинг использования памяти в ОС UNIX и реконфигурировать SGA и зоны памяти PGA в зависимости от текущих профилей использования.
Уровень автоматической настройки задается новым параметром pga_aggregate_target. В Oracle 9i для управления памятью используется сложный алгоритм, который повышает скорость выполнения операций, интенсивно использующих память, таких, как хеш-соединения и большие сортировки.
Сейчас АБД 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 для реконфигурирования памяти в зависимости от текущих запросов процессов.
На пути к созданию самонастраивающихся баз данных
Oracle 9i развивается в направлении создания полностью самонастраивающейся архитектуры, но АБД Oracle несут ответственность за настройку конфигурации памяти в соответствии с характером ее использования. В общем, для определения времени изменения характеристик работы можно использовать запросы v$ -представлений и пакет STATSPACK. Мы видим три подхода к автоматизации настройки:
- Обычное планируемое реконфигурирование. Реконфигурирование памяти SGA и PGA полезно для экземпляра, работающего в двух режимах (OLTP и DSS).
- Динамическое реконфигурирование на основании анализа трендов. Для прогнозирования времени изменения характеристик работы можно использовать пакет STATSPACK, а для реконфигурирования памяти SGA и PGA - пакет dbms_job.
- Динамическое реконфигурирование. Так же как Oracle 9i динамически перераспределяет память зоны 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 ).
Использование представления Oracle 9i v$db_cache_advice
В Oracle 9i появилось новое представление 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 , тем меньше будет операций дискового ввода-вывода. [В Руководстве по оптимизации производительности Oracle 9i по аналогичному примеру делается более умеренный вывод: увеличение текущего размера кеша не приведет к значительному повышению производительности. - прим. А.П.Соколова.]
Как правило, нужно настраивать всю доступную память сервера, значение параметра db_cache_size следует устанавливать по точке "сокращающихся доходов" (diminishing returns) - точки, после которой увеличение количества буферов блоков данных не приводит к существенному увеличению коэффициента попадания в кеш буферов (рис. 2). Этот подход позволяет АБД Oracle определять оптимальное количество буферов.
Рис. 2. Определение оптимального значения параметра: db_cache_size.
Общее правило увеличения значения параметра db_cache_size простое: если увеличение количества буферов приводит к повышению коэффициента попаданий в кеш и есть свободная память, значение параметра db_cache_size нужно увеличивать. Увеличение количества буферов приводит к увеличению объема требуемой оперативной памяти, но для СУБД не всегда можно использовать всю память машины. Поэтому АБД должен аккуратно оценивать объем доступной памяти и определять оптимальное количество буферов блоков.
Совет: для сбора статистик в представлении v$db_cache_advice требуется предварительное выделение буферов данных (включается установкой параметра db_cache_advice ), поэтому может оказаться целесообразным только одноразовое включение сбора статистик для определения оптимального размера кеша буферов. Помните: для сбора аналогичной информации вы можете использовать коэффициент попаданий в кеш буферов данных.
В более сложных базах данных Oracle 9i можно управлять не только количеством буферов блоков, но и размером блоков. Например, некоторые блоки могут быть очень большими, что позволит уменьшить конкуренцию ввода-вывода. Помните: затраты на ввод-вывод блока размером 32К не существенно превышают затраты на ввод-вывод блока размером 4К. Если приложение "кластеризует" записи в блоках базы данных, проектировщик базы данных для минимизации ввода-вывода может принять решение об увеличении размера некоторых блоков данных. Более подробно об использовании блоков данных разного размера см. главу 8 Концепций ).
Когда нужно начинать динамическую реконфигурацию
Если вы с помощью своих скриптов обнаружили перезагруженную область памяти, вам нужно будет принять решение о выборе области памяти, размер которой можно уменьшить, чтобы расширить перезагруженную область памяти. В таблице 1 показаны пороговые условия инициирования динамического изменения размеров областей памяти.
| Область памяти | Условие перезагруженности | Условие недозагруженности |
| Разделяемый пул | Непопадания в библиотечный кеш | Нет непопаданий |
| Кеш буферов данных | Коэффициент попадания < 90% | Коэффициент попадания > 95% |
| Суммарная память PGA | Много многопроходной обработки | 100% оптимальной обработки |
Таблица 1. Пороговые условия динамического перераспределения памяти.
На практике выбор области памяти, размер которой можно уменьшить, заключается в выборе между разделяемым пулом и суммарной памятью PGA (см. рис. 3). Дело в том, что размер разделяемого пула почти всегда меньше размера памяти для буферов данных и PGA.
Рис. 3. Типичная конфигурация памяти баз данных Oracle.
Заключение
Средства автоматической настройки Oracle 9i предоставляют АБД беспримерную возможность управления размерами зон памяти любых компонентов SGA и PGA. По мере развития Oracle 9i будут разрабатываться механизмы автоматической реконфигурации памяти в зависимости от потребностей обработки.