 |
|
Автонастройка базы данных Oracle 10g:
Глобальная Системная область Oracle SGA
Oracle Database Tips by Donald Burleson
|
База данных Oracle 10g поддерживает
автоматизацию большего количества повседневных задач администратора базы данных
(АБД), чем когда-либо прежде. Представленые ниже методы помогут Вам прямо сейчас
подготовиться к этому с использованием скриптов автоматизации базы данных
Oracle9i.
Многие метрики Oracle становятся автонастраивающимися, хотя
АБД все более и более постигают тонкости настройки баз данных. В базе данных
Oracle 10g(http://otn.oracle.com/products/database/oracle10g/index.html)
мы встретим большую, чем когда-либо прежде, способность к автонастройке.
Например, возможность динамического распределения памяти
Oracle 10g позволяет осуществить автонастройку SGA (Глобальной
Системной области Oracle). Ниже в этой статье я объясню, как протестировать
экземпляр Oracle9i и отрегулировать области памяти для
sort_area_size и pga_aggregate_target,
large_pool_size, sga_max_size, а также db_cache_size
в зависимости от требований сервера и собственно базы данных. Обсуждаемые приемы
основаны на использовании в течение определенного времени пакета Statspack для
мониторинга областей памяти и определения характерных особенностей в
использовании системных ресурсов.
Я также расскажу, как создать аналитический механизм для
автоматической реконфигурации Oracle9i в зависимости от текущих
требований функционирования и приведу демонстрационный код, на основе которого
Вы сможете написать свои собственные скрипты автоматизации, которые
промоделируют эффективность возможностей базы данных Oracle 10g. (Для
примера я представлю скрипт, который автоматически отыскивает часто используемые
сегменты малого размера и назначает им опцию KEEP в [буферном]
пуле, обеспечивая, тем самым, их полное кэширование.) Поскольку каждая база
данных отлична от другой, эти скритпы для большей доходчивости преднамеренно
сокращены и упрощены. И поэтому Вы должны будете расширить примеры и написать
собственные скрипты автоматизации, которые будут соответствовать вашей
вычислительной среде.
Из возможности автоматизированной автонастройки наибольшую
выгоду извлекут те предприятия, для которых характерны:
- Bi-modal systems
(системы двойного функционирования) - особую выгоду из автонастройки областей RAM (random-access memory -
оперативное запоминающее устройство - словарь Lingvo) извлекут
системы, в которых чередуется функциональность оперативной обработки
транзакций (OLTP - online transaction processing) и хранилища данных (data
warehouse processing);
- 32-bit shops
(32-битовые организации)-
32-битовые серверы ограничены размером своей RAM-памяти (максимально до
1.7GB). Поэтому для организаций, где функционируют 32-битовые серверы,
эффективное использование ресурсов оперативной памяти особенно важно.
Но важно также помнить, что есть и негативные стороны в
использовании очень большого db_cache_size. Поскольку прямой
доступ к данным производится с использованием механизма хэширования (hashing),
время от времени база данных должна обследовать все блоки в RAM-кэше:
- Systems with high invalidations
(системы с
повышенной опасностью на отказ) - Всякий раз, когда программа опустошает
(truncate) таблицу, использует временные таблицы или очищает большой объем
данных, Oracle должен просмотреть все блоки в db_cache_size,
чтобы удалить модифицированные (dirty) блоки. Это действие может вызвать
чрезмерные непроизводительные издержки для системы, у которой db_cache_size
больше чем 10gB.
- High Update Systems
(системы повышенной
обновляемости) - процесс записи (DBWR) должен просматривать все блоки в
db_cache_size, когда выполняется асинхронная (asynchronous) запись. Наличие
слишком большого db_cache_sizer может стать причиной слишком большой
нагрузки на процесс записи в базу (database writer).
Теперь давайте рассмотрим правила, на которых организуется
подменяющая (behind) автонастраивающаяся база данных.
Правила подменяющей автонастройки
Один из самых обычных приемов для реконфигурирования
экземпляра Oracle состоит в использовании скрипта, который запускается пакетом
dbms_job или внешним планировщиком, например, cron в UNIX. Простой
пример: рассмотрим базу данных, которая в течение дня функционирует в режиме
OLTP, а ночью - в режиме хранилища данных. Для такой разновидности базы данных
можно спланировать выполнение реконфигурации памяти SGA для обеспечения наиболее
соответствующей внутренней конфигурации по типу обработки, выполняемой
экземпляром Oracle.
В листинге 1 приводится скрипт, который используется при
реконфигурации Oracle в режим поддержки принятия решений (decision-support
processing).
Листинг 1.
#!/bin/ksh
# Сначала мы определяем среду . . . .
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
!
Отметим, что существенные изменения значений
конфигурационных параметрах shared_pool, db_cache_size
и pga_aggregate_target соответстуют функциональности хранилища
данных. Этот скрипт планируется для исполнения при помощи пакета dbms_job каждый
день, например, в 18:00.
На листинге 1 мы видим команды alter system
которые составляют фундамент возможности автонастройки базы данных Oracle 10g.
Помните, что оперативная память? это дорогостоящий ресурс Oracle-сервера, и на
АБД лежит вся ответственность за распределение оперативной памяти на сервере.
Недоиспользуемая оперативная память - это ненужная трата дорогих аппаратных
ресурсов ЭВМ.
Но когда [оперативная память] полностью распределена,
избыточное ее назначение [также может быть] расточительно. Например, назначение
shared_pool_size=400m, когда нужно только 200m,
неэффективно, потому что эта оперативная память могла бы использоваться другой
подобластью SGA, например, db_cache_size.
Чтобы проиллюстрировать концепцию переконфигурации
оперативной памяти, рассмотрим следующий пример с неукомплектованным
(under-allocated) [кэшем] 16К-байтных буферов данных, в котором наблюдается
низкий коэффициент попадания в кэш (a poor data buffer hit ratio), и [кэшем]
32К-байтовых буферов данных, в котором отмечается их избыток (over-allocated) и
наблюдается хороший коэффициент попадания в кэш (a good data buffer hit ratio) - см. Фигура 1.
Фигура 1: Избыточная и недостаточная области оперативной
памяти.
Используя команду alter system, мы можем
подрегулировать [распределение] оперативной памяти между кэшами буферов данных,
чтобы когда это станет необходимым, перераспределить оперативную память - см.
Фигура 2.
Фигура 2: Динамическое перераспределение оперативной памяти
Команды alter system можно использовать в
многих типах Oracle-скриптов, включая динамический SQL, dbms_job и скрипты
оболочки (shell). На Листинге 2 приведен простой SQL*Plus скрипт для
перераспределения размеров RAM-кэшей.
Листинг 2.
-- ****************************************************************
-- Динамическая модификация SGA
--
-- Copyright (c) 2003 By Donald K. Burleson - All Rights reserved.
-- ****************************************************************
set heading off
set feedback off
set verify off
accept decrease_pool char prompt 'Enter cache to decrease: '
accept increase_pool char prompt 'Enter cache to increase: '
accept change_amount number prompt 'Enter amount to change: - spool run_sga.sql
select
'alter system set &decrease_pool =
'||to_char(to_number(value)-&change_amount)||';'
from v$parameter
where name = lower(?&decrease_pool?);
select
'alter system set &increase_pool =
'||to_char(to_number(value)+&change_amount)||';'
from v$parameter
where name = lower(?&increase_pool?;
spool off
set feedback on
@run_sga
Этот скрипт запрашивает имя и размер кэша и выдает
соответствующую команду alter system для перераспределения
размеров областей оперативной памяти. Вот каков его диалог:
SQL> @dyn_sga
Enter cache to decrease: shared_pool_size
Enter cache to increase: db_cache_size
Enter amount to change: 1048576
alter system set shared_pool_size = 49283072;
System altered.
alter system set db_cache_size = 17825792;
System altered.
Теперь когда мы видим, как области оперативной памяти легко
изменяются в базе данных Oracle Database 10g, давайте рассмотрим
несколько правил для вызова автоматической регулировки областей оперативной
памяти.
Когда следует задействовать динамическую реконфигурацию
Всякий раз, когда скрипты мониторинга экземпляра определяют
перенапряженную (overstressed) область оперативной памяти, необходимо выбрать
область, из которой будет "отнята" оперативная память. В таблице 1
приведен пример порогововых условий для задействования динамического измения в
отношении трех главных подобластей SGA. Разумеется, каждая система отлична от
другой, и Вы будете устанавливать эти границы согласно вашим потребностям.
Например, в многих организациях применяются несколько размеров блоков
(blocksizes) и имеются отдельные оперативные области для db_32k_cache_size (для
табличных проостранств, размещающих индексы), db_keep_cache_size (для часто
используемых объектов малого размера) и так далее.
Таблица 1
| Область оперативной
памяти |
Условие
недостаточности
(Overstressed condition) |
Условие избыточности
(Over-allocated condition) |
Разделяемый пул
(Shared pool) |
Непопадание в библиотечный кэш
(Library cache misses) |
Нет непопадания
(No misses) |
Кэш буферов данных
(Data buffer cache) |
Коэффициент нахождения
(Hit ratio) < 90% |
Коэффициент нахождения
(Hit ratio) > 95% |
Совокупность PGA
(PGA aggregate) |
Высокий уровень много проходного
выполнения
(High multi-pass exec) |
100% оптимальное исполнение
(optimal executions) |
Важно помнить, что требования базы данных постоянно
изменяются, согласно выполняемым SQL-запросам; оптимальная [конфигурация] SGA в
9:00 утра вряд ли будет оптимальной в 3:00 по полудни. Для того, чтобы увидеть
изменения в характеристиках обрабатывающих процессов, следует запустить на
выполнение Statspack-отчеты в тот момент времени, когда у Oracle изменяются
требования к оперативной памяти. Также можно запустить утилиты
v$db_cache_advice, v$pga_target_advice,
v$java_pool_advice или v$db_shared_pool_advice, чтобы
увидеть полученный выигрыш от сделанных изменений размеров областей оперативной
памяти.
Еще один популярный подход к автоматической динамической
реконфигурации SGA состоит в отслеживании тенденций. Рекомендуется использовать
Statspack для предсказания тех моментов времени, когда изменяются характеристики
обработки и применить пакет dbms_job или динамический SQL, чтобы запустить
подходящие конкретных случаях изменения SGA. Давайте вкраце рассмотрим подход,
основанный на тенденциях.
Получение графиков поведения (сигнатур) системы
Общий подход к реконфигурации, основанный на анализе
тенденций, состоит в том, чтобы использовать хронологические данные, полученные
с помощью Statspack, для выявления прогноз-тенденций, построения графиков
поведения (signatures - сигнатур) системы и использовать эти данные для
изменения базы данных.
Это решение является аналогом организации производственного
процесса в реальном масштабе времени (just-in-time - своевременно,
точно-ко-времени), когда детали появляются на рабочих местах как раз тогда,
когда они нужны для сборки. База данных Oracle 10g позволяет АБД
предвидеть потребности обработки и регулярно планировать соответствующее
вмешательство, гарантируя тем самым, что ресурсы SGA своевременно (just-in-time)
предоставлены изменившимся процессам обработки.
Автонастройка областей памяти Oracle влечет изменение
значений нескольких Oracle-параметров. Несмотря на то, что в базе данных Oracle
10g имеется более 250 параметров, которые управляют использованием
каждого ее свойства, только небольшая горстка параметров важна для
автоматической настройки Oracle SGA:
- db_cache_size -
db_cache_size определяет число
буферов для блоков базы данных в Oracle SGA и представляет один из самых
важных параметров памяти Oracle;
- db_keep_cache_size
- этот пул буферов данных был
подпулом db_block_buffers в Oracle8i, но начиная с базы
данных Oracle9i стал отдельной областью оперативной памяти.
- db_nn_cache_size -
база данных Oracle 10g
имеет отдельные пулы буферов данных, которые используются для разделения
(segregate) данных и изолирования (isolate) объектов с различными
характеристиками ввода/вывода;
- shared_pool_size -
shared_pool_size определяет
размер разделяемого для всех пользователей системы пула, включающего SQL-области
и каэш словаря данных;
- pga_aggregate_target -
pga_aggregate_target
определяет область RAM, предназначенную для общесистемных сортировок и
хэш-соединений;
Как Вы видите, концентрация на самых важных мерах по
поддержанию общего состояния вашей базы данных Oracle - это вовсе не слабая
задача. Давайте попробуем отследить тенденции по библиотечному кэшу и определить
как автоматически регулировать [параметр] shared_pool_size.
Использование консультативных справок по базе данных Oracle
10g
База данных Oracle 10g содержит комплект
консультативных справок, которые точно предскажут изменения от изменения размера
любой из областей оперативной памяти. Консультативные справки в базе данных
Oracle 10g включают:
- Консультативная справка по разделяемому пулу (Shared
pool advice) - v$shared_pool_advice
- Консультативная справка по целевой PGA (PGA target
advice) - v$pga_target_advice
- Консультативная справка по кэшу данных (Data cache
advice) - v$db_cache_advice
- Консультативная справка по Java-пулу (Java Pool advice)
- v$java_pool_advice
Эти справки - лучшие свидетели оправданности применения
автонастраивающихся изменений. В следующих секциях будет показано, как
консультативные справки вызываются и как интерпретируются [их результаты]. Коль
скоро Вы правильно проинтерпретируете эти выходные данные, то сможете сами
написать скрипты автоматизации, чтобы сгенерировать соответствующие извещения,
чтобы проинтерпретировать результаты своих запусков, чтобы автоматически
изменить размеры областей оперативной памяти.
Консультативная справка Shared Pool Advice
Эта вспомогательная функциональность была расширена в базе
данных Oracle9i Release 2, когда была включена новая справка (advice),
названная v$shared_pool_advice, которая в будущих выпусках
распространится на все SGA-области в оперативной памяти.
Начиная с базы данных Oracle9i Release 2,
представление v$shared_pool_advice показывает граничные
расхождения при разборах (parses) SQL-предложений, когда размер разделяемого
пула изменяется от 10% до 200% своего текущего значения.
Справку shared pool advice очень легко
сконфигурировать: если она инсталлирована, Вы можете запустить простой скрипт с
запросом к представлению v$shared_pool_advice и увидеть граничные
расхождения при разборах SQL-предложений для различных размеров
shared_pool. Выходные данные представленого ниже скрипта покажут Вам
результаты динамического увеличения или уменьшения значения параметра
shared_pool_size.
-- ************************************************
-- Display shared pool advice
-- ************************************************
set lines 100
set pages 999
column c1 heading 'Pool |Size(M)'
column c2 heading 'Size|Factor'
column c3 heading 'Est|LC(M) '
column c4 heading 'Est LC|Mem. Obj.'
column c5 heading 'Est|Time|Saved|(sec)'
column c6 heading 'Est|Parse|Saved|Factor'
column c7 heading 'Est|Object Hits' format 999,999,999
SELECT
shared_pool_size_for_estimate c1,
shared_pool_size_factor c2,
estd_lc_size c3,
estd_lc_memory_objects c4,
estd_lc_time_saved c5,
estd_lc_time_saved_factor c6,
estd_lc_memory_object_hits c7
FROM
v$shared_pool_advice;
Est Est
Time Parse
Pool Size Est Est LC Saved Saved Est
Size(M) Factor LC(M) Mem. Obj. (sec) Factor Object Hits
---------- ---------- ---------- ---------- ---------- ---------- ------------
48 .5 48 20839 1459645 1 135,756,032
64 .6667 63 28140 1459645 1 135,756,101
80 .8333 78 35447 1459645 1 135,756,149
96 1 93 43028 1459645 1 135,756,253
112 1.1667 100 46755 1459646 1 135,756,842
128 1.3333 100 46755 1459646 1 135,756,842
144 1.5 100 46755 1459646 1 135,756,842
160 1.6667 100 46755 1459646 1 135,756,842
176 1.8333 100 46755 1459646 1 135,756,842
192 2 100 46755 1459646 1 135,756,842
Здесь мы видим статистику для разделяемого пула в диапазоне
от 50% до 200% от текущего размера. Эти статистические данные могут дать Вам
хорошее представление о надлежащем значении [параметра] shared_pool_size.
Если Вы автоматизируете изменения размеров областей SGA с автоматически
выдаваемыми командами alter system, создайте такой выход и
напишите программу для интерпретирования результатов. Это будет лучшим приемом,
который гарантирует, что разделяемый пул и библиотечный кэш всегда обладают
достаточным объмом памяти. Далее, давайте посмотрим, как мы сможем отследить
использование разделяемого пула через какие-то интервалы времени и построить "сигнатуры"
("signatures" - графики поведения), которые позволят нам предсказать те моменты
времени, когда потребуется регулирование разделяемого пула.
Мы можем использовать пакет Statspack, чтобы собрать на
выходе отметки по времени, когда коэффициент попадания в библиотечный кэш падает
ниже определенного уровня, как это показано на Листинге 3.
Листинг 3.
-- ****************************************************************
-- Почасовое отображение состояния библиотечного кэша с
-- использованием пакета STATSPACK
--
-- Copyright (c) 2003 By Donald K. Burleson - All Rights reserved.
-- ****************************************************************
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');
Приведенные ниже выходные данные показывают на повторяющуюся
каждый день нехватку оперативной памяти в разделяемом пуле между 9:00 и 10:00
утра.
Cache Misses Library Cache
Yr. Mo Dy Hr. execs While Executing Miss Ratio
--------------- ------- --------------- -------------
2001-12-11 10 10,338 6,443 .64
2001-12-12 10 182,477 88,136 .43
2001-12-14 10 190,707 101,832 .56
2001-12-16 10 72,803 45,932 .62
Из этого примера следует, что АБД должен спланировать [переброску]
дополнительной оперативной памяти в shared_pool_size на период ее
там недостатка.
Построение сигнатур для pga_aggregate_target
Размер области PGA в базе данных Oracle 10g
очень важен, поскольку от этого зависит скорость сортировки и [реализации]
хэшированных SQL-соединений. Следует динамически изменить значение параметра
pga_aggregate_target, когда выполняется любое из следующих условий:
- Всякий раз, когда значение статистического параметра
"estimated PGA memory for one-pass" из представления v$sysstat statistic
превышает pga_aggregate_target, Вам следует увеличить pga_aggregate_target.
- Всякий раз, когда значение статистического параметра
"workarea executions - multipass" из представления v$sysstat более 1%, можно
получить выгоду за счет добавления оперативной памяти.
- Если имеет место переукомплектование (over-allocate)
памяти PGA, то можно уменьшить значение pga_aggregate_target всякий раз,
когда значение параметра "workarea executions?optimal" в v$sysstat стойко
держится в районе 100 процентов.
Представление v$pgastat обеспечивает итоговую
статистику по использованию PGA и автоматического менеджера памяти. Для того,
чтобы быстро получить краткий обзор для всей базы данных Oracle 10g,
следующий простой запрос превосходно собирает полную статистику использования
PGA:
check_pga.sql
-- *************************************************************
-- Отображение детальной PGA статистики
--
-- *************************************************************
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
В этой выборке из v$pgastat мы видим следующие
важные показатели:
- Total PGA used for auto workareas
(Общее число PGA,
использованных для авто рабочих областей) - Этот параметр отражает
расходование оперативной памяти на все соединения (connections),
функционирующие в режиме автоматической памяти. Напомним, что не всем
внутренним процессам Oracle разрешает использовать возможность
автоматической памяти. Например, Java and PL/SQL разместятся в оперативной
памяти, но это не отразится в общей PGA-статистике. Следовательно,
необходимо вычесть это значение из общего объема, ассигнованного PGA, чтобы
увидить размер памяти, используемый как для соединений, так и потраченный на
Java и PL/SQL.
- Estimated PGA memory for optimal/one-pass
(Приблизительная
память PGA для оптимального\однопроходного [выполнения]) - Это
статистические оценки, как много памяти требуется, чтобы выполнить все
соединенные задачи в оптимальном режиме. Напомним, что когда база данных
Oracle 10g испытывает нехватку памяти, АБД вызывает многопроходное
(multi-pass) действие, чтобы попытаться определить местонахождение недавно
освобожденной оперативной памяти. Этот показатель критичен для мониторинга
расходования оперативной памяти в базе данных Oracle 10g, и
большинство Oracle-ских АБД увеличат размер pga_aggregate_target до этого
значения.
В базе данных Oracle 10g можно использовать
новую консультативную справку, дублирующую v$pga_target_advice.
Эта справка покажет граничные изменения PGA в оптимальном однопроходном или
многопроходном выполнение для различных размеров pga_aggregate_target
в пределах от 10% до 200% от текущего значения.
Листинг 4 показывает простой запрос, применяющий эту
новую справку, а следом идет пример вывода.
Листинг 4.
-- ************************************************
-- Отображение pga target advice
-- ************************************************
column c1 heading 'Target(M)'
column c2 heading 'Estimated|Cache Hit %'
column c3 heading 'Estimated|Over-Alloc.'
SELECT
ROUND(pga_target_for_estimate /(1024*1024)) c1,
estd_pga_cache_hit_percentage c2,
estd_overalloc_count c3
FROM v$pga_target_advice;
Ниже мы видим, что в текущем процессе
pga_aggregate_target укомплектована с избыточностью и можно безопасно
обкорнать память этой области, перебросив ее в другое место:
Estimated Estimated
Target(M) Cache Hit % Over-Alloc.
---------- ----------- -----------
113 73 0
225 81 0
450 92 0
675 100 0
900 100 0
1080 100 0
1260 100 0 <= current size
1440 100 0
1620 100 0
1800 100 0
2700 100 0
3600 100 0
5400 100 0
7200 100 0
Как Вы видите, можно легко создать автоматические методы для
определения дефицита PGA-памяти (используя Statspack) и написать задания (jobs - работы) для динамического изменения параметра pga_aggregate_target
, что гарантирует оптимальное использование оперативной памяти при
сортировках (sorts) и хэшированных соединениях (hash joins).
Построение сигнатур для буферов данных
[Когда]АБД заметит, что на практически изменение
коэффициента попадания в буфер данных (DBHR - data buffer hit ratio)
увеличиватся с частотой измеряемых (measured intervals) интервалов. Например,
Statspack может сообщить о 92 % процентов DBHR в почасовых интервалах, но
указать широкое изменение (большой разброс), когда коэффициент замеряется в
двухминутных интервалах, как показано на Фигура 3.
Как общая рекомендация, вся память, доступная на хосте
должна быть настроена так, чтобы параметр db_cache_size находился в точке "сокращяющегося
убывания" ("diminishing returns" - сокращающиеся доходы - словарь Lingvo)
распределения оперативной памяти (см. Фигура 4), когда дополнительные
буфера не улучшают существеным образом коэффицент попадания в кэш буферов данных.
Новое представление v$db_cache_advice подобно старой утилите
x$kcbrbh, которая была представлена в Oracle7 и использовалась, чтобы отследить
попадания в буферы; точно так же представление x$kcbcbh использовалось, чтобы
отследить буферные промахи. Коэффициент попадания в буферы данных - это те же
сведения, что [дает] v$db_cache_advice, так что большинство профессионалов,
настраивающих Oracle, могут использовать оба инструмента для контроля
эффективности использования своих буферов данных.
Скрипт на Листинге 5 следует использовать, чтобы
задействовать функцию справки по кэшу, когда используется v$db_cache_advice, и
база данных функционирует достаточно долго, чтобы дать представительские
результаты.
Листинг 5
-- ****************************************************************
-- Display cache advice
-- ****************************************************************
column c1 heading 'Cache Size (m)' format 999,999,999,999
column c2 heading 'Buffers' format 999,999,999
column c3 heading 'Estd Phys|Read Factor' format 999.90
column c4 heading 'Estd Phys| Reads' format 999,999,999
select
size_for_estimate c1,
buffers_for_estimate c2,
estd_physical_read_factor c3,
estd_physical_reads c4
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';
Используя этот скрипт, Вы можете получить справку по кэшу
для всех ваших буферных пулов для 2-х, 4-х, 8-ми, 16-ти и 32-х килобайтных
буферов данных .
Выход этого скрипта показан ниже. Обратите внимание, что
значения ранжируются по 10 процентов от 0,1 текущего до удвоенного размера
db_cache_size.
Estd Phys Estd Phys
Cache Size (MB) Buffers Read Factor Reads
---------------- ------------ ----------- ------------
30 3,802 18.70 192,317,943 <- 10% size
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 Current Size
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 <- 2x size
Как показано на Фигура 4 , оптимальная установка для
кэша буферов данных представляет собой точку, где предельная выгода от
дополнительных буферов [начинает] уменьшается. Конечно, эта оптимальная точка
изменится через какое-то время, и именно поэтому мы нуждаемся в действенной [методике]
реконфигурации SGA так, чтобы мы могли изменить размер кэша согласно текущим
потребностям обработки [в целом].
Для тренд-анализа (trend analysis - общее направление,
тенденция), [мгновенные] изменения в DBHR (data buffer hit ratio) не существенны,
и средние коэффициенты попадания в буферы данных могут быть сгенерированы по
двум измерениям (dimensions): средний DBHR по дням недели и средний DBHR по
часам в сутках.
Помните, что хотя изменения происходят в буферах данных
быстро, долгосрочный анализ иногда может обеспечить ключ-подсказку, указывающую
на проблемы в обработках в базе данных в целом. Почти каждая база данных Oracle
демонстрирует поведение, обусловленное выполнением регулярно спланированных
процессов, отвечающих своим сигнатурам.
Вывод Statspack-скрипта, ежечасно составляющего средний
DBHR, показывается ниже. Отчет показывает средний коэффициент попадания в
течение каждого дня в течение шести месяцах сбора данных. DBHR-сигнатура этой
базы данных становится очевидной, если эти данные представить в виде электронной
таблицы (spreadsheet).
hr BHR
-- -----
00 .94
01 .96
02 .91
03 .82
04 .80
05 .90
06 .94
07 .93
08 .96
09 .95
10 .84
12 .91
13 .96
14 .95
17 .97
18 .97
19 .95
20 .95
21 .99
22 .93
23 .94
График этих данных показан на Фигура 5. На ней мы
видим несколько интересных периодических тенденций.
Из диаграммы становится понятно, что DBHR снижается ниже 90
процентов от 3:00 до 4:00 утра и в 10:00 вечера ежедневно. Чтобы решить проблему,
АБД следовало бы спланировать динамическую настройку, добавляя каждый день [в
определенные часы] больше оперативной памяти к db_cache_size.
Похожий скрипт на (Листинге 6) может выдать среднее
значение DBHR по дням недели.
Листинг 6
set pages 999;
column bhr format 9.99
column mydate heading 'yr. mo dy Hr.'
select
to_char(snap_time,'day') mydate,
avg( (((new.consistent_gets-old.consistent_gets) +
(new.db_block_gets-old.db_block_gets))-
(new.physical_reads-old.physical_reads))
/ ((new.consistent_gets-old.consistent_gets) +
(new.db_block_gets-old.db_block_gets)) ) bhr
from
perfstat.stats$buffer_pool_statistics old,
perfstat.stats$buffer_pool_statistics new,
perfstat.stats$snapshot sn
where new.name in ('DEFAULT','FAKE VIEW')
and new.name = old.name
and new.snap_id = sn.snap_id
and old.snap_id = sn.snap_id-1
and new.consistent_gets > 0
and old.consistent_gets > 0
having
avg((((new.consistent_gets-old.consistent_gets) +
(new.db_block_gets-old.db_block_gets)) -
(new.physical_reads-old.physical_reads)) /
((new.consistent_gets-old.consistent_gets) +
(new.db_block_gets-old.db_block_gets)) ) < 1
group by to_char(snap_time,'day') ;
Вывод этого скрипта показан ниже.
DOW BHR
--------- -----
sunday .91
monday .98
tuesday .93
wednesday .91
thursday .96
friday .89
saturday .92
Мы можем затем вставить эти данные в электронную таблицу и
поместить на диаграмму, как это показано на Фигура 6.
Этот отчет полезен в установлении периодической и правильной
буферной сигнатуры, поскольку ломаная линия графика ясно показывает на
потребность увеличения db_cache_size по понедельникам и пятницам. Далее понятно,
что надо применить пакет Statspack для исследования различий [в ходе
производственных процессов обработки данных] в эти и другие дни недели.
Далее, давайте рассмотрим еще один метод автонастройки, при
помощи которого мы автоматически идентифицируем и назначим объекты для KEEP-пула,
что обеспечит в полное кэширование объектов в оперативной памяти.
Автоматизация назначения KEEP-пула
Согласно Oracle-документации, " из общего множества
сегментов хорошим кандидатом для размещения в KEEP-пуле является тот, размер
которого меньше 10% от объема буферного пула DEFAULT и который требует для себя
не менее 1% всех системных операций ввода/вывода". Достаточно просто определить
сегменты, размер которых меньше 10% от объема кэша буферов данных, но Oracle не
имеет какого-либо прямого механизма, который прослеживал бы ввод/вывод на уровне
сегмента. Одно из решений состоит в том, чтобы разместить каждый сегмент в
изолированное табличное пространство. Тогда Statspack покажет полную картину
ввода/вывода. Но это очень непрактичное решение для сложных схем с сотнями
сегментов.
Используя идею подмены в том, что установка опции KEEP
обеспечит полное кэширование, мы определим те объекты, которые являются
маленькими и обладают непропорционально активной деятельностью. Применяя эту
директиву, следует рассмотреть кэширование любых объектов, у которых:
- объект потребляет больше чем 10 % полного
размера кэша буферов данных
- больше чем 50 % объекта уже постоянно находятся
в кэше буферов данных (согласно запросу к x$bh).
Чтобы идентифицировать эти объекты, мы запускаем explaining
(отображение построенных планов выполнения) всех SQL-предложений в базах данных,
которые обращаясь к малым таблицам, используют полное табличное сканирование.
Затем мы периодически исследуем кэш буферов данных, обращая внимание на любые
объекты, которые содержат больше чем 80% своих блоков в оперативной памяти.
Лучший метод для того, чтобы идентифицировать таблицы и
индексы для KEEP-пула, это проверить текущие блоки, находящиеся в кэше буферов
данных. Результатом такой проверки должно стать простое правило: любой объект,
который имеет больше чем 80% своих блоков данных в кэше буферов данных, вероятно,
должен полностью кэшироваться.
Очень маловероятно, что незаслуживающая [такой участи]
таблица или индекс ответили бы этому критерию. Конечно, нужно запускать этот
скрипт неоднократно в течение дня, поскольку содержимое [кэша] буферов
изменяется очень быстро.
Скрипт из Листинга 7 можно выполнять хоть каждый час,
используя механизм dbms_job, и автоматизировать мониторинг за кандидатами в
KEEP-пул .
Листинг 7
set pages 999
set lines 92
spool keep_syn.lst
drop table t1;
create table t1 as
select
o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks
from dba_objects o, v$bh bh
where o.data_object_id = bh.objd
and o.owner not in ('SYS','SYSTEM')
and bh.status != 'free'
group by o.owner, o.object_name,
o.subobject_name, o.object_type
order by count(distinct file# || block#) desc ;
select
'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||
' storage (buffer_pool keep);'
from t1, dba_segments s
where s.segment_name = t1.object_name
and s.owner = t1.owner
and s.segment_type = t1.object_type
and nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
and buffer_pool <> 'KEEP'
and object_type in ('TABLE','INDEX')
group by s.segment_type, t1.owner, s.segment_name
having
(sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80 ;
Каждый раз, когда находится кандидат, АБД изменит
соответствующим образом определение объекта и скорректирует общей объем KEEP-пула,
чтобы разместить в нем новый объект. Ниже представлен результат выполнения этого
скрипта:
alter TABLE LIS.BOM_DELETE_SUB_ENTITIES storage (buffer_pool keep);
alter TABLE LIS.BOM_OPERATIONAL_ROUTINGS storage (buffer_pool keep);
alter INDEX LIS.CST_ITEM_COSTS_U1 storage (buffer_pool keep);
alter TABLE ISM3.FND_CONCT_PROGRAMS storage (buffer_pool keep);
alter TABLE ISM3.FND_CONCT_REQUESTS storage (buffer_pool keep);
alter TABLE IS.GL_JE_BATCHES storage (buffer_pool keep);
alter INDEX IS.GL_JE_BATCHES_U2 storage (buffer_pool keep);
alter TABLE IS.GL_JE_HEADERS storage (buffer_pool keep);
Когда проидентифицированы сегменты для размещения в KEEP-пуле,
нужно скорректировать величину параметра db_keep_cache_size, чтобы гарантировать
наличие достаточного большого количества блоков для полного кэширования всех
сегментов, назначенных в этот пул.
Конечно, есть много исключений в этом автоматизированном
решении. Например, эти скрипты не выделяют табличные секции (table partitions) и
объектные типы (object types). Они должны использоваться только как система
взглядов Вашей стратегии кэширования в KEEP-пуле, а не запускаться абы-как
(as-is).
Будущее Автонастройки Базы данных
Среди многих новых возможностей баз данных Oracle10g,
обеспечивающих простоту автоматизированной настройки, наиболее ярким средствами
являются новый PL/SQL-пакет dbms_advisor и консультативная справка
по Java-пулу:
- Java Pool Advisory (Консультативная справка по Java-пулу)
- эта утилита предсказывают выигрыш от добавления или удаления фрагментов
оперативной памяти из области java_pool_size.
- SQLAccess Advisor (Справка SQLAccess) - это во
истинну новая функциональность базы данных Oracle - новый пакет
dbms_advisor, который позволяет Вам получать справку по созданию,
обслуживанию и использованию индексов и материализованных представлений.
- tune_mview advisor utility (Утилита-справка
tune_mview) - это еще один новый компонент пакета dbms_advisor
в базе данных Oracle 10g, который позволяет Вам получать экспертную
справку, какие могут быть использованы материализованные представления,
чтобы повысить эффективность (hyper-charge) ваших SQL-запросов.
Помните, что коли уж Oracle обеспечивает мощные средства
типа предложений alter system, Вы должны освоить их применение,
чтобы автоматизировать стандартные задачи АБД. Это освободит ваше время для
действий в более сложных областях администрирования базы данных Oracle.
and include the URL for the page.
