Databases Reference
In-Depth Information
Indexing MV Log Columns
Sometimes, you may need better performance from your fast refreshing MVs. One way to do this is through indexes
on columns of the MV log table. In particular, Oracle uses the
SNAPTIME$$
column or the primary key column, or both,
when refreshing or purging. Therefore, indexes on these columns can improve performance:
SQL> create index mlog$_sales_idx1 on mlog$_sales(snaptime$$);
SQL> create index mlog$_sales_idx2 on mlog$_sales(sales_id);
You shouldn't add indexes just because you think it may be a good idea. Only add indexes on the MV log tables
when you have known performance issues with fast refreshes. Keep in mind that adding indexes consumes resources
in the database. Oracle has to maintain the index for DML operations on the table, and an index uses disk space.
Viewing Space Used by an MV Log
You should consider periodically checking the space consumed by an MV log. If the space consumed is growing (and
never shrinking), you may have an issue with an MV's not successfully refreshing and hence causing the MV log never
to be purged. Here is a query to check the space of MV logs:
select segment_name, tablespace_name
,bytes/1024/1024 meg_bytes, extents
from dba_segments
where segment_name like 'MLOG$%'
order by meg_bytes;
Here is some sample output:
SEGMENT_NAME TABLESPACE_NAME MEG_BYTES EXTENTS
-------------------- ------------------------------ ---------- ----------
MLOG$_USERS MV_DATA 1609 3218
MLOG$_ASSET_ATTRS MV_DATA 3675.5 7351
This output indicates that a couple of MV logs most likely have purging issues. In this situation there are probably
multiple MVs that are using the MV log, and one of them isn't refreshing on a daily basis, thus preventing the log from
being purged.
You may run into a situation in which an MV log hasn't been purged for quite some time. This can happen
because you have multiple MVs using the same MV log, and one of those MVs isn't successfully refreshing anymore.
This can happen when a DBA builds a development environment and connects development MVs to the production
environment (it shouldn't happen, but it does). At some later point in time, the DBA drops the development database.
The production environment still has information regarding the remote development MV and won't purge MV log
records because it thinks a fast refreshable MV needs the log data to refresh.
In these scenarios, you should determine which MVs are using the log (see the section “Determining How Many
MVs Reference a Central MV Log,” later in this chapter), and resolve any issues. After the problem is solved, check the
space being used by the log, and see if it can be shrunk (see the next section, “Shrinking the Space in an MV Log”).
Shrinking the Space in an MV Log
If an MV log doesn't successfully delete records, it grows large. After you resolve the issue, and the records are deleted from
the MV log, you can set the high-water mark for the MV log table to a high value. But, doing so may cause performance
issues and also unnecessarily consumes disk space. In this situation, consider shrinking the space used by the MV log.
