Monitorear el estado de las estadisticas de los segmentos referenciados en una sesión

Cualquiera que haya trabajado con bases de datos Oracle habrá experimentado alguna vez problemas de performance. La gran mayoria de estas degradaciones en los tiempos de los procesos o aplicaciones se deben a falta o desactualización de las estadísticas de los segmentos referenciados en las sentencias ejecutadas. Para los que cada tanto tenemos la tarea de analizar problemas de rendimiento, nos resulta necesario partir por identificar la sesión o sesiones instanciadas por el proceso o aplicacion con el problema. En el caso de procesos batch los operadores nos suelen suministrar los sid de las sesiones para poder generar una traza de ejecución o monitoreo online. El siguiente script muestra los segmentos con estadísticas STALE o EMPTY dado un sid de sesión:

set line 150
set pagesize 9999
set verify off

col owner format a15
col segment_name format a30
col type format a5

ACCEPT sid PROMPT "Ingrese el SID a analizar: "


select sq.sql_id,
       sq.object_type type,
       st.owner owner,
       st.table_name segment_name,
       st.partition_name partition_name ,
       st.subpartition_name subpartition_name,
       st.last_analyzed last_analyzed 
from v$session se,
     v$sql_plan sq,
     dba_tab_statistics st
where se.sql_id = sq.sql_id
  and se.sid = &sid
  and sq.OBJECT_OWNER = st.owner 
  and sq.OBJECT_NAME = st.table_name 
  and sq.object_type = 'TABLE'
  and sq.object_owner not in ('SYS','SYSTEM')
  and nvl(st.stale_stats,'YES') = 'YES'  
union all
select sq.sql_id,
       sq.object_type type,
       si.owner owner,
       si.table_name segment_name,
       si.partition_name partition_name ,
       si.subpartition_name subpartition_name,
       si.last_analyzed last_analyzed 
from v$session se,
     v$sql_plan sq,
     dba_ind_statistics si
where se.sql_id = sq.sql_id
  and se.sid = &sid
  and sq.OBJECT_OWNER = si.owner 
  and sq.OBJECT_NAME = si.index_name 
  and sq.object_type = 'INDEX'
  and sq.object_owner not in ('SYS','SYSTEM')
  and nvl(si.stale_stats,'YES') = 'YES'  
/

set verify on

Si quisieramos ver todas los segmentos con estadisticas nulas o viejas de todas las tablas referenciadas actualmente podemos usar la siguiente variante mas generica del script anterior:

set line 150
set pagesize 9999

col owner format a15
col segment_name format a30
col type format a5

select sq.sql_id,
       sq.object_type type,
       st.owner owner,
       st.table_name segment_name,
       st.partition_name partition_name ,
       st.subpartition_name subpartition_name,
       st.last_analyzed last_analyzed 
from v$sql_plan sq,
     dba_tab_statistics st
where sq.OBJECT_OWNER = st.owner 
  and sq.OBJECT_NAME = st.table_name 
  and sq.object_type = 'TABLE'
  and sq.object_owner not in ('SYS','SYSTEM')
  and nvl(st.stale_stats,'YES') = 'YES'  
union all
select sq.sql_id,
       sq.object_type type,
       si.owner owner,
       si.table_name segment_name,
       si.partition_name partition_name ,
       si.subpartition_name subpartition_name,
       si.last_analyzed last_analyzed 
from v$sql_plan sq,
     dba_ind_statistics si
where sq.OBJECT_OWNER = si.owner 
  and sq.OBJECT_NAME = si.index_name 
  and sq.object_type = 'INDEX'
  and sq.object_owner not in ('SYS','SYSTEM')
  and nvl(si.stale_stats,'YES') = 'YES'  
order by sql_id
/