RE: How to do Light Scans . . .

Rather than write it all over again. http://www.ibm.com/developerworks/db2/library/techarticle/dm-0205parker/ Although scanning that briefly I see that it does not mention that Light Scans will not work against a table with varchars. j. On Mon, Jan 5, 2009 at 11:10 AM, LIGHT SCANS wrote: > Over-simplified definition: LIGHT SCANS occur when you sequentially scan data without using the BUFFERS.  This can be about 1 to 3 times faster than regular scans! It does not like locking, filtering or varchar but it will tolerate some of it.  IBM tells me (and I'm paraphrasing) that it uses SHMVIRTSIZE to hold one or more "private buffers" and then processes them in parallel, asynchronously, and block-wise.  It can run with or without PDQ.  And if you set LIGHT_SCANS=FORCE, it will bypass the need for PDQ, big tables and small BUFFERS. Setup example: In ONCONFIG it uses very little SHMVIRTSIZE so the only things that I recommend that you "tune" are the RA_PAGES and RA_THRESHOLD.  When you increase them you also increase the number of "bufcnt" in "onstat -g lsc".  Then run "export LIGHT_SCANS=FORCE".  An finally in your SQL put "SET ISOLATION TO DIRTY READ;" and "SET  PDQPRIORITY 0;" More tips and tricks: 1.  The "onstat -g lsc" and "onstat -g ses <session_id>" shows LIGHT SCAN info. 2.  LIGHT SCANS by itself is usually faster than PDQ by itself. 3.  Only combine LIGHT SCANS and PDQ when doing update statistics HIGH to make fewer passes through the table. 4.  Update statistics HIGH can use PDQ and/or LIGHT SCANS.  LOW and MEDIUM cannot. 5.  LIGHT SCANS might occur even if you don't want them to.  That happens when a scan first fills up the BUFFERS, then fills up the PDQ and then finally does LIGHT SCANS. Please help: I do not know everything about light scans.  But I want to!  So please add your comments, corrections, etc. _______________________________________________ Informix-list mailing list Informix-list@iiug.org <mailto:Informix-list@iiug.org> <mailto:Informix-list@iiug.org> http://www.iiug.org/mailman/listinfo/informix-list <http://www.iiug.org/mailman/listinfo/informix-list>; <http://www.iiug.org/mailman/listinfo/informix-list>;

Hello Jack, Your link is great. It explains more about Light Scans than the Informix manuals. I wish that the IBM Informix Dynamic Server Performance Guide would have it, especially your formula for bufcnt! As to the varchars, you are probably right. I'm going to test it. Thanks, L.S.