Traitement des requêtes Cloud SQL

Les tables externes Oracle ne disposent pas d'index classiques. Les requêtes portant sur ces tables externes requièrent généralement une analyse complète de la table. L'agent de traitement Oracle Cloud SQL sur les DataNodes du cluster Hadoop étend les fonctionnalités d'analyse intelligente (comme les déchargements des prédicats de filtre) aux tables externes Oracle. L'analyse intelligente a été utilisée pendant un certain temps sur Oracle Exadata Database Machine pour effectuer le filtrage de colonne et de prédicat dans la couche de stockage avant le renvoi des résultats de requête à la couche de base de données. Dans Cloud SQL, l'analyse intelligente est une dernière étape de filtrage qui est effectuée localement sur le serveur Hadoop afin de garantir que seuls les éléments demandés sont envoyés au serveur de requête Cloud SQL. Les cellules Cloud SQL exécutées sur les DataNodes Hadoop peuvent effectuer des analyses intelligentes sur divers formats de données dans HDFS, comme CSV, Avro et Parquet.

Cette implémentation de l'analyse intelligente exploite la puissance du traitement massivement parallèle du cluster Hadoop pour filtrer les données au niveau de leur source. Elle peut rejeter de façon préemptive une grande partie des données non pertinentes (jusqu'à 99 % du total). Les avantages sont multiples :

• Les déplacements de données et le trafic réseau entre le cluster et la base de données sont considérablement réduits.

• Les ensembles de résultats renvoyés au serveur Oracle Database sont beaucoup plus petits.

• Les données sont agrégées chaque fois que cela est possible grâce à l'évolutivité et au traitement du cluster.

Les résultats de requête sont renvoyés bien plus rapidement. Il s'agit de la conséquence directe de la réduction du trafic sur le réseau et de la charge sur Oracle Database.

Pour les données stockées dans HDFS, Oracle Cloud SQL gère automatiquement les index de stockage, et ce, de façon transparente pour Oracle Database. Les index de stockage contiennent le récapitulatif de la répartition des données stockées dans HDFS sur un disque dur. Ils réduisent le coût des opérations d'E/S et le coût de l'UC associé à la conversion des données de fichiers plats en blocs Oracle Database. Un index de stockage est comparable à un index négatif. Il indique à l'analyse intelligente que les données ne se trouvent pas dans un bloc de données, ce qui permet donc à l'analyse intelligente d'ignorer la lecture de ce bloc. Cela peut réduire considérablement le nombre d'E/S.

Les index de stockage peuvent être utilisés uniquement pour les tables externes reposant sur HDFS et créées à l'aide du pilote ORACLE_HDFS ou ORACLE_HIVE. Ils ne peuvent pas être utilisés pour les tables externes se servant de StorageHandlers, telles qu'Apache HBase et Oracle NoSQL.

Un index de stockage est un ensemble d'index de région en mémoire, dans lequel chaque index de région stocke des récapitulatifs de 32 colonnes au maximum. Il existe un index de région pour chaque fractionnement. Le contenu stocké dans un index de région ne dépend pas des autres index de région. Cette organisation les rend hautement évolutifs et permet d'éviter les conflits de verrous internes.

Les index de stockage conservent les valeurs minimale et maximale des colonnes d'une région pour chaque index de région. Les valeurs minimale et maximale permettent d'éliminer les E/S superflues. Cette méthode est également appelée filtrage des E/S. La statistique sur les octets d'E/S de granule de cellule XT économisés par les index de stockage, disponible dans la vue V$SYSSTAT, indique le nombre d'octets d'E/S économisés grâce à l'utilisation des index de stockage.

Les index de stockage améliorent les requêtes qui utilisent les comparaisons suivantes :

• Egalité (=)

• Inégalité (<, != ou >)

• Inférieur ou égal à (<=)

• Supérieur ou égal à (>=)

• EST NULL

• N'EST PAS NULL

Les index de stockage sont créés automatiquement après que le service Oracle Cloud SQL a reçu une requête comportant un prédicat de comparaison supérieur à la valeur maximale ou inférieur à la valeur minimale de la colonne dans une région.

Elimination des E/S disque à l'aide des index de stockage

La figure suivante représente une table et des index de région. Les valeurs de la colonne B de la table sont comprises entre 1 et 8. Un premier index de région stocke la valeur minimale 1 et la valeur maximale 5. L'autre index de région stocke la valeur minimale 3 et la valeur maximale 8.

Pour une requête comme celle ci-dessous, seul le premier ensemble de lignes correspond. Les E/S disque sont éliminées car les valeurs minimale et maximale du second ensemble de lignes ne correspondent pas à la clause WHERE de la requête.

SELECT * 
FROM TABLE 
WHERE B < 2;

Amélioration des performances de jointure à l'aide des index de stockage

L'utilisation des index de stockage permet aux jointures de table d'ignorer les opérations d'E/S inutiles. Par exemple, la requête suivante réalise une opération d'E/S et applique un filtre de Bloom uniquement au premier bloc de la table de faits. Les filtres de Bloom sont cruciaux pour améliorer les performances de jointure. Dans cet exemple, un prédicat se trouve sur la table de dimensions, et non sur la table de faits. Le filtre de Bloom est créé en fonction de dim.name=Hard drive et est ensuite appliqué à la table de faits. Par conséquent, même si le filtre figure sur la table de dimensions, vous pouvez filtrer les données au niveau de leur source (Hadoop) en fonction des résultats de la requête de dimension. Cela permet également d'exécuter certaines optimisations comme les index de stockage.

SELECT count(*) 
FROM fact, dimension dim  
WHERE fact.m=dim.m and dim.product="Hard drive";

Les E/S du second bloc de la table de faits sont complètement éliminées par les index de stockage car la plage de valeurs minimale/maximale (5 et 8) de celui-ci n'est pas présente dans le filtre de Bloom.

De nombreux systèmes Big Data prennent en charge un certain niveau de déchargement de prédicat, soit via le type de fichier lui-même (par exemple, Apache Parquet), soit via les API StorageHandler et le partitionnement de Hive . Oracle Cloud SQL tire parti de ces fonctions de déchargement en propageant des prédicats d'Oracle Database vers des systèmes qui les prennent en charge. Par exemple, la propagation de prédicats permet d'appliquer les comportements automatiques suivants :

• Les requêtes portant sur les tables Hive partitionnées sont nettoyées en fonction des prédicats de filtre sur les colonnes de partition.

• Les requêtes portant sur les fichiers Apache Parquet et Apache ORC réduisent les E/S en testant les prédicats par rapport aux structures de type index internes contenues dans ces formats de fichier.

  Remarque
  
  Les fichiers Parquet doivent être créés avec Hive ou Spark. Les fichiers Parquet créés à l'aide d'Apache Impala ne disposent pas des statistiques requises pour réaliser la propagation de prédicats.

• Les requêtes portant sur Oracle NoSQL Database ou Apache HBase utilisent des prédicats SARGable pour piloter les sous-analyses de données dans la banque de données distante.

Les requêtes portant sur les données Hadoop peuvent impliquer le traitement d'objets volumineux (LOB) avec potentiellement des millions d'enregistrements. Renvoyer ces objets à Oracle Database pour le filtrage et l'analyse n'est pas efficace. Oracle Cloud SQL peut améliorer considérablement les performances en propageant le traitement CLOB vers ses propres cellules de traitement sur le cluster Hadoop. Le filtrage dans Hadoop réduit le nombre de lignes renvoyées à Oracle Database. L'analyse réduit la quantité de données renvoyées à partir d'une colonne dans chaque ligne filtrée.

Vous pouvez désactiver ou réactiver la propagation du traitement CLOB en fonction de vos besoins.

Pour le moment, cette fonctionnalité s'applique uniquement aux expressions JSON renvoyant des données CLOB. Les expressions de filtre JSON admissibles à l'évaluation de la couche de stockage incluent une syntaxe simplifiée, JSON_VALUE et JSON_QUERY.

La même prise en charge sera fournie pour les autres types CLOB (tels que substr et instr) ainsi que pour les données BLOB dans une prochaine version.

{"ponumber":9764,"reference":"LSMITH-20141017","requestor":"Lindsey Smith","email": "Lindsey@myco.com", "company":"myco" …}

CREATE TABLE POS_DATA
  ( pos_info CLOB )
  ORGANIZATION EXTERNAL
  ( TYPE ORACLE_HDFS
    DEFAULT DIRECTORY DEFAULT_DIR
    LOCATION ('/data/pos/*')
  )
 REJECT LIMIT UNLIMITED;

SELECT p.pos_info.email, p.pos_info.requestor
FROM POS_DATA p
WHERE p.pos_info.company='myco'

Oracle Cloud SQL utilise la technologie Oracle In-Memory pour propager le traitement d'agrégation vers les cellules Cloud SQL. Cela permet à Cloud SQL d'exploiter la puissance de traitement du cluster Hadoop pour répartir les agrégations entre les noeuds de cluster.

Les gains de performances peuvent être considérablement plus rapides par rapport aux agrégations qui n'effectuent pas de déchargement, en particulier lorsqu'il existe un nombre modéré de regroupements récapitulatifs. Pour les requêtes de table unique, l'opération d'agrégation doit systématiquement effectuer un déchargement.

Les cellules Cloud SQL prennent en charge les agrégations de table unique et multitables (par exemple, les tables de dimensions jointes à une table de faits). Pour les agrégations multitables, Oracle Database utilise l'optimisation de transformation par vecteur de clé dans laquelle les vecteurs de clé sont propagés vers les cellules pour le processus d'agrégation. Ce type de transformation est utile pour les requêtes SQL de jointure en étoile utilisant des opérateurs d'agrégation classiques (comme SUM, MIN, MAX et COUNT), qui sont courants dans les requêtes de gestion.

Une requête de transformation par vecteur est une requête plus efficace qui utilise un filtre de Bloom pour les jointures. Lorsque vous utilisez une requête transformée par un vecteur avec des cellules Cloud SQL, les performances des jointures dans la requête sont améliorées par la possibilité de décharger le filtrage des lignes utilisées pour l'agrégation. Une opération KEY VECTOR USE apparaît dans le plan de requête pendant cette optimisation.

Dans les cellules Cloud SQL, les requêtes transformées par un vecteur bénéficient d'un traitement plus efficace en raison de l'application de colonnes de regroupement (vecteurs de clé) à l'index de stockage Cloud SQL.

Oracle Cloud SQL offre un certain nombre de statistiques qui peuvent fournir des données pour les analyses de performances.

SQL> SELECT sn.name,ms.value 
FROM V$MYSTAT ms, V$STATNAME sn 
WHERE ms.STATISTIC#=sn.STATISTIC# AND sn.name LIKE '%XT%'; 

NAME                                                      VALUE
-----------------------------------------------------     -----
cell XT granules requested for predicate offload          0 
cell XT granule bytes requested for predicate offload     0
cell interconnect bytes returned by XT smart scan         0 
cell XT granule predicate offload retries                 0
cell XT granule IO bytes saved by storage index           0 

SQL> SELECT n.name, round(s.value/1024/1024) 
FROM v$mystat s, v$statname n
WHERE s.statistic# IN (462,463)
AND s.statistic# = n.statistic#;

cell XT granule bytes requested for predicate offload  32768
cell interconnect bytes returned by XT smart scan   32

SQL> SELECT count(*) FROM bdsql_parq.web_sales;

  COUNT(*)
----------
 287301291

SQL> SELECT substr(n.name, 0,60) name, u.value
FROM v$statname n, v$mystat u
WHERE ((n.name LIKE 'key vector%') OR
       (n.name LIKE 'vector group by%') OR
       (n.name LIKE 'vector encoded%') OR
       (n.name LIKE '%XT%') OR
       (n.name LIKE 'IM %' AND n.name NOT LIKE '%spare%'))
      AND u.sid=userenv('SID')
      AND n.STATISTIC# = u.STATISTIC#
      AND u.value > 0;


NAME                                                      VALUE
-----------------------------------------------------     -----
cell XT granules requested for predicate offload          808 
cell XT granule bytes requested for predicate offload     2.5833E+10
cell interconnect bytes returned by XT smart scan         6903552 
vector group by operations sent to cell                   1
vector group by rows processed on cell                    287301291
vector group by rows returned by cell                     808

SELECT substr(n.name, 0,60) name, u.value
FROM v$statname n, v$mystat u
WHERE ((n.name LIKE 'key vector%') OR
       (n.name LIKE 'vector group by%') OR
       (n.name LIKE 'vector encoded%') OR
       (n.name LIKE '%XT%'))
      AND u.sid=userenv('SID')
      AND n.STATISTIC# = u.STATISTIC#


NAME                                                      VALUE
-----------------------------------------------------     -----
cell XT granules requested for predicate offload          250 
cell XT granule bytes requested for predicate offload     61,112,831,993
cell interconnect bytes returned by XT smart scan         193,282,128 
key vector rows processed by value                        14,156,958
key vector rows filtered                                  9,620,606
key vector filtered on cell                               273,144,333
key vector probed on cell                                 287,301,291
key vectors sent to cell                                  1
key vectors sent to cell in lite mode due to quota        1
key vector serializations in lite mode for cell           1
key vector efilters created                               1