MONITOR RAC

El siguiente sql , permite tener una vision general de seteos del motor y estado de valores .

 

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 c: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;
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 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;
SPOOL OFF