11. April 2008 by fmunoz.

PROMPT
PROMPT ******************************************** LIBRARY CACHE ***********************************************************
PROMPT

COLUMN y new_value sid NOPRINT
SELECT name||’_'||TO_CHAR(sysdate, ‘ddmonyy_hh24miss’) y FROM v$database;

SPOOL library_cache_&sid..txt

BREAK ON HASH_VALUE

SELECT hash_value, sql_text
FROM gv$sqltext
WHERE command_type = 3
ORDER BY 1, piece;

CLEAR BREAKS

SPOOL OFF

Posted in Memoria, Scripts | Print | No Comments »

11. April 2008 by fmunoz.

PROMPT
PROMPT ******************************************** LATCH ***************************************************************************************************
PROMPT

PROMPT ******************************************** ESPERAS POR LATCH

SELECT inst_id, event evento, total_waits esperas, total_timeouts timeouts, average_wait promedio
FROM gv$system_event
WHERE event = ‘latch free’
ORDER BY 1;

PROMPT
PROMPT ******************************************** CONTENCION POR LATCH (HIT % > 99%)

COLUMN “HIT %” FORMAT 999.99
COLUMN “IHIT %” FORMAT 999.99

SELECT inst_id, name latch, gets, misses, 100*(1-misses/DECODE(gets,0,1,gets))  “HIT %”,
       immediate_gets igets, immediate_misses imisses,
       100*(1-immediate_misses/DECODE(immediate_gets + immediate_misses,0,1,immediate_gets + immediate_misses)) “IHIT %”
FROM gv$latch
WHERE gets + immediate_gets > 0 AND
      (1-sleeps/DECODE(gets,0,1,gets) < 0.990 OR 1-immediate_misses/DECODE(immediate_gets + immediate_misses,0,1,immediate_gets + immediate_misses) < 0.990)
ORDER BY 2,1;

COLUMN “HIT %” CLEAR
COLUMN “IHIT %” CLEAR

PROMPT
PROMPT ******************************************** SESIONES OCUPANDO LATCH

SELECT s.inst_id, s.username usuario, s.osuser, l.name latch, l.laddr laddress, s.machine, s.program, s.status, s.sid, s.serial#, type, module, a.name accion
FROM gv$session s, gv$latchholder l, audit_actions a
WHERE s.inst_id = l.inst_id AND
      s.sid = l.sid AND
      a.action = command;

PROMPT
PROMPT ******************************************** SQL

BREAK ON hash_value

SELECT hash_value, sql_text
FROM gv$sqltext_with_newlines
WHERE (address, hash_value) IN (SELECT sql_address, sql_hash_value
                                FROM gv$session s, gv$latchholder l
                                WHERE s.inst_id = l.inst_id AND s.sid = l.sid)
ORDER BY 1, piece;

PROMPT
PROMPT ******************************************** BLOQUES CUBIERTOS

SELECT dbarfil, dbablk, class, state
FROM X$BH
WHERE HLADDR IN (SELECT laddr FROM gv$latchholder);

PROMPT
PROMPT ******************************************** SESIONES ESPERANDO LATCH
SELECT inst_id, sid, event EVENTO, wait_time, seconds_in_wait, state, name latch, p1 address
FROM gv$session_wait s, v$latchname l
WHERE event LIKE ‘latch%’ AND
      l.latch#=s.p2;

Posted in Memoria, Scripts | Print | 1 Comment »

11. April 2008 by fmunoz.

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

Posted in Memoria, Scripts | Print | 1 Comment »

11. April 2008 by fmunoz.

PROMPT
PROMPT ******************************************** CONTENIDO BUFFER CACHE ************************************************
PROMPT

SELECT inst_id, owner||’.'||object_name objeto, object_type tipo, COUNT(*) bloques
FROM gv$bh b, dba_objects o
WHERE objd = object_id
GROUP BY inst_id, owner||’.'||object_name, object_type
HAVING COUNT(*) > 100
ORDER BY 4 DESC;

Posted in Memoria, Scripts | Print | 1 Comment »