Re: IDS Features - MAX_PDQPRIORITY

Hi, Did you find this information in an official Informix manual?. If this is the case please let us know the name and page in the manual to open a documentation defect. Fragment elimination is INDEPENDANT of PDQPRIORITY settings. The following small testcase proves this. Notice that PDQPRIORITY is set to zero, the selects obviouly skip the fragments that do not meet the domain. The text in red shows the elimination of the correct fragments Regards $ cat fragment.sql drop database gusprueba; create database gusprueba in rootdbs; create table t1 (c1 integer, c2 char(32)) fragment by expression c1 < 1000 in dbspace1, c1 >=1000 in dbspace2; insert into t1 values(1, "gustavo" ); insert into t1 values(1000, "castro" ); set PDQPRIORITY 0; set explain on; select * from t1 where c1 < 1000; select * from t1 where c1 >= 1000; $ dbaccess - fragment.sql Database dropped. Database created. Table created. 1 row(s) inserted. 1 row(s) inserted. PDQ Priority set. Explain set. c1 c2 1 gustavo 1 row(s) retrieved. c1 c2 1000 castro 1 row(s) retrieved. Database closed. $ cat sqexplain.out QUERY: ------ select * from t1 where c1 < 1000 Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.t1: SEQUENTIAL SCAN (Serial, fragments: 0) Filters: informix.t1.c1 < 1000 QUERY: ------ select * from t1 where c1 >= 1000 Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.t1: SEQUENTIAL SCAN (Serial, fragments: 1) Filters: informix.t1.c1 >= 1000 Art Kagel wrote: > Just double checked. We're both wrong. With PDQPRIORITY zero no > parallelism is enabled. PDQPRIORITY 1 or LOW enables parallel scans > ONLY. PDQPRIORITY 2 or greater enables fragment elimination, parallel > sorting, and MGM memory use. > > On Dec 28, 2007 10:47 AM, Gustavo Castro <gcastroc@bellsouth.net > <mailto:gcastroc@bellsouth.net>> wrote: > > Hi > > As far as I remember fragment elimination is always ON. What is > activated at PDQPRIORITY of 1 is parallel scans. > > regards > > Gustavo > > Art S. Kagel wrote: > > On Dec 27, 4:36 pm, Dave < In4Mix...@gmail.com > <mailto:In4Mix...@gmail.com>> wrote: > > > >> Hello, > >> > >> Aside from fragment elimination, are there any other features > that are > >> enabled by IDS that result from setting MAX_PDQPRIORITY to 1 (or > >> greater)?? > >> > >> Any help would be greatly appreciated. > >> > > > > At PDQPRIORITY 1 only fragment elimination is activated. At > > PDQPRIORITY 2 or greater all other parallelization features are > > enabled with the degree of parallelism controlled by the > > MAXPDQPRIORITY governor and using the resulting PDQ level as a > > percentage of available resources. So, if there are 24 CPU VPS and > > you have and effective PDQ level of 10% (PDQPRIORITY * > MAXPDQPRIORTY) > > then you will get parallel query enabled with as many threads as > > required but only 2 CPU VPs running those threads. With > effective PDQ > > level 50% you'll have up to 12 CPU VPs processing your threads. > MGM > > memory is similarly doled out according to the number of active PDQ > > Queries and the PDQ level. > > > > Note that if a PDQ query cannot get access to the level of resources > > it requires due to competition from other PDQ queries it will wait > > until the required resources are available. This means that if a > > query is running that has requested a PDQPRIORITY of 100 it > cannot run > > until all preexisting PDQ queries have completed and no new PDQ > > queries can run until it completes. They will be placed on the wait > > queue. > > > > Art S. Kagel > > > > > >> --Dave > >> > > > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org <mailto:Informix-list@iiug.org> > > http://www.iiug.org/mailman/listinfo/informix-list > <http://www.iiug.org/mailman/listinfo/informix-list>; > > > > > > > > >

On 1 Jan, 01:18, Gustavo Castro <gcast...@bellsouth.net> wrote: > Hi, > > Did you find this information in an official Informix manual?. If this > is the case please let us know the name and page in the manual to open a > documentation defect. Fragment elimination is INDEPENDANT of PDQPRIORITY > settings. The following small testcase proves this. Notice that > PDQPRIORITY is set to zero, the selects obviouly skip the fragments that > do not meet the domain. The text in red shows the elimination of the > correct fragments > Can you please open a different documentation defect?: http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.sqlr.doc/sqlr266.htm "LOW Data values are fetched from fragmented tables in parallel." further down in the "Using PDQPRIORITY with Dynamic Server" section on the same page we have "When you specify LOW, Dynamic Server uses no forms of parallelism.". Which is it "Dynamic Server uses no forms of parallelism." or "Data values are fetched from fragmented tables in parallel."?? http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.sqls.doc/sqls838.htm is clearer - "LOW Data values are fetched from fragmented tables in parallel. (In Dynamic Server, when you specify LOW, the database server uses no other forms of parallelism.) " and "Value 1 is the same as LOW". I would like more documentation on http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.sqlr.doc/sqlr266.htm - "When PDQPRIORITY is set to HIGH, Dynamic Server determines an appropriate value to use for PDQPRIORITY based on several criteria. These include the number of available processors, the fragmentation of tables queried, the complexity of the query, and additional factors." It would be nice to get a clear formula for this!