Ce sont des index B*Tree qui indexent les valeurs miroirs ou inverses (par exemple, le nom
"FRED" devient "DERF"). La syntaxe est identique, il faut juste spécifier le mot clé REVERSE à la fin de l’instruction :
Create index Nom_Index on NomTable (<col1>[,<col2>,…]) REVERSE
Le but de ce type d’index est d’éviter que deux valeurs qui se suivent dans leur ordre naturel se retrouvent dans la même feuille. L’objectif est ici de s’organiser pour avoir un mauvais Clustering Factor. Mais alors dans quel intérêt ?
Imaginez une table dont la clé est un numéro séquentiel et qu’il y ait de nombreuses insertions en parallèle. On se retrouve alors avec plusieurs requêtes qui vont vouloir mettre à jour la même feuille d’index. Or, cette opération ne peut pas être faite par deux requêtes simultanément donc cela aurait pour effet de bloquer chacune d’elles le temps que la précédente ait terminé la mise à jour de la feuille d’index. Avec un index reverse, elles vont mettre à jour des feuilles d’index différentes et ainsi elles ne se gêneront pas. Cela permet donc de réduire la contention d’accès en écriture des feuilles d’index, ce qui sera intéressant sur des requêtes parallèles, particulièrement en environnement Oracle RAC. Cependant, ce type d’index montre des limites car il n’est utilisable que sur des conditions de type égalité (=), ce qui signifie qu’il ne fonctionnera pas pour des opérations Index Range Scan.
vendredi 30 octobre 2015
Clustering Factor
Clustering Factor ou en français Le facteur de foisonnement :
Clustering Factor établit le nombre de liens qu’il y a entre l’ensemble des feuilles de l’index et les blocs dans le tas. Il permet d’estimer si le fait que plusieurs valeurs se trouvent dans une même feuille d’index nécessitera plutôt peu de lecture de blocs différents dans le tas ou plutôt beaucoup.
Les données relatives au Clustering Factor sont disponibles dans la dictionnaire et on y accède par la requête suivante :
select t.INDEX_NAME,
t.LEAF_BLOCKS,
t.DISTINCT_KEYS,
t.CLUSTERING_FACTOR
from sys.user_ind_statistics t
where index_name like '%CLIENT%';
INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
-------------------- --------- ----------- -------------- ---------- -------------------------------
PK_CLIENTS 94 45000 759
IS_CLIENTS_VILLE 130 1096 43479
Clustering_Factor/Leaf_Blocks donne le nombre de liens qu’il y a en moyenne
entre une feuille et des blocs de données dans le tas : un ratio inférieur ou égal à 3
est excellent. Par contre, lorsque Clustering_Factor tend vers le nombre d’enregistrements, alors le Clustering Factor est considéré comme mauvais. Prenons deux cas pour illustrer le Clustering Factor :
Premier cas : L’index de clé primaire sur la colonne Nocmd de la table des commandes cmd. Si on suppose que les numéros de commandes sont créés séquentiellement, de façon ordonnée et jamais supprimés, le tas sera globalement dans le même ordre que l’index. Si l’index retourne plusieurs RowID depuis un même bloc (ce qui est caractéristique d’un prédicat sur une partie de clé ou sur un intervalle), alors il est probable que ces RowID désignent le même bloc ou peu de blocs différents dans le tas (puisque, généralement, il y aura plus de blocs dans le tas que de blocs d’index). Dans ce cas, le Clustering Factor sera bon.
Second cas: Un index sur la colonne Ville dans la table des clients. Si on suppose que les clients n’agissent pas de façon concertée ou dirigée, alors ils seront mis dans le tas dans l’ordre suivant lequel ils ont passé leur première commande mais ils seront normalement dans des villes différentes. Donc, le fait de rechercher tous les clients situés à "Toulouse" retournera, depuis une ou plusieurs pages consécutives de l’index, des RowID qui désigneront des blocs qui seront probablement
sans aucun point commun. Dans ce cas, le facteur de foisonnement sera mauvais.
Un bon Clustering Factor permettra d’avoir de meilleures performances lors d’une opération Index Range Scan puisque, pour chaque feuille d’index, il y aura peu de blocs de données à charger depuis le tas. Pour les autres types d’accès, l’impact sera généralement faible. Un mauvais Clustering Factor aura pour effet de considérer un Index Range Scan moins intéressant.
Clustering Factor établit le nombre de liens qu’il y a entre l’ensemble des feuilles de l’index et les blocs dans le tas. Il permet d’estimer si le fait que plusieurs valeurs se trouvent dans une même feuille d’index nécessitera plutôt peu de lecture de blocs différents dans le tas ou plutôt beaucoup.
Les données relatives au Clustering Factor sont disponibles dans la dictionnaire et on y accède par la requête suivante :
select t.INDEX_NAME,
t.LEAF_BLOCKS,
t.DISTINCT_KEYS,
t.CLUSTERING_FACTOR
from sys.user_ind_statistics t
where index_name like '%CLIENT%';
INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
-------------------- --------- ----------- -------------- ---------- -------------------------------
PK_CLIENTS 94 45000 759
IS_CLIENTS_VILLE 130 1096 43479
Clustering_Factor/Leaf_Blocks donne le nombre de liens qu’il y a en moyenne
entre une feuille et des blocs de données dans le tas : un ratio inférieur ou égal à 3
est excellent. Par contre, lorsque Clustering_Factor tend vers le nombre d’enregistrements, alors le Clustering Factor est considéré comme mauvais. Prenons deux cas pour illustrer le Clustering Factor :
Premier cas : L’index de clé primaire sur la colonne Nocmd de la table des commandes cmd. Si on suppose que les numéros de commandes sont créés séquentiellement, de façon ordonnée et jamais supprimés, le tas sera globalement dans le même ordre que l’index. Si l’index retourne plusieurs RowID depuis un même bloc (ce qui est caractéristique d’un prédicat sur une partie de clé ou sur un intervalle), alors il est probable que ces RowID désignent le même bloc ou peu de blocs différents dans le tas (puisque, généralement, il y aura plus de blocs dans le tas que de blocs d’index). Dans ce cas, le Clustering Factor sera bon.
Second cas: Un index sur la colonne Ville dans la table des clients. Si on suppose que les clients n’agissent pas de façon concertée ou dirigée, alors ils seront mis dans le tas dans l’ordre suivant lequel ils ont passé leur première commande mais ils seront normalement dans des villes différentes. Donc, le fait de rechercher tous les clients situés à "Toulouse" retournera, depuis une ou plusieurs pages consécutives de l’index, des RowID qui désigneront des blocs qui seront probablement
sans aucun point commun. Dans ce cas, le facteur de foisonnement sera mauvais.
Un bon Clustering Factor permettra d’avoir de meilleures performances lors d’une opération Index Range Scan puisque, pour chaque feuille d’index, il y aura peu de blocs de données à charger depuis le tas. Pour les autres types d’accès, l’impact sera généralement faible. Un mauvais Clustering Factor aura pour effet de considérer un Index Range Scan moins intéressant.
In-Mermory Parallel Execution
In-Mermory Parallel Execution utilise une agrégation des mémoires disponibles pour éliminer au maximum le besoin des IO physique pour les opération parallélisées. Autrement dit Oracle peut prendre la décision de placer en mémoire un objet(une table) sur lequel les exécutions parallèle sont autorisées. Pour une architecture en RAC il reparti les fragment de la table dans les différents Buffer Cache des instances actives du cluster. Cette répartition ou mapping permet à Oracle de déterminer le buffer cache utile pour trouver un ensemble de données nécessaires pour répondre à une requête et ainsi réduire le besoin d'I/O physique. Cette technique est rendu possible par le quantité importante de mémoire disponible dans les nouvelles infrastructures. Pour bénéficier de l'option In-Mermory Parallel Execution il faut que le paramètre d'initialisation PARALLEL_DEGREE_POLICY soit configuré à AUTO (valeur par défaut MANUEL)
dimanche 4 octobre 2015
INDEX VIRTUEL
Un index virtuel est un index que l'on déclare sans qu'il ne soit physiquement créé. Une simulation d'un index. En effet dans bien des cas et principalement dans les environnements de prod on peut vouloir tester un index pour voir s'il améliore les performances sans impacter la production
Avec une consommation cpu, IO et stockage.
Considérons la requête suivante :
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM sh.sales WHERE quantity_sold > 10000
3 /
Explained.
SQL> SELECT *
2 FROM TABLE (DBMS_XPLAN.display (NULL, NULL, 'BASIC +COST'))
3 /
--------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
--------------------------------------------------
| 0 | SELECT STATEMENT | | 441 (12)|
| 1 | PARTITION RANGE ALL| | 441 (12)|
| 2 | TABLE ACCESS FULL | SALES | 441 (12)|
-------------------------------------------------
Nous avons un table access full sur la table SALES. On peut se poser la question si un index serai pas plus performant.
SQL> ALTER SESSION SET "_use_nosegment_indexes"=TRUE;
Session altered.
SQL> CREATE INDEX sh.sales_vi1 ON sh.sales(quantity_sold) NOSEGMENT;
Index created.
L'option NOSEGMENT indique qu'il s'agit d'un index virtuel.
On execute de nouveau la requête :
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM sh.sales WHERE quantity_sold > 10000;
Explained.
SQL> SELECT *
2 FROM TABLE (DBMS_XPLAN.display (NULL, NULL, 'BASIC +COST'));
| Id | Operation | Name | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 (0)|
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 3 (0)|
| 2 | INDEX RANGE SCAN | SALES_VI1 | 2 (0)|
---------------------------------------------------------------------
On peut voir dans le plan d'exécution l'utilisation de l'index et l'optimisation qu'il apporte : le coût passe de 441 to 3 on peut donc conseiller la création d'un tel index ou le créer physiquement.
vendredi 2 octobre 2015
EXADATA Offloading/SmartScan
Dans cet article nous allons parler de la notion "Exadata offloading ou SmartScan". Offloading peut éventuellement être traduit par déporter ou déléguer un travail.
La notion du offloading est la recette miracle permettant de faire la différence entre Exadata et toute autre plateforme utilisée pour faire tourner des bases de données Oracle. On parle de offloading pour décrire l'action qui consiste à déplacer les traitements en principe réalisés par le serveur de base de données vers la couche stockage (cellule). L'objectif n'est pas uniquement un transfert de charge des serveurs bases de données aux serveurs de stockage pour reduire la consommation de CPU. L'objectif principal est la réduction de la quantité des données retournées aux serveurs de base de données. Il est à noter que l'importante quantité de données retournées aux serveurs de base de données par la couche stockage représente un des principaux goulot d'étranglement des bases de données.
On parle d'Offloading ou de SmartScan. Cependant le SmartScan fait plus référence aux améliorations apportées par Exadata aux traitements SQL. Le terme Offloading est lui plus générique et fait référence en plus à d'autres aspects par exemple l'impact sur la sauvegarde et restauration.
Pour illustrer l'importance de l'Exadata Offloading on prend un exemple on ne peut plus simple:
Une requête SQL qui récupère un champ dans une ligne dans une table et pour couronner le tout, elle filtre sur le Rowid de la ligne. Dans une architecture non Exadata un minimum d'un bloc devra être chargé en mémoire.
En supposant que la table enregistre en moyenne 50 lignes par bloc. Cela implique que nous allons transférer 49 lignes inutilement au serveur de base de données. Vous pouvez alors imaginer le cas des énormes quantités de données inutiles transmises aux serveurs de base de données et du temps nécessaire pour les transmettre. Exadata à été conçu principalement pour résoudre cette problématique via le Offloading qui a pour principal objectif de réduire la quantité de données retournée par la couche stockage aux serveurs de base de données.
Exadata implemente dans sa couche software des algorithmes permettant de tirer le meilleur profit de la couche hardware. Pour tester l'impact du Offloading je vais modifier le paramètre cell_offload_processing et ensuite interroger une table TAB d'une taille de 385 millions de lignes :
SYS@SU14> alter session set cell_offload_processing=false;
Session altered.
Elapsed: 00:00:00.06
SYS@SU14> select count(*) from TAB where col1 < 0;
COUNT(*)
----------
2 1 row selected.
Elapsed: 00:00:51.09
Je modifie de nouveau le paramètre cell_offload_processing pour activer le Offloading :
SYS@SU14> alter session set cell_offload_processing=true;
Session altered.
Elapsed: 00:00:00.07
SYS@SU14> select count(*) from TAB where col1 < 0;
COUNT(*)
----------
2 1 row selected.
Elapsed: 00:00:00.15
On peut voir l'amélioration du temps d'exécution de la requête.
Je parlerai prochainement des mécanismes qui sont derrière le SmartScan.
Inscription à :
Articles (Atom)