Problem with materialised view log on AUSP

[Former Member]

I have a materialised view in an external database that gets refreshed via a materialised view log on AUSP.

Database is Oracle 9.2.0.7 on TRU64.

R/3 version is 4.7 ext110

I've been looking at the number of records in this log (MLOG$_AUSP), total fluctuates between 0 and 1200 records.

Not much!

However, if I try and do a select count(*) from sapr3.MLOG$_AUSP it takes over a minute.

The execution plan is:

COUNT(*)
----------
         0

Elapsed: 00:01:42.60

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=86389 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'MLOG$_AUSP' (Cost=86389 Card=506
          )





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     569248  consistent gets
     529854  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        652  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

I don't get it. Why could there be so many reads if the record count is so low?

This is also affecting my database buffer quality.

Any help would be appreciated.

[Former Member]

Well, it would appear a simple reorg has fixed the problem!