RE: seq. scans

I use the following script to identify tables with a large number of sequential scans. It requires that "TBLSPACE_STATS 1" be specified in your $ONCONFIG file. There is also a known Informix product defect (108509) which states: SEQSCANS IN SYSPTPROF INCREASED BY 1 EVENTHOUGH QUERY PLAN SHOWED INDEX PATH HAS BEEN USED, NOTHING IN SEQUENTIAL TechInfo Center does not show this defect as being fixed in any release. #!/usr/bin/ksh print "\\n\\n\\tTables with the most seqscans per sysmaster:systprof" $INFORMIXDIR/bin/dbaccess sysmaster - <<-EOF! 2>/dev/null SELECT FIRST 20 a.tabname[1,5] AS TABLE, b.npused, a.bufreads, a.bufwrites, a.seqscans, a.pagreads, a.pagwrites FROM sysptprof a, sysptnhdr b WHERE a.partnum = b.partnum AND a.seqscans > 5000 ORDER BY a.seqscans desc ; EOF! -----Original Message----- From: Colleen M. Morrow [mailto:Colleen_M._Morrow@jonesday.com] Sent: Monday, May 01, 2000 13:11 To: informix-list@iiug.org Subject: seq. scans _______________________________________________________________ This message and any attachments are intended for the individual or entity named above. If you are not the intended recipient, please do not read, copy, use or disclose this communication to others; also please notify the sender by replying to this message, and then delete it from your system. Thank you. _______________________________________________________________ In the onstat -p output, I'm seeing a lot of sequential scans. Is there any way to find out what table these are occuring on. Or possibly the process doing it? TIA.

In article <8ekqcv$pbp$1@news.xmission.com>, Bernstein, Rick <rbernste@alarismed.com> writes > >I use the following script to identify tables with a large number of >sequential scans. It requires that "TBLSPACE_STATS 1" be specified >in your $ONCONFIG file. >There is also a known Informix product defect (108509) which states: > >SEQSCANS IN SYSPTPROF INCREASED BY 1 EVENTHOUGH QUERY PLAN >SHOWED INDEX PATH HAS BEEN USED, NOTHING IN SEQUENTIAL > >TechInfo Center does not show this defect as being fixed in any release. > True, but sometimes the same problem is logged more than once and so it may be fixed under another bug number... 73BETA: SEQSCANS FROM SYSPTPROF IN SYSMASTER IS INCREMENTED WHEN A KEYFIRST INDEX SCAN IS DONE This is fixed in 7.30.UC9!! I need to get testing..I have UC10 on Linux. > >#!/usr/bin/ksh > > print "\\n\\n\\tTables with the most seqscans per sysmaster:systprof" > $INFORMIXDIR/bin/dbaccess sysmaster - <<-EOF! 2>/dev/null > SELECT FIRST 20 > a.tabname[1,5] AS TABLE, > b.npused, > a.bufreads, > a.bufwrites, > a.seqscans, > a.pagreads, > a.pagwrites > FROM sysptprof a, sysptnhdr b > WHERE a.partnum = b.partnum > AND a.seqscans > 5000 > ORDER BY a.seqscans desc > ;> EOF! > > > >-----Original Message----- >From: Colleen M. Morrow [mailto:Colleen_M._Morrow@jonesday.com] >Sent: Monday, May 01, 2000 13:11 >To: informix-list@iiug.org >Subject: seq. scans > > > > > > >_______________________________________________________________ > >This message and any attachments are intended for the individual or entity >named above. If you are not the intended recipient, please do not read, >copy, use or disclose this communication to others; also please notify the >sender by replying to this message, and then delete it from your system. >Thank you. >_______________________________________________________________ > >In the onstat -p output, I'm seeing a lot of sequential scans. Is there >any way to find out what table these are occuring on. Or possibly the >process doing it? TIA. > -- David Williams

I did have 7.30.FC7 and the bug was present. I now have 7.30.FC10 and that bug has been resolved. Clifton Bean "David Williams" <djw@smooth1.demon.co.uk> wrote in message news:YIfoNDAM9gD5Ewnd@smooth1.demon.co.uk... > In article <8ekqcv$pbp$1@news.xmission.com>, Bernstein, Rick > <rbernste@alarismed.com> writes > > > >I use the following script to identify tables with a large number of > >sequential scans. It requires that "TBLSPACE_STATS 1" be specified > >in your $ONCONFIG file. > >There is also a known Informix product defect (108509) which states: > > > >SEQSCANS IN SYSPTPROF INCREASED BY 1 EVENTHOUGH QUERY PLAN > >SHOWED INDEX PATH HAS BEEN USED, NOTHING IN SEQUENTIAL > > > >TechInfo Center does not show this defect as being fixed in any release. > > > > True, but sometimes the same problem is logged more than once and > so it may be fixed under another bug number... > > > 73BETA: SEQSCANS FROM SYSPTPROF IN SYSMASTER IS INCREMENTED WHEN A > KEYFIRST INDEX SCAN IS DONE > > This is fixed in 7.30.UC9!! I need to get testing..I have UC10 on > Linux. > > > > > >#!/usr/bin/ksh > > > > print "\\n\\n\\tTables with the most seqscans per sysmaster:systprof" > > $INFORMIXDIR/bin/dbaccess sysmaster - <<-EOF! 2>/dev/null > > SELECT FIRST 20 > > a.tabname[1,5] AS TABLE, > > b.npused, > > a.bufreads, > > a.bufwrites, > > a.seqscans, > > a.pagreads, > > a.pagwrites > > FROM sysptprof a, sysptnhdr b > > WHERE a.partnum = b.partnum > > AND a.seqscans > 5000 > > ORDER BY a.seqscans desc > > ;> > EOF! > > > > > > > >-----Original Message----- > >From: Colleen M. Morrow [mailto:Colleen_M._Morrow@jonesday.com] > >Sent: Monday, May 01, 2000 13:11 > >To: informix-list@iiug.org > >Subject: seq. scans > > > > > > > > > > > > > >_______________________________________________________________ > > > >This message and any attachments are intended for the individual or entity > >named above. If you are not the intended recipient, please do not read, > >copy, use or disclose this communication to others; also please notify the > >sender by replying to this message, and then delete it from your system. > >Thank you. > >_______________________________________________________________ > > > >In the onstat -p output, I'm seeing a lot of sequential scans. Is there > >any way to find out what table these are occuring on. Or possibly the > >process doing it? TIA. > > > > -- > David Williams

Good day Experts, Is increasing of BUFFERS and building indexes only way to descrease seqscans? David Williams wrote: > In article <8ekqcv$pbp$1@news.xmission.com>, Bernstein, Rick > <rbernste@alarismed.com> writes > > > >I use the following script to identify tables with a large number of > >sequential scans. It requires that "TBLSPACE_STATS 1" be specified > >in your $ONCONFIG file. > >There is also a known Informix product defect (108509) which states: > > > >SEQSCANS IN SYSPTPROF INCREASED BY 1 EVENTHOUGH QUERY PLAN > >SHOWED INDEX PATH HAS BEEN USED, NOTHING IN SEQUENTIAL > > > >TechInfo Center does not show this defect as being fixed in any release. > > > > True, but sometimes the same problem is logged more than once and > so it may be fixed under another bug number... > > 73BETA: SEQSCANS FROM SYSPTPROF IN SYSMASTER IS INCREMENTED WHEN A > KEYFIRST INDEX SCAN IS DONE > > This is fixed in 7.30.UC9!! I need to get testing..I have UC10 on > Linux. > > > > >#!/usr/bin/ksh > > > > print "\\n\\n\\tTables with the most seqscans per sysmaster:systprof" > > $INFORMIXDIR/bin/dbaccess sysmaster - <<-EOF! 2>/dev/null > > SELECT FIRST 20 > > a.tabname[1,5] AS TABLE, > > b.npused, > > a.bufreads, > > a.bufwrites, > > a.seqscans, > > a.pagreads, > > a.pagwrites > > FROM sysptprof a, sysptnhdr b > > WHERE a.partnum = b.partnum > > AND a.seqscans > 5000 > > ORDER BY a.seqscans desc > > ;> > EOF! > > > > > > > >-----Original Message----- > >From: Colleen M. Morrow [mailto:Colleen_M._Morrow@jonesday.com] > >Sent: Monday, May 01, 2000 13:11 > >To: informix-list@iiug.org > >Subject: seq. scans > > > > > > > > > > > > > >_______________________________________________________________ > > > >This message and any attachments are intended for the individual or entity > >named above. If you are not the intended recipient, please do not read, > >copy, use or disclose this communication to others; also please notify the > >sender by replying to this message, and then delete it from your system. > >Thank you. > >_______________________________________________________________ > > > >In the onstat -p output, I'm seeing a lot of sequential scans. Is there > >any way to find out what table these are occuring on. Or possibly the > >process doing it? TIA. > > > > -- > David Williams

Marat wrote: > > Good day Experts, > > Is increasing of BUFFERS and building indexes only way to descrease seqscans? [Related question SNIPPED] Umm, building missing indexes and updating statistics to at least the levels recommended in the Performance Guide and implemented in my dostats utility and others available from the IIUG Software Repository. Increasing BUFFERS will not affect sequential scans beyond making them run faster. Art S. Kagel

I thank you all for your advices & explanations. "Art S. Kagel" wrote: > Marat wrote: > > > > Good day Experts, > > > > Is increasing of BUFFERS and building indexes only way to descrease seqscans? > [Related question SNIPPED] > > Umm, building missing indexes and updating statistics to at least the > levels recommended in the Performance Guide and implemented in my dostats > utility and others available from the IIUG Software Repository. Increasing > BUFFERS will not affect sequential scans beyond making them run faster. > > Art S. Kagel