Creating a Complete Refreshable Materialized View

More on Materialized Views

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query

Creating a Complete Refreshable Materialized View
 
Before you can create a Materialized View you need CREATE MATERIALIZED VIEW and CREATE TABLE system privileges.

Appropriate for Materialized View’s where the base tables have a significant amount of row changes. Also required when Fast Refresh is not possible.

create materialized view EXPERIMENTS_MV

refresh

complete

on demand

as

select USERNAME, EXP_NAME, sum(MINUTES) AS MINUTES, count(*) AS TOTAL

from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B

where A.EXPID = B.EXPID

group by USERNAME,EXP_NAME

order by USERNAME,EXP_NAME;

Query user_mviews

select mview_name

,refresh_method

,refresh_mode

,fast_refreshable

from user_mviews

where mview_name = ‘EXPERIMENTS_MV’;

MVIEW_NAME        REFRESH_METHOD    REFRESH_MODE      FAST_REFRESHABLE

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

EXPERIMENTS_MV    COMPLETE          DEMAND            NO

How to refresh a Materialized View:

Use the refresh procedure of the DBMS_MVIEWpackage.

Passing in one parameter:

(The refresh method will be the default refresh method for

that Materialized View)

The name of the Materialized View and the refresh method.

SQL> exec dbms_mview.refresh(' EXPERIMENTS_MV');

Passing in two parameters:

The name of the Materialized View

The refresh method.

SQL> exec dbms_mview.refresh(' EXPERIMENTS_MV','C');

You can include more than one Materialized View name

with a comma delimited string in quotes.

For example:

SQL> exec dbms_mview.refresh('MV_1, MV_2,MV_3’,'CFC');

If you exclude the refresh type parameter then the default

refresh method is used. You will get an error if you try to

use F for Fast Refresh and your Materialized View is not

set up as a Fast Refreshable Materialized View.

Refresh Types:

C = Complete

F = Fast

? = Force

SQL> exec dbms_mview.refresh(' EXPERIMENTS_MV','?');

Force: Performs a FAST if possible, otherwise it performs a

complete refresh.

More on Materialized Views

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query

What is a Materialized View?

More on Materialized Views
  Materialized Views

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query

What is a Materialized View?

Let’s start by looking at the differences between a View and a Materialize View.

A View is a logical table based on one or more tables or views.

Logical table meaning that the query is run at the time you select from the view.

A Materialized View is like a regular view except that the initial query has already been run and the results are actually stored in a physical table. When you select from a Materialized View you are only querying the underlying table where the results are already stored.

View – SQL query executed when you query the view.

Materialized View – Select directly from a base table where the SQL query results are already stored.

Materialized Views VS. Row/Result Cache

• Materialized View – The results stored in a table. Refreshed completely or only the DML transactions to the base table on commit or at periodic intervals.
• Result Cache – The results stored in memory.  Refreshed anytime the underlying data is changed.

 Complete Refresh VS. Fast Refresh

• Complete Refresh simply reloads the entire Materialized View from scratch.
• Fast Refresh, once created, only applies the DML changes to the Materialized View.

 Materialized View Log

• Fast Refresh requires a Materialized View Log be created prior to creating the Materialized View. The Materialized View Log is the table that stores all the DML changes that take place on the base table for the Materialized View. The Materialized View Log is self maintained and once the records are applied to the Materialized View they are automatically purged from the Materialized View log.

Refresh Mode

On Demand, On Commit, or Never.

• On Demand:  Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package REFRESH.

• On Commit: Refresh occurs automatically when a transaction that modified one of the Materialized View's base tables commits.  

For example, let’s say you have query that provides a sum of minutes and totals by user and experiment. You have an application that is running this query and because of the sum and aggregate it continues to take longer as time goes by.

select USERNAME, EXP_NAME, sum(MINUTES) AS MINUTES, count(*) AS TOTAL

from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B

where A.EXPID = B.EXPID

group by USERNAME,EXP_NAME

order by USERNAME,EXP_NAME;

Let’s transform this query into a table that can be queried.

CREATE TABLE EXPERIMENTS

AS

select USERNAME, EXP_NAME, sum(MINUTES) AS MINUTES, count(*) AS TOTAL

from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B

where A.EXPID = B.EXPID

group by USERNAME,EXP_NAME

order by USERNAME,EXP_NAME;

You put into place a nightly process to truncate and refresh it each day.

TRUNCATE TABLE EXPERIMENTS;

INSERT INTO EXPERIMENTS...

Now each day the users have sub-second query results by selecting from EXPERIMENTS.  The only problem is that the data is only up to date through the prior day.

This is similar to a Materialized View, but a Materialized View can not only be automated, it can greatly enhance this process.  A Materialized View has additional features, many which we will discuss, that a regular View does not have. 



Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query

Materialized Views

Materialized View technology has been available since Oracle version 7. Originally it was called snapshot technology and soon became known as Materialized View Replication. There are several reasons you might want to use Materialized Views in your environment. First, it’s a great way to offload data to a data warehouse or a separate reporting database.  Also you can use them to improve query performance by periodically computing and storing the results of aggregated data and complex queries. 

The following is based on my white paper for a session I am presenting at Collaborate in Denver, Co the week of April 7th. 

This blog is broken into several specific  topics on Materialized Views.

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

Creating a Fast-Refreshable Materialized View based on a Complex Query

Purging or deleting audit trail files in 11g

Whether you are using audit_trail = OS or DB there will be audit files accumulating in the audit_file_dest. Much more if audit_trail parameter is set to DB. This is the script I use to delete the files in the adump directory.

More on Oracle audit files.

Where are these audit files?

SQL> select value from v$parameter where name = 'audit_file_dest';

VALUE

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

/app/oracle/admin/db251/adump

I run this file through corntab every night.
It tells me 

• how many audit trail files there are
• how many audit trail files are going to be deleted
• After purging, how many audit files are left

 Create a file in your common scripts or desired directory.

vi delete_aud_files.sh

Make it executable.

chmod 770 delete_aud_files.sh

Add it to your crontab to run every day.

## Delete Audit files 8:30pm every night

30 20 * * * /oracle/scripts/cleanup/delete_aud_files.sh

This is an example of the script I use.

NOTE: items in RED should be editing to your environment.

#!/bin/sh
ORACLE_BASE=/app/oracle; export ORACLE_BASE
ORACLE_SID=db251; export ORACLE_SID
DAY_AFTER=3; export DAY_AFTER
OUTF=/oracle/scripts/cleanup/cleanup_audit_files.log ; export OUTF
#
echo `date '+%m/%d/%y %A %X'` > ${OUTF}
echo >> ${OUTF}
echo "SCRIPT NAME:   $0" >> ${OUTF}
echo "SERVER:        "`uname -n` >> ${OUTF}
echo "KEEP DURATION: $DAY_AFTER days" >> ${OUTF}
echo >> ${OUTF}
echo >> ${OUTF}
#
echo "Delete audit files owned by oracle..." >> ${OUTF}
echo >> ${OUTF}
echo >> ${OUTF}

# change directory to the audit file directory
cd ${ORACLE_BASE}/admin/${ORACLE_SID}/adump
echo >> ${OUTF}
echo "Deleting from directory:" >> ${OUTF}
echo "[${ORACLE_BASE}/admin/${ORACLE_SID}/adump]" >> ${OUTF}
echo >> ${OUTF}
echo "The total number of files in directory is:" >> ${OUTF}

# output the total count of audit files to outfile
ls -al | wc -l >> ${OUTF}
echo >> ${OUTF}
echo "Total number of files to be deleted is:" >> ${OUTF}

# output the total number of audit files that will be deleted
find . -maxdepth 1 -type f -mtime +$DAY_AFTER -name "*.aud" | wc -l >> ${OUTF}
echo >> ${OUTF}

# delete the audit files
find . -maxdepth 1 -type f -mtime +$DAY_AFTER -name "*.aud" | xargs rm
echo "Files successfully deleted." >> ${OUTF}
echo "Total number of files remaining:" >> ${OUTF}

# output the remaining count of audit files in the adump directory
ls -al | wc -l >> ${OUTF}
echo >> ${OUTF}
echo >> "Complete with delete." >> ${OUTF}
#
# Now email results
echo >> $OUTF
echo `date '+%m/%d/%y %A %X'` >> $OUTF
cat $OUTF | /bin/mailx -s "`uname -n` : delete old Oracle audit files" my_email@company.com
exit

This is what the email output looks like:

06/04/12 Monday 20:30:01

SCRIPT NAME:   /oracle/scripts/cleanup/delete_aud_files.sh
SERVER:        neutron
KEEP DURATION: 3 days


Delete audit files owned by oracle...

Deleting from directory:
[/app/oracle/admin/db251/adump]

The total number of files in directory is:
38677

Total number of files to be deleted is:
16589

Files successfully deleted.
Total number of files remaining:
22094

06/04/12 Monday 20:30:03

Oracle 11g Audit Trail

If you don't care about auditing the DML in your database then you will agree with me that Oracle should not have made auditing mandatory. Yes you can set this parameter to NONE, but you will still get some level of auditing. This is only a brief overview and I am not going into too much depth here.

The possible values for the audit_trail parameter are
 SQL> show parameters audit_trail

DB
DB_EXTENDED
OS
XML
NONE

If the value is DB you should set up a cron script to purge these files:
Script to purge audit trail files in Oracle 11g.

Where are the audit files

SQL> select value from v$parameter where name = 'audit_file_dest';

VALUE

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

/app/oracle/admin/db251/adump


To update this parameter
SQL> alter system set audit_trail=db scope=spfile;
SQL> shutdown immediate;
SQL>startup;
SQL> show parameter audit_trail;
AUDIT_TRAIL TYPE   VALUE
-----------------   ------    -------
audit_trail           string   DB

The default is OS. If the setting is OS then you should also periodically delete rows from the AUD$ table.

SQL> select count(*) from aud$;

COUNT(*)

----------

344273

SQL> select count(*) from aud$ where ntimestamp# > sysdate - 21;

 COUNT(*)

----------

53550

SQL> delete from aud$ where ntimestamp# > sysdate - 21;

53550 rows deleted.

Clear alerts in Oracle Enterprise Manager OEM

I am using 11g, but this work in 10g as well.

I had this annoying alert appearing under my alert tab on OEM for some time. A failed job, yes I knew it failed and fixed it that day. I waited for the alert to disappear, but alas, it did not. I acknowledged it and thought that was the end of that. Well it's been hanging around for some time and when I click on it the alert it take me to a jobs page with no information on how to clear.

I started hunting around for information on how to clear alerts that have no purpose in OEM and I found a lot of posting that said to log in as sysman and query the mgmt_current_severity and then use em_severity.delete_current_severity to remove the desired alert.

Okay that sounds good, but then I found a post by a Said Ahmed. He posted the following query which I found to be very useful so here it is.

First log in as SYSMAN or your repository owner.

select t.target_name
, t.target_type
, collection_timestamp
, message
, 'exec em_severity.delete_current_severity(''' ||
t.target_guid || ''',''' ||
metric_guid || ''',''' ||
key_value || ''')' em_severity
from sysman.mgmt_targets t
inner join
sysman.mgmt_current_severity s
on
t.target_guid = s.target_guid;


This will give you information about all the alerts in OEM. Copy the result from the em_severity column that you want removed. Run it exactly as it appears. Be sure you are logged in as sysman.

For example:
SQL> exec em_severity.delete_current_severity('stuff','moretuff','SCHEMA')

Before deletion

After deletion