Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Автонастройка базы данных 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.

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational