Sea un Heroe, sea proactivo!

12. January 2009 by admin.

Comiésemos este año nuevo con una nueva mentalidad, o una resolución de año nuevo si quieres pensarlo de ese modo, dejemos de ser reactivos y empecemos a ser proactivos. Ser proactivo va a ayudarlo a reducir sus costos en administración de base de datos, incrementar tu nivel de eficiencia, ayudarte a cumplir más fácilmente los SLA’s y lo que es mejor salvar tus horas de sueño cuando estés de turno.

¿Porque chequear los problemas solamente cuando estos ya son críticos, o cuando ya es muy tarde y la base de datos ya está abajo o congelada, o lo que es peor, cuando los usuarios ya están gritando?

Ser proactivo es la mejor manera de mantener tu base de datos saludable y de mostrarle a tu empresa o tus clientes que tu realmente te preocupas por ellos.

No sé porque, pero muchos DBA’s gastan la mayoría de su tiempo siendo bomberos solamente (puro apagando incendios) arreglando problemas o trabajando en requerimientos de los usuarios. Este tipo de mentalidad o comportamiento va solamente traer como resultado, miles de dólares en horas extras (si es que te las pagan), varias horas sin sistema para los usuarios, baja performance de las aplicaciones y lo que es peor de todo, varios usuarios insatisfechos que pensaran que tú no tienes el conocimiento necesario para estar a cargo de su data.

Mencionemos un pequeño ejemplo, tienes configurado el alarma del área de archive logs en que se dispare cuando este en un 95% lleno, y esto ocurre en la mitad de la noche, algunos DBA’s van a tomar este alerta seriamente y lo arreglaran de inmediato, otros esperaran hasta el día siguiente para resolverlo ya que o están muy cansados y dormidos, o no tienen en ese momento acceso a internet para resolverlo. Hubiera sido mucho mejor y más fácil si en vez del alerta estar configurado en un nivel crítico solamente, haberlo puesto con un monto mas proactivo como por ejemplo un 75% o un 85%, o lo que hubiera sido aun mejor, haber mirado la salud de tu DB antes de haberte ido del trabajo para detectar y resolver cualquier posible problema antes que fuera un problema real o para planear como solucionarlo a tiempo antes de que te despierten en la mitad de la noche o te interrumpan durante el fin de semana (Acuérdate siempre que tu tiempo personal y familiar es lo más importante que tienes y siempre debes de trabajar en pro de protegerlo y cuidarlo). A mí personalmente siempre me gusta recomendar a los DBA’s que corran 2 checklists diarios, uno a principio de su jornada laboral y otro al final de esta.

Yo conozco a muchos DBA’s que reclaman todo el tiempo que no tienen vida, ya que se vuelven locos cuando están de turno por el volumen de llamados y de las interrupciones que ocurren durante los fines de semana y noches. Pero esto solamente les ocurre porque ponen su atención y tiempo en solucionar los síntomas y no los problemas de raíz o si no es posible, en tomar medidas proactivas para protegerse.

Ser proactivo no solamente lo ayudara a obtener una mejor calidad de vida, también lo ayudara a detectar puntos de mejora en: performance, seguridad o quién sabe, simplemente evitar un posible desastre futuro antes que cualquier otra persona lo haya detectado.

Aquí podrás encontrar un script de checklist que podrás usar como ejemplo para ayudarte a hacer tu vida un poco mas fácil (Este no es mi script completo, pero muy bueno para empezar). Este script es una compilación de varios scripts de checklist y tu podras modificar facilmente las variables (thresholds) segun tus necesidades, pero eso si, acuerdese siempre de tener una baseline con que compararlo.

Como mencionado antes, este script no solamente lo ayudara a Ud. A detectar problemas actuales o futuros, pero también lo ayudara a detectar futuros requerimientos de tuning también.

Aquí está un ejemplo de lo que sería la primera parte del reporte generado por el script:

– ———————————————————————– –
– Oracle Instance Information
– ———————————————————————– –
Cpu_Count                                    4   |      Host_Name              OLIVER
Instance_Name                        prod   |      Database_Status      ACTIVE
Status                                     OPEN   |     Startup_Time          10-01-2009 19:50
Version                            11.1.0.7.0   |      Instance_Role          PRIMARY_INSTANCE
Database Space (Mb)           36604    |      SGA (Mb)                 511
Nb. Datafiles                               43    |     Nb. Tempfiles           1

Archive destination LOCATION=E:\oracle\oradata\prod\archive
Database log mode ARCHIVELOG
Background Dump Dest d:\oracle\diag\rdbms\prod\prod\trace
Spfile D:\ORACLE\PRODUCT\11.1\PROD\DATABASE\SPFILEPROD.ORA
Redo size (Kb) 102400

– ———————————————————————– –
– Instance CheckList –
– ———————————————————————– –
Instance Status                                  OK    |      Listener Status      OK
– ———————————————————————– –
– Performance Memory CheckList –
– ———————————————————————– –
Total Sessions < 700                           OK - 19
Active sessions number <15                 OK - 9
Data Buffer Hit Ratio > 80                   OK - 97
L.Buffer Reload Pin Ratio > 99           OK - 99
Row Cache Miss Ratio < 0.015            NO - 1.351
Dict.Buffer Hit Ratio > 80                    OK - 99
Log Buffer Waits = 0                            NO - 110
Log Buffer Retries < 0.0010                 OK - 0
Switch number (Daily Avg) < 5            OK - 1
Jobs Broken = 0                                   OK 0
Shared_Pool Failure = 0                     OK - 0
– ———————————————————————– –
– Storage CheckList                                            –
– ———————————————————————– –
Dba_Tablespaces Status                     OK   |       V$Log Status             OK
V$Datafile Status                                OK   |       V$Tempfile Status   OK
V$Recover_File                                  OK   |       V$Recovery_Log      OK
Tablespace in Backup Mode = 0        OK - 0
Tablespace < 95%                              OK- 0
Objects Invalid = 0                              NO - 147
Indexes unusable = 0                          OK - 0
Trigger Disabled = 0                            NO- 5
Constraint Disabled = 0                       NO - 2
Objects close max extents = 0             OK - 0
Objects can not extent = 0                  NO - 552
User Objects on Systems = 0               NO - 26
FK Without Index = 0                          NO - 138
– ———————————————————————– –
– Datagard CheckList                                          –
– ———————————————————————– –
Datagard Errors = 0                             OK- 0
Datagard Gap = 0                               OK - 0
Archives not Aplied < 5                       OK - 2
– ———————————————————————- –
– Installed options :
– ———————————————————————- –
- Objects option
- Connection multiplexing option
- Connection pooling option
- Database queuing option
- Incremental backup and recovery option
- Instead-of triggers option
- Parallel load option
- Proxy authentication/authorization option
- Plan Stability option
- Coalesce Index option
- Transparent Application Failover option
- Sample Scan option
- Java option
- OLAP Window Functions option

Ud. También podrá encontrar varios productos que lo podrán ayudar a monitorear y configurar sus alertas de una forma proactiva, tales como: Grid Control (Oracle), Enterprise Manager (Oracle), Insider (FourthElephant), Spotlight (Quest), entre muchos otros más disponibles en el mercado y también caso sea su preferencia, utilizar sus propios scripts para esta finalidad. La idea es siempre usarlos de una forma proactiva, nunca reactiva.

Por eso cambiemos nuestra mentalidad y dejemos de ser bomberos para comenzar a ser realmente héroes.

Mucha suerte,

Francisco Muñoz Alvarez

Posted in Monitoreo, Tuning, Experiencia, Tutoriales, Scripts | Print | 4 Comments »

status with colors :D

13. June 2008 by hmiranda.

####### $HOME/bin/status #################

#!/bin/bash
SETCOLOR_SUCCESS=”\33[1;32m”
SETCOLOR_FAILURE=”\33[1;31m”
SETCOLOR_WARNING=”\33[1;33m”
SETCOLOR_NORMAL=”\33[0;39m”

lsnrctl status > /dev/null 2>&1

if test $? == 0 ; then
echo -en “Listener $SETCOLOR_SUCCESS OK $SETCOLOR_NORMAL \n”
else
echo -en “Listener $SETCOLOR_FAILURE FAIL $SETCOLOR_NORMAL \n”
fi
for i in $(cat /etc/oratab | grep -v “#” | strings | awk -F: ‘{print $1}’) ; do
if test “” != “$(ps -fea | grep pmon | grep $i)” ; then
echo -en “$i \t $SETCOLOR_SUCCESS OK $SETCOLOR_NORMAL $SETCOLOR_WARNING\t”
export ORACLE_SID=$i
sqlplus -S / as sysdba <<EOF
@$HOME/bin/status.sql
EOF
echo -en “$SETCOLOR_NORMAL”
else
echo -en “$i \t $SETCOLOR_FAILURE FAIL $SETCOLOR_NORMAL \n”
fi
done

############################################################

############## $HOME/bin/status.sql #############################

set feedb off
set pages 0
select status from v$instance ;

############################################################

Posted in Monitoreo, Scripts | Print | 2 Comments »

FlashBack Monitoring Script

10. June 2008 by fmunoz.

Hola,

Aqui les publico un script para monitorear el area de Flashback.

PROMPT
PROMPT ******************************************** FLASH RECOVERY AREA *****************************************************

SELECT NAME fra, SPACE_LIMIT/1024/1024/1024 “GB LIMITE”, ROUND(SPACE_USED/1024/1024/1024) “GB USADOS”, ROUND(SPACE_RECLAIMABLE/1024/1024) “MB RECLAMABLES”, NUMBER_OF_FILES archivos
FROM V$RECOVERY_FILE_DEST;

PROMPT
PROMPT ******************************************** FLASH RECOVERY AREA USAGE

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

PROMPT
PROMPT ******************************************** FLASHBACK

SELECT * FROM GV$FLASHBACK_DATABASE_LOGFILE;

SELECT * FROM GV$FLASHBACK_DATABASE_LOG;

SELECT * FROM GV$FLASHBACK_DATABASE_STAT;

Saludos a todos,

Francisco Munoz Alvarez

Posted in Monitoreo, Scripts | Print | 1 Comment »

Monitoreo de RAC

3. June 2008 by fmunoz.

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

Posted in Monitoreo, Scripts | Print | 12 Comments »

Script CheckList.sql para verificar status de Instancia

4. May 2008 by jpuga.

prompt
prompt
prompt ################################################################################
prompt
prompt
prompt By Mohit sharma
prompt mohit.sharma@india.com
prompt
prompt Edited By
prompt juanpuga@gmail.com
prompt
prompt ################################################################################

set linesize 132
set pagesize 1000

spool report.txt

prompt #### Up Time ####

select ‘Hostname : ‘ || host_name
,’Instance Name : ‘ || instance_name
,’Started At : ‘ || to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) stime
,’Uptime : ‘ || floor(sysdate - startup_time) || ‘ days(s) ‘ ||
trunc( 24*((sysdate-startup_time) -
trunc(sysdate-startup_time))) || ‘ hour(s) ‘ ||
mod(trunc(1440*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||’ minute(s) ‘ ||
mod(trunc(86400*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||’ seconds’ uptime
from sys.v_$instance;
prompt
prompt
prompt ################################################################################

prompt .>>>>>> Object status

select object_type,status,count(*) cnt from user_objects group by object_type,status;
prompt
prompt
prompt ################################################################################

prompt .>>>>>> Invalid objects

select owner, object_type, substr(object_name,1,30) object_name
from sys.dba_objects
where status=’INVALID’
order by object_type;

prompt
prompt
prompt ################################################################################

prompt .>>>>>> Recover files

select * from sys.v_$recover_file;
prompt
prompt
prompt ################################################################################

prompt .>>>>>> partitioned table

select table_name from user_tables where partitioned=’YES’;
prompt
prompt
prompt ################################################################################

prompt .>>>>>> Free space in Tablespace

Select tablespace_name,sum(bytes/1024/1024) from sys.dba_free_space group by tablespace_name;
prompt
prompt
prompt ################################################################################

prompt .>>>>>>Max Extents Status

select segment_name, segment_type, extents, max_extents from sys.dba_segments where max_extents-extents<100;
prompt
prompt
prompt ################################################################################

prompt .>>>>>> Any partitioned Object approaching to max extents

SELECT PARTITION_NAME,EXTENTS,MAX_EXTENTS,NEXT_EXTENT,max_extents-nvl(next_extent,0) FROM sys.dba_segments
where max_extents-nvl(next_extent,0) < 1000
and partition_name is not null;
prompt
prompt
prompt ################################################################################

prompt .>>>>>> Block Contention

SELECT class,sum(count) total_waits, sum(time) total_time FROM v$waitstat GROUP BY class;

prompt
prompt
prompt ################################################################################

prompt .>>>>>> Latch Contention

SELECT a.name,100.*b.sleeps/b.gets ratio1,100.*b.immediate_misses/decode((b.immediate_misses+b.immediate_gets),0,1) ratio2
FROM v$latchname a, v$latch b WHERE
a.latch# = b.latch# AND b.sleeps > 0;

prompt
prompt
prompt ################################################################################

prompt .>>>>>> Top 10 Queries using more disk reads

select disk_reads,sql_text from v$sqlarea where disk_reads>10000 and rownum < 11 order by disk_reads desc;

prompt
prompt
prompt ################################################################################

prompt .>>>>>> Top 10 Queries using more buffer reads

select buffer_gets,sql_text from v$sqlarea where buffer_gets>10000 and rownum < 11 order by buffer_gets desc;

prompt
prompt
prompt ################################################################################

prompt .>>>>>> buffer cache hit ratio

select (1- (sum(decode(a.name,’physical reads’,value,0)))/
(sum(decode(a.name,’db block gets’,value,0)) +
sum(decode(a.name,’consistent gets’,value,0)))) * 100 pct
from v$sysstat a;

prompt
prompt
prompt ################################################################################

prompt .>>>>>> Reloads/Pins in v$librarycache

select sum(pins) “executions”,sum(reloads) “cache Misses”,sum(reloads)/sum(pins)*100 “Ratio” from v$librarycache;

prompt
prompt
prompt ################################################################################

prompt .>>>>>> Get miss ratio from v$rowcache

select (sum(getmisses)/sum(gets)) * 100 “Hit Ratio” from v$rowcache;

prompt
prompt
prompt ################################################################################

prompt .>>>>>> Redo log space request ratio in v$systat

select (req.value*5000)/entries.value “ratio”
from v$sysstat req,v$sysstat entries
where req.name=’redo log space requests’ and entries.name=’redo entries’ ;

prompt
prompt
prompt ################################################################################

prompt .>>>>>> RBS Contention

select sum(waits)/sum(gets)*100 from v$rollstat;

prompt
prompt
prompt ################################################################################

prompt .>>>>>> sorts stats

Select * from v$sysstat where name like ‘%sorts%’;

prompt
prompt
prompt ################################################################################

prompt .>>>>>> datafiles I/O

select name, phyrds, phywrts from v$datafile df, v$filestat fs where df.file# = fs.file# order by phyrds;

prompt
prompt
prompt
prompt

spool off

Posted in Monitoreo, Tuning | Print | No Comments »

Informacion de la Library Cache

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 »

Informacion sobre Latch

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 »

Informacion Sobre Locks

11. April 2008 by fmunoz.

PROMPT
PROMPT ******************************************** LOCK ******************************************************************

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

SPOOL lock_&sid..txt

PROMPT
PROMPT ******************************************** PARAMETROS CONFIGURADOS

SELECT inst_id, name parametro, DECODE(type,1,’boolean’,2,’string’,3,’integer’) type, value valor
FROM gv$parameter2
WHERE name IN (’row_locking’,'dml_locks’,'enqueue_resources’)
ORDER BY 2,1;

PROMPT
PROMPT ******************************************** BLOQUEOS DE USUARIO

SELECT s.inst_id, o.owner||’.'||o.object_name objeto, start_time, t.xidusn, s.sid, s.serial#, s.machine, s.username usuario, s.osuser,
       DECODE(l.type, ‘TM’, ‘DML’, ‘UL’, ‘PL/SQL User Lock’, l.type) lock_type,
       DECODE(lmode,
  0, ‘None’,           /* Mon Lock equivalent */
  1, ‘Null’,           /* N */
  2, ‘Row-S (SS)’,     /* L */
  3, ‘Row-X (SX)’,     /* R */
  4, ‘Share’,          /* S */
  5, ‘S/Row-X (SSX)’, /* C */
  6, ‘Exclusive’,      /* X */
  TO_CHAR(lmode)) held,
       DECODE(request,
  0, ‘None’,           /* Mon Lock equivalent */
  1, ‘Null’,           /* N */
  2, ‘Row-S (SS)’,     /* L */
  3, ‘Row-X (SX)’,     /* R */
  4, ‘Share’,          /* S */
  5, ‘S/Row-X (SSX)’, /* C */
  6, ‘Exclusive’,      /* X */
  TO_CHAR(request)) requested,
       DECODE(block,
         0, ‘Not Blocking’, /* NOT blocking any other processes */
  1, ‘Blocking’,      /* This lock blocks other processes */
  2, ‘Global’,        /* This lock is global, so we can’t tell */
  TO_CHAR(block)) blocking_others
FROM gv$lock l, dba_objects o, gv$session s, gv$transaction t
WHERE l.type IN (’TM’,'UL’) AND
      o.object_id(+) = id1 AND
      s.sid = l.sid AND
      s.inst_id = l.inst_id AND
      l.inst_id = t.inst_id AND
      saddr = ses_addr(+)
ORDER BY 1,5;

Posted in Monitoreo, Scripts | Print | 1 Comment »

Detectar Operaciones Largas

11. April 2008 by fmunoz.

PROMPT
PROMPT ******************************************** LOCK ******************************************************************

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

SPOOL lock_&sid..txt

PROMPT
PROMPT ******************************************** PARAMETROS CONFIGURADOS

SELECT inst_id, name parametro, DECODE(type,1,’boolean’,2,’string’,3,’integer’) type, value valor
FROM gv$parameter2
WHERE name IN (’row_locking’,'dml_locks’,'enqueue_resources’)
ORDER BY 2,1;

PROMPT
PROMPT ******************************************** BLOQUEOS DE USUARIO

SELECT s.inst_id, o.owner||’.'||o.object_name objeto, start_time, t.xidusn, s.sid, s.serial#, s.machine, s.username usuario, s.osuser,
       DECODE(l.type, ‘TM’, ‘DML’, ‘UL’, ‘PL/SQL User Lock’, l.type) lock_type,
       DECODE(lmode,
  0, ‘None’,           /* Mon Lock equivalent */
  1, ‘Null’,           /* N */
  2, ‘Row-S (SS)’,     /* L */
  3, ‘Row-X (SX)’,     /* R */
  4, ‘Share’,          /* S */
  5, ‘S/Row-X (SSX)’, /* C */
  6, ‘Exclusive’,      /* X */
  TO_CHAR(lmode)) held,
       DECODE(request,
  0, ‘None’,           /* Mon Lock equivalent */
  1, ‘Null’,           /* N */
  2, ‘Row-S (SS)’,     /* L */
  3, ‘Row-X (SX)’,     /* R */
  4, ‘Share’,          /* S */
  5, ‘S/Row-X (SSX)’, /* C */
  6, ‘Exclusive’,      /* X */
  TO_CHAR(request)) requested,
       DECODE(block,
         0, ‘Not Blocking’, /* NOT blocking any other processes */
  1, ‘Blocking’,      /* This lock blocks other processes */
  2, ‘Global’,        /* This lock is global, so we can’t tell */
  TO_CHAR(block)) blocking_others
FROM gv$lock l, dba_objects o, gv$session s, gv$transaction t
WHERE l.type IN (’TM’,'UL’) AND
      o.object_id(+) = id1 AND
      s.sid = l.sid AND
      s.inst_id = l.inst_id AND
      l.inst_id = t.inst_id AND
      saddr = ses_addr(+)
ORDER BY 1,5;

Posted in Monitoreo, Scripts | Print | 1 Comment »

Colectar Informacion del Rac

11. April 2008 by fmunoz.

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 ******************************************** PARAMETROS
PROMPT

SELECT inst_id, name parametro, DECODE(type,1,’boolean’,2,’string’,3,’integer’) type, value valor, 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 ******************************************** INSTANCIAS DEL RAC
PROMPT

SELECT inst_id, instance_name instancia, host_name servidor, thread#,instance_role, startup_time stime,
status, archiver, log_switch_wait, logins, shutdown_pending, database_status, active_state
FROM gv$instance
ORDER BY 1;

PROMPT ******************************************** HEBRAS
PROMPT

SET NUMWIDTH 15

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