Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 11, 2019 - 9:10 pm UTC

Last updated: October 22, 2019 - 2:58 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi ASKTOM team,
I am not very good with parallelism, so have a question about DW database. These are my current settings:

parallel_adaptive_multi_user    boolean TRUE   
parallel_automatic_tuning       boolean FALSE  
parallel_degree_limit           string  CPU    
parallel_degree_policy          string  MANUAL 
parallel_execution_message_size integer 16384  
parallel_force_local            boolean FALSE  
parallel_instance_group         string         
parallel_io_cap_enabled         boolean FALSE  
parallel_max_servers            integer 1120   
parallel_min_percent            integer 0      
parallel_min_servers            integer 0      
parallel_min_time_threshold     string  AUTO   
parallel_server                 boolean TRUE   
parallel_server_instances       integer 4      
parallel_servers_target         integer 448    
parallel_threads_per_cpu        integer 2


These are all at default level, nothing specified in SPFILE. The tables have DEGREE=2 or 4. Some of the bigger indexes have DEGREE=DEFAULT. But what I am seeing is that even without any hint, many users are being allocated 168 or 190 or more parallel slaves. This is causing major problems because CPU goes to 90%+. What I want to do is to set a parameter (or parameters) at database level, so no matter what the DEGREE is on the table/index or what a user specifies in a hint, any user whose query runs with parallelism, gets a max of 8 parallel query slaves. Is that possible?

Thanks

and Connor said...

Check out parallel_degree_limit. From the docs:

Values

CPU

The maximum degree of parallelism is limited by the number of CPUs in the system. The formula used to calculate the limit is PARALLEL_THREADS_PER_CPU * CPU_COUNT * the number of instances available (by default, all the opened instances on the cluster but can be constrained using PARALLEL_INSTANCE_GROUP or service specification). This is the default.

IO

The maximum degree of parallelism the optimizer can use is limited by the I/O capacity of the system. The value is calculated by dividing the total system throughput by the maximum I/O bandwidth per process. You must run the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure on the system to use the IO setting. This procedure will calculate the total system throughput and the maximum I/O bandwidth per process.

integer

A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to ADAPTIVE, AUTO, or LIMITED.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Confused

A reader, October 22, 2019 - 5:15 am UTC

"Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to ADAPTIVE, AUTO, or LIMITED. "

Requester posted his PARALLEL_DEGREE_POLICY as MANUAL.

So confusing why is happening such parallelism??
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.