Pro Oracle Database 11g Administration (66 page)

logs. Use the WITH COMMIT SCN clause to create an SCN–based MV log:

SQL> create materialized view log on inv tablespace users with commit scn; 391

CHAPTER 15 ■ MATERIALIZED VIEWS

You can view whether an MV log is SCN based by querying USER_MVIEW_LOGS: select

log_table

,commit_scn_based

from user_mview_logs;

Indexing Materialized View 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, consider indexing the primary-key column and the SNAPTIME$$ column. Oracle potentially uses two columns in WHERE clauses when refreshing an MV or purging the MV log. Here are examples of creating indexes on MV log columns: SQL> create index mlog$_inv_idx1 on mlog$_inv(snaptime$$) tablespace mv_index; SQL> create index mlog$_inv_idx2 on mlog$_inv(inv_id) tablespace mv_index; 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 consumes disk space. Indexes aren’t free silver bullets for performance issues; they come with some cost, so you should use them judiciously.

Viewing Space Used by a Materialized View 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 not successfully refreshing (and hence causing the MV log never to be purged). Here’s 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’s some sample output:

SEGMENT_NAME TABLESPACE_NAME MEG_BYTES EXTENTS

------------------------- --------------- ----------- ----------

MLOG$_USER_ROLES_ASSOC MV_DATA 24.00 48

MLOG$_ASSET_GEO_LOCATIONS MV_DATA 60.50 121

MLOG$_USERS MV_DATA 88.50 177

MLOG$_REGISTRATIONS MV_DATA 465.00 930

This output indicates that several MV logs most likely have purging issues. The MLOG$_REGISTRATIONS

segment is consuming nearly 500MB of space. In this situation, there are probably multiple MVs that are using the MV log, and one of the MVs isn’t refreshing on a daily basis, thus preventing the log from being purged.

You may run into a situation where 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 392

CHAPTER 15 ■ MATERIALIZED VIEWS

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 on Determining How Many MVs Reference a Central MV Log) 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 a Materialized View Log

If an MV log doesn’t successfully delete records, this causes it to grow to a large size. 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 situations, consider shrinking the space used by the MV log.

In this example, MLOG$_REGISTRATIONS had a problem with not purging records because of an issue with an associated MV not successfully refreshing. This MV log subsequently grew to a large size. The issue was identified and resolved, and now the log’s space needs to be reduced. To shrink the space in an MV log, first enable row movement on the appropriate MV log MLOG$ table: SQL> alter table mlog$_registrations enable row movement;

Next, issue the ALTER MATERIALIZED VIEW LOG ON...SHRINK statement. Notice that the table after the ON keyword is the master table name (in this example, it’s the REGISTRATIONS table): SQL> alter materialized view log on registrations shrink space;

This statement may take a long time, depending on the amount of space it shrinks. After the statement finishes, you can disable row movement:

SQL> alter table mlog$_registrations disable row movement;

You can verify that the space has been reduced by querying DBA_SEGMENTS: select

segment_name

,tablespace_name

,bytes/1024/1024 meg_bytes

,extents

from dba_segments

where segment_name = 'MLOG$_REGISTRATIONS';

The space used by this segment is quite small:

SEGMENT_NAME TABLESPACE_NAME MEG_BYTES EXTENTS

------------------------- --------------- ----------- ----------

MLOG$_REGISTRATIONS MV_DATA .50 1

Checking the Row Count of a Materialized View Log

As mentioned earlier in this chapter, sometimes there are problems with an MV refreshing, and this results in large numbers of rows building up in the corresponding MV log table. This sometimes happens when multiple MVs are using one MV log, and one of the MVs can’t perform a fast refresh. In this situation, the MV log continues to grow until the issue is resolved.

393

CHAPTER 15 ■ MATERIALIZED VIEWS

One way of detecting whether an MV log isn’t being purged is to periodically check the row counts of the MV log tables. The following query uses SQL to generate SQL that creates a script that checks row counts for MV log tables owned by the currently connected user:

set head off pages 0 lines 132 trimspool on

spo mvcount_dyn.sql

select 'select count(*) || ' || '''' || ': ' || table_name || ''''

|| ' from ' || table_name || ';'

from user_tables

where table_name like 'MLOG%';

spo off;

This script generates a script named mvcount_dyn.sql that contains the SQL statements to select row counts from the MLOG$ tables. When you’re inspecting row counts, you must be somewhat familiar with your application and have an idea what a normal row count is. Here’s some sample code generated by the previous script:

select count(*) || ': MLOG$_PRODUCT_TAXONOMY' from MLOG$_PRODUCT_TAXONOMY; select count(*) || ': MLOG$_REGISTRATIONS' from MLOG$_REGISTRATIONS;

select count(*) || ': MLOG$_ROLES' from MLOG$_ROLES;

Moving a Materialized View Log

You may need to move an MV log because the initial creation script didn’t specify the correct tablespace.

A common scenario is when the tablespace isn’t specified, and the MV log is placed by default in a tablespace like USERS. You can verify the tablespace information with this query: select

table_name

,tablespace_name

from user_tables

where table_name like 'MLOG%';

If any MV log tables need to be relocated, use the ALTER MATERIALIZED VIEW LOG ON MOVE statement. Notice that you specify the name of the master table (and not the underlying MLOG$

table) on which the MV is created:

SQL> alter materialized view log on inv move tablespace tbsp2;

Also keep in mind that when you move a table, any associated indexes are rendered unusable (because the ROWID of every record in the table has just changed). You can check the status of the indexes as shown:

select

a.table_name

,a.index_name

,a.status

from user_indexes a

,user_mview_logs b

where a.table_name = b.log_table;

Any unusable indexes must be rebuilt. Here’s an example of rebuilding an index: SQL> alter index mlog$_inv_idx1 rebuild;

394

CHAPTER 15 ■ MATERIALIZED VIEWS

Dropping a Materialized View Log

There are a couple of reasons why you may want to drop an MV log:

• You initially created an MV log, but requirements have changed and you no longer need it.

• The MV log has grown large and is causing performance issues, and you want to drop it to reset the size.

Before you drop an MV log, you can verify the owner, master table, and MV log table with the following query:

select

log_owner

,master -- master table

,log_table

from user_mview_logs;

Use the DROP MATERIALIZED VIEW LOG ON statement to drop an MV log. You don’t need to know the name of the MV log, but you do need to know the name of the master table on which the log was created.

This example drops the MV log on the INV table:

SQL> drop materialized view log on inv;

You should see the following message (if successful):

Materialized view log dropped.

If you have permissions, and you don’t own the table on which the MV log is created, you can specify the schema name when dropping the materialized view log:

SQL> drop materialized view log on .

If you’re cleaning up an environment and want to drop all MV logs associated with a user, then use SQL to generate SQL to accomplish this. The following script creates the SQL required to drop all MV

logs owned by the currently connected user:

set lines 132 pages 0 head off trimspool on

spo drop_dyn.sql

select 'drop materialized view log on ' || master || ';'

from user_mview_logs;

spo off;

The previous SQL*Plus code creates a script named drop_dyn.sql that contains the SQL statements that can be used to drop all MV logs for a user.

Refreshing Materialized Views

Typically, you refresh MVs at periodic intervals. You can either refresh the MVs manually or automate this task. The following sections cover these related topics:

• Manually refreshing MVs from SQL*Plus

• Automating refreshes using a shell script and scheduling utility

• Automating refreshes using the built-in Oracle job scheduler

395

CHAPTER 15 ■ MATERIALIZED VIEWS

The result cache can significantly improve performance if you have long-running queries that operate on relatively static data. MVs are better suited for replicating data and storing the results of complex queries that only require new results on a periodic basis (such as daily, weekly, or monthly).

Automating Refreshes Using a Shell Script and Scheduling Utility

Many MVs must be refreshed on a daily basis. To achieve this, you can use a Linux/Unix utility such as cron that calls a shell script to refresh the MVs. This approach

• Is easy to implement and maintain

• Makes it easy to create a daily log file for auditing

• Sends e-mail when the job has problems or when the database isn’t available Here’s an example of a shell script that contains the logic to refresh an MV:

#!/bin/bash

if [ $# -ne 1 ]; then

echo "Usage: $0 SID"

exit 1

fi

#

MAILX='/bin/mailx'

MAIL_LIST='[email protected]'

ORACLE_SID=$1

jobname=CWP

# See Chapter 2 for details on using a utility

# like oraset to source OS variables

# Source oracle OS variables

. /var/opt/oracle/oraset $ORACLE_SID

date

#