DBA, parallel query

Historiquement, l’option PQO (parallel query option) a été introduite en v7.1. Cette option permet d’exécuter certains ordres SQL en parallèle, accélérant le traitement. Dans sa version initiale, le parallélisme pouvait être appliqué lors de l’utilisation des ordres select ou create . . . as select. Oracle 8.0 a apporté le concept de PDM (Parallel Data Manipulation), applicable aux objets fragmentés ou partitionnés.

Il n’y a pas de lien direct entre Parallel Server et l’option Parallel Query !

Le parallélisme des traitements est assurée par un process coordinateur et plusieurs process parallèles. En règle générale, deux process parallèles ne vont pas agir sur un même extent. L’utilisation de cette option est bénéfique surtout en environnement multiprocesseur, mais des architectures soignées permettent d’en tirer profit même en architecture mono processeur. La distribution des objets sur disques accélérant l’utilisation de ce type de process.

Les process en question agissent autour de la même SGA pour une même session.
Toutes les actions sur un objet ne sont pas parallelisables, voyons les actions et les objets qui en supportent ce concept. Pour la suite, le mot fragmenté peut se référer aux partitions. Les ordres DDL suivantes peuvent être parallélisées :

Les process en question agissent autour de la même SGA pour une même session.
Create table . . . as select . . .
Create index
Rebuild d’un index ou d’une de ses partitions
Déplacement d’une partition
Split d’une partition

Ces actions peuvent être parallélisée si l’objet source ou cible est fragmenté.
Les manipulation des données (DML) suivantes peuvent être parallélisées :

Delete from . . . appliqué sur des tables fragmentées.
Update . . . SET . . . appliqué sur des tables fragmentées.
Insert . . . select . . . appliqué sur des tables fragmentées.

SQL*LOADER peut agir en parallèle sur des tables fragmentées.
En ce qui concerne les tables partitionnées, un degré supplémentaire de parallélisme peut être recherché dans le cas de l’utilisation des sous partitions.
Pour que des process agissent en parallèle sur un objet, l’ordre SQL qui veut en tirer profit doit le signaler :

Select /*+parallel(matable,3) */ * from matable where . . .

Techniquement, le parallélisme peut être obtenu par trois méthodes :

Par ROWID. Ce parallélisme peut tirer profit des objets non fragmentés, la granularité du parallélisme étant plutôt logique.
Par partition
Par process esclaves

Oracle 8i amène des fonctionnalités supplémentaires avec un degré supérieur de fiabilité, contrôle et initialisation : Le niveau de parallelisation par défaut a été amélioré, un nouvel algorithme d’autoadaptivité a été introduit, la répartition des charges des process parallèle d’effectue d’une meilleure manière en environnement Parallel Server.
Pour faciliter le tuning de l’utilisation de l’option, 8i introduit quelques paramètres utiles pour une auto adaptabilité du système à ce type d’action. Le paramètre PARALLEL_AUTOMATIC_TUNING, si mis à TRUE utilisera des valeurs par défaut pour tous les process et mettra à TRUE le paramètre décrit dans la phrase suivante PARALLEL_ADAPTIVE_MULTI_USER Bien évidement, les interventions manuelles pour le tuning restent possibles. Dans les versions antérieures à 8i, le degré de parallélisme devait être indiqué. C’est Oracle qui décide en 8i des paramètres optimaux en fonction de la charge du système au moment du déclenchement de la requête. Le contrôle de l’allocation des process en parallèle peut être effectué avec des nouveaux paramètres : PARALLEL_ADAPTIVE_MULTI_USER qui permet le choix pertinent du degré de parallélisme et PARALLEL_PROCESSES_PER_CPU qui permet d’établir le nombre de process par défaut, toujours en fonction de la charge du système. Au niveau de la base de données, une nouvelle limitation des ressources pour chaque utilisateur apparaît avec PARALLEL_DEGREE_LIMIT qui indique le degré de parallélisme maximal pour les utilisateurs et qui intègre la gestion des plans de ressources.

Le dictionnaire de données et PDML

Dans le dictionnaire de données, les vues V$PX_SESSION, V$PX_SESSTAT, V$PX_PROCESS, V$PX_PROCESS_SYSSTAT fournissent des informations sur les statistiques concernant l’exécution des requêtes en parallèle:

SELECT s.qcsid, n.name, sum(s.value)
FROM V$PX_SESSTAT s, v$statname n
WHERE s.statistic# = n.statistic#
GROUP BY s.qcsid, n.name;

La vue V$SESSION contient une nouvelle colonne, PDML_ENABLED, qui peut être à YES ou NO.

Activation de PDML

Le mode PDML est activé avec des alter au niveau de la session :

alter session [enable|disable|force] parallel [DML/DDL] [parallel n] ;

Cette commande spécifie qu’Oracle peut tenter d’exécuter les requêtes concernées en parallèle. Le degré de parallélisme concernant les ordres DML est toutefois dicté d’une manière prioritaire par les HINTS ou les clauses des objets concernés. En ce qui concerne les ordres DDL, elles seront exécutés avec le degré de parallélisme par défaut. L’option force est nouvelle en 8i et concerne les objets qui n’ont aucune clause de parallélisme dans leur clause de stockage ou ceux qui sont attaqués par des requêtes sans HINTS. Dans tous les cas, le degré de parallélisme doit se trouver dans l’intervalle [PARALLEL_MIN_SERVERS, PARALLEL_MAX_SERVERS].
Aucune opération de modification d’une table modifié en parallèle ne peut être tentée pendant la modification.

Même avec PDML actif dans la session, aucune garantie de la réalisation de l’ordre en parallèle ne peut être assurée. Les HINTS modifient le degré de parallélisme spécifié par la clause de stockage de la table.

De nouveaux hints ont été crées pour être utilisés avec les INSERT : APPEND et NOAPPEND.
Aucun process parallèle n’est démarré lors d’un lock table ou select . . . for update, ainsi lors de l’utilisation des contraintes auto-referentielles (sur la même table) ou différées.

Les transactions parallélisées et les restaurations

Dans les versions antérieures à 8i, une chute d’instance pendant une transaction de type PDML déterminait lors du rollforward suivant le STARTUP une sérialisation des actions de SMON, d’ou un temps de restauration beaucoup plus grand que le temps de la transaction originelle elle-même. A partir de 8i, la restauration des transactions peut être effectuée en parallèle si le paramètre PARALLEL_TRANSACTION_RECOVERY est mis à HIGH (4*nbCPU), LOW (2*nbCPU), ou FALSE (valeur par défaut).

Les transactions parallélisées et les RBS

Du fait qu’une transaction PDML est exécutée par plusieurs process, même le nombre de rollback segment doit être bien calculé et les RBS bien distribués sur plusieurs disques pour éviter des contentions physiques. Deux process peuvent utiliser le même RBS, provoquant des perte de performances notables. Bien que indépendants, si un process échoue, tous les autres provoquent un rollback pour garantir une transaction cohérente. Un algorithme de type two phase commit est utilisé par le coordinateur de la transaction.
Ces actions peuvent être parallélisées si l’objet source ou cible est fragmenté.

Exemple de requêtes parallélisées

SQL> commit;
Commit complete.
SQL> alter session enable PARALLEL dml;
Session altered.
SQL> alter session enable PARALLEL ddl;
Session altered.
SQL> select username, pdml_status, pddl_status, pq_status, pdml_enabled
2 from v$session where username is not null;
USERNAME PDML_STA PDDL_STA PQ_STATU PDM
------------------------------ -------- -------- -------- ---
SYS ENABLED ENABLED ENABLED YES
SQL> select * from v$pq_sesstat where statistic like '%Parallel%';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 0 9
DML Parallelized 0 0
SQL> create table anp.opqb as
2 select * from anp.opq;
Table created.
SQL> select * from v$pq_sesstat where statistic like '%Parallel%';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 1 10
DML Parallelized 0 0
SQL> insert /*+parallel (anp.opqb,5) */ into anp.opqb
2 select /*+parallel (anp.opq, 4) */ * from anp.opq;
2560 rows created.
SQL> select * from v$pq_sesstat where statistic like '%Parallel%';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 1 11
DML Parallelized 0 0
SQL> commit;
Commit complete.
SQL> UPDATE /*+PARALLEL (anp.opqb,2) */
2 anp.opqb SET c1 = c1*10;
5120 rows updated.
SQL> select * from v$pq_sesstat where statistic like '%Parallel%';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 0 11
DML Parallelized 0 0
SQL> UPDATE /*+PARALLEL (anp.opqb,2) */
2 anp.opqb SET c1 = c1*10;
5120 rows updated.
SQL> DELETE /*+PARALLEL (anp.opqb,4) */
2 FROM anp.opqb
3 WHERE to_number(substr(c2,1,1)) < 5;
4096 rows deleted.
SQL> select * from v$pq_sesstat where statistic like '%Parallel%';
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 0 11
DML Parallelized 0 0
SQL> select * from v$pq_sysstat where statistic not like '%Msgs%';
STATISTIC VALUE
------------------------------ ----------
Servers Busy 0
Servers Idle 3
Servers Highwater 3
Server Sessions 101
Servers Started 1
Servers Shutdown 0
Servers Cleaned Up 0
Queries Initiated 33
DML Initiated 0
DFO Trees 33
Sessions Active 0
11 rows selected.
SQL> spool off;

L’exemple précédent montre l’utilisation des process en parallèle pour toutes les opérations de création des tables as select, de lecture des tables. La parallelisation n’a pas été effectuée pour les delete ou update, ceci est possible uniquement pour les tables partitionnées.

Les rollback segments différés
Les rollback segments différés introduits en V7.3 accélerent le processus de restauration de la base du point de vues des utilisateurs. La base est immediatement disponible apres le rollforward, des process background effectuent le rollback. Dans le cas d’une demande utilisateur d’une donnée non encore restaurée, son process demandera un rollback immediate des données concernées. Le rollback s’effectue donc avec la base ouverte et est effectué soit par PMON qui balaye regulierement l’ensemble des RBS ou par les process utilisateur qui se heurtent d’une ancienne transaction interrompue. Cette architecture pouvait impliquer de temps de rollback demesurés si par exemple les transactions interrompues s’effectuait en paralléle (voir paragraphe ‘Oracle Parallel Query’, page 1).
Les rollback segments fast start
Oracle 8i utilise des process parallèles de lecture des RBS et améliore les temps de restauration. Le paramètre FAST_START_PARALLEL_ROLLBACK gére le nombre de process paralleles :
HIGH - Il determine l’utilisation de maximum 4*nbCPU process
LOW - Il determine l’utilisation de maximum 2*nbCPU process
FALSE - Il désactive cette option.

Conclusion
Oracle peut tirer profit d’une architecture mono processeur (Plus la version est élevée, plus de processus parallèles par processeur sont disponibles).

...