Wrong parallelism degree for the heavy SQL commands
Posted by Kamran Agayev A. on 24th June 2015
Yesterday I got a call from the Datawarehouse team member who claimed that some reports started to run slowly. Hmm … Slowly … Right after I got a call, I checked the session and found that it was running with parallel degree 8. The degree value was too low. I checked parallelism parameters of the database:
SQL> set linesize 150
SQL> show parameter parallel_degree_policy
NAME TYPE VALUE
———————————— ——————————– ——————————
parallel_degree_policy string AUTO
SQL>
The automatic degree of parallelism future was enabled.
parallel_degree_limit parameter was set to CPU. As the formula to calculate the limit is PARALLEL_THREADS_PER_CPU * CPU_COUNT * the number of instances available, I got the necessary values and calculated
SQL> show parameter PARALLEL_THREADS_PER_CPU
NAME TYPE VALUE
———————————— ——————————– ——————————
parallel_threads_per_cpu integer 2
SQL>
SQL> show parameter cpu_count
NAME TYPE VALUE
———————————— ——————————– ——————————
cpu_count integer 47
SQL>
So the parallel_degree_limit=47*2=94 (which is pretty high)
So there is no issue regarding the SQL statement and the database level parallelism configuration. I got an execution plan of the same SQL command from SYS user, and here what I got after the execution plan:
Note
– automatic DOP: Computed Degree of Parallelism is 94 because of degree limit
Hmm… If I run the query with SYS user, the query runs with correct parallelism degree. If I run it with different user, I get the only 8 parallel sessions. The first thing came into my mind was Resource Manager.
I need to check the Resource Manager, but …. but …. there was a problem with the Enterprise Manager I know you’re smiling, because you should know how it’s hard to view and configure resource manager with command line interface.
No way, I don’t have an option to make the customer wait for me to configure the agent of the Grid Control. First of all, I queried DBA_RSRC_CONSUMER_GROUP_PRIVS view to get the list of consumer groups that the specific user is assigned:
SQL> SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS
2 where grantee=’PROD_USER’;
GRANTEE GRANTED_GROUP GRA INI
—————————— —————————— — —
PROD_USER CG_BATCHES NO NO
PROD_USER CG_REP_LOW NO NO
PROD_USER CG_REP_HIGH NO NO
PROD_USER CG_REP_NORMAL NO NO
SQL>
Then I queried DBA_RSRC_PLAN_DIRECTIVES view to get detailed information about the resource plan directives of the consumer groups that the specific user belongs to:
SQL> SELECT plan, group_or_subplan, cpu_p1, cpu_p2, cpu_p3, cpu_p4, parallel_degree_limit_p1
2 FROM DBA_RSRC_PLAN_DIRECTIVES
3 WHERE plan = ‘DWH_DAY_PLAN’
4 AND group_or_subplan IN (SELECT granted_group
5 FROM DBA_RSRC_CONSUMER_GROUP_PRIVS
6 WHERE grantee = ‘PROD_USER’);
PLAN GROUP_OR_SUBPLAN CPU_P1 CPU_P2 CPU_P3 CPU_P4 PARALLEL_DEGREE_LIMIT_P1
—————————— —————————— ———- ———- ———- ———- ————————
DWH_DAY_PLAN CG_REP_LOW 0 0 0 50 16
DWH_DAY_PLAN CG_REP_HIGH 0 65 0 0 32
DWH_DAY_PLAN CG_REP_NORMAL 0 0 80 0 24
DWH_DAY_PLAN CG_BATCHES 0 0 0 50 16
SQL>
So the highest parallelism degree was set for CG_REP_HIGH consumer group, thus the user should use that group. Next, I checked V$SESSION view to check which consumer groups are used by that user:
SQL>
SELECT resource_consumer_group, COUNT (1)
FROM v$session
WHERE username = ‘PROD_USER’
GROUP BY resource_consumer_group;
RESOURCE_CONSUMER_GROUP COUNT(1)
——————————– ———-
CG_REP_HIGH 70
OTHER_GROUPS 154
_ORACLE_BACKGROUND_GROUP_ 3
Ops. Most of the connections are using OTHER_GROUPS consumer group which has a very limited parallelism degree. So why the users were switching to the OTHER_GROUPS consumer group? The first reason was the switch consumer group feature. The sessions are either switched manually (which is not possible to happen) or automatically based on the CPU and I/O resource limits defined for that group (which is not defined in the group specification) So what is the problem? Ah, may be mapping priority. I checked the dba_rsrc_mapping_priority view to get the list of mapping priority:
SQL> SELECT * FROM dba_rsrc_mapping_priority;
ATTRIBUTE PRIORITY STATUS
—————————— ———- ——————————
EXPLICIT 1
SERVICE_MODULE_ACTION 2
SERVICE_MODULE 3
MODULE_NAME_ACTION 4
MODULE_NAME 5
SERVICE_NAME 6
ORACLE_USER 7
CLIENT_PROGRAM 8
CLIENT_OS_USER 9
CLIENT_MACHINE 10
CLIENT_ID 11
11 rows selected.
SQL>
It didn’t gave me enough information and I checked the consumer group usage based on oracle user, OS user, module name and etc. And what I found was that all users that come from the same machine (from where the heavy SQL commands were running) were using OTHER_GROUPS consumer group. I queried dba_rsrc_group_mappings view to get information about session attributes (such as module name, OS user and etc.) with the consumer groups, and provided the OS username as a parameter for the VALUE column:
SQL> SELECT * FROM dba_rsrc_group_mappings
WHERE value=’REP_USR’;
And didn’t get any value. I asked the guy who called me if they changed the username of the OS, and he told me “Yes” 2 days ago they migrated the software to the new machine and now uses different username to run the reports. (The old one was REPUSER) I queried the old user at DBA_RSRC_GROUP_MAPPINGS view and get the output!
SQL> col value format a15
SQL> col consumer_group format a15
SQL> select * from dba_rsrc_group_mappings
2 WHERE value=’REPUSER’;
ATTRIBUTE VALUE CONSUMER_GROUP STATUS
—————————— ————— ————— ——————————
CLIENT_OS_USER REPUSER CG_REP_HIGH
SQL>
So the previous user was assigned to the specific consumer group, and after the software moved to the new host and used new OS user to run the reports, it didn’t find to which consumer group it belongs and switched to OTHER_GROUPS consumer group. After founding it, I manually set the new OS user to the CG_REP_HIGH consumer group as follows:
BEGIN
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping (DBMS_RESOURCE_MANAGER.CLIENT_OS_USER,’REP_USR’,’CG_REP_HIGH’);dbms_resource_manager.validate_pending_area();
dbms_resource_manager.submit_pending_area();
END;
/
After assigning the user to the consumer group, we opened a new session to run the report and checked the consumer group of that session. It was as what excepted – CG_REP_HIGH. And we got all parallelism degrees back and users were happy
Posted in Uncategorized | 2 Comments »