SEQSCANS Counter in sysptprof

Hello IIUG Community We are running a hugh server with aprox 8TB attached Databases. In sum everything works very well and also the overall Performance is good. As we want to implent compression we have identified Tables with a high I/O impact. While searching for this, we have found some (in our Eyes) strange things. Which I would like to discuss here: Here is a sysptprof Entry of a specific Table. This Table has in sum 6 GB (800.000 Pages) data. A onstat –z running every day at midnight to reset the stats: dbsname yyyyyy tabname xxxxxx partnum 18874577 lockreqs 2642 lockwts 0 deadlks 0 lktouts 0 isreads 2272 iswrites 912 isrewrites 0 isdeletes 0 bufreads 12784 bufwrites 1101 seqscans 749 pagreads 3973 pagwrites 209 As you see there are listed 749 seq scans for this Table. The number increases a longer a day is. Now it is early in the morning, and the working Day has just begun. It is not unusual to see here 6.000 or anything like that at the End of the Working Day. As a squential Scan of a non indexed Field took nearly 10 Minutes, it is nearly immpossible that only „real sequential scans“ are increases this counter, because of it would take about 41 Days to do this full 6.000 seqscans. ;) We have also traced the Traffic, but never found a „real sequential Scan“ :( Did someone know all actions that increase the seqscan counter in sysptprof? Thank You for your Time, and your Answers. Gr33tZ From Germany Andy

They just have to be started... not completed. SELECT FIRST 1 * FROM table; will do a sequential scan, and will count as one. Even the app can decide that it doesn't want to fetch more data and close the cursor. Regards. On Fri, Feb 23, 2018 at 9:03 AM, ANDY W. <aweis@arz-emmendingen.de> wrote: > Hello IIUG Community > > We are running a hugh server with aprox 8TB attached Databases. In sum > everything works very well and also the overall Performance is good. > > As we want to implent compression we have identified Tables with a high I/O > impact. While searching for this, we have found some (in our Eyes) strange > things. Which I would like to discuss here: > > Here is a sysptprof Entry of a specific Table. This Table has in sum 6 GB > (800.000 Pages) data. A onstat –z running every day at midnight to reset > the > stats: > > dbsname yyyyyy > tabname xxxxxx > partnum 18874577 > lockreqs 2642 > lockwts 0 > deadlks 0 > lktouts 0 > isreads 2272 > iswrites 912 > isrewrites 0 > isdeletes 0 > bufreads 12784 > bufwrites 1101 > seqscans 749 > pagreads 3973 > pagwrites 209 > > As you see there are listed 749 seq scans for this Table. The number > increases > a longer a day is. Now it is early in the morning, and the working Day has > just begun. It is not unusual to see here 6.000 or anything like that at > the > End of the Working Day. As a squential Scan of a non indexed Field took > nearly > 10 Minutes, it is nearly immpossible that only „real sequential scans“ are > increases this counter, because of it would take about 41 Days to do this > full > 6.000 seqscans. ;) We have also traced the Traffic, but never found a „real > sequential Scan“ :( Did someone know all actions that increase the seqscan > counter in sysptprof? > > Thank You for your Time, and your Answers. > > Gr33tZ From Germany > Andy > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Hello Fernando We just thought the same, but could not find any hints if it is right. Thank you for your quick Responce. Gr33tZ from Germany Andy

To find your query turn on sqltrace in low mode. You can then search for the part number of the table in the query plans displayed by 'onstat -g his'. Bear in mind that 'onstat -g ppf' reports part numbers in hex and 'onstat -g his' uses decimal. Ben.

You may also be running into this problem: IT02564: INDEX SKIP SCAN IS COUNTED AS SEQSCAN IN PARTITION PROFILE http://www-01.ibm.com/support/docview.wss?uid=swg1IT02564 This has sent me on chasing for a large number of table scans before, which are actually index skip scans. Fixed in 12.10.xC5. Mike -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ANDY W. Sent: Friday, February 23, 2018 2:59 AM To: ids@iiug.org Subject: Re: SEQSCANS Counter in sysptprof [40753] Hello Fernando We just thought the same, but could not find any hints if it is right. Thank you for your quick Responce. Gr33tZ from Germany Andy **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Hy Mike Thank you for your Responce. This is a really good hint. ATM we are at XC4, but we will update to XC10 within the next few weeks, so we will see soon if the seq scans will be reduced right after the Update.