Beware of (fast) Materialized Views created with DBLinks

Today I was looking at a particular instance's MLOGs (logs created by Materialized Views with REFRESH FAST ON DEMAND) and noticed that the source MVs were not located on this server at all. These MVs were built on another instance referencing tables on this server by use of DBLinks.


Recap of MLOGs (or snapshot logs):

When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must re-execute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.

To fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.

Source: 
http://www.morganslibrary.org/reference/materialized_views.html

When I used this query to look at the underlying MVs, all of them had not had a refresh since 2009:

Select

a.OWNER,a.MASTER,a.MVIEW_LAST_REFRESH_TIME,m.OWNER,m.NAME,m.MVIEW_SITE

From dba_base_table_mviews a,dba_REGISTERED_MVIEWS m

Where m.MVIEW_ID=a.MVIEW_ID

Since these were never analyzed, I couldn't initially determine how much data they were holding so I analyzed these MLOG tables and they summed to about 80 million rows of data. 

With that information, and the knowledge that these MVs were no longer needed, I dropped the MVs on the remote instance and reanalyzed the MLOGs again. Most all the rows were removed! Great right? Well, now some cleanup on these tables needed to take place to give back this wasted space to the Tablespace.

Being on 10g, the "shrink" commands were used:

alter materialized view log on < table > enable row movement;

alter materialized view log on < table > shrink space compact;

alter materialized view log on < table > shrink space;

alter materialized view log on < table > disable row movement;

alter materialized view log on < table > deallocate unused;

Now onto what remaining MVs are requiring the MLOGs?

After verifying that no MVs existed referencing these Master tables, I dropped the snapshot logs since they were still being written to using:

DROP MATERIALIZED VIEW LOG ON < table >;

SOA Composites Dashboard

So I wanted to create a simple SSRS report, and have it emailed on a regular basis that showed some basic information about our running Composites instead of clicking into each Group/Composite in SOA Enterprise Manager (EM).

Here is the query I initially came up with that seems to work pretty well:


SELECT SOAGroup,
         SOAComposite,
         SOAVersion,
         max(id) as composite_id,
         decode(min(state), 0, 'Running',
         1, 'Completed',
         2, 'Running with faults',         
         3, 'Completed with faults',
         4, 'Running with recovery required',
         5, 'Completed with recovery required',
         6, 'Running with faults and recovery required',
         7, 'Completed with faults and recovery required',
         8, 'Running with suspended',
         9, 'Completed with suspended',
         10,'Running with faults and suspended',
         11,'Completed with faults and suspended',
         12,'Running with recovery required and suspended',
         13,'Completed with recovery required and suspended',
         14,'Running with faults, recovery required, and suspended',
         15,'Completed with faults, recovery required, and suspended',
         16,'Running with terminated',
         17,'Completed with terminated',
         18,'Running with faults and terminated',
         19,'Completed with faults and terminated',
         20,'Running with recovery required and terminated',
         21,'Completed with recovery required and terminated',
         22,'Running with faults, recovery required, and terminated',
         23,'Completed with faults, recovery required, and terminated',
         24,'Running with suspended and terminated',
         25,'Completed with suspended and terminated',
         26,'Running with faulted, suspended, and terminated',
         27,'Completed with faulted, suspended, and terminated',
         28,'Running with recovery required, suspended, and terminated',
         29,'Completed with recovery required, suspended, and terminated',
         30,'Running with faulted, recovery required, suspended, and terminated',
         31,'Completed with faulted, recovery required, suspended, and terminated',
         32,'UNKNOWN',
         64,'???????') run_state, 
         MAX (Created_time) AS last_run_time,
         SYSDATE - MAX (Created_time) AS Elapsed,
         'http://soaprod.ocint.com:7001/em/faces/ai/soa/messageFlow?target=/Farm_base_domain/base_domain/soa_server1/'
         || SOAGROUP ||'/' || SOACOMPOSITE || '%20[' || SOAVERSION || ']&type=oracle_soa_composite&soaContext='
         || SOAGROUP || '/' || SOACOMPOSITE || '!' || SOAVERSION || '/' || max(id) AS URL       
    FROM (SELECT SUBSTR (composite_dn, 1, INSTR (composite_dn, '/') - 1) AS SOAGroup,
                 SUBSTR (composite_dn, INSTR (composite_dn, '/') + 1, (INSTR (composite_dn, '!') - INSTR (composite_dn, '/') - 1)) AS SOAComposite,
                 SUBSTR (composite_dn, INSTR (composite_dn, '!') + 1, (INSTR (composite_dn, '*') - INSTR (composite_dn, '!') - 1)) AS SOAVersion,
                 Source_Name,
                 Created_time,
                 id,
                 state
            FROM prd_soainfra.composite_instance)
GROUP BY SOAGroup, SOAComposite, SOAVersion
ORDER BY SOAGroup, SOAComposite, SOAVersion

I then created a Data Source to the SOA (PRD_SOAINFRA user) Server and scheduled a report with email delivery. The Composite ID column was hyperlinked using the URL column (not displayed) in SSRS. This allows you to click and be taken directly to the Composite in EM once logged in.

Database Resource Manager - CREATE_SIMPLE_PLAN

How to use Oracle's CREATE_SIMPLE_PLAN to implement a Database Resource Manager (DRM) in 10g.

You can use the following as a easy way to segregate certain user connections based on CLIENT_PROGRAM. For example: Oracle Discoverer (client and web-based) and Oracle SQL Developer.

This plan will demonstrate how to put sessions using these programs in a lower priority queue while maintaining the rest of your users in the "default" queue. SYS and SYSTEM users are in the highest queue.

Step 1:

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

  DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (

    attribute      => DBMS_RESOURCE_MANAGER.client_program,

    value          => 'DIS51USR.EXE',  

    consumer_group => 'LOW_GROUP');

  DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (

    attribute      => DBMS_RESOURCE_MANAGER.client_program,

    value          => 'DIS51WS@OCTEST.OCINT.COM (TNS V1-V3)',

    consumer_group => 'LOW_GROUP');

  DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (

    attribute      => DBMS_RESOURCE_MANAGER.client_program,

    value          => 'SQL DEVELOPER',

    consumer_group => 'LOW_GROUP');

END;

Step 2:

EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

Step 3:
exec dbms_resource_manager.submit_pending_area();

Now let's validate what was created using the following SQL.

Validate the Plan creation:

SELECT

     plan

    ,num_plan_directives

    ,cpu_method

    ,active_sess_pool_mth

    ,parallel_degree_limit_mth

    ,queueing_mth

    ,status

    ,mandatory

  FROM dba_rsrc_plans;

Groups within a Plan:SELECT     plan

    ,group_or_subplan

    ,type

    ,cpu_p1

    ,cpu_p2

    ,cpu_p3

    ,status

  FROM dba_rsrc_plan_directives

 WHERE plan = 'XXOC_SIMPLE_PLAN1';

User Mappings:

SELECT

     initial_rsrc_consumer_group,

     username

   FROM dba_users

 ORDER BY 1,2;

Special User Mappings (showing client_program):

SELECT *

FROM dba_rsrc_group_mappings;

Ready to Activate your Plan?

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = XXOC_SIMPLE_PLAN1;

(If RAC - run on each node. This means you can have different plans on different Nodes.)

View active plans:

SELECT * FROM V$RSRC_PLAN;

Monitor your Plans activity:

SELECT * FROM V$RSRC_CONSUMER_GROUP;

To allow for Group Switching between Default_Consumer_Group and Low_Group you can do the following:



BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'XXOC_SIMPLE_PLAN1',
group_or_subplan => 'DEFAULT_CONSUMER_GROUP',
new_comment => ' ',
new_cpu_p1 => 0, new_cpu_p2 => 80, new_cpu_p3 => 0, new_cpu_p4 => 0,
new_cpu_p5 => 0, new_cpu_p6 => 0, new_cpu_p7 => 0, new_cpu_p8 => 0,
new_parallel_degree_limit_p1 => -1,
new_active_sess_pool_p1 => -1,
new_queueing_p1 => -1,
new_switch_group => 'LOW_GROUP',
new_switch_time => -1,
new_switch_estimate => true,
new_max_est_exec_time => -1,
new_undo_pool => -1
);
dbms_resource_manager.submit_pending_area();
END;