-- 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.

-- Oracle 11g SQL Plan Management (SPM) --

What is SQL Plan Management (SPM)

A big part of the performance of an application depends on its query execution, for the most part the Oracle Optimizer does a fair job without human intervention, however problems may arise for many reasons, until now there has not been guarantee that a plan execution will always change for better, many people have opted to use stored outlines (freeze execution plans) or lock their optimizer statistics, however the risk her is that you may be as well avoiding a more optimal execution plan. 

SQL Plan Management provides a framework for completely transparently controlled  execution plan evolution. It is a mechanism that records and evaluates your SQL plans over time and creates SQL Plan baselines which is composed by a group of plans known to be efficient. The idea is to use this as a preventive method to preserve the performance of SQL statements. With SPM the optimizer automatically managed execution plans and ensures that only known or verified plans are used. In other words when a new plan is found for a SQL Statement, it will not be used until it has been verified by the database to have comparable or better performance than the current plan.

Let me explain that better, to produce this guarantee only accepted (trusted) execution plans will be used; any plan evolution will be tracked and evaluated at a later point in time and only accepted as verified if the new plan causes no runtime change or an improvement of the runtime.

The main advantages of SPM is that it minimizes potential performance regressions that may occur due to event such as new deployments or upgrades. 

SPM Components

A) SQL Plan Baseline Capture

Process of creating SQL plan baselines that represent accepted executions plans for all relevant SQL statements. For it to work you must seed the SQL management base with the execution plans, which will become the new SQL plan baseline. There are 2 ways to achieve this

•  A.1 - Automatic Plan Capture: Automatic plan capture can be switched on by setting the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE (The default value is false), once enabled the SPM repository will be populated with any repeatable SQL statement.
• A.2 - Bulk load execution plans: This is specially useful when a database upgraded from previous a version of Oracle to 11g, or for new deployments. You can use achieve this in three different ways.
• A.2.1 - Using SQL Tuning Sets (STS): Capture the plan for a workload into a STS, then load the plan into the SQL Management Base as SQL Plan baslines using the PL/SQL Procedure DBMS_SPM.LOAD_PLANS_FROM_SQLSET. These are the steps to follow for this approach
• Create a STS that contain the execution plan for each of the SQL Statements.
• Load the STS into a staging table and export the staging table into a flat file.
• import the staging table from a flat file into the Database and unload the STS.
• Use DBMS_SPM.LOAD_PLANS_FROM_SQLSET to load the execution plans into the SQL Management base.
• A.2.2 - Using the plan in the Cursor Cache: Identify the SQL Statements you wish to capture and load them using the procedure DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.
• A.2.3 - Unpack existing SQL plan baselines from as staging table:  If an application is developed in house, you can export a good plan from the test system into production using the following procedure.
• Create a staging table using the DBMS_SPM.CREATE_STGTAB_BASELINE procedure.
• Pack the plan beselines you want to export into the table using DBMS_SPM.PACK_STGTAB_BASLINE function.
• Export the staging table into a flat using the export command or data pump.
• Transfer this flat file to the target system. 
• Import the staging table from the flat file using the import command or data pump
• Unpack the SQL plan baselines from the staging table into the SQL Management base using the DBMS_SPM.UNPACK_STGTAB_BASELINE function.

B) SQL Plan Baseline selection

Process of ensuring that only accepted execution plans are used for statements with a SQL Plan baseline and track all new executions in the plan history for a particular statement. Each time a SQL Statement gets compiled, the optimizer uses the traditional cost-based search method to build the best cost plan. Having the initialization parameter OPTIMIZER_USE_PLAN_BESELINES is set to TRUE (Default value) the optimizer will try to find a matching plan in the SQL Plan baselines. If a match if found then it is used, if not the newly generated plan will be added to the plan history; it will have to be verified before it can be accepted as a SQL Plan baseline. Instead of executing the new plan the optimizer will cost each of the accepted plans for the SQL statement and pick the one with the lower cost.

C) SQL Plan Baseline evolution

Process of evaluating all unverified executions  plans for a given statement in the plan history to be come either accepted or rejected. When a new plan if found, it is added to the plan history as a non-accepted plan that needs to be verified. it is possible to evolve a SQL statement execution plan using DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE using either of the following methods

    - Accept the plan only if it performs better than the existing SQL plan baselines.

    - Accept the plan without doing performance verification

    - Run the performance comparison and generate a report without evolving the new plan.

NOTE: The plan history includes all accepted and unaccepted plans, An unaccepted plan can be unverified (newly found but not verified) or rejected (verified but not found to be a performer ).  The Plan baselines are stored in the plan history in the SQL Management base in the SYSAUX tablespace. It is possible to influence the optimizer's choice of plan by marking a SQL Plan as fixed, Fixed SQL plans baselines tells the optimizer that they are preferred.

How to Enable / Disable SQL Plan Management tracing

You can enable tracing by executing the command below connected as a DBA. This will enable traces for all SPM session which will be located in /diag/rdbms///trace/

exec dbms_spm.configure('spm_tracing',1);

The following command will disable the tracing.

exec dbms_spm.configure('spm_tracing',0);

You can verify if tracing is enable or disables by executing the following command

SELECT parameter_name, parameter_value  FROM sys.smb$config WHERE parameter_name='SPM_TRACING';

In addition you can also enable tracing at the session level as follow

alter session set events 'trace [sql_planmanagement.*]';

-- Basic knowledge about some Network Artifacts --

As it had happened to any other DBAs out there, our roles have been evolving and mine hasn't been the exception. I recently have been more involved with Network architectures, I am sharing this information with you hoping that it can be useful.


VIP (Virtual IP): It is an IP that is not bound to a single physical interface.

LB (Load Balancer): Artifact that allows you to share network requests (following some algorithm) across multiple resources.

GSLB (Global Server Load Balancing): Allows you to route network traffic to multiple locations. It's main objective is to route your connection to the most efficient server available to you. 

GSS (Global Site Selector): This is a Cisco GSLB Product.

GTM (Global Traffic Manager): This is a F5 GSLB Product.


Broadcast: When you need to have more than two nodes see the traffic on the same subnet.

Unicast: Unicast is used when two network nodes need to talk to each other.

Anycast: With anycast you advertise the same network in multiple spots of the Internet, and rely on shortest-path calculations to funnel clients to your multiple locations. As far the network nodes themselves are concerned, they're using a unicast connection to talk to your nodes


Multicast: Is like a broadcast that can cross subnets, but unlike broadcast does not touch all nodes. Nodes have to subscribe to a multicast group to receive information.

------------------------------------------------------------
| TYPE      | ASSOCIATIONS     | SCOPE           | EXAMPLE |
------------------------------------------------------------
| Unicast   | 1 to 1           | Whole network   | HTTP    | 
------------------------------------------------------------
| Broadcast | 1 to Many        | Subnet          | ARP     |
------------------------------------------------------------
| Multicast | One/Many to Many | Defined horizon | SLP     |
------------------------------------------------------------
| Anycast   | Many to Few      | Whole network   | 6to4    |
------------------------------------------------------------

Developers vs. DBAs who will win the battle?

In the last 9 months I have been involved in several battles with the development group, and before I continue let me be clear DBAs are humans and we do mistakes. I have tried several approaches to those issues, but only 1 has provided me the satisfaction and the benefits expected.

"A natural approach is to say, I didn't do anything, the problem is not in the database, it is in your code, and it is not my business since I am not a developer. "

As a DBA we tent to think this way. "I did not change anything in the database, I see no errors in the alert.log therefore is not my problem. "

My question is, for how long are we going to say the same, over and over, even that is technically true. I lose a tremendous amount of time in useless RCA meetings (Root Cause Analysis) where everybody points the finger to everybody else, and people worry more about who to blame it to, instead of how are we going to get it fix for good and avoid future recurrences.

Let me be clear, "No one in a organization, DBA nor Developers wants for outages or problems to happen", if there were no problems, we will have no jobs, therefore problems will happen, Focus on learning from the problems and avoid them in the future.

The approach that has worked for me, and have provided the satisfaction and the benefits expected is to research about where is the problem, even that it may not database related. Yes don't be afraid to put your nose into somebody's else business (I know it does not sound right but you know what I mean with it ;-) ), you are getting affected so they need to understand clearly your interest.

In the past I have found that some of the problems are:

- The developers are been push by the project managers into meeting unrealistic and
unnecessary deadlines, and they sacrifice quality for meeting the deadline, in other words
if it works is good enough, I don't care the resources it will consume or how long will take to
run.

- Very inefficient spaghetti code produced for a constant change in the scope of the project.

- Lack of coding standards, and in this case the DBA produced coding standards for them and
the DBA is the watcher that those standards are met. (Yes, I know it is not in our job
description, but it works).

- The developer does not know how to code, they just know a reduced set of instructions and
try to program with whatever they know, therefore producing tons on inefficient code. For
this case we are providing the developers with a database architecture workshop and a SQL
and SQL tuning workshop. In other words I have to help them so they can help me. (Yes,
another one that is not in your job description).

Conclusion: Don't be afraid to think out of the box, rather than complaining about the problems
try to find out where are the problems (even if they are outside your
boundaries) and try to be part of the solution.

-- How to compare the data in two tables using hash values --

We recently had the need to verify if there has been changes on a table since a specific date (for data integrity purposes), here is an easy and effective way to accomplish that.

For this example we create a table called X with the names of my family.


SQL> create table x (x1 number, x2 varchar2(10));

Table created.

SQL> insert into x values (&1,'&2');
Enter value for 1: 1
Enter value for 2: Rafael
old   1: insert into x values (&1,'&2')
new   1: insert into x values (1,'Rafael')

1 row created.

SQL> /
Enter value for 1: 2
Enter value for 2: Kristina
old   1: insert into x values (&1,'&2')
new   1: insert into x values (2,'Kristina')

1 row created.

SQL> /
Enter value for 1: 3
Enter value for 2: Nathan
old   1: insert into x values (&1,'&2')
new   1: insert into x values (3,'Nathan')

1 row created.

SQL> /
Enter value for 1: 4
Enter value for 2: Andrea
old   1: insert into x values (&1,'&2')
new   1: insert into x values (4,'Andrea')

1 row created.

SQL> /
Enter value for 1: 5
Enter value for 2: Samantha
old   1: insert into x values (&1,'&2')
new   1: insert into x values (5,'Samantha')

1 row created.

SQL> commit;

Commit complete.
SQL> select * from x;

        X1 X2
---------- ----------
         1 Rafael
         2 Kristina
         3 Nathan
         4 Andrea
         5 Samantha

We are going to use the Oracle hash function along with the sum function to calculate the hash valued of the aggregated data on the table.


SQL> select sum(ora_hash(x1||'|'||x2) from x;

SUM(ORA_HASH(X1||'|'||X2)
----------------------------------------
                                  108834




The number above represents the sum of the aggregation of all the column and all the rows for table x, now let's see what happen when we change one of the rows.

SQL> update x set x2='SAMANTHA' where x2 like 'Samantha';

1 row updated.

SQL> commit;

Commit complete.




Now we apply the same hash function.

SQL> /

SUM(ORA_HASH(X1||'|'||X2)
----------------------------------------
                                  112024

It is also a good idea using something like CRC-16 (Cyclic Redundancy Check), but that is a matter of preference. As you can see above the value changed, you can use dynamic SQL to build the sql statement for all the tables in a schema and store the hash value on a table, that will make it easier at the time you need to compare the data on each table.

Cheers!.

-- More about Oracle 11gR2 Single Client Access Name (SCAN) --

The introduction of SCAN have caused a lot of confusion among network administrators, architects and DBAs, let's see if we can keep this to basis to make it less confusing.  

Why do we need this?

 It provides a single name for clients to access Oracle Databases, this is specially true for database clusters, the reason is because you do not have to update later on the client information if you change the cluster configuration. SCAN gets configured when you install Oracle Grid Infrastructure (New name for Clusterware and ASM together), During the installation you will be asked for a SCAN name, ideally you want to define the SCAN in your DNS, with this method your network administrator needs to create a virtual hostname that resolves to 3 IP addresses using a round robin algorithm. The reason behind why 3 IP Addresses is related to load balancing and HA requirements regardless of the number of servers in your cluster. Each IP must be on the same subnet as the public network in your database cluster.

 NOTE: The 3 IPs above should not be assigned to a network interface, Oracle Clusterware will take care of it. Your DNS entry should looks like

blog-scan-oraprofessionals.com   IN A 192.168.1.102 
                                                      IN A 192.168.1.103 
                                                      IN A 192.168.1.104 

 How it works?

Clients connecting to the database using the SCAN will receive 3 IP, the client will try to connect using the list of IPs it received, if the client receives an error , it will try the other addresses before it returns an error. When the SCAN listener received a connection request, it checks for the least loaded instance providing the requested service, then it re-directs the connection to the local listener on the node that have the service available. Now as you can see by the explanation above, your client connection information won't be affected by the configuration of your cluster.