Beaucoup d’entre vous utilisent surement le paramètre DEGREE dans les procédures du package DBMS_STATS afin de paralléliser leur traitement de calcul de statistiques. Mais ce que beaucoup ignorent c’est que le parallélisme ne se fait qu’au niveau du segment. Il ne peut y avoir un calcul de statistiques sur plusieurs segments à la fois. Donc, le parallélisme appliqué au calcul de statistiques n’est intéressant que lorsqu’on a affaire à de très grosses tables. Heureusement, depuis la 11.2.0.2 il est possible d’avoir un calcul de statistiques pouvant s’effectuer sur plusieurs tables et partitions en même temps, c’est ce qu’on appelle le mode CONCURRENT. Ce mode permet d’accélérer formidablement le calcul de statistiques en profitant pleinement des ressources de la machine d’autant plus qu’on peut le combiner avec le mode parallel.
J’ai récemment pu améliorer le temps de calcul de statistiques d’une des bases de mon client grâce au mode concurrent. Pour se rendre compte de l’amélioration apportée voyons d’abord ce que donne le calcul de statistiques effectué sans le mode concurrent sur un schéma de 800GB et dont les plus grosses tables font plusieurs dizaine de GB.
J’ai récemment pu améliorer le temps de calcul de statistiques d’une des bases de mon client grâce au mode concurrent. Pour se rendre compte de l’amélioration apportée voyons d’abord ce que donne le calcul de statistiques effectué sans le mode concurrent sur un schéma de 800GB et dont les plus grosses tables font plusieurs dizaine de GB.
Test du calcul de stats séquentiel :
SQL> select sum(bytes)/1024/1024/1024 from dba_segments where owner='SCHEMA1';
SUM(BYTES)/1024/1024/1024
-------------------------
801,098389
exec dbms_stats.gather_schema_stats(ownname => 'SCHEMA1', method_opt => 'for all columns size 1');
PL/SQL procedure successfully completed.
Elapsed: 12:55:42.16
Le calcul de statistiques sur le schéma a mis un peu de moins de 13 heures.
Pendant que le calcul de statistiques tournait j’ai lancé plusieurs requêtes à des moments différents pour voir les sessions actives sur ma base et on constate bien que le calcul de statistiques se faisait en mode séquentiel car seule une session était en train de travailler:
Pendant que le calcul de statistiques tournait j’ai lancé plusieurs requêtes à des moments différents pour voir les sessions actives sur ma base et on constate bien que le calcul de statistiques se faisait en mode séquentiel car seule une session était en train de travailler:
SQL> @sql_cur_stmt
SID SERIAL# PROG ADDRESS HASH_VALUE SQL_ID CHILD PLAN_HASH_VALUE EXECS AVG_ETIME EVENT SQL_TEXT
----- ---------- ---------- ---------------- ---------- ------------- ------ --------------- ---------- ----------- -------------------- -----------------------------------------
2 45373 sqlplus.ex 0000002D356943A8 3121805777 85j1xbkx15yfj 0 226853730 1 692.32 db file sequential r select /*+ no_parallel_index(t, "HISCRN1
Test du calcul de stats en parallel :
J’ai ensuite testé le calcul de stats en mode parallel avec un DEGREE de 16
exec dbms_stats.gather_schema_stats(ownname => 'SCHEMA1', degree=> 16, method_opt => 'for all columns size 1'); Elapsed: 11:15:20.68
Cette fois le calcul de statistiques a mis un peu plus de 11 heures. C’est mieux que le mode séquentiel mais ça reste néanmoins toujours trop long. Pendant que le calcul de statistiques en mode parallèle tournait j’ai également lancé quelques requêtes pour voir ce que j’avais dans mes sessions actives :
SID SERIAL# PROG ADDRESS HASH_VALUE SQL_ID CHILD PLAN_HASH_VALUE EXECS AVG_ETIME EVENT SQL_TEXT
----- ---------- ---------- ---------------- ---------- ------------- ------ --------------- ---------- ----------- -------------------- -----------------------------------------
5 12814 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 db file scattered re /* SQL Analyze(0) */ select /*+ full(t)
1163 42516 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 db file scattered re /* SQL Analyze(0) */ select /*+ full(t)
235 40059 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 db file scattered re /* SQL Analyze(0) */ select /*+ full(t)
237 46728 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 db file scattered re /* SQL Analyze(0) */ select /*+ full(t)
314 18466 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 db file scattered re /* SQL Analyze(0) */ select /*+ full(t)
391 30356 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 db file scattered re /* SQL Analyze(0) */ select /*+ full(t)
468 20257 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 db file scattered re /* SQL Analyze(0) */ select /*+ full(t)
540 28533 sqlplus.ex 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 PX Deq: Execute Repl /* SQL Analyze(0) */ select /*+ full(t)
547 15123 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 db file scattered re /* SQL Analyze(0) */ select /*+ full(t)
623 29688 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 db file scattered re /* SQL Analyze(0) */ select /*+ full(t)
702 62908 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 db file scattered re /* SQL Analyze(0) */ select /*+ full(t)
774 31336 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 db file scattered re /* SQL Analyze(0) */ select /*+ full(t)
852 17484 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 db file scattered re /* SQL Analyze(0) */ select /*+ full(t)
931 1917 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 db file scattered re /* SQL Analyze(0) */ select /*+ full(t)
1005 45861 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 db file scattered re /* SQL Analyze(0) */ select /*+ full(t)
1081 37925 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 db file scattered re /* SQL Analyze(0) */ select /*+ full(t)
163 26835 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0 0 2617663258 1 50.30 ON CPU /* SQL Analyze(0) */ select /*+ full(t)
On constate bien que le calcul de statistiques se faisait en parallèle puisque j’ai bien 16 sessions correspondant à mes process slaves et une session correspondant à ma session principale. On s’aperçoit également que bien que j’ai plusieurs sessions en parallèles elles travaillent toutes sur un seul objet qui est la table T. Cela rejoint donc ce que je disais en préambule de mon article : le mode parallel ne peut s’effectuer qu’au niveau segment et non pas sur plusieurs tables à la fois.
Test du calcul de stats en mode concurrent :
Pour utiliser le mode CONCURRENT il suffit de positionner le paramètre global CONCURRENT qui est désactivé par défaut (même en 12c). Le nombre de sessions concurrentes va dépendre de la valeur du paramètre job_queue_processes.
Voyons ce que le mode CONCURRENT donne pour mon schéma SCHEMA1 :
Voyons ce que le mode CONCURRENT donne pour mon schéma SCHEMA1 :
exec DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');
exec dbms_stats.gather_schema_stats(ownname => 'SCHEMA1', method_opt => 'for all columns size 1');
ORA-20000: Unable to gather statistics concurrently: Resource Manager is not enabled.
ORA-06512: at "SYS.DBMS_STATS", line 35980
ORA-06512: at line 1
Le message d’erreur m’indique qu’il n’y a pas de Resource Plan de défini, or pour que le calcul de statistiques se fasse en mode concurrent il faut que Ressource Manager puisse gérer les ressources consommées par chaque job. On peut soit définir son propre Resource Plan soit utiliser celui fourni par Oracle qui est le DEFAULT_PLAN :
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = default_plan;
exec DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');
exec dbms_stats.gather_schema_stats(ownname => 'SCHEMA1', method_opt => 'for all columns size 1');
PL/SQL procedure successfully completed.
Elapsed: 02:25:16.99
Cette fois ci le calcul de stats a pris 2h25 au lieu de 12h55 en mode séquentiel et 11h15 en mode parallèle. Le gain est énorme.
Voici ce qu’on pouvait voir dans V$SESSION pendant que les jobs concurrents tournaient :
Voici ce qu’on pouvait voir dans V$SESSION pendant que les jobs concurrents tournaient :
SQL> @sql_cur_stmt
SID SERIAL# PROG ADDRESS HASH_VALUE SQL_ID CHILD PLAN_HASH_VALUE EXECS AVG_ETIME EVENT SQL_TEXT
----- ---------- ---------- ---------------- ---------- ------------- ------ --------------- ---------- ----------- -------------------- -----------------------------------------
623 9359 oracle@eqd 0000002D32EF62F8 2948485349 3g5vrh6rvwn75 0 968244646 1 12.76 db file scattered re /* SQL Analyze(1) */ select /*+ full(t)
1085 50128 oracle@eqd 0000002D3518A618 4209280689 4abxz8gxf91pj 1 1515091901 1 113.57 db file sequential r select /*+ no_parallel_index(t, "IHSPOS1
701 8020 oracle@eqd 0000002CD975A2D8 3982881736 4qj5dp7qqbwy8 1 2053917943 1 1,079.45 db file sequential r select /*+ no_parallel_index(t, "IAVOPEI
86 30708 oracle@eqd 0000002D148BDE88 1780980331 59p1yadp2g6mb 0 0 36 .02 Streams AQ: waiting call DBMS_AQADM_SYS.REGISTER_DRIVER ( )
2 45373 sqlplus.ex 0000002DFC7337D0 3466778119 8kruwyz7a5ph7 0 0 2 23,393.51 Streams AQ: waiting BEGIN dbms_stats.gather_schema_stats(ownn
930 47139 oracle@eqd 0000002E0DE75D10 2559624494 96u7bryc91j9f 1 614356722 1 1,905.43 db file sequential r select /*+ no_parallel_index(t, "HISMVC1
548 12639 oracle@eqd 0000002CD8900280 1387000160 auv55cp9arwb0 1 981273022 1 65.32 db file sequential r select /*+ no_parallel_index(t, "ITPCRO9
775 22196 oracle@eqd 0000002C7FC02950 2839555010 d92sd5qnn0ay2 1 3067632440 1 42.85 db file sequential r select /*+ no_parallel_index(t, "RTPSPR1
773 32465 oracle@eqd 0000002CD3983ED0 1227027996 dh8dxq14k5xhw 0 3635621864 1 294.04 db file scattered re /* SQL Analyze(1) */ select /*+ full(t)
317 19697 oracle@eqd 0000002C7E6227C0 2780047690 dtkjh72kv8aaa 0 3588248285 1 .01 db file scattered re /* SQL Analyze(1) */ select /*+ full(t)
469 42289 oracle@eqd 0000002CDB8A0F60 1695986908 fzp1yupkjdd6w 1 43487272 1 862.32 db file sequential r select /*+ no_parallel_index(t, "AVICNF8
Comme le paramètre job_queue_processes est positionné à 10 j’ai uniquement 10 sessions concurrentes, et on s’aperçoit que contrairement au calcul de statistiques en parallèle chaque session travaille sur des tables différentes.
On peut penser qu’avec un paramètre job_queue_processes supérieur à 10 il aurait été possible d’obtenir un meilleur résultat.
Il est également possible de combiner le mode concurrent avec le calcul de statistiques en parallèle. Je l’ai testé pour mon schéma mais le gain n’est que de 2 minutes :
-- test calcul de stats concurrent+parallel
exec DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');
alter system set parallel_adaptive_multi_user=false;
exec dbms_stats.gather_schema_stats(ownname => 'SCHEMA1', degree=> 16, method_opt => 'for all columns size 1');
PL/SQL procedure successfully completed.
Elapsed: 02:23:41.27
Intéressant.
RépondreSupprimerMerci