-- Can DBMS_JOBS Failover when using instance affinity? --

One of my customers recently approached me asking this question, first let me explain what is instance affinity for DBMS_JOBS.

Let's assume you have 3 jobs you need to run and you are using Oracle RAC, you have 3 instances (1 Database), and you want to be able to balance the load (not using SQL Net load balancing), you could use node affinity to ensure that a particular job only runs from one node.

DBMS_JOB.INSTANCE (JOB IN BINERY_INTEGER,
           INSTANCE                     IN BINARY_INTEGER,
           FORCE                          IN BOOLEAN DEFAULT FALSE);

In the above syntax the INSTANCE parameter represent the instance number where you want the job to run, the FORCE when FALSE (Default) and having INSTANCE > 0 will alter the job affinity for the specified instance assuming that instance is running, if INSTANCE = 0 then the job will be executed by any instance, If FORCE = TRUE then the job will be altered regardless of the state of the instance as long as the INSTANCE parameter is not negative or null.

Leaving that clear the answer is no, DBMS_JOB node affinity will not failover to a surviving instance during an Oracle RAC instance failover, it will simple not run while the instance is down.

People have suggested that Oracle should enhance DBMS_JOB to achieve that function, the answer is that they did, the DBMS_SCHEDULER uses job classes that you can associate to an specific service, and as long as you failover the service to the surviving instance the job will be executed there.

BEGIN
DBMS_SCHEDULER.CREATE_J0B_CLASS(
    JOB_CLASS_NAME => '[CLASS_NAME]',
    SERVICE => '[SERVICE_NAME]');
END;
/

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB(
     JOB_NAME => '[JOB_NAME]',
     PROGRAM_NAME => '[PROGRAM_NAME]',
     START_DATE => '[START_DATE]',
     JOB_CLASS => '[CLASS_NAME]',
     COMMENTS => '[COMMENTS]',
     AUTO_DROP => FALSE,
     ENABLES => TRUE);
END;

In this way you can distribute job across RAC instances using job classes and services.

-- Oracle Materialized Views real case Performance Improvements --

Recently I got pulled into a case where our customer was complaining that Materialized Views were taking too long to refresh, upon research I found the following:

• Materialized Views are located on the same server as the source tables.
• They use the Materialized views to do joins of tables and views for reporting purposes.
• They have an Active Data Guard Database on the DR Data Center.
• They created 3 refresh groups in an attempt to improve performance
• They are not using the scheduler but DBA_JOBS.
• They do complete refresh every 2 hours.
• Their database is a 3 node RAC cluster.

These were my recommendations:

• Do you really need Materialized views, or a view using the proper partitioning and parallel query will do the job, never underestimate the power of Parallel Query using partitioned tables and having the right indexes in place.
• Use your Active Data Guard (ADG) for reporting purposes.
• Favor Fast Refresh over Complete refresh.
• Use the Oracle Scheduler rather than DBA_JOBS.
• Use node affinity so you do not tax a single server consuming a bunch of resources during the refresh.

I know this is just common sense, but I though about share it with you as there may be some other people having similar issues.