Thanks for the question, sandeep.
Asked: July 25, 2006 - 5:28 pm UTC
Last updated: July 28, 2006 - 7:48 pm UTC
Version: 9.2.0.4
Viewed 1000+ times
You Asked
Users are complaining a very slow response time for queries. I observed that most of them do not run parallel, hence downgrading performance. Same query runs parallel at 1 time returning results in 3mins and at some other time, it doesnt and take ages to work.
I have 16CPU 16G RAM tru64 hp server.
Here are my parameters set.
Do you think parallel_max_servers should be high and please comment on processes. It looks to high. I am confused with the part, if I have automatic parallel turned on, why do I need to adjust these other parameters --
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean TRUE
parallel_execution_message_size integer 8192
parallel_max_servers integer 128
parallel_min_servers integer 0
parallel_server_instances integer 1
parallel_threads_per_cpu integer 16
cpu_count integer 16
parallel_threads_per_cpu integer 16
processes integer 600
Tom, parallel_threads_per_cpu is already set since long. I did not set it. And I thought since this is a 16cpu machine, having the parameter set to 16 is the optimal way. Do you think this is the problem?
Slower response time is when there are more concurrent users on the system. In that case, lots of the user queries seem to be NOT running parallel. Do you think due to this parameter setting, system is using all the resources to run only couple of queries parallel and so other queries are running on single thread?
Also, wanted to mention that, I have lot of table build scripts which truncate and insert/*+ APPEND PARALLEL(x,8) */ hint. Do you think I should remove DOP from here??
THanks a lot Tom.
and Tom said...
you do not want 16 processes per cpu - I would suggest letting them default to more normal (much lower) values.
The system is working *as designed*. You have finite resources (16 cpus). When lots of users try to use it concurrently, the automatic parallel tuning kicks in and each user session gets less and less of this shared resource.
If you have 32 concurrently active users - I would hope they would be using serial processing at that point - you just don't have any CPU to give them beyond that!
<quote src=Effective Oracle by Design>
Parallel Processing Scalability
The other thing people get wrong with parallel query is its scalability. Parallel query is not a scalable solution! Parallel query was initially designed so that a single user in a data warehouse could completely and totally consume 100% of the resources on that machine. So, if you had a 16-CPU big box with gobs of RAM and tons of disk space, you could write a single query that would be able to make use of the entire machine. Well, if you have two people doing that, they will both be competing for the same resources—they will both try to consume 100% of the machine. Take that up to 100 users, and you can easily see the problem.
This is one of the reasons I like the parallel automatic tuning with the adaptive multiuser routines. If I am the only one on the machine, I can fully consume all of the resources. If, on the other hand, I run the same query when there are 50 other users logged in and running queries, I will most likely get far fewer resources (parallel threads) than I did when I was the sole user of the machine. The degree of parallelism is not fixed. It adapts to the current workload, making parallel query a more scalable solution than before. Instead of killing the machine or having queries fail because they could not get sufficient resources, I can let the machine adjust its resource usage over time. This provides the best of both worlds and what end users expect from a system. They know that when it is busy, it will run slower.
</quote>
Rating
(4 ratings)
Is this answer out of date? If it is, please let us know via a Comment