Re: Slow query with 7.31UC2
In article <83lnn4$5hu$1@nnrp1.deja.com>, drumzspace <drumzspace@my-deja.com> wrote: > Hi everyone, > > First, the system: > -Running 7.31UC2 on an HPUX 10/20 K410, no KAIO or PDQ > -The application is PeopleSoft HR/Benefits 7.02 (client side SQR) > -Updating statistics nightly w/ Art Kagel's dostats program > -Logical and phys logs are on their own controllers; same w/ root and > mirrored root. > > We had this program that ran in about 30-40 minutes on 7.30UC5. Now in > 7.31UC2 it takes HOURS UPON HOURS. I'm hoping someone here can give me > some constructive advice as to what I should do to tune this query, or > some other advice (i.e. I'm fully prepared for someone to shred my > onconfig! :) > > The two tables involved are ps_pay_deduction (828823 rows) and > ps_pay_check (93956). > [BIG SNIP] Many thanks to Robert Johnson at Informix (not the blues musician) who recommended I drop all distributions. It worked quite well! .ben. -- to reply: drumzspace (AT) yahoo (DOT) com check out my music @ Soul Pagoda: http://www.mp3.com/soulpagoda Sent via Deja.com http://www.deja.com/ Before you buy.
drumzspace wrote: > Many thanks to Robert Johnson at Informix (not the blues musician) who > recommended I drop all distributions. It worked quite well! Can You please show us explain file after dropping distributions? > .ben. Leonid.
I had a similar problem just yesterday with our PeopleSoft database which we were doing only UPDATE STATISICS HIGH per PeopleSoft's recommendation. A query that was running less than a minute on IDS 7.23 was taking almost an hour in 7.30UC2. Some HUGE complex query with 12 main tables in each of two UNIONed SELECTs each of which had four filters depending on subqueries. In the sqexplain file 7.32 estimated a cost of ~3500 and 2 rows returned (which was accurate as to the number of rows) while 7.30 estimated a cost of 8 and 2 rows returned but takes 100 times as long to process. Xtree showed the query stalled on a repeated sort in a lower level join (go try to figure out which joined pair 8-{). Tried everything, finally in frustration I tried dostats and the query was almost instantaneous! Now Drumzspace has had a similar problem with the dostats generated stats and no distributions is the solution. How is one to know how to tune the server and databases for complex apps like PeopleSoft, Baan, SAS, etc.? I'm back to what I used to say to Menlo in the early days of IDS "I know more about this engine than most folk out there and I don't know what the ^%$# I'm doing half the time!". Don't get me wrong, I'm getting the job done, as I did in this case, and I probably would have stumbled into Drumzspace's solution as well, but I'm doing just that - stumbling in the dark looking for my glasses and navigating around the furniture by experience alone. What chance do new users and DBAs have? Just needed to vent. Thanks. Art S. Kagel drumzspace wrote: > > In article <83lnn4$5hu$1@nnrp1.deja.com>, > drumzspace <drumzspace@my-deja.com> wrote: > > Hi everyone, > > > > First, the system: > > -Running 7.31UC2 on an HPUX 10/20 K410, no KAIO or PDQ > > -The application is PeopleSoft HR/Benefits 7.02 (client side SQR) > > -Updating statistics nightly w/ Art Kagel's dostats program > > -Logical and phys logs are on their own controllers; same w/ root and > > mirrored root. > > > > We had this program that ran in about 30-40 minutes on 7.30UC5. Now > in > > 7.31UC2 it takes HOURS UPON HOURS. I'm hoping someone here can give > me > > some constructive advice as to what I should do to tune this query, or > > some other advice (i.e. I'm fully prepared for someone to shred my > > onconfig! :) > > > > The two tables involved are ps_pay_deduction (828823 rows) and > > ps_pay_check (93956). > > > > [BIG SNIP] > > Many thanks to Robert Johnson at Informix (not the blues musician) who > recommended I drop all distributions. It worked quite well! > > .ben. > -- > to reply: drumzspace (AT) yahoo (DOT) com > > check out my music @ Soul Pagoda: http://www.mp3.com/soulpagoda > > Sent via Deja.com http://www.deja.com/ > Before you buy.
In article <385FC9D2.5968225@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >I had a similar problem just yesterday with our PeopleSoft database which we >were doing only UPDATE STATISICS HIGH per PeopleSoft's recommendation. A >query that was running less than a minute on IDS 7.23 was taking almost an >hour in 7.30UC2. Some HUGE complex query with 12 main tables in each of >two UNIONed SELECTs each of which had four filters depending on subqueries. > >In the sqexplain file 7.32 estimated a cost of ~3500 and 2 rows returned >(which was accurate as to the number of rows) while 7.30 estimated a cost of >8 and 2 rows returned but takes 100 times as long to process. Xtree showed >the query stalled on a repeated sort in a lower level join (go try to figure >out which joined pair 8-{). > >Tried everything, finally in frustration I tried dostats and the query was >almost instantaneous! Now Drumzspace has had a similar problem with the dostats ALWAYS works for me. >dostats generated stats and no distributions is the solution. How is one to No distributions sounds dodgy to me. I've never seen dostats fail on a properly indexed table.. Can someone give me an example where it failed (table +index schemas and sqexplain.out example. -- David Williams
In article <385F501A.497B5F19@dati.lv>, Leonids.Voroncovs@dati.lv wrote: > drumzspace wrote: > > > Many thanks to Robert Johnson at Informix (not the blues musician) who > > recommended I drop all distributions. It worked quite well! > > Can You please show us explain file after dropping distributions? Well, here's the newest of the new output. I added two indexes yesterday per Jay Buckler's recommendation and now it runs faster than ever. However, when I tried the new indexes with distributions it was a dog. When I tried the indexes and dropped distributions, it flew. Now, I'm still keeping the rest of my distributions, but now I have one *MORE* thing to try when things go slow. New cost: 3 (1) Index Keys: emplid company paygroup check_dt pay_end_dt off_cycle pagen linen sepchk (Key-Only) Lower Index Filter: (informix.pc.company = '100' AND (informix.pc.paygroup = '01' AND (informix.pc.emplid = '558476318' AND informix.pc.check_dt >= 01/01/1999 ) ) ) Upper Index Filter: informix.pc.check_dt <= 12/17/1999 2) informix.ee: INDEX PATH Filters: informix.ee.ded_class <= 'K' (1) Index Keys: company paygroup pay_end_dt off_cycle pagen linen sepchk dedcd ded_class ded_cur (Key-Only) Lower Index Filter: (informix.ee.dedcd = '253' AND (informix.ee.ded_class = 'B' AND (informix.ee.sepchk = informix.pc.sepchk AND (informix.ee.linen = in formix.pc.linen AND (informix.ee.pagen = informix.pc.pagen AND (informix.ee.off_cycle = informix.pc.off_cycle AND (informix.ee.pay_end_dt = informix.pc.pay_end_ dt AND (informix.ee.paygroup = informix.pc.paygroup AND informix.ee.company = informix.pc.company ) ) ) ) ) ) ) ) NESTED LOOP JOIN Thanks, everyone. Ben Guerard Napa County -- to reply: drumzspace (AT) yahoo (DOT) com check out my music @ Soul Pagoda: http://www.mp3.com/soulpagoda Sent via Deja.com http://www.deja.com/ Before you buy.
David Williams wrote: > > In article <385FC9D2.5968225@bloomberg.net>, Art S. Kagel > <kagel@bloomberg.net> writes [SNIP] > dostats ALWAYS works for me. > > >dostats generated stats and no distributions is the solution. How is one to > No distributions sounds dodgy to me. I've never seen dostats fail on a > properly indexed table.. > > Can someone give me an example where it failed (table +index schemas > and sqexplain.out example. Oh, I have. We have a table (actually a mess of similar tables) with an index headed by a DATETIME indicating the source date of the row and there are other indexes on more important columns. The DATETIME index is needed for report filtering and sorting. Everything works fine most of the time but each monthly load job bogs down if stats are updated HIGH or LOW. The reason is that almost half of the load updates existing rows while the balance adds new rows so we always try the update first (trying the insert first is just too slow as all those duplicate rows have to be rolled back). During the end-of-month load there are initially no rows with the new month and so the optimizer, looking at the distributions decides that there is a HUGE filter value to using the DATETIME index which happens to have fewer levels than the preferred index that is used for the updates at other times when there are already a significant number of rows for the current month. The solution on 7.24 (no optimizer hints mind you) is to manually update the sysindex nlevels to be lower for the preferred index. Now I am finally moving that database to 7.31 next month and we'll just put a hint in and problem solved but the point is the standard stats actually hurt us here. Art S. Kagel