Re: Some guidance on tuning a slow query please

One more thing you may want to consider since the tables you're joining are being updated during busy time is to set your isolation to dirty read if you can get away with that ... On 3/5/07, Floyd Wellershaus <fwellers@yahoo.com> wrote: > > Thanks Art. Good tips. > > A couple of questions. > > > > How can I tell how many LRU pairs and Cleaners I should bump to ? Do the > LRU's use memory ? > > > > Something else about this query, I may have found the culprit, although > it's weird. I opened a case on it. > > Every time it runs I notice that it's totally recreating the view. It can > be seen in the sqexplain.out. Well that's a big view to have to create. > Even though the query that calls the view is only selecting a few hundred > rows, it causes the underlying table of the view to be scanned. > > I can't say I understand why but: > > 1) If I select the seqscans on that table from sysptprof, it's stable, > then I run the query, and the number of scans bumps by one. So I know it's > doing it. Plus, as I said, I can see the view being created in the > sqexplain. > > > > 2) I did some troubleshooting on the view, it has like 13 joins 5 or so of > them are outer joins. If I take all the outer joins away and make them > regular joins, it doesn't scan the table or create the view. If I add even > one outer join , then call that view in a query where it has to join the > view to another table, then BOOM. It does it. > > > > So why does an outer join in the view create statement cause this behavior > > > and > > for my own understanding, why does calling the view, cause a full table > scan on the main underlying table ? > > > > Thanks again, and in advance for any advice. > > > > Floyd > > > > > > ----- Original Message ---- > From: Art S. Kagel <kagel@bloomberg.net> > To: informix-list@iiug.org > Sent: Monday, March 5, 2007 6:13:04 PM > Subject: Re: Some guidance on tuning a slow query please > > Floyd Wellershaus wrote: > > We have a query that takes a long time when the system get's busy. It > > used to take about 20-30 seconds all the time. I added buffers and now > > it only takes about 3-4 seconds, until we get busy, then it goes up to > > 30 seconds again. > > Based on the symptom, I'd say you can up the buffer cache again. Looks > like > you have enough cache to not thrash when the system is quiet, but when > other > processes are running your big query is losing it's data pages and having > to > reload them again and again. Watch onstat -P over time (say every 5-10 > seconds) during peak load when this query is running and look for a small > number of partnums that are trading #of buffers up and down and increase > BUFFERS by at least the size of that constant trade. > > > The query is ugly, and makes use of a view that is very ugly. The view > > joins 11 tables, the biggest of which is about 800,000 rows, and is > > forced to be scanned, due to the nature of the view. I don't see putting > > > an index on it, since we need all the rows. That is the recipient table. > > > > From the stats, I was thinking of maybe fragmenting the recipient table > > > even though it has less than a million rows in it. But I don't know if I > > > would get the parallel scanning unless we use pdq ( we are on version > > IDS10.0.FC5 ). > > Might help with PDQ on, but don't count on miraculous improvements in this > > case, especially with only one CPU VP running. > > > Also, our san is raided ( 1+0 ) and plaided, so I really don't know > > where the data is, as it's scattered all over the disk. I really think > > this has to do with buffer waits. How do I decrease those ? > > > > Yes bufwaits is high. A BR of 25.7 is death. Max out LRUs and make > CLEANERS match. BTR looks OK, but that's because when this poor query > is not running you have plenty of buffers. > > > > > At this point, I would rather check out if there is anything to do to > > fix this other than having to rewrite the query and the view. It seems > > there may be some tuning on the engine and some data management that > > could solve this problem. But I would appreciate any advice. > > > > > Art S. Kagel > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- The biggest oxymoron: common sense