Utilitaire : TKPROF pour tracer une sessions Oracle

TKPROF : cet utilitaire du système d'exploitation convertit la sortie d'une session SQL TRACE dans un format lisible.

Utiliser SQL Trace et TKPROF

Une procédure spécifique doit être suivie pour évaluer correctement les performances des instructions SQL à l'aide de SQL Trace et de TKPROF :

•          La première étape consiste à définir les paramètres d'initialisation de façon appropriée. Cette définition peut s'effectuer au niveau de l'instance, mais il est possible également de définir certains paramètres au niveau de la session.

•          Vous devez appeler SQL Trace soit au niveau de l'instance, soit au niveau de la session. En règle générale, il est conseillé de l'appeler au niveau de la session.

•          Exécutez l'application ou l'instruction SQL à diagnostiquer.

•          Arrêtez SQL Trace pour pouvoir fermer correctement le fichier trace au niveau du système d'exploitation.

•          Utilisez TKPROF pour rendre lisible le fichier trace généré au cours de la session de trace. Si le fichier de sortie n'est pas lisible, il est très difficile d'interpréter les résultats.

•          Pour diagnostiquer les performances de l'instruction SQL, utilisez les résultats de TKPROF.

Paramètres d'initialisation

Deux paramètres du fichier init.ora gèrent la taille et la destination du fichier de sortie généré par l'utilitaire SQL Trace :

                max_dump_file_size = n

Ce paramètre est exprimé en octets s'il porte la mention Ko ou Mo, ou, à défaut, en nombre de blocs du système d'exploitation. La valeur par défaut est de 10000 blocs de système d'exploitation.

Lorsqu'un fichier trace dépasse la taille définie par la valeur de ce paramètre, le message suivant s'affiche à la fin du fichier : *** Trace file full ***

Le paramètre suivant détermine la destination du fichier trace :

                user_dump_dest = directory

Vous devez définir un troisième paramètre pour obtenir les statistiques temporelles :

                timed_statistics = TRUE

La résolution des statistiques temporelles s'effectue au centième de seconde.

Le paramètre TIMED_STATISTICS peut également être défini de manière dynamique au niveau de la session, à l'aide de la commande ALTER SESSION.

Activer et désactiver SQL Trace

SQL Trace peut être activé ou désactivé à l'aide de différentes méthodes, au niveau de l'instance ou de la session.

Au niveau de l'instance :

La définition du paramètre SQL_TRACE au niveau de l'instance constitue l'une des méthodes d'activation de la fonction de trace. Toutefois, l'instance doit être arrêtée, puis redémarrée une fois que la fonction de trace n'est plus utilisée. En outre, le taux de performance doit être élevé, car toutes les sessions de l'instance font l'objet d'un suivi d'exécution.

Au niveau de la session :

L'utilisation de la fonction de trace au niveau de la session requiert un taux de performance moindre, car le suivi peut porter sur des sessions spécifiques. Vous pouvez activer ou désactiver SQL Trace :

•          à l'aide de la commande ALTER SESSION, qui permet d'exécuter la fonction de trace pendant toute la durée de la session ou jusqu'à ce que la valeur FALSE soit définie,

•          en utilisant la procédure DBMS_SESSION.SET_SQL_TRACE pour la session,

•          en utilisant la procédure DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION pour activer la fonction de trace dans une session différente de la session actuelle.

Rendre le fichier trace lisible à l'aide de TKPROF

$ tkprof   tracefile.trc   output.txt   

NB : Prochain article : Interpréter un fichier trace générer par TKPROF

Chaînage et Migration des données ORACLE

1.  Introduction. 3

    1.1- Migrations des lignes. 3

    1.2- Lignes chaînées

2. Détecter les tables avec des lignes migrées ou chaînées. 4

    2-1- Nombre total d’événements 'table fetch continued row' depuis le démarrage de l'instance. 4

    2.2 - Détecter toutes les tables ayant des lignes chaînées ou migrées

3- Comment éviter les lignes migrées et chaînées

4- Conclusion. 7

1.  Introduction

Dans le cadre de l’optimisation préventive et curative des traitements et accès aux données, et après avoir remarqué quelques dégradations de performances sur notre base production, le chaînage et les migrations de ligne peuvent en être responsables en partie. Il est possible toutefois de détecter et de diagnostiquer ces phénomènes, et dans une plus grande mesure de reparamétrer la base de données pour empêcher le chaînage et la migration de lignes, ce qui est le notre cas aujourd’hui.

L'article se propose :

§  de présenter le chaînage et la migration de lignes

§  d’identifier le chaînage et la migration de lignes au niveau de notre base PRODDB.

§  de montrer comment éviter le chaînage et la migration de lignes.

Les lignes migrées affectent les systèmes OLTP qui utilisent des indexes pour lire quelques lignes.

Les lignes chaînées affectent les lectures d'indexes et les scans de tables (full table scan).

Dans le pire des cas, des I/O supplémentaires pour toutes les lectures peuvent apparaître à cause du chaînage et des migrations de lignes, I/O supplémentaires dégradant les performances.

1.1- Migrations des lignes

Une ligne sera migrée lorsqu'une commande update sur la ligne en question ne permettra pas à cette dernière de demeurer sur le bloc (faute d'espace disponible au niveau du bloc). Une migration d'une ligne implique que l'intégralité de la ligne sera déplacée vers un autre bloc, ne laissant sur le bloc d'origine qu'une adresse de redirection (forward address). Le bloc original ne dispose plus que du RowID et la ligne entière est déplacée vers un autre bloc.

• Impacts des migrations de ligne sur les "Full Scan Tables"

Lors des scans de tables, les adresses de redirection sont ignorées. Lors d'un full scan, le moteur traitera quoiqu'il arrive la ligne, aussi l'adresse de redirection peut être ignorée.

• Impacts des migrations de ligne sur les lectures d'indexes

En revanche, lors d'une lecture d'une table à travers un index, des I/Os supplémentaires sont générés. En effet l'index indiquera au moteur de se rendre à l'adresse file_id X, block Y, slot Z pour trouver la ligne, mais à cette adresse, le moteur est renvoyé vers une autre adresse file_id A, block B, slot C : aussi une E/S (logique ou physique) supplémentaire est générée pour effectivement trouver cette ligne.

1.2- Lignes chaînées

Dans le cas du chaînage de lignes, une ligne ne peut être contenue dans un seul bloc. Certaines conditions engendrent le chaînage de lignes :

§  les tables pour lesquelles la taille de la ligne excède la taille du bloc

§  les tables ayant des colonnes de type long et long raw

§  les tables ayant plus de 255 colonnes.

Les données pour une ligne sont réparties sur plusieurs blocs.

2. Détecter les tables avec des lignes migrées ou chaînées

2-1- Nombre total d'évènements 'table fetch continued row' depuis le démarrage de l'instance

La vue V$SYSSTAT indique combien de fois depuis le démarrage de l'instance la statistique table fetch continued row a été incrémentée, ce qui revient à rechercher le nombre de fois où l'instance a rencontré des lignes chaînées ou migrées :

   SELECT 'Lignes migrées ou chainée = '||VALUE

   FROM v$sysstat

   WHERE lower (NAME) = 'table fetch continued row';

Lignes migrées ou chainée = 54 101 159

L'interprétation de la requête peut signifier plusieurs choses :

§  on peut avoir une table avec une ligne chaînée ou migrée qui a été extraite 54101159 fois.

§  on peut avoir 54101159 tables avec une ligne chaînée ou migrée, ligne qui a été extraite.

Aussi : 54101159, ceci peut être dramatique ou pas ! Tout ceci est fonction :

§  depuis combien de temps l'instance est démarrée  (notre cas : 2015-03-04 18:03:22)

§  combien de lignes cela représente en pourcentage par rapport au nombre total de lignes extraites.

 Pour cela on doit comparer les statistiques table fetch continued row  en pourcentage avec les statistiques table fetch by rowid :

SELECT NAME, VALUE

FROM v$sysstat

WHERE lower(name) like 'table fetch%';

NAME                                                                  VALUE

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

table fetch by rowid                                             3,3659E+10

table fetch continued row                                          54101159

On remarque que 16.5% des lignes sont chainées ou migrées, chiffre qu’il faut essayer de réduire.

2.2 - Détecter toutes les tables ayant des lignes chaînées ou migrées

Le diagnostique a été effectué sur les schémas ci-dessous, et sera généraliser sur tous les schémas de la BD : 'TBAADM','CUSTOM','CRMUSER','TBAGEN'

SELECT owner_name,

       table_name,

       count(head_rowid) row_count

FROM chained_rows

WHERE owner_name  in ('TBAADM','CUSTOM','CRMUSER','TBAGEN')

GROUP BY owner_name,table_name

ORDER BY 3 DESC

Résultats :

OWNER_NAME	TABLE_NAME	ROW_COUNT
TBAADM	INT_ADJUSTMENT_RGTR_TABLE	37717
CRMUSER	SALEBACKEND	6995
CRMUSER	ACCOUNTS	5055
TBAADM	BATCH_JOB_MONITOR_TBL	861
CRMUSER	AGENT_ENTITY_DETAILS	168
CRMUSER	AUDITTRAIL	142
TBAADM	AUDIT_TABLE	72
TBAADM	HOLIDAY_MAST_TABLE	62
TBAADM	IN_TRAN_DETAILS_TABLE	44
CRMUSER	CREDITBUREAU	20
CRMUSER	SALE_DOCUMENT	10
CRMUSER	SUSPECTS	6
TBAADM	GENERAL_ACCT_MAST_TABLE	3
TBAADM	GEN_SCHM_PARM_TABLE	2
CRMUSER	DEMOGRAPHIC	1

3- Comment éviter les lignes migrées et chaînées

En augmentant la valeur du paramètre PCTFREE, cela peut empêcher les migrations de lignes dans le sens ou plus d'espace est réservé dans un bloc. Il est également possible de réorganiser les tables et indexes ayant un fort taux de suppressions.

La commande ALTER TABLE ... MOVE permet de relocaliser les données d'une table non partitionnée ou d'une partition d'une table dans un nouveau segment, et optionnellement dans un nouveau tablespace. Cette commande permet également de modifier les attributs de stockage comme le paramètre PCTFREE.

Première étape : lancement de la commande ALTER TABLE ... MOVE

Exemple :

ALTER TABLE AUDITTRAIL MOVE

   PCTFREE 20

   PCTUSED 40

   STORAGE (INITIAL 20K NEXT 40K MINEXTENTS 2 MAXEXTENTS 20 PCTINCREASE 0);

Dans la commande ci-dessous, le paramètre PCTFREE est modifié pour passer de 10 à 20.

Seconde étape : reconstruction des indexes de la table ou de la partition en question

Déplacer une table sur un nouveau segment modifie les rowids de la table. Les indexes de la table sont alors marqués avec le statut UNUSABLE et les commandes DML accédant à la table en utilisant ces indexes rencontrent l'erreur ORA-01502. Aussi après une commande ALTER TABLE <table_name> MOVE, les indexes doivent être recréées ou reconstruits :

select index_name,

       index_type,

       owner, status

from  dba_indexes

where table_name='AUDITTRAIL'

Récupérer les index dont le statu est : UNUSABLE, puis exécuter la commande ci-dessous :

alter index index_name rebuild;

4- Conclusion

Les lignes migrées affectent les systèmes OLTP qui utilisent des indexes pour lire des lignes singulières. Les lignes réellement chaînées affectent les lectures d'indexes et les full scans de tables.

§  les migrations de lignes sont typiquement causées par des opérations UPDATE

§  les chaînage de lignes sont typiquement causées par des opérations INSERT

§  les commandes SQL qui crééent ou interrogent ces lignes migrées ou chaînées dégradent les performances à cause d'I/Os supplémentaires.

§  Pour supprimer les lignes chaînées ou migrées, utiliser une valeur plus élevée du paramètre PCTFREE en utilisant la commande ALTER TABLE MOVE.

Techniques de FLASHBACK d'ORACLE

1- Vue d’ensemble
Les techniques de flashback sont un ensemble de fonctionnalités proposées par Oracle qui permettent de voir l’état passé de données, ou de ramener une table ou la totalité de la base de données dans le passé.
Les fonctionnalités proposées sont les suivantes :

 - Flashback Query : permet de lire les données telles qu’elles étaient à un instant dans le passé (appary en version 9i).

- Flashback Version Query : permet de voir toutes les versions d’une ligne sur un certain intervelle de temps (apparu en version 10).

- Flashback Transaction Query) : permet de voir les modifications réalisées par une ou plusieurs transactions sur un certain intervalle de temps (apparue en version 10).

- Flashback Transaction : permet d’annuler les modifications d’une transaction, et de ses transactions dépendantes( apparue en version 11).

- Flashback Data Archive (Oracle Total Recall) : permet de conserver sur le long terme, toutes les modifications apportées à une table (apparue en version 11).

- Flashback Table : permet de ramener une table dans l état où elle était, juste avant sa suppression (apparue en version 10).

- Flashback Database : permet de ramener la totalité de la base de données dans l’état où elle était à un certain moment dans le passé (apparue en version 11).

Seule la fonctionnalité Flashback Query est disponible dans toutes les éditions de la base de données (et donc notamment en Standart Edition).
La fonctionnalité Flashback Data Archive, est une option de l’Entreprise Edition et nécessite donc, une licence supplémentaire.

Les autres fonctionnalités de Flashback nécessitent l’Entreprise Edition, mais sans option supplémentaire.
Les fonctionnalités de Flashback de requête (Flashback Query, Flashback Version Query et Flashback Transaction Query), et la fonctionnalité de Flashback Table, utilisent les informations d’annulation pour revenir en arrière. Le paramètre UNDO_RETENTION et le tablespace d’annulation doivent donc être correctement dimensionnés, si vous souhaitez pouvoir retourner loin dans le passé.

2- Niveau ligne
Flashback Query

Pour lire les données telles qu’elles étaient à un instant donné du passé, vous pouvez utiliser l’option AS OF sur une table présente dans la clause FROM d’une requête SELECT.

Syntaxe :
Nom_table AS OF { TIMESTAMP | SCN } expression

L’option TIMESTAMP permet de retourner à un instant donné du passé en indiquant une date et une heure ; dans ce cas, l’expression doit être de type TIMESTAMP. L’option  SCN permet de retourner à un instant donné du passé en indiquant un numéro SCN ; dans ce cas, l’expression doit être un nombre.


SQL> select   *   from   emp   where   empno = 7369;

     EMPNO ENAME      JOB              MGR    HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- --------------------------------
      7369 SMITH     CLERK                  7902    17/12/80               800                        20

SQL>  select to_char(sysdate, 'DD/MM/YYY HH24:MI:SS') "SYSDATE",
  2  dbms_flashback.get_system_change_number "SCN"
  3  FROM dual;

SYSDATE                  SCN
------------------ ----------
06/10/013 15:31:48    3446120



La fonction GET_SYSTEM_Change_NUMBER du package  DBMS_FLASHBACK retourne le numéro SCN courant. Il faut le privilège  EXECUTE sur le package pour l’utiliser.

Un peu plus tard :


SQL>  update emp set ename = 'ZAKARIA' where empno = '7369';
1 ligne mise à jour.
SQL> commit;
Validation effectuée.

SQL> select to_char(sysdate, 'DD/MM/YYY HH24:MI:SS') "SYSDATE",
  2  dbms_flashback.get_system_change_number "SCN"
  3  FROM dual;

SYSDATE                   SCN
------------------ ----------
06/10/013 15:38:41    3446364



Maintenant je vais retourner vers le passé avec la notion du temps :

SQL>  SELECT *  FROM EMP 
  2  AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' minute
  3  where empno = 7369;
  
  EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
--------- ---------- --------- ---------- -------- ---------- ---------- ----------
     7369 SMITH      CLERK               7902 17/12/80             800                              20



Ou bien avec la notion du SCN :


SQL> SELECT *  FROM EMP 
  2  AS OF SCN 3446120
  3  where empno = 7369;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH              CLERK           7902 17/12/80        800                                  20




NB : c'est un résumé de la partie FLASHBACK du livre "Administration ORACLE 11g" de OLIVIER HEURTEL.

Statistiques ORACLE, Package DBMS_STAT

La semaine dernière, et lors d’une mission de consulting chez un opérateur télécoms, on a confronté un problème de performance qui concerne les statistiques.

Bref, le problème est le suivant :

L’équipe de développement a  développé une application qui utilise une base de données Oracle 10g. Au niveau du serveur de recette l’application marche très bien, avec un temps de réponse raisonnable pour  la plus part des requêtes, mais une fois l’application est en production, oooops le temps de réponse est vraiment anormale (Exemple : une requête en recette : 15s, en prod : 3 minutes !!!!!!!!!!!!!).

NB : Avant mon intervention, ils ont bien vérifié que la configuration des 2 serveurs (Recette et Prod) ont les mêmes caractéristiques . Les 2 BD ont aussi la même volumétrie.

Avant de commencer d’inspecter les plans d’exécutions, il fallait voir les statistiques utilisées par Oracle. On sait parfaitement qu’Oracle se base sur les statistiques collectées pour déterminer le meilleur plan d’exécution. Si on arrive à avoir les mêmes statistiques que le serveur de recette, il nous suffit de rejouer son scénario et si le problème vient réellement d'un plan d'exécution différent, alors on va  pouvoir identifier la ou les requêtes en question.

Pour cela, on va sauvegarder les statistiques du serveur de recette :

1-      Créer une table pour sauvegarder les statistique : (USER à le schéma qui utilise l ’application, NOM_TABLE_STATS à Nom de la table des statistiques)

     exec DBMS_STATS.CREATE_STAT_TABLE (ownname=>'USER', stattab=>'NOM_TABLE_STATS');

2-      Alimenter la table NOM_TABLE_STATS par les statistiques du serveur de recette (A travers un Export du package DBMS_STATS)

    exec DBMS_STATS.EXPORT_SCHEMA('USER','NOM_TABLE_STATS');

3-      Au niveau du serveur PROD, importer les statistique déjà collectées :

-          Pour cela, il faut déjà supprimé les statistiques du PROD par la commande suivante :

exec DBMS_STATS.DELETE_SCHEMA_STATS('USER',FORCE=>TRUE);

-          Importer les statistiques :

 exec DBMS_STATS.IMPORT_SCHEMA_STATS('USER','NOM_TABLE_STATS');

Et voilà, maintenant on a les même statistiques au niveau des 2 serveurs, après les tests au niveau de la PROD, on a constaté que le temps de réponse est très raisonnable.

NB : Normalement c’est la première étape qu’on doit faire, il y a des cas où le problème persiste toujours, dans ce cas il faut revoir et analyser les plans d’exécutions pour voire s’il y a des full scan ou des mauvaises utilisation d’index ou bien autre chose.

Zakaria EL HAMDAOUI

Consultant Oracle