Oracle Некоторые вопросы оптимизации

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

select 1- (sum(decode(name, 'physical reads',value,0))/
(sum(decode(name, 'db block gets',value,0)) +
(sum(decode(name, 'consistent gets',value,0)))))
"Read Hit Ratio"
from v$sysstat;


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


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


Процент попадания в словарный кэш
Если меньше 95, то стоит задуматься над увеличением SHARED_POOL_SIZE


select sum(gets), sum(getmisses),
(1-(sum(getmisses)/(sum(gets)+ sum(getmisses))))* 100 HitRate
from v$rowcache;


Процент попаданий для кэша разделяемых SQL-запросов
и процедур на языке PL/SQL
процент попаданий при выполнении PinHitRatio должен быть не менее 95
процент попаданий при загрузке RelHitRatio должен быть не менее 99


select sum(pins) "Executions",
sum(pinhits) "Hits",
((sum(pinhits)/sum(pins))* 100) "PinHitRatio",
sum(reloads) "Misses",
((sum(pins)/(sum(pins) + sum(reloads)))* 100) "RelHitRatio"
from v$librarycache


Объекты PL/SQL, которые следует закрепить в памяти
(объекты, для хранения которых нужно больше 100 Кбайт)

select name, sharable_mem
from v$db_object_cache
where sharable_mem>100000
and type in ('PACKAGE','PACKAGE_BODY','FUNCTION','PROCEDURE')
and kept='NO';


Сессии, наиболее интенсивно использующие процессорное время

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

- При помощи команды top операционной системы UNIX.

- С использованием динамической статистики Oracle,
выполнив следующий запрос:

select v.sid, s.name "statistic", v.value
from v$statname s, v$sesstat v
where s.name = 'CPU used by this session'
and v.statistic# = s.statistic#
and v.value > 0
order by 3 desc;

Следует учитывать, что в данном запросе интересует не большое
значение статистики само по себе, а скорость ее роста в данный
момент времени. Связано это с тем, что сессия, функционирующая в
течение длительного периода времени, при незначительном приросте,
могла в сумме использовать большое количество процессорного времени.

Повторный разбор SQL-предложений

select name, value from v$sysstat
where name in (
'parse time cpu',
'parse time elapsed',
'parse count (hard)'
);

SQL-предложения, подвергающиеся наиболее частым разборам

select sql_text, parse_calls, executions
from v$sqlarea
order by parse_calls desc;

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

select sql_text, version_count
from v$sqlarea order by version_count desc;

SQL-предложения, наиболее интенсивно выполняющие обращения к блокам
данных:

select address, hash_value, buffer_gets, executions,
buffer_gets/executions "gets/exec", sql_text
from v$sqlarea
where executions > 0
order by 3 desc;

(следует обращать внимание на SQL-предложения с большим
отношением gets/exec или значением buffer_gets)

Интенсивные согласованные чтения

Система может тратить большое количество ресурсов на формирование
согласованного представления информации. Наиболее часто это происходит
в следующих ситуациях:

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

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

- Система сканирует слишком много буферов для того чтобы найти свободный.
Необходимо увеличить интенсивность скидывания буферов на диск процессом
DBWRn. Кроме этого можно увеличить размер кэша буферов для уменьшения
нагрузки для DBWR. Для нахождения среднего количества буферов, которое
необходимо просмотреть в списке LRU (Least Reasently Used) для нахождения
свободного буфера, необходимо использовать сле-дующий запрос:

select 1+sum(decode(name, 'free buffer inspected', value, 0)) /
sum(decode(name, 'free buffer requested', value, 0))
from v$sysstat
where name in (
'free buffer inspected',
'free buffer requested'
);

Результат должен быть равен в среднем 1-му или 2-м блокам.
Если количество блоков больше, то необходимо увеличить кэш буферов
или настроить процессы DBWRn.
Аналогично следует поступать, если велико среднее количество "грязных"
буферов в конце списка LRU:

select * from v$buffer_pool_statistics;


Ожидания выполнения

Для определения наиболее частых причин ожидания необходимо выполнить
следующий запрос:

select * from v$system_event
where event != 'Null event' and
event != 'rdbms ipc message' and
event != 'pipe get' and
event != 'virtual circuit status' and
event not like '%timer%' and
event not like 'SQL*Net % from %'
order by time_waited desc;

Обращать внимание следует на события с наибольшими временами ожидания.

Наиболее часто встречающиеся причины ожиданий:

- Buffer busy wait - данное событие обычно возникает, если несколько
сессий пытаются прочитать один и тот же блок, или одна или несколько
сессий ожидают окончания изменения одного блока. Конкуренция за блок
корректируется в зависимости от типа блока:

Блок данных:

- Уменьшите количество строк в блоке путем изменения параметров
pctfree/pctused или уменьшением BD_BLOCK_SIZE.

- Проверьте на наличие right.hand.indexes (индексов, в которые добавляются
данные многими процессами в одну точку). Возможно, следует использовать
индексы с обратными ключами.

Заголовок сегмента:

- Увеличьте количество freelists.

- Увеличьте размер экстентов для таблицы.

Заголовок сегмента отката:

- Добавьте больше сегментов отката для уменьшения количества транзакций
на сегмент.

- Уменьшите значение параметра TRANSACTION_PER_ROLLBACK_SEGMENT.

Блок сегмента отката:

- Увеличьте сегмент отката.

- Free buffer wait - обычно возникает, если процесс DBWR не справляется
с записью блоков на диск. Необходимо увеличить его пропускную способность.

- Latch free - конкуренция за доступ к защелкам. При помощи следующего
запроса можно определить защелки, которые ожидают активные сессии в
данный момент времени:

select * from v$latch
where latch# in (
select p2 from v$session_wait
where event = 'latch free' );

Конкуренция за доступ к защелкам

Одной из причин простоя процессов может быть конкуренция за доступ
к защелкам. Защелка - это внутренняя структура данных Oracle,
контролирующая доступ к объектам, находящимся в SGA (System Global Area).

О возникновении конкуренции за доступ к защелкам сигнализирует появление
сессий с ожиданием события "latch free" в динамическом представлении
V$SESSION_WAIT и соответственно рост статистики ожидания "latch free"
в V$SESSION_EVENT.

- Статистика по ожиданиям защелок в системе:

select * from v$system_event where event = 'latch free';

- Текущие ожидания защелок:

select * from v$session_wait where event = 'latch free';


- Защелки, доступ к которым ожидают процессы в текущий момент времени:

select * from v$latch
where latch# in (
select p2 from v$session_wait where event = 'latch free' );

Выявить возникновение конкуренции за доступ к защелкам в системе поможет
скрипт response_time_breakdown.sql.

Наиболее часто встречающиеся причины ожиданий:

- Сache buffers chains - данная защелка запрашивается при поиске блока
данных, кэшированного в SGA. Поскольку буферный кэш представляет собой
последовательность блоков, каждая последовательность защищается защелкой,
которая является дочерней для данной защелки. Конкуренция за доступ к
данной защелке вызывается очень активным доступом к одному блоку, и обычно
требует для исправления переписывания приложения. Определить блоки данных
в кэше буферов, при обращении к которым возникают задержки, поможет
следующий запрос:

select a.name, b.obj,
b.dbarfil file#, b.dbablk block#,
a.gets, a.sleeps
from v$latch_children a, x$bh b
where a.addr = b.hladdr and sleeps > 100
order by 5 desc;

select /*+ ordered first_rows*/
l.child#,
e.owner||'.'||e.segment_name segment,
e.segment_type type,
x.dbarfil file#,
x.dbablk block#,
trunc(l.sleeps/l.gets,5) rate
from
( select
(max(sleeps/gets)+avg(sleeps/gets))/2 rate,
sum(sleeps) sleeps
from
sys.v_$latch_children
where
name = 'cache buffers chains'
) a,
sys.x$bh x,
sys.v_$latch_children l,
sys.dba_extents e
where
x.inst_id = userenv('Instance') and
l.sleeps/l.gets > a.rate and
l.addr = x.hladdr and
x.dbarfil = e.file_id and
x.dbablk between e.block_id and e.block_id+e.blocks
order by rate desc;

Cущественное уменьшение количества ожиданий защелки данного типа
можно выставлением скрытого параметра базы данных

_db_block_hash_buckets = 2*db_block_buffers;


Недостаточность ресурсов памяти

Разделяемый буфер (shared pool)

- Коэффициент попадания в библиотечный кэш, должен быть близок к 1:

select (sum(pins - reloads)) / sum(pins) "lib cache"
from v$librarycache;

- Коэффициент попадания в словарный кэш (содержащий библиотечные данные),
коэффициент должен быть близок к 1:

select (sum(gets - getmisses - usage - fixed)) / sum(gets) "row cache"
from v$rowcache;

- Свободное место в разделяемом кэше:

select * from v$sgastat where name = 'free memory';

- Коэффициент не попадания в библиотечный кэш:

select sum(pins) "executions",
sum(reloads) "cache misses while executing",
sum(reloads)/sum(pins) "miss rate"
from v$librarycache;


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

- Коэффициент попаданий в буфер должен быть больше 0.9:

select name, 1-physical_reads/(db_block_gets+consistent_gets) "Hits"
from v$buffer_pool_statistics;

- Коэффициент ожидания LRU защелок при обращении к кэшу буферов:

select bp.name, max(sleeps / gets) ratio
from v$buffer_pool bp, v$latch_children lc
where lc.name = 'cache buffers lru chain'
and lc.child# between bp.lo_setid and bp.hi_setid
group by bp.name;

Кэш журналов регистраций (redo log buffer)
Количество промахов при обращении к кэшу журналов регистрации:

select name, value
from v$sysstat
where name = 'redo buffer allocation retries';
Области сортировки (sort areas)
Количество сортировок на диске и в памяти:
select name, value
from v$sysstat
where name in ('sorts (memory)', 'sorts (disk)');

Конкуренция за доступ к ресурсам

Конкуренция за сегменты отката

Количество ожиданий доступа к сегментам отката не должно превышать 1%.
Если коэффициент ожиданий выше, то необходимо увеличить количество
сегментов отката:

select w.class, w.count/s.value "Rate"
from v$waitstat w,
( select sum(value) value from v$sysstat
where name in ('db block gets', 'consistent gets')) s
where w.class in (
'system undo header',
'system undo block',
'undo header',
'undo block');


Какие таблицы и индексы стали причиной высокого ожидания события  buffer busy waits

select
OWNER ,
OBJECT_NAME ,
OBJECT_TYPE ,
TABLESPACE_NAME
from v$segment_statistics
where statistic_name = 'buffer busy waits'
order by value desc

Долго выполняющиеся транзакции:

select hash_value, executions,
round(elapsed_time/1000000,2) total_time,
round(cpu_time/1000000,2) cpu_seconds
from (select * from v$sql order by elapsed_time desc)


Далее по hash_value находим план

select * from v$sql_plan
where hash_value = 3287028449


Просмотр SQL - кода сеанса

select a.sid, a.username, s.sql_text
from v$session a, v$sqltext s
where a.sql_address = s.address
and a.sql_hash_value = s.hash_value
and a.username = 'ANGOR'
and a.status = 'ACTIVE'
order by a.username, a.sid, s.piece;

Monitor long running operations using v$session_longops

SELECT s.sid,
       s.serial#,
       s.machine,
       s.machine,
       s.sql_id,
       s.event,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#
ORDER BY elapsed desc;




Как посмотреть план запроса:

Запросы потребляющие ресурсы CPU:

select substr(sql_text,1,40) sql_text, hash_value, cpu_time
from v$sql
where cpu_time > 10000000
order by cpu_time;


Или любой другой запрос:

SELECT hash_value, address, child_number, sql_id, plan_hash_value
FROM v$sql
WHERE  sql_text LIKE '%select sysdate from dual%'
AND sql_text NOT LIKE '%v_sql%';

По hash_value:

SELECT
   lpad(' ', 2 * level) || pt.operation || ' ' || pt.options || ' ' || pt.object_name "Plan Access Path",
   pt.cost,
   pt.cardinality,
   pt.bytes,
   pt.cpu_cost,
   pt.io_cost,
   pt.temp_space,
   pt.access_predicates,
   pt.filter_predicates,
   pt.qblock_name as "Query Block name"
FROM (select *
             from v$sql_plan
             where HASH_VALUE = 2343063137
             and ADDRESS =  '00007FF7C88C4128'
             and  CHILD_NUMBER = 0
           ) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0;

Или по sql_id:

SELECT
   lpad(' ', 2 * level) || pt.operation || ' ' || pt.options || ' ' || pt.object_name "Plan Access Path",
   pt.cost,
   pt.cardinality,
   pt.bytes,
   pt.cpu_cost,
   pt.io_cost,
   pt.temp_space,
   pt.access_predicates,
   pt.filter_predicates,
   pt.qblock_name as "Query Block name"
FROM (select *
             from v$sql_plan
             where   sql_id =  '7h35uxf5uhmm1'
             and child_number = 0
            ) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0;


Из AWR по sql_id и plan_hash_value:

SELECT lpad(' ', 2 * level) || pt.operation || ' ' || pt.options || ' ' ||
       pt.object_name "Query Plan",
       pt.cost,
       pt.cardinality,
       pt.bytes,
       pt.cpu_cost,
       pt.io_cost,
       pt.temp_space,
       pt.access_predicates,
       pt.filter_predicates,
       pt.qblock_name
 FROM (select *
              from dba_hist_sql_plan
              where sql_id = '7h35uxf5uhmm1'
              and plan_hash_value = 1388734953
        ) pt
 CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0



В 11g из v$sql_plan_monitor :

 SELECT lpad(' ', 2 * level) || pt.plan_operation || ' ' || pt.plan_options || ' ' ||
       pt.plan_object_name "Query Plan",
       pt.plan_cost,
       pt.plan_cardinality,
       pt.plan_bytes,
       pt.plan_cpu_cost,
       pt.plan_io_cost,
       pt.plan_temp_space,
       pt.starts,
       pt.output_rows,
       pt.workarea_mem,
       pt.workarea_tempseg,
       pt.physical_read_requests,
       pt.physical_write_requests
  FROM (select *
          from v$sql_plan_monitor pt
         where sql_id = '7h35uxf5uhmm1'
  --                and sql_plan_hash_value = :sql_plan_hash_value
  --                and sid = :sid
  --                and sql_exec_id = :sql_exec_id
           and status = 'EXECUTING') pt
CONNECT BY PRIOR pt.plan_line_id = pt.plan_parent_id
 START WITH pt.plan_line_id = 0


 Также можно получить отчет:


SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL /temp/report_sql_monitor.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       =>  '7h35uxf5uhmm1',
  type         => 'HTML',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF