too slow response for sort operation..

Posted in 2006

Summary

An Oracle user new to Informix reported that a simple SELECT with ORDER BY on a 12,000-row table took over 10 seconds, and asked how to diagnose it. Respondents suggested using SET EXPLAIN ON to inspect the optimizer plan (likely a sequential scan), running UPDATE STATISTICS regularly (scripts such as Art Kagel's dostats in utils2_ak), and indexing the ORDER BY column. Others listed tuning options: PDQPRIORITY, PSORT_NPROCS, DBSPACETEMP/PSORT_DBTEMP temp spaces, DS_TOTAL_MEMORY/DS_MAX_QUERIES, and DS_NONPDQ_QUERY_MEM to raise the default 128K non-PDQ sort memory. The poster never replied, so no confirmed resolution is recorded.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

YOO JAEDO — Thu, 19 Jan 2006 12:59:27 — source: IIUG Forums & Mailing Lists

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......

↪ replying to YOO JAEDO

Marcus Haarmann — Thu, 19 Jan 2006 16:45:11 — source: IIUG Forums & Mailing Lists

Hi, Try the command "set explain on;" before running the statement (from dbaccess for example). It will write a log file in the home dir of the connecting user on the db-server(!) with name sqexplain.out. You can see the path of the query optimizer. In case you created any indexes, and these are not used (sequential scan will be in the explain file), you should know that informix makes use of these sometimes depending on the previous execution of an "update statistics" statement (for the table might be enough). This should be done frequently (we do an update statistics each night, but it is only necessary if there are big moves in amount/structure of records or if a new index is present.) Informix recommends update statistics high on each table with indices for the index columns. We do this each week with a script. (Original source was http://www.weideneder.de/download/admin/updstat.scr, but we modified a little). Hope this helps. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of YOO JAEDO Sent: Thursday, January 19, 2006 1:59 PM To: ids@iiug.org Subject: too slow response for sort operation.. [6228] 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.

↪ replying to YOO JAEDO

ART KAGEL, — Thu, 19 Jan 2006 16:56:33 — source: IIUG Forums & Mailing Lists

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.

↪ replying to Marcus Haarmann

ART KAGEL, — Thu, 19 Jan 2006 16:58:04 — source: IIUG Forums & Mailing Lists

You can also perform the recommended update statistics commands using my dostats utility which is included in the package utils2_ak available for download from the IIUG Software Repository. Art S. Kagel ----- Original Message ----- From: Marcus Haarmann <ids@iiug.org> At: 1/19 11:44 Hi, Try the command "set explain on;" before running the statement (from dbaccess for example). It will write a log file in the home dir of the connecting user on the db-server(!) with name sqexplain.out. You can see the path of the query optimizer. In case you created any indexes, and these are not used (sequential scan will be in the explain file), you should know that informix makes use of these sometimes depending on the previous execution of an "update statistics" statement (for the table might be enough). This should be done frequently (we do an update statistics each night, but it is only necessary if there are big moves in amount/structure of records or if a new index is present.) Informix recommends update statistics high on each table with indices for the index columns. We do this each week with a script. (Original source was http://www.weideneder.de/download/admin/updstat.scr, but we modified a little). Hope this helps. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of YOO JAEDO Sent: Thursday, January 19, 2006 1:59 PM To: ids@iiug.org Subject: too slow response for sort operation.. [6228] 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.

↪ replying to YOO JAEDO

Everett Mills — Thu, 19 Jan 2006 17:03:19 — source: IIUG Forums & Mailing Lists

Yoo (or is it Jaedo?)- Is your query covered by an index? Is your order by column indexed? If so, have you run update statistics? If not, if you plan on running the query frequently you may want to consider creating the index(es). Use "set explain on" to see what the optimizer is doing. It's probably using a table scan. On equivalent hardware the operation on Informix should be similar to Oracle, if not a bit quicker. --EEM > -----Original Message----- > From: YOO JAEDO [mailto:joolist@dreamwiz.com] > Sent: Thursday, January 19, 2006 6:59 AM > To: ids@iiug.org > Subject: too slow response for sort operation.. [6228] > > > 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.

↪ replying to YOO JAEDO

Obnoxio The Clown — Thu, 19 Jan 2006 21:34:38 — source: IIUG Forums & Mailing Lists

YOO JAEDO said: > > 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.. Get a consultant in. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com

↪ replying to YOO JAEDO

David Williams — Fri, 20 Jan 2006 00:03:19 — source: IIUG Forums & Mailing Lists

Before using PDQ and parallel sort options consider the following. If you do not want to use the parallel sort options then by default IDS uses 128K per sort. There is a new parameter in version 10 (and later 9.40 versions) called DS_NONPDQ_QUERY_MEMthat allows you to change this 128K limit for all sessions. ----- Original Message ----- From: "YOO JAEDO" <joolist@dreamwiz.com> To: <ids@iiug.org> Sent: Thursday, January 19, 2006 12:59 PM Subject: too slow response for sort operation.. [6228] > > 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. > > --- > [This E-mail has been scanned for viruses but it is your responsibility > to maintain up to date anti virus software on the device that you are > currently using to read this email. ] > > > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.371 / Virus Database: 267.14.20/234 - Release Date: > 18/01/2006 > > --- [This E-mail has been scanned for viruses but it is your responsibility to maintain up to date anti virus software on the device that you are currently using to read this email. ]