Unanswered: Parallel Query for reporting sqls on a Datawarehouse.
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.
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?