I've asked a related question recently which led me to the following:
I have a v9.7 instance with typical OLTP queries running but also servicing larger more complex queries.
I would like to enable INTRA_PARALLEL and have MAX_QUERYDEGREE set to 2. We have an 8 CPU box.
In the old days there was a formula where MAXAGENTS = MAX_QUERYDEGREE * MAX_CONNECTIONS.
Since MAXAGENTS is now deprecated in v9.7, is there a similar formula for v9.7 to make sure you do not end up with high agent stealing activity?
This topic has been locked.
5 replies Latest Post - 2012-12-18T23:29:54Z by SystemAdmin
Pinned topic v9.7 MAX_QUERYDEGREE formula
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-12-18T23:29:54Z at 2012-12-18T23:29:54Z by SystemAdmin
Re: v9.7 MAX_QUERYDEGREE formula2012-12-13T23:38:03Z in response to SystemAdminAndreS,
Do you have the intra_parallel parm set to YES? I'm curious if controlling the max_querydegree is really benefiting your system :/
A setting of -1 allows the optimizer to determine an appropriate value. If you don't have intra_parallel turned on, I believe max_querydegree is ignored anyway. There are other ways to control max_querydegree at a query level instead of setting a global limit.
I had a conversation with one of the IBM engineers that help set up the iSAS appliances last year, and his recommendation is to set the intra_parallel parm to NO and max_querydegree to -1. The hardware we were setting up was a DPF environment with DB2 9.7, AIX 6.1 on 2 lpars with 6 DPF partitions, backed by a 320 drive SAN. The workload is both OLTP and OLAP and performs quite well.
Have you seen improvements by coding hard values for this parm?
bluey01 110000ENQX50 PostsACCEPTED ANSWER
Re: v9.7 MAX_QUERYDEGREE formula2012-12-14T02:39:59Z in response to SystemAdminHi Randy
When you say "There are other ways to control max_querydegree at a query level instead of setting a global limit." - what are some ways of doing this. Most of our DBs are ok, but there is one that is used for large queries and we want to get better performance from this DB.
Even a pointer to an IBM reference or this would be great.
Re: v9.7 MAX_QUERYDEGREE formula2012-12-14T09:31:13Z in response to SystemAdminHello,
I believe the recommandation for INTRA_PARALLEL NO is for DPF configuration.
It would have been YES for a sigle node configuration with complex queries.
Re: v9.7 MAX_QUERYDEGREE formula2012-12-18T23:29:54Z in response to SystemAdminHi All
So to answer my own question, it appears that in v9.7 at least, there is a clear link between "agents stolen" and INTRA_PARALLEL ON. I have tested this with other instances where I am running the same databases serving the same applications but where parallelism is turned off. Thos instances does not have an ever increasing "agents stolen" value.
Agenst stolen increases even with connection pooling and concentrator enabled and with more than enough max connections specified.
Re: v9.7 MAX_QUERYDEGREE formula2012-12-18T23:26:35Z in response to SystemAdminHi Randy
In my case I have a predominantly OTLP database but we do serve massive queries over 2000 chars in length joining 7+ tables. These kind of queries LOVE running with intra-parallelism on.
I know this from experience when I was wokign at another place and a SUN T2 solution was sold to a customer by our sales team to upgrade their Sun v440 server. We did a like for like move of DB2 from the v440 to the T2 and performance dropped considerably. Daytime apllication processing was fine but after hours batch processing performed like a dog.
What I've found was that DB2 was fielding complex queries returning multi row result sets in batch time and the threaded architecture of the sun T2 CPU was too light weight to service those single threaded. At the end we managed to get reasonable performance by enabling intra-parallelism with a high value for dft query degree.
During my investigation why the T2 was so slow, I've discovered that high core speed CPUs with lower "hardware threads" such as Intel, still adds benefits to a DB server when running multi threaded queries as long as the dft degree is kept to a smaller value.