11. April 2008 by jpuga.

Como se si mi BD esta usando Pfile o SPfile?

Posted in Consultas | Print | 3 Comments »

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 »

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 ******************************************** 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 »

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 »

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;

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 ******************************************** EVENTOS INDESEABLES
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 ESTADISTICAS DE SEGMENTOS
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(cum_wait_time) cum_wait_time
FROM gv$enqueue_stat
GROUP BY eq_type
HAVING SUM(cum_wait_time) > 100000
ORDER BY 3 DESC;

SPOOL OFF

Posted in Monitoreo, Scripts | Print | No Comments »

11. April 2008 by fmunoz.

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;

Posted in Monitoreo, Scripts | Print | No Comments »

11. April 2008 by fmunoz.

PROMPT
PROMPT ******************************************** HOT BLOCKS **********************************************************************************************

SELECT inst_id, CHILD, ADDR, GETS, MISSES, SLEEPS
FROM (select inst_id, CHILD#  CHILD, ADDR, GETS, MISSES, SLEEPS
      from gv$latch_children
      where name = ‘cache buffers chains’
      order by 6 desc)
WHERE ROWNUM < 11;

select /*+ RULE */
  e.owner||’.'||e.segment_name  segmento, e.extent_id  extent#, x.dbablk - e.block_id + 1  block#, x.tch hits, l.child#
from
  sys.gv$latch_children  l, sys.x$bh  x, sys.dba_extents  e
where
  x.hladdr  IN (SELECT ADDR
                FROM (select ADDR, SLEEPS
                      from gv$latch_children
                      where name = ‘cache buffers chains’
                      order by 2 desc)
                WHERE ROWNUM < 11) and
  e.file_id = x.file# and
  x.hladdr = l.addr and
  x.dbablk between e.block_id and e.block_id + e.blocks -1
  order by x.tch desc ;

Posted in Tuning, Scripts | Print | 1 Comment »

11. April 2008 by fmunoz.

PROMPT ******************************************** HOT RESOURCES

SET FEEDBACK OFF

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

SPOOL hot_&sid..txt

SELECT inst_id, sid, event, p1 file_number, p2 block_number, p3 razon
FROM gv$session_wait
WHERE event LIKE ‘buffer busy%’;

SELECT inst_id, w.sid, w.event, p3 razon, w.wait_time, w.seconds_in_wait, w.state, e.owner||’.'||e.segment_name segmento, e.segment_type tipo, e.partition_name
FROM gv$session_wait w, dba_extents e
WHERE w.event LIKE ‘buffer busy%’ AND
      w.p1 = e.file_id AND
      w.p2 BETWEEN e.block_id AND e.block_id + e.blocks - 1;
     
SELECT inst_id, w.sid, w.event, p3 razon, w.wait_time, w.seconds_in_wait, w.state, s.owner||’.'||s.segment_name segmento, s.segment_type tipo, s.freelists
FROM gv$session_wait w, dba_segments s
WHERE w.event LIKE ‘buffer busy%’ AND
      w.p1 = s.header_file AND
      w.p2 = s.header_block;

SPOOL OFF

SET FEEDBACK ON

Posted in Tuning, Scripts | Print | No Comments »