PDQPRIORITY

Hello, IDS 11.10.FC2W1 on Solaris 10 (4 Dual core CPU , 16 GB RAM). I am a little confused about the Onconfig Parameter MAX_PDQPRIORITY . we have it as MAX_PDQPRIORITY 100. no PDQPRIORITY parameter set in informix.profile,nor do we have any appln sql starting with SET PDQPRIORITY _ ; Does MAX_PDQPRIORITY 100 in onconfig implies all the SQL's will run with PDQPRIORITY 100 i guess no but then will the DB decide PDQPRIORITY for SQL's. my observation in onstat -g ses pid If i set PDQPRIORITY 100 before any sql statement only then it runs with a PDQPRIORITY =100 . I also observed that when i run a simple sql (select * from table)with PDQPRIORITY > 0 the i see followin in onstat -g ses sid tid name rstcb flags curstk status 138 sqlexec 1e5b87c00 Y--P--- 7791 cond wait sm_read - 139 scan_1.0 1e5b84b70 ------- 3711 sleeping secs: 2 - Now this status -> sleeping secs: 2 is not there when i run the same Select with set PDQPRIORITY 0 ; tid name rstcb flags curstk status 138 sqlexec 1e5b87c00 Y--P--- 7791 cond wait sm_read - and here the tid 139 row is not there. Can some body please explain me what is the difference . How do i set PDQPRIORITY 1 for all SQL's running in all databases for IDS 11.10.FC2W1 So that i have all SQL's (appln + dba)running with PDQPRIORITY 1. For achieving this do i have to add a line PDQPRIORITY in the onconfig file or some another setting? Have i missed something. Our db is nearly a OLTP .Please let me know what should be the PDQPRIORITY value & with what all parameter's do i have to implement the value for all SQL's that run in all of my databases. Thanking you all vikas.

You said "Our db is nearly a OLTP", so the MAX_PDQPRIORITY in your onconfig should be 0 but this won't take effect until you bounce the instance. However, you can dynamically force all sessions running w/o pdq but (I believe) issue "onmode -D 0" ----- Original Message ---- From: VIKAS HIVARKAR <vikashivarkar3@gmail.com> To: ids@iiug.org Sent: Friday, April 4, 2008 10:45:27 AM Subject: PDQPRIORITY [11782] Hello, IDS 11.10.FC2W1 on Solaris 10 (4 Dual core CPU , 16 GB RAM). I am a little confused about the Onconfig Parameter MAX_PDQPRIORITY . we have it as MAX_PDQPRIORITY 100. no PDQPRIORITY parameter set in informix.profile,nor do we have any appln sql starting with SET PDQPRIORITY _ ; Does MAX_PDQPRIORITY 100 in onconfig implies all the SQL's will run with PDQPRIORITY 100 i guess no but then will the DB decide PDQPRIORITY for SQL's. my observation in onstat -g ses pid If i set PDQPRIORITY 100 before any sql statement only then it runs with a PDQPRIORITY =100 . I also observed that when i run a simple sql (select * from table)with PDQPRIORITY > 0 the i see followin in onstat -g ses sid tid name rstcb flags curstk status 138 sqlexec 1e5b87c00 Y--P--- 7791 cond wait sm_read - 139 scan_1.0 1e5b84b70 ------- 3711 sleeping secs: 2 - Now this status -> sleeping secs: 2 is not there when i run the same Select with set PDQPRIORITY 0 ; tid name rstcb flags curstk status 138 sqlexec 1e5b87c00 Y--P--- 7791 cond wait sm_read - and here the tid 139 row is not there. Can some body please explain me what is the difference . How do i set PDQPRIORITY 1 for all SQL's running in all databases for IDS 11.10.FC2W1 So that i have all SQL's (appln + dba)running with PDQPRIORITY 1. For achieving this do i have to add a line PDQPRIORITY in the onconfig file or some another setting? Have i missed something. Our db is nearly a OLTP .Please let me know what should be the PDQPRIORITY value & with what all parameter's do i have to implement the value for all SQL's that run in all of my databases. Thanking you all vikas. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! ________________________________________________________________________________ ____ You rock. That's why Blockbuster's offering you one month of Blockbuster Total Access, No Cost. http://tc.deals.yahoo.com/tc/blockbuster/text5.com

VIKAS HIVARKAR wrote: MAX_PDQPRIORITY is applied as a percentage multiplier against any user setting of PDQPRIORITY and so acts to limit the maximum PDQPRIORITY setting. See the table below: MAX_PDQPRIORITY PDQPRIORITY setting Effective PDQPRIORITY 100 100 100 100 50 50 75 100 75 75 50 37.5 10 100 10 10 50 3.5 0 100 0 Like that. Oh, while you probably don't need PDQPRIORITY for user sessions in an OLTP environment, DO NOT set MAX_PDQPRIORITY to zero! You will want to set PDQPRIORITY to something other than zero if you are using fragmented tables and want to take advantage of fragment elimination and parallel query. Also for maintenance like index builds and update statistics processing a high PDQPRIORITY is necessary for reasonable processing time. Also there will still be larger DSS style report queries in an OLTP system that will have to sort data and without PDQPRIORITY there can be no parallel sorting so such queries will tend to be too slow. Art S. Kagel Oninit > Hello, > > IDS 11.10.FC2W1 on Solaris 10 (4 Dual core CPU , 16 GB RAM). > > I am a little confused about the Onconfig Parameter MAX_PDQPRIORITY . > > we have it as MAX_PDQPRIORITY 100. > no PDQPRIORITY parameter set in informix.profile,nor do we have any appln sql > starting with SET PDQPRIORITY _ ; > > Does MAX_PDQPRIORITY 100 in onconfig implies all the SQL's will run with > PDQPRIORITY 100 i guess no but then will the DB decide PDQPRIORITY for SQL's. > > my observation in onstat -g ses pid > If i set PDQPRIORITY 100 before any sql statement only then it runs with a > PDQPRIORITY =100 . > I also observed that when i run a simple sql (select * from table)with > PDQPRIORITY > 0 the i see followin in onstat -g ses sid > tid name rstcb flags curstk status > 138 sqlexec 1e5b87c00 Y--P--- 7791 cond wait sm_read - > 139 scan_1.0 1e5b84b70 ------- 3711 sleeping secs: 2 - > > Now this status -> sleeping secs: 2 is not there when i run the same Select > with set PDQPRIORITY 0 ; > tid name rstcb flags curstk status > 138 sqlexec 1e5b87c00 Y--P--- 7791 cond wait sm_read - > > and here the tid 139 row is not there. > Can some body please explain me what is the difference . > > How do i set PDQPRIORITY 1 for all SQL's running in all databases for IDS > 11.10.FC2W1 So that i have all SQL's (appln + dba)running with PDQPRIORITY 1. > > For achieving this do i have to add a line PDQPRIORITY in the onconfig file or > some another setting? > > Have i missed something. > > Our db is nearly a OLTP .Please let me know what should be the PDQPRIORITY > value & with what all parameter's do i have to implement the value for all > SQL's that run in all of my databases. > > Thanking you all > vikas. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > > >

Vikas, in onconfig PDQPRIORITY 1 and restart IDS or in /etc/profile PDQPRIORITY=1 and restart server. Regards, V.Boura "VIKAS HIVARKAR" <vikashivarkar3@gmail.com> Odeslal: ids-bounces@iiug.org 04.04.2008 16:45 Odpovìzte prosím u¾ivateli ids@iiug.org Komu ids@iiug.org Kopie Pøedmìt PDQPRIORITY [11782] Hello, IDS 11.10.FC2W1 on Solaris 10 (4 Dual core CPU , 16 GB RAM). I am a little confused about the Onconfig Parameter MAX_PDQPRIORITY . we have it as MAX_PDQPRIORITY 100. no PDQPRIORITY parameter set in informix.profile,nor do we have any appln sql starting with SET PDQPRIORITY _ ; Does MAX_PDQPRIORITY 100 in onconfig implies all the SQL's will run with PDQPRIORITY 100 i guess no but then will the DB decide PDQPRIORITY for SQL's. my observation in onstat -g ses pid If i set PDQPRIORITY 100 before any sql statement only then it runs with a PDQPRIORITY =100 . I also observed that when i run a simple sql (select * from table)with PDQPRIORITY > 0 the i see followin in onstat -g ses sid tid name rstcb flags curstk status 138 sqlexec 1e5b87c00 Y--P--- 7791 cond wait sm_read - 139 scan_1.0 1e5b84b70 ------- 3711 sleeping secs: 2 - Now this status -> sleeping secs: 2 is not there when i run the same Select with set PDQPRIORITY 0 ; tid name rstcb flags curstk status 138 sqlexec 1e5b87c00 Y--P--- 7791 cond wait sm_read - and here the tid 139 row is not there. Can some body please explain me what is the difference . How do i set PDQPRIORITY 1 for all SQL's running in all databases for IDS 11.10.FC2W1 So that i have all SQL's (appln + dba)running with PDQPRIORITY 1. For achieving this do i have to add a line PDQPRIORITY in the onconfig file or some another setting? Have i missed something. Our db is nearly a OLTP .Please let me know what should be the PDQPRIORITY value & with what all parameter's do i have to implement the value for all SQL's that run in all of my databases. Thanking you all vikas. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

Vaclav Boura wrote: > Vikas, > in onconfig PDQPRIORITY 1 and restart IDS or in /etc/profile PDQPRIORITY=1 > and restart server. > PDQPRIORITY is no longer a valid ONCONFIG parameter for the instance but ONLY an environment variable that effects user sessions ONLY. Even setting it in the server's environment does not serve as a default for sessions that do not set their own. The default PDQPRIORITY for ALL sessions that do not either set the environment variable or execute a SET PDDQPRIORITY statement in the session is zero (0) regardless of the MAX_PDQPRIORITY setting in the ONCONFIG file. As IB Jonathan alluded to, the most common reason to see extensive multi-threaded sessions when PDQPRIORITY was not set is to have stored routines compiled with a non-zero PDQPRIORITY in which case they will execute with the PDQPRIORITY that was active at compile time. If they are compiled with PDQPRIORITY zero, then they execute with the user sessions' PDQPRIORITY settings. For this reason dostats automatically and explicitely sets PDQPRIORITY to zero before executing update statistics for stored procedures and functions regardless of the PDQPRIORITY setting it was using for tables (environment or -P) unless specifically asked to use a different setting (-Q). Art S. Kagel Oninit > Regards, > V.Boura > > "VIKAS HIVARKAR" <vikashivarkar3@gmail.com> > Odeslal: ids-bounces@iiug.org > 04.04.2008 16:45 > Odpovìzte prosím u¾ivateli > ids@iiug.org > > Komu > ids@iiug.org > Kopie > > Pøedmìt > PDQPRIORITY [11782] > > Hello, > > IDS 11.10.FC2W1 on Solaris 10 (4 Dual core CPU , 16 GB RAM). > > I am a little confused about the Onconfig Parameter MAX_PDQPRIORITY . > > we have it as MAX_PDQPRIORITY 100. > no PDQPRIORITY parameter set in informix.profile,nor do we have any appln > sql > starting with SET PDQPRIORITY _ ; > > Does MAX_PDQPRIORITY 100 in onconfig implies all the SQL's will run with > PDQPRIORITY 100 i guess no but then will the DB decide PDQPRIORITY for > SQL's. > > my observation in onstat -g ses pid > If i set PDQPRIORITY 100 before any sql statement only then it runs with a > > PDQPRIORITY =100 . > I also observed that when i run a simple sql (select * from table)with > PDQPRIORITY > 0 the i see followin in onstat -g ses sid > tid name rstcb flags curstk status > 138 sqlexec 1e5b87c00 Y--P--- 7791 cond wait sm_read - > 139 scan_1.0 1e5b84b70 ------- 3711 sleeping secs: 2 - > > Now this status -> sleeping secs: 2 is not there when i run the same > Select > with set PDQPRIORITY 0 ; > tid name rstcb flags curstk status > 138 sqlexec 1e5b87c00 Y--P--- 7791 cond wait sm_read - > > and here the tid 139 row is not there. > Can some body please explain me what is the difference . > > How do i set PDQPRIORITY 1 for all SQL's running in all databases for IDS > 11.10.FC2W1 So that i have all SQL's (appln + dba)running with PDQPRIORITY > 1. > > For achieving this do i have to add a line PDQPRIORITY in the onconfig > file or > some another setting? > > Have i missed something. > > Our db is nearly a OLTP .Please let me know what should be the PDQPRIORITY > > value & with what all parameter's do i have to implement the value for all > > SQL's that run in all of my databases. > > Thanking you all > vikas. > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ------------------------------------------------------------------------ > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!!