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'
