Monitoreo de RAC

Estimados,

Aqui comparto con uds un script para monitorear Oracle en RAC.

set pagesize 1000
set linesize 160
column parameter format a30
column type format a10
column value format a15
column description format a55
column server format a20
column db_status format a9
column id format 99
column instance format a12

PROMPT
PROMPT ******************************************** RAC ********************************************************************
PROMPT

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

SPOOL rac_&sid..txt

SELECT TO_CHAR(sysdate, ‘dd-mm-yy hh24:mi:ss’) inicio
FROM dual;

PROMPT ******************************************** PARAMETERS
PROMPT

SELECT inst_id, name parameter, DECODE(type,1,’boolean’,2,’string’,3,’integer’) type, value, description
FROM gv$parameter2
WHERE name LIKE ‘parallel%’ OR
name LIKE ‘cluster%’ OR
name LIKE ‘%listener’ OR
name LIKE ‘%parallelism’ OR
name IN (’active_instance_count’,'instance_groups’,'db_name’,'instance_name’,’service_names’)
ORDER BY 2,1;

PROMPT ******************************************** INSTANCES ON RAC
PROMPT

SELECT inst_id id, instance_name name, host_name server, thread#,instance_role, startup_time stime,
status, archiver, log_switch_wait log_wait, logins, shutdown_pending, database_status db_status, active_state
FROM gv$instance
ORDER BY 1;

PROMPT ******************************************** THREADS
PROMPT

SET NUMWIDTH 15

SELECT thread#, status, enabled, groups, instance, current_group#, sequence#, enable_time, disable_time, checkpoint_change#
FROM v$thread;

SET NUMWIDTH 10

PROMPT ******************************************** RAC WAIT EVENTS
PROMPT

SELECT inst_id, event evento, total_waits esperas, total_timeouts timeouts, average_wait promedio
FROM gv$system_event
WHERE event LIKE ‘global cache%’ OR
event LIKE ‘%busy%’ OR
event IN (’library cache pin’,'DFS lock handle’)
ORDER BY 2;

PROMPT ******************************************** GLOBAL CACHE SERVICE
PROMPT

PROMPT ******************************************** GLOBAL CACHE STATISTICS
PROMPT

SELECT inst_id, name, value
FROM gv$sysstat
WHERE name LIKE ‘global cache%’ AND
value > 0
ORDER BY name, inst_id;

PROMPT ******************************************** GLOBAL CACHE SERVICE REQUEST LATENCY (CONSISTENT READ BLOCK) < 20 MS
PROMPT

SELECT received.inst_id, received.value “cr blocks received”, receive_time.value “cr block receive time”, 10*receive_time.value/received.value “LATENCY MS”
FROM gv$sysstat received, gv$sysstat receive_time
WHERE received.name = ‘global cache cr blocks received’ AND
receive_time.name = ‘global cache cr block receive time’ AND
received.inst_id = receive_time.inst_id;

PROMPT ******************************************** GLOBAL CACHE SERVICE REQUEST LATENCY (CURRENT BLOCK) < 20 MS
PROMPT

SELECT received.inst_id, received.value “current blocks received”, receive_time.value “current block receive time”, 10*receive_time.value/received.value “LATENCY MS”
FROM gv$sysstat received, gv$sysstat receive_time
WHERE received.name = ‘global cache current blocks received’ AND
receive_time.name = ‘global cache current block receive time’ AND
received.inst_id = receive_time.inst_id;

PROMPT ******************************************** AVERAGE BLOCK MODE CONVERTION TIME < 20 MS
PROMPT

SELECT converts.inst_id, converts.value “global cache converts”, convert_time.value “global cache convert time”, 10*convert_time.value/converts.value “AVERAGE CONVERT TIME MS”
FROM gv$sysstat converts, gv$sysstat convert_time
WHERE converts.name = ‘global cache converts’ AND
convert_time.name = ‘global cache convert time’ AND
converts.inst_id = convert_time.inst_id;

PROMPT ******************************************** AVERAGE GET TIME < 30 MS
PROMPT

SELECT gets.inst_id, gets.value “global cache gets”, get_time.value “global cache get time”, 10*get_time.value/gets.value “AVERAGE GET TIME MS”
FROM gv$sysstat gets, gv$sysstat get_time
WHERE gets.name = ‘global cache gets’ AND
get_time.name = ‘global cache get time’ AND
gets.inst_id = get_time.inst_id;

PROMPT ******************************************** EVENTS
PROMPT

SELECT inst_id, name, value
FROM gv$sysstat
WHERE name IN (’global cache blocks corrupt’,'global cache blocks lost’,'global cache convert timeouts’) AND
value > 0
ORDER BY 3 DESC;

SELECT lost.inst_id, lost.value “gc blocks lost”, cur.value “gc current blocks served”, consistent.value “gc cr blocks served”, 100*lost.value/(cur.value + consistent.value)
FROM gv$sysstat lost, gv$sysstat cur, gv$sysstat consistent
WHERE lost.name = ‘global cache blocks lost’ AND
cur.name = ‘global cache current blocks served’ AND
consistent.name = ‘global cache cr blocks served’ AND
lost.inst_id = cur.inst_id AND
cur.inst_id = consistent.inst_id;

SELECT a.inst_id, a.value “DBWR fusion writes”, b.value “physical writes”, 100*a.value/b.value
FROM gv$sysstat a, gv$sysstat b
WHERE a.name = ‘DBWR fusion writes’ AND
b.name = ‘physical writes’ AND
a.inst_id = b.inst_id;

PROMPT ******************************************** < 30
PROMPT

SELECT a.inst_id, a.value “gc defers”, b.value “gc current blocks served”, 100*a.value/b.value
FROM gv$sysstat a, gv$sysstat b
WHERE a.name = ‘global cache defers’ AND
b.name = ‘global cache current blocks served’ AND
a.inst_id = b.inst_id;

SELECT username||’.'||name segmento, kind tipo, partition_name, SUM(forced_reads) forced_reads, t.status
FROM gv$cache_transfer t, dba_users u
WHERE user_id = owner# AND
username NOT LIKE ‘%SYS%’
GROUP BY username||’.'||name, kind, partition_name, t.status
HAVING SUM(forced_reads) > 10
ORDER BY 4 DESC;

PROMPT
PROMPT ******************************************** TOP TEN SEGMENTS STATISTICS
PROMPT

COLUMN statistic_name FORMAT a34

PROMPT ******************************************** GLOBAL CACHE CR BLOCKS SERVED

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 = ‘global cache cr blocks served’
GROUP BY owner||’.'||object_name, subobject_name, statistic_name, tablespace_name, object_type
HAVING SUM(value) > 0
ORDER BY 4 desc)
WHERE rownum < 11;

PROMPT ******************************************** GLOBAL CACHE CURRENT BLOCKS SERVED

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 = ‘global cache current blocks served’
GROUP BY owner||’.'||object_name, subobject_name, statistic_name, tablespace_name, object_type
HAVING SUM(value) > 0
ORDER BY 4 desc)
WHERE rownum < 11;

COLUMN statistic_name CLEAR

PROMPT ******************************************** GLOBAL ENQUEUE SERVICE
PROMPT

PROMPT ******************************************** GLOBAL ENQUEUE STATISTICS
PROMPT

SELECT inst_id, name, value
FROM gv$sysstat
WHERE name LIKE ‘global lock%’
ORDER BY name, inst_id;

PROMPT ******************************************** AVERAGE GLOBAL ENQUEUE TIME < 30 MS
PROMPT

SELECT sync_gets.inst_id, sync_gets.value “global lock sync gets”, async_gets.value “global lock async gets”,
get_time.value “global lock get time”, 10*get_time.value/(sync_gets.value + async_gets.value) “AVERAGE GET TIME MS”
FROM gv$sysstat sync_gets, gv$sysstat async_gets, gv$sysstat get_time
WHERE sync_gets.name = ‘global lock sync gets’ AND
async_gets.name = ‘global lock async gets’ AND
get_time.name = ‘global lock get time’ AND
sync_gets.inst_id = get_time.inst_id AND
sync_gets.inst_id = async_gets.inst_id;

PROMPT ******************************************** AVERAGE GLOBAL LOCK CONVERT TIME < 20 MS
PROMPT

SELECT sync_converts.inst_id, sync_converts.value “global lock sync converts”, async_converts.value “global lock async converts”,
convert_time.value “global lock convert time”, 10*convert_time.value/(sync_converts.value + async_converts.value) “AVERAGE LOCK CONVERT TIME MS”
FROM gv$sysstat sync_converts, gv$sysstat async_converts, gv$sysstat convert_time
WHERE sync_converts.name = ‘global lock sync converts’ AND
async_converts.name = ‘global lock async converts’ AND
convert_time.name = ‘global lock convert time’ AND
sync_converts.inst_id = convert_time.inst_id AND
sync_converts.inst_id = async_converts.inst_id;

PROMPT ******************************************** TICKETS GLOBAL ENQUEUE SERVICE
PROMPT

SELECT inst_id, tckt_avail, tckt_limit, tckt_rcvd, tckt_wait, status
FROM gv$ges_traffic_controller;

PROMPT ******************************************** TICKETS DLM
PROMPT

SELECT inst_id, tckt_avail, tckt_limit, tckt_rcvd, tckt_wait, status
FROM gv$dlm_traffic_controller;

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

SELECT inst_id, namespace, dlm_lock_requests, dlm_pin_requests, dlm_pin_releases, dlm_invalidation_requests, dlm_invalidations
FROM gv$librarycache
ORDER BY 2,1;

PROMPT ******************************************** ROW CACHE
PROMPT

SELECT inst_id, parameter, gets, getmisses, DECODE(100*getmisses/gets,0,1,100*getmisses/gets) “ROW CACHE MISS RATIO %”, dlm_requests, dlm_conflicts, dlm_releases
FROM gv$rowcache
WHERE dlm_conflicts > 0 AND
DECODE(100*getmisses/gets,0,1,100*getmisses/gets) > 2
ORDER BY 2,1;

PROMPT ******************************************** ENQUEUE STATISTICS
PROMPT

SELECT eq_type, SUM(total_wait#) esperas, SUM(***_wait_time) ***_wait_time
FROM gv$enqueue_stat
GROUP BY eq_type
HAVING SUM(***_wait_time) > 100000
ORDER BY 3 DESC;

SPOOL OFF

Saludos,

Francisco Munoz Alvarez

This entry was posted on 3. June 2008 at 00:42 and is filed under Monitoreo, Scripts. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or trackback from your own site.

| Print