btree percentages at 95%

Hi all.... We have been analyzing the performance of a particular job and I was wondering if anyone has insight into the following question. The analysis shows that the buffers have 96% btree data and only 3% data. Might this be a symptom of the index bug which shows up in systems that have been migrated from 7.24 to 7.31? The "onstat -p" shows most of my read-aheads occurring for ixda-RA and not idx-RA. Also, the Profile shows that the read/write buffer cache hit ratio is good (99%) so the page vs buffer access are low. Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 63481 91053 114009191 99.94 2473 25449 465944 99.47 isamtot open start read write rewrite delete commit rollbk 111216550 330470 747181 107676672 135508 18757 1536 1973 0 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 3338.00 98.54 0 0 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 21775 0 39171673 0 0 0 3606 25690 ixda-RA idx-RA da-RA RA-pgsused lchwaits 40287 0 4299 44505 1034787 The Buffer Percentages show: Data 3.12 Btree 96.75 Other 0.13

While you could be encountering a bug, key-only scans on Indexes could also be the cause of your "onstat -P" values. Your Index Read-Ahead values seem to support the latter (which, of course, is not necessarily a bad thing). You could explain the important queries of your batch process to get a better understanding of what's happening. Rudy Doug McAllister wrote: > Hi all.... > > We have been analyzing the performance of a particular job and I was > wondering if anyone has insight into the following question. > .... > > The Buffer Percentages show: > > Data 3.12 > Btree 96.75 > Other 0.13

<!doctype html public "-//w3c//dtd html 4.0 transitional//en"> <html> You indeed have been bitten by the bug Dianne talks about, but if you upgrade to 7.31.UC4, be prepared to set the environment variable "LRUAGE=1" to complete the fix. In addition, a new bug is introduced in which the engine will hang if a table is created in sysmaster database at checkpoint time. According to my sources, 7.31.UC5 should be available any day now. <p>HTH <p>Milton J. Vidrine, Jr. <br>Garden Ridge, Inc. <p>Doug McAllister wrote: <blockquote TYPE=CITE>Hi all.... <p>We have been analyzing the performance of a particular job and I was <br>wondering if anyone has insight into the following question. <p>The analysis shows that the buffers have 96% btree data and only 3% <br>data. Might this be a symptom of the index bug which shows up in systems <br>that have been migrated from 7.24 to 7.31? <br>The "onstat -p" shows most of my read-aheads occurring for ixda-RA and <br>not idx-RA. <p>Also, the Profile shows that the read/write buffer cache hit ratio is <br>good (99%) so the page vs buffer access are low. <br>Profile <br>dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached <br>63481&nbsp;&nbsp;&nbsp; 91053&nbsp;&nbsp;&nbsp; 114009191 99.94&nbsp;&nbsp; 2473&nbsp;&nbsp;&nbsp;&nbsp; 25449&nbsp;&nbsp;&nbsp; 465944&nbsp;&nbsp; 99.47 <p>isamtot&nbsp; open&nbsp;&nbsp;&nbsp;&nbsp; start&nbsp;&nbsp;&nbsp; read&nbsp;&nbsp;&nbsp;&nbsp; write&nbsp;&nbsp;&nbsp; rewrite&nbsp; delete&nbsp;&nbsp; commit <br>rollbk <br>111216550 330470&nbsp;&nbsp; 747181&nbsp;&nbsp; 107676672 135508&nbsp;&nbsp; 18757&nbsp;&nbsp;&nbsp; 1536 <br>1973&nbsp;&nbsp;&nbsp;&nbsp; 0 <p>gp_read&nbsp; gp_write gp_rewrt gp_del&nbsp;&nbsp; gp_alloc gp_free&nbsp; gp_curs <br>0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 <p>ovlock&nbsp;&nbsp; ovuserthread ovbuff&nbsp;&nbsp; usercpu&nbsp; syscpu&nbsp;&nbsp; numckpts flushes <br>0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3338.00&nbsp; 98.54&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 <p>bufwaits lokwaits lockreqs deadlks&nbsp; dltouts&nbsp; ckpwaits compress seqscans <br>21775&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 39171673 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3606&nbsp;&nbsp;&nbsp;&nbsp; 25690 <p>ixda-RA&nbsp; idx-RA&nbsp;&nbsp; da-RA&nbsp;&nbsp;&nbsp; RA-pgsused lchwaits <br>40287&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4299&nbsp;&nbsp;&nbsp;&nbsp; 44505&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1034787 <p>The Buffer Percentages show: <p>Data&nbsp; 3.12 <br>Btree 96.75 <br>Other 0.13</blockquote> </html>

Thanks to all who replied... Informix tells me that 7.31.uc5 IS now available for some platforms. Solaris 2.6, however, is not ready yet. Unfortunately I will have to live with things the way they are now since we are in a hard code freeze until after the "EVENT" (y2k). "Milton J. Vidrine, Jr." wrote: > You indeed have been bitten by the bug Dianne talks about, but if you > upgrade to 7.31.UC4, be prepared to set the environment variable > "LRUAGE=1" to complete the fix. In addition, a new bug is introduced > in which the engine will hang if a table is created in sysmaster > database at checkpoint time. According to my sources, 7.31.UC5 should > be available any day now. > > HTH > > Milton J. Vidrine, Jr. > Garden Ridge, Inc. > > Doug McAllister wrote: > >> Hi all.... >> >> We have been analyzing the performance of a particular job and I was >> >> wondering if anyone has insight into the following question. >> >> The analysis shows that the buffers have 96% btree data and only 3% >> data. Might this be a symptom of the index bug which shows up in >> systems >> that have been migrated from 7.24 to 7.31? >> The "onstat -p" shows most of my read-aheads occurring for ixda-RA >> and >> not idx-RA. >> >> Also, the Profile shows that the read/write buffer cache hit ratio >> is >> good (99%) so the page vs buffer access are low. >> Profile >> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits >> %cached >> 63481 91053 114009191 99.94 2473 25449 465944 99.47 >> >> isamtot open start read write rewrite delete >> commit >> rollbk >> 111216550 330470 747181 107676672 135508 18757 1536 >> 1973 0 >> >> gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs >> 0 0 0 0 0 0 0 >> >> ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes >> 0 0 0 3338.00 98.54 0 0 >> >> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress >> seqscans >> 21775 0 39171673 0 0 0 3606 25690 >> >> ixda-RA idx-RA da-RA RA-pgsused lchwaits >> 40287 0 4299 44505 1034787 >> >> The Buffer Percentages show: >> >> Data 3.12 >> Btree 96.75 >> Other 0.13 >