Hello !

Mais non, OS Watcher n'est pas un nouveau régime à la mode....

Il vous est surement déjà arrivé q'un de vos charmants clients  vous interpelle à 15h en vous demandant pourquoi "c'était lent" à  09:00...

OS Watcher ne va pas donner une réponse à votre question, mais va permettre  d'avoir une idée de la santé du système à un instant t.

OS Watcher est un petit utilitaire gratuit fourni par Oracle. D'ailleurs lors d'ouverture de TAR au support Oracle, il est fréquent d'avoir à le mettre en place. Il en existe une version Windows, mais l'aricle décrit la mise en place sous Linux.

Ou trouver OS Watcher ?

Comme indiqué précédemment, on le trouve sur Metalink et plus précisemment via la note  OS Watcher Black Box User Guide [ID 301137.1]. En bas de la note vous aurrez deux liens:

• Un pour télécharger le tool.
• Un pour télécharger le guide d'utilisation.

Comment installer OS Watcher ?

En admettant que vous n'ayez pas poussé la curiosité jusqu'à la lire le guide d'utilisation, il suffit de suivre les quelques étapes suivantes.

• Copier le fichier fraichement téléchargé (oswbb404.tar) sur le serveur à surveiller.
• Le décompresser : tar xfv oswbb404.tar

Et voila l'installation est terminée.... (on n'a connu plus compliqué)

nous avons maintenant un repertoire oswbb qui contient un ensemble de scritps & repertoires.

Comment démarrer & stopper la surveillance ?

Pour démarrer la surveillance, il faut juste lancer le script startOSWbb.sh avec deux paramètres.

• L'intervalle de temps en secondes pour la prise d'informations.
• La durée en heures de retention des informations.

Ex: Pour avoir une prise d'information toutes les minutes pendant 48h, il faudra lancer la commande:

./startOSWbb.sh 60 48

Si vous voulez lancer la surveillance en tâche de fond et que celle ci continue même en cas de fermeture de votre terminal préféré (putty.exe ou autres)

nohup ./startOSWbb.sh 60 45 &

Vous pouvez vérifier que la surveillance est en cours via la commande suivante

ps -ef |grep OSWatcher

Et pour stopper toute cela ?

rien de plus simple.

./stopOSWbb.sh

Ou récuperer les informations collectées ?

Et bien encore une fois rien de bien compliqué. Dans notre répertoire d'installation nous avons maintenant un repertoire archive qui contient lui même des repertoires propres aux types de collectes (oswiostat, oswmpstat ,...)

Comme je le disais en début d'article OS Wacther ne va pas résoudre vos problèmes, mais il va vous permettre de savoir quel était l'etat du serveur en CPU / IO, ... ou les process qui tournaient à ce moment. Ce qui reste un bon début pour une investigation.

@+

LAO

Bonsoir,

Alors ce soir j'ai décidé de vous parler des indexes (les B-TREE). Comme vous le savez peut-être leur nom vient du fait que si l'on schématise l'index on obtient un arbre avec des feuilles d'ou la terminologie.

Globalement, lorsque l'on parle de maintenance d'indexes et donc de reconstruction d'indexes, on obtient de nombreuses variantes. Juste pour rire, voici les plus fréquentes:

• "Ca ne sert à rien de reconstruire les indexes, car aucun impact sur les performances"
• "Il faut reconstruire les indexes toutes les semaines..."
• Et puis on ceux qui se demandent pourquoi reconstruire les indexes, puisqu'il est connu de facon notoire qu'ORACLE fait tout et tout seul. Faut bien justifier qu'un DBA ne sert à rien.

Si vous avez déjà lu certains articles sur ce blogs, vous devez commencer à savoir que je suis partisan des démonstrations et que surtout vous ne devez pas vous faire dicter votre façon d'administrer vos bases par des "idées reçues". SI vous faites quelque chose il est bon de savoir pourquoi vous le faites.

 
Pour cela, je vais prendre une table T_4 avec deux colonnes de type number (i et j).
Et je vais créer une clé primaire sur i, après avoir renseigné la table avec quelques 2 millions de lignes.
Ensuite, je vais supprimer aléatoirement un certain nombre de valeur pour simuler une activité sur la table.

CREATE TABLE T_4 (i NUMBER, j NUMBER);
ALTER TABLE T_4 ADD CONSTRAINT PK_T4 PRIMARY KEY(i);

/*Remplissage */
BEGIN
   FOR z IN 1..2000000 LOOP
      INSERT INTO T_4 VALUES (z,TRUNC(DBMS_RANDOM.VALUE(1,345000)));
   END LOOP;
   COMMIT;
END;
/

/*suppression aléatoire de lignes */
DECLARE ligne_a_supprimer NUMBER;
BEGIN
   FOR z  IN 1..1000000 LOOP
      ligne_a_supprimer := TRUNC(DBMS_RANDOM.VALUE(1,2000000));
      DELETE FROM T_4 WHERE i=ligne_a_supprimer;
   END LOOP;
   COMMIT;
END;
/

EXEC DBMS_STATS.Gather_Table_Stats(user,'T_4',CASCADE=>TRUE);

A la fin des ces manipulations, il me reste un peu plus d'1.2 millions de lignes dans ma table.


Il ne faut pas oublier qu'un index a pour objectif d'accélere l'accès au données. Seulement lors de la suppression de lignes l'index n'est pas reconstruit et par conséquent l'indexes conserve des feuillezs avec des plages de valeurs n'existant plus. C'est ce genre d'opération qui va entrainer une fragmentation de notre indexes et par conséquent en réduire la performance.


Ex: Je veux compter le nimbre de lignes ayant une valeur pour i comprise entre 230000 et 612000. 

SELECT COUNT(*) FROM T_4 WHERE i BETWEEN 230000 AND 612000;

La requete n'est pas complexe. Oracle va effectuer un IRS (Index Range Scan) sur la clé primaire.
Le plus simple est encore d'ouvrir une session SQL + en prenant soin d'activer AUTOTRACE, et puis également le chronomètre.

SET AUTOTRACE ON
SET TIMING ON
SELECT COUNT(*) FROM T_4 WHERE i BETWEEN 230000 AND 612000;

Résultat:




Les informations que nous avons ici, ne nous indiquent pas si l'indexe est fragmenté. Il revient au DBA de surveiller ses bases et d'automatiser certains traitements pour surveiller quels sont les indexes à reconstruire. En effet, on ne peut pas se satisfaire de reconstruire tous les indexes de façon périodique. Je concède que sur des bases à faible volumétrie, cela n'est pas un souci, mais sur des tables de plusieurs dizaines ou centaines de millions de lignes,cela pourrait vite devenir problématique.
Dans le cas présent, je vais faire les manipulations de façon manuelle, et (peut-être) dans un post prochaine, je publierai un script pour automatiser la chose.

Avant tout pour savoir, si un index mérite d'être reconstruit,il faut avoir des informations dessus et pour cela nous allons devoir analyzer l'indexe pour récupérer des informations que malheureusement nous n'avons pas dans la vue USER_INDEXES.

ANALYZE INDEX PK_T4 VALIDATE STRUCTURE;

La subtilité arrive ici. Oracle stocke les informations relatives à cette analyze dans la vue INDEX_STATS. Et le souci, c'est que cette vue ne contient que les informations relative à la dernière instruction "ANALYZE .... VALIDATE STRUCTURE;" et donc cette vue renverra toujours qu'une seule ligne !

Cette vue dispose d'un certain nombre de colonne, et pour ceux que cela intéresse, je vous invite à vous rendre sur la documentation en ligne Oracle:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4216.htm#sthref2014

En ce qui me concerne uniquement certaines colonnes vont m'interesser.
Effectuons la requête suivante:

Si j'ai choisis ces colonnes ce n'est evidement pas par hasard.
En effet, il existe certaines règles indiquant que :
-1/ Que si la valeur de "height" qui représente la profondeur de l'arbre de l'index est supérieur à 4, alors il faut reconstruire l'indexe.
-2/ Que si le ratio entre le nombre de lignes supprimées dans les pages d'indexe par le nombre total de lignes dans les pages d'indexe est supérieur de 20 à 30%, alors il faut reconstuire. Il n'y pas de valeur fixe, et cela dépendra certainement de votre volumétrie, de votre matériel, et des requêtes impliquées dans l'utilisation de ces indexes.
La règle d'or reste : Tester, mesurer avant et après et apprenez à dompter votre base.


Dans mon cas j'obtiens à un ratio de l'ordre de 36%, je décide donc de reconstruie l'index.

ALTER INDEX PK_T4 REBUILD;

Dans la foulée j'enchaine une nouvelle analyze 

ANALYZE INDEX PK_T4 VALIDATE STRUCTURE;

Et sans surprise, j'obtiens un ratio de 0 puisque je viens de reconsruire l'index.
Reste à vérifier l'impact sur les performances. Afin de ne pas fausser un eventuel mise en cache des données, je me connecte en system pour vider le buffer cache.

CONNECT SYSTEM/*****
ALTER SYSTEM FLUSH BUFFER_CACHE;

 
 Puis je me reconnecte en owner:

SET AUTOTRACE ON
SET TIMING ON
SELECT COUNT(*) FROM T_4 WHERE i BETWEEN 230000 AND 612000;

Résultat:



Que constate t-on ?

•  Un gain de temps (0.15 s contre 0.26 s)
•  Et de facon plus significative 487 accès disques contre 801 avec notre indexe fragmenté. Car maintenant nous pouvons parlé d'indexe fragmenté.

Dans notre exemple nous avons utilisé une petite table avec uniquement deux colonnes et une clé primaire. Mais il n'est pas rare dans une base d'avoir des centaines de tables qui subissent des insertions, et des suppressions de façon fréquente. Si l'on ajoute cela à des requêtes surement bien plus compliquées que celle que je viens de faire, on peut obtenir une application qui rame....
Gardez cela à l'esprit. Il arrive parfois (en tant qu'editeur) que l'on demande un dump à notre client qui a des problèmes de performances que l'on n'arrive pas à reproduire. Le soucis, c'est que lorsque vous allez importer, vous allez forcement reconstruire les indexes et donc faire disparaitre une éventuelle source du problème ! 

LAO. 

• Une table Reponse_1 
• 115 millions de lignes
• 10 colonnes q1 à q10 correspondant à des notes de 0 à 10 pour une grille de 100 formulaires dans 25 pays possibles.
  
  
   

Par ailleurs, je m'étais arreté à décembre 2007, mais bien evidemment tous les jours les statistiques arrivent de part nos 25 pays et continuent d'alimenter notre table.

Pas besoin d'être fort en mathématiques pour comprendre:
 décembre 2007=115 millions de lignes
 Années 2008 => Plus d' UN MILLIARD DE LIGNES.


L'idée étant de conserver bien sur l'année en cours + une année d'archive: Au bas mot 2,5 milliards de lignes !!

Les intérrogations seront essentiellement par date et par quesionnaire.
Il convient de bien réfléchir à comment stocker ces informations car les manipulations de centaines de millions de lignes ne s'effectuent pas de la même facon que pour une table de quelques milliers de lignes.

Tout devient critique (Statistiques, sauvegarde, maintenance des indexes,...)
Pour les tables à forte volumétrie ORACLE a bien sur pensé à nous, et il a créé le partitionnement.

Cela va revenir à découper notre table selon des critères bien précis.
Il existe trois type de partitionnement:

• Partition par intervalle (BY RANGE)
• Partition par liste (BY LIST)
• Partiyion par haschage (BY HASH)

Dans mon cas, Etant donnée que je vais interroger la table par date et par questionnaire, il peut être jucieux d'effectuer un partitionnement par intervalle sur la date, suivi d'un sous partitionnement par questionnaire. Pour le sous partitionnement, on utilisera un partitionnement par hash. 
Afin de permettre à ORACLE d'utiliser au mieux son algorythme de répartition des valeurs, il convient de choisir un nombre de sous partitions qui soit une puissance de 2( 4,8,16,32,64,128,...)

En général les deux raisons qui poussent  à effectuer du partionnement sont:

1. Une administration plus simple.
2. Des gains de performance.

Pour ce qui est de l'administration on en reparlera dans un prochain article.

Pour les performances, c'est assez simple à comprendre.
Admettons que je fasse une requête du type

Si ma table est partitionnée par date et sous partitionné par type de formulaire, ORACLE va utiliser cette information pour aller directement dans la partition concernée et va donc travailler sur un sous ensemble de la table. 

Assez parlé, crééons une nouvelle table qui prennent en compte le partitionnement.

(
q1 number,
q2 number,
q3 number,
q4 number,
q5 number,
q6 number,
q7 number,
q8 number,
q9 number,
q10 number,
id_pays number,
id_formulaire number,
date_questionnaire number)
PARTITION BY RANGE(date_questionnaire) SUBPARTITION  BY HASH(id_formulaire) SUBPARTITIONS  64
(  

  PARTITION P_20071201 VALUES LESS THAN (20071202),
  PARTITION P_20071202 VALUES LESS THAN (20071203),
  PARTITION P_20071203 VALUES LESS THAN (20071204),
  PARTITION P_20071204 VALUES LESS THAN (20071205),
  PARTITION P_20071205 VALUES LESS THAN (20071206),
  PARTITION P_20071206 VALUES LESS THAN (20071207),
  PARTITION P_20071207 VALUES LESS THAN (20071208),
  PARTITION P_20071208 VALUES LESS THAN (20071209),
  PARTITION P_20071209 VALUES LESS THAN (20071210),
  PARTITION P_20071210 VALUES LESS THAN (20071211),
  PARTITION P_20071211 VALUES LESS THAN (20071212),
  PARTITION P_20071212 VALUES LESS THAN (20071213),
  PARTITION P_20071213 VALUES LESS THAN (20071214),
  PARTITION P_20071214 VALUES LESS THAN (20071215),
  PARTITION P_20071215 VALUES LESS THAN (20071216),
  PARTITION P_20071216 VALUES LESS THAN (20071217),
  PARTITION P_20071217 VALUES LESS THAN (20071218),
  PARTITION P_20071218 VALUES LESS THAN (20071219),
  PARTITION P_20071219 VALUES LESS THAN (20071220),
  PARTITION P_20071220 VALUES LESS THAN (20071221),
  PARTITION P_20071221 VALUES LESS THAN (20071222),
  PARTITION P_20071222 VALUES LESS THAN (20071223), 
  PARTITION P_20071223 VALUES LESS THAN (20071224),
  PARTITION P_20071224 VALUES LESS THAN (20071225),
  PARTITION P_20071225 VALUES LESS THAN (20071226),
  PARTITION P_20071226 VALUES LESS THAN (20071227), 
  PARTITION P_20071227 VALUES LESS THAN (20071228),
  PARTITION P_20071228 VALUES LESS THAN (20071229),
  PARTITION P_20071229 VALUES LESS THAN (20071230),
  PARTITION P_20071230 VALUES LESS THAN (20071231),
  PARTITION P_20071231 VALUES LESS THAN (20080101)
) ;

Et oh surprise, résultat

• Quand utiliser tel type de partitionnement ?
• Comment indiquer un tablespace pour les partitions et les sous partitionnement
• Quels sont les types de combinaisons possibles ?
• Les indexes partitionnés
• Que se passe t-il pour les indexes 
  • Sont-il liés à la table ou à la partition ?
• Quelles sont les opérations possibles sur partition ?
• ... et bien d'autres encore

Et bien je réponds, qu'avec ce sujet, j'ai de quoi remplir mon blog pour novembre !!!!

LAO. 

Bonjour,

Les performances, toujours les performances... Le nerf de la guerre pour beaucoup d'entre nous.
Mais comme pour un virus, il est toujours plus facile de le traiter quand on sait à qui on n'a à faire
Au passage, je rappelle qu'il existe des outils fournis par ORACLE (Statspack, AWR,...)
Et jr rappelle également qu'il existe une règle (non absolue) qui veut que 80 % des problèmes de performance
proviennent de requêtes mal écrite et donc consommatrices.

Il serait donc interessant de pouvoir extraire très rapidement ces fameuses requêtes afin de pouvoir
les traiter.

Oracle, notre bienfaiteur nous donne le moyen d'interroger une vue pour cela.
Il s'agit de v$sqlarea.

Bien évidemment, il faut se connecter avec un utilisateur ayant des droits sur cette vue. Si vous n'avez pas accès au compte system, demandez gentillement à votre DBA de vous créer un utilisateur ayant accès à ce type de vue.
Normalement, il devrait le faire puisque à l'avenir ca lui fera moins de travail.

Requête d'intérrogation:

Pour executer cette requête, je vous conseille d'utiliser ORACLE SQL Developer (outil gratuit téléchargable sur le site d'oracle)
http://www.oracle.com/technology/software/products/sql/index.html

Vous pouvez utiliser  tout autre outil capable de lire facilement les clob.

Quelles informations tirons nous de cette requête ?

- Le text (entier) de la requête sql
- Le temps cpu en s
- Le temps d'execution en s
- Le nombre de lignes retournées.
- Le nombre de blocs accédés en mémoire.
- Le nombre de lectures disques
- Le nombre de fois ou cette requête a été executé.

Par ailleurs, j'ai inclus une clause where sur la colonne Parsing_Schema_Name afin d'isoler les requêtes d'un user ORACLE particulier.
Ce qui  nous évite toutes les requêtes SYSTEM.

Quelques pistes:

- A partir du résultat, vous pouvez facilement extraire les requêtes qui consomment anormalement.
D'un point de vue "editeur de logiciel", ce type de requête permet de mettre facilement certains scheminements "bizarres" pour arriver à un résultat.

- Indépendamment du temps d'executions, il peut être interessant de comparer le cpu_time et le elapsed_time.
Un tros gros écart entre les deux signifie des délais d'attentes qui peuvent être divers (accès disques, verrous,...)

- On peut également surveiller les requêtes qui sont executées de nombreuses fois (parfois plusieurs millions de fois).
si il s'agit de requetes sur des tables statitques, on pourra gerer cette information en cache (au niveau logiciel).

- Toujours dans le cas de requêtes avec jointures executées de très nombreuses fois sur des tables statiques. On pourra créer des vues materialisées pour éliminer le cout de la jointure.

- Si pour une application donnée on s'est fixé des objectifs de performance (un doux rêve), on peut rechercher toutes les requêtes qui ne correspondent pas à ces critères de performance (ex: toutes les requêtes ayant un temps cpu > 1s).

- un nombre elevé de buffer_gets + disk_reads, peut indiquer un scan complet d'une table volumineuse et donc peut être l'absence d'indexes ou de statistiques qui ne sont pas à jour sur cette table.

Comme vous pouvez le voir, cette simple petite requête permet de se poser pas mal de questions et apportent parfois des réponses.

L'optimisation est un combat de tous les jours.
N'abandonnez pas, ne faiblissez pas !

LAO.

Create index idx_q10_r2 on reponse_2(q10);

---

Faut compter quelques minutes par indexes ... et auxquelles il faut ajouter quelques GO... supplémentaires.
Et pour être plus précis, il faut 1,7 GO par index multiplié par 10 ce qui nous fait 17 GO d'indexes.

Voyons au moins si le jeu en vaut la chandelle.

Paradoxalement les statistiques nous indiquent une baisse conséquente des consistent gets (174000) ainsi que des lectures physiques (189000) le temps de'execution est passé lui d'une quarentaine de secondes à presque deux minutes !

les statistiques nous indiquent également qu'il y a eu quelques 1000 appels recursifs (certainement des appels systemes[mais pour quoi faire ?].

Le plan d'execution nous montre que nos indexes sont bien utilisés.

Alors que faire ? Gacher 17 GO d'index pour un résultat qui devrait être meilleur mais qui ne l'est pas !
Ce qui coute en temps ce sont les "appels récursives" qui sont certainement des appels systemes.
Mais pourquoi faire ?

Le plan d'execution nous indique bien que les indexes sont utilisés, mais qu'ORACLE effectue une conversion des ROWID en BITMAP !
Du sex dans ORacle ? Non mais un type d'index : Les index bitmap !

3 ème hypothèse.

Si oracle décide de faire une conversion de type bitmap, peut être avons nous interet à créer directement des indexes de type bitmap.

Qu'est ce qu'un index bitmap ? :
ex: Créeons un index bitmap sur q1.

---

CREATE BITMAP INDEX idx_q1_r1 ON Reponse_1(q1);

---

1ere constation : La création est beaucoup plus rapide que pour un index classique.

Comme ca marche ?
Q1 peut prendre 11 valeurs (de 0 à 10). Oracle va alors créer un tableau de valeur de 0 à 10 et pour chaque rowid (numéro de sécu d'une ligne dans la table, et accés direct à l'info) va indiquer par 0 ou 1 pour chaque valeur. On peut représenter cela par un tableau. Admettons que pour les premières lignes de la tables, les notes à q1 soient 2,3,4,0,0,1,5,8,9,1,6...

	2	3	4	0	0	1	5	8	9	1	6
0	0	0	0	1	1	0	0	0	0	0	0
1	0	0	0	0	0	1	0	0	0	1	0
2	1	0	0	0	0	0	0	0	0	0	0
3	0	1	0	0	0	0	0	0	0	0	0
4	0	0	1	0	0	0	0	0	0	0	0
5	0	0	0	0	0	0	1	0	0	0	0
6	0	0	0	0	0	0	0	0	0	0	1
7	0	0	0	0	0	0	0	0	0	0	0
8	0	0	0	0	0	0	0	1	0	0	0
9	0	0	0	0	0	0	0	0	1	0	0

Une suite de 1 er 0 pour définir mon index !!! du binaire ! C'est pas beau.
Beau je ne sais pas, mais efficace certainement.
 
2ème constatation: la taille de mon index est de l'ordre de 150 MO soit 10 fois moins important qu'un index normal.

Crééons donc tous nous index. (on travaille sur la table Reponse_1)

---

Create bitmap index idx_q1_r1 on reponse_1(q1);

Create bitmap index idx_q2_r1 on reponse_1(q2);

Create bitmap index idx_q3_r1 on reponse_1(q3);

Create bitmap index idx_q4_r1 on reponse_1(q4);

Create bitmap index idx_q5_r1 on reponse_1(q5);

Create bitmap index idx_q6_r1 on reponse_1(q6);

Create bitmap index idx_q7_r1 on reponse_1(q7);

Create bitmap index idx_q8_r1 on reponse_1(q8);

Create bitmap index idx_q9_r1 on reponse_1(q9);

Create bitmap index idx_q10_r1 on reponse_1(q10);

---

à peine 1,5 GO pour les dix indexes. Même pas la taille d'un index sur la table Reponse_2.
 

Reste à esperer qu'en plus ca améliore les performances.
Rejouons notre requête.


1er appel : 6,5 secondes ! pour uniquement 12431 consistents et 12316 lecture phyisiques.
Faut dire que je viens de faire ma première requete sur la table et que la base vient de démarrer. Ce qui est rarement le cas en production.

2ème appel: moins d'une seconde !!!!!! et 12324 consistents gets pour 0 accès disque !

Pour info, dans les deux hypothèses précédentes j'avais également fait plusieurs appels ! mais vu la taille des indexes cela ne pouvait rester en mémoire d'ou les accès disques.
Ma base est configuré avec une SGA de 512 MO et une PGA de 100 MO


J'en vois venir qui vont vouloir coller du BITMAP partout, alors lisez d'abors les lignes qui suivent:

Avertissement:

• On ne pose un index bitmap que sur une colonne contenant à nombre réduit de valeurs distinctes !  Typiquement on ne posera pas un index bitmap sur la colonne date_questionnaire sachant que la table va être alimenté tous les mois (voir tous les jours). La documentation ORACLE parle de 300 valeurs pour la limite de valeurs distincte. Moi je dis qu'il faut tester et mesurer (statistiques, taille index,...)
• On pose principalement des indexes bitmap dans des environnement de type datawarehouse ou sur des tables statiques. Imaginons un instant que je décide qu'une question puisse avoit la note de 11 ! à la première insertion avec la valeur 11, ce sont les 115 millions de lignes d'indexes qui vont être mise à jour.
• On préferera créer des index bitmap sur une colonne plutot que des indexes composés.

Conclusion:
C'est beau, c'est pratique et c'est efficace. Mais une question commence à venir, si ma table doit être alimentée tous les jours et qu'un index bitmap est couteux lors des mise à jour, comment allons nous gérer cela ?

Affaire à suivre ....