Finding queries which result in sequential scans

Hey all. I have a lot of seqscans and I'd like to try to find out whether there are specific queries being run in our app that could either be restructured to take advantage of existing indexes or could use new indexes. The engine is good at reporting overall seqscan stats (how many occurred) but less helpful in finding which queries are causing the seqscans. I've tried writing a query that grabs the currently-executing sequential scans out of syssqexplain: select '====================================' separator, current current_time, sqx_sessionid, sqx_sqlstatement, sqx_seqscan, sqx_estcost from syssqexplain where sqx_seqscan = 1 and sqx_estcost > 1 and sqx_sqlstatement not like "%syssqexplain%" order by sqx_estcost desc ...but running this once per minute has an unfortunate tendency to eventually crash the engine... It's also needlessly redundant in that it reports the same query multiple times if the query endures longer than a minute. I was thinking it would be a better idea to write a trigger on syssqexplain that would write the query info to another table - that way it's only getting written once, and the overall process is a lot lighter-weight. I'm a bit reluctant to add a trigger to a system table, though, for obvious reasons. Can anybody suggest a good, reliable, low-impact way to log the queries that result in sequential scans? If putting a trigger on syssqexplain sounds good, could somebody with a closer understanding of the internals suggest a safe way to do so? Would it be an insert trigger? Or an update trigger (if the query gets inserted and then later marked as sequential)? Thanks. -- John Hardin KA7OHZ <johnh@aproposretail.com> Internal Systems Administrator voice: (425) 672-1304 Apropos Retail Management Systems, Inc. fax: (425) 672-0192 ----------------------------------------------------------------------- ...the Fates notice those who buy chainsaws... -- www.darwinawards.com

"John Hardin" <johnh@aproposretail.com> wrote > ...but running this once per minute has an unfortunate tendency to > eventually crash the engine... what !!! I use 9.21 which is far less stable than later versions and I have used this query every 5 seconds. The engine never crashed. How can you be so certain that this query crashes the engine. > I was thinking it would be a better idea to write a trigger on > syssqexplain that would write the query info to another table - that way > it's only getting written once, and the overall process is a lot > lighter-weight. sysmaster does not support triggers. It is not a real database. It is a pseudo database only.

John Hardin wrote: > Hey all. > > I have a lot of seqscans and I'd like to try to find out whether there are > specific queries being run in our app that could either be restructured to > take advantage of existing indexes or could use new indexes. > > The engine is good at reporting overall seqscan stats (how many occurred) > but less helpful in finding which queries are causing the seqscans. > > I've tried writing a query that grabs the currently-executing sequential > scans out of syssqexplain: > > > select > '====================================' separator, current > current_time, > sqx_sessionid, > sqx_sqlstatement, > sqx_seqscan, > sqx_estcost > from > syssqexplain > where > sqx_seqscan = 1 > and sqx_estcost > 1 > and sqx_sqlstatement not like "%syssqexplain%" order by > sqx_estcost desc > > > ...but running this once per minute has an unfortunate tendency to > eventually crash the engine... > > It's also needlessly redundant in that it reports the same query multiple > times if the query endures longer than a minute. > > I was thinking it would be a better idea to write a trigger on > syssqexplain that would write the query info to another table - that way > it's only getting written once, and the overall process is a lot > lighter-weight. > > I'm a bit reluctant to add a trigger to a system table, though, for > obvious reasons. > > Can anybody suggest a good, reliable, low-impact way to log the queries > that result in sequential scans? > > If putting a trigger on syssqexplain sounds good, could somebody with a > closer understanding of the internals suggest a safe way to do so? Would > it be an insert trigger? Or an update trigger (if the query gets inserted > and then later marked as sequential)? > > Thanks. > As you already been told, you can't have triggers on sysmaster. You can also take another path, but the success depends on your environment. You can count the sequential scans for each table (onstat -g ppf I think). Then create an history. If your environment is stable enough and you have access to the code you can try to match the tables with the queries. This has obvious disadvantages, but also has a few hidden advantages. In my environment most of the sequential scans are made on very small or even temporary tables. Ocasionally we hit sequential scans on big and permanent tables, but it's not usual. And my experience tells me that a wrong index can be worst then a sequential scan. For start, it's much harder to track. Regards,

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 "John Hardin" <johnh@aproposretail.com> wrote in message news:<pan.2004.05.18.16.10.03.389488@aproposretail.com>... > Hey all. > > I have a lot of seqscans and I'd like to try to find out whether there are > specific queries being run in our app that could either be restructured to > take advantage of existing indexes or could use new indexes. > > The engine is good at reporting overall seqscan stats (how many occurred) > but less helpful in finding which queries are causing the seqscans. > > I've tried writing a query that grabs the currently-executing sequential > scans out of syssqexplain: > > > select > '====================================' separator, current > current_time, > sqx_sessionid, > sqx_sqlstatement, > sqx_seqscan, > sqx_estcost > from > syssqexplain > where > sqx_seqscan = 1 > and sqx_estcost > 1 > and sqx_sqlstatement not like "%syssqexplain%" order by > sqx_estcost desc > > > ...but running this once per minute has an unfortunate tendency to > eventually crash the engine... > > It's also needlessly redundant in that it reports the same query multiple > times if the query endures longer than a minute. > > I was thinking it would be a better idea to write a trigger on > syssqexplain that would write the query info to another table - that way > it's only getting written once, and the overall process is a lot > lighter-weight. > > I'm a bit reluctant to add a trigger to a system table, though, for > obvious reasons. > > Can anybody suggest a good, reliable, low-impact way to log the queries > that result in sequential scans? > > If putting a trigger on syssqexplain sounds good, could somebody with a > closer understanding of the internals suggest a safe way to do so? Would > it be an insert trigger? Or an update trigger (if the query gets inserted > and then later marked as sequential)? > > Thanks.