Consistent reads against a heavily updated table (snapshot)

From: NetComrade <netcomradeNSPAM_at_bookexchange.net>
Date: Wed, 16 Feb 2005 16:27:39 GMT
Message-ID: <421370d6.1192753308@localhost>

Well,

We've tried to make a 'transactional' snapshot, where the snapshot is not truncated when being refreshed (but sticking it into a refresh group). However, the queries running against the snapshot take almost as long to run as for the snapshot to refresh, which is not acceptable. Right now we are looking for alternatives (making snapshot a table, and updating selected rows), however, wanted to know if there is anyway to speed up, or it's just a function of slow IO.

Regular query takes seconds to exe and results in

       2227  consistent gets
         36  physical reads
          0  redo size

Same query while snapshot is running:
      70177  consistent gets
      10246  physical reads
      39364  redo size

.......
We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes remove NSPAM to email Received on Wed Feb 16 2005 - 10:27:39 CST