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 »

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 »

15. May 2008 by fmunoz.

Estimados,

Es con gran Orgullo que les informo del nacimiento del “Grupo de Usuarios Oracle de Chile”, o “Chilean Oracle User Group” con su sigla “CLOUG”. para acceder a este y hacerse miembro, que por el momento es gratuito deben ir a:

www.cloug.org

Muchos saludos a todos,

Francisco Munoz Alvarez

Posted in Principal | Print | No Comments »

7. May 2008 by fmunoz.

Aqui van algunos tips para ayudarte con la performance de un import en 9i.

1- Analyze solo despues de la carga (Analyze=n)
2 -Tener el export a ser importado en un disco separado de los de datos de la BD.
3- Usar commit=n
4- Utilizar un solo segmento de rollback grande y poner los demas offline durante import
5- Utilizar indexes=n y recrearlos despues de terminado el import
6- Utilizar un buffer grande para el import. ejemplo:

(opcion del import - tiempo seg -Reduccion %)

commit=y - 120 - 0
commit=y,buffer=64000 - 100 - 17%
commit=n,buffer=30720 - 72 - 40%
commit=n,buffer=64000 - 67 - 44%

7- Pre-crear tablas anticipadamente con scripts y setear ignore=y
8- Setear db_cache_size y log_buffer para un tamano mayor
9- Parar archive, (ALTER DATABASE NOARCHIVELOG;)

Despues de terminada la carga, no olvidarse de crear los indices y generar estadisticas nuevas.

Espero haberte podido ayudar con estos tips.

Saludos,

Francisco Munoz Alvarez

Posted in White Papers | Print | 1 Comment »

6. May 2008 by fmunoz.

Estimados,

 En mi carrera, varias veces me toco ver situacion y discusiones sobre cuando y como crear nuevos grupos de Redologs en una BD Oracle.

Como ejemplo sitare una situacion que ocurrio en 2 grandes proyectos de Chile (No citare nombres para no causar problemas con algunos colegas). Mi idea de recurir a este tema no es causar discusiones y si plantear mi vision y solucion para problemas de este tipo.

Cuando se descubre un gran volumen de contecion (Wait) en redologs, en las mayoria de las veces al ver el numero de log switches se descubre que hay un gran numero de cambio de redologs por hora o en algunos casos en pequenas ventanas de tiempo.

 Ejemplo:

En la BD en question ocurrian mas de 110 log switches por hora y en algunas situaciones, llegaba a casi 600.

Esta BD tenia 10 grupos de redo con dos miembros de 10 MB cada uno. Una BD de 800 GB de tamano y muy transaccional.

Debido a esto, esta BD se pisaba la cola muchas veces durante el dia causando seria contencion en ese sentido. Ese fue el motivo de mi discucion con ciertos Gurus de Oracle en Chile. La solucion de ellos era crear mas grupos de redo de 10MB, por ejemplo de 10 a 50 grupos para disminuir las veces que se pisaba la cola el log switch. Esto en mi opinion no solucionaba el problema ya que el numero de log switches por hora continuaria igual que antes. Es como por ejemplo, estar en un bote que se esta hundiendo, y para evitar esto utilizas una cuchara de sopa para sacar el agua. Por mas que te esfuerces el agua que entra es mayor al que sale. Puedes hacerlo mas rapido y solo lograras hundirte y cansarte como loco. Pero si en vez de una cuachara usaras un balde de 5 litros, podrias sacar el agua mas rapido de lo que entra y evitar que se hunda, caso sea necesario, utilizarias un balde mayor caso el monto de agua que entra es mas grande. Lo mismo se aplica en este sentido. Por eso, yo discutia que habia que aumentar el tamano de los miembros de cada grupo para controlar esta situacion. Es correcto que aumentar el tamano de los miembros de los grupos de Redolog aumentara el tamano de cada archive log, y consequentemente demorara mas el tiempo de recuperacion en caso de falla ya que la cantidad de transacciones a ser aplicada por cada archive sera mayor. Pero que es mejor?

En esta situacion se generan mas de 1000 archives de 10MB cada uno diariamente, sera mejor aplicar 1000 archives en caso de ser necesaria una recuperacion de la BD por falla fisica o logica 24 hrs despues del ultimo respaldo o aplicar 100 archives de 100MB? o 10 de 1GB? Aumentar el tamano de los miembros de los grupos de Redologs ayuda a evitar que se llenen muy rapido y que causen contencion a nivel de I/O y del LGWR y ARC.

Para saber el numero de log switches en su BD puede ejecutar el siguiente script:

spool logs.log
set echo on
set linesize 150
set pagesize 500
column day format a16  heading ‘Dia’
column d_0 format a3  heading ‘00′
column d_1 format a3  heading ‘01′
column d_2 format a3  heading ‘02′
column d_3 format a3  heading ‘03′
column d_4 format a3  heading ‘04′
column d_5 format a3  heading ‘05′
column d_6 format a3  heading ‘06′
column d_7 format a3  heading ‘07′
column d_8 format a3  heading ‘08′
column d_9 format a3  heading ‘09′
column d_10 format a3  heading ‘10′
column d_11 format a3  heading ‘11′
column d_12 format a3  heading ‘12′
column d_13 format a3  heading ‘13′
column d_14 format a3  heading ‘14′
column d_15 format a3  heading ‘15′
column d_16 format a3  heading ‘16′
column d_17 format a3  heading ‘17′
column d_18 format a3  heading ‘18′
column d_19 format a3  heading ‘19′
column d_20 format a3  heading ‘20′
column d_21 format a3  heading ‘21′
column d_22 format a3  heading ‘22′
column d_23 format a3  heading ‘23′
column  Total   format 9999
column status  format a8
column member  format a40
column archived heading ‘Archived’ format a8
column bytes heading ‘Bytes|(MB)’ format 9999
Ttitle  ‘Log Info’  skip 2
select l.group#,f.member,l.archived,l.bytes/1078576 bytes,l.status,f.type
  from v$log l, v$logfile f
 where l.group# = f.group#
/
Ttitle off
prompt =========================================================================================================================
Ttitle  ‘Log Switch on hour basis’  skip 2

select to_char(FIRST_TIME,’DY, DD-MON-YYYY’) dia,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’00′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’00′,1,0))) d_0,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’01′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’01′,1,0))) d_1,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’02′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’02′,1,0))) d_2,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’03′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’03′,1,0))) d_3,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’04′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’04′,1,0))) d_4,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’05′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’05′,1,0))) d_5,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’06′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’06′,1,0))) d_6,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’07′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’07′,1,0))) d_7,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’08′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’08′,1,0))) d_5,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’09′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’09′,1,0))) d_9,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’10′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’10′,1,0))) d_10,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’11′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’11′,1,0))) d_11,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’12′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’12′,1,0))) d_12,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’13′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’13′,1,0))) d_13,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’14′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’14′,1,0))) d_14,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’15′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’15′,1,0))) d_15,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’16′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’16′,1,0))) d_16,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’17′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’17′,1,0))) d_17,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’18′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’18′,1,0))) d_18,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’19′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’19′,1,0))) d_19,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’20′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’20′,1,0))) d_20,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’21′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’21′,1,0))) d_21,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’22′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’22′,1,0))) d_22,
       decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’23′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’23′,1,0))) d_23,
       count(trunc(FIRST_TIME)) Total
 from v$log_history
 group by to_char(FIRST_TIME,’DY, DD-MON-YYYY’)
 order by to_date(substr(to_char(FIRST_TIME,’DY, DD-MON-YYYY’),5,15) )
/
Ttitle off
prompt
spool off

Espero que esta nota ayude a algunos de uds a detectar este tipo de situaciones, es muy comun encontrar esto en muchas Bases de Datos. Es increible lo que ayuda cuando se detecta y se soluciona este problema.

Para finalizar acuerdense que cada vez que ocurre un log switch, ocurre un checkpoint o sea imaginense que si esta ocurriendo un log switch 100 veces en una hora, 100 veces el oracle limpio el buffer a disco, 100 veces actualizo los header (encabezados) de todos los archivos de la BD ,100 veces genero archives y mucho mas cosas que ocurren por debajo a nivel de procesos, consultas,etc. En estos casos dejamos el pobre DBWR que es el encargado de hacer esto con la lengua afuera y empiezan a ocurrir los “incomplete checkpoints” en el alert.log. Ya pueden imaginarse la cantidad de I/O generada tambien.

Espero haberles ayudado a entender este importante proceso de tuning de una BD.

Muchos Saludos,

Francisco Munoz Alvarez

Posted in White Papers | Print | No Comments »

28. April 2008 by fmunoz.

Uno de los grandes problemas que entrentan muchos DBAs en el mercado actual, es que no saben o no entienden el funcionamento del soporte Oracle, como utilizar las mejores funcionalidades del metalink, etc.

 Ahora podran entender mejor el funcionamento de este utilizando este seminario online de “Trabajando Efectivamente con Soporte Oracle”.

Para acceder a este utilziar el siguiente link: http://oukc.oracle.com/static05/opn/25688/082505_25688/index.htm

Muchos saludos a todos,

Francisco Munoz Alvarez.

 Nota: Mis agradecimientos a Juan Puga por informarme de este material.

Posted in Principal | Print | 1 Comment »

16. April 2008 by fmunoz.

Estimados,

En este momento me encuentro trabajando en notas tecnicas referentes los siguientes temas:

• Parsing (Hard Parse y Soft Parse cual es la diferencia y como funcional realmente) 
• Licencias Oracle
• Como funciona el Oracle (La Instancia)
• Entender NoLogging y Logging Metodologia
• Database Tuning
  • Redologs
  • Datafiles
  • I/O
  • Estadisticas
  • Particionamento
  • Locks

Tambien me encuentro trabajando en un libro que explica facilmente y claramente lo que es y como funciona una BD Oracle y introducion al Lenguage SQL y PL/SQL, en un formato que hasta un TAM (Technical Account Manager) lo entienda.

Tanto en Chile, como en NZ me e dado cuenta que no existem muchos DBAs con experiencia en Tuning y con un real entendimiento de como funciona el Oracle internamente. Mi idea es cambiar eso un poco y darles las herramientas y la informacion necesaria para que todos los DBAs que quieran evolucionar entiendan estas tecnicas y las puedan aplicar en sus respectivos ambientes de trabajo. 

Cada semana estare publicando 1 o mas notas tecnicas para ponerlos al tanto de mis experiencias.

Saludos a todos,

Francisco Munoz Alvarez

Posted in Principal | Print | No Comments »

14. April 2008 by fmunoz.

Salio la nueva lista de Productos afectados por el nuevo patch de actualizacion de seguridad de Oracle.

Este nuevo patch solo actualiza Server side no Client side y afecta los siguientes productos:

Para mas informacion favor ir al siguiente link:

http://www.oracle.com/technology/deploy/security/critical-patch-updates/cpuapr2008.html

Tambien les recuerdo que el patch set para 10.2.0.4 ya se encuentra disponible para ser bajado, esta version permitira la integracion para pruebas y grabar cargas de esta version a la 11g.

Saludos,
Francisco Munoz Alvarez

Posted in News | Print | 1 Comment »

11. April 2008 by fmunoz.

Francisco Javier Munoz Alvarez
email: mbatec@hotmail.com 

OVERVIEW:

• Around 16 years of experience in consulting, analysis, support, Implementation, and Migration of Oracle Databases
• Excellent Implementation Methodologies
• Expertise in most phases of Database life cycle: Development, Stabilization, Security, Backup and Recovery, Tuning, Installations, and Data warehouse (“ETL”).
• Extensive experience with SQL and PL/SQL.
• Strong analytical, communication and leadership skills. Strong team player
• Worked as Associate Technology at Oracle Brazil and Chile
• Oracle Instructor for New Horizons Center, Chile.  (www.newhorizons.com)
• OCE Oracle Certified Expert (Oracle RAC 10g)
• OCP Oracle 10g DBA Certified
• OCP Oracle E-Business Suite 11i
• OCA Oracle Application Server 10g Administrator
• OCA - PL/SQL DEVELOPER
• OCM Database Administrator Oracle 7
• OCA Oracle 9i DBA Certified
• ITIL Fundamentals Certified

TECHNICAL SUMMARY:

Oracle 7, Oracle 8i, Oracle 9i , 10g , SQL Server Administration , SQL*Plus, SQL, PL/SQL(cursors, sub-queries, triggers, procedures, packages, etc.), SQL Navigator, Toad, Insider, MySQL, PHP, ASP, HTML, Microsoft Access, SQL Server 2000, SQL Load, ETL, Oracle Replication, RAC, ODBC, Oracle Data Warehouse, Datagard, Oracle Grid, OEM, MAA (Oracle Maximum Availability Architecture), Listener, SQL NET, Data Pump, HP/UX, SUN/SOLARIS, NT, AIX, LINUX, DG/UX, Windows 2003, RS/9000,VMS, UNIX, Windows ,Macromedia Flash, Fireworks, FrontPage, Adobe Photoshop, Paintshop Pro ,Microsoft Office suite, Microsoft Project
EXPERIENCE: 

DATACOM SYSTEMS –Auckland, New Zealand

DATACOM -  New Zealand’s largest locally owned IT services company

02/08 – Present, Senior Oracle Consultant

• DBA Service Management for a wide DBA services (Oracle/SQL Server/OSS Databases)
  • This role will cover process/operational/functional management across the DBA functions and working with line managers to develop synergies across the different DBA teams (virtual team of 9)
• Assisting account managers with presales activities (RFP’s, prospects etc.)
• Oracle Consultancy 

SONDA S.A. - Santiago, Chile,
South America 

SONDA is one of the main systems integrators and IT service providers in Latin America. Its mission is to add value to its clients by providing world class IT services and solutions, helping them to achieve a better competitive position. Employing more than 4.500 people, and with annual sales of more than US$ 350 millions, it has an extensive reach in the region, with operations in Argentina, Brazil, Chile, Colombia, Costa Rica, Ecuador, Mexico, Peru and Uruguay.

12/06-02/08, Manager of DBA and Applications Department, Senior DBA

Responsible for Database Administration of 112 productions instances, manage a team of 23 DBA’S, responsible for reducing down time and increase all compliance of SLAs, introduced the MOF (Microsoft Operation Frameworks) ITIL based methodology to the department, increased applications performance, Database performance, new security checklists, preventive monitoring checklists and tools (Insider, Application Manager, and Tivoli), and introduce the Oracle Maximum Availability Architecture.  Provided technical support for critical databases, Data Warehouse databases, user problems, and developers. Monitored error logs, tablespaces, indexes, PL/SQL, data analysis, Triggers, Backup & Recovery Techniques, performance tuning, data migrations, software installations, database creations/configurations and upgrading the Oracle systems. Maintained Oracle systems in both Development and Production environments, 24 * 7 . Accomplishments:

• SLA - Order of Service Attention (20 minutes) : 64% to 96% at 3 months.
• SLA - Order of Service Finish (4 Hours): 52% to 95% at 3 months.
• SLA - Uptime Instances: 92.9% to 99.9% at 3 months.
• SLA - Customer Satisfaction: 68% to 98% at 3 months
• Obtain ISO 9001-2000 Certification to department.
• Percent of DBA’s certifications: 8% to 90% at 6 months (Coach all DBA’s , and provided internal training)
• Generated several new business opportunities

Environments: Oracle 7.x, 8.i, 9.i , 10g ,SQL Server, ETL, MAA, OEM, MYSQL, E-Commerce, SQL*Plus, SQL, PL/SQL, ACCESS, SQL*Load, Rac, Oracle Replication, Datagard, HP/UX, SUN/SOLARIS, NT, AIX, LINUX, Windows, Tru64, Toad, Insider, Data pump, ODBC, Listener.
EXPRESS TRAVEL GROUP –
Charlotte, NC

12/01-12/06 Senior Oracle DBA

Responsible for Internet Development and Database Administration.Responsible for reducing down time, increase applications performance, Database performance, security , and projects success. Provided technical support for databases, user problems, and developers. Monitored error logs, tablespaces, indices, PL/SQL, data analysis, Triggers, Backup & Recovery Techniques, performance tuning, data migrations, software installations, database creations/configurations and upgrading the Oracle systems, Maintained Oracle systems in both Development and Production environments, 24 * 7, loads, and security.

Environments: Oracle 7.x, 8.i, 9.i , Flash, PHP, MYSQL, FrontPage , Fireworks, PhotoShop, ASP, E-Commerce, SQL*Plus, SQL, PL/SQL, ACCESS, SQL*Load, Rac, Oracle Replication, HP/UX, SUN/SOLARIS, NT, LINUX, Import, Export, ODBC, Listener. 

PLANET MEDIA INT’L, INC –
Miami, FL

03/97-12/01 Senior Oracle DBA and Web Administrator

Responsible for the Internet Development and Oracle Administration Area. Provided technical support for databases, user problems, and developers. Monitored error logs, tablespaces, indices, PL/SQL, data analysis, Triggers, Backup & Recovery Techniques, performance tuning, data migrations, software installations, database creations/configurations and upgrading the Oracle systems.

Environments: Oracle 7, 8i, 9i , Flash, PHP, MYSQL, FrontPage
MBA & TECHNOLOGY LTDA. –
Santiago, Chile

01/92-03/97 Database Manager
Responsible for the department of Database Administration (Installation and configuration, backup & recovery strategies, system architecture planning and mentoring, performance tuning of Database and applications, SQL*Plus, PL/SQL, SQL*Net, SQL*Loader, Oracle Replication Manager), distribution of products Quest Corp. for Chile, controlling complete net sales and supporting on-line the products Quest. (Quest Corp. is a software manufacture for DBA, established in California, USA)

Environments: SUN, DG/UX, RS/9000, HP/UX, NT, SOLARIS, VMS, UNIX, ORACLE7, ORACLE8 and ORACLE REPLICATION MANAGER
FAST AIR CARRIER S.A (LAN CHILE HOLDING).
Santiago, Chile

05/90-05/93 Oracle DBA
DEFENSE INFORMATICA LTDA
Curitiba, Brazil

01/87-04/90 Developer/Consultant/DBA
EDUCATION:

UNIVERSIDAD DE SANTIAGO DE CHILE – USACH

Bachelor of Computer Science degree, May 1997
ORACLE EDUCATION,CALIFORNIA

Master Oracle DBA degree in Oracle7, 1997

Data Processing Courses, 1988 – Present

Took or Passed more than 30 courses, like Administration Oracle8i , Administration and Tuning Oracle 9i, and SQL & PL/SQL Advanced for Oracle 9i, Oracle 10g DBA I, DBA II, Tuning and 10g RACMost recent course: Oracle 10g DBA I, DBA II, Tuning and 10g RAC, Administration and Tuning Oracle 9i, and SQL & PL/SQL Advanced for Oracle 9i)
SKILLS & INTERESTS:
Trilingual: Proficient in English, Spanish and Portuguese. Strong analytical, communication and leadership skills.. Strong team player, and Paramedic (Volunteer Rescue Squad Team).

Posted in Curriculum | Print | No Comments »

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 »