Ok

En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.

Stats missing

Pour afficher les stats sur les tables :

set pages 200

col index_owner form a10
col TABLE_NAME for a40
col table_owner form a10
col owner form a10

spool checkstat.lst

PROMPT Regular Tables

select owner,table_name,last_analyzed, global_stats
from dba_tables
where owner not in ('SYS','SYSTEM')
order by owner,table_name
/

 

Pour les tables avec partitions :

set pages 200

col index_owner form a10
col TABLE_NAME for a40
col table_owner form a10
col owner form a10

spool checkstat.lst

PROMPT Partitioned Tables

select table_owner, table_name, partition_name, last_analyzed, global_stats
from dba_tab_partitions
where table_owner not in ('SYS','SYSTEM')
order by table_owner,table_name, partition_name
/

Pour afficher les stats sur les indexes ;

set pages 500
set lines 200
col index_owner form a10
col index_name for a40
col table_owner form a10
col owner form a10

select owner, index_name, last_analyzed, global_stats
from dba_indexes
where owner not in ('SYS','SYSTEM')
order by 3
/

Pour afficher les stats manquant avec un taux de % de 10 :

select m.TABLE_OWNER,
m.TABLE_NAME,
m.INSERTS,
m.UPDATES,
m.DELETES,
m.TRUNCATED,
m.TIMESTAMP as LAST_MODIFIED,
round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
t.num_rows as last_known_rows_number,
t.last_analyzed
From dba_tab_modifications m,
dba_tables t
where m.table_owner=t.owner
and m.table_name=t.table_name
and table_owner not in ('SYS','SYSTEM')
and ((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
order by timestamp desc;

 

Écrire un commentaire

Optionnel