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
Aucun message portant le libellé Statistique. Afficher tous les messages
Aucun message portant le libellé Statistique. Afficher tous les messages
mercredi 21 avril 2010
mercredi 24 mars 2010
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;
/
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.
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.
mardi 3 février 2009
Collecte de statistiques système
La collecte des statistiques système est malheureusement très souvent oubliée par les administrateurs et pourtant, elle ne le devrait pas car l'optimiseur effectue un calcul des coûts plus adéquat lorsque celles-ci sont disponibles.
Les statistiques Système procure à l'optimiseur des informations près de la réalité sur la façon dont votre système se comporte réellement. Ces informations permettent à l'optimiseur de produire une meilleure déduction entre les temps estimés et réelles pour l'exécution d'une requête.
Les Statistiques Système contiennent les détails qui permettent à l'optimiseur de comparer les coûts entre les entrées / sorties (I/O) et ceux liés aux processeurs (CPU).
Lorsque vous commencer à utiliser les statistiques système, l'optimiseur devient plus sensible au moment de choisir entre une lecture séquentielle (table scan) et l'utilisation d'index, car le coût d'une lecture multiblocs pour un accès séquentiel comportera des informations sur le temps d'exécution. Par exemple, Oracle peut décider qu'une lecture séquentielle d'une table serait plus rapide en se basant uniquement sur la vitesse de lecture de blocs (simple et multi).
Différentes façons de collecte peuvent être effectuées. Personnellement, j'aime bien avoir le contrôle alors, j'opte pour la méthode manuelle.
-- Établir une connexion avec SYS
connect / as sysdba
-- Créer la table de collecte de statistiques
EXEC DBMS_STATS.DELETE_SYSTEM_STATS('stats','stats','SYS');
EXEC DBMS_STATS.CREATE_STAT_TABLE ('SYS','stats','SYSAUX');
-- Démarrer la collecte en mode manuelle
EXEC DBMS_STATS.GATHER_SYSTEM_STATS -
(gathering_mode=>'START', -
stattab => 'stats', -
statid => 'stats', statown => 'SYS');
-- Vérifier si la collecte est activée
column statid format a7
column c1 format a13
column c2 format a16
column c3 format a16
select STATID, C1, C2, C3,to_char(sysdate,'MM-DD-YYYY HH24:MI:SS')
from stats;
-- Arrêt de la collecte
-- ATTENTION: Avant d'arrêter, assurez-vous d'avoir laisser fonctionner la collecte
-- pendant une période significative
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS -
(gathering_mode=>'STOP', -
stattab => 'stats', -
statid => 'stats', -
statown => 'SYS');
-- Prendre une copie des statistiques actuelles
EXEC DBMS_STATS.CREATE_STAT_TABLE('SYS','stats_bkp','SYSAUX');
EXEC DBMS_STATS.EXPORT_SYSTEM_STATS -
(stattab => 'stats_bkp', -
statid => 'stats_bkp', -
statown => 'SYS');
select * from sys.stats_bkp;
-- Activer les statistiques
EXEC DBMS_STATS.IMPORT_SYSTEM_STATS -
(stattab => 'stats', -
statid => 'stats', -
statown => 'SYS');
-- Afficher les nouvelles statistiques
select * from sys.aux_stats$;
Et, voilà, c'est assez simple n'est-ce pas !
Je vous conseille fortement d'utiliser les statistiques système. La mise en fonction de ces statistiques doivent être effectuée avec précaution. Pour minimiser les impacts et les surprises, effectuer des essais de performance sur un environnement dédié à des tests et comparer vos résultats.
Les statistiques Système procure à l'optimiseur des informations près de la réalité sur la façon dont votre système se comporte réellement. Ces informations permettent à l'optimiseur de produire une meilleure déduction entre les temps estimés et réelles pour l'exécution d'une requête.
Les Statistiques Système contiennent les détails qui permettent à l'optimiseur de comparer les coûts entre les entrées / sorties (I/O) et ceux liés aux processeurs (CPU).
Lorsque vous commencer à utiliser les statistiques système, l'optimiseur devient plus sensible au moment de choisir entre une lecture séquentielle (table scan) et l'utilisation d'index, car le coût d'une lecture multiblocs pour un accès séquentiel comportera des informations sur le temps d'exécution. Par exemple, Oracle peut décider qu'une lecture séquentielle d'une table serait plus rapide en se basant uniquement sur la vitesse de lecture de blocs (simple et multi).
Différentes façons de collecte peuvent être effectuées. Personnellement, j'aime bien avoir le contrôle alors, j'opte pour la méthode manuelle.
-- Établir une connexion avec SYS
connect / as sysdba
-- Créer la table de collecte de statistiques
EXEC DBMS_STATS.DELETE_SYSTEM_STATS('stats','stats','SYS');
EXEC DBMS_STATS.CREATE_STAT_TABLE ('SYS','stats','SYSAUX');
-- Démarrer la collecte en mode manuelle
EXEC DBMS_STATS.GATHER_SYSTEM_STATS -
(gathering_mode=>'START', -
stattab => 'stats', -
statid => 'stats', statown => 'SYS');
-- Vérifier si la collecte est activée
column statid format a7
column c1 format a13
column c2 format a16
column c3 format a16
select STATID, C1, C2, C3,to_char(sysdate,'MM-DD-YYYY HH24:MI:SS')
from stats;
-- Arrêt de la collecte
-- ATTENTION: Avant d'arrêter, assurez-vous d'avoir laisser fonctionner la collecte
-- pendant une période significative
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS -
(gathering_mode=>'STOP', -
stattab => 'stats', -
statid => 'stats', -
statown => 'SYS');
-- Prendre une copie des statistiques actuelles
EXEC DBMS_STATS.CREATE_STAT_TABLE('SYS','stats_bkp','SYSAUX');
EXEC DBMS_STATS.EXPORT_SYSTEM_STATS -
(stattab => 'stats_bkp', -
statid => 'stats_bkp', -
statown => 'SYS');
select * from sys.stats_bkp;
-- Activer les statistiques
EXEC DBMS_STATS.IMPORT_SYSTEM_STATS -
(stattab => 'stats', -
statid => 'stats', -
statown => 'SYS');
-- Afficher les nouvelles statistiques
select * from sys.aux_stats$;
Et, voilà, c'est assez simple n'est-ce pas !
Je vous conseille fortement d'utiliser les statistiques système. La mise en fonction de ces statistiques doivent être effectuée avec précaution. Pour minimiser les impacts et les surprises, effectuer des essais de performance sur un environnement dédié à des tests et comparer vos résultats.
S'abonner à :
Messages (Atom)