Re: BUFWAITS on SQL query

Richard Savage wrote: You could try running "onstat -F" to see the number of foreground writes. A few dozen is not a problem, but 10's of thousands are. If there are many foreground writes, increase the number of page cleaners, or decrease value of LRU_MAX_DIRTY. bongartz@my-deja.com wrote: > > I have a single sql query which results in a lot of bufwaits and to much > running time > > I played with LRU CLEANERS BUFFERS and NUMAIOVPS but was unable to > reduce the bufwaits. > > Why does a single query produce bufwaits without another session trying > to access > the buffers. > > Here is my query > > SELECT AN.PKID_ANLEGER, AN.GPNR_ANL, > AN.PKID_FONDS, AN.VERM_ART, AN.ERW_ART, > AN.EINLAGE, AN.GUELTIG_AB, AN.GUELTIG_BIS, > F.FONDSNR, GP.TYP,> AN.HR_OK, AN.OV_OK, AN.FN_OK, AN.EINLAGE * 1.95583 > FROM GP, FONDS F, ANLEGER AN > WHERE F.PKID_FONDS = 82 > AND AN.GPNR_ANL = GP.GPNR > AND F.PKID_MANDANT = 1 > AND F.PKID_FONDS = AN.PKID_FONDS > > and here the output of onstat -p for this query: > > Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 00:00:49 -- > 45528 K > bytes > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 4188 4619 34995 88.03 0 0 0 0.00 > isamtot open start read write rewrite delete commit > rollbk > 24702 73 6112 12367 0 0 0 0 > 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 4.07 1.22 0 0 > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 3084 0 24769 0 0 0 0 3 > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 3914 0 0 3914 3 > > Any help on this is greatly welcome > > Sent via Deja.com http://www.deja.com/ > Before you buy.