Questions re NUMCPUVPS, PDQPRIORTY, AFF_NPROCS and AFF_SPROC

Hi, Not sure if anyone can shed some light on a couple of questions I have about Dynamic Server and NUMCPUVPS, PDQPRIORTY, AFF_NPROCS and AFF_SPROC ? Firstly our setup: We are running a data replication setup (2 systems each running Dynamic Server 9.21-FC3x-1 on HP-UX 11.0). The primary server has 6 CPU's and has the following configuration parameters: SHMTOTAL 0 SHMADD 96000 MULTIPROCESSOR 1 NUMCPUVPS 6 AFF_SPROC 0 AFF_NPROCS 0 MAX_PDQPRIORITY 100 DS_MAX_QUERIES 1280 DS_TOTAL_MEMORY 163840 DS_MAX_SCANS 1048576 The secondary read-only server has 4 CPU's and has the following configuration parameters: SHMTOTAL 0 SHMADD 96000 MULTIPROCESSOR 1 NUMCPUVPS 4 AFF_SPROC 0 AFF_NPROCS 0 MAX_PDQPRIORITY 100 DS_MAX_QUERIES 1280 DS_TOTAL_MEMORY 163840 DS_MAX_SCANS 1048576 We are not using processor affinity. CPU VP's can be serviced/executed by any of the 6 physical CPU's on the primary server. We have 900+ users accessing the primary server and we don't appear to have many issues - things seem to run quite well (although I am sure someone out there could tune the system a little more !). We are not implementing Kernel AIO, and all chunks are RAW. Question 1: Based on real-world experience, can anyone let me know how to decide what the NUMCPUVPS should be set at ? We currently have NUMCPUVPS = # Physical CPU's......and I repeat there do not seem to be any issues. I have read many articles on setting the NUMCPUVP parameter for various versions of Dynamic Server from 7.21 to 9.21 and these articles have mentioned: a) NUMCPUVPS should be set equal to the # of physical CPU's, or b) NUMCPUVPS should be set to 1 less than the # of physical CPU's, or c) NUMCPUVPS can be set to > than the # of physical CPU's (for older versions of Dynamic Server on HP-UX 10.2 that had bugs with using the NOAGE, AFF_NPROCS and AFF_SPROC parameters). Question 2: How do we determine when the engine determines a query uses PDQ and when it doesn't ? a) When we execute the following SQL script and display stats for the memory grant manager using "onstat -g mgm | more", we see that engine does NOT appear to be using PDQ: set isolation to dirty read; set pdqpriority 60;select sum(f.original_loan_amt) from table_name_1 a, table_name_2 b, table_name_3 c, table_name_4 f where f.credit_appl_id = a.credit_appl_id and b.credit_appl_id = a.credit_appl_id and c.operator_code = a.alt_ch_operator_cd and b.person_id = 1 and a.alt_ch_branch_num LIKE '032448%'; Details from "onstat -g mgm | more" show: Informix Dynamic Server 2000 Version 9.21.FC3X1 -- On-Line (Prim) -- Up 15 days 11:10:56 -- 796972 Kbytes Memory Grant Manager (MGM) -------------------------- MAX_PDQPRIORITY: 100 DS_MAX_QUERIES: 1280 DS_MAX_SCANS: 1048576 DS_TOTAL_MEMORY: 163840 KB Queries: Active Ready Maximum 0 0 1280 Memory: Total Free Quantum (KB) 163840 163840 128 Scans: Total Free Quantum 1048576 1048576 1 Load Control: (Memory) (Scans) (Priority) (Max Queries) (Reinit) Gate 1 Gate 2 Gate 3 Gate 4 Gate 5 (Queue Length) 0 0 0 0 0 Active Queries: None Ready Queries: None Free Resource Average # Minimum # -------------- --------------- --------- Memory 11946.7 +- 10067.9 0 Scans 1048575.0 +- 0.0 1048575 Queries Average # Maximum # Total # -------------- --------------- --------- ------- Active 1.0 +- 0.0 1 6 Ready 0.0 +- 0.0 0 0 Resource/Lock Cycle Prevention count: 0 b) However, when we modify the above SQL script to: set isolation to dirty read; set pdqpriority 60;select f.credit_appl_id from table_name_1 a, table_name_2 b, table_name_3 c, table_name_4 f where f.credit_appl_id = a.credit_appl_id and b.credit_appl_id = a.credit_appl_id and c.operator_code = a.alt_ch_operator_cd and b.person_id = 1; Then re-execute it and check using "onstat -g mgm | more" we see the engine is using PDQ: Informix Dynamic Server 2000 Version 9.21.FC3X1 -- On-Line (Prim) -- Up 15 days 11:18:40 -- 796972 Kbytes Memory Grant Manager (MGM) -------------------------- MAX_PDQPRIORITY: 100 DS_MAX_QUERIES: 1280 DS_MAX_SCANS: 1048576 DS_TOTAL_MEMORY: 163840 KB Queries: Active Ready Maximum 1 0 1280 Memory: Total Free Quantum (KB) 163840 163840 128 Scans: Total Free Quantum 1048576 1048575 1 Load Control: (Memory) (Scans) (Priority) (Max Queries) (Reinit) Gate 1 Gate 2 Gate 3 Gate 4 Gate 5 (Queue Length) 0 0 0 0 0 Active Queries: --------------- Session Query Priority Thread Memory Scans Gate 79493 c00000001c89cbd0 60 c0000000175096e0 0/0 1/1 - Ready Queries: None Free Resource Average # Minimum # -------------- --------------- --------- Memory 13165.7 +- 9740.2 0 Scans 1048575.0 +- 0.0 1048575 Queries Average # Maximum # Total # -------------- --------------- --------- ------- Active 1.0 +- 0.0 1 7 Ready 0.0 +- 0.0 0 0 Resource/Lock Cycle Prevention count: 0 Any help would be great !!!!! Thanks, Damion Reeves Informix Database Administrator EDS Australia Adelaide Solution Centre