Dimanche 9 novembre 2008 7 09 /11 /Nov /2008 21:40

Performance - Partitionnement (1)

Bonsoir,

Me revoici avec ma table de 115 millions de lignes (j'ai perdu accidentellement celle de 125 millions). Vu le nombre de lignes ce n'est pas très important.
Mes indexes bitmap m'ont permis d'avoir des temps de réponse très acceptable sur des requêtes portant sur les notes  mais j'ai des soucis car bon nombre de requêtes portent également sur des journées précises en fonction du type de formulaire.

Pour ceux qui débarqueraient, je rappelle l'article d'origine :http://www.lao-dba.com/article-23969555.html
  • 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

SELCECT COUNT (*) FROM Reponse_1 WHERE date_questionnaire=20071225 AND id_formulaire=14; 


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.

CREATE TABLE REPONSE_1_P 

(
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)
) ;


Comme vous pouvez le constater, la syntaxe n'a rien d'extraordinaire. On définit l'inervalle pour ma partition "by range" et on indique ensuite le nombre de sous partitions. Ici, j'ai choisis 64, ce qui signigie 64 sous partitions par partition soit un total de 64*31= 1984 sous partitions.

On peut simplifier en disant que ma table de 115 millions est découpée en 1984 espaces de rangement caractérisés par une date et un type de formulaire.

Si l'on a une répartion homogène des formulaires par date, cela nous fait environ 60000 lignes par sous-ensemble, ce qui est beaucoup plus souple à manipuler !

Je vous épargne toutes les options de stockage, mais il bien sur possible de spécifier des tablespaces particuliers pour les partitions et les sous partitions.

Maintenant que notre table REPONSE_1_P est créer, il faut l'alimenter ! 
INSERT INTO Reponse_1_P SELECT * FROM Reponse_1;
COMMIT;
BEGIN
DBMS_STAT.GATHER_TABLE_STATS(user,tabname=>'REPONSE_1_P',
        granularity=>'ALL',
          Estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/

Simple mais long ! Si vous voulez faire, faites le tourner la nuit (je vous rappelle qu'il faut quelque GO pour la table).

Pour le moment je ne vais pas créer les indexes bitmap, ni d'indexes sur la date ou sur le questionnaire.

Alors une petite démonstration:
On ouvre son petit SQLPLUS
SQL> connect LAO/LAO
SQL> SET AUTOTRACE ON
SQL> SET LINESIZE 131
SQL> SELECT   COUNT(*) from Reponse_1_P
SQL> WHERE date_questionnaire=20071225 and id_formulaire=15;



Et oh surprise, résultat quasi instantané ! (1 seconde au plus) et pourtant le plan d'execution indique un FULL SCAN sur la table.

Effectivement, on un full scan, mais si vous regardez les étapes 2 et 3 du plan, vous constatez que ORACLE effectue les opération "Partition Range Single" et "Partition Hash Single", et dans les deux dernière colonnes, on peut apercevoir les partitions parcourues par ORACLE !!

Donc un FULL mais uniquement sur les partitions utiles !
Que du bonheur ! une table de 115 millions de lignes sans indexes et des résultats de requetes rapide. En admettant que les types de formulaire soient répartis dans différentes équipes de marketing, on s'assure de la sorte des accès concurents sur la table sans risque de collisions ! 

Pour ceux qui seraientt décu que je ne sois pas allé plus loin dans les différents type de partitionnement, sous partitionnement et qui se demandent :
  • 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. 

 AVERTISSEMENT : Oracle Partionning, n'est possible qu'avec une version Enterprise et nécessite une license particulière. Oracle parle de "Extra cost".



Par LAO - Publié dans : PERFORMANCE - Communauté : ORACLE
Ecrire un commentaire - Voir les 4 commentaires
Retour à l'accueil

Commentaires

Salut, Juste pour comprendre, les partitions doivent être créées en amont, i.e. que si je gère une table contenant pour chaque jour de l'année un ensemble de ligne, je dois spécifier ma partition pour chacun de ces jours, soit 365 jours. Si je gère mes données sur plusieurs années, je dois donc répéter l'opération pour chaque année. N'y a-t-il pas un moyen automatique d'indiquer à Oracle de créer une partition à chaque nouvelle journée que je crée dans ma table ?
Commentaire n°1 posté par Franck le 13/11/2008 à 10h11
Bonjour,

On peut toujours vouloir automatiser en créeant par exemple un job mensuel ou annuel qui va créer les partitions automatiquement. Cependant, cela fait partie du boulot du DBA, qui en plus au moment d'ajouter les nouvelles partitions va devoir eventuellement réorganiser son espace (nouveau tablesapace, purge d'anciennes partitions obsoletes,..).
Typiquement si on doit gérer des partitions "par journée", on pourra découper les taches d'administration par mois ou trimestre, voir par année en fonction de la volumétrie de l'accessabilié aux données. En effet dans le cas de dataware house ou les données sont statiques, on a grand interet à placer rapidement le maximum de tablespace en READ ONLY.
Cela nous permetra d'optimiser les sauvegardes.
Pour rappel, tous les exemples sont fait sur une version 10 Release 2..
Ce qui laisse entendre que ORACLE 11 apporte du nouveau.
LAO.
Réponse de LAO le 13/11/2008 à 10h39
Du coup le partitionnement ça à l'air super mais mes index deviennent useless du coup non? Mettre index+partitionnement au final pourrait être plus lent que partionnement Du coup j'attend avec impatience la partie sur le partionnement et les index :)
Commentaire n°2 posté par adetag le 10/12/2008 à 19h33
Bonsoir,
Je confirme que le partionnemet est efficace, voir nécessaire sur des environnements ou l'on parle en TERA. Par ailleurs sur la partie Index, as tu déjà jeté un oeil sur l'article qui parle des indexes locaux (http://www.lao-dba.com/article-25050126.html).
Le partitionnement et l'indexation sont complémentaires, sauf que dans mon exemple étant donné que je requete sur les colonnes de partitionnement, la partition se substitue en quelque sorte à l'index. Mais il ne faut pas perdre de vue  que l'on peut indexer les autres colonnes.
Réponse de LAO le 10/12/2008 à 20h08
J'aurai une question sur le choix et la signification du nombre 64.
La table comporte bien 31 souspartitions.
Pourquoi le choix du nombre 64 et pas 32 par exemple ?
Merci d'avance.
Commentaire n°3 posté par siouat le 15/07/2009 à 23h09
Bonsoir,
Je réalise avoir mal formulé ma question.
Il y aurait bien 31 partitions par range.
Vous indiquez 64 sous partitions. Pourquoi 64 et où sont-elles ces sous partitions ?
Merci
Commentaire n°4 posté par siouat le 15/07/2009 à 23h19
Bonjour,

64, 32,128 effectivement, j'aurai pu choisir une autre valeur. Mais l'objet du blog est d'aborder des concepts et en prenant des exemples non issue du monde de la production, il n'est pas toujours aisé de justifier un choix parmis un autre. Il faut savoir que l'on utilise le partionnement à la fois pour une maintenance plus facile, et également dans un souci d'amélioration des performances. Dans le cas des performances, Partitionnement rime avec parallelisme, et donc dans certains cas, c'est le nombre de processeurs qui va dicter le choix du nombre de sous-partitions. Il va de soit que la volumétrie a également son importance.
Pour ce qui est des infos sur les sous-partitions, vous pouvez interrroger les vues systemes user_tab_subpartitions et dba_tab_subpartitions.
LAO.
Réponse de LAO le 16/07/2009 à 09h52
Ecrire un commentaire

Archives

 

Catégories

 

Recherche

 

Articles récents

Liste complète

 

Calendrier

Février 2012
L M M J V S D
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29        
<< < > >>

 

Profil

  • LAO
  • My Oracle blog
  • Homme
  • 06/05/1972
  • IDF
  • DBA

 

Contact - C.G.U. - Rémunération en droits d'auteur - Signaler un abus - Articles les plus commentés