RE: BUFWAITS on SQL query

Run the query with set explain on, and see what it is doing. You may be missing something in the database. MW -----Original Message----- From: owner-informix-list@iiug.iiug.org [mailto:owner-informix-list@iiug.iiug.org]On Behalf Of bongartz@my-deja.com Sent: Monday, December 04, 2000 11:50 PM To: informix-list@iiug.org Subject: 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.