BUFWAITS on SQL query

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.

OS and Version of Informix are always nice. In article <90fst4$lfm$1@nnrp1.deja.com>, 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. onstat -c might help us see what you have these set to. > > Why does a single query produce bufwaits without another session trying > to access > the buffers. If you have multiple AIOVP's reading the data in, they could run into LRU contention trying to put the data into your 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. > Sent via Deja.com http://www.deja.com/ Before you buy.