Question re. sysptprof
Morning all from a damp, squally, dreary, grey, shabby little seaside town on the south coast! Just a quick ask regarding sysptprof; I'm using evidence from this to try to reduce the number of indexes on a round-robin fragmented table with 51 Million rows and 23 indexes, via the following sql: select t.tabname[1,20],i.idxname[1,20], p.isreads, p.bufreads, p.pagreads from systables t, sysindexes i, sysmaster:systabnames n, sysmaster:sysptprof p where t.tabid = i.tabid and i.idxname = n.tabname and n.partnum = p.partnum and t.tabname = "cashbook" order by 5, 1,2 giving us results similar to: tabname idxname isreads bufreads pagreads cashbook ncashbk_cdn_71 0 4358039 6531 cashbook ncashbk_cdn_85 38 4380481 7507 cashbook ncashbk_cdn_85 33 4392820 7622 cashbook ncashbk_cdn_71 0 4340645 8705 cashbook ncashbk_cdn_96 100 2367651 8897 cashbook ncashbk_cdn_96 99 2362255 9053 cashbook cbk_rr_cdn_01 378 4844936 9664 cashbook cbk_rr_cdn_01 296 4843241 9672 cashbook ncashbk_cdn_84 16710 4141585 9700 cashbook ncashbk_cdn_84 16116 4129121 9776 ...and so on Which column should I pay most attention to - I'm guessing isreads? Why are there buf & page reads when isreads are 0? Thanks (Off to boil the kettle - again!)
iiug@perrior.net wrote: > Morning all from a damp, squally, dreary, grey, shabby little seaside > town on the south coast! > Just a quick ask regarding sysptprof; I'm using evidence from this to > try to reduce the number of indexes on a round-robin fragmented table > with 51 Million rows and 23 indexes, via the following sql: > > select t.tabname[1,20],i.idxname[1,20], p.isreads, p.bufreads, > p.pagreads > from systables t, sysindexes > i, > sysmaster:systabnames n, sysmaster:sysptprof > p > where t.tabid = > i.tabid > and i.idxname = > n.tabname > and n.partnum = > p.partnum > and t.tabname = "cashbook" > order by 5, 1,2 > > giving us results similar to: > tabname idxname isreads bufreads > pagreads > > cashbook ncashbk_cdn_71 0 > 4358039 6531 > cashbook ncashbk_cdn_85 38 > 4380481 7507 > cashbook ncashbk_cdn_85 33 > 4392820 7622 > cashbook ncashbk_cdn_71 0 > 4340645 8705 > cashbook ncashbk_cdn_96 100 > 2367651 8897 > cashbook ncashbk_cdn_96 99 > 2362255 9053 > cashbook cbk_rr_cdn_01 378 > 4844936 9664 > cashbook cbk_rr_cdn_01 296 > 4843241 9672 > cashbook ncashbk_cdn_84 16710 > 4141585 9700 > cashbook ncashbk_cdn_84 16116 > 4129121 9776 > > ...and so on > > Which column should I pay most attention to - I'm guessing isreads? > Why are there buf & page reads when isreads are 0? Not feeling my sharpest today, but I think isreads are physical disk reads. The fact that any reads at all are occurring means that the index is being used. However, that still means that some of these indexes could be subotimal. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com
my 2 0.01 on this: if you do an insert/delete and may be updates, the indexes will be hit. the btcleaner, rebalancing the tree etc. so using sysptprof is not really bullitproof to kick out indexes i guess. i suggest to grab also the qrys hitting the table (if possible..) and use sqexplain on them to check if an index is used.... Superboer. On 4 dec, 11:02, i...@perrior.net wrote: > Morning all from a damp, squally, dreary, grey, shabby little seaside > town on the south coast! > Just a quick ask regarding sysptprof; I'm using evidence from this to > try to reduce the number of indexes on a round-robin fragmented table > with 51 Million rows and 23 indexes, via the following sql: > > select t.tabname[1,20],i.idxname[1,20], p.isreads, p.bufreads, > p.pagreads > from systables t, sysindexes > i, > sysmaster:systabnames n, sysmaster:sysptprof > p > where t.tabid = > i.tabid > and i.idxname = > n.tabname > and n.partnum = > p.partnum > and t.tabname = "cashbook" > order by 5, 1,2 > > giving us results similar to: > tabname idxname isreads bufreads > pagreads > > cashbook ncashbk_cdn_71 0 > 4358039 6531 > cashbook ncashbk_cdn_85 38 > 4380481 7507 > cashbook ncashbk_cdn_85 33 > 4392820 7622 > cashbook ncashbk_cdn_71 0 > 4340645 8705 > cashbook ncashbk_cdn_96 100 > 2367651 8897 > cashbook ncashbk_cdn_96 99 > 2362255 9053 > cashbook cbk_rr_cdn_01 378 > 4844936 9664 > cashbook cbk_rr_cdn_01 296 > 4843241 9672 > cashbook ncashbk_cdn_84 16710 > 4141585 9700 > cashbook ncashbk_cdn_84 16116 > 4129121 9776 > > ...and so on > > Which column should I pay most attention to - I'm guessing isreads? > Why are there buf & page reads when isreads are 0? > > Thanks > (Off to boil the kettle - again!)
I agree with superboer. I would also add that you need to consider the purpose of this database. If its an ODS or OLTP, then you may want to review your indexes, and the queries to determine if you could reduce the number of indexes by also creating compound indexes as a replacement. (I don't know why these DW dbas insist on indexing single columns and avoiding compound indexes. (Ok, I know why some do it but they're being lazy and want to limit schema changes...) In a DW, it may make some sense to have multiple indexes if you don't know what queries you'll have and allow dynamic queries for analysis. YMMV -G > From: superboer7@t-online.de > Subject: Re: Question re. sysptprof > Date: Thu, 4 Dec 2008 05:19:11 -0800 > To: informix-list@iiug.org > > my 2 0.01 on this: > > if you do an insert/delete and may be updates, the indexes will be > hit. > the btcleaner, rebalancing the tree etc. > so using sysptprof is not really bullitproof to kick out indexes i > guess. > > i suggest to grab also the qrys hitting the table (if possible..) and > use sqexplain on them to check > if an index is used.... > > Superboer. > > > > On 4 dec, 11:02, i...@perrior.net wrote: > > Morning all from a damp, squally, dreary, grey, shabby little seaside > > town on the south coast! > > Just a quick ask regarding sysptprof; I'm using evidence from this to > > try to reduce the number of indexes on a round-robin fragmented table > > with 51 Million rows and 23 indexes, via the following sql: > > > > select t.tabname[1,20],i.idxname[1,20], p.isreads, p.bufreads, > > p.pagreads > > from systables t, sysindexes > > i, > > sysmaster:systabnames n, sysmaster:sysptprof > > p > > where t.tabid = > > i.tabid > > and i.idxname = > > n.tabname > > and n.partnum = > > p.partnum > > and t.tabname = "cashbook" > > order by 5, 1,2 > > > > giving us results similar to: > > tabname á á á á á á áidxname á á á á á á á á áisreads á ábufreads > > pagreads > > > > cashbook á á á á á á ncashbk_cdn_71 á á á á á á á á 0 > > 4358039 á á á á6531 > > cashbook á á á á á á ncashbk_cdn_85 á á á á á á á á38 > > 4380481 á á á á7507 > > cashbook á á á á á á ncashbk_cdn_85 á á á á á á á á33 > > 4392820 á á á á7622 > > cashbook á á á á á á ncashbk_cdn_71 á á á á á á á á 0 > > 4340645 á á á á8705 > > cashbook á á á á á á ncashbk_cdn_96 á á á á á á á 100 > > 2367651 á á á á8897 > > cashbook á á á á á á ncashbk_cdn_96 á á á á á á á á99 > > 2362255 á á á á9053 > > cashbook á á á á á á cbk_rr_cdn_01 á á á á á á á á378 > > 4844936 á á á á9664 > > cashbook á á á á á á cbk_rr_cdn_01 á á á á á á á á296 > > 4843241 á á á á9672 > > cashbook á á á á á á ncashbk_cdn_84 á á á á á á 16710 > > 4141585 á á á á9700 > > cashbook á á á á á á ncashbk_cdn_84 á á á á á á 16116 > > 4129121 á á á á9776 > > > > ...and so on > > > > Which column should I pay most attention to - I'm guessing isreads? > > Why are there buf & page reads when isreads are 0? > > > > Thanks > > (Off to boil the kettle - again!) > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Send e-mail faster without improving your typing skills. http://windowslive.com/Explore/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_speed_122008
On 4 Dec, 15:35, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > I agree with superboer. > > I would also add that you need to consider the purpose of this database. If its an ODS or OLTP, then you may want to review your indexes, and the queries to determine if you could reduce the number of indexes by also creating compound indexes as a replacement. (I don't know why these DW dbas insist on indexing single columns and avoiding compound indexes. (Ok, I know why some do it but they're being lazy and want to limit schema changes...) > > In a DW, it may make some sense to have multiple indexes if you don't know what queries you'll have and allow dynamic queries for analysis. > > YMMV > > -G > > > > > From: superbo...@t-online.de > > Subject: Re: Question re. sysptprof > > Date: Thu, 4 Dec 2008 05:19:11 -0800 > > To: informix-l...@iiug.org > > > my 2 0.01 on this: > > > if you do an insert/delete and may be updates, the indexes will be > > hit. > > the btcleaner, rebalancing the tree etc. > > so using sysptprof is not really bullitproof to kick out indexes i > > guess. > > > i suggest to grab also the qrys hitting the table (if possible..) and > > use sqexplain on them to check > > if an index is used.... > Well, yes, agree on both counts. There's plenty more analysis going on regarding cleaners, index selectivity and the queries placed on that table, so we're not just relying on sysptprofs, it's so I can gather some more ammo for me to fire at the developers & design team. I was just intrigued about the different figures in the isreads, bufreads and pagreads columns, is all.