Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2003
    Posts
    8

    Post Unanswered: some weird problem with order by

    Hi folks,

    i have some problems with our server.
    we run a SCO 5.0.5 at 2,2 GhZ Xeon Processor and 512MB-RAM, with an Informix IDS 9.20.UC1.

    If we run some specific sql-statments with an order by statement it simply hang up, with ALL free pages consumed.
    it appear NOT at all statemenst, just some specific ones

    output of onstat -d ist that:

    227# onstat -d

    Informix Dynamic Server 2000 Version 9.21.UC2 -- On-Line -- Up 04:32:32 -- 176128 Kbytes

    Dbspaces
    address number flags fchunk nchunks flags owner name
    18d167d0 1 0x1 1 3 N informix rootdbs
    1 active, 2047 maximum

    Chunks
    address chk/dbs offset size free bpages flags pathname
    18d16918 1 1 0 1000000 148871 PO- /dev/rootdbs
    18d555f0 2 1 0 1000000 3 PO- /dev/rootdbs1
    18d55758 3 1 0 1000000 0 PO- /u/informix/dbspace/rootdbs2

    i hope somebody can help me, cause i dont know where to search

    on our other server,
    the statement works fine there

    thx

    Harlequin

  2. #2
    Join Date
    Jun 2003
    Location
    Hamburg, Germany
    Posts
    38
    Do you see errors... sqlcode 229, ISAM 131 ?

    This looks like a nasty setup. Can you attach the config file?

    How is this server set up compared to the other? Less dbspace available here? A temp dbspace over there?

  3. #3
    Join Date
    Sep 2003
    Posts
    8
    yes
    both errors -229 and -131 occurs in some our programs when they execute some sql sorts, but not regurlary
    sometimes they work fine, sometimes they got down with these errors

    when i run the sql statement in the shel, no error occurs, it hangs only :-(

    the server ist setup identicaly with 2 other servers, where the sqlstatement works.. but there we get sometimes altough -299 and -131
    (same dbspace on them) but on other sqlstatements :-)

    but no extra temp dbtemp at all (we use the rootdbs for temp)

    i attach our onconfig for u
    Attached Files Attached Files

  4. #4
    Join Date
    Jun 2003
    Location
    Hamburg, Germany
    Posts
    38
    The issue is, that you are running out of sort space. Most informix setups will have a dedicated area (a temp dbspace) that is used when informix is working to create indexes, do sorts and joins on SQL queries, etc. If several people are running 'big' queries doing sorts and joins at the same time you'll probably get the error pretty quickly and on several sessions. You may always get a failure on 1 type of query if it has to sort lots of data (for example) and the space available is never going to be enough (because of data volumes) even when no other user is on the system. The frequency depends on data size, how many concurrent users and the types of SQL their sessions execute relative to the temp space available for infomrix to work in.

    I guess what has happened is that your data has now grown to the size where you now will get this problem more frequently. You may never have had it before even, but now that the data has reached that critical size, where temp tables for executing SQL statements exceed the size of available space to work in.

    Simple solution is to extend the space or create a temp dbspace (you'd need to update your config file for that and restart informix).

    Alternatively, you could reduce the size of your database by archiving (then delete) data (if that's appropriate).

  5. #5
    Join Date
    Sep 2003
    Posts
    8
    i just added a temp dbspace with 500mb,, but nothing happens.
    the sql statements dont run :-(

  6. #6
    Join Date
    Jun 2003
    Location
    Hamburg, Germany
    Posts
    38
    Did you update the config file and restart informix?

  7. #7
    Join Date
    Sep 2003
    Posts
    8
    yes i did

    and restartet the server....

    hm... i have tried my old onconfig and now it works?!?!?!?!?!?

    maybe its the optimizer flag in the onconfig?

  8. #8
    Join Date
    Jun 2003
    Location
    Hamburg, Germany
    Posts
    38
    An easy way to see what is happening in the dbspaces is get someone to execute the query when you are logged in as informix user.

    As informix, keep running the onstat -d command.
    This shows the status of the dbspaces.

    If the temp dbspace is being used, then you see it filling up with data and then releasing the space when the query is finished.
    If you see the space is not being used, then there is something wrong with your config file because it should be used for your query.
    If you see it fill up and get the errors, then there is not enought temp dbspace for your query to complete.

  9. #9
    Join Date
    Jun 2003
    Location
    Hamburg, Germany
    Posts
    38
    I just thought of something else, pretty obvious thing really, but ....

    Do you regularly make a statistics update? This can improve your performance (As long as you set them correctly and keep them updated).

    You could also rebuild the indexes before the stats update if your data is pretty old and gets heavy updates,additions,deletions.

  10. #10
    Join Date
    Sep 2003
    Posts
    8
    no we dont

    think im going to introduce here some new database rulez, 'cause the former dba was not realy a good dba

    when i look at the database design.. oh my god
    :-)

  11. #11
    Join Date
    Sep 2003
    Posts
    8
    grr

    ive found the reason

    OPTCOMPIND 0 # To hint the optimizer

    that was it

    if i switch to 2, it works perfectly

    if i witch to 0, it hangs up :-(

    can someone explain me this parameter?

  12. #12
    Join Date
    Jun 2003
    Location
    Hamburg, Germany
    Posts
    38
    OPTCOMPIND is one of the PDQ parameters. Setting it to 2 hints the query optimiser to use various join methods to improve performance when making typically big table joins and sorting output for OLAP-type environments. Setting it to 0 is used when you are tuning for OLTP type installation.
    In a mixed environment I guess it's a toss-up and you just have to play with the setting. I never thought of it I must admit, because 2 is the default value (OLTP queries will not really be slowed down much anyway when you set it to 2, because they are small/quick by nature anyway - so selecting 2 is a good default. Never heard of anyone changing that value ;-) ).

  13. #13
    Join Date
    Sep 2003
    Posts
    8
    hm, thx
    BUT

    if i change it to 0, our programms runs VERY well, if i change it to 2, the programs slows down

    (we have much sequentiel reading)

    hm, i think i do a COMPLETE new config file for our databases, and do a ALL OVER new indexing for our tables....

    much work to do :-(

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •