Introduction
Un client est venu me voir avec un problème sur une table comprenant 300 colonnes... Oui, 300! Son problème a été rapidement résolu, il ne portait pas sur ce nombre de colonnes MAIS je me suis posé la question : est-ce que toutes les colonnes de cette table sont vraiment utilisées? Est-il possible de nettoyer la table en supprimant des colonnes et gagner ainsi de l'espace disque, voir de supprimer des index sur des colonnes devenues inutiles au fil des ans?

La question est : où trouver les statistiques d'utilisation des colonnes? Attention, je ne parle pas des statistiques de la table DBA_TAB_COL_STATISTICS mais des stats disant que telle ou telle colonne a été utilisée dans 100% ou 60% ou 0% des SELECT sur la table.

La réponse est NULLE PART! Oracle n'a pas prévu, à ma connaissance, de monitorer les colonnes de ses tables; c'est pourquoi j'ai développé un script avec le package DBMS_FGA pour résoudre ce problème.

Pour rappel, DBMS_FGA est le package PL/SQL qui permet de faire de l'audit de granularité fine (FGA = Fine Grained Auditing). Je vous laisse lire la doc officielle Oracle mais ce package est très puissant pour tracer de façon très fines certaines opérations : par exemple, qui fait des SELECT sur le nom et prénom des employés ayant un salaire plus élevé que 100 000€?



Points d'attention
La solution ci-dessous ne concerne que l'utilisation des colonnes dans les SELECT et pas dans les UPDATE. Cela signifie qu'une colonne très fréquemment updatée ne ressortira pas dans le script que j'ai développé... mais vous pouvez l'adapter pour prendre en compte l'UPDATE :-)

L'astuce est de créer une police d'audit pour CHAQUE colonne de la table à surveiller et non pas une seule police d'audit pour toutes les colonnes! De la sorte, dans la table DBA_AUDIT_POLICIES, nous avons un enregistrement pour chaque colonne surveillée dans la colonne POLICY_COLUMN, ce qui nous permettra de générer des statistiques colonne par colonne.



Base de tests
N'importe quelle base Oracle mais l'édition d'Oracle doit avoir l'option FGA activée, ce qui exclut l'Express Edition d'Oracle sous Windows. Si à la compilation de mon script vous obtenez l'erreur "ORA-00439: feature not enabled: Fine-grained Auditing", cela signifie que votre version d'Oracle ne peut pas gérer les fonctionnalités d'audit FGA.
 


                   
Exemples
============================================================================================
Etape 1 : vérifier quelles sont les polices d'audit déjà existantes.

============================================================================================
Les tables système utilisées par DBMS_FGA sont DBA_AUDIT_POLICIES et DBA_FGA_AUDIT_TRAIL.
Dans notre base, aucune police d'audit n'existe pour le moment.
          SQL> select * from DBA_AUDIT_POLICIES;
          no rows selected

          SQL> select * from DBA_FGA_AUDIT_TRAIL order by EXTENDED_TIMESTAMP;
          no rows selected


============================================================================================
Etape 2 : créer les procédures d'audit pour chaque colonne de la table à surveiller.

============================================================================================
Table de tests
J'utilise une table toute simple.
          SQL> DESC EMP;
          Nom NULL                     ?           Type
          ---------------------------------------------------------------------------------------------
          NAME                                         VARCHAR2(50 CHAR)
          FIRST_NAME                               VARCHAR2(50 CHAR)
          SALARY                                      NUMBER(8,2)
          HIRE_DATE                                  DATE


Création d'un fichier FGA01.sql avec l
e code de la procédure de création des polices d'audit suivant.

          SET SERVEROUTPUT ON;

          DECLARE
                    v_s_table_name VARCHAR2(30);
                    v_s_column_name VARCHAR2(30);
                    v_s_policy_name VARCHAR2(100);

                    -- Curseur pour récupérer la liste des colonnes de la table passée en paramètre. Le schéma est HR.
                    CURSOR v_cur_list_column_name IS
                    SELECT COLUMN_NAME
                    FROM DBA_TAB_COLUMNS
                    WHERE TABLE_NAME = '&&1' AND OWNER = 'HR'
                    ORDER BY COLUMN_ID;

          BEGIN
                    -- Création de la liste des polices d'audit : un audit par colonne
                    OPEN v_cur_list_column_name;

                    WHILE TRUE
                    LOOP

                              v_s_column_name := NULL;
                              v_s_policy_name := NULL;

                              FETCH v_cur_list_column_name INTO v_s_column_name;
                              EXIT WHEN v_cur_list_column_name%NOTFOUND;

                              v_s_policy_name := 'AUDIT_' || '&1' || '_' || v_s_column_name;

                              DBMS_FGA.ADD_POLICY (
                              object_schema => 'HR',
                              object_name => '&1',
                              policy_name => v_s_policy_name,
                              audit_column => v_s_column_name,
                              statement_types => 'SELECT',
                              audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED,
                              audit_column_opts => DBMS_FGA.ANY_COLUMNS);

                    END LOOP;

                    CLOSE v_cur_list_column_name;

          EXCEPTION
          -- Gestion des erreurs
          WHEN OTHERS THEN
               DBMS_OUTPUT.PUT_LINE('Une erreur est survenue. Code : ' || TO_CHAR(SQLCODE) || '. Message : ' || SQLERRM);

          END;

          /


Compilation de la procédure avec en paramètre la table à surveiller.
Le user qui compile la procédure doit avoir les droits d'exécution sur le package DBMS_FGA. J'ai mis le user en dur, à savoir HR, mais il est possible de modifier le code et d'ajouter un deuxième paramètre correspondant au schéma de la table à surveiller.

J'ai stocké mon code dans le fichier FGA01.sql sur le disque F et je trace la table EMP.
          SQL> @F:\FGA01 EMP
          ancien 10 : WHERE TABLE_NAME = '&&1' AND OWNER = 'HR'
          nouveau 10 : WHERE TABLE_NAME = 'EMP' AND OWNER = 'HR'
          ancien 26 : v_s_policy_name := 'AUDIT_' || '&1' || '_' || v_s_column_name;
          nouveau 26 : v_s_policy_name := 'AUDIT_' || 'EMP' || '_' || v_s_column_name;
          ancien 30 : object_name => '&1',
          nouveau 30 : object_name => 'EMP',

         
Procédure PL/SQL terminée avec succés.


Vérification que les polices d'audit ont été créées.
Une police d'audit a bien été créée pour chacune des colonnes de la table de test.
         
SQL> select OBJECT_SCHEMA , OBJECT_NAME , POLICY_OWNER , POLICY_NAME , POLICY_COLUMN from DBA_AUDIT_POLICIES;
          OBJECT_SCHEMA           OBJECT_NAME         POLICY_OWNER           POLICY_NAME                POLICY_COLUMN

          ------------------------------ ------------------------------ ---------------------------- ------------------------------ ------------------------------
          HR                               EMP                     HR                            AUDIT_EMP_NAME                 NAME
          HR                               EMP                     HR                            AUDIT_EMP_FIRST_NAME           FIRST_NAME
          HR                               EMP                     HR                            AUDIT_EMP_SALARY             SALARY
          HR                               EMP                     HR                            AUDIT_EMP_HIRE_DATE           HIRE_DATE



============================================================================================
Etape 3 : lancer les ordres SQL à  tracer

============================================================================================

Les ordres SQL à lancer pour tester mon script sont nombreux : prise en compte du caractère *, gestion des sous-requêtes mais aussi des requêtes avec les noms précis des colonnes.... Par exemple :
          SELECT COUNT(*) FROM EMP_FRANCE WHERE SALAIRE IN (SELECT SALARY FROM EMP);
         
SELECT * FROM EMP;
          SELECT NAME, FIRST_NAME FROM EMP;
          ...

Quelles sont les informations d'audit enregistrées par Oracle suite à ces trois SELECT? Pour chaque colonne impactée, une entrée est bien créée dans DBA_FGA_AUDIT_TRAIL et c'est exactement ce que nous voulons : un enregistrement par colonne sélectionnée!
          SQL> select to_char(timestamp, 'DD/MM/YYYY HH:MI:SS'), db_user, object_name, policy_name from dba_fga_audit_trail order by 1;
          TO_CHAR(TIMESTAMP,'           DB_USER           OBJECT_NAME           POLICY_NAME

          ------------------- ------------------------------ --------------------------------------------------------------------------------------------------
          17/02/2017 03:03:16                 HR                     EMP                               AUDIT_EMP_SALARY
          17/02/2017 03:03:26                 HR                     EMP                               AUDIT_EMP_SALARY
          17/02/2017 03:03:26                 HR                     EMP                               AUDIT_EMP_HIRE_DATE
          17/02/2017 03:03:26                 HR                     EMP                               AUDIT_EMP_FIRST_NAME
          17/02/2017 03:03:26                 HR                     EMP                               AUDIT_EMP_NAME
          17/02/2017 03:03:38                 HR                     EMP                               AUDIT_EMP_FIRST_NAME
          17/02/2017 03:03:38                 HR                     EMP                               AUDIT_EMP_NAME

          7 ligne(s) sélectionnée(s).



============================================================================================
Etape 4 : lancer l'ordre SQL de calcul des statistiques

============================================================================================

Il faut maintenant calculer les statistiques sur les enregistrements générés par DBMS_FGA dans les tables DBA_AUDIT_POLICIES et DBA_FGA_AUDIT_TRAIL.

          -- Calcul des stats : le nom de la colonne auditée est dans la table DBA_AUDIT_POLICIES, colonne POLICY_COLUMN

         
SELECT
                    P.POLICY_COLUMN COLONNES,
                    COUNT(*) NOMBRE,
                    ROUND(COUNT(*)/(SELECT COUNT(*) FROM DBA_FGA_AUDIT_TRAIL WHERE OBJECT_NAME = '&&1' AND OBJECT_SCHEMA = 'HR') *100,2) POURCENTAGES
          FROM
                    DBA_AUDIT_POLICIES P
                    JOIN DBA_FGA_AUDIT_TRAIL T
                    ON (P.POLICY_NAME = T.POLICY_NAME)
          WHERE T.OBJECT_NAME = '&1' AND P.OBJECT_SCHEMA = 'HR'
          GROUP BY P.POLICY_COLUMN
          UNION
          SELECT
          COLUMN_NAME, 0, 0 --Select des colonnes de la table sur lesquelles aucun SELECT n'a eu lieu
          FROM
          DBA_TAB_COLUMNS
          WHERE TABLE_NAME = '&1' AND OWNER = 'HR' AND COLUMN_NAME NOT IN (
                    SELECT
                              DISTINCT P.POLICY_COLUMN
                    FROM
                              DBA_AUDIT_POLICIES P JOIN DBA_FGA_AUDIT_TRAIL T ON (P.POLICY_NAME = T.POLICY_NAME)
                    WHERE T.OBJECT_NAME = '&1' AND T.OBJECT_SCHEMA = 'HR')
          ORDER BY POURCENTAGES DESC, COLONNES;

Le résultat obtenu est le suivant. Non seulement nous avons le nombre de SELECT où telle colonne apparait (y compris avec le caractère *) mais aussi le pourcentage d'apparition des colonnes dans tous les SELECT. Il va de soi que par rapport à notre problématique, c'est surtout les colonnes avec 0 en "NOMBRE" qui nous intéressent.

          COLONNES           NOMBRE           POURCENTAGES
          ------------------------------ ---------- ----------------------------------------
          FIRST_NAME           2                     28,57
          NAME                     2                      28,57
          SALARY                  2                     28,57
          HIRE_DATE             1                     14,29
         4 ligne(s) sélectionnée(s).


============================================================================================
Etape 5 : supprimer les polices d'audit FGA

============================================================================================
Pour supprimer les polices d'audit précédemment créées, il faut utiliser la procédure DBMS_FGA.DROP_POLICY.