Re: 7.10.UD1 Optimizer Inconsistencies

> Perhaps someone can shed some light on the way the 7.10 > optimizer is working (or not working). > > I ran a benchmark query with the following unexpected results. > BTW, it was run on an empty SC2000 with 512MB ram, and OnLine > was initialized before each run. > > 1). Ran the query with indexes attached to the table: 22 minutes. > > 2). Ran the query with indexes separated from the data on > different devices: 10 minutes. This better performance > was expected, but it was achieved ONLY after updating > statistics high for each leading column in the index and > updating statisitics medium for each non-leading column > in the index. OPTCOMPIND was set to 0 and set explain > reported a cost over 3,000,000. When I ran this prior > to updating statisitics high and medium, only low statistics > were used and the query never finished. I killed it when > I saw it took longer than the 22 minutes above. > > --- so far so good --- > > 3). Ran the query again just like in #2 above, except I > set OPTCOMPIND to 2 (the new default in UD1). > The reported cost was only around 200,000 > but the query was performed using hash joins and it actually > took 20% longer. > > I obviously have reverted to using OPTCOMPIND of 0, even though > the cost is higher, the performance is better. Doesn't make any > sense however. Any optimizer experts out there? > ================================================ > Michael Reed, Radiix, Inc. > 3588 Plymouth Rd. Suite 257 > Ann Arbor, MI 48105 > voice: 313-677-UNIX email: mreed@radiix.com I'm far from an optimizer expert, but maybe I can shed a little light. One of the factors that influences how the optimizer chooses to read a table is the isolation level. Dirty Read is generally the fastest if the "questionable" results are acceptable. If your isolation level is NOT set to Repeatable Read then the optimizer chooses the *least expensive* of several options, which may not be the fastest. In your case the optimizer chose the hash join, rather than the nested-loop join. It makes sense that with your indexes on a separate disk it would be faster I/O-wise to use them directly than to create a hash index. Have you tried the SET OPTIMIZATION LOW option? If you are joining more than four or five tables the optimizer can spend more time than it's worth examining all the possible scan methods. The only way I know to check this is to try it both ways and compare the SET EXPLAIN ON output. You may also account for the skewness of your data by using a specific RESOLUTION for the statistics, and by altering the CONFIDENCE of the statistics. You may have a data set that just happens to cause the optimizer to choose the worst path. It is possible to influence the optimizer by altering the query. You can include more "where" clauses or alter the order of the tables. Lastly, do the disks holding the index and the data run on the same controller? Perhaps you ran into a bottleneck when the optimizer read the indexes to build the hash index, then read the tables to create the join, then wrote a temp table for a sort (?), then read the temp table for the result. This is just a guess. I got most of this directly from the "Informix-OnLine Dynamic Server Performance Guide", version 7.1. Dated December 1994, the part number is 000-7708. Some of it came from the Informix "Managing Large Databases" class and book. The rest I learned in Elizabeth Suto's wonderful book "Informix-OnLine Performance Tuning", ISBN 0-13-124322-5. Good luck, __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | |Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | |________________________________________________________________|