Utile ce SQL_ID !

Actuellement, nous travaillons à concevoir un outil de vérification de la performance des requêtes SQL entre une base de données Oracle 10g et 11g. Ceci nous permettra d'évaluer les impacts et d'être proactif dans l'identification, la configuration et, s'il y a lieu, dans les changements à apporter.

Pour débuter, nous collectons les requêtes et leurs statistiques qui sont exécutées par les utilisateurs via leurs applications sur la base de données 10g et nous stockons tout simplement ces informations dans des tables de travail. En faisant cela, nous sommes certains d'avoir des requêtes réelles, des statistiques claires et précises. Lorsque nous aurons migrés la base de données à Oracle 11g, la plupart des requêtes collectées précédemment seront sans aucun doute exécutées sur la nouvelle base de données. Lors de la conception de notre script, nous nous demandions comment nous pourrions retrouver et jumeler les requêtes identiques pour comparer les statistiques. À notre grande surprise, nous avons remarqué que le valeur contenue dans la colonne SQL_ID est identique entre les deux versions de bases de données. Vous comprendrez que ça vient de nous simplifier la vie et, pas juste un peu. Ce sera simple de regrouper les requêtes par SQL_ID et de les comparer puis faire ressortir celles ayant des valeurs de statistiques dont l'écart est prononcé.

Voici une démonstration :

SQL> conn system@A009
Enter password:
Connecté.
SQL> Select count(*) from sys.v_$parameter;

COUNT(*)
------------
263

SQL> select a.SQL_ID, a.executions, a.disk_reads, a.buffer_gets, a.sql_text
2 from v$sql a, all_users b
3 where a.parsing_user_id = b.user_id
4 and b.username = 'SYSTEM'
5 and a.sql_text = 'Select count(*) from sys.v_$parameter'
6 order by 1 desc;

SQL_ID EXECUTIONS DISK_READS BUFFER_GETS SQL_TEXT
------------- ------------ ------------ ------------ --------------------------------------
3yq5vhv923584 1 1 2 Select count(*) from sys.v_$parameter

SQL> conn system@mig11201
Enter password:
Connecté.
SQL> Select count(*) from sys.v_$parameter;

COUNT(*)
------------
343

SQL> select a.SQL_ID, a.executions, a.disk_reads, a.buffer_gets, a.sql_text
2 from v$sql a, all_users b
3 where a.parsing_user_id = b.user_id
4 and b.username = 'SYSTEM'
5 and a.sql_text = 'Select count(*) from sys.v_$parameter'
6 order by 1 desc;

SQL_ID EXECUTIONS DISK_READS BUFFER_GETS SQL_TEXT
------------- ------------ ------------ ------------ --------------------------------------
3yq5vhv923584 1 0 2 Select count(*) from sys.v_$parameter

Utilisation de la commande "tar"

La commande "tar" permet de regrouper plusieurs fichiers sous un même fichier. Cette commande fusionne les fichiers sans les compresser.

Concevoir un archive

tar -vcf nom_fichier_archive.tar nom_fichier

• tar: la commande
• vcf: les options
• v : fournit des informations lors de la conception de l’archive (optionnelle)
• c: Indique de créer un archive
• f: permet de spécifier le nom du fichier d’archive
• nom_fichier_archive.tar: nom donné au fichier d’archive
• nom_fichier : nom du dossier (ou fichiers) à archiver

Extraire le contenu d’un archive

La commande tar est aussi utilise pour extraire les fichiers contenu dans l’archive.
Extraire tout le contenu d’un archive

tar -vxf nom_fichier_archive.tar

• tar: la commande
• vxf: les options
• v: Afficher des informations lors de l’extraction (optionnelle)
• x: Indique d’extraire les fichiers
• f: Indique que l’archive contient des fichiers
• nom_fichier_archive.tar: Nom du fichier d’archive à traiter

Extraire certains fichiers d’un archive

tar -xvf nom_fichier_archive.tar "*.dbf"

Afficher le contenu d’un fichier d’archive

tar -tf nom_fichier_archive.tar

Manipuler avec soins car contient des paramètres standards, cachés et Hints

L'une des premières vérifications que je vous suggère fortement de faire lorsque vous effectuez de l'optimisation de requêtes est de vérifier la valeur des paramètres de bases de données qui ont été personnalisées (modifiées).

Dans plusieurs cas que j'ai rencontrés, surtout dans le cadre de projet de migration, certains paramètres avaient été modifiés pour contourner des problèmes (bugs) et, suite à la migration, ceux-ci sont la plupart du temps plus nécessaires.

Utiliser la commande "ALTER SESSION" pour ceux qui sont modifiable dynamiquement. Vous pourrez vérifier le comportement de vos requêtes avant d'appliquer le changement de façon permanente (ALTER SYSTEM) sur la base de données.

Autres conseils, évitez de modifier les paramètres de base de données, surtout ceux cachés. Vous aurez des problèmes lors des futures migrations. Si vous n'avez pas le choix, je vous conseille de bien documenter leur utilisation.

C'est la même situation pour les HINTS de requêtes, ce n'est que des diachylons et… souvenez-vous qu'un diachylon fini toujours par se décoller :)

Objets sans statistiques ou désuètes

Voici un script qui permet d'afficher les objets dont les statistiques sont absentes ou désuètes (stale) :

SET SERVEROUTPUT ON

DECLARE
vListeObjt dbms_stats.ObjectTab;
BEGIN
-- Stats désuetes
dbms_output.put_line('-- **********************');
dbms_output.put_line('-- Statistiques désuètes');
dbms_output.put_line('-- **********************');
dbms_stats.gather_database_stats(objlist=>vListeObjt, options=>'LIST STALE');
FOR i in vListeObjt.FIRST..vListeObjt.LAST
LOOP
dbms_output.put_line(vListeObjt(i).ownname || '.' || vListeObjt(i).ObjName || ' (' || vListeObjt(i).ObjType || ' ' || vListeObjt(i).partname||' )');
END LOOP;
-- Sans Stats
dbms_output.put_line('-- **********************');
dbms_output.put_line('-- Sans statistique');
dbms_output.put_line('-- **********************');
dbms_stats.gather_database_stats(objlist=>vListeObjt, options=>'LIST EMPTY');
FOR i in vListeObjt.FIRST..vListeObjt.LAST
LOOP
dbms_output.put_line(vListeObjt(i).ownname || '.' || vListeObjt(i).ObjName || ' (' || vListeObjt(i).ObjType || ' ' || vListeObjt(i).partname||' )');
END LOOP;
END;
/

Comportement des statistiques systèmes en mode RAC

Lors de la collecte des statistiques systèmes d'un environnement RAC, on peut déclencher la collecte à partir de n'importe quelle instance. Tout s'effectue correctement... À vrai dire, pas exactement car les autres instances attachées au cluster ne prennent pas en compte les nouvelles valeurs.

Je me suis aperçu de ce problème quand une requête est devenue gourmande. Le plan d'exécution de la requête était complètement différent sur 2 instances. En me connectant sur l'une des instances du cluster, j'ai vérifié les valeurs des statistiques (select * from sys.aux_stats$). C'est à ce moment que j'ai remarqué qu'elles avaient été régénérées récemment et l'un de mes collègues me l'a confirmé. J'ai aussi eu la confirmation que la lenteur avait été observée depuis la date de la régénération des statistiques. Donc, je pouvais déduire que la collecte de statistiques systèmes était l'élément déclencheur.

Que se soit sur l'une ou l'autre des instances du cluster, les mêmes statistiques sont supposées être utilisées dont je suspecte que le problème soit au niveau de la mémoire ou d'une certaine cache. J'ai tenté une simple purge du "shared pool" et celle des données mais sans succès. Comme à l'habitude, j'ai fait une petite recherche sur Metalink et effectivement, il y a un problème de répertorier sur la base de données 10.2.0.3. (ID 7645777.8) concernant un plan d'exécution différent sur des instances RAC. La solution : Recycle the instances... Qu'est-ce que cela veut dire exactement ?!? J'imagine que c'est l'équivalent d'un redémarrage d'instance et, si c'est le cas, et bien, je ne peux pas me le permettre. Vu que ce bug concerne les statistiques systèmes alors, j'ai décidé de pousser la vérification en influençant l'optimisateur pour qu'il n'utilise pas les statistiques liées au CPU. Pour ce faire, j'ai utilisé le hint "NO_CPU_COSTING". Les résultats furent concluants, j'ai obtenu le même plan d'exécution que sur l'instance où la requête est optimale.

Tant qu'à pousser, j'ai décidé d'effectuer une trace de l'optimisateur sur l'exécution de la requête sur chacune des instances avec la commande :

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'

Les 2 fichiers de trace ont démontrés que les statistiques systèmes sont différentes donc ça confirme que l'optimisateur n'utilise pas les mêmes valeurs d'une instance à l'autre... Inquiétant ?

Après réflexion, je me suis dit, pourquoi ne pas répéter ou presque les mêmes étapes de collecte de statistiques systèmes mais cette fois-ci, en étant connecté sur l'instance n'ayant pas le même comportement ou si vous préférez, le même plan d'exécution. Alors, j'ai effectué ceci :

-- Établir une connexion sur l'instance qui n'utilise pas les nouvelles valeurs
connect sys@inst1 as sysdba

-- Prendre une copie des statistiques actuelles
Drop table stats_bkp;
EXECUTE DBMS_STATS.CREATE_STAT_TABLE ('SYS','stats_bkp','SYSAUX');
EXECUTE DBMS_STATS.EXPORT_SYSTEM_STATS (stattab => 'stats_bkp', statid => 'stats_bkp', statown => 'SYS');
select * from sys.stats_bkp;

-- Charger les valeurs
execute DBMS_STATS.DELETE_SYSTEM_STATS;
EXECUTE DBMS_STATS.IMPORT_SYSTEM_STATS (stattab => 'stats_bkp', statid => 'stats_bkp', statown => 'SYS');
select * from sys.aux_stats$;

-- Initialiser les espaces mémoires
Alter system flush shared_pool;
Alter system flush buffer_cache;

Et bien oui, ça fonctionne! La requête a maintenant le même plan d'exécution que sur l'autre instance. Stop! Le problème s'est déplacé sur l'autre instance... oups! Ok, alors, je vais refaire la même chose, mais cette fois-ci sur l'instance où le problème s'est déplacé. Suite à l'exécution des commandes précédentes, le plan d'exécution est redevenu celui d'auparavant et, sans impacter le plan d'exécution sur l'autre instance.

Je crois qu'un redémarrage de l'instance aurait pu régler le problème sauf que lorsque nous sommes sur un environnement de production, cela n'est pas toujours une solution et ce, même si c'est un environnement RAC à moins que vous preniez le temps de déplacer tous les services vers une autre instance puis que vous patientez jusqu'à ce que toutes les sessions se soient reconnectés vers la nouvelle instance.

Les caprices d'Oracle EM

S'il vous arrive de rencontrer l'erreur "numeric or value error" lorsque vous naviguez à travers l'outil d'Oracle Enterprise Manger (Grid Control)... et possiblement la version Stand Alone (database Control), pensez à tout simplement modifier la langue utilisée par votre fureteur.

J'ai rencontré à maintes reprises ce problème et le simple fait de mettre la langue à "Anglais" vous permet de continuer ou plutôt contourner ce problème.

Statut de la capture « INITIALIZING » ou « DICTIONNARY INITIALIZING »

En effectuant la commande suivante pour vérifier le processus de capture :

select * from v$streams_capture;

Si le statut (STATE) indique "INITIALIZING" ou "DICTIONARY INITIALIZATION", assurez-vous que tout les fichiers d'archive nécessaire au fonctionnement de Stream sont présent. Pour ce faire, exécuter le script "Health Check" (Streams Configuration Report and Health Check Script [ID 273674.1]) .

Suite à l'exécution du script, chercher la section intitulé "++ Minimum Archive Log Necessary to Restart Capture ++". Cette section vous indiquera quel fichier d'archive vous devez avoir sur disque pour que la capture puisse reprendre.

Voici un extrait du résultat du script "Health Check" :

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

++ Minimum Archive Log Necessary to Restart Capture ++
Note: This query is valid for databases where the capture processes exist for the same source database.

Capture will restart from SCN 17548223670 in the following file:
/uhm004_u04/home/dba/oracle/admin/R002/arch/R002_723971581073678.arc (04:53:41 02/15/10)
PL/SQL procedure successfully completed.

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

Si vous remarquez qu'il vous manque des fichiers d'archive, vous devrez procéder à une restauration des fichiers d'archive via Recovery Manager.

Voici comment procéder :

rman target sys/xxxx@BD_CIBLE catalog rman/xxxx@BD_CATALOG

RMAN> run {
allocate channel ch01 type 'SBT_TAPE';
restore archivelog sequence between 72390 and 72414;
}

Dans le cas présent, la restauration s'est effectué en précisant des numéros de séquences mais plusieurs autres types de restauration sont possibles tels que par date, par # scn, etc...
Suite à la restauration des fichiers d'archive, le processus de capture reprendra graduellement. Vérifier à quelques reprises le statut du processus de capture, il changera de valeurs, puis il restera dans l'état "CAPTURING CHANGES".

select * from v$streams_capture;

Assurez-vous qu'il n'y a pas d'erreur sur l'ensemble des processus impliqué dans la réplication Oracle Streams :

SELECT CAPTURE_NAME, STATUS, ERROR_MESSAGE, ERROR_NUMBER
FROM DBA_CAPTURE;

SELECT PROPAGATION_NAME, STATUS, ERROR_MESSAGE, ERROR_DATE
FROM DBA_PROPAGATION;

SELECT APPLY_NAME, STATUS, ERROR_MESSAGE, ERROR_NUMBER
FROM DBA_APPLY;

SELECT APPLY_NAME, SOURCE_DATABASE, LOCAL_TRANSACTION_ID,
ERROR_NUMBER, ERROR_MESSAGE, MESSAGE_COUNT
FROM DBA_APPLY_ERROR;