Stats Oracle SYSTEME et schema

Voir si les stats Oracle sont calculées sur mon schéma : 

set lines 150
set pages 3000
col TABLE_NAME for a30
col INDEX_NAME for a30
col COLUMN_NAME for a30

define schema=&schema

spool stats_&schema

— Voir les stats sur mes tables
select table_name, num_rows, BLOCKS, STALE_STATS from DBA_TAB_STATISTICS where owner = ‘&schema’ order by table_name;

–Voir les stats sur mes indexes
select INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS, LAST_ANALYZED from DBA_IND_STATISTICS where OWNER = ‘&schema’ order by TABLE_NAME, INDEX_NAME;

–Voir les stats sur mes colonnes
select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, DENSITY, LAST_ANALYZED, AVG_COL_LEN from DBA_TAB_COL_STATISTICS where owner = ‘&schema’ order by TABLE_NAME, COLUMN_NAME;

spool off
exit

— Mise en place des statistiques systèmes et os (sans mode ‘concurrent true’ !)
execute sys.dbms_stats.gather_system_stats(‘Start’);
execute sys.dbms_stats.gather_system_stats(‘Stop’);
execute sys.dbms_stats.gather_system_stats(‘interval’,interval => 15); # durée de 60 mins
execute sys.dbms_stats.delete_system_stats();

— Vérifier si les statistiques sont calculées
select pname, pval1 from sys.aux_stats$ where sname = ‘SYSSTATS_MAIN’ order by 1;

Autre source d’infos : 

https://fadace.developpez.com/oracle/statistiques-systeme/

http://dbaoraclesql.canalblog.com/archives/2019/03/30/37218417.html