STATISTIQUES : effet du truncate

Une question que je me pose souvent : le TRUNCATE invalide-t'il les statistiques d'une table ? Réponse : non.

Démonstration :
 

  

SQL> CREATE TABLE toto (a number);

Table created.

SQL> INSERT INTO toto VALUES (999);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT blocks, avg_row_len FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TOTO';

    BLOCKS AVG_ROW_LEN
---------- -----------


SQL> exec dbms_stats.gather_table_stats(USER,'TOTO');

PL/SQL procedure successfully completed.

SQL> SELECT blocks, avg_row_len FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TOTO';

    BLOCKS AVG_ROW_LEN
---------- -----------
         5           4

SQL> TRUNCATE TABLE toto;

Table truncated.

SQL> SELECT blocks, avg_row_len FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TOTO';

    BLOCKS AVG_ROW_LEN

---------- -----------

         5           4

SQL> exec dbms_stats.gather_table_stats(USER,'TOTO');

PL/SQL procedure successfully completed.

SQL> SELECT blocks, avg_row_len FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TOTO';

    BLOCKS AVG_ROW_LEN
---------- -----------
         0           0

UPDATE : critère supplémentaire dans la table source

On souhaite mettre à jour une table CIB à partir d'une table SRC sur un critère de jointure conditionné par un prédicat supplémentaire.

La difficulté est due au fait que la table à mettre à jour contient des lignes qu'il ne faudra pas mettre à jour et que ce critère est indiqué dans la table source (src.c='NON')

Préparation :

SQL> CREATE TABLE src (a NUMBER, b NUMBER, c CHAR(3));

Table created.

SQL> CREATE TABLE cib (a NUMBER, b NUMBER);

Table created.

SQL> INSERT INTO src VALUES (1,11,'OUI');

1 row created.

SQL> INSERT INTO src VALUES (1,12,'NON');

1 row created.

SQL> INSERT INTO src VALUES (2,22,'NON');

1 row created.

SQL> INSERT INTO cib VALUES (1,0);

1 row created.

SQL> INSERT INTO cib VALUES (2,0);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM src;

         A          B C
---------- ---------- ---
         1         11 OUI
         1         12 NON
         2         22 NON

SQL> SELECT * FROM cib;

         A          B
---------- ----------
         1          0
         2          0

Exécution :

Rappel : on souhaite mettre à jour la table cib à partir des valeurs de la table src uniquement lorsque la cle de cib est présente dans src et que le champ c de src vaut 'OUI'

SQL> UPDATE cib
  2  SET cib.b = (
  3     SELECT src.b
  4     FROM src
  5     WHERE src.a=cib.a
  6  )
  7  ;
        SELECT src.b
        *
ERROR at line 3:
 
ORA-01427: single-row subquery returns more than one row
La table source renvoie plus d'une valeur pour une valeur de clé donnée
 

SQL> UPDATE cib
  2  SET cib.b = (
  3     SELECT src.b
  4     FROM src
  5     WHERE src.a=cib.a
  6  )
  7  WHERE src.c='OUI'
  8  ;
WHERE src.c='OUI'
      *
ERROR at line 7:
ORA-00904: "SRC"."C": invalid identifier

Le prédicat est mal placé

SQL> UPDATE cib
  2  SET cib.b = (
  3     SELECT src.b
  4     FROM src
  5     WHERE src.a=cib.a
  6     AND src.c='OUI')
  7  ;

2 rows updated.

SQL> SELECT * FROM cib;

         A          B
---------- ----------
         1         11
         2

Les valeurs des clés sans correspondance sont mises à jour par null

SQL> ROLLBACK;

Rollback complete.

SQL> UPDATE cib
  2  SET cib.b = (
  3     SELECT src.b
  4     FROM src
  5     WHERE src.a=cib.a
  6     AND src.c='OUI')
  7  WHERE EXISTS (
  8     SELECT 1
  9     FROM src
 10     WHERE src.a=cib.a)
 11  )
 12  ;

2 rows updated.

SQL> SELECT * FROM cib;

         A          B
---------- ----------
         1         11
         2

La condition where exists est inefficace, des valeurs des clés sans correspondance sont mises à jour par null

SQL> ROLLBACK;

Rollback complete.

SQL> UPDATE cib
  2  SET cib.b = (
  3     SELECT src.b
  4     FROM src
  5     WHERE src.a=cib.a
  6     AND src.c='OUI')
  7  WHERE EXISTS (
  8     SELECT 1
  9     FROM src
 10     WHERE src.a=cib.a
 11     AND src.c='OUI'
 12  )
 13  ;

1 row updated.

SQL> SELECT * FROM cib;

         A          B
---------- ----------
         1         11
         2          0 

 
Écriture correcte, on a le résultat souhaité

SQL> ROLLBACK;

Rollback complete.

Écriture équivalente sous forme de MERGE

SQL> MERGE INTO cib t_c
  2  USING (
  3     SELECT *
  4     FROM src
  5     WHERE c='OUI'
  6  ) t_s
  7  ON (t_c.a=t_s.a)
  8  WHEN MATCHED THEN
  9  UPDATE SET t_c.b=t_s.b
 10  ;

1 row merged.

SQL> SELECT * FROM cib;

         A          B
---------- ----------
         1         11
         2          0

SQL> ROLLBACK;

Rollback complete.

UPDATE : vue de jointure in-line

Update de vue de jointure "in-line" et index unique

 

SQL> create table SRC (a number, b number);

Table created.

SQL> create table CIB (a number, b number);

Table created.

SQL> insert into SRC values (1,11);

1 row created.

SQL> insert into SRC values (2,12);

1 row created.

SQL> insert into CIB values (1,0);

1 row created.

SQL> insert into CIB values (2,0);

1 row created.

SQL> select * from SRC;

         A          B
---------- ----------
         1         11
         2         12

SQL> select * from CIB;

         A          B
---------- ----------
         1          0
         2          0

SQL> update (
  2     select SRC.b srcb, CIB.b cibb
  3     from SRC, CIB
  4     where SRC.a=CIB.a
  5  )
  6  set cibb = srcb
  7  ;
set cibb = srcb
    *
ERROR at line 6:

ORA-01779: cannot modify a column which maps to a non key-preserved table

Un index unique doit être créé sur la clé de la table source

SQL> create unique index IU_SRC on SRC (a);

Index created.

SQL> update (
  2     select SRC.b srcb, CIB.b cibb
  3     from SRC, CIB
  4     where SRC.a=CIB.a
  5  )
  6  set cibb = srcb
  7  ;

2 rows updated.

SQL> select * from SRC;

         A          B
---------- ----------
         1         11
         2         12

SQL> select * from CIB;

         A          B
---------- ----------
         1         11
         2         12

MERGE : unicité des clés de la table source

Dans un MERGE, la table appelée en source du MERGE (using) ne doit pas avoir de doublons sur sa clé de jointure.

 

SQL> CREATE TABLE nwprices (
  2     article_name VARCHAR2(20),
  3     price NUMBER,
  4     last_price CHAR(1)
  5  );

Table created.

SQL> INSERT INTO articles
  2     VALUES ('CABLE 13mm CUIVRE',15.50);

1 row created.

SQL> INSERT INTO articles
  2     VALUES ('CABLE 25mm CUIVRE',19.25);

1 row created.

SQL> INSERT INTO nwprices
  2     VALUES ('CABLE 13mm CUIVRE',15.80,'O');

1 row created.

SQL> INSERT INTO nwprices
  2     VALUES ('CABLE 25mm CUIVRE',21.05,'N');

1 row created.

SQL> INSERT INTO nwprices
  2     VALUES ('CABLE 25mm CUIVRE',21.45,'O');

1 row created.

SQL> COL price FOR 99.00
SQL> SELECT * FROM articles;

ARTICLE_NAME          PRICE
-------------------- ------
CABLE 13mm CUIVRE     15.50
CABLE 25mm CUIVRE     19.25

SQL> SELECT * FROM nwprices;

ARTICLE_NAME          PRICE L
-------------------- ------ -
CABLE 13mm CUIVRE     15.80 O
CABLE 25mm CUIVRE     21.05 N
CABLE 25mm CUIVRE     21.45 O

SQL> MERGE INTO articles ar
  2  USING nwprices nw
  3  ON (ar.article_name=nw.article_name)
  4  WHEN MATCHED
  5  THEN UPDATE
  6  SET ar.price=nw.price
  7  ;
USING nwprices nw
      *
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables


SQL> MERGE INTO articles ar
  2  USING (
  3     SELECT * FROM nwprices
  4     WHERE last_price='O') nw
  5  ON (ar.article_name=nw.article_name)
  6  WHEN MATCHED
  7  THEN UPDATE
  8  SET ar.price=nw.price
  9  ;

2 rows merged.

SQL> SELECT * FROM articles;

ARTICLE_NAME          PRICE
-------------------- ------
CABLE 13mm CUIVRE     15.80
CABLE 25mm CUIVRE     21.45

SQL>

OUTER JOIN : syntaxe ANSI et syntaxe Oracle

Ci-dessous un aide-mémoire pour se souvenir de l'écriture des jointures externes avec les deux syntaxes autorisées dans Oracle.

SQL> CREATE TABLE pays (id_pays NUMBER, pays VARCHAR2(10));

Table created.

SQL> CREATE TABLE villes (
  2  id_ville NUMBER,
  3  ville VARCHAR2(10),
  4  id_pays NUMBER);

Table created.

SQL> INSERT INTO pays VALUES (1, 'USA');

1 row created.

SQL> INSERT INTO pays VALUES (2, 'RUSSIE');

1 row created.

SQL> INSERT INTO pays VALUES (3, 'FRANCE');

1 row created.

SQL> INSERT INTO villes VALUES (1, 'DENVER', 1);

1 row created.

SQL> INSERT INTO villes VALUES (2, 'CHICAGO', 1);

1 row created.

SQL> INSERT INTO villes VALUES (3, 'MOSCOU', 2);

1 row created.

SQL> INSERT INTO villes VALUES (4, 'DJAKARTA', 5);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> -- LEFT OUTER JOIN
SQL> SELECT pays, ville
  2  FROM pays p LEFT OUTER JOIN villes v
  3  ON p.id_pays=v.id_pays
  4  ORDER BY 1,2
  5  ;

PAYS       VILLE
---------- ----------
FRANCE
RUSSIE     MOSCOU
USA        CHICAGO
USA        DENVER

SQL> SELECT pays, ville
  2  FROM pays p, villes v
  3  WHERE p.id_pays=v.id_pays(+)
  4  ORDER BY 1,2
  5  ;

PAYS       VILLE
---------- ----------
FRANCE
RUSSIE     MOSCOU
USA        CHICAGO
USA        DENVER

SQL> -- RIGHT OUTER JOIN
SQL> SELECT pays, ville
  2  FROM pays p RIGHT OUTER JOIN villes v
  3  ON p.id_pays=v.id_pays
  4  ORDER BY 1,2
  5  ;

PAYS       VILLE
---------- ----------
RUSSIE     MOSCOU
USA        CHICAGO
USA        DENVER
           DJAKARTA

SQL> SELECT pays, ville
  2  FROM pays p, villes v
  3  WHERE p.id_pays(+)=v.id_pays
  4  ORDER BY 1,2
  5  ;

PAYS       VILLE
---------- ----------
RUSSIE     MOSCOU
USA        CHICAGO
USA        DENVER
           DJAKARTA

SQL> -- FULL OUTER JOIN
SQL> SELECT pays, ville
  2  FROM pays p FULL OUTER JOIN villes v
  3  ON p.id_pays=v.id_pays
  4  ORDER BY 1,2
  5  ;

PAYS       VILLE
---------- ----------
FRANCE
RUSSIE     MOSCOU
USA        CHICAGO
USA        DENVER
           DJAKARTA

SQL> SELECT pays, ville
  2  FROM pays p, villes v
  3  WHERE p.id_pays=v.id_pays(+)
  4  UNION
  5  SELECT pays, ville
  6  FROM pays p, villes v
  7  WHERE p.id_pays(+)=v.id_pays
  8  ORDER BY 1,2
  9  ;

PAYS       VILLE
---------- ----------
FRANCE
RUSSIE     MOSCOU
USA        CHICAGO
USA        DENVER
           DJAKARTA

BIND VARIABLE PEEKING : fonctionnement

Très bon article sur le peeking :

To peek or not to peek

 
Un autre excellent lien sur le bind variable peeking :

Flushing a cursor out of the Library Cache


Extrait de la doc Oracle 10gR2 :

From Chapter 13 of the Performance tuning Guide:
 
“13.4.1.2 Peeking of User-Defined Bind Variables
 
The query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE clause condition, as well as if literals have been used instead of bind variables. On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values.

When bind variables are used in a statement, it is assumed that cursor sharing is intended and that different invocations are supposed to use the same execution plan. If different invocations of the cursor would significantly benefit from different execution plans, then bind variables may have been used inappropriately in the SQL statement. Bind peeking works for a specific set of clients, not all clients.”

Oracle 11g :
 

Solution définitive aux problèmes de bind varable peeking en 11g avec "Adaptive Cursor Sharing"

Adaptive Cursor Sharing : explication 

STATISTIQUES : pending statistics 11g

Pending Statistics

Dans les versions d'Oracle antérieures à la 11g, les nouvelles statistiques de l'optimiseur étaient mises à disposition tout de suite. En 11g, c'est toujours le comportement par défaut, mais on a maintentant la possibilité de conserver les statistiques nouvellement générées dans un état d'attente jusqu'à ce que l'on choisisse de les publier.

Par défaut Les statistiques sont automatiquement publiées :

SQL> SELECT DBMS_STATS.get_prefs('PUBLISH') FROM dual;

DBMS_STATS.GET_PREFS('PUBLISH')
-------------------------------------------
TRUE

1 row selected.

Pour modifier le comportement par défaut, et ne pas publier automatiquement les futures mises à jour 

des statistiques de la table SCOTT.EMP :

EXEC DBMS_STATS.set_table_prefs('SCOTT','EMP','PUBLISH','false');

Pour publier automatiquement les futures mises à jour des statistiques de la table SCOTT.EMP :

EXEC DBMS_STATS.set_table_prefs('SCOTT','EMP','PUBLISH','true');

Vues listant les statistiques à publier :

SELECT * FROM [DBA|ALL|USER]_TAB_PENDING_STATS;
SELECT * FROM [DBA|ALL|USER]_IND_PENDING_STATS;

Pour publier toutes les statistiques en attente :

EXEC DBMS_STATS.publish_pending_stats(NULL, NULL);

Pour publier les statistiques en attente de l'objet SCOTT.EMP :

EXEC DBMS_STATS.publish_pending_stats('SCOTT','EMP');

Pour supprimer les statistiques en attente de l'objet SCOTT.EMP :

EXEC DBMS_STATS.delete_pending_stats('SCOTT','EMP');

L'optimiseur peut utiliser les statistiques en attente (pending) en positionnant le paramètre OPTIMIZER_USE_PENDING_STATISTICS à TRUE (FALSE par défaut), ce qui revient à supprimer la fonctionnalité de statistiques en atente.

Il est possible également de modifier le paramètre au niveau session, ce qui permet de tester le bénéfice des nouvelles statistiques dans une session avant de les publier.

ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS=TRUE;

Il est possible de transférer les statistiques en attente d'une base à une autre avec la procédure DBMS_STATS.EXPORT_PENDING_STATS

STATISTIQUES : extended statistics 11g

Statistiques étendues

Oracle peut désormais en 11g, recueillir de nouveaux types de statistiques, rendant la qualité de l'estimation de sélectivité bien meilleure dans les cas concernés.

Statistiques multi-colonnes

Lorsque dans la clause where d'une requête, figurent plusieurs prédicats mono-colonnes d'une même table, la corrélation entre ces colonne peut avoir des conséquences sur la sélectivité de ce groupe de colonnes.
Il est par conséquent très utile de pouvoir définir des groupes de colonnes sur lesquels on déterminera des statistiques de répartition de données afin de rendre plus optimal le choix de l'optimiseur dans ces cas.

Creating a Column Group

DECLARE
  cg_name varchar2(30);
BEGIN
  cg_name := dbms_stats.create_extended_stats(null,'customers', 
             '(cust_state_province,country_id)');
END;
/

Getting a Column Group

select sys.dbms_stats.show_extended_stats_name('sh','customers',
       '(cust_state_province,country_id)') col_group_name
from dual;

The output is similar to the following:

COL_GROUP_NAME
----------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM

Dropping a Column Group

exec dbms_stats.drop_extended_stats('sh','customers',

'(cust_state_province,country_id)');

Monitoring Column Groups

Use the dictionary table user_stat_extensions to obtain information about MultiColumn statistics:

Select extension_name, extension
from user_stat_extensions
where table_name='CUSTOMERS';

EXTENSION_NAME                EXTENSION
-----------------------------------------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_("CUST_STATE_PROVINCE","COUNTRY_ID")

Use the following query to find the number of distinct values and find whether a histogram has been created for a column group:

select e.extension col_group, t.num_distinct, t.histogram
  2  from user_stat_extensions e, user_tab_col_statistics t
  3  where e.extension_name=t.column_name
  4  and e.table_name=t.table_name
  5  and t.table_name='CUSTOMERS';

COL_GROUP                             NUM_DISTINCT      HISTOGRAM
-----------------------------------------------------------------
("COUNTRY_ID","CUST_STATE_PROVINCE")  145               FREQUENCY

Statistiques d'expressions

Lorsque une fonction est appliquée à une colonne dans la clause "where" (fonction(col1)=constant), l'optimiseur n'a aucun moyen de savoir comment cette fonction affecte la sélectivité de la colonne.
En recueillant les statistiques de cette expression, l'optimiseur obtient une valeur de sélectivité bien plus juste.
 

Creating Expression Statistics 

select
dbms_stats.create_extended_stats(null,'customers',

'(lower(cust_state_province))')
from dual;

Monitoring Expression Statistics

Select extension_name, extension
from user_stat_extensions
where table_name='CUSTOMERS';

EXTENSION_NAME                    EXTENSION
------------------------------------------------------------------
SYS_STUBPHJSBRKOIK9O2YV3W8HOUE    (LOWER("CUST_STATE_PROVINCE"))

Dropping Expression Statistics

exec dbms_stats.drop_extended_stats(null,'customers',

'(lower(country_id))'); 

Il est possible de calculer directement les statistiques sur une extension (groupe ou fonction) de colonne(s) sans avoir à créer cette extension explicitement, celle-ci l'est implicitement et demeure dans la liste des extension.
 

Gathering Extended Statistics 
 
exec dbms_stats.gather_table_stats('sh','customers',method_opt =>
'for all columns size skewonly
for columns (cust_state_province,country_id) size skewonly');

et
 

exec dbms_stats.gather_table_stats('sh','customers', method_opt =>
'for all columns size skewonly
 for columns (lower(cust_state_province)) size skewonly');


Nb : skewonly indique à Oracle de ne calculer des histogrammes que pour les colonnes qui ont une répartition très inégale.

STATISTIQUES : lock statistics

Le package DBMS_STATS offre la possibilité des verrouiller les statistiques d'une table ou de toutes les tables d'un schéma.

Ceci peut être utile lorsque l'on souhaite que la collecte automatique ne passe pas sur certaines tables dont on maîtrise le chargement et donc le moment opportun pour passer les statistiques.

Procédures de lock de statistiques :

DBMS_STATS.LOCK_TABLE_STATS (
  ownname VARCHAR2,tabname VARCHAR2);
DBMS_STATS.UNLOCK_TABLE_STATS ( 
  ownname VARCHAR2,tabname VARCHAR2);

DBMS_STATS.LOCK_SCHEMA_STATS (
  ownname VARCHAR2);
DBMS_STATS.UNLOCK_SCHEMA_STATS (
  ownname VARCHAR2);

Exemples de comportement en LOCK ou UNLOCK :

SQL> drop table couuk8.toto;

Table dropped.

SQL> CREATE TABLE couuk8.toto (
  2     a NUMBER
  3  );

Table created.

SQL> SELECT last_analyzed FROM dba_tables
  2  WHERE table_name='TOTO'
  3  AND owner='COUUK8';

LAST_ANA
--------


SQL> -- test 1
SQL> EXEC DBMS_STATS.LOCK_TABLE_STATS ('COUUK8','TOTO');

PL/SQL procedure successfully completed.

SQL> col owner for a8 trunc
SQL> col table_name for a10 trunc
SQL> SELECT owner, table_name, stattype_locked
  2  FROM dba_tab_statistics
  3  WHERE owner = 'COUUK8'
  4  AND stattype_locked IS NOT NULL
  5  ;


OWNER    TABLE_NAME STATT
-------- ---------- -----
COUUK8   TOTO       ALL

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('COUUK8');

PL/SQL procedure successfully completed.

SQL> SELECT last_analyzed FROM dba_tables
  2  WHERE table_name='TOTO'
  3  AND owner='COUUK8';

LAST_ANA
--------


SQL> -- test 2
SQL> EXEC DBMS_STATS.UNLOCK_TABLE_STATS ('COUUK8','TOTO');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('COUUK8');

PL/SQL procedure successfully completed.

SQL> SELECT last_analyzed FROM dba_tables
  2  WHERE table_name='TOTO'
  3  AND owner='COUUK8';

LAST_ANA
--------
01/08/11

SQL> -- test 3
SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS ('COUUK8','TOTO');

PL/SQL procedure successfully completed.

SQL> SELECT last_analyzed FROM dba_tables
  2  WHERE table_name='TOTO'
  3  AND owner='COUUK8';

LAST_ANA
--------


SQL> EXEC DBMS_STATS.LOCK_TABLE_STATS ('COUUK8','TOTO');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('COUUK8','TOTO');
BEGIN DBMS_STATS.GATHER_TABLE_STATS ('COUUK8','TOTO'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13437
ORA-06512: at "SYS.DBMS_STATS", line 13457
ORA-06512: at line 1


SQL> -- test 4
SQL> EXEC DBMS_STATS.UNLOCK_TABLE_STATS ('COUUK8','TOTO');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('COUUK8','TOTO');

PL/SQL procedure successfully completed.

SQL> SELECT last_analyzed FROM dba_tables
  2  WHERE table_name='TOTO'
  3  AND owner='COUUK8';

LAST_ANA
--------
01/08/11

STATISTIQUES : dynamic sampling

Oracle offre la possibilité à l'optimiseur de calculer ses statistiques à la volée, en utilisant le mécanisme DYNAMIC SAMPLING. Ceci peut se révéler efficace lorsque Oracle détecte que des statistiques sont absentes ou insuffisament précises, en contre-partie, le parsing de la requête durera un peu plus longtemps...

Pour activer ce mécanisme, il faut 

• soit que le paramètre d'initialisation OPTIMIZER_DYNAMIC_SAMPLING soit positionné à une valeur entre 1 et 10
• soit que le hint /*+ DYNAMIC_SAMPLING(tab value) */ soit indiqué dans une requête.

Exemple :

 
SELECT /*+ dynamic_sampling(e 1) */ count(*) 
FROM employees e;

Oracle détermine ainsi au moment de la compilation si une requête peut bénéficier du DYNAMIC SAMPLING. Si tel est le cas, un SQL recursif est exécuté pour scanner un échantillon des blocks de la table et comparer les résultats aux valeurs des prédicats de la requête avant de choisir le meilleur plan d'exécution.

STATISTIQUES : statistiques système

Statistiques "SYSTEM"

En plus des statistiques sur la composition des données des tables, l'optimiseur Oracle doit connaître l'état du système sur lequel il détermine ses plans d'exécution. Pour ce faire, les statistiques systèmes doivent être calculées et fournies à l'optimiseur.

Ces statistiques comportent les informations de performances générales de la CPU et des I/O du serveur. Les besoins en CPU et I/O sont estimés par l'optimiseur pour chaque requête, et sont mises en regard des performances du serveur précédemment déterminées par le calcul de statistiques système. Ainsi Oracle est en mesure de choisir le meilleur plan d'exécution.

Oracle analyse l'activité du sytème dans une période de temps donnée (workload statistics) ou simule une activité (noworkload statistics). Les statistiques système sont recueillies par la procédure suivante DBMS_STATS.GATHER_SYSTEM_STATS.

Extrait de la doc Oracle.
Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2)
Chap 14.

Gathering Workload Statistics

To gather workload statistics, either:

• Run the dbms_stats.gather_system_stats('start') procedure at the beginning of the workload window, then the dbms_stats.gather_system_stats('stop') procedure at the end of the workload window.

• Run dbms_stats.gather_system_stats('interval', interval=>N) where N is the number of minutes when statistics gathering will be stopped automatically.

To delete system statistics, run dbms_stats.delete_system_stats(). Workload statistics will be deleted and reset to the default noworkload statistics.

Gathering Noworkload Statistics

To gather noworkload statistics, run dbms_stats.gather_system_stats() with no arguments. There will be an overhead on the I/O system during the gathering process of noworkload statistics. The gathering process may take from a few seconds to several minutes, depending on I/O performance and database size.

 

The information is analyzed and verified for consistency. In some cases, the value of noworkload statistics may remain its default value. In such cases, repeat the statistics gathering process or set the value manually to values that the I/O system has according to its specifications by using the dbms_stats.set_system_stats procedure.

"Oracle Corporation highly recommends that you gather system statistics"

Détermination des valeurs des statistiques système :

SQL> select version from v$instance;

VERSION

-----------------

10.2.0.4.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1

------------------------------ ----------

SREADTIM                            8,855

MREADTIM                           39,009

CPUSPEED                              432

MBRC                                   28

MAXTHR                          164772864

SLAVETHR                           538624

6 rows selected.

Statistiques sur les FIXED OBJECTS

Il est également nécessaire de recueillir les statistiques sur les objets fixes "fixed objects" comme les tables de performance dynamiques en utilisant la procédure DBMS_STATS.GATHER_FIXED_OBJECTS_STATS.
Ces objets enregistrent l'activité courante de la base.

Ces statistiques doivent être collectées lorsque la base a une activité représentative.


Statistiques sur les SCHEMAS SYSTEMES
  

Enfin pour calculer les statistiques sur les schémas système (SYS, SYSTEM, CTXSYS, DRSYS), on peut utiliser la procédure DBMS_STATS.GATHER_DICTIONARY_STATS.

STATISTIQUES : collecte automatique

DBMS_STATS

Le package DBMS_STATS est au centre du processus de calcul de statistiques sur les objets de la base Oracle depuis la version Oracle 8i.

Pour les procédures GATHER_SCHEMA_STATS et GATHER_DATABASE_STATS, L'option GATHER_STALE permet de calculer les statistiques devenues obsolètes (contenu des tables modifié de plus de 10%). L'option GATHER_AUTO calcule en plus les statistiques des tables qui n'en n'ont pas (comme l'option GATHER_EMPTY)

GATHER_AUTO = GATHER_STALE + GATHER_EMPTY

NB : L'option GATHER (tout court), indique de recalculer tout le schéma sans autre considération.


MONITORING

Le monitoring est le process qui enregistre le nombre de modifications survenues sur une table.

En 9i, les paramètres MONITORING et NOMONITORING des tables indiquaient si les modifications subies par ces tables devaient être monitorées ou pas.

A partir de la 10g, ce paramètre est obsolète et n'est plus pris en compte. Seul le paramètre d'init STATISTICS_LEVEL commande le monitoring et le calcul de statistiques des objets de la base.

Valeurs possibles :

    *  ALL (collecte automatique des statistiques activé + statistiques supplémentaires OS et plans). The additional statistics are timed OS statistics and plan execution statistics.
    *  TYPICAL (valeur par défaut - collect automatique des statistiques activée)
    *  BASIC (désactive le monitoring des tables ainsi que le calcul automatique des statistiques - non recommandé par Oracle)

Le monitoring des tables, actualise les nombre de lignes mises à jour depuis le dernier calcul de statistiques de cette table. Toutes les 3 heures environ, SMON flush les valeurs de monitoring contenues dans la SGA dans le dictionnaire de données. (vue DBA_TAB_MODIFICATIONS).

Flush manuel :

EXEC dbms_stats.flush_database_monitoring_info;

GATHER_STATS_JOB

Le job GATHER_STATS_JOB est prédéfini lors de la création de la base Oracle. Il se charge de collecter les statistiques pour tous les objets de la base pour lesquels il n'y a pas de statistiques ou pour lesquels les statistiques sont obsolètes.

Le scheduler exécute ce job lorsque la fenêtre de maintenance est ouverte. (Par défaut tous les jours de 22H00 à 06H00) - Le job s'interrompt à la fermeture de la fenêtre de maintenance. Le Job appelle la procédure DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC. Cette procédure est similaire à la procédure DBMS_STATS.GATHER_DATABASE_STATS, mais en plus elle priorise les objets à analyser.

Lister le job GATHER_STATS_JOB :

SELECT *
FROM DBA_SCHEDULER_JOBS

WHERE JOB_NAME = 'GATHER_STATS_JOB';

Si l'on souhaite collecter les statistiques manuellement et donc désactiver le calcul automatique :

en 10g :

BEGIN

  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

END;

/

en 11g :

BEGIN

  DBMS_AUTO_TASK_ADMIN.DISABLE(

    client_name => 'auto optimizer stats collection',

    operation => NULL,

    window_name => NULL);

END;

/

En 11g deux nouveaux concepts sont introduits :

 - prise de statistiques sur des colonnes multiples et sur des fonctions de colonnes
 - possibilité de publier les statistiques