Version-11gR1 , datawarehouse
When queries are executed in parallel the query is performing very slow.as many such parallel queries run at a time. Previously it was not the case , but as the data is grown now it's taking time.
Please let me know if any parameter to tune to improve parallelism.
Queries are run on big tables ,around 40GB size of tables ..Pls. Advice
CPU utilization is just 20% and 10G memory also is available , only 10% of swapping memory in use.
Yes, it's set to manual, we 'll try putting it in auto and check. IF any other recommendation ?
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
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_io_cap_enabled boolean FALSE
parallel_max_servers integer 485
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 1024
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
When the same parallel query run alone the query executes faster in 10 min. but when ran in parallel with other queries during peak hours it takes 30 minutes.
Whenever there is a performance issue, directly jumping into changing few DB level parameters to get it fixed is a bad idea. And does not work most of the time. Every performance problem need to be addressed independently. You need to identify the underlying bottleneck. Without that trying to find a solution is just like shooting in the dark. There are various oracle supplied tools that can be used to identify performance bottleneck.
Once you are done with that and still have issues I would suggest you this thread HOW TO: Post a SQL statement tuning request - template posting by RandolfGeist. This lets you know what are the details you need to provide in a public forum when asking for a performance tuning request.
Perhaps not? Could well be that the PQ pool is exhausted and the query is run serially. Or maybe there is a global cache issue that causes serialisation.
One needs to first determine WHAT is happening is the point I raised above. Claiming that parallel query is no longer working correctly, based on mere observation of elapsed run-time, is a very dangerous and fundamentally incorrect claim - as it lacks any evidence to substantiate that claim.
Heck it could be that a fibre channel port failed, reducing I/O bandwidth with 50%. Or that 2 of the 4 cluster nodes are down, reducing CPU and I/O capacity with 50%. It could be due to a rebalance of the ASM diskgroup(s) being done by the DBA, at power 11, seriously degrading I/O performance. Etc.
Claims like the one made is just plain silly - it MUST be backed up with HARD technical evidence. Else is not worth the bits used to write it in..
Ya.. It's IO issue.Higher waits observed in User IO, and few queries found which requires tuning which includes these parallel queries and
for few queries no recommendastion available when run tuning advisor but Full table scan was observed for explain plan.
For SQL tuning we are working with developers, just wanted to make sure whether anything can be done to improve the parallelism.
We do not know what analysis you did. We cannot see the results. So you need to tell us the exact details.
Higher I/O waits mean nothing without context. Higher than what? What is the baseline? How was the I/O measured? For which processes?
I/O waits are a fact of live when dealing with spinning rust.