Parmis les nouveautés de la version 11.2.0.2, vous pouvez mettre en attente des requêtes en parallèle lorsqu’un certain nombre de processus est utilisé. Grâce à la gestion automatique du parallèlisme et resource manager, les requêtes qui sont dégradées en série en gestion manuelle, sont mises en files d’attente pour être traitées en parallèle dès que possible en mode automatique. Pour bien comprendre de quoi il s’agit, commencez par lire Requêtes Parallèles, Partie 1/2 : Downgrade en Série qui présente la sérialisation des requêtes parallèles. Mais revenons au sujet…
L’intérêt de cette fonctionnalité est évident. Prenons un exemple :
- Supposons que notre base de données n’exécute qu’une seule typologie de requêtes : des rapports,
- Disons que notre requête type s’exécute avec un parallèlisme de 16 en 5 minutes et qu’en série, elle prend 1 heure,
- Imaginons que notre système puisse exécuter un maximum de 4 requêtes simultanément sans dégrader les performances du système. Pour assurer le meilleur parallèlisme, vous aurez activé 128 processus parallèles
Avec cet exemple, si, en moyenne, il y a 1 rapports exécuté toutes les 5 minutes mais que pendant certains pics, vous pouvez aller jusqu’à 5 ou 6 rapports exécutés simultanément… Vous voyez où je veux en venir ?
Activer la gestion automatique du parallèlisme
Comme dans l’article précédent, il n’est pas nécessaire d’utiliser un parallèlisme de 128 pour démontrer cette fonctionnalité. 4 suffit largement !
Pour mettre en oeuvre cette fonctionnalité, vous activerez d’abord gestion automatique du parallelisme.
alter system set parallel_degree_policy=AUTO;
Pour illustrer le fait que l’on peut fixer une limite sur un sous-ensemble de processus par plan d’exécution. Pour cela, on affectera 8 processus en parallèle et une cible d’utilisation de 8 également. On fixera la limite à 4 (i.e. 50% de 8) :
alter system set parallel_max_servers=8;
alter system set parallel_servers_target=8;
Plan de Ressources
Pour activer la file d’attente des requêtes parallèles, vous devez créer un plan de ressources et mettre en oeuvre les paramètres parallel_target_percentage et parallel_queue_timeout. Voici un script qui :
- crée un plan
BI_PLAN - crée un groupe de ressources
ADHOCpouvant consommer jusqu’à 50% des processus parallèles avant une mise en attente - donne le droit à l’utilisateur
DEMOd’utiliser le groupeADHOC - change le groupe par défaut de l’utilisateur
DEMO
connect / as sysdba
BEGIN
-- Create a Pending Area
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-- Create 2 groups
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
CONSUMER_GROUP => 'AdHoc',
COMMENT => 'AdHoc Queries');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
CONSUMER_GROUP => 'Report',
COMMENT => 'Report Application');
-- Create a Resource Plan
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
PLAN => 'BI_PLAN',
COMMENT => 'Schedule Resources between Reports and AdHoc Queries');
-- Add directives to the plan
-- - Queue when 100% of the servers processes are reached
-- - Keep 10 mins in the queue
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'BI_PLAN',
GROUP_OR_SUBPLAN => 'AdHoc',
COMMENT => 'AdHoc Queries Group',
PARALLEL_TARGET_PERCENTAGE => 50,
PARALLEL_QUEUE_TIMEOUT => 600);
-- Add another directive for the plan
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'BI_PLAN',
GROUP_OR_SUBPLAN => 'Report',
COMMENT => 'Report Queries Group');
-- Add another directive for the plan
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'BI_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'Other Group');
-- Validate the plan
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-- Submit the plan
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
begin
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'DEMO',
consumer_group => 'AdHoc',
grant_option => false);
end;
/
exec DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
BEGIN
-- Create a Pending Area
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-- Define Mapping Rule for Users
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'DEMO', 'AdHoc');
-- Define Mapping Priorities to set Users/Group Mapping 1st
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI(
EXPLICIT => 1,
SERVICE_MODULE_ACTION => 4,
SERVICE_MODULE => 5,
MODULE_NAME_ACTION => 6,
MODULE_NAME => 7,
SERVICE_NAME => 3,
ORACLE_USER => 2,
CLIENT_PROGRAM => 8,
CLIENT_OS_USER => 9,
CLIENT_MACHINE => 10);
-- Validate the plan
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-- Submit Pending Area
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = BI_PLAN;
select username, INITIAL_RSRC_CONSUMER_GROUP
from dba_users
where username='DEMO';
USERNA INITIAL_RSRC_CONSUMER_GROUP
------ ---------------------------
DEMO ADHOC
select attribute, priority
from DBA_RSRC_MAPPING_PRIORITY
order by 2;
select username, RESOURCE_CONSUMER_GROUP
from v$session;
ATTRIBUTE PRIORITY
------------------------------ ---------------
EXPLICIT 1
ORACLE_USER 2
SERVICE_NAME 3
SERVICE_MODULE_ACTION 4
SERVICE_MODULE 5
MODULE_NAME_ACTION 6
MODULE_NAME 7
CLIENT_PROGRAM 8
CLIENT_OS_USER 9
CLIENT_MACHINE 10
Tester la mise en file d’attente
Comme dans l’article précédent, lancez la requête parallèle ci-dessous dans l’utilisateur DEMO
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;
Lancez-en 3 en parallèle. Vous pouvez les surveiller dans v$session comme ci-dessous :
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 RESOURCE_CONSUMER_GROUP format a8
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,
s.resource_consumer_group,
(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 RESOURCE SLAVES
----- ------- -------------- ------ -------------------------- ------------------------- -------- ------
6 77 cxabct87yuntq DEMO sqlplus@pink (TNS V1-V3) resmgr:pq queued ADHOC
191 19 cxabct87yuntq DEMO sqlplus@pink (TNS V1-V3) resmgr:pq queued ADHOC
200 45 cxabct87yuntq DEMO sqlplus@pink (TNS V1-V3) PX Deq: Execute Reply ADHOC 4
Comme vous pouvez l’observer ci-dessus, les requêtes sont mises en file d’attente par Resource Manager. Vous pouvez également superviser l’exécution des requêtes avec SQL Real-Time Monitoring :
var rep clob
exec :rep:=dbms_sqltune.REPORT_SQL_MONITOR(session_id=>191, -
session_serial => 19 )
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 where t2.colx = 1 group by t1.zone order by t1.zone
Global Information
------------------------------
Status : QUEUED
Instance ID : 1
Session : DEMO (191:19)
SQL ID : cxabct87yuntq
SQL Execution ID : 16777240
Execution Started : 05/30/2011 22:35:26
First Refresh Time : 05/30/2011 22:35:26
Last Refresh Time : 05/30/2011 22:35:26
Duration (Queued) : 297s (296s)
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus@pink (TNS V1-V3)
Global Stats
=====================
| Elapsed | Queuing |
| Time(s) | Time(s) |
=====================
| 296 | 296 |
=====================
SQL Plan Monitoring Details (Plan Hash Value=1757222182)
===========================================================================
| Id | Operation | Activity | Activity Detail |
| | | (%) | (# samples) |
===========================================================================
| 0 | SELECT STATEMENT | 100.00 | resmgr:pq queued (285) |
| 1 | PX COORDINATOR | | |
| 2 | PX SEND QC (ORDER) | | |
| 3 | SORT GROUP BY | | |
| 4 | PX RECEIVE | | |
| 5 | PX SEND RANGE | | |
| 6 | HASH GROUP BY | | |
| 7 | MERGE JOIN CARTESIAN | | |
| 8 | PX BLOCK ITERATOR | | |
| 9 | TABLE ACCESS FULL | | |
| 10 | BUFFER SORT | | |
| 11 | PX RECEIVE | | |
| 12 | PX SEND BROADCAST | | |
| 13 | PX BLOCK ITERATOR | | |
| 14 | TABLE ACCESS FULL | | |
===========================================================================
Fin d’exécution
La requête se termine après 5 min pour la première :
Elapsed: 00:05:34.47
10 minutes pour la seconde
Elapsed: 00:11:06.77
et en erreur pour la dernière du fait du timeout sur le plan :
select /*+ parallel(2) */ t1.zone, sum(t1.colx), count(t1.colt)
*
ERROR at line 1:
ORA-07454: queue timeout, 600 second(s), exceeded
Conclusion
Pour terminer cet exemple, supprimez l’utilisateur DEMO, le plan BI_PLAN et les groupes associés :
begin
-- Create a Pending Area
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-- delete the plan and the unused Groups
dbms_resource_manager.DELETE_PLAN_CASCADE(plan=>'BI_PLAN');
-- Validate the plan
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-- Submit Pending Area
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
end;
/
drop user demo cascade;
Vous voilà prêt pour des tests plus poussés pour vos requêtes adhoc ou vos rapports BI.
