Parmis les nouveautés de la version 11.2.0.2, vous pouvez mettre en attente des requêtes en parallèle lorsque les processus parallèles sont épuisés plutôt que de les exécuter en série. Dans la seconde partie de cet article, je vous propose d’illustrer cette nouvelle fonctionnalité.
Mais avant ça, nous allons illustrer comment une requête en parallèle est exécutée en série. Vous verrez également que le plan, même capturé dans la shared pool, s’affiche avec un DOP qui n’est pas celui d’un plan en série…
Un exemple de requête parallèle
Evidemment, vous pouvez tester cette fonctionnalités sur un environnement qui supporterait 128 processus en parallèle et quelques To de données de stockage capable de monter en charge. Cela dit, si vous n’avez que quelques Go et processeurs/cores, cela suffit à la démonstration.
Je vous propose de créer une requête qui n’a pas beaucoup de sens ci-dessous mais qui pourra s’exécuter en parallèle. Créer un schéma de démonstration simple avec une seule table T1 de 1Go environ :
create user demo identified by demo
default tablespace users
temporary tablespace temp;
grant connect, resource, dba to demo;
connect demo/demo
create table t1(id number,
zone number,
colx number,
colt varchar2(1024));
insert /*+ append */ into t1
(select rownum, mod(rownum, 17), mod(rownum,243), rpad('X', 1024, 'Y')
from dba_objects, dba_objects
where rownum <= 1024*1024);
commit;
exec dbms_stats.gather_table_stats(user, 'T1', method_opt=>'for all columns size 254');
Vous pourrez alors exécuter une requête comme celle ci-dessous en forçant la mise en oeuvre du parallélisme à l’aide du hint parallel(2) :
set num 15
set timing on
select /*+ parallel(2) */ t1.zone, sum(t1.colx), count(t1.colt)
from t1, t1 t2
where t2.colx = 1
group by t1.zone
order by t1.zone;
Sur mon ordinateur, cette requête prend environ 7 minutes. Si elle va trop vite sur votre ordinateur, vous pouvez simplement augmenter la liste des données dans la clause t2.colx in (1) en mettant, par exemple t2.colx between 1 and 5. Pour superviser l’exécution de cette requête en parallèle et en supposant que vous soyez seul à travailler sur la base de données, vous pouvez lancer un script comme celui ci-dessous dans une session séparée pour visualiser le plan d’exécution :
set lines 180
col sid format 9999
col serial# format 9999
col event format a25
col sql_id format a14 new_value sql_id
col program format a26
col username format a6
set lines 120
set pages 1000
col slaves format 999
select s.sid, s.serial#, s.sql_id, s.username, s.program, s.event,
(select count(*)
from V$PX_SESSION px
where px.sid!=QCSID
and px.QCSID=s.sid
group by QCSID) slaves
from gv$session s, v$process p
where s.status='ACTIVE' and s.sid != sys_context('userenv', 'sid')
and p.addr=s.paddr
and p.pname is null
and s.username is not null
order by s.sid;
SID SERIAL# SQL_ID USERNA PROGRAM EVENT SLAVES
--- ------- -------------- ------ -------------------------- ------------------------- ------
6 9 cxabct87yuntq DEMO sqlplus@pink (TNS V1-V3) PX Deq: Execute Reply 4
set pages 1000
set tab off
set lines 120
select *
from table(dbms_xplan.display_cursor(
sql_id=>'&&sql_id',
format=>'basic +note +predicate +cost'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ parallel(2) */ t1.zone, sum(t1.colx), count(t1.colt
t1, t1 t2 where t2.colx = 1 group by t1.zone order by t1.zo
Plan hash value: 1757222182
--------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2505K(100)|
| 1 | PX COORDINATOR | | |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 2505K (36)|
| 3 | SORT GROUP BY | | 2505K (36)|
| 4 | PX RECEIVE | | 2505K (36)|
| 5 | PX SEND RANGE | :TQ10001 | 2505K (36)|
| 6 | HASH GROUP BY | | 2505K (36)|
| 7 | MERGE JOIN CARTESIAN | | 1667K (3)|
| 8 | PX BLOCK ITERATOR | | 26409 (1)|
|* 9 | TABLE ACCESS FULL | T1 | 26409 (1)|
| 10 | BUFFER SORT | | 2479K (36)|
| 11 | PX RECEIVE | | 26411 (1)|
| 12 | PX SEND BROADCAST | :TQ10000 | 26411 (1)|
| 13 | PX BLOCK ITERATOR | | 26411 (1)|
|* 14 | TABLE ACCESS FULL| T1 | 26411 (1)|
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access(:Z>=:Z AND :Z<=:Z)
14 - access(:Z>=:Z AND :Z<=:Z)
filter("T2"."COLX"=1)
Note
-----
- Degree of Parallelism is 2 because of hint
Parallèlisme saturé, requête en série
Pour visualiser ce qu’il se passe quand le parallelisme est saturé, changez le nombre de processus en parallèle à 4 comme ci-dessous :
alter system set parallel_max_servers=4;
Ouvrez ensuite deux sessions avec et exécutez la requête parallèle de la section précédente en simultané ; dans une autre session, lancez la commande de supervision ci-dessous dans une 3e session :
set lines 180
col sid format 9999
col serial# format 9999
col event format a25
col sql_id format a14 new_value sql_id
col program format a26
col username format a6
set lines 120
set pages 1000
col slaves format 999
select s.sid, s.serial#, s.sql_id, s.username, s.program, s.event,
(select count(*)
from V$PX_SESSION px
where px.sid!=QCSID
and px.QCSID=s.sid
group by QCSID) slaves
from gv$session s, v$process p
where s.status='ACTIVE' and s.sid != sys_context('userenv', 'sid')
and p.addr=s.paddr
and p.pname is null
and s.username is not null
order by s.sid;
SID SERIAL# SQL_ID USERNA PROGRAM EVENT SLAVES
--- ------- -------------- ------ -------------------------- ------------------------- ------
70 53 cxabct87yuntq DEMO sqlplus@pink (TNS V1-V3) PX Deq: Execute Reply 4
191 7 cxabct87yuntq DEMO sqlplus@pink (TNS V1-V3) direct path read
Comme vous pouvez le voir dans la colonne SLAVES, une seule des 2 requêtes s’exécute en parallèle. Et pourtant, bien que les 2 requêtes ne s’exécutent pas avec le même niveau de parallèlisme, il y a un unique child cursor pour la requête :
col child_number format 999
select sql_id,
address,
child_address, child_number,
pq_slave_mismatch
from v$sql_shared_cursor
where sql_id='&&sql_id';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER P
-------------- ---------------- ---------------- ------------ -
cxabct87yuntq 000000007BB99820 000000007DB2A488 0 N
Vous pouvez vérifier que c’est bien le cas une fois vos requêtes exécutées ; dans la premère session, lancez la commande ci-dessous qui montre que la requête s’est exécutée en parallèle :
Elapsed: 00:07:46.65
select statistic, last_query
from v$pq_sesstat;
STATISTIC LAST_QUERY
------------------------------ ---------------
Queries Parallelized 1
DML Parallelized 0
DDL Parallelized 0
DFO Trees 1
Server Threads 4
Allocation Height 2
Allocation Width 1
Local Msgs Sent 148
Distr Msgs Sent 0
Local Msgs Recv'd 148
Distr Msgs Recv'd 0
La requête de la seconde session n’a pas été exécutée en parallèle :
Elapsed: 00:14:08.79
select statistic, last_query
from v$pq_sesstat;
STATISTIC LAST_QUERY
------------------------------ ---------------
Queries Parallelized 0
DML Parallelized 0
DDL Parallelized 0
DFO Trees 0
Server Threads 0
Allocation Height 0
Allocation Width 0
Local Msgs Sent 0
Distr Msgs Sent 0
Local Msgs Recv'd 0
Distr Msgs Recv'd 0
SQL Real-Time Monitoring
Comme interroger le plan dans la shared pool ne montre pas de manière évidente que la requête est « downgradée », donnons une chance à SQL Real-Time Monitoring et là, très bonne nouvelle puisque la requête exécutée en série montre que le DOP a été réduit :
var rep clob
exec :rep:=dbms_sqltune.REPORT_SQL_MONITOR(session_id=>191, -
session_serial => 7 )
set long 20000
set longchunksize 20000
col rep format a210
set lines 210
set pages 1000
print rep
----------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
select /*+ parallel(2) */ t1.zone, sum(t1.colx), count(t1.colt) from t
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : DEMO (70:53)
SQL ID : cxabct87yuntq
SQL Execution ID : 16777220
Execution Started : 05/30/2011 18:07:34
First Refresh Time : 05/30/2011 18:07:38
Last Refresh Time : 05/30/2011 18:19:48
Duration : 735s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus@pink (TNS V1-V3)
DOP Downgrade : 100%
Global Stats
===================================================================
| Elapsed | Cpu | IO | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
===================================================================
| 742 | 737 | 1.01 | 3.62 | 343K | 2702 | 3GB |
===================================================================
SQL Plan Monitoring Details (Plan Hash Value=1757222182)
======================================================================
| Id | Operation | Name | Rows | Cost |
Alors que si vous regardez le plan qui s’exécute effectivement en parallèle, SQL Real Time Monitoring, le DOP n’est pas modifié :
var rep clob
exec :rep:=dbms_sqltune.REPORT_SQL_MONITOR(session_id=>70, -
session_serial => 53 )
set long 20000
set longchunksize 20000
col rep format a210
set lines 210
set pages 1000
print rep
REP
---------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
select /*+ parallel(2) */ t1.zone, sum(t1.colx), count(t1.colt) from t1, t1 t2 wh
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : DEMO (70:53)
SQL ID : cxabct87yuntq
SQL Execution ID : 16777221
Execution Started : 05/30/2011 18:23:29
First Refresh Time : 05/30/2011 18:23:29
Last Refresh Time : 05/30/2011 18:23:29
Duration : 22s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus@pink (TNS V1-V3)
Global Stats
=================================================================================
| Elapsed | Cpu | IO | Concurrency | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
=================================================================================
| 37 | 37 | 0.03 | 0.15 | 0.12 | 9722 | 80 | 78MB |
=================================================================================
Parallel Execution Details (DOP=2 , Servers Allocated=4)
=================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Concurrency |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) |
=================================================================================
| PX Coordinator | QC | | 0.16 | 0.01 | | 0.15 |
| p000 | Set 1 | 1 | | | | |
| p001 | Set 1 | 2 | | | | |
| p002 | Set 2 | 1 | 18 | 18 | 0.02 | |
| p003 | Set 2 | 2 | 18 | 18 | 0.02 | |
=================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1757222182)
=================================================================================
| Id | Operation | Name | Rows | Cost | Time
| | | | (Estim) | | Active(s)
=================================================================================
Voilà le problème illustré ; dans la prochaine partie, vous découvrirez comment paramétrer « Resource Manager » pour que les plans soient mis en attente au lieu d’être exécutés en série comme ci-dessus.
