Informacion sobre el Buffer Cache
PROMPT
PROMPT ******************************************** BUFFER CACHE **********************************************************
PROMPT
PROMPT
PROMPT ******************************************** PARAMETROS CONFIGURADOS
PROMPT
SELECT inst_id, name parametro, DECODE(type,1,’boolean’,2,’string’,3,’integer’) type, value valor, description
FROM gv$parameter2
WHERE (name IN (’db_block_buffers’,'db_block_size’,'db_cache_advice’,'db_writer_processes’,'cpu_count’,'dbwr_io_slaves’) OR
name LIKE ‘db%cache_size’ OR name like ‘db_block_chek%’) AND
value <> ‘0′
ORDER BY 2,1;
PROMPT
PROMPT ******************************************** INDICADORES DE RENDIMIENTO DEL DATA BUFFER CACHE
PROMPT
PROMPT ******************************************** BUFFER CACHE HIT RATIO > 90%
COLUMN “BUFFER CACHE HIT RATIO %” FORMAT 999.99
SELECT logical.inst_id, logical.value “SESSION LOGICAL READS”, direct.value “PHYSICAL READS DIRECT”, lob.value “PHYSICAL READS DIRECT (LOB)”, physical.value “PHYSICAL READS”,
100*(1-(physical.value-lob.value-direct.value)/logical.value) “BUFFER CACHE HIT RATIO %”
FROM gv$sysstat logical, gv$sysstat physical, gv$sysstat direct, gv$sysstat lob
WHERE logical.name = ’session logical reads’ AND
physical.name = ‘physical reads’ AND
direct.name = ‘physical reads direct’ AND
lob.name = ‘physical reads direct (lob)’ AND
logical.inst_id=physical.inst_id AND
physical.inst_id=direct.inst_id AND
direct.inst_id=lob.inst_id;
PROMPT
PROMPT ******************************************** OTRAS ESTADISTICAS
PROMPT
PROMPT ******************************************** DB CACHE ADVICE
SELECT inst_id, id, name buffer, block_size, advice_status, size_for_estimate, size_factor, estd_physical_read_factor,
estd_physical_reads
FROM gv$db_cache_advice
ORDER BY 1,7;
PROMPT
PROMPT ******************************************** BUFFER INSPECCIONADOS
SELECT inst_id, name, value
FROM gv$sysstat
WHERE name = ‘free buffer inspected’
ORDER BY 1;
PROMPT
PROMPT ******************************************** ESPERAS POR ESPACIO EN EL BUFFER CACHE –> 0
SELECT inst_id, event evento, total_waits esperas, total_timeouts timeouts, average_wait promedio
FROM gv$system_event
WHERE event = ‘free buffer waits’
ORDER BY 1;
PROMPT
PROMPT ******************************************** ESPERAS POR BLOQUES EN EL BUFFER CACHE
SELECT inst_id, event evento, total_waits esperas, total_timeouts timeouts, average_wait promedio
FROM gv$system_event
WHERE event = ‘buffer busy waits’
ORDER BY 1;
COLUMN statistic_name FORMAT a17
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL of VALUE ON REPORT
PROMPT
PROMPT ******************************************** TOP 10 OBJETOS CON ESPERAS
SELECT objeto, subobject_name, statistic_name, value, tablespace, tipo
FROM (SELECT owner||’.'||object_name objeto, subobject_name, statistic_name, SUM(value) value, tablespace_name tablespace, object_type tipo
FROM gv$segment_statistics
WHERE statistic_name = ‘buffer busy waits’
GROUP BY owner||’.'||object_name, subobject_name, statistic_name, tablespace_name, object_type
HAVING SUM(value) > 0
ORDER BY 4 desc)
WHERE rownum < 11;
CLEAR BREAKS
CLEAR COMPUTES
PROMPT
PROMPT ******************************************** CONFIGURACION DE MULTIPLES BUFFERS
SELECT inst_id, id, name buffer, block_size, resize_state, prev_size, current_size, target_size
FROM gv$buffer_pool
ORDER BY 1,2;
PROMPT
PROMPT ******************************************** SEGMENTOS EN OTROS BUFFERS
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL of KB ON REPORT
SELECT segment_type tipo, owner||’.'||segment_name segmento, partition_name, tablespace_name tablespace, buffer_pool buffer, blocks, ROUND(bytes/1024) kb, freelist_groups, freelists,
extents, initial_extent/1024 iextent, next_extent/1024 nextent, pct_increase
FROM dba_segments
WHERE buffer_pool <> ‘DEFAULT’
ORDER BY 1,2,3;
CLEAR BREAKS
CLEAR COMPUTES
PROMPT
PROMPT ******************************************** INDICADORES DE RENDIMIENTO DE BUFFERS MULTIPLES
SELECT inst_id, name buffer, block_size, db_block_gets + consistent_gets session_logical_reads, physical_reads,
100*(1 - (physical_reads/(db_block_gets + consistent_gets))) “BUFFER CACHE HIT RATIO %”, buffer_busy_wait
FROM gv$buffer_pool_statistics
WHERE db_block_gets + consistent_gets > 0
ORDER BY 1,2;
PROMPT
PROMPT ******************************************** CONTENCION
SELECT inst_id, name parametro, DECODE(type,1,’boolean’,2,’string’,3,’integer’) type, value valor, description
FROM gv$parameter2
WHERE name = ‘timed_statistics’
ORDER BY 1;
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL of COUNT ON REPORT
SELECT inst_id, class, count esperas, time
FROM gv$waitstat
WHERE count > 0
ORDER BY 2,1;
CLEAR BREAKS
CLEAR COMPUTES
COLUMN statistic_name CLEAR