Re: PDQPRIORITY AND PSORT_NPROCS: How do they work together

ssoman@omm.com wrote: > > Hi all, > IF you set PDQPRIORITY AND PSORT_NPROCS for a query, how do they work in > conjunction with each other ?If there are multiple scan threads working at a > time, do all of the have the same PDQPRIORITY ? Does it actually help setting > both the parameters, or do they counteract each other. First off, PSORT_NPROCS is only used for a query if you need a sort of some kind on non-indexed columns. It is used for running sorts in parallel in the following circumstances: ORDER BY clause GROUP BY clause Sort merge join Index builds. It determines the number of sort threads to run. PDQPRIORITY is used to allocate resources to a query. So these two have no direct relationship, except they could both come into play for the same query. Therefore; yes, it does help to set them both. > Can someone list the typical cases where one would use > PDQPRIORITY, and where one would use PSORT_NPROCS ? PDQPRIORITY is best used for complex queries which involve fragmented (partitioned) tables. Be careful not to hog all the resources, and be careful that most PDQ queries are not sitting waiting for resources. PSORT_NPROCS is best used to speed up sort processes. Based on the number of physical CPUs. > How would I know if PDQPRIORITY is not useful to a query ? Benchmark. > Would looking at onstat -g mgm and not seeing the query listed > imply that ?? If PDQPRIORITY is set, the query will always show up with onstat -g mgm, because the Memory Grant Manager (MGM) is in charge of allocating resources to it. Hope that helps, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+