Pour un bon fonctionnement de l’optimiseur d’oracle le référentiel de statistiques doit être à jour. c’est une sorte de cartographie du contenu de la base et du comportement des instances.
L’optimiseur est ainsi capable de déterminer la stratégie d’accès aux données la plus adaptée et la plus performante, un peu comme un GPS qui doit connaitre le réseau routier et les distances entre les villes afin de déterminer l’itinéraire le plus court ou le plus rapide entre deux points.
Il est fortement recommandé d’avoir un GPS à jour si on veut avoir les dernières modification du réseau, c’est le même principe chez oracle. ainsi les statistiques sont AUTOMATIQUE.
On peut avoir besoin d’effectuer des statistique manuel :
Trace sur les JOBS de Statistiques:
————————————
SET VERIFY ON
SET FEEDBACK ON
SET TERMOUT ON
SET HEADSEP ON
SET HEADING ON
SET LINESIZE 10000
SET PAGESIZE 10000
SET LINES 500
SET TRIMSPOOL OFF
col owner format a10
col JOB_NAME format a25
col REPEAT_INTERVAL format a30
SELECT OWNER, JOB_NAME, ENABLED, STATE,
TO_CHAR(LAST_START_DATE, ‘dd/mm/yyyy hh24:mi:ss’) LAST_START_DATE,
TO_CHAR(START_DATE, ‘dd/mm/yyyy hh24:mi:ss’) START_DATE,
TO_CHAR(END_DATE, ‘dd/mm/yyyy hh24:mi:ss’) END_DATE,
RESTARTABLE, REPEAT_INTERVAL, RUN_COUNT
FROM DBA_SCHEDULER_JOBS
WHERE job_name LIKE ‘%STATS%’;
Statistique simple sur une table en particulier :
—————————————————
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>’BDD’,TABNAME=>’INSTANTDECIMALSERIE_DATA’,CASCADE=>TRUE);
Statistique simple sur un index en particulier :
———————————————–
EXECUTE DBMS_STATS.GATHER_INDEX_STATS(ownname => ‘SCOTT’, indname => ‘EMP_IDX1’);
Statistique plus complet sur un schémas avec options:
—————————————————-
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => ‘BDD’, CASCADE=>TRUE, OPTIONS => ‘GATHER’, METHOD_OPT=> ‘FOR ALL COLUMNS SIZE AUTO’);
Statistique intégrale sur une instance:
—————————————
EXECUTE DBMS_STATS.GATHER_DATABASE_STATS;
Statistique intégrale sur un schémas:
————————————
EXEC DBMS_UTILITY.ANALYZE_SCHEMA(‘BDD’,’COMPUTE’);
Statistique à 100% pour un schémas:
———————————-
EXEC dbms_stats.gather_schema_stats (ownname=>’BDD’, estimate_percent=>100, granularity=>’ALL’,
CASCADE=>TRUE, method_opt=>’FOR ALL COLUMNS SIZE SKEWONLY’, degree=>8, options=>’GATHER’);
Script permettant de générer le calcul des statistiques pour l’ensemble des tables d’un schema:
———————————————————————————————-
spool E:\BACKUP\CALCUL_STATISTIQUES.TXT
DECLARE
CURSOR C1 IS
SELECT *
FROM DBA_TABLES
WHERE OWNER IN (‘BDD’, ‘SCOTT’)
ORDER BY OWNER
, TABLE_NAME;
BEGIN
DBMS_OUTPUT.PUT_LINE (TO_CHAR (SYSDATE, ‘dd/mm/yyyy HH24:MI:SS’));
FOR S1 IN C1 LOOP
DBMS_OUTPUT.PUT_LINE ( S1.OWNER
|| ‘ ==> Table : ‘
|| S1.TABLE_NAME);
DBMS_STATS.gather_table_stats (
OWNNAME => s1.OWNER
, TABNAME => S1.TABLE_NAME
, CASCADE => TRUE
, METHOD_OPT => ‘FOR ALL COLUMNS FOR ALL INDEXED COLUMNS SIZE AUTO’
);
END LOOP;
DBMS_OUTPUT.PUT_LINE (TO_CHAR (SYSDATE, ‘dd/mm/yyyy HH24:MI:SS’));
COMMIT;
END;
/
spool off
Pour suppression des statistiques:
———————————
EXECUTE dbms_stats.delete_schema_stats(‘BDD’,FORCE=>TRUE)
pour calcul des statistiques du système:
—————————————
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(‘INTERVAL’,60);
END;
/
ou via un start & stop
BEGIN
dbms_stats.gather_system_stats(‘start’);
END;
/
–suivi plus tard (quelque minutes ou heures)
BEGIN
dbms_stats.gather_system_stats(‘stop’);
END;
/
Voir le résultat des statistiques systèmes :
——————————————-
SELECT * FROM sys.aux_stats$;
Historique des analyses:
————————
PROMPT NB tables analysées depuis 15 jours
select count(*) from dba_tables where last_analyzed > sysdate-15;
PROMPT NB tables analysées depuis 7 jours
select count(*) from dba_tables where last_analyzed > sysdate-7;
PROMPT NB tables analysées depuis 1 jours
select count(*) from dba_tables where last_analyzed > sysdate-2;
PROMPT Stat la plus recente :
select to_char(max(last_analyzed),’YYYY/MM/DD HH24:MI:SS’) from dba_tables;
Donne les infos des stats sur la table donnée en paramètre:
———————————————————-
PROMPT Donne les infos des stats sur la table donnee
select owner, table_name, NUM_ROWS, SAMPLE_SIZE, SAMPLE_SIZE*100/NUM_ROWS as sample_pct, LAST_ANALYZED, STATTYPE_LOCKED, STALE_STATS
from dba_tab_statistics
where owner = upper(‘BDLT_PR9’)
and table_name = upper(‘INSTANTDECIMALSERIE_DATA’);
OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE SAMPLE_PCT LAST_ANA STATT STA
———- —————————— ———- ———– ———- ——– —– —
BDLT_PR9 INSTANTDECIMALSERIE_DATA 501712436 501712436 100 19/03/15 NO
1 ligne sÚlectionnÚe.
OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE SAMPLE_PCT LAST_ANA STATT STA
———- —————————— ———- ———– ———- ——– —– —
BDLT_PR8 INSTANTDECIMALSERIE_DATA 291219526 291219526 100 26/03/15 NO
1 ligne sÚlectionnÚe.
col LOW_VALUE for A9
col HIGH_VALUE for A9
select COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, NUM_NULLS, DENSITY, NUM_BUCKETS, SAMPLE_SIZE, HISTOGRAM , LAST_ANALYZED
from DBA_TAB_COL_STATISTICS
where owner = upper(‘BDLT_PR8’)
and table_name = upper(‘INSTANTDECIMALSERIE_DATA’)
order by COLUMN_NAME;
BDLT_PR8
INSTANTDECIMALSERIE_DATA
Donne les infos des stats sur les index de la table donnée en paramètre:
———————————————————————–
PROMPT Donne les infos des stats des indexes sur la table donnee
set lines 220
col OWNER for A5
col INDEX_NAME for A12
col TABLE_OWNER for A10
col TABLE_NAME for A10
col PARTITION_NAME for A8
select index_name, table_owner as OWNER, table_name , partition_name, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR,
NUM_ROWS, SAMPLE_SIZE, to_char(LAST_ANALYZED,’DD/MM/YY’), GLOBAL_STATS, USER_STATS, STATTYPE_LOCKED, STALE_STATS
from DBA_IND_STATISTICS
where table_owner= upper(‘&1’)
and table_name = upper(‘&2’);