Script - Vsesstat_analisis.sql

Script para localizar sesiones problemáticas , (http://www.dba-oracle.com/oracle10g_tuning/t_sql_top_sessions.htm):

En este caso, la ordenación es por el número de "user rollbacks", para identificar conexiones de servidores de aplicación con posibles problemas de gestión de transacciones.

select * from
(select a.sid sid,
       SUBSTR(b.USERNAME,1,15) USUARIO,
       SUBSTR(b.OSUSER,1,10) OSUSER,
       SUBSTR(b.STATUS,1,10) ESTADO,
--       SUBSTR(b.module,1,20) modulo,
--     SUBSTR(b.MACHINE,1,30) MAQUINA,
   to_char(logon_time,'dd-mon-yy hh:mi:ss pm') logon_time,
   sysdate fecha_actual,
   sum(decode(c.name,'user commits',value,0)) commits,
   sum(decode(c.name,'user rollbacks',value,0)) rollbacks,
   sum(decode(c.name,'execute count',value,0)) executions,
  (sum(decode(c.name,'physical reads  ',value,0)) +
   sum(decode(c.name,'physical writes',value,0)) +
   sum(decode(c.name,'physical writes direct',value,0)) +
   sum(decode(c.name,'physical writes direct (lob)',value,0))+
   sum(decode(c.name,'physical reads  direct (lob)',value,0)) +
   sum(decode(c.name,'physical reads   direct',value,0)))
   total_physical_io,
  (sum(decode(c.name,'db block gets',value,0)) +
   sum(decode(c.name,'db block changes',value,0)) +
   sum(decode(c.name,'consistent changes',value,0)) +
   sum(decode(c.name,'consistent gets ',value,0)) )
   total_logical_io,
  (sum(decode(c.name,'session pga memory',value,0))+
   sum(decode(c.name,'session uga memory',value,0)) )
   total_memory_usage,
   sum(decode(c.name,'parse count (total)',value,0)) parses,
   sum(decode(c.name,'cpu used by this session',value,0))
   total_cpu,
   sum(decode(c.name,'parse time cpu',value,0)) parse_cpu,
   sum(decode(c.name,'recursive cpu usage',value,0))
     recursive_cpu,
   sum(decode(c.name,'cpu used by this session',value,0)) -
   sum(decode(c.name,'parse time cpu',value,0)) -
   sum(decode(c.name,'recursive cpu usage',value,0))
     other_cpu,
   sum(decode(c.name,'sorts (disk)',value,0)) disk_sorts,
   sum(decode(c.name,'sorts (memory)',value,0)) memory_sorts,
   sum(decode(c.name,'sorts (rows)',value,0)) rows_sorted
from sys.v_$sesstat  a,
     sys.v_$session b,
     sys.v_$statname c
where
a.sid=b.sid and
a.statistic#=c.statistic# and
      c.NAME in ('physical reads  ',
                 'physical writes',
                 'physical writes direct',
                 'physical reads   direct',
                 'physical writes direct (lob)',
                 'physical reads   direct (lob)',
                 'db block gets',
                 'db block changes',
                 'consistent changes',
                 'consistent gets ',
                 'session pga memory',
                 'session uga memory',
                 'parse count (total)',
                 'CPU used by this session',
                 'parse time cpu',
                 'recursive cpu usage',
                 'sorts (disk)',
                 'sorts (memory)',
                 'sorts (rows)',
                 'user commits',
                 'user rollbacks',
                 'execute count'
)
group by a.sid ,
       SUBSTR(b.USERNAME,1,15) ,
       SUBSTR(b.OSUSER,1,10) ,
       SUBSTR(b.STATUS,1,10) ,
       SUBSTR(b.SERVER,1,7) ,
       SUBSTR(b.module,1,20) ,
     SUBSTR(b.MACHINE,1,30) ,
   to_char(logon_time,'dd-mon-yy hh:mi:ss pm')
order by rollbacks desc)
where rownum < 14
/

MySql Cluster sobre infraestructura Grid de ORACLE

Uso de Oracle Grid Infraestructure 11g para dar servicio a bases de datos MySQL en modo Cluster:

MySql Cluster sobre infraestructura Grid de ORACLE