Hello @all,
this is my first post here.
I have a question:

We have a "big" production database working as our datawarehouse.
Beside the loader processes, the reporting is done via cognos reports.
In order to speed up the huge generated sqls over partitioned tables and indexes, the team leader of our developers "forced" us to use parallel query.
This indeed gave some long-runners more speed, but too few sessions participate on parallelism because the stock of available parallel servers is limited.


parallel_server,FALSE
parallel_server_instances,1
recovery_parallelism,0
fast_start_parallel_rollback,LOW
parallel_min_percent,0
parallel_min_servers,100
parallel_max_servers,300
parallel_instance_group,,
parallel_execution_message_size,16384
parallel_degree_policy,MANUAL
parallel_adaptive_multi_user,TRUE
parallel_threads_per_cpu,2
parallel_automatic_tuning,FALSE
parallel_io_cap_enabled,FALSE
parallel_min_time_threshold,AUTO
parallel_degree_limit,CPU
parallel_force_local,FALSE
parallel_servers_target,256

Where parallel_min_servers and parallel_max_servers are the only "not default" parameters in this context.



The idea of our dev-teams are to generally give each table and index a degree of 2.

I could not tell, how many times i tried to tell them, that parallel query is for single long running batch processes. Not really for 20-40 parallel reporting sqls.
Another fact is that as long as Cognos delivers the result set in html-pages and the user browse through the result set, the parallel servers of that session are blocked. Even the user let the result set open maybe the whole day.

Can i reduce the max parallel servers a whole query uses, not only for each subquery etc?
How did you handle this? Did you enable parallel query for reporting purposes?

Thank you,
gosar