Эти материалы являются объектом авторского права и защищены законами РФ и международными соглашениями о защите авторских прав. Перед использованием материалов вы обязаны принять условия лицензионного договора на использование этих материалов, или же вы не имеете права использовать настоящие материалы

Авторская площадка "Наши орбиты" состоит из ряда тематических подразделов, являющихся моими лабораторными дневниками, содержащими записи за разное, иногда продолжительно отличающееся, время. Эти материалы призваны рассказать о прошедшем опыте, они никого ни к чему не призывают и совершенно не обязательно могут быть применимы кем-то ещё. Это только лишь истории о прошлом

статья не завершена

О тюнинге экземпляра

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

Если оптимизация прикладного решения по большей части является задачей разработчика, хотя абсолютно незаслуженно иногда её пытаются вручить в нагрузку администратору БД, то оптимизация экземпляра является непосредственной задачей именно администратора. Хотя найти проблемные запросы, выкатить по ним статистику утилизации ресурсов движка и передать разработчику также непосредственная задача администратора

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

Для решения этих задач СУБД Oracle ведёт детальную статистику деятельности экземпляра несколько сотен параметров, а также статистику событий ожидания, возникающих при невозможности движка выполнить ту или иную задачу в силу занятости некоторого ресурса тоже несколько сотен предопределённых событий ожидания

Знание о связях и взаимном влиянии компонент движка, а также о соответствии параметров статистики и событий ожидания функционированию той или иной компоненты позволяет решать задачи оптимизации экземпляра с той или иной степенью «мастерства», напрямую зависящей от адекватности картины устройства СУБД и достаточной детальности этой картины

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

Добиться существенной компенсации работы "криво" разработанной прикладной части средствами тюнинга экземпляра, равно как и наращиванием мощности аппаратной платформы, в общем случае малореально, а в частных случаях возможны несущественные выигрыши. Об этом говорит руководство по тюннгу экземпляра, и гораздо более жёстко об этом говорят такие признанные специалисты - практики, как Том Кайт. Этот постулат лучше сразу принять за аксиому, ибо последующий опыт только подтверждает его. Задачей администратора является оптимальная конфигурация движка, обеспечение корректных условий для работы оптимизатора и тому подобные "системные" задачи, но не оптимизация прикладного решения и отдельных запросов в частности. Ниже будет представлен скелетный алгоритм анализа функционирования экземпляра и даны пояснения к нему в разрезе следующих акцентов

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

Инструменты администрирования

Как накапливаются данные статистики и событий ожидания ? В общем случае это счётчики, монотонно возрастающие с момента старта экземпляра и доступный как таблицы и представления через стандартные SQL запросы. Таким образом считав значение какого-либо счётчика сейчас и через десять минут, можно увидеть приращение за эти десять минут. Такие счётчики статистики и событий ожидания ведутся как на экземпляр в целом, так и с несколько большей детализацией - на текущие сессии. Это базовый механизм

На базовый механизм, являющийся не самым удобным для непосредственного использования, могут быть надстроены различные расширения. Например Oracle поставляет пакет statspack, не стоящий дополнительных денег, и реализующий периодический съём значений счётчиков и сохранение их в базе, а также построение отчёта по двум указанным точкам среза. Важно, что это стандартный механизм, и что он не требует дополнительных отчислений

Учёт по текущим сессиям, аналогичный ASH, такой механизм не обеспечивает (и вообще охват сессионной статистики пакетом Statspack разнится от версии к версии движка, в 9-й версии он был шире, чем в 11), однако позволяет как посмотреть на состояние экземпляра в динамике, так и выявить наиболее тяжёлые запросы за указанный период. Однако аналитика запросов подразумевает использование смежных периодов и корректное конфигурирование библиотечного кэша - его должно хватать для сохранения информации по SQL запросам без вытеснения от одной точки среза до следующей - иначе статистика просто будет неадекватна. Что касается сессионной статистики - "наклепать" некоторый аналог ASH вполне реально, только вот аналитический интервал в 10 секунд он обеспечивать не будет, но ведь это и не всегда нужно

Расширением механизма Statspack является AWR, хранящий уже величину приращения, а также сохраняющий данные по наиболее тяжёлым сессиям ASH. Этот механизм поставляется в составе СУБД, однако требует дополнительных лицензионных отчислений, о чём знают не все, и стоимость лицензии на Tuning Pack довольно высока. Именно из AWR графическая консоль администратора от Oracle (DB Console и Oracle Management Server) берёт информацию для построения любимых администраторами БД наглядных графиков работы экземпляра, и далеко не все догадываются, что, не лицензировав эту, включёную по умолчанию. опцию (Diagnostic Pack, включает ADDM, AWR и др. опции, $5000 за сокет, что сопоставимо со стоимостью сокета для редакции Standart Edition самой СУБД), они являются пиратами. Хотя, конечно, этот вопрос каждому стоит прояснить для себя из официальных источников

Вместе с тем, за исключением ASH, совсем неполной альтернативой которому является съём сессионных статистик либо триггером по окончании сессии, либо периодическим заданием, получить информацию о статистиках и событиях ожидания можно как напрямую из представлений счётчиков, являющихся базовым механизмом, так и из statspack, который может быть развёрнут без дополнительных лицензионных отчислений. Конечно без своих доработок это будет не так красиво, совсем не так удобно и без графиков, да и вглубь придётся влезть больше. Но возможно оно того стоит - ведь не всегда компания, в которой вы работаете, готова будет лицензировать топовые решения и дополнительные опции СУБД, да и агрегирующую надстройку написать не очень сложно

В качестве инструментов для администрирования, сбора статистик и проведения аналитики автор придерживается смешанного подхода. Если лицензирование вам позволяет - необходимо использовать поставляемые Oracle графические консоли и AWR. Но только как дополнение к «самописным» инструментам, позволяющим более глубоко понимать отдельные тонкости и грани работы СУБД

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

Этот продукт автора называется ОрСиМОН БЕССТ и предназначен для удобного доступа к текущему состоянию БД и обеспечивает быструю модификацию отдельных модулей при выявлении каких либо неточностей или необходимости дополнения. Средства разработки Perl/HTML/CGI. Такой подход, на взгляд автора, более нагляден, удобен и прост. Однако этот продукт не обеспечивает сбора и анализа статистик, но является удобной формой к административным представлениям БД

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

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

Модульная архитектура этого продукта позволяет наращивать функциональность по мере необходимости. Коллектор статистик разрабатывался для работы с версией движка 9i, написан на Pl/SQL, Perl и Shell, для отдачи информации пользователю используется Oracle HTTP server, с привлечением modPlSQL для форм аналитики на Pl/SQL и просто функциональность Apache для CGI форм на shell и perl. Разработка этого инструмента была заморожена автором в 2008 году в силу ряда причин

Администратор БД может создать свои продукты, аналогичные описанным авторским, для получения в чём то более удобных инструментов, чем предлагаемые штатные. И именно поэтому здесь говорится о продуктах автора цикла статей по мере приобретения опыта или сразу вы можете компоновать свой набор инструментария в дополнение к поставляемому компанией Oracle штатно, потому что удобство работы важный аспект для получения более качественного результата

Для построения графиков могут использоваться специализированные UNIX инструменты, например графическая библиотека GD с многочисленными программными интерфейсами, или такие утилиты, как GNUPlot, о чём запланирована отдельная статья

Что касается инструментов, предоставляемых Oracle, то, как уже говорилось, их использование в дополнение к другим инструментам приветствуется, они предоставляют некоторый набор функционала, удобный в первую очередь для получения наглядных графиков и «взвешенной» аналитики масштаба экземпляра и сессий, но скрывают тонкости реализации и требуют дополнительного лицензирования AWR

Сказанное касается инструментов, напрямую работающих с СУБД. Однако важно также получать и анализировать статистику операционной системы, с чем вполне справляются штатные утилиты ОС UNIX (напоминаю, что настоящий цикл ориентирован на IT специалистов - юниксоидов), такие, как sar, vmstat и т.д. Более детально использование таких утилит автор рассматривает в своей отдельной статье. Кроме того администраторы БД могут разработать надстройки над стандартными утилитами UNIX, аналогичные компоненте "коллектора и анализа статистики ОС" авторской системы КоСиКУЛС БЕССТ, в частности в разделе скриншотов представлены формы агрегации, анализа и построения графиков компоненты

Анализ статистик на достаточность и оптимальность конфигурирования компонент

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

Достаточность является критичным показателем, оптимальность опциональна, она позволяет по возможности уменьшить выделенные экземпляру ресурсы до величин, которые ещё не влияет на ухудшение производительности движка

-- из представления со статистиками V$SYSSTAT
Hit Ratio = 1 - ((physical reads - physical reads direct - physical reads direct (lob)) / (db block gets +
            consistent gets - physical reads direct - physical reads direct (lob))
-- из  V$BUFFER_POOL_STATISTICS
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
       1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
       FROM V$BUFFER_POOL_STATISTICS;
       

COLUMN object_name FORMAT a40
COLUMN number_of_blocks FORMAT 999,999,999,999
SELECT o.object_name, COUNT(1) number_of_blocks 
       FROM DBA_OBJECTS o, V$BH bh
       WHERE o.object_id  = bh.objd AND o.owner != 'SYS'
       GROUP BY o.object_name ORDER BY count(1);

SELECT SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, 
       ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READS
       FROM V$DB_CACHE_ADVICE
       WHERE NAME = 'KEEP'
             AND BLOCK_SIZE = (SELECT VALUE FROM V$PARAMETER 
                                      WHERE NAME = 'db_block_size')
             AND ADVICE_STATUS = 'ON';

SELECT namespace, sum(pins), sum(pinhits), sum(pins) / sum(pinhits) hitratio 
       FROM V$LIBRARYCACHE ORDER BY namespace ;

SELECT namespace, pins, pinhits, reloads, invalidations
       FROM V$LIBRARYCACHE ORDER BY namespace ;

SELECT NAME, VALUE FROM V$SYSSTAT
       WHERE NAME IN ('parse time cpu', 'parse time elapsed',
                      'parse count (hard)',
                      'CPU used by this session' );

col sql_text for a90
select UPPER(substr(sql_text,0,70)) AS SQL_TEXT, 
       sum(version_count) COUNT, sum(parse_calls) PARSE, 
       sum(executions) EXEC, sum(SHARABLE_MEM) MEM
       from v$sqlarea group by UPPER(substr(sql_text,0,70))
       having sum(version_count) > 25 order by 2 ;

SELECT * FROM V$SGASTAT 
       WHERE NAME = 'free memory' AND 
                      POOL = 'shared pool';

column parameter format a21
column pct_succ_gets format 999.9
column updates format 999,999,999
SELECT parameter, sum(gets), sum(getmisses), 
       100*sum(gets - getmisses) / sum(gets) pct_succ_gets, 
       sum(modifications) updates
       FROM V$ROWCACHE WHERE gets > 0
       GROUP BY parameter ;

SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) 
       "ROW CACHE" FROM V$ROWCACHE;       

SELECT NAME, VALUE FROM V$SYSSTAT
       WHERE NAME = 'redo buffer allocation retries';

SELECT AVG(lsize), MAX(lsize)
       FROM (SELECT (BLOCKS * BLOCK_SIZE)/((NEXT_TIME - FIRST_TIME) * 86400)
            as lsize
       FROM V$ARCHIVED_LOG) ;

SELECT LOW_OPTIMAL_SIZE/1024 low_kb, 
       (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
       OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, 
       MULTIPASSES_EXECUTIONS
       FROM V$SQL_WORKAREA_HISTOGRAM 
       WHERE TOTAL_EXECUTIONS != 0 ;

SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc, 
       onepass_count, round(onepass_count*100/total, 2) onepass_perc,
       multipass_count, round(multipass_count*100/total, 2) multipass_perc
       FROM (SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
                    sum(OPTIMAL_EXECUTIONS) optimal_count,
                    sum(ONEPASS_EXECUTIONS) onepass_count,
                    sum(MULTIPASSES_EXECUTIONS) multipass_count
                    FROM v$sql_workarea_histogram
                    WHERE low_optimal_size > 64*1024) ;

col sql_text format A80 wrap 
SELECT sql_text, sum(ONEPASS_EXECUTIONS) onepass_cnt,
       sum(MULTIPASSES_EXECUTIONS) mpass_cnt 
FROM V$SQL s, V$SQL_WORKAREA wa 
WHERE s.address = wa.address 
GROUP BY sql_text 
HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0;

col "O/1/M" format a10
col name format a20
SELECT operation, options, object_name name, trunc(bytes/1024/1024) "input(MB)",
       trunc(last_memory_used/1024) last_mem, trunc(estimated_optimal_size/1024) 
            optimal_mem, 
       trunc(estimated_onepass_size/1024) onepass_mem,
       decode(optimal_executions, null, null, 
              optimal_executions||'/'||onepass_executions||'/'||
              multipasses_executions) "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'
             AND p.hash_value=3738161960 ;

SELECT HASH_VALUE, SQL_TEXT, SORTS, EXECUTIONS
       FROM V$SQLAREA ORDER BY SORTS;

SELECT NAME, VALUE FROM V$SYSSTAT
       WHERE NAME IN ('sorts (memory)', 'sorts (disk)') ;

SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_MAX_MEM 
       FROM V$PROCESS;

-- 10 жрущих больше всего памяти
SELECT * FROM ( SELECT workarea_address, operation_type, policy, estimated_optimal_size
                       FROM V$SQL_WORKAREA
                       ORDER BY estimated_optimal_size )
         WHERE ROWNUM <= 10 ;

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%');

SELECT LOW_OPTIMAL_SIZE/1024 low_kb, (HIGH_OPTIMAL_SIZE+1)/1024 high_kb, 
       estd_optimal_executions estd_opt_cnt, 
       estd_onepass_executions estd_onepass_cnt, 
       estd_multipasses_executions estd_mpass_cnt 
       FROM v$pga_target_advice_histogram 
       WHERE pga_target_factor = 2 AND estd_total_executions != 0 
       ORDER BY 1 ;

SELECT AVG(lsize), MAX(lsize)
       FROM (SELECT (BLOCKS * BLOCK_SIZE)/((NEXT_TIME - FIRST_TIME) * 86400)
                    as lsize
                    FROM V$ARCHIVED_LOG) ;

SELECT AVG(usize), MAX(usize) 
       FROM (SELECT sum(UNDOBLKS)/sum((END_TIME - BEGIN_TIME) * 86400)
                    as usize
                    FROM V$UNDOSTAT) ;

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

Штатные инструменты типа statspack могут сравнивать только две точки среза, тогда как оптимальным является построение ряда данных по нескольким десяткам или даже сотням последовательных точек среза, возможно с построением графиков. Альтернативой является использование ADDM/AWR и DBConsole/OMS, но это путь, требующий дополнительного лицензирования и предполагающий только заложенную функциональность. Или же можно использовать самописные инструменты, позволяющие неограниченно расширять функциональность в соответствии с пониманием администратора, в том числе обеспечивающие группировку по дням, часам и т.п.

Также важно, что такой анализ возможен только при предварительном сборе статистической информации, что делается не всегда. Чудес не бывает для обеспечения результата должны быть сформированы определённые условия, а именно предварительный сбор статистик

Итак, в случае, когда указанные параметры находятся в пределах нормы, и проведённый параллельно анализ событий ожидания (о чём в следующем разделе) не выявил аномалий, а анализ утилизации аппаратных ресурсов средствами ОС не выявил нехватки каких либо ресурсов - можно говорить о достаточно качественном функционировании экземпляра, и об отсутствии основных «узких мест». В таком состоянии движок сконфигурирован достаточно оптимально и на существующей аппаратной платформе дальнейшая оптимизация работы решения должна вестись в сторону оптимизации прикладной части, а при необходимости - и аппаратной платформы. В случае же, когда отдельные из указанных параметров выходят за пределы нормы, необходимо провести дополнительное конфигурирование экземпляра

Анализ событий ожидания экземпляра

В процессе обработки пользовательских запросов и других функций движок СУБД может столкнуться с ситуацией, когда какие либо ресурсы заняты, и в этом случае ему приходится ждать освобождения требуемых ресурсов. Такие "события ожидания" являются непроизводительными, и их возникновение и характеристики движок отслеживает и сохраняет в процессе работы экземпляра. Информация о событиях ожидания доступна через административные представления, например V$SYS_EVENT и V$SESS_EVENT, и может быть проанализирована администратором для выявления проблемных мест

Фактически анализ событий ожидания, вместе с анализом основных статистических показателей и анализом статистик ОС, является основным методом контроля функционирования движка СУБД, и обеспечивает принятие обоснованных решений по оптимизации. Как и информация по статистикам, информация по событиям ожидания хранится в кумулятивных счётчиках, расположенных в памяти и доступных через административные представления. Как и для статистик, существуют разнообразные надстройки, позволяющие периодически сохранять информацию о событиях ожидания и анализировать её. К штатным надстройкам относятся statspack, не требующий дополнительного лицензирования, и AWR, входяхий в отдельно лицензируемый пакет ADDM. Штатные графические консоли DBConsole и консоль OMS используют информацию AWR, что подразумевает предварительное лицензирование пакета ADDM

Что можно добавить к штатным консолям ? Как всегда самописные решения. Сбор информации о событиях ожидания и вычисления "дельт", дающих приращения для рассматриваемого периода (периодов) времени можно организовать как на основе использования statspack, так и на основе непосредственного периодического съёма информации с административных представлений кумулятивных счётчиков экземпляра. В дальнейшем потребуется аналитика собранной информации. и здесь можно использовать мощь самописных решений для формирования требуемых администратору результатов

Важным моментом является существование так называемых "фиктивных" событий ожидания, отражающих не непроизводительные ожидания ресурсов, но простой в силу отсутствия заданий для обработки. Такие фиктивные события ожидания отражаются вместе с непроизводительными ожиданиями и не должны учитываться при проведении анализа и поиска узких мест. Фиктивные события ожидания описаны в административном представлении STATS$IDLE_EVENT (события простоя)

Другим важным моментом является необязательная, но крайне желательная возможность сбора дополнительной информации по событиям ожидания, относящаяся ко времени ожиданий. Эта возможность включается установкой инициализационного параметра движка TIMED_STATISTICS в ON, и обеспечивает адекватную аналитику с учётом не только количества возникновения событий ожидания, но и времени ожидания для каждого события. Без включения этой возможности анализ событий ожидания становится гораздо менее продуктивным

Ещё одним важным аспектом является различная детализация. Движок СУБД поддерживает сбор статистики и информации о бобытиях ожидания как для экземпляра в целом, так и для каждой текущей сессии. Информация о событиях ожидания для текущих сессий имеет существенно больший уровень детализации. Например, события ожидания текущих сессий могут отразить не только факт ожидания чтения информации в кэш буферов, но и указывают на конкретный файл и блок. Для агрегирующих представлений масштаба экземпляра такая информация уже недоступна, так как файли и блоки каждый раз будут разные, однако суммарная информация содержит данные об общем времени простоя по причине указанного события ожидания, и для анализа в масштабе экземпляра это вполне достаточная информация. Потом можно спускаться "ниже", но углублённый и сессионный анализ планируется рассмотреть отдельно в другом разделе

Таким образом первый горизонт аналитики событий ожидания задействует информацию о событиях ожидания масштаба экземпляра. Чтто может предложить здесь штатный инструментарий ? Не требующий дополнительного лицензирования statspack предлагает сравнение двух указанных ему точек среза, и вывод отчёта по ним. AWR предлагает не сильно больше возможностей - сравнение двух пар точек срезов, и требует дополнительных лицензионных отчислений. Насколько достаточно такой аналитики ? Получить информацию по двум точкам можно, но такой анализ не учтёт возможных отклонений в течении рассматриваемого периода. Поэтому в дополнение к предлагаемой штатным инструментарием возможностям имеет смысл добавить дополнительную аналитику. Это особенно важно, когда количество БД не ограничивается одной - двумя

Автор настоящего цикла статей использует свой метод "взвешенного анализа событий ожидания", при котором организован съём срезов информации за относительно короткие промежутки времени - например 20 или 30 минут. В дальнейшем для каждой пары соседних точек среза вычисляется значение приращений, и за рассматриваемый период, содержащий множество точек среза, производится выборка наиболее тяжёлых событий ожидания. Далее такие события упорядочиваются как по времени ожиданий, так и по количеству попадания каждого такого ожидания в общую выборку. Полученная аналитическая картина является гораздо более наглядной, чем предоставляемое штатным инструментарием сравнение двух точек

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

SELECT class, count FROM V$WAITSTAT
       WHERE count > 0 ORDER BY count DESC;

SELECT segment_owner, segment_name
       FROM DBA_EXTENTS
       WHERE file_id = <&p1>
             AND <&p2> BETWEEN block_id AND 
                       block_id + blocks - 1;

SELECT s.sql_address, s.sql_hash_value
       FROM V$SESSION s, V$SESSION_WAIT w
       WHERE w.event LIKE 'db file%read'
             AND w.sid = s.sid ;

SELECT segment_owner, segment_name FROM DBA_EXTENTS
       WHERE file_id = &p1 AND &p2 between 
             block_id AND block_id + blocks - 1 ;

# отразить ждущие освобождения блокировки
SELECT * FROM V$LOCK WHERE request > 0:

# отразить блокирующие, для которых есть ждущие
SELECT DECODE(request,0,'Holder: ','Waiter: ')|| sid sess, 
       id1, id2, lmode,request, type
       FROM V$LOCK
       WHERE (id1, id2, type) IN (SELECT id1, id2, type 
                                         FROM V$LOCK WHERE request>0)
       ORDER BY id1, request;

# текущие ожидания защёлок
SELECT n.name, SUM(w.p3) Sleeps
       FROM V$SESSION_WAIT w, V$LATCHNAME n
       WHERE w.event = 'latch free' AND
             w.p2 = n.latch#
       GROUP BY n.name ;

# сравнить вручную
SELECT sql_text FROM V$SQLAREA
       WHERE executions < 4
       ORDER BY sql_text;

# найти отличающиеся литералами полуавтоматом
SELECT SUBSTR(sql_text,1, 60), COUNT(*) FROM V$SQLAREA
       WHERE executions < 4
       GROUP BY SUBSTR(sql_text, 1, 60)
       HAVING COUNT(*) > 1;

# поиск постоянно перепарсиваемых для тюнинга
# когда PARSE_CALLS стремится к EXECUTIONS
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS
       FROM V$SQLAREA
       ORDER BY PARSE_CALLS ;

# поиск сессий с постоянным перепарсиванием
SELECT pa.sid, pa.value "Hard Parses", ex.value "Execute Count"
       FROM v$sesstat pa, v$sesstat ex
       WHERE pa.sid=ex.sid
             AND
             pa.statistic# = (select statistic# FROM v$statname
                                     where name='parse count (hard)')
             AND
             ex.statistic# = (select statistic# FROM v$statname
                                       where name='execute count')
             AND pa.value > 0 ;

Анализ профиля загрузки

статья не завершена

Белонин С.С. (С), октябрь 2010 года