clarification of parallel query on informix and oracle

Hi Gurus , I use oracle 9i rac on linux redhat ia64 . When i issue a parallel query on oracle , the query bypasses the oracle buffer cache and use process memory instead. So no caching occurs. When a full table scan is issued in parallel , it is also true that , oracle always bypasses the buffer cache.(Related oracle wait event for this is direct path read) . It does not matter whether the table which is full table scanned has some cached blocks in memory , they are not used by parallel query . So if there is no file system caching in use , subsequent running of same parallel query take same time since no caching is inuse. As i remember correctl , with informix , we also use som ebypassing mechanism called light scans which bypasses the resident memory and uses virtual memory , but how about the caching? Does informix use caching when a query runs in parallel .. Kind Regards, hope

this is some time ago; if i recall correctly: there is an env var called export LIGHT_SCANS=FORCE and there are restrictions on it; check the manual for it; it may also be dependant on your isolation level; if dirty read then light_scans ?? monitor it using onstat -g lsc there is also something called light appends --> temp table with no log and temp dbspaces can use this; also the high perf loader; monitor using onstat -g lap you can also 'configure' bits of it; RA_PAGES and RA_THRESHOLD onconfig params have influence on how many buffers are used for light scans/appends. See you Superboer. hopehope_123@yahoo.com (utkanbir) wrote in message news:<f6c90ebe.0410052300.6f6ecd72@posting.google.com>... > Hi Gurus , > > I use oracle 9i rac on linux redhat ia64 . When i issue a parallel > query on oracle , the query bypasses the oracle buffer cache and use > process memory instead. So no caching occurs. When a full table scan > is issued in parallel , it is also true that , oracle always bypasses > the buffer cache.(Related oracle wait event for this is direct path > read) . It does not matter whether the table which is full table > scanned has some cached blocks in memory , they are not used by > parallel query . So if there is no file system caching in use , > subsequent running of same parallel query take same time since no > caching is inuse. > > > As i remember correctl , with informix , we also use som ebypassing > mechanism called light scans which bypasses the resident memory and > uses virtual memory , but how about the caching? Does informix use > caching when a query runs in parallel .. > > > Kind Regards, > hope

On Wed, 06 Oct 2004 03:00:04 -0400, utkanbir wrote: Read the Administrator's Guide, Light Scans are not used for parallel queries in general, most go through the IDS buffer cache. Light scans do avoid the buffer cache and are used for queries that include a long sequential scan of a table and are only used for pages from that table. This is to prevent a large table scan from thrashing the buffer cache and forcing recently used pages out of the cache. This behavior is independent of whether the query is parallelized or not. Art S. Kagel > Hi Gurus , > > I use oracle 9i rac on linux redhat ia64 . When i issue a parallel query on > oracle , the query bypasses the oracle buffer cache and use process memory > instead. So no caching occurs. When a full table scan is issued in parallel > , it is also true that , oracle always bypasses the buffer cache.(Related > oracle wait event for this is direct path read) . It does not matter whether > the table which is full table scanned has some cached blocks in memory , > they are not used by parallel query . So if there is no file system caching > in use , subsequent running of same parallel query take same time since no > caching is inuse. > > > As i remember correctl , with informix , we also use som ebypassing > mechanism called light scans which bypasses the resident memory and uses > virtual memory , but how about the caching? Does informix use caching when a > query runs in parallel .. > > > Kind Regards, > hope

>Light scans do avoid the > buffer cache and are used for queries that include a long sequential scan of > a table and are only used for pages from that table. I've wondered about that, too. So, if a light scan is to be used, IDS ignores any pages already in the buffer cache? Suppose the whole table is already cached (say it was made resident)-- would that affect the answer? Thank you. DG