Metricas x Usuario

METRICAS X USUARIO

 

CACHE HIT RATIO

SELECT   Round(((Sum(Decode(a.name, 'consistent gets', a.value, 0)) +

         Sum(Decode(a.name, 'db block gets', a.value, 0)) -

         Sum(Decode(a.name, 'physical reads', a.value, 0))  )/

           (Sum(Decode(a.name, 'consistent gets', a.value, 0)) +

             Sum(Decode(a.name, 'db block gets', a.value, 0))))

             *100,2) "Hit Ratio %"

FROM   v$sysstat a

 

COMMITS X HORA

select sum(value) sum from v$sysstat where name like 'user commits' or name like 'user rollbacks';

 

CURSORES OPEN CLUSTER

 

select count(*) from gv$open_cursor

where user_name='SYSADM'

and cursor_type='OPEN'

 

ESPACIO USADO AREA DE RECUPERACION

SELECT round(space_used/1024/1024/1024) AS "Space Used (Gb)" FROM v$recovery_file_dest

 

MAXIMO TIEMPO ESPERA CONCURRENCIA

select max(CONCURRENCY_WAIT_TIME) from gv$sql

 

MAXIMO USUARIOS EJECUCION STATEMENT

select max(users_opening) from gv$sql

 

NUMERO DE SESIONES $ PROCESOS ACTIVOS

SELECT count(*)

FROM   gv$session s,

       gv$process p

WHERE  s.paddr  = p.addr

AND    s.status = 'ACTIVE'

ORDER BY s.username, s.osuser

 

NUMERO DE SESIONES $ PROCESOS INACTIVOS

 

SELECT count(*)

FROM   gv$session s,

       gv$process p

WHERE  s.paddr  = p.addr

AND    s.status = 'INACTIVE'

ORDER BY s.username, s.osuser

 

NUMERO DE SESIONES & PROCESOS TOTALES

 

SELECT count(*)

FROM   gv$session s,

       gv$process p

WHERE  s.paddr  = p.addr

ORDER BY s.username, s.osuser

 

NUMERO DE SESIONES CLUSTER

select count(*) from gv$session

 

NUMERO DE SESIONES ESPERA

select count(*) from v$session_wait

 

NUMERO DE DESIONES ESPERA > 60s

select count(*) from v$session_wait

where seconds_in_wait > 60

 

NUMERO DE REGISTROS > TABLA

select max(num_rows) from dba_tables

 

OBJETOS INVALIDOS

 

select count(*) from dba_objects

where status = 'INVALID'

 

TIEMPO SERVICIO PROMEDIO

select ( s.service_seconds + w.wait_seconds) / user_calls service_time

from (

 select

 (sum(value)/100) / ( 3600 * 24 ) service_seconds

 from v$sysstat

 where upper(name) like '%CPU%'

 and class in (1,64) -- User and SQL

) s,

(

 select

 sum(time_waited/100) wait_seconds

 from v$system_event

 where event not like '%timer'

 and event not like '%from client'

) w,

(

 select

 sum(value) user_calls

 from v$sysstat

 where name like 'user%'

) u

 

TOTAL REDO X DIA

select round(sum(blocks*block_size)/1024/1024/1024,2) Gb from v$archived_log

where completion_time between sysdate-1 and sysdate

 

TOTAL REDO X MES

select round(sum(blocks*block_size)/1024/1024/1024,2) Gb from v$archived_log

where completion_time between sysdate-31 and sysdate

 

TRANSACCIONES X SEGUNDO

select round(sum(s.value / (86400 * (SYSDATE - startup_time))),3) "TPS" from v$sysstat s ,v$instance i where s.NAME in ('user commits','transaction rollbacks');

 

TIEMPO CURSORES > 1s

select count(*) from gv$sql

where elapsed_time > 1000000

 

TIEMPO CURSORES > 2s

select count(*) from gv$sql

where elapsed_time > 2000000

 

TIEMPO RESPUESTA > 10s

select count(*) from gv$sql

where cpu_time > 10000000

 

Tiempo Respuesta > 1m

select count(*) from gv$sql

where cpu_time > 60000000

 

Tiempo Respuesta > 1s

 

select count(*) from gv$sql

where cpu_time > 1000000

 

Total Queries Cluster

 

select count(*) from gv$sql;

 

Total Queries Invalid

 

select count(*) from gv$sql where object_status <> 'VALID'