COMPUTE STATISTICS : obsolète

INDEX CREATION : COMPUTE STATISTICS

Dans les versions antérieurs à Oracle 10g, on pouvait utiliser la clause COMPUTE STATISTICS dans une commande de CREATE INDEX.


Cette clause a été dépréciée. Elle est toujours supportée pour la compatibilité ascendante, et ne causera pas d'erreur.


Oracle collecte désormais les statistiques automatiquement durant la création ou le rebuild de l'index.

STATISTICS : NO_INVALIDATE

Le calcul de statistiques via DBMS_STATS n'a pas, par défaut, d'impact immédiat sur les plans d'exécution. Ceci est un choix délibéré d'Oracle pour éviter de démultiplier les parsing nécessaires à l'exécution des requêtes. Sauf qu'en contre-partie, on risque d'avoir des surprises.

Par exemple : si j'ai une table vide qui possède des statistiques à jour, et que je lance une requête qui utilise cette table, puis que je charge cette table, que je recalcule les statistiques, et que je relance la requête dans laquelle cette table est impliquée (jointure par ex.). Surprise : le temps d'exécution est extrêmement long (bien trop long), le plan d'exécution n'a pas changé !!

Oracle utilise par défaut la fonction DBMS_STATS.AUTO_INVALIDATE, fonction qui indique à Oracle d'invalider les plans d'exécution liés à la table en question, lorsque cela lui convient (dans un maximum de 5H de temps), ce afin d'étaler la charge de recalcule des plans d'exécution.

Aussi, pour forcer l'invalidation des plans d'exécution, Oracle nous laisse la possibilité d'indiquer NO_INVALIDATE => FALSE.

En conclusion, voici ce que je retiens de ce problème :Lorsque l'on charge des tables en annule et remplace (surtout si le nombre de lignes chargé est très variable), il faut tout de suite après le chargement lancer un calcul de statistiques, et avec l'option NO_INVALIDATE => FALSE.

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 

TRUNCATE : Appel de Procédure Stockée via db link

Alors que le delete, lui, fonctionne sans problème, la commande truncate en tant que DDL ne passe pas au travers d'un db link.

Sur la base cible :

SQL> SELECT COUNT(*) FROM toto;

  COUNT(*)
----------
         1

Sur la base source :
 
SQL> DELETE FROM toto@versqua;

1 row deleted.

SQL> ROLLBACK;

Rollback complete.

SQL> TRUNCATE TABLE toto@versqua;
TRUNCATE TABLE toto@versqua
                    *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

Il existe une solution qui consiste à disposer une procédure stockée sur la base cible qui effectue le truncate. Cette procédure étant appelée depuis la base source via db link.
 

Sur la base cible :

SQL> SELECT COUNT(*) FROM toto;

  COUNT(*)
----------
         1
 

SQL> CREATE PROCEDURE p_u_truncate (p_table IN VARCHAR2)
  2     IS
  3     BEGIN
  4        EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || p_table;
  5     END;
  6  /

Procedure created.

Sur la base source :

SQL> EXEC p_u_truncate@versqua('TOTO');

PL/SQL procedure successfully completed.

Sur la base cible :

SQL> SELECT COUNT(*) FROM toto;

  COUNT(*)
----------
         0

Explication : la procédure stockée sur la base cible est appelée depuis la base source et s'exécute sur la base cible.

DOUBLONS : détecter les doublons partiels

Comment détecter les lignes d'une table faisant doublon sur une liste de colonne (et non pas sur l'ensemble de la ligne).

Exemple :

SQL> CREATE TABLE toto (
  2     pays VARCHAR2(3),
  3     ville VARCHAR2(10),
  4     note NUMBER
  5  );

Table created.

SQL> INSERT INTO toto VALUES ('FRA','PARIS',1);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','PARIS',2);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','PARIS',3);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','PARIS',3);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','LYON',4);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','LYON',5);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','MARSEILLE',5);

1 row created.

SQL> INSERT INTO toto VALUES ('USA','NEW YORK',5);

1 row created.

SQL> COMMIT;

Commit complete.

Pour le cas classique :

SQL> SELECT t1.pays, t1.ville, t1.note, count(*)
  2  FROM toto t1
  3  GROUP BY t1.pays, t1.ville, t1.note
  4  HAVING COUNT(*) > 1
  5  ;

PAY VILLE            NOTE   COUNT(*)
--- ---------- ---------- ----------
FRA PARIS               3          2

Et la liste :

SQL> SELECT t1.* FROM toto t1 WHERE t1.rowid >= (
  2     SELECT MIN(t2.rowid) FROM toto t2
  3     WHERE t1.pays=t2.pays
  4       AND t1.ville=t2.ville
  5       AND t1.note=t2.note
  6     HAVING COUNT(*) > 1
  7  )
  8  ;

PAY VILLE            NOTE
--- ---------- ----------
FRA PARIS               3

FRA PARIS               3

Et par extension, pour le cas des doublons partiels :

SQL> SELECT t1.pays, t1.ville, COUNT(*)
  2  FROM toto t1
  3  GROUP BY t1.pays, t1.ville
  4  HAVING COUNT(*) > 1
  5  ;

PAY VILLE        COUNT(*)
--- ---------- ----------
FRA LYON                2
FRA PARIS               4

Et la liste :

SQL> SELECT t1.* FROM toto t1 WHERE t1.rowid >= (
  2     SELECT MIN(t2.rowid) FROM toto t2
  3     WHERE t1.pays=t2.pays
  4       AND t1.ville=t2.ville
  5     HAVING COUNT(*) > 1
  6  )
  7  ;

PAY VILLE            NOTE
--- ---------- ----------
FRA PARIS               1
FRA PARIS               2
FRA PARIS               3
FRA PARIS               3
FRA LYON                4
FRA LYON                5

APPEND NOLOGGING : accélérer les insertions

Pour les chargements massifs, il peut être intéressant d'utiliser certaines options qui permettent d'accélérer le temps de traitement. Bien sûr ces options ont des contre-parties.

L'option APPEND pour l'insert force Oracle à passer directement au-delà du HWM (High Water Mark) pour insérer les nouvelles lignes. Elle évite ainsi la recherche des blocks libres et réduit fortement le nombre de blocks à sauvegarder dans les UNDO. Mais elle conduit à une éventuelle perte d'espace disque.

L'option NOLOGGING indique à Oracle qu'il n'est pas nécessaire de logguer dans les REDO les changements qui ont lieu dans la table. Il s'ensuit une écriture moins importante dans les REDO, mais également l'impossibilité de récupérer la table avec les nouvelles lignes en cas de restauration de cette dernière.

Exemple

Préparation :

SQL> CREATE TABLE toto AS
  2  SELECT rownum ID, dbms_random.string('A', 12) STR,
  3  trunc(dbms_random.value(1,1000)) NUM
  4  FROM all_objects
  5  WHERE rownum <= 200000
  6  ;

Table created.

SQL> INSERT INTO toto SELECT * FROM toto;

200000 rows created.
SQL> INSERT INTO toto SELECT * FROM toto;

400000 rows created.
SQL> COMMIT;

Commit complete.

Session 1 :

SQL> SET TIMING ON
SQL> INSERT INTO toto SELECT * FROM toto;

800000 rows created.

Elapsed: 00:00:31.04
SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:02.09
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'undo change vector size';

NAME                                                       VALUE
----------------------------------------------------- ----------
undo change vector size                                  2270900
 

SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'rollback changes - undo records applied';

NAME                                                       VALUE
----------------------------------------------------- ----------
rollback changes - undo records applied                     6087

SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'redo size';

NAME                                                       VALUE
----------------------------------------------------- ----------
redo size                                               31320224

Session 2 :

SQL> SET TIMING ON
SQL> INSERT /*+ APPEND */ INTO toto SELECT * FROM toto;

800000 rows created.

Elapsed: 00:00:12.06

SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:00.00
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'undo change vector size';

NAME                                                       VALUE
----------------------------------------------------- ----------
undo change vector size                                   258516

SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'rollback changes - undo records applied';

NAME                                                       VALUE
----------------------------------------------------- ----------
rollback changes - undo records applied                        0

SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'redo size';

NAME                                                       VALUE
----------------------------------------------------- ----------
redo size                                               25438660

Session 3 :

SQL> SET TIMING ON
SQL> INSERT /*+ APPEND NOLOGGING */ INTO toto SELECT * FROM toto;

800000 rows created.

Elapsed: 00:00:04.01

SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:00.00
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'undo change vector size';

NAME                                                       VALUE
----------------------------------------------------- ----------
undo change vector size                                      208

SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'rollback changes - undo records applied';

NAME                                                       VALUE
----------------------------------------------------------------
rollback changes - undo records applied                        0
 
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'redo size';

NAME                                                       VALUE
----------------------------------------------------- ----------
redo size                                               24488552

en résumé :

APPEND   NOLOGGING  INSERT(sec)  UNDO_VECTOR  REDO_SIZE

     N           N           31      2270900   31320224

     Y           N           12       258516   25438660

     Y           Y            4          208   24488552

ENABLE ROW MOVEMENT : tables partitionnées

Oracle garantit qu'une ligne ne sera pas déplacée physiquement par un update et donc que son ROWID ne sera pas changé. Ce phénomène pourrait en effet survenir dans une table partitionnée avec un update sur la colonne de partitionnement, forçant Oracle à déplacer la ligne dans la bonne partition. (Déplacement physique, changement de ROWID).

Exemple :

SQL> CREATE TABLE toto(a NUMBER)
  2  PARTITION BY RANGE (a)(
  3  PARTITION p1 VALUES LESS THAN (10),
  4  PARTITION p2 VALUES LESS THAN (20),
  5  PARTITION p3 VALUES LESS THAN (MAXVALUE));

Table created.

SQL> INSERT INTO toto VALUES (5);

1 row created.

SQL> UPDATE toto SET a=15;
UPDATE toto SET a=15
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

 

Pourquoi Oracle provoque-t'il cette erreur ?

A priori on ne voit pas de raison pour laquelle on ne pourrait pas tranquillement mettre à jour une table, même sur la colonne de partitionnement, sans provoquer une erreur... Ceci est en fait un héritage, qu'Oracle nous a transmis pour que lors de l'introduction de tables partitionnées, les programmes qui utilisaient les ROWID n'aient pas de mauvaise surprise en partitionnant leurs tables. Le changement du ROWID lors d'un update pouvant être catastrophique avec ce genre de conception de programme.

Mais comment faire alors ?

Par défaut, donc, Oracle empêche les mises à jour des colonnes de partitionnement des tables partitionnées. Pour autoriser ces mises à jour, il est donc nécessaire de passer la commande ENABLE ROW MOVEMENT en préalable :

Exemple :

SQL> ALTER TABLE toto ENABLE ROW MOVEMENT;

Table altered.

SQL> UPDATE toto SET a=15;

1 row updated.

Bien qu'il existe la commande inverse qui rétablit l'impossibilité de mettre à jour ces colonnes (DISABLE ROW MOVEMENT), si aucun de vos programmes n'utilise les ROWID, il n'est pas nécessaire de repasser les tables en mode par défaut (disable).

Voici tout de même la commande :

SQL> ALTER TABLE toto DISABLE ROW MOVEMENT;

Table altered.

VARIABLES : bind, substitution, programme

Dans cet exemple sont illustrés les emplois des différents types de variable suivants :

 

- variable de substitution (define) - interprétée par SqlPlus

- bind variable (variable) - SQL - PL/SQL
- variable de programme (declare) - PL/SQL

Ces variables sont utilisées pour des raisons bien spécifiques, et ne doivent en aucun cas être confondues...

Exemple

  

SQL> SET VERIFY OFF
SQL> DEFINE matable=dba_users;
SQL> VARIABLE b NUMBER;
SQL> DECLARE
  2  res VARCHAR2(30);
  3
  4  BEGIN
  5  :b :=0;
  6  SELECT username INTO res FROM &matable WHERE user_id=:b;
  7  dbms_output.put_line('USER IS : '||res);
  8  END;
  9  /
USER IS : SYS

PL/SQL procedure successfully completed.

Autre exemple pour une bind variable en SQL pur

SQL> VARIABLE b NUMBER;
SQL> EXECUTE :b:=0;

PL/SQL procedure successfully completed.

SQL> SELECT username
  2  FROM all_users
  3  WHERE user_id = :b;

USERNAME
------------------------------
SYS

OUT : valeur de retour d'une procedure

Les procédures Oracle offrent la possibilité de renvoyer une valeur en sortie au travers d'une variable OUT définie dans la liste des paramètres de la procédure.

CREATE OR REPLACE PROCEDURE p_sqrt(
    a IN NUMBER, b OUT NUMBER
) AS
BEGIN
    b:=a*a;
END;
/

SET SERVEROUTPUT ON SIZE 2000

DECLARE
  entree NUMBER;
  sortie NUMBER;
BEGIN
  entree:=9;
  p_sqrt(entree,sortie);
  dbms_output.put_line(entree||'² = '||sortie);
END;
/

9² = 81

PL/SQL procedure successfully completed.

CUBE : requête analytique

Il existe de nombreuses fonctions analytiques permettant d'obtenir des rapports multi-dimensionnels plus ou moins complexes.

Exemple de Cube :

SET LINES 150
SET PAGES 30
SET FEED OFF
SET ESCAPE \

BREAK ON typ SKIP 1 ON tbs SKIP 0
COL tbs FOR A10 TRUNC

SELECT
    NVL(t.typ,'ALL') AS typ,
    NVL(t.tablespace_name,'________') AS tbs,
    ROUND(SUM(t.bytes)/1024/1024,0) AS siz_mo
FROM (
    SELECT
   (CASE
        WHEN s.segment_type LIKE 'TAB%' THEN 'TAB'
        WHEN s.segment_type LIKE 'IND%' THEN 'IND'
        ELSE 'OTH'
    END
    ) AS typ,
    s.tablespace_name,
    s.bytes
    FROM dba_segments S
    WHERE owner='SYS'
) T
GROUP BY CUBE(t.typ, t.tablespace_name)
ORDER BY 1 DESC, 2 DESC
/

SET FEED ON
SET ESCAPE OFF

 

Résultat :
 

TYP TBS            SIZ_MO
--- ---------- ----------
TAB TOOLS              23
    SYSTEM            402
    SYSAUX            205
    ________          629

OTH UNDOTBS            52
    SYSTEM            218
    SYSAUX             21
    ________          291

IND TOOLS               0
    SYSTEM            370
    SYSAUX            283
    ________          654

ALL UNDOTBS            52
    TOOLS              23
    SYSTEM            990
    SYSAUX            508
    ________         1573