Tuning : Génération automatique des fichiers AWR

Pour effectuer du tuning sur une base de données, on peut être amené à générer les fichiers AWR sur une période plus ou moins longue. Manuellement, cette opération peut être fastidieuse.
On s’attachera ici à décrire la méthode de génération des AWR sur une période d’une journée.
En pré requis on s’assurera qu’une directory, au sens Oracle, a bien été définie dans la base de données a auditer.
On s’assurera que l’intervalle entre la création des snapshots est correctement positionné. Un intervalle de 10 a 15mn est un bon compromis entre la qualité des informations recueillies et l’overhead généré sur la base.
Le paramétrage de l’intervalle entre les snapshots ainsi que la durée de rétention des informations peut se faire de 2 façons différentes.

En mode ligne de commande, connectez vous a la base de données en tant que sysdba et lancez la commande suivante:

EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
 interval => 10,
 retention => 20160);

Pour la création d’un snapshot toutes les 10mn avec une rétention de 14 jours.
Autre possibilité, passer par la console d’administration
Une fois que vous êtes connecté à la base de données voulue:

• Choisir l’onglet                            Server

• Puis dans le paragraphe              Statistics Management

• Sélectionnez le lien                     Automatic Workload Repository

• Bouton                                       Edit

• Saisir les valeurs voulues dans    Retention Period et Interval

• Puis bouton                                 OK

Une fois le paramétrage en place et les snapshots générés sur la période voulue, dans notre exemple 1 journée, on peut lancer la génération des AWRs.
On va générer un fichier AWR correspondant à l’intervalle défini précédemment, au format texte ou html sur la journée de la veille.
Cela correspond à 151 fichiers pour un intervalle de 10mn.
Dans un premier temps, on va créer la directory.
Connectez vous en tant que sysdba puis lancez la commande suivante

CREATE OR REPLACE DIRECTORY AWR_DIR as '/tmp/awr/{DATABASE_NAME}';

Où {DATABASE_NAME} correspond au nom de la base de données à auditer.
On peut maintenant lancer la génération des awrs.
Toujours en tant que sysdba

-- Positionnement de la variable globale correspondant au type de rapport désiré
---------------------------------------------------------------------------------
variable gl_file_type varchar2(4);
begin
 :gl_file_type := '{AWR_TYPE}' ;
 end;
 /
-- ou {AWR_TYPE} prend la valeur txt ou html
-- procédure de génération des fichiers
-----------------------------------------
 declare
 lc_dbid NUMBER;
 lc_snap_debut NUMBER;
 lc_snap_fin NUMBER;
 awr_file_name VARCHAR2(24);
 instance_liste AWRRPT_INSTANCE_LIST_TYPE;
 lc_instance_liste VARCHAR2(1023);
 snap_init NUMBER;
 snap_suivant NUMBER;
 boucle NUMBER := 0;
-- Variables utilisées dans la création des fichiers
-----------------------------------------------------
 Num_Fichier Utl_File.File_Type;
 Repertoire VARCHAR2(200):='AWR_DIR';
-- Curseur permettant de retrouver la liste des instances RAC
--------------------------------------------------------------
 cursor curs_instance_liste is
 select to_char(inst_id,'9') inst_id
   from gv$instance
  order by inst_id;
-- curseur permettant de générer les informations correspondant au fichier txt
-- Pour l'ensemble des instances de la base de données
-------------------------------------------------------------------------------
 cursor curs_awr_text_report (c_dbid NUMBER ,
                              c_instances VARCHAR2 ,
                              c_snapdeb NUMBER ,
                              c_snapfin NUMBER ) is
 SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_TEXT (
                                                                      c_dbid ,
                                                                      c_instances ,
                                                                      c_snapdeb ,
                                                                      c_snapfin ,
                                                                      0
                                                                      )
                    )
 ;
-- curseur permettant de générer les informations correspondant au fichier html
-- Pour l'ensemble des instances de la base de données
--------------------------------------------------------------------------------
 cursor curs_awr_html_report (c_dbid NUMBER ,
                              c_instances VARCHAR2 ,
                              c_snapdeb NUMBER ,
                              c_snapfin NUMBER ) is
 SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML (
                                                                      c_dbid ,
                                                                      c_instances ,
                                                                      c_snapdeb ,
                                                                      c_snapfin ,
                                                                      0
                                                                      )
                    )
 ;
begin
-- recherche des numéros de snapshoot
--------------------------------------
 select DBID,
        MIN(SNAP_ID),
        MAX(SNAP_ID)
   into lc_dbid ,
        lc_snap_debut,
        lc_snap_fin
   from dba_hist_snapshot
  where trunc(BEGIN_INTERVAL_TIME) = trunc(sysdate - 1)
  group by DBID;
-- génération des awr's
---------------------------
  for ind_boucle in lc_snap_debut .. lc_snap_fin
   loop
    snap_init := ind_boucle ;
    snap_suivant := ind_boucle + 1;
-- génération du nom de fichier
--------------------------------
    select 'awr_' ||to_char(BEGIN_INTERVAL_TIME,'YYYYMMDD_HH24MISS') || '.' || :gl_file_type
      into awr_file_name
      from dba_hist_snapshot
     where dbid = lc_dbid
       and snap_id = snap_init
       and rownum = 1;
    boucle := 0 ;
    lc_instance_liste :='';
    for liste in curs_instance_liste
     loop
      if boucle != 0
       then
        lc_instance_liste := lc_instance_liste || ',' ;
      end if;
      boucle := boucle + 1;
      lc_instance_liste := lc_instance_liste || substr(liste.inst_id,2);
    end loop;
    Num_fichier := Utl_File.Fopen( Repertoire, awr_file_name ,'W', 32000);
    if :gl_file_type = 'txt'
     then
      for ligne in curs_awr_text_report (lc_dbid,lc_instance_liste,snap_init,snap_suivant)
       loop
        Utl_File.Put_Line( Num_fichier,ligne.output);
      end loop;
     else
      for ligne in curs_awr_html_report (lc_dbid,lc_instance_liste,snap_init,snap_suivant)
       loop
        Utl_File.Put_Line( Num_fichier,ligne.output);
       end loop;
     end if;
     Utl_File.Fclose(Num_fichier);
   end loop;
 end;
 /

Voila, les fichiers sont générés, reste à passer à la phase d’analyse. Et là, c’est une autre histoire.
Une dernière remarque, les fichiers de type html sont intrinsèquement beaucoup plus volumineux et donc plus long à générer.