введение

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

Выложен он как обычно - без какой либо ответственности и с надежной, что кому нибудь оно поможет. Цифры после имени разделов в скобках соответствуют разделам родной документации Oracle по тюнингу 9i, где и можно получить более детальную информацию. Использовать этот перевод можно, но вся ответственность не на авторе, но на использующем. Ну и не забываем при использовании указывать ссылку на настоящий материал и моё имя - как автора перевода. Где еще можно получить информацию - в родной документации от Oracle, правда на английском, купив официальный русский перевод (делает компания RdTex, стоит, насколько помню, порядка 6-10 тыс. руб.), или купив книги каких либо авторов, что явно не есть первоисточник.За сим - поехали

конфигурирование и использование памяти БД Oracle9i (14)

Корректно сконфигурированные и управляемые кэши памяти могут резко увеличить производительность системы. Oracle хранит данные в памяти и на дисках, причем обращение к дискам существенно медленнее и потребляет вспомогательные ресурсы CPU, так что использование памяти предпочтительно. Целью оптимизациия является максимально допустимое снижение обращений к дискам и замещение их обращениями к кэшам памяти, которые есть shared pool, large pool, java pool, buffer cache, log buffer и process private memory. Причем размер shared pool, large pool, buffer cache и process private memory можно менять динамически. Память в shared pool, large pool, java pool, buffer cache выделяется в гранулах, равных 4Мб при SGA до 128Мб, иначе 16Мб, текущий размер гранулы можно увидеть в V$SGA_DYNAMIC_COMPONENTS. Общий доступный инстансу размер памяти выставляется параметром SGA_MAX_SIZE, который можно указать достаточно большим для последующего динамического увеличения отдельных пулов

Для Oracle 9i и выше доступны информационные представления V$SGA_CURRENT_RESIZE_OPS (операции в прогрессе), V$SGA_RESIZE_OPS (100 последних завершенных операций), V$SGA_DYNAMIC_COMPONENTS (результат всех операций со старта инстанса), V$SGA_DYNAMIC_FREE_MEMORY (доступная для будущего ресайзинга память SGA)

Конфигурирование кэшей должно соответствовать потребностям приложения, что может резко уменьшить утилизацию ресурсов (защелок кэшей, CPU, I/O). Важно, чтобы приложение писалось для эффективного использования СУБД Oracle. Важным является максимальное уменьшение пэйджинга (перенесения страниц памяти на диск), что бывает при нехватке памяти. SGA необходимо располагать в физической память (параметр LOCK_SGA предотвращает пэйджинг). Для просмотра текущей конфигурации можно использовать команду SHOW SGA. При выделении памяти для экземпляра необходимо оставить память для серверных процессов (обслуживающих запросы пользователей к экземпляру) и для других программ, работающих на этом хосте. Также нужно быть готовым к итеративному характеру процесса конфигурирования памяти

кэш буферов (buffer cache)

Статистика по buffer cache представлена в V$DB_CACHE_ADVICE (требует параметра DB_CACHE_ADVICE = ON) и коэффициентах попадания в память. V$DB_CACHE_ADVICE (отражает ОЦЕНКУ ввода/вывода при изменении размера buffer cache), а вывод можно получить командой:

  
COLUMN size_for_estimate          
       FORMAT 999,999,999,999 heading 'Cache Size (MB)'
COLUMN buffers_for_estimate       
       FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor  
       FORMAT 999.90 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads        
       FORMAT 999,999,999 heading 'Estd Phys| Reads'
  
SELECT size_for_estimate, buffers_for_estimate, 
       estd_physical_read_factor, estd_physical_reads
       FROM V$DB_CACHE_ADVICE 
       WHERE name = 'DEFAULT' AND block_size = 
             (SELECT value FROM V$PARAMETER 
	             WHERE name = 'db_block_size') 
             AND advice_status = 'ON' ;

Кстати стоит учесть, что отражаемые Oracle ожидания ввода вывода не обязательно есть физические чтения - это может быть и чтение из буффера файловой системы. Коэффициент попадания в буффер отражает эффективность использования буффера - как часто запрошенный блок находился в кэше без обращения к диску, при вычислении используются статистики 'session logical reads' (чтений блока с диска и из памяти), 'physical reads' (запрос блока, приводящий к доступу к файлам данных на диске, запрошенный блок пишется кеш или, при прямом чтении, в выделенный блок памяти), physical reads direct (физических чтений, пропуская LOB и пропуская чтения в buffer cache), physical reads direct (lob) (физических чтений в LOB, пропуская чтения в buffer cache), db block gets (чтения неизмененных блоков), consistent gets (чтение восстановленных консистентных блоков)

Вычисляется по формуле: Hit Ratio = 1 - ((physical reads - physical reads direct - physical reads direct (lob)) / (db block gets + consistent gets - physical reads direct - physical reads direct (lob))

Интерпретация статистики должна учитывать контекстные детали. Низкий коэффициент попадания в кэш не подразумевает увеличения производительности при увеличении размера кэша, но высокий коэффициент говорит об адекватной конфигурации размера buffer cache. Частое повторное сканирование больших таблиц и индексов уменьшает коэффициент попадания. По возможности нужно избегать повторяющихся запросов, кэшируя данные на промежуточной или клиентской стороне. При долгом сканировании полученные блоки не попадают в голову списка последних использованных (LRU), в результате блоки устаревают раньше, чем читаются для проведения индексного просмотра или сканирования малых таблиц (малые - до 2% от buffer cache, больше 20 - большие). В результате низкий коэффициент попадания в процессе корректного full scan больших таблиц также нужно учитывать при интерпретации статистики. В OLTP системах каждая строка обычно используется около одного раза и смысла хранение дянных в buffer cache нет. Распространенная ошибка - начинать увеличивать размер buffer cache без учета особенностей, что не даст результата в случае full scan больших таблиц или оптимизации операций, не использующих buffer cache

Предпосылки увеличения buffer cache - низкий коэффициент попадания при оптимизированном для избегания full scan приложении. Увеличение делается установкой нового значения параметра DB_CACHE_SIZE, при этом DB_CACHE_ADVICE выключается, а V$DB_CACHE_ADVICE содержит старые данные до последующего включения DB_CACHE_ADVICE. Этот параметр указывается в количестве блоков табличных пространств с размером по умолчанию, но при необходимости использовать табличные пространства с отличным от умалчиваемого размером блока, также необходимо выделить для них свои buffer cache установкой параметров DB_nK_CACHE_SIZE

В случае высокого коэффициента попадания, а также небольшого ожидаемого прироста I/O, или же если память нужна для другого, можно уменьшить buffer cache изменением значения параметра DB_CACHE_SIZE (или DB_nK_CACHE_SIZE соответственно). Также существует возможность выделить несколько пулов buffer cache для одного размера блока (DEFAULT, KEEP - параметр DB_KEEP_CACHE_SIZE, RECYCLE - параметр DB_RECYCLE_CACHE_SIZE), причем для каждого соответствующего объекта пул указывается опцией BUFFER_POOL в классе STORAGE. Для получения данных для каждого отдельного пула можно воспользоваться командами:

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 NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
       1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))
           "Hit Ratio"
       FROM V$BUFFER_POOL_STATISTICS;

Для определения объектов, занимающих много буферов, используется представление V$BH (блоки в buffer cache) и следующие 2 метода. Метод 1:

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

Метод 2 работает для одного сегмента
- найти ID - SELECT DATA_OBJECT_ID, OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME = UPPER('SEGMENT_NAME') ;
- найти количество буферов в кэше - SELECT COUNT(*) BUFFERS FROM V$BH WHERE objd = найденный_ID ;
- найти количество буферов экземпляра - SELECT NAME, BLOCK_SIZE, SUM(BUFFERS) FROM V$BUFFER_POOL GROUP BY NAME, BLOCK_SIZE HAVING SUM(BUFFERS) > 0 ;
- вычислить процент использования буферов объектом - = буферов_занятых_объектом / буферов_всего

разделяемый (shared pool)

shared pool используется для хранения разных объектов, в т.ч. данные словаря, блоки PL/SQL, запросы SQL в текстовой и разобранной форме и т.п. Его использует конфигурация разделяемых серверов, параллельные запросы и RMAN. Корректное конфигурирование shared pool влияет на производительность возможностью хранения разобранных запросов (что уменьшает утилизацию CPU) и данных словаря в памяти, причем словарь еще и модифицируется в памяти, также уменьшает утилизацию защёлок. Выделение памяти в shared pool производится в кусках (chunks), сто позволяет размещать большие объекты в непоследовательных областях. Свободные непоследовательные области учитываются и носят назрание резервного пула (reserved pool). Еще одна отличительная черта - использование LRU механизма устаревания данных, когда наименее используемые данные устаревают первыми

Для эффективного использования библиотечного кэша рекомендуется на использовать связанные переменные для хранилищ данных, т.к. в этом случае оптимизатор может определить селективность столбцов. А вот для OLTP систем правильно использовать разделяемые курсоры (использовать связанные переменные, избегать динамического SQL, избегать изменения опций оптимизатора в сессиях пользователей, приветствовать стандартизацию имен при разработке и использование хранимых процедур). Также рекомендуется сохранять одно соединение для сессии и избегать новых соединений для каждого запроса, рекомендуется явно указывать объекты с именем схемы, избегать отработки DDL на загруженных системах в пиковые часы, а также рекомендуется выделять достаточный кэш для обновления SEQUENCE (в термах CREATE и ALTER), что уменьшает блокировки словаря. Важно грамотно разрабатывать приложения, в том числе - не закрывать курсоры т, тем более, сессии, вместо этого менять значения связанных переменных

Основной целью оптимизации библиотечного кэша является повторное использование сохраненных разобранных запросов без излишнего выделения памяти. Это актуально на OLTP системах и не очень важно на DSS. Оптимально, когда shared pool больше размера часто используемых объектов. Количество переразборов в колонке RELOADS представления V$LIBRARYCACHE должно стремиться к нулю, как и значение колонки INVALIDATIONS (например, из за отработанных DDL). Другой ключевой статистикой является количество свободной памяти в shared pool в пиковые часы. Эта статистика доступна в V$SGASTAT, и оптимально, когда количество свободной памяти минимально при отсутствующих перезагрузках (reloads). Еще одним индикатором является коэффициено попадания в библиотечный кэш. Ниже приводятся примеры команд:

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

Library Cache Hit Ratio = sum(pinhits) / sum(pins)

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

Представление V$SHARED_POOL_ADVICE отражает ожидаемое время разбора при изменении размера shared pool, а V$LIBRARY_CACHE_MEMORY отражет распределение памяти в библиотечном кэше по типам объектов. Представление V$ROWCACHE отражает сведения о кэше словаря, пример:

 
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;       

Для сохранения SQL областей после первичного выделения необходимо увеличивать размер библиотечного кэша до момента, когда количество перезагрузок (reloads) будет стремиться к нулю. Увеличение доступной shared pool памяти производится увеличением параметра SHARED_POOL_SIZE. Также может потребоваться увеличить количество открытых в сессии курсоров, за чтототвечает параметр OPEN_CURSORS. Применительно к кэшу словаря коэффициент попадания в кеэ не должен превышать 10-15%, увеличение доступной кэшу словаря памяти произвадится увеличением значения параметра SHARED_POOL_SIZE. Этим же параметром можно уменьшать размер shared pool

Также полезно посмотреть в посесионную информацию об утилизации памяти из V$SQLAREA (агрегированные по тексту SQL курсоры) и v$SQL (cshst rehcjhs):

 
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 ;

большой пул (large pool)

большой пул (large pool) не использует механизмы устаревания (LRU age out) и используется параллельными запросами (для кэширования результатов), RMAN (для кэширования буферов ввода/вывода - размером в сотни килобайт - при бэкапе и восстановлении), в архитектуре разделяемых серверов MTS

В MTS архитектуре, чтобы разгрузить используемый по умолчанию shared pool, полезно сконфигурировать large pool параметром LARGE_POOL_SIZE, однако даже в этом случае нужно помнить, что небольшой объём памяти в shared pool будет задействован. Также полезно установить равными параметры SORT_AREA_SIZE и SORT_AREA_RETAINED_SIZE, что позволит хранить сортировки по возможности в large pool, а не на диске). Также имеет смысл обратить внимание на сессионные статистики "session UGA memory" и "Session UGA memory max", отражающих утилизацию пользовательской памяти (для dedicated эта часть памяти пользовательских процессов, для MTS - часть shared pool)

SELECT SUM(VALUE) || ' BYTES' "TOTAL MEMORY FOR ALL SESSIONS"
       FROM V$SESSTAT, V$STATNAME
       WHERE NAME = 'session uga memory'
             AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;

SELECT SUM(VALUE) || ' BYTES' "TOTAL MAX MEM FOR ALL SESSIONS" 
       FROM V$SESSTAT, V$STATNAME
       WHERE NAME = 'session uga memory max'
             AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;

Параметр PRIVATE_SGA позволяет ограничить размер каждой пользовательской сессией области SGA для MTS архитектуры. Конец ремарки про MTS

Если попадания в библиотечный кэш 100%, можно установить параметр CURSOR_SPACE_FOR_TIME в true, что предотвратит выгрузку соответствующих SQL area до закрытия всех связанных с ней курсоров (но если памяти для размещения нового запроса не хватит - разбора не будет и Oracle выдаст ошибку, так что использовать очень аккуратно)

Отдельные курсоры могут использоваться в различных сессиях, и их можно сохранять между сессиями в кэше разделяемых курсоров (управляемым алгоритмом LRU), указав параметром SESSION_CACHED_CURSORS максимальное количество хранимых курсоров. Детектируется количество запросов на разбор для SQL предложения, и при значении более трех такой курсор является кандидатом на сохранени в кэше разделяемых курсоров. Определить адекватность размера кэша сессионных курсоров поможет системная статистика 'session cursor cache hits', отражающая количество найденных в сессионном кэше разборов. Полезно сравнивать с общим количеством разборов

резервный пул (reserved pool)

Хотя Oracle выделяет память в shared pool небольшими кусками (минимальный 4400 байт), может потребоваться выделение большого непрерывного куска, для чего Oracle резервирует часть shared pool - параметр SHARED_POOL_RESERVED_SIZE = default 5%, больше 50% не даст. При выделении памяти сначала происходит попытка выделения в shared pool, если места нет - в reserved shared pool, если места нет - попытка освободить память от устаревших объектов для размещения сначала в shared pool, затем reserved shared pool. Для тюнинга используется представление V$SHARED_POOL_RESERVED, причем если есть память для увеличения SGA, цель - обеспечить стремление к нулю REQUEST_MISSES, если же размер памяти ограничен, то цель не получать REQUEST_FAILURES или хотя бы ограничить их рост. Рекомендации таковы:

• резервный пул слишком мал, если значение REQUEST_FAILURES больше нуля и растет. Увеличивать резервный пул и разделяемый пул вообще
• резервный пул слишком велик, если REQUEST_MISSES равно нулю и не растет, а FREE_MEMORY больше 50% от SHARED_POOL_RESERVED_SIZE. Уменьшать резервный пул
• индицируется недостаточность shared pool - если REQUEST_FAILURES больше нуля и растет, то при включении резервного пула (SHARED_POOL_RESERVED_SIZE больше нуля) необходимо уменьшить размер резервного пула, а при выключенном резервном пуле - увеличивать размер SHARED_POOL_SIZE

Для управления shared pool, в частности для предотвращения выгрузки объектов, может использоваться пакет DBMS_SHARED_POOL. Также для принудительной попытки расшаривать похожие курсоры можно установить параметр CURSOR_SHARING в значение SIMILAR или FORCE, что позволит несколько сгладить отсутствие техники разделяемых переменных при разработке приложений. Не рекомендуется для DSS, д и вообще рекомендуется использовать связанные переменные ВМЕСТО. Когда можно - когда в библиотечном кэшк есть масса запросов, отличающихся только значениями переменных И при этом большое количество не попаданий в библиотечный кэш

Java pool

Декументация не дает описания, но отсылает к руководству разнаботчика Oracle на Java

буффер оперативных журналов (redo logs buffer)

Ёмкость буфера определяет параметр LOG_BUFFER, и особенно важна для производительности системы с частыми изменениями данных. Статистика 'redo buffer allocation retries' должна стремиться к нулю и не должна увеличиваться. Также важно проверять событие ожидания 'log buffer space', чем оно реже и короче, тем адекватнеее сконфигурирован буффер

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

Методами оптимизации наряду с увеличением размера являются перенос оперативных журналов на выделенное быстрое устройство, тюнинг процессов записи архивных журналов

PGA

PGA является областью памяти, содержащей данные и контрольную информацию серверных процессов (по терминологии курсов Oracle, серверный процесс - это процесс, запускаемый в dedicated режиме для взаимодействия с клиентом, но никак не CORE процессы экземпляра, эти последние называют фоновыми). Пример - runtime cursor area, создаваемая при каждом выполнении курсора - новая - серверным процессом и размещаемая в PGA (в MTS режиме часть может быть в SGA). Большая часть такой work area выделяется под интенсивные операторы - основанные на сортировках (ORDER BY, GROUP BY, ROLLUP, оконные функции), Hash-join, Bitmap merge, Bitmap create, запись буферов пакетных загрузок данных

Work area поддаются тюнингу. Оптимальным размером является вмещающий данные и дополнительные информационные структуры для каждого конкретного запроса SQL. Если памяти меньше, то Oracle при обработке запроса проводит множественное обращение к данным. Если достаточно одного добавочного прохода, это называют однопроходный (one-pass) размер work area, если недостаточно - это многопроходный размер work area. Целью оптимизации является обеспечение оптимального размера work area, при незначительном проценте однопроходных случаев. "Многопроходной" конфигурации следует избегать как для OLTP, так и для DSS

Для версий Oracle до 9 предусматривались множественные конфигурационные параметры SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE и CREATE_BITMAP_AREA_SIZE, довольно плохо поддающиеся оптимизации. С 9 версии рекомендуется использовать автоматическое распределение памяти в PGA\ установкой параметра PGA_AGGREGATE_TARGET (не работает в MTS режиме)

Хорошим правилом при начальним выделении PGA является для OLTP (total_mem * 80%) * 20%, и для DSS (total_mem * 80%) * 50%, Для последующего мониторинга используются представления V$PGASTAT (отражает общую статистику PGA), V$PROCESS (отражает посессионное использование PGA), V$SQL_WORKAREA_HISTOGRAM (отражает количество отработанных work area c optimal, one-pass и multi-pass размером памяти), V$SQL_WORKAREA_ACTIVE, V$SQL_WORKAREA (кумулятивная статистика по каждому загруженному курсору). Ниже приведены примеры запросов:

-- общая статистика PGA
SELECT * FROM V$PGASTAT ;

-- посессионная статистика PGA
SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_MAX_MEM 
       FROM V$PROCESS;

-- распределение по типу выделенного PGA
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) ;

-- активные work area
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type OPERATION, trunc(EXPECTED_SIZE/1024) ESIZE,
       trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
       NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
       FROM V$SQL_WORKAREA_ACTIVE ORDER BY 1,2 ;
       
-- 10 жрущих больше всего памяти
SELECT * FROM ( SELECT workarea_address, operation_type, policy, 
                       estimated_optimal_size
                       FROM V$SQL_WORKAREA
                       ORDER BY estimated_optimal_size )
         WHERE ROWNUM <= 10 ;

-- курсоры с одной или несколькими рабочими областями 
-- в режиме one-pass или multi-pass
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;

-- по данным ADDRESS и HASH_VALUE можно вычислить текущий 
-- план исполнения, соответствующий рабочей области
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 ;

-- вычислить ADDRESS и HASH_VALUE можно так
SELECT address, hash_value FROM V$SQL 
       WHERE sql_text LIKE '%my_pattern%';

Для проведения оптимизации рекомендуется использолвать аналитические представления V$PGA_TARGET_ADVICE и V$PGA_TARGET_ADVICE_HISTOGRAM (также необходимо включить PGA_AGGREGATE_TARGET и установить STATISTICS_LEVEL в TYPICAL или ALL. Применительно к V$PGA_TARGET_ADVICE необходимо избегать попадания в зону overhead (столбец ESTD_OVERALLOC_COUNT представления V$PGA_TARGET_ADVICE), а также стремиться к увеличению hit ratio. Представление V$PGA_TARGET_ADVICE_HISTOGRAM отражает отработку с потимальным, одно- и многопроходным размером work area

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 ;

Также полезно получать информацию о количестве рабочих областей с различными типами размеров, для чего можно использовать представления V$SYSSTAT и v$SESSTAT

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

Оптимизация с помощью параметра SORT_AREA_SIZE допустима только для конфигурации разделяемых серверов (MTS) или при выключенном PGA_AGGREGATE_TARGET, причем в этом втором случае категорически рекомендуется SORT_AREA_SIZE включить. Сортировки включают следующие причины CREATE INDEX, SELECT .... ORDER BY, SELECT DISTINCT, SELECT .... GROUP BY, SELECT ... CONNECT BY, SELECT ... CONNECT BY ROLLUP и sort merge joins. Если значением параметра WORKAREA_SIZE_POLICY является MANUAL, то максимальное количество доступной сортировкам памяти определено параметром SORT_AREA_SIZE. Если сотрировка не умещается в выделенной памяти, она разбивается на части, промежуточный результат каждой части сохраняется во временном табличном пространстве пользователя, далее все результаты сливаются за один (если памяти хватает) или несколько проходов. Ввод и вывод сохраняемых частей входят в операцию сортировки и не затрагивает buffer cache. Oracle собирает соответствующие сортировкам статистики в V$SQLAREA и V$SYSSTAT ('sorts (memory)', 'sorts (disk)'), причем целью оптимизации в OLTP является избегание дисковых сортировок

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

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

С версии Oracle 8.0 память под сортировки выделяется не целиком, а кусками (chunks), размером DB_BLOCK_SIZE до достижения предела в SORT_AREA_SIZE (это динамический параметр, определяющий максимальный размер памяти для каждой сортировки). При работе многих сессий происходит множественное выделение кусков, что может снизить производительность, и, в случае недостатка свободной памяти, породить своппинг и пэйджинг. Так что есть ситуации, когда увеличение SORT_AREA_SIZE не приносит улучшения производительности. Если существенное количество сортировок требует дисковых операций, то увеличение SORT_AREA_SIZE может увеличить производительность (особенно для OLTP систем, но и к DSS применимо). Параметр SORT_AREA_RETAINED_SIZE отражает минимальный размер памяти, до которого принудительно уменьшается SORT_AREA_SIZE после того, как данные сортировки начинают отдаваться пользователю или следующей обработке. Для выделенных серверов освобождаемая память не отдается ОС, но остаётся доступной сессии, так что особого смысла установка этого параметра не имеет. В MTS конфигурации этот параметр позволяет экономить память, но может приводить к дополнительным дисковым операциям

Еще особенность - можно избежать сортировок при создании индексов и операциях группировки, если заранее известно, что начальные данные отсортированы заранее (для этого используются термы CREATE INDEX ... NOSORT и GROUP BY NOSORT)

конфигурирование ввода / вывода БД Oracle9i (15)

Вводная часть содержит базовую информацию по дискам, массивам и т.п. Понятно, что подсистема ввода / вывода очень важна, но говорится, что корректно спроектированное Oracle приложение мало зависит от дисковой подсистемы. Ну, как частный случай - да. Таки дисковая подсистема имеет ключевые для целей настоящего перевода параметры - скорость чтения и записи, пропускная способность по обслуживанию конкурентных запросов, размер атомарного размера I/O запроса информацией (для OLTP оптимален DB_BLOCK_SIZE, для DSS - DB_BLOCK_SIZE * n), обеспечение доступности и размер хранилища. Разные виды сохраненных данных имеют разные характеристики:

• Archive logs - скорость чтения High, скорость записи High, конкурентность Low
• Redo logs - скорость чтения High, скорость записи High, конкурентность Low
• Undo segment tablespaces - скорость чтения Low, скорость записи High, конкурентность High
• TEMP tablespaces - скорость чтения Low, скорость записи Low, конкурентность High
• Index tablespaces - скорость чтения Low, скорость записи Medium, конкурентность High
• Data tablespaces - скорость чтения High, скорость записи Medium, конкурентность High
• Application log and output files - скорость чтения Low, скорость записи Medium, конкурентность High
• Binaries (shared) - скорость чтения Low, скорость записи Low, конкурентность High

Также важно учитывать - количество и размер дисков, тип RAID и характеристики stripe, возможность использования сырых (RAW) устройств и асинхронного ввода/вывода, коррости ввода / вывода на последовательных и случайных опреациях ... Статистику по вводу выводу формируют параметры количества чтений (V$FILESTAT.PHYRDS) и количество записей (V$FILESTAT.PHYWRTS), среднее время чтения, количество операций ввода / вывода - отражающие как общее количестов, так и скорость операций в единицу времени. Для управления режимами ввода / вывода (синхронный/асинхронный, прямой I/O) используется параметр FILESYSTEMIO_OPTIONS. Также важно выбрать адекватный размер блоков БД, зависящий от типа системы (OLTP или DSS), средней длины строк, режима доступа к строкам и т.п.

восстановление экземпляра (Instance Recovery 17)

Применительно к сбою экземпляра Oracle производит восстановление автоматически, разбивая его на стадии подката данных из журналов и последующего отката незавершенных транзакций. Периодически Oracle отрабатывает контрольную точку (checkpoint), гарантирующую сброс изменений ранее SCN в файлы данных, что позволяет сократить время восстановления экземлпяра. Для оптимизации отработки контрольных точек можно - оптимизировать размер оперативных журналов до переключения раз в 12 минут, оптимизировать значения параметорв LOG_CHECKPOINT_INTERVAL (интервал изменений в блоках, ограничен 90% от самого маленького журнала, определяется в V$INSTANCE_RECOVERY.LOG_FILE_SIZE_REDO_BLKS), LOG_CHECKPOINT_TIMEOUT (интервал отработки контрольных точек в секунах, по умолчанию отключен), FAST_START_IO_TARGET (верхний лимит количества грязных блоков, ограничение - не меньше 1000), FAST_START_MTTR_TARGET (заказываемый интервал восстановления экземпляра в секундах, при этом первые три параметра нужно выключить вручную) позволяют настроить или отключить fast start checkpointing, RECOVERY_PARALLELISM (количество одновременно отрабатывающих процессов восстановления экземпляра, не может быть больше PARALLEL_MAX_SERVERS), LOG_PARALLELISM (количество одновременно работающих процессов обработки оперативных журналов)

Автоуправление временем восстановления органичивается величиной 3600 секунд сверху и минимальным количестовм грязных буфферов (1000). Эффективный интервал восстановления можно получить в V$INSTANCE_RECOVERY.MTTR_TARGET. Представление V$INSTANCE_RECOVERY содержит общие сведения о режимах восстановления. Т.к. режим автоматического управления временем восстановления генерирует дополнительный ввод / вывод, такое превышение можно расчитать, обработав две представленные ниже выборки (разнесенные на начало и конец периода) по формуле

SELECT NAME, VALUE FROM V$SYSSTAT
       WHERE NAME IN ('physical reads','physical writes',
             'physical writes non checkpoint');
[((PW_2 - PW_1) - (PWNC_2 - PWNC_1)) / 
 ((PR_2 - PR_1) + (PW_2 - PW_1))] x 100% = EIO

При включении FAST_START_MTTR_TARGET и установке STATISTICS_LEVEL в TYPICAL или ALL начинает заполняться аналитическое представление V$MTTR_TARGET_ADVICE. При выключении автоматического управления временем восстановления представление сохраняет данные прежнего включения

Вторая фаза восстановления - откат незавершенных транзакций, активизируется автоматически после открытия базы и может быть дополнительно распараллелена параметром FAST_START_PARALLEL_ROLLBACK, после чего SMON выступит в роли диспетчера этой операции, а результат можно увидеть в представлениях V $FAST_START_SERVERS (все процессы отката) V$FAST_START_TRANSACTIONS (откачиваемые транзакции)

конфигурирование UNDO и временных сегментов БД Oracle9i (18)

Oracle категорически рекомендует использовать UNDO_MANAGEMENT=AUTO. При выборе ручного управления нужно понимать, что есть рекомендация о количестве сегментов отката, зависящего от количества одновременных транзакций - до 16 транзакций - 4 сегмента, до 32 - 8 сегментов, выше - количество одновременных транзакций / 4. Что касается размера, нужно учитывать, что для DSS нужны большие сегменты отката, а для OLTP нужны поменьше, но большее количество

Тюнинг временных табличных пространств оптимизирует сортировки. Рекомендуется устанавливать параметры хранения - PCTINCREASE в ноль, INITIAL и NEXT одинаковыми и соотносимыми с SORT_AREA_SIZE. В качестве временных могут быть использованы разные виды табличных пространств - TEMPORARY (появилось в Oracle 9i, создается командой CREATE TEMPORARY TABLESPACE. Самый эффективный вариант - LMT сегмены создаются один раз при старте экземпляра, процессы используют сушествующие экстенты, при необходимости разово добавляются новые, экстенты могут быть размазаны по страйпу). Менее эффективным типам является табличное пространство с типом временное (не путать с временными табличными пространствами) - управляется словарем, сегменты создаются при первой сортировке, экстенты добавляютя по необходимости, создается командой CREATE TABLESPACE с классом TEMPORARYЮ. Самый плохой выбор - постоянные табличные пространства - каждый процесс создает свой сегмент, повторно сегменты не используются, одна операция может потребовать массового размещения и освобождения экстентов. Также во временных табличных пространствах и табличных пространствах с типом временное создавать постоянные объекты нельзя

конфигурирование SHARED серверов (19)

Для разделяемых серверов состояение доступно в V$DISPATCHER (общая информация о диспетчерах) и V$DISPATCHER_RATE (статистика процессов - диспетчеров). Включение режима определяется параметрами DISPATCHERS (текущее количество диспетчеров) и MAX_DISPATCHERS (максимальное количество диспетчеров), SHARED_SERVERS (не меньше 10) и MAX_SHARED_SERVERS (по умолчанию 2 * SHARED_SERVERS). Кроме увеличения количества диспетчеров можно автивировать пул, например, установкой параметра DISPATCHERS = "(PROTOCOL=TCP)(POOL=ON)" или включение м режима мультиплексирования (несколько сессий одним диспетчером) установкой параметра DISPATCHERS = "(PROTOCOL=TCP)(MULTIPLEX=ON)". Статистика очереди запросов к разделяемым серверам доступна в V$QUEUE, а количество запущенных разделяемых серверов в представлении V$SHARED_SERVER

SELECT DECODE(TOTALQ, 0, 'No Requests',WAIT/TOTALQ || ' HUNDREDTHS OF SECONDS')
       "AVERAGE WAIT TIME PER REQUESTS" FROM V$QUEUE WHERE TYPE = 'COMMON';   

SELECT COUNT(*) "Shared Server Processes" FROM V$SHARED_SERVER
       WHERE STATUS != 'QUIT';

Для предотвращения использования разделяемых серверов новыми пользователями нужно выставить SHARED_SERVERS в ноль (ALTER SYSTEM), установка положительного значения включает доступность разделяемых серверов. Для временного выключения диспетчеров нужно использовать команду ALTER SYSTEM SHUTDOWN [IMMEDIATE], а для включения - выставить положительное значение DISPETCHERS

утилиты для сбора статистики БД Oracle9i (20)

Существуют следующие механизмы сбора статистики - Oracle Diagnostic Pack,statspack,UTLBSTAT/UTLESTAT,представления V$xxx. Важно собирать статситику периодически, чтобы иметь возможность сравнить результаты в нормальные и проблемные периоды времени и отследить изменения в состоянии системы. Также важно агрегировать статистику операционной системы (в документации представлена простая методика организации накопления статистичесих срезов)

При анализе статистики рекомендуется использовать:

• Коэффициенты попадания (hit ratio). Например коэффициенты попадания в buffer cache, мягких разборов, защелок. Используются как индикаторы
• События ожидания (wait events) с временной' статистикой. Требуют включения опции TIMED_STATISTICS
• События простоя, статистика ОС и т.п.

тюнинг экземпляра (22)

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

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

• статистики CPU - системная, пользователская и I/O загрузка, процессы не-Oracle и Oracle, Oracle статистика использования CPU - v$sysstat, v$sesstat, V$RSRC_CONSUMER_GROUP. Также можно использовать отчеты statspack при распределении CPU по различным оракловым процессам
• статистики ввода / вывода - если очередь превышает два или время обслуживания больше 20-30 ms. Для ОС это sar -d и iostat, также собираются события Oracle из V$SYSTEM_EVENT ('db file sequential read', 'db file scattered read', 'db file single write', 'db file parallel write'). Увязка этих данных с отчетами Statspack выявит "горячие" файлы и табличные пространства, также полезно сравнить эти данные со статистикой ОС. Перед переконфигурированием подсистемы ввода/вывода нужно оптимизировать "горячие" SQL запросы, которые можно выявить по большому количеству физических чтений в V$SQLAREA или по разделу 'SQL ordered by physical reads' отчетов Statspack
• статистика утилизации сети - определяется средствами ОС, может быть уменьшена выносом обработок на сервер и снижением передаваемых объёмов данных

Регулятор сбора статистики - STATISTICS_LEVEL (BASIC не собирает расширенной статистики, TYPICAL собирает (Buffer cache advisory, MTTR advisory, Shared Pool sizing advisory, Segment level statistics, PGA target advisory, Timed statistics) и ALL в дополнение собирает Timed operating system statistics и Row source execution statistics. Текущие настройки отражаются в V$STATISTICS_LEVEL, также при установке уровня BASIC необходимо явно задать TIMED_STATISTICS в TRUE, чтобы обеспечить сбор временных меток. Вообще явное указание DB_CACHE_ADVICE, TIMED_STATISTICS и TIMED_OS_STATISTICS переопределяет значения, наследуемые из STATISTIC_LEVEL

События ожидания представлены в V$SESSION_WAIT, V$SESSION_EVENT, and V$SYSTEM_EVENT, при TIMED_STATISTICS = TYPICAL или ALL там же отражаются и временные метки. Используются для реактивного тюнинга (по свершившемуся факту). Статистики отражены в большом количестве представлений, в т.ч. в V$SYSSTAT, V$SESSTAT, V$FILESTAT, V$ROLLSTAT, V$ENQUEUE_STAT, V$LATCH, V$SEGSTAT_NAME, V$SEGSTAT, V$SEGMENT_STATISTICS. Статистики могут использоваться для Ф-анализа и проактивного мониторинга

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

Первыми обычно анализируются события ожидания, но если есть ранее собранная статистика, сначала можно провести сравнение базовой и проблемной статистики. Статистиками профиля загрузки являются redo size, session logical reads, db block changes, physical reads, physical writes, parse count (total), parse count (hard), and user calls из представления V$SYSSTAT. Полезно нормализовать их по транзакции (изменения в приложении) и времени (изменения производительности), также можно воспользоваться уже готовым разделом Load Profile из отчетов Statspack

Когда Oracle ожидает чего либо, он фиксирует событие ожидания, и если это не событие простоя (idle), это есть непроизводительное ожидание. Наиболее эффективно отсортировать события по времени ожидания, для чего должна быть включена TIMED_STATISTICS. Иначе возможна только менее эффективная сортировка по количеству возникновения событий. Метод анализа событий ожидания - вытащить наиболее процентно жручие (по времени!) события из представлений или раздела 'Top 5 Wait Events' отчета StatsPack, определить количество ожиданий и среднее время ожидания, для лидеров проанализировать смежную статистику, на основании этих данных построить модель проблемы и пути решения

Существует отражающая проблемы производительности статистика, не координирующая с событиями ожидания:

• Redo Log Space Requests Statistic. Статистика V$SYSSTAT.'statistic redo log space requests' сервер ждал места не в кэше, а в оперативных журнала. Увеличение размера кэша здесь не поможет. Необходимо обратить внимание на тюнинг checkpoints, DBWR или процессы архивирования, на на LGWR
• Read Consistency. Целостные чтения, реализуемые обращением к сегментам отката, влекут за собой следующие возможные проблемы:
  - если на одной таблице есть много мелких модифицирующих транзакций и, одновременно, долго играющие модифицирующие запросы, может потребоваться частое проведение откатов для обеспечения ссылочной целостности. Для выявления проблемы нужно сверить статистики в V$SYSSTAT
  -- статистика consistent changes отражают количество проведенных в UNDO записей отмены, что может сильно отвлекать ресурсы
  -- статистика consistent gets отражает количество чтений, при которых пришлось выбирать данные из UNDO
  - если есть всего несколько больших сегментов отката, возможно узкое место - таблица транзакций. Решение здесь - много мелких сегментов отката или автоматическое управление пространством отмены
  - при недостаточности сегментов отмены отмечаются V$ROLLSTAT.WAITS / V$ROLLSTAT.GETS = малая величина И в V$WAITSTAT много ожиданий буферов с классом (CLASS) 'undo header'
• Chained rows (продленные строки) отражены статистикой 'table fetch continued row' в V$SYSSTAT. При изменении строки с увеличением данных такая строка мигрирует (migrate) в новый блок, если помещается целиком, если же нет - данные хранятся в нескольких блоках, а строка является сцепленной (chained). Мигрированные и сцепленные строки ухудшают производительность. Просмотреть сведения о них можно командой ANALYZE с модификатором LIST CHAINED ROWS, собирающей данные в отдельную таблицу (или используя скрипт поставщика UTLCHN1.SQL). Увеличение PCTFREE может помочь избежать миграции строк
• Parse-Related Statistics отражает утилизацию ресурсов за время парсинга, и может сигнализировать о большом использовании ресурсов на фазе парсинга, а не на фазе отработки запросов. Соответствующие статистики можно получить запросом:
  

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

Коэффициент "parse time CPU / parse time elapsed" отражает время отработки парсинга и должен стремиться к 1, что говорит об отсутствии ожиданий ресурсов (защелок и т.п.) в процессе парсинга. Коэффициент "parse time CPU / CPU used by this session" время, используемое для парсинга относительно общего времени сессии, искомое приближение к нулю говорит о том, что время на парсинг не тратится

События ожидания

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

buffer busy waits Это событие отражает конкуренцию за какой либо блок в одном из кэшей. Статистику по классам буферов (основные классы data block, segment header, undo header, and undo block) можно получить из V$WAITSTAT, а из V$SESSION_WAIT можно получить данные о файле (P1) и блоке в нем (параметр P2), по которым можно вычислить сегмент и его тип:

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;

Реакция на событие зависит от класса блоков

• заголовок сегмента (segment header) скорее всего связан с утилизацией FREELIST. По возможности необходимо перевести управление сегментами в автоматический режим, что исключит необходимость установки параметров PCTUSED, FREELISTS, and FREELIST GROUPS. Freelist - список свободных (свободного места больше PCTFREE) блоков в экстентах некоторого сегмента, определяется модификатором параметров хранения FREELISTS, по умолчанию один. Методом решения является увеличение количества freelists, а при недостаточности - введение групп списков (даже для одного экземпляра, в RAC у каждого экземпляра должна быть своя группа). Текущие настройки для сегмента можно увидеть запросом:

  SELECT SEGMENT_NAME, FREELISTS FROM DBA_SEGMENTS
         WHERE SEGMENT_NAME = segment name
               AND SEGMENT_TYPE = segment type;
  

• блок данных (data block) для таблицы или индекса необходимо исключить использование неселективных индексов, проверить нагруженные индексы (когда вставка инициируется многими процессами, например, последовательности), перейти на автоматическое управление сегментами или увеличить количество freelist
• заголовок отмены (undo header) - если не используется автоматическое управление пространством отмены, нужно увеличить количество сегментов отката
• блок отмены (undo block) - если не используется автоматическое управление пространством отмены, нужно увеличить размер сегментов отката

db file scattered read (рассеянное по памяти чтение) это событие подразумевает, что пользовательский процесс производит чтение в буффер, и в процессе ожидает I/O операций. Обычно это чтение в несвязанные разделы буфера, обычно это чтение multiboot, оно может подразумевать fast-scan операции, а не только полное сканирование таблицы. Параметрами V$SESSION_WAIT являются P1 - абсолютный номер файла, P2 - номер блока, P3 - количество блоков. На здоровой системе вполне ожидаемы после ожиданий простоя (idle), однако появление ожиданий прямого чтения (direct read подразумевает full scan в параллельным запросом) и db file scattered read говорят о недостаточности доступа по индексам. Дополнительные симптомы - низкий коэффициент попадания в кэш и большое время на события ожидания для пользователей с проблемным временем отклика. Это ситуация чрезмерного ввода / вывода, для ее разрешения необходимо снизить I/O тюнингом запросов, конфигурированием workarea, добавлением дисков или разнесением данных на разные диски

Для начала необходимо пробовать тюнить SQL, для чего нужно найти запросы как по событиям ожидания сессий, как и по размеру физического ввода/вывода в V$SQLAREA. Причинами могут быть плохо оптимизированный SQL, неиспользование индексов, высокопараллельный запрос или недостаточно аккуратная статистика оптимизатора. Также возможно некорректное распределение денных по дискам, что выявляется аналитикой нагрузки на диски. Ниже примеры вычисления SQL и сегментов, порождающих событие:

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 ;

db file sequential read (последовательное чтение) это событие подразумевает, что пользовательский процесс производит чтение в буффер, и в процессе ожидает I/O операций. От предыдущего отличается тем, что чтение в последовательную область и, обычно это одноблоковое чтение. Обычно это результат использования индексов, редко full scan усекается до одного блока. Параметрами события в V$SESSION_WAIT являются P1 - абсолютный номер файла, P2 - номер блока, P3 - количество блоков. На здоровой системе события вполне ожидаемы после ожиданий простоя (idle), однако появление db file sequential read свидетельствует о необходимости предпочтительного использования на больших warehouse операций полного сканирования таблиц в параллельном режиме

direct path read и direct path read (lob) эти события характеризуются прямым чтением пользовательским процессом данных в PGA, минуя SGA. Если система не поддерживает асинхронный ввод/ывод, каждое новое ожидание соответствует новому запросу ввода/вывода. Иначе при каждом обращении к блоку, еще не прочитанному в буфер, возникает запрос на чтение и обновляется статистика событий ожидания. Однако, в отличие от scattered и sequental read, количество событий ожидания может быть не равно количеству запросов на чтение. Параметры V$SESSION_WAIT являются P1 - абсолютный номер файла, P2 - номер блока, P3 - количество блоков. Причины - сортировки не помещаются в памяти и уходят на диск (потом забираются пользователем через direct read), для сканирования используются параллельные дочки, серверный процесс обрабатывает буфера быстрее, чем подсистема ввода вывода возвращает буфера (сигнал о перегрузке I/O подсистемы). Это довольно ожидаемо для warehouse, но для DSS требует расследования

Для дисковых сортировок можно вычислить породившие сортировку запросы в V$TEMPSEG_USAGE, и размер сортировки в V$SESSTAT, далее можно уменьшить сортировку тюнингов SQL. Если WORKAREA_SIZE_POLICY = MANUAL, можно вручную увеличить SORT_AREA_SIZE, для автоматического режима нужно увеличивать PGA_AGGREGATE_TARGET. Для table full scan возможны искажения оптимизатора при большом коэффициенте параллелизма таблицы, необходимо проверить, что чтение производится корректно (?). Для запросов с hash join чрезмерный ввод / вывод возможен при малом размере HASH_AREA_SIZE, который имеет смысл увеличить (для автоматического режима нужно увеличивать PGA_AGGREGATE_TARGET

direct path write отражает ожидания при прямой записи из PGA минуя DBWR (запись сортировок на диск, параллельные DML операции, прямые (direct-path) вставки и параллельные create table as select, а также некоторые LOB операции. Как и для direct path read при работе ФС в асинхронном режиме количество ожиданий не равняется количеству запросов на запись. Сессии ждут, когда все буфера PGA обработаны и нельзя продолжать выполнение до завершения поераций ввода / вывода. Параметры V$SESSION_WAIT - P1 (файл для записи), P2 (стартовый блок для записи), P3 (количество записываемых блоков). Причины возникновения - сортировки не помещаются в память или для создания и наполнения объектов используются параллельные операции. Для больших сотрировок можно вычислить породившие сортировку запросы в V$TEMPSEG_USAGE, и размер сортировки в V$SESSTAT, далее можно уменьшить сортировку тюнингов SQL. Если WORKAREA_SIZE_POLICY = MANUAL, можно вручную увеличить SORT_AREA_SIZE, для автоматического режима нужно увеличивать PGA_AGGREGATE_TARGET. Для параллельных DML операций нужно проверить распределение данных по дискам и выверить разметку (sizing) ФС на соответствие коэффициенту паралеллизма

enqueue очереди блокируют серийный доступ к ресурсам БД. Это событие показывает, что сессия ожидает снятия блокировки, подвешенной другой сессией. Параметрами V$SESSION_WAIT являются P1 (тип блокировки, то же в "V$LOCK.TYPE = chr(bitand(P1,-16777216) / 16777215) || chr(bitand(P1,16711680) / 65535))", для запроса блокировки вычисляется как request = mod(P1, 65536)), P2 (то же, что и V$LOCK.ID1), P3 (V$LOCK.ID2). Сессии, держащие (hold) блокировки, можно получить из представления V$LOCK, как и ожидающие (для этих поле REQUEST != 0)

# отразить ждущие освобождения блокировки
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;

реакция зависит от типа блокировки

• ST enqueue - блокировка возникает только для управляемых словарем бабличних пространств при задержке автоматического выделения места. Для временных табличных пространств нужно убедиться в использовании TEMPFILES. Для динамически растущих сегментов оптимально перейти на LMT, при невозможности - увеличит размер next_extent для избегания постоянного выделения места, или же предварительно выделить место для сегмента командой ALTER TABLE ALLOCATE EXTENT
• HW enqueue - блокировка гарантирует последовательное выделение места за пределами HIGH WATER MARK сегмента. Параметры позволяют вычислить табличное пространство (V$SESSION_WAIT.P2 / V$LOCK.ID1) и заголовок увеличиваемого сегмента (V$SESSION_WAIT.P2 / V$LOCK.ID2). Решением является ручное довыделение места
• TM enqueue - эта стек DML блокировок, чаще всего событие ожидания возникает при отсутствии издекса для поля внешнего ключа, и создание индекса позволяет избегать такого события ожидания
• TX enqueue - эксклюзивная блокировка с начала первого изменения транзакции до операций COMMIT или ROLLBACK. Mode 6 возникает при ожидании строки, занятой другой сессией и разрешается командами COMMIT/ROLLBACK в удерживаеющей сессии. Mode 4 есть ьожидание ITL block, возникает при попытке заблокировать строку в блоке, в котором находятся другие заблокированные строки и свободных транзакционных слотов нет, решается увеличением количества транзакционных слотов (ITL, параметры INITTRANS или MAXTRANS для таблицы). Также возникает при ожидании сессией возможных задваиваний значения в уникальном индексе, когда при попытке записи одного значения двумя сессиями вторая ждет, возбуждать или не ORA-0001, решается COMMIT/ROLLBACK первой сессии. Также возникает пр ожидании фрагмента bitmap индекса, когда несколько сессий меняют строки, покрываемые одним фрагментом bitmap индекса, решение то же - COMMIT/ROLLBACK в первой сессии. Также возникает при ожидании PREPARED транзакции

free buffer waits возникает, когда серверный процесс не может найти свободный буфер и запрашивает сброс процессом грязных (в которых были изменены данные) буферов на диск. Причины - медленный I/O, ожидание ресурсов (например, защелки), слишком маленький буфер DBRW не успевает обработать или же слишком большой - один DBRW не справляется. При частом возникновении нужно отследить события ожидание, связанные с DBRW. Если это записи, смотреть в V$FILESTAT и статистику ОС на предмет допустимости нагрузки и последующая оптимизация или расширение подсистемы ввода / вывода, если малый кеш - коэффициент попадания будет низкий, а новый оптимальный размер можно подсмотреть в V$DB_CACHE_ADVICE. Если размер кеша адекватен, но проблема остается - можно перевести DBRW в асинхронный режим или активировать несколько DBRW параметром DB_WRITER_PROCESSES (количество сканеров LRU списка кеша, опция предпочтительна на SMP системах с асинхронным I/O) или параметром DBWR_IO_SLAVES (непосредственно дочки, осуществляющие ввод / вывод, допустимо при одном CPU или для эмуляции асинхронного I/O, который предпочтителен). При отсутствии поддержки асинхронного I/O его необходимо отключить в движке DISK_ASYNCH_IO = false. Также нужно использовать только один из параметров DB_WRITER_PROCESSES и DBWR_IO_SLAVES

latch free являются низкоуровневыми внутренними блокировками для защиты структур памяти. Счетчик события обновляется, когда при попытке получения защелки (latch) она не отдаёся с первого раза. Обращать внимание стоит, если время ожидания по событию составляет большую часть общего времени ожидания для системы или пользователя. Для определения причины нужно идентифицировать защёлку (их много видов под разные задачи), анализировать смежные ресурсы (например для нагрузки на защёлки библиотечного кэша полезно проанализировать показатели полных и неполных запросов), проанализировать запросы проблемных сессий. Параметры V$SESSION_WAIT - P1 (адрес защёлки), P2 (номер защелки), P3 (время ожидания процессом защёлки)

# текущие ожидания защёлок
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 ;

Обычно проблемы с защелками shared pool или библиотечного кэша связаны с парсингом

# сравнить вручную
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 ;

cache buffer lru chain защищает списки буферов в кэше и устанавливается при каждом добавление, удалении или перемещении кэшей _в списке_. Для SMP систем Oracle автоматически устанавливает количество защелок в половину числа процессоров, для однопроцессорной системы достаточно одной защелки. Подвешивание (включение) защёлок влияет на производительность, отслеживается по V$LATCH, V$SESSION_EVENT и V$SYSTEM_EVENT, избегать можно тюнингом или редизайном приложения и неиспользованием кэша буферов заданиями DSS систем

cache buffers chains защищает буферный список и используется при поиске, размещении или удалении буфера _в кэше_. Установка этой защелки обычно подразумевает наличие "горячих блоков". Для выявления сильно нагруженной буферной цепочки и блока нужно проанализировать статистику защёлок этих в V$LATCH_CHILDREN. Если значения GETS, MISSES и SLEEPS больше, чем по другим таким же защёлкам, то это искомая защелка с адресом, по которому можно вычислить горячий блок:

SELECT file#, dbablk, class, state, TCH
       FROM X$BH
       WHERE HLADDR = 'V$LATCH_CHILDREN.ADDR' ;

Поле X$BH.TCH является счетчиком обращений к буферу, и большая величина указывает на потенциальный "горячий блок". Одна защелка защищает много блоков и нужно провести множественные наблюдения для вычисления "горячего блока", и, далее вычислить сегмент из DBA_EXTENTS

log buffer space событие подразумевает ожидание серверным процессом свободного места в журнальном буфере, по причине наполнения буфера настолько быстрого, что LGWR не успевает записывать данные в redo log. Если подсистема воода / вывода для оперативных журналов адекватна, то необходимо увеличить размер журнального буффера до момента, пока событие станет редким или перестанет появляться, до этого выверять и тюнить подсистему I/O

log file switch это группа из двух событий - log file switch (archiving needed) и log file switch (checkpoint incomplete) - подразумевает, что LGRW не может переключиться на следующий журнал, и все COMMIT запросы ожидают этого переключения. При возникновении события log file switch (archiving needed) необходимо выявить причину, по которой архивер не может записать архивную информацию в разумные сроки. Причины могут быть - нехватка места для архивного журнала, архивер не может читать из оперативного журнала достаточно быстро (связано с LGWR), архивер не может отписывать достаточно быстро (в этом случае нужно проверить пропускную способность целевого хранилища, потом - его заполненность, потом - увеличивать количество процессов архивирования ARCHn. Для события log file switch (checkpoint incomplete) необходимо выявить причины, по которым DBWR отрабатывает медленно - это или медленное хранилище, или неоптимальное распределение данных в хранилище. Если не DBWR - причиной может быть малое количество журнальных групп или малый размер оперативных журналов, когда цикл переключения оперативных журналов проходится быстрее, чем DBWR отрабатывает запись данных по контрольной точке - в этом случае нужно учеличить количество журнальных групп и/или размер групп соответственно

log file sync при запросе операций COMMIT или ROLLBACK окончание такой операции требует сброса соответствующей порции данных в оперативные журналы процессом LGWR, и ожидание такого сброса отражает событие ожидания log file sync. Если среднее время ожидания мало, а количество ожиданий велико, вероятно использование COMMIT после каждой операции (например после каждого INSERT), тогда полезно перейти на пакетный COMMIT (после нескольких операций, а не после одной). Если же среднее время ожидания велико, наиболее вероятен медленный ввод/вывод, что требует перераспределения данных (например, вынос оперативных журналов на отдельные шпинделя) или аппаратная модернизация подсистемы ввода/вывода

rdbms ipc reply это событие отражает ожидание ответа от одного из фоновых процессов

idle wait events события простоя фиксируют отсутствие заданий для обработки СУБД. В случае наличия узких мест такие события говорят о том, что узкие места - не в СУБД. К событиям простоя относятся: 'smon timer', 'pmon timer', 'rdbms ipc message', 'Null event', 'parallel query dequeue', 'pipe get', 'client message', 'SQL*Net message to client', 'SQL*Net message from client', 'SQL*Net more data from client', 'dispatcher timer', 'virtual circuit status', 'lock manager wait for remote message', 'PX Idle Wait', 'PX Deq: Execution Msg', 'PX Deq: Table Q Normal', 'wakeup time manager', 'slave wait', 'i/o slave wait', 'jobq slave wait', 'null event', 'gcs remote message', 'gcs for action', 'ges remote message', 'queue messages'. Полный список доступен в таблице STATS$IDLE_EVENT