DBA ARABIC

ﺍلPlan ﺘﻘﻭﻡ ﺒﺘﻘﺴﻴﻡ ﺍﻟﻤﻭﺍﺭﺩ ﺒﻴﻥ ﻤﺠﻤﻭﻉ ﺍلConsumer Groups، ﻭﻗﺩ ﻴﻜﻭﻥ ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﺍﻟﻭﺍﺤﺩﺓ ﻋﺩﺩ ﻤﻥ ﺍلResource Manager Plan، ﻭﻟﻜﻥ ﻓﻲ ﺍﻟﻠﺤﻅﺔ ﺍﻟﻭﺍﺤﺩ ﺘﻨﺸﻁ Plan ﻭﺍﺤﺩﺓ ﻓﻘﻁ،

ﻫﻲ ﻤﻥ ﺘﺘﺤﻜﻡ ﻓﻲ ﺍﻟﻤﻭﺍﺭﺩ ﻭﻴﺘﻡ ﺘﻁﺒﻴﻘﻬﺎ ﻋﻠﻲ ﺠﻤﻴﻊ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ. ﻭﻟﺤﻅﺔ ﺇﻨﺸﺎﺀ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﻴﺘﻡ ﺇﻨﺸﺎﺀ ﺜﻼﺙ ﺨﻁﻁ ﺘﺤﺩﺜﻨﺎ ﻋﻨﻬﺎ ﺴﺎﺒﻘﺎﹰ.

ﻤﺒﺩﺌﻴﹰﺎ ﻴﻤﻜﻥ ﺘﻭﺯﻴﻊ ﺍﻟﻤﻭﺍﺭﺩ ﻋﻠﻲ ﺍﻟﻤﺠﻤﻭﻋﺎﺕ ﻋﻠﻲ ﺜﻤﺎﻨﻲ ﻤﺴﺘﻭﻴﺎﺕ،

ﻭﺍﻟﺤﻘﻴﻘﺔ ﺃﻥ ﻫﻨﺎﻙ ﻋﺩﺩ ﻤﻥ ﺍﻟﻤﻭﺍﺭﺩ ﻨﺴﺘﻁﻴﻊ ﺍﻟﺘﺤﻜﻡ ﺒﻬﺎ ﻤﻥ ﺨﻼل ﺍلPlan Manager Resource:

1- CPU Method: ﻭﻫﻲ ﺘﻤﻜﻨﻙ ﻤﻥ ﺍﻟﺘﺤﻜﻡ ﻭﻟﺘﺤﺩﻴﺩ ﻤﺼﺎﺩﺭ ﻭﺤﺩﺓ ﺍﻟﻤﻌﺎﻟﺠﺔ ﺍﻟﻤﺭﻜﺯﻴﺔ CPU ﻭﺘﻘﺴﻴﻤﻬﺎ ﺒﻴﻥ ﺍﻟﻤﺠﻤﻭﻋﺎﺕ Consumer Groups ﺃﻭ الSubplan.

2- Active Session Pool with Queuing: ﻴﺴﻤﺢ ﻟﻙ ﺘﺤﺩﻴﺩ ﻋﺩﺩ ﺍلSessions ﺍﻟﻨﺸﻁﺔ ﺍﻟﻤﺘﺼﻠﺔ ﻓﻲ ﻨﻔﺱ ﺍﻟﻭﻗﺕ ﻋﻥ ﻁﺭﻴﻕ ﺍل Consumer Group،

ﺇﺫﺍ ﺘﺠﺎﺯﺕ ﺍﻟﻤﺠﻤﻭﻋﺔ ﺍﻟﻌﺩﺩ ﺍﻟﻤﺴﻭﺡ ﺒﻪ ﻤﻥ ﺍلSessions ﻓﺈﻥ ﻫﺫﻩ ﺍلSessions ﺘﻅل ﻤﻭﺠﻭﺩﺓ ﻓﻲ ﺼﻑ ﺒﺈﻨﺘﻅﺎﺭ ﺇﻨﺘﻬﺎﺀ ﺍﺤﺩ ﺍلSession ﺍﻟﻨﺸﻁﺔ،

ﻋﻤﻭﻤﹰﺎ ﺃﻨﺕ ﻴﻤﻜﻥ ﺃﻴﻀﺎﹰ ﺃﻥ ﺘﺤﺩﺩ ﻓﺘﺭﺓ ﺯﻤﻨﻴﺔ ﻹﻨﺘﻅﺎﺭ ﺍلSession ﻓﻲ ﺍﻟﺼﻑ ﻗﺒل ﺃﻥ ﻴﺼﺩﺭ ﺍﻟﺨﻁﺄ.

3- Degree of Parallelism: ﺘﻤﻜﻨﻙ ﻤﻥ ﺍﻟﺴﻴﻁﺭﺓ ﻋﻠﻲ ﺍﻟﺩﺭﺠﺔ ﺍﻟﻘﺼﻭﻱ ﻟﻠﻌﻤل ﻋﻠﻲ ﺍﻟﺘﻭﺍﺯﻱ ﻷﻱ ﻋﻤﻠﻴﺔ ﻀﻤﻥ ﺍلConsumer Group.

4- Execution Time Limit: ﺘﺴﻤﺢ ﻟﻙ ﺒﺘﺤﺩﺩ ﺍﻗﺼﻲ ﻭﻗﺕ ﻤﺴﻤﻭﺡ ﺒﻪ ﻟﺘﻨﻔﻴﺫ ﺍﻟﻌﻤﻠﻴﺎﺕ.

5- Idle Time Limit: ﺘﺴﻤﺢ ﻟﻙ ﺒﺘﺤﺩﻴﺩ ﺍﻟﻭﻗﺕ ﺍﻟﺫﻱ ﻴﻤﻜﻥ ﺃﻥ ﺘﻜﻭﻥ ﺍلSession ﻋﺎﻁﻠﺔ ﻋﻥ ﺍﻟﻌﻤل ﻗﺒل ﺃﻥ ﺇﻨﻬﺎﺀ ﺍلSession.

6- Undo Pool: ﺘﺴﺘﻁﻴﻊ ﻤﻥ ﺨﻼﻟﻪ ﺍﻟﺴﻴﻁﺭﺓ ﻋﻠﻲ ﻜﻤﻴﺔ ﺍلUndo ﺍﻟﺘﻲ ﻴﻤﻜﻥ ﺃﻥ ﺘﺘﻭﻟﺩ ﻤﻥ ﻗﺒل ﺍلConsumer Group ﺃﻭ ﺍلSubplan.

ﻨﺴﺘﺨﺩﻡ ﺍﻹﺠﺭﺍﺀ CREATE_PLAN ﺍﻟﻤﻭﺠﻭﺩ ﻓﻲ ﺍﻟﺤﺯﻤﺔ DBMS_RESOURCE_MANAGER ﻹﻨﺸﺎﺀ ﺍلPLAN ﻭﻫﺫﺍ ﺍﻹﺠﺭﺍﺀ ﻴﺤﺘﻭﻱ ﻋﻠﻲ ﻋﺩﺩ ﻤﻥ ﺍﻟﻤﺘﻐﻴﺭﺍﺕ.

ﻭﺃﻴﻀﺎﹰ ﺴﻨﺴﺘﺨﺩﻡ ﺍﻹﺠﺭﺍﺀ CREATE_PLAN_DIRECTIVE ﻟﻤﻨﺢ ﺍﻟﻤﺼﺎﺩﺭ ﻟلCONSUMER GROUPS ﻋﻥ ﻁﺭﻴﻕ ﺍلPLAN.

ﺇﺫﺍﹰ ﺴﻨﻘﻭﻡ ﺒﺈﻨﺸﺎﺀ ﺍلPLAN ﻋﻥ ﻁﺭﻴﻕ ﺍﻹﺠﺭﺍﺀ CREATE_PLAN ﻭﻟﻜﻥ ﺴﻨﻘﻭﻡ ﻻﺤﻘﺎﹰ ﺒﺈﻨﺸﺎﺀ DIRECTIVE ﻋﻥ ﻁﺭﻴﻕ ﺍﻹﺠﺭﺍﺀ CREATE_PLAN_DIRECTIVE ﻟﺘﻭﺯﻴﻊ ﺍﻟﻤﺼﺎﺩﺭ ﺒﻴﻥ

ﺍلCONSUMER GROUPS ﻋﻥ ﻁﺭﻴﻕ ﺍلPLAN.

ﻟﻨﻔﺘﺭﺽ ﻤﺜﻼﹰ ﺃﻨﻨﺎ ﺴﻨﻘﻭﻡ ﺒﺈﻨﺸﺎﺀ PLAN ﺍﺴﻤﻬﺎ NEW PLAN ﺴﻨﻘﻭﻡ ﺒﺘﻭﺯﻴﻊ ﺍﻟﻤﺼﺎﺩﺭ ﺒﺎﺴﺘﺨﺩﺍﻡ ﺍلDIRECTIVES ﻋﺒﺭ ﺍلPLAN ﺒﻴﻥ ﺍلCONSUMER GROUPS،

ﺤﻴﺙ ﺴﻨﻘﻭﻡ ﺒﺘﻭﺯﻴﻊ ﺍﻟﻤﻭﺍﺭﺩ وهي:

.(CPU Method , Active Session , Degree of Parallelism , Execution Time Limit ,Idle Time Limit ,Undo Pool)

ﻫﺫﻩ ﻫﻲ ﻤﺘﻐﻴﺭﺍﺕ ﺍﻹﺠﺭﺍﺀ CREATE_PLAN_DIRECTIVE ﺤﻴﺙ:

Plan name of resource plan

group_or_subplan name of consumer group or subplan

comment comment for the plan directive

cpu_p1 first parameter for the CPU resource allocation method

cpu_p2 second parameter for the CPU resource allocation method

cpu_p3 third parameter for the CPU resource allocation method

cpu_p4 fourth parameter for the CPU resource allocation method

cpu_p5 fifth parameter for the CPU resource allocation method

cpu_p6 sixth parameter for the CPU resource allocation method

cpu_p7 seventh parameter for the CPU resource allocation method

cpu_p8 eighth parameter for the CPU resource allocation method

active_sess_pool_p١ first parameter for the max. active sessions allocation method

queueing_p١ queue timeout in seconds

parallel_degree_limit_p١ first parameter for the degree of parallelism allocation method

switch_group group to switch once switch time is reached

switch_time max execution time within a group

switch_estimate use execution time estimate to assign group?

max_est_exec_time max. estimated execution time in seconds

undo_pool max. cumulative undo allocated for consumer groups

max_idle_time max. idle time

max_idle_blocker_time max. idle time when blocking other sessions

switch_time_in_call max execution time within a top call - will switch back to home group after call

ﻻﺤﻅ ﺃﻥ ﻫﻨﺎﻙ ٨ ﻤﺴﺘﻭﻴﺎﺕ ﻟﺘﻭﺯﻴﻊ ﻤﺼﺎﺩﺭ ﺍلCPU ﺒﻴﻥ ﺍﻟﻤﺠﻤﻭﻋﺎﺕ، ﻤﻊ ﺍﻟﺘﺄﻜﻴﺩ ﻋﻠﻲ ﺃﻥ ﻜل DIRECTIVE ﻤﻭﺠﻬﺔ لGroup Consumer ﻭﺍﺤﺩﺓ،

ﻭﻟﻨﻔﺘﺭﺽ ﺍﻻﻥ ﺃﻨﻨﺎ ﻨﺭﻴﺩ ﺇﻨﺸﺎﺀ Plan ﺘﺴﻤﻲ NEW_PLAN ﻫﺫﻩ ﺍلPlan ﻤﻭﺠﻬﺔ ﻟﺜﻼﺙ ﻤﺠﻤﻭﻋﺎﺕ ﻭﻫﻲ (SYS_GROUP & NEW_GROUP & OTHER_GROUPS).

ﺃﻱ ﺃﻨﻨﺎ ﻨﺤﺘﺎﺝ Plan ﻭﺍﺤﺩة ﻭﺜﻼﺙ Directives, ﻜل Directive ﻤﻭﺠﻬﺔ لConsumer Group ﻤﻌﻴﻨﺔ.

ﻟﻨﻔﺘﺭﺽ ﺃﻨﻨﺎ ﻨﺭﻴﺩ ﻤﻨﺢ ﺍﻟﻤﺠﻤﻭﻋﺔ SYS_GROUP ﺠﺯﺀ ﻤﻥ ﻤﺼﺎﺩﺭ ﺍلCPU ﻋﻠﻲ ﺍﻟﻨﺤﻭ ﺍﻟﺘﺎﻟﻲ:

ﺍﻟﻤﺴﺘﻭﻱ ﺍﻻﻭل: 60%.

ﺍﻟﻤﺴﺘﻭﻱ ﺍﻟﺜﺎﻨﻲ: 50%.

ﺍﻟﻤﺴﺘﻭﻱ ﺍﻟﺜﺎﻟﺙ: 40%.

ﺒﻤﻌﻨﻲ ﺃﻨﻪ ﻓﻲ ﺍﻟﻤﺴﺘﻭﻱ ﺍﻷﻭل ﺍﻷﻭﻟﻴﺔ ﻟﻠﻤﺠﻤﻭﻋﺔ SYS_GROUP ﺒﻨﺴﺒﺔ ﺘﺼل ﺇﻟﻲ 60 % ﻭﺃﻤﺎ ﺍﻟﻨﺴﺒﺔ ﺍﻟﺒﺎﻗﻴﺔ ﻭﻫﻲ 40 % ﺴﻴﺘﻡ ﺘﻘﺴﻴﻤﻬﺎ ﺒﻴﻥ ﺍﻟﻤﺠﻤﻭﻋﺘﻴﻥ ﺍﻻﺨﺭﺘﻴﻥ

(NEW_GROUP & OTHER_GROUPS) ﻭﺫﻟﻙ ﻋﻠﻲ ﺍﻟﻨﺤﻭ ﺍﻟﺘﺎﻟﻲ:

ﺍﻟﻤﺴﺘﻭﻱ ﺍﻷﻭل: 20 %.

ﺍﻟﻤﺴﺘﻭﻱ ﺍﻟﺜﺎﻨﻲ: 25%.

ﺍﻟﻤﺴﺘﻭﻱ ﺍﻟﺜﺎﻟﺙ: 30%.

ﺇﻨﺸﺎﺀ Resource Manager Plans and Directive:

DECLARE

PLAN VARCHAR2(200);

GROUP_OR_SUBPLAN VARCHAR2(200);

COMMENT VARCHAR2(200);

CPU_P1 NUMBER;

CPU_P2 NUMBER;

CPU_P3 NUMBER;

ACTIVE_SESS_POOL_P١ NUMBER;

MAX_IDLE_TIME NUMBER;

BEGIN

PLAN := 'NEW_PLAN';

COMMENT := 'THIS IS NEW PLAN';

GROUP_OR_SUBPLAN := 'OTHER_GROUPS';

CPU_P1 :=20 ;

CPU_P2 :=25 ;

CPU_P3 :=30 ;

ACTIVE_SESS_POOL_P1 :=3 ;

MAX_IDLE_TIME := 2;

SYS.DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

SYS.DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

SYS.DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN,COMMENT );

SYS.DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ('NEW_PLAN', 'OTHER_GROUPS', 'DIRECTIVE TO OTHER_GROUPS ', 20 ,25,30,3,2 );

SYS.DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ('NEW_PLAN', 'SYS_GROUP', 'DIRECTIVE TO SYS_GROUP ',60,50,40,4,3 );

SYS.DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ('NEW_PLAN', 'NEW_GROUP', 'DIRECTIVE TO NEW_GROUP ', 20,25,30,2,1);

SYS.DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

SYS.DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

COMMIT;

END;

ﻫﻜﺫﺍ ﻗﻤﻨﺎ ﺒﺈﻨﺸﺎﺀ PLAN ﺘﺴﻤﻲ NEW_PLAN ﻭﺃﻴﻀﺎﹰ ﻗﻤﻨﺎ ﺒﺈﻨﺸﺎﺀ ﺜﻼﺙ Directives ﻤﻭﺠﻬﺔ للمجموعات (OTHER_GROUPS & SYS_GROUP & NEW_GROUP).

يجب ﺍﻟﺘﺫﻜﻴﺭ ﺒﺄﻨﻙ ﺘﺴﺘﻁﻴﻊ ﺍﻟﺘﻌﺎﻤل ﻤﻊ ﺜﻤﺎﻨﻴﺔ ﻤﺴﺘﻭﻴﺎﺕ، ﺒﻌﺩ ﺫﻟﻙ ﻴﻤﻜﻥ ﻤﻨﺢ ﺒﺎﻗﻲ ﺍﻟﻤﺘﻐﻴﺭﺍﺕ ﻤﺜل ﺍل active_sess_pool_p1 ﻭﻫﻲ ﻟﺘﺤﺩﻴﺩ ﺃﻜﺒﺭ ﻋﺩﺩ ﻟلSessions ﺍﻟﻨﺸﻁﺔ ﺍﻟﺘﻲ ﺘﻨﺘﻤﻲ

لConsumer Group ﻭﺍﺤﺩﺓ ﺒﻌﺩ ﻫﺫﺍ ﺍﻟﻌﺩﺩ ﺴﺘﻅل ﺍلSessions ﻓﻲ ﺼﻑ ﺍﻹﻨﺘﻅﺎﺭ. ﻭﺃﻴﻀﺎﹰ ﺍل max_idle_time ﻟﺘﺤﺩﻴﺩ ﺍﻟﻭﻗﺕ ﺍﻟﺫﻱ ﻴﻤﻜﻥ ﺃﻥ ﺘﻅﻠﻪ ﺍلSession ﻋﺎﻁﻠﺔ ﺒﻌﺩﻩ ﻴﺘﻡ ﺇﻨﻬﺎﺀ ﻫﺫﻩ

ﺍلSession ﻭﻫﻜﺫﺍ ﺒﺎﻗﻲ ﺍﻟﻤﺘﻐﻴﺭﺍﺕ.

ﺍﻻﻥ ﻨﺴﺘﻁﻴﻊ ﺘﻔﻌﻴل ﻫﺫﻩ ﺍلPlan ﻓﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ ﻋﻥ ﻁﺭﻴﻕ ﺍﻻﻤﺭ:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='NEW_PLAN';

ﻓﻲ ﻫﺫﻩ ﺍﻟﻠﺤﻅﺔ ﺴﻭﻑ ﻴﺘﻡ ﺘﻔﻌﻴل ﻫﺫﻩ ﺍلPlan ﻋﻠﻲ ﻗﺎﻋﺩﺓ ﺍﻟﺒﻴﺎﻨﺎﺕ.

ﻴﻤﻜﻨﻙ ﺍﻹﺴﺘﻌﻼﻡ ﻋﻥ ﺍلPlan ﻭﺍلDirective ﻋﻥ ﻁﺭﻴﻕ:

DBA_RSRC_PLANS

DBA_RSRC_PLAN_DIRECTIVES

------------------------------------------------------

DATABASE

SQL

VIDEOS

Orcale Tutorial Content

Managing Resources

Create Resource Manager Plans & Directive