RE: partition buffer summary - Btree percentage

Don't know if Pete already replied, but here is JM's "script": ============================================================================ = I have just tracked down this issue and dropping and re-creating the indexes will help, but only for a period of time. To find out if you have this problem the following select will identify the tables. SELECT trim(dbsname)||":"||trim(tabname) FROM syspaghdr P, systabnames T WHERE P.pg_flags = 208 AND P.pg_partnum > 1048570 AND P.pg_partnum = T.partnum This select is slow, because it must read every single page in your online system. The line "P.pg_partnum > 1048570" does not look important, but it is! This will help reduce the number of pages read by a significant amount. Without this line you will read free, unused pages, physical log, logical logs and all pages which are not associated with tables. The problem stems from the fact that shrinking index nodes can leave the leaf flag set. To see if you generally do this type of shrinking run the following select statement and see if it returns a row: select * from sysshmhdr where value <> 0 and number = 95; This problem does exist in both 7.2 and 7.3. The best thing to do is to call in and ask for bug 115327 to be fixed. This should address the problem from two sides. First it will ensure that only node pages and only node pages will be put in the MED_HIGH queue and second it will stop the creation of pages being flagged as node and leaf pages. In addition you will not need to rebuild the indexes. Hope this helps, ---jmiller John Miller ============================================================================ I also HTH! Paul Mosser -----Original Message----- From: mzernikow@my-deja.com [mailto:mzernikow@my-deja.com] Sent: Tuesday, October 19, 1999 9:28 AM To: informix-list@iiug.org Subject: Re: partition buffer summary - Btree percentage Hello, I am very interested in the script of Mr. Miller for finding invalid page types. Is it still possible to get one copy of this script? If yes, could you please send me a copy to MZernikow@heyde.de. Many Thanks in advance MfG Mathias Zernikow In article <7s7dav$t2j$1@nnrp1.deja.com>, plsmith@my-deja.com wrote: > Vardan > > You have almost certainly run into bug no. 115327 and you should be > able to request a patch to fix it. > It is a bug in 7.2x and 7.3x whereby btree pages can be generated with > an invalid page type. This page type is then handled incorrectly by the > new 7.30 buffer priority management. > Dropping and rebuilding affected indexes is a reasonable workaround, but > the problme could re-occur depending on the habits of you application. > The invalid page types are generated when all the rows are deleted from > a table thus compressing the index down to a single page. When rows are > inserted an invalid page type is then copied through all the leaf nodes. > If you look at onstat -R you will see that the majority of pages are in > the medium-high category, whereas data pages are medium-low. Leaf node > pages should also be medium low, but because of the invalid page type > they are placed in the medium-high category, thus squeezing out the data > pages. If you install a patch you should see a reversal of these > percentages and an increase in your read cache. > If you talk to Informix tech support refer them to case No. 861366 which > contains a reproducable example. > I have a small script which will identify invalid pages types (sql > courtesy of John Miller, thanks ). > > email me if you would like a copy. > > hth > Pete Smith > Logica > > In article <7s5n3g$mq7$1@nnrp1.deja.com>, > Vardan Aroustamian <vaar@geocities.com> wrote: > > Hello, > > > > I'm new to SAP environment and would like to get some input > > from experienced users. > > > > Anyone knows of some "usual" percentage for Data vs. Btree > > pages in the buffer pool (from onstat -P output). > > > > Currently I have > > > > Percentages: > > Data 20.09 > > Btree 77.75 > > Other 2.16 > > > > I heard about some bug when Btree is more than 90%. > > I'm not sure will I reach that number. > > > > Informix 7.30.UC7XB > > SunOS 5.5.1 > > > > Database ~50GB > > SAP 4.5 > > > > Actually I believe this is not SAP specific question. > > Anyway, thanks for any information. > > > > Vardan > > > > -- > > Vardan Aroustamian > > vaar@geocities.com > > > > Sent via Deja.com http://www.deja.com/ > > Share what you know. Learn what you don't. > > > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't. > Sent via Deja.com http://www.deja.com/ Before you buy.