Re: too slow response for sort operation..- PSORT_

Do you need to set PSORT_DBTEMP when using PSORT_NPROCS if you have DBSPACETEMP set with plenty of dbspaces? I've never been able to find anything that would completely clarify the correlation between these parameters. I have DBSPACETEMP defined in my $ONCONFIG as well as PSORT_DBTEMP and PSORT_NPROCS in my environment. If I unset PSORT_DBTEMP will it actually improve my sorts since I have way more space(s) allocated to DBSPACETEMP or does the PSORT_NPROC only work well with PSORT_DBTEMP? --- "ART KAGEL, ...." <KAGEL@bloomberg.net> wrote: > > Things affecting ORDER BY performance: > > > Statistics on the table not up-to-date so that the optimizer is not > using an > index when available and it would improve performance. > > > Environment variable PDQPRIORITY=0 disables parallel data > acquisition and > sorting capabilities. Should be at least 1, but higher values from > 2-100 > allocate additional resources to your query. > > > Environment variable PSORT_NPROCS<2 disables parallel sorting. > Should be set > to the number of threads to use for sorting and should not exceed 2 x > > NUMCPUVPS> (or the # VPs set in the VPCLASS cpu) ONCONFIG file parameter. > > > Environment variable DBSPACETEMP not set or set to a single temp > dbspace or > to > only include non-temp dbspaces. Your instance should be configured > with 3 or > more dbspaces configured as 'temp' dbspaces and these along with one > or more > 'normal' dbspaces (to be used for logged temp tables) should be > listed in the > DBSPACETEMP environment variable for the IDS instance or the user's > session. > > > If you do not have temp dbspaces or your filesystems are very fast > with lots > of cache, you can try setting the environment variable PSORT_DBTEMP > to a list > of > at least 3 (up to 6) filesystems with enough free space to hold > sort-work > files, set PSORT_NPROCS and PDQPRIORITY as above. > > > Increase the amount of memory allocated to parallel queries and > sorts to > avoid > spooling sort-work files to disk at all by modifying the ONCONFIG > parameter > DS_TOTAL_MEMORY. You may also have to adjust DS_MAX_QUERIES and > DS_MAX_SCANS> to permit multiple DSS style queries to run in parallel - otherwise > they will > run one at a time. > > Art S. Kagel > > ----- Original Message ----- > From: Yoo Jaedo <ids@iiug.org> > At: 1/19 11:29 > Hi.. > > Actually I started using the Informix Database from a few days ago. > Until now I'v just only used oracle db. > Unfortunagely I found a little bit serious problem. Informix DB > response too > slowly to the request of sort operation. It took more than 10 secs > when I > include order by cluase for the table in which there just only 12000 > records. > > And I don't know what is problem. Is there any good tool or method to > make me > check what's worng? Any comments on this problem is appreciated.. > Thanks in advance...... > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > <div id="RTEContent">DL Redden <br> <br>Visit my wifes web site <a href="http://www.lishalynn.com">www.lishalynn.com</a>; for all of your makeup and skin care needs.</div>

IF PSORT_NPROCS>1 and PDQPRIORITY>0 then IFF PSORT_DBTEMP is set those filesystems will be used for temporary sort-work files for ORDER BY, index builds, GROUP BY, UNIQUE, and UPDATE STATISTICS if needed. IF PSORT_DBTEMP is unset or is set to the empty string then the protocols for using dbspaces listed in DBSPACETEMP is enabled instead. Any temp dbspaces listed will be used and if all the listed dbspaces are 'normal' then IB rootdbs is used (no time to look it up again now, it's in the Administrator's Reference somewhere). Art S. Kagel ----- Original Message ----- From: Dl Redden <ids@iiug.org> At: 1/19 12:37 Do you need to set PSORT_DBTEMP when using PSORT_NPROCS if you have DBSPACETEMP set with plenty of dbspaces? I've never been able to find anything that would completely clarify the correlation between these parameters. I have DBSPACETEMP defined in my $ONCONFIG as well as PSORT_DBTEMP and PSORT_NPROCS in my environment. If I unset PSORT_DBTEMP will it actually improve my sorts since I have way more space(s) allocated to DBSPACETEMP or does the PSORT_NPROC only work well with PSORT_DBTEMP? --- "ART KAGEL, ...." <KAGEL@bloomberg.net> wrote: > > Things affecting ORDER BY performance: > > > Statistics on the table not up-to-date so that the optimizer is not > using an > index when available and it would improve performance. > > > Environment variable PDQPRIORITY=0 disables parallel data > acquisition and > sorting capabilities. Should be at least 1, but higher values from > 2-100 > allocate additional resources to your query. > > > Environment variable PSORT_NPROCS<2 disables parallel sorting. > Should be set > to the number of threads to use for sorting and should not exceed 2 x > > NUMCPUVPS> (or the # VPs set in the VPCLASS cpu) ONCONFIG file parameter. > > > Environment variable DBSPACETEMP not set or set to a single temp > dbspace or > to > only include non-temp dbspaces. Your instance should be configured > with 3 or > more dbspaces configured as 'temp' dbspaces and these along with one > or more > 'normal' dbspaces (to be used for logged temp tables) should be > listed in the > DBSPACETEMP environment variable for the IDS instance or the user's > session. > > > If you do not have temp dbspaces or your filesystems are very fast > with lots > of cache, you can try setting the environment variable PSORT_DBTEMP > to a list > of > at least 3 (up to 6) filesystems with enough free space to hold > sort-work > files, set PSORT_NPROCS and PDQPRIORITY as above. > > > Increase the amount of memory allocated to parallel queries and > sorts to > avoid > spooling sort-work files to disk at all by modifying the ONCONFIG > parameter > DS_TOTAL_MEMORY. You may also have to adjust DS_MAX_QUERIES and > DS_MAX_SCANS> to permit multiple DSS style queries to run in parallel - otherwise > they will > run one at a time. > > Art S. Kagel > > ----- Original Message ----- > From: Yoo Jaedo <ids@iiug.org> > At: 1/19 11:29 > Hi.. > > Actually I started using the Informix Database from a few days ago. > Until now I'v just only used oracle db. > Unfortunagely I found a little bit serious problem. Informix DB > response too > slowly to the request of sort operation. It took more than 10 secs > when I > include order by cluase for the table in which there just only 12000 > records. > > And I don't know what is problem. Is there any good tool or method to > make me > check what's worng? Any comments on this problem is appreciated.. > Thanks in advance...... > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > <div id="RTEContent">DL Redden <br> <br>Visit my wifes web site <a href="http://www.lishalynn.com">www.lishalynn.com</a>; for all of your makeup and skin care needs.</div> ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.