partition buffer summary - Btree percentage

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.

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. Here are some numbers from two my SAP sites : Data 86.80 Index 4.32 Other 8.88 Data 42.36 Index 30.30 Other 27.34 Both sites are IDS 7.30TC7XB, WinNT 4.0, SP5. Databases : 1-st ~ 30 GB, 2-nd ~ 10 GB (development). SAP 4.0B. > > Informix 7.30.UC7XB > SunOS 5.5.1 > > Database ~50GB > SAP 4.5 > > Actually I believe this is not SAP specific question. Yes, of course not. This bug is actual for 7.31 - as far as I remember. -- With best regards, Yuri Dovgart, SAP R/3, Informix consultant, "Telecominvest" company. E-mail y_dovgart@tci.ukrtel.net Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Sounds like you either have a LOT of index's or the index's you have are badly fragmented. Have you looked at oncheck -pT for detailed statistics? That can help you identify Index's that are taking up large amounts of space (oncheck -pt can help also, but -T gives you statistics on # of entries per leaf). Then you could drop wastefull Index's and creating them. That will compact them. S.W. Vardan Aroustamian <vaar@geocities.com> wrote in message news:7s5n3g$mq7$1@nnrp1.deja.com... > 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.

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.

Any idea in which version this bug is fixed? John Carlson Informix DBA WHSmith USA 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.

Thank you guys - Yuri, Rick, Steven, Pete, It seems we are hitting bug 115327. I've got OSS note 167846 and probably we'll upgrade soon. Right now I have (from onstat -P output) Percentages: Data 17.76 Btree 81.01 Other 1.22 and about 70% of buffers in medium-high priority level of LRU queues (from onstat -R output). I appreciate your help and will post follow-up later. Regards Vardan In article <7s5qkp$pjs$1@nnrp1.deja.com>, Yuri Dovgart <y_dovgart@tci.ukrtel.net> wrote: > 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. > > Here are some numbers from two my SAP sites : > > Data 86.80 > Index 4.32 > Other 8.88 > > Data 42.36 > Index 30.30 > Other 27.34 > > Both sites are IDS 7.30TC7XB, WinNT 4.0, SP5. Databases : 1-st ~ 30 GB, > 2-nd ~ 10 GB (development). SAP 4.0B. > > > > Informix 7.30.UC7XB > > SunOS 5.5.1 > > > > Database ~50GB > > SAP 4.5 > > > > Actually I believe this is not SAP specific question. > > Yes, of course not. This bug is actual for 7.31 - as far as I remember. > > -- > With best regards, Yuri Dovgart, > SAP R/3, Informix consultant, > "Telecominvest" company. > E-mail y_dovgart@tci.ukrtel.net > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't. > -- Vardan Aroustamian vaar@geocities.com Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.