Re: Finding queries which result in sequential scans

It's also worth bearing in mind that an indexed read can still be reading a large proportion of a table if the choice is a bad one (or if there aren't any indexes that would be good choices). In fact a bad indexed read can often take considerably longer than a sequential scan, especially if the seq scan would have been able to use PDQ, light scans, etc. and/or if the index is on the same disk as the data. Andy malcolm.weallans@btopenworld.com (malcolm) wrote in message news:<3efc1745.0405190026.2d5f5e0e@posting.google.com>... > I found that the best way to tackle this is to find all tables with a > large number of sequential scans (sysptprof linked to systabinfo) and > then discard the squential scans where the table has only a few rows. > That way you can identify the most common tables. > Next find the user doing the most sequential scans (syssesprof, > syssessions). It gives a lead into who and what is causing the > problem. > > And then I guess you could monitor the sql statements for the most > offending users that also referenced the most offending tables. > > Using this technique I have managed to find a number of examples of > sequential access causing poor performance. > > I think the query you are running could encounter many problems with > sysmaster. Your filter means that all rows in syssqexplain will need > to be read every time. In those situations I recommend selecting the > sysmaster table into a temp table and then doing a select from there. > I find it tends to work better that way. > > regards > > Malcolm