AUDIT : principes

Paramètres d'initialisation concernant l'audit :

SQL> col name for a25
SQL> col value for a45
SQL> select name, value from v$parameter where name like '%audit%';

NAME                      VALUE
------------------------- -------------------------------------

audit_sys_operations      FALSE
audit_file_dest           /opt/oracle/product/10gr2/rdbms/audit
audit_syslog_level
audit_trail               DB

audit_sys_operations : permet l'audit de toutes les opérartions réalisées par SYS (y compris AS SYSDBA ou SYSOPER). Les traces ainsi générées sont générées sur l'OS (et non en base puisque la base peut être fermée lorsque SYS est connecté). Par conséquent on doit avoir obligatoirement audit_trail OS.


audit_file_dest : chemin de destination des traces d'audit lorsque celles-ci sont de type "OS"


audit_syslog_level : paramètre indiquant le niveau de trace que l'on souhaite pour l'audit des actions SYS

   ex : AUDIT_SYSLOG_LEVEL=local1.warning

audit_trail : décrit le type de trace d'audit : 

      DB : dans la table sys.aud$
   OS : dans des fichiers de trace sur le serveur
   DB + EXTENDED : en base avec possibilité de tracer les queries associées
   XML : dans des fichiers de trace XML sur le serveur
   DBMS_FGA.DB + DBMS_FGA.EXTENDED : idem DB + EXTENDED mais pour le FGA.


Par défaut, l'audit du privilège CREATE SESSION est activé :

SQL> select user_name, privilege from dba_priv_audit_opts;

USER_NAME                      PRIVILEGE
------------------------------ ---------------------------
                               CREATE SESSION

Déclanchement de l'audit d'un objet :

SQL> audit select, insert, delete on lc.titi by access;

Audit succeeded.

SQL> col owner for a5
SQL> col object_name for a11
SQL> col object_type for a11
SQL> select * from dba_obj_audit_opts where object_name='TITI';

OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
----- ----------- ----------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
LC    TITI        TABLE       -/- -/- -/- A/A -/- -/- A/A -/- -/- A/A -/- -/- -/- -/- -/- -/- -/-

Trace d'audit : 

SQL> delete from titi;

0 rows deleted.

SQL> select count(*) from titi;

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

SQL> col username for a11
SQL> col os_username for a11
SQL> col terminal for a15
SQL> col action_name for a11
SQL> alter session set NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> select username, os_username, terminal, action_name, timestamp from (select * from dba_audit_session where username='LC' order by timestamp desc nulls last) where rownum < 2;

USERNAME    OS_USERNAME TERMINAL        ACTION_NAME TIMESTAMP
----------- ----------- --------------- ----------- -------------------
LC          Clala       PFIXE080686     LOGON       13/02/2012 16:58:18

SQL> select os_username, username, terminal, timestamp, action_name from dba_audit_object where owner='LC' and obj_name='TITI';

OS_USERNAM USERNAME   TERMINAL        TIMESTAMP           ACTION_NAM
---------- ---------- --------------- ------------------- ----------
Clala      LC         PFIXE080686     13/02/2012 16:58:23 DELETE
Clala      LC         PFIXE080686     13/02/2012 16:58:47 SELECT

Suppression de l'action d'audit :

SQL> noaudit all on lc.titi;

Noaudit succeeded.

SQL> select * from dba_obj_audit_opts where object_name='TITI';

no rows selected

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>