count sequential scans avoiding skip scan access
Jacobo asked whether Informix counts index SKIP SCAN access as a sequential scan, since pf_seqscans (and seqscans in sysptprof/sysptntab) appeared to increase for a table queried via skip scan, making that counter unreliable for spotting true sequential scans. Art Kagel expected it to count against the index, not the table; Fernando explained skip scan is really an index path with rowids sorted for more sequential data-page access, so it shouldn't count as a seqscan and suggested opening a PMR. Andreas said it was a known defect fixed in 12.10.xC5; Jacobo was on 11.70 and accepted it as a bug there.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi and, as always, thanks beforehand. Is there a way to count the true sequential scans done in a table? Because skip scan access method also increases pf_seqscans (I'm right here isn't it?). Is there also a way to know the skip scan access performance?
Jacabo: I have always thought that the skip scan counts against the index being skip scanned rather than against the table it indexes. John: can you comment? Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Apr 1, 2016 at 2:39 AM, JACOBO BALBUENA <jacobo.bc@gmail.com> wrote: > Hi and, as always, thanks beforehand. > > Is there a way to count the true sequential scans done in a table? > > Because skip scan access method also increases pf_seqscans (I'm right here > isn't it?). > > Is there also a way to know the skip scan access performance? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bea43fc1e01ec052f69bb5a
I checked the pf_seqscans of the table after repeating a query that had the skip scan. I trust you 1000 times more. I may have done the test wrong or someone else was querying the table at the same time I was testing. I'll repeat the test and post here what I did and what where the results.
Credit to you. I never tested it. Interesting result. My impression was based on thinking "Well, it isn't really the table that's being scanned, but the leaf nodes of the index." I guess the IBM engineers don't think the same way I do. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Apr 1, 2016 at 6:47 AM, JACOBO BALBUENA <jacobo.bc@gmail.com> wrote: > I checked the pf_seqscans of the table after repeating a query that had the > skip scan. > > I trust you 1000 times more. I may have done the test wrong or someone else > was querying the table at the same time I was testing. > I'll repeat the test and post here what I did and what where the results. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bd6bac4a9dd6e052f6a5928
The problem is that I was trusting that field to point out tables that where having sequential scans. Is there another trustier way to get the tables that are getting sequential scans on?
I use te seqscans column from sysptprof which is just a view on sysptntab, so same value. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Apr 1, 2016 at 7:15 AM, JACOBO BALBUENA <jacobo.bc@gmail.com> wrote: > The problem is that I was trusting that field to point out tables that > where > having sequential scans. Is there another trustier way to get the tables > that > are getting sequential scans on? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113ee868e11dfa052f6ab28b
Can you make it clear that you're referring to INDEX PATH (SKIP SCAN) in the query plan? If that's the case I think you should open a PMR. That's not even close to a sequential scan. And to complement Art's answer, we're not scanning the leaf nodes... We're doing a regular INDEX PATH (key-first) and before we access the data pages we're ordering the rowids so that we access the data pages in a more "sequential" mode. This can have a huge benefit as these accesses will take advantage of several caches in the several layers (disk, SANs etc.) So, unless I'm completely wrong this should not count as a sequential scan, and you should not try to find another way if this is a bug... Meanwhile I hardly see the engine taking this path (version 12 seems to be smarter). Which version are you using? I'd like to confirm this happens, but today I won't have time for it probably... Regards. On Fri, Apr 1, 2016 at 12:15 PM, JACOBO BALBUENA <jacobo.bc@gmail.com> wrote: > The problem is that I was trusting that field to point out tables that > where > having sequential scans. Is there another trustier way to get the tables > that > are getting sequential scans on? > > > > ******************************************************************************* > 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... --94eb2c0810ac85c35c052f6abee7
Hi Jacobo, as far as I know this problem shouldn't exist any more since 12.10.xC5,=20 i.e. has been detected as a defect and fixed. HTH, Andreas From: "JACOBO BALBUENA" <jacobo.bc@gmail.com> To: ids@iiug.org Date: 01.04.2016 08:40 Subject: count sequential scans avoiding skip scan access [36882] Sent by: ids-bounces@iiug.org Hi and, as always, thanks beforehand.=20 Is there a way to count the true sequential scans done in a table?=20 Because skip scan access method also increases pf=5Fseqscans (I'm right her= e=20 isn't it?).=20 Is there also a way to know the skip scan access performance?=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20
Its 11.70 hopefully I will be able to convince cusomer his server won't blow up if I put 12.12 in 3-4 months... Thanks all for the answers. I guess its 11:70 bug.