Sequential Scan vs Index Read

The facts. OS = AIX 4.3.3 Machine = RS 6000 F80 (64 bit) IDS = 7.31.FD1 We have a table that is being sequentially scanned over 100 times an hour during our peak hours. This table has flucuated between 20,000 and 40,000 rows over the course of this month. This table already has multiple indices (5). The sequential scans must be slowing down the users, but the users are not complaining, because I think it has always been this way for them . The issue is that we have over 250 seperate 4GL programs that query this table somewhere in the code. The code is from a third party supplier that is not moving very fast to help us solve this issue. Are there any Informix tools available to us to see which queries are reading the table through an index, and which ones are sequentially scanning the table? To run set explain for all 250+ 4GL programs would be too time consuming and cumbersome. Any help would be appreciated, thank you. Roy Verstegen WSI - Informix DBA

Roy, 40,000 rows isn't all that many. The real issue, at least from where I sit is how many pages the table consumes. If the rows are relatively small, you've probably got a lot of them on a single page. Your page size is 4096; there are 26 bytes of overhead per page (thank you Mark Scranton for the internals class), so if your row size were 50 bytes, you'd get 80 of them on a page. Given 40,000 rows, that would be about 500 pages for the data. At only 500 pages, I'm not sure that the optimizer is going to prefer indexes over the sequential scan, and if the table is hit often, hopefully it's in the buffer cache, so you won't be reading disk anyway. Do you know if the queries return single rows, or generally more than one row? If they return a single row, I'd agree that the index is probably the right way to go; if they return more than one, I'm guessing that you're actually faster doing the sequential scan. Let me know what you think; I'm curious what others think about this. Dan Michaelis 813.978.6534 (office) 813.303.3225 (pager) dan.michaelis@verizon.com

Good point about the row size, rows returned, and hits on the table. I should have included it in my question. Row size is 275, which puts 14 rows per page, so when the table has 40,000 rows that comes out to over 2850 pages. Based on what this table is used for in our company, I would say the queries do not return many rows (1 to 10 rows). Average hits per hour on this table is 17,000 reads/hour. -----Original Message----- From: dan.michaelis@verizon.com [mailto:dan.michaelis@verizon.com] Sent: Wednesday, February 12, 2003 3:07 PM To: Roy Verstegen Cc: forum.subscriber@iiug.org; ids@iiug.org Subject: Re: Sequential Scan vs Index Read [354] Roy, 40,000 rows isn't all that many. The real issue, at least from where I sit is how many pages the table consumes. If the rows are relatively small, you've probably got a lot of them on a single page. Your page size is 4096; there are 26 bytes of overhead per page (thank you Mark Scranton for the internals class), so if your row size were 50 bytes, you'd get 80 of them on a page. Given 40,000 rows, that would be about 500 pages for the data. At only 500 pages, I'm not sure that the optimizer is going to prefer indexes over the sequential scan, and if the table is hit often, hopefully it's in the buffer cache, so you won't be reading disk anyway. Do you know if the queries return single rows, or generally more than one row? If they return a single row, I'd agree that the index is probably the right way to go; if they return more than one, I'm guessing that you're actually faster doing the sequential scan. Let me know what you think; I'm curious what others think about this. Dan Michaelis 813.978.6534 (office) 813.303.3225 (pager) dan.michaelis@verizon.com

Roy, Hmmm... Now you've got me... <laugh> That might still fit in cache, though it seems like it's large enough to be cleaned out, unless it really is the only thing being hit. That frequency, it might be there anyway. I don't know of any good ways to see if a table is being sequentially scanned vs. index read without doing as you suggested; using set explain. I don't know if ispy might help, but there's a noticeable bit of overhead from what I understand in using that. You might only want to do that in a test environment, and get the results for analysis on the prod environment. Given your kind of traffic, I'd be concerned that you'd slow yourself down. The only thing that I might do is look at total "real" I/O on the system. If you find that most of your reads are buffer reads, and few are actual page reads, then it probably doesn't matter much anyway; if you're always reading memory things are going to be pretty fast no matter what. Let me know what you've decided to do... I'm always interested in creative solutions to problems! Dan Michaelis 813.978.6534 (office) 813.303.3225 (pager) dan.michaelis@verizon.com "Roy Verstegen" <VERROY@wsinc.com To: Daniel C. Michaelis/EMPL/FL/Verizon@VZNotes > cc: ids@iiug.org Subject: RE: Sequential Scan vs Index Read [354] 02/12/2003 04:32 PM Good point about the row size, rows returned, and hits on the table. I should have included it in my question. Row size is 275, which puts 14 rows per page, so when the table has 40,000 rows that comes out to over 2850 pages. Based on what this table is used for in our company, I would say the queries do not return many rows (1 to 10 rows). Average hits per hour on this table is 17,000 reads/hour.

How are you able to determine that THAT table is being sequentially scanned? Is it possible to take the top 'n' programs that the users access and run SET EXPLAIN on them? Have you run 'update statistics' against this table? > -----Original Message----- > From: Roy Verstegen [mailto:VERROY@wsinc.com] > Sent: Wednesday, February 12, 2003 3:19 PM > To: ids@iiug.org > Subject: Sequential Scan vs Index Read [354] > > > The facts. > OS = AIX 4.3.3 > Machine = RS 6000 F80 (64 bit) > IDS = 7.31.FD1 > > We have a table that is being sequentially scanned over 100 > times an hour > during our peak hours. This table has flucuated between > 20,000 and 40,000 > rows over the course of this month. This table already has > multiple indices > (5). The sequential scans must be slowing down the users, but > the users are > not complaining, because I think it has always been this way > for them . The > issue is that we have over 250 seperate 4GL programs that > query this table > somewhere in the code. The code is from a third party > supplier that is not > moving very fast to help us solve this issue. > > Are there any Informix tools available to us to see which queries are > reading the table through an index, and which ones are > sequentially scanning > the table? To run set explain for all 250+ 4GL programs would > be too time > consuming and cumbersome. > > Any help would be appreciated, thank you. > > Roy Verstegen > WSI - Informix DBA > > > "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."

How are you able to determine that THAT table is being sequentially scanned? select CURRENT YEAR TO MINUTE date_time, b.tabname, a.nrows, b.seqscans from systables a, sysmaster:sysptprof b where a.partnum = b.partnum and a.tabid > 99 and a.tabtype = 'T' and a.nrows > 1000 and b.seqscans >= 100; We run this query hourly, drop the data into a table and then run onstat -z afterwards. I believe we may have gotten this from Lester Knutsen (great resource!). Is it possible to take the top 'n' programs that the users access and run SET EXPLAIN on them? Good idea, we can pursue this. Have you run 'update statistics' against this table? We run update statistics nightly for the entire db, not specifically against this table.

Roy, Check out by retrieving records from sysmaster.syssqexplain for this table. That will give an idea of the type of SQL's ran for this table, estimated cost,rows etc. Hope this helps. Rajesh Rajasekaran Informix Database Administrator Forest Pharmaceuticals Inc. 314-493-7073 (Work) 314-662-6619 (Cell) 314-514-2457 (Home) -----Original Message----- From: Roy Verstegen [mailto:VERROY@wsinc.com] Sent: Wednesday, February 12, 2003 2:19 PM To: ids@iiug.org Subject: Sequential Scan vs Index Read [354] The facts. OS = AIX 4.3.3 Machine = RS 6000 F80 (64 bit) IDS = 7.31.FD1 We have a table that is being sequentially scanned over 100 times an hour during our peak hours. This table has flucuated between 20,000 and 40,000 rows over the course of this month. This table already has multiple indices (5). The sequential scans must be slowing down the users, but the users are not complaining, because I think it has always been this way for them . The issue is that we have over 250 seperate 4GL programs that query this table somewhere in the code. The code is from a third party supplier that is not moving very fast to help us solve this issue. Are there any Informix tools available to us to see which queries are reading the table through an index, and which ones are sequentially scanning the table? To run set explain for all 250+ 4GL programs would be too time consuming and cumbersome. Any help would be appreciated, thank you. Roy Verstegen WSI - Informix DBA

Couldn't you tell which connection was doing all the reads based on the onstat -u? Then based on that couldn't you start to monitor onstat -r -gses <id> to see which sql statement was being executed and for what duration? Just a thought? Thanks, Greg "dan.michael.... " To: ids@iiug.org <dan.michaelis@v cc: erizon.com> Subject: RE: Sequential Scan vs Index Read [357] Sent by: forum.subscriber @iiug.org 02/12/2003 04:12 PM Roy, Hmmm... Now you've got me... <laugh> That might still fit in cache, though it seems like it's large enough to be cleaned out, unless it really is the only thing being hit. That frequency, it might be there anyway. I don't know of any good ways to see if a table is being sequentially scanned vs. index read without doing as you suggested; using set explain. I don't know if ispy might help, but there's a noticeable bit of overhead from what I understand in using that. You might only want to do that in a test environment, and get the results for analysis on the prod environment. Given your kind of traffic, I'd be concerned that you'd slow yourself down. The only thing that I might do is look at total "real" I/O on the system. If you find that most of your reads are buffer reads, and few are actual page reads, then it probably doesn't matter much anyway; if you're always reading memory things are going to be pretty fast no matter what. Let me know what you've decided to do... I'm always interested in creative solutions to problems! Dan Michaelis 813.978.6534 (office) 813.303.3225 (pager) dan.michaelis@verizon.com "Roy Verstegen" <VERROY@wsinc.com To: Daniel C. Michaelis/EMPL/FL/Verizon@VZNotes > cc: ids@iiug.org Subject: RE: Sequential Scan vs Index Read [354] 02/12/2003 04:32 PM Good point about the row size, rows returned, and hits on the table. I should have included it in my question. Row size is 275, which puts 14 rows per page, so when the table has 40,000 rows that comes out to over 2850 pages. Based on what this table is used for in our company, I would say the queries do not return many rows (1 to 10 rows). Average hits per hour on this table is 17,000 reads/hour.

----- Original Message ----- From: "Roy Verstegen " <VERROY@wsinc.com> To: <ids@iiug.org> Sent: Wednesday, February 12, 2003 4:37 PM Subject: RE: Sequential Scan vs Index Read [356] > > Row size is 275, which puts 14 rows per page, so when the table has 40,000 > rows that comes out to over 2850 pages. You have a 4K Page? If the table has that sort of usage and rowcount an index is in order. Rajesh has the right idea - check the explan table to see how it's being used. If you have the source code and a 4gl compiler, you can get analyze_idx from the iiug site. This will scan your source code and tell you how the tables in your database are being used from the code standpoint and then score each column's value as an index with regard to its use. More of a sledgehammer than a scalpel, but useful. cheers j.

Ahh, now I understand. There was a bug where seqscans was incremented when a key-first scan on an index occurred. I saw something similiar to this a while back; it said that one of my largest tables was begin sequentially scanned. Not totally correct. > -----Original Message----- > From: Roy Verstegen [mailto:VERROY@wsinc.com] > Sent: Wednesday, February 12, 2003 5:53 PM > To: ids@iiug.org > Subject: RE: Sequential Scan vs Index Read [359] > > > How are you able to determine that THAT table is being > sequentially scanned? > > select CURRENT YEAR TO MINUTE date_time, > b.tabname, a.nrows, b.seqscans > from systables a, sysmaster:sysptprof b > where a.partnum = b.partnum > and a.tabid > 99 > and a.tabtype = 'T' > and a.nrows > 1000 > and b.seqscans >= 100; > > We run this query hourly, drop the data into a table and then > run onstat -z > afterwards. I believe we may have gotten this from Lester > Knutsen (great > resource!). > > Is it possible to take the top 'n' programs that the users > access and run > SET EXPLAIN on them?> > Good idea, we can pursue this. > > Have you run 'update statistics' against this table? > > We run update statistics nightly for the entire db, not > specifically against > this table. > > > "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."