In this article I’m going to disclose Oracle Resource Manager Internal plan and how it can influence on database performances.
Many of Oracle DBA are convinced that Resource Manager is not active, and that RM has to be enabled to be active.
To confirm that theory, DBA can execute the following statement:
select name, display_value, description from v$parameter where name like 'resource_manager_plan'; resource_manager_plan Null resource mgr top plan
But that is only partly correct, as one of my client observe the following wait event in AWR report:
By searching the net, it’s clear that event I srelated to session waiting to be allocated a quantum of CPU time.
This comes as a big surprise as that event is connected with Resource Manager which is disabled, and DBA claims RM is disabled.
Although user defined RM plan is not enabled by default, RM have two additional plans that are enabled by default.
While the former one is well known by now (it is responsible for statistics maintenance, segment adviser etc., and is enabled by defined Scheduler Window), INTERNAL_PLAN is still a mystery.
First let’s check if that plan is enabled or not, by executing the following SQL:
select name, is_top_plan, cpu_managed, instance_caging, parallel_servers_total from v$rsrc_plan; INTERNAL_PLAN TRUE OFF OFF 10
Even if instance caging (might be covered in future) is not enabled, we have limits for PARALLEL_SERVERS_TOTAL, which is currently 10.
Now, if I reduce the number of PARALLEL_SERVER_TOTAL value from 10 → 2:
alter system set parallel_servers_target = 2 scope = memory;
and execute the same command again:
select name, is_top_plan, cpu_managed, instance_caging, parallel_servers_total from v$rsrc_plan; INTERNAL_PLAN TRUE OFF OFF 2
PARALLEL_SERVER_TOTAL is now 2.
As RM custom plan is not enabled, and instance caging is also off, when that event can be seen?
As you can see that I can influence on PARALLEL_SERVER_TOTAL column by changing the parameter PARALLEL_SERVERS_TARGET, which is the number of parallel server processes allowed to run in parallel before statement queuing will be used, and it has to be set to:
0.75x PARALLEL_MAX_SERVERS (maximum number of parallel servers processes allowed to run on the system),
wait event will be visible only when running SQL statements in parallel.
If you see in AWR report the following wait event among top events:
I hope you’ll know what it is, why you can see it and how to resolve it.