Re: Performance degraded after detaching indexes / fragme

Thomas J. Girsch wrote: > Be careful here, Art. On a 4 CPU system, PDQPRIORITY=25 is NOT exactly the > same as PDQPRIORITY=2. All kinds of resources are controlled by > PDQPRIORITY, not just CPUs. More important parameters controlled by PDQ are > memory and total threads. > We have an instance the has only 3 CPU VPs, and I can assure you that with > PDQPRIORITY=10 it uses all three CPUs, not just one, as you claim below. > No, what you're assigning with PDQPRIORITY is the maximum amount of memory > and maximum number of threads available to a query. > It's also important to note that setting PDQPRIORITY=25 does not necessarily > give you 25% of the system resources. It gives you 25% of MAXPDQPRIORITY% > of the resources. So to use a simple example, let's say DS_TOTAL_MEMORY is > set to 800 MB, and MAXPDQPRIORITY is set to 50. PDQPRIORITY=25 would give > you 100 MB of memory, not 200. It gives you 25% of 50% of 800 MB, or 25% of > 400 MB. Confusing? Yes, but it's important to understand. > >Mario note that on a 4 CPU system PDQPRIORITY=25 is EXACTLY the same as > >PDQPRIORITY=2 since PDQPRIORITY is the % of available resources and you > >have 4 CPUs PDQPRIORITY=25 means "Use only one CPU" which is exactly > >what happens with PDQPRIORITY < 25 on your system. Remember that the > >granularity of PDQPRIORITY is the available granularity of the > >resources available. On a 24 CPU system like our Sun E6000s the > >granularity of PDQPRIORITY is 5 (we only use 20 CPU VPs) on yours it is > >25 so you would have to set it between 26 and 50 to get even a second > >thread involved in your query. On your E6000 the granularity is > >between 7 & 8 so you could test the effects of PDQ more readily there. I apologize for being overly simplistic. I was specifically addressing Mario's comments about not gaining anything be using more CPUs and made an unspecified assumption. I understand about PDQPRIORITY under MAXPDQPRIORITY constraint, again I was keeping things simple. I still hold that with PDQPRIORITY 25 on a 4CPU system you are not taking full advantage of the CPUs especially, as Mario has stated, you are concurrently running three other queries. Indeed I would propose that it would not run any faster if the other three queries were not running at all. I maintain that if you were to take that onto the 13 CPU VP system and ran it with PDQPRIORITY=31 (in my calculations pressing 4CPU VPs into service) with the same 3 other jobs sharing the remaining PDQPRIORITY or 69, or on my 28 CPU VP system with PDQPRIORITY=15, assuming the same individual CPUs, the query would complete faster. Art S. Kagel