SPM - SQL PLAN MANAGER

As a DBA some I feel that Oracle is living thing ! for example query performance. Out of no where a gentle looking SQL statement may pop up your top time consuming query list. A sql query which gives efficient performance in 3 months ago (Or even week ago), now giving horrible performance that threatening you cool database performance :)

Yes, we all DBA know that a gentle statement can screwed up due to
- Re-guthering optimizer statistics
- Change in optimizes parameters
- Change in Schema? Object structure (i.e, metadata definition)


Though Optimizer always try to choose list expensive plan for each SQL statement execution, Some time It (optimizer) choose expensive plans and raise annoying situations for peace loving DBAs :)

Well Oracle 11g come up with a new feature that can handle such weird situation. The simplest solution we can think is a mechanism that only allow trusted SQL plans to be executed and reject untrusted plans. O Yes Oracle 11g gives us a manager named SPM that do the same thing.

SQL PLAN MANAGER (SPM) has three major components

• SQL Plan Baseline Capture - Create SQL Plan Baseline for trusted (accepted) SQL plans.
• SQL Plan Baseline Selection - Conform that only accepted SQl plans are used when an SQL statement is executed.
• SQL Plan Baseline Evolution - Evaluated all SQL Plans (old & new) for each SQL statement. The new/old plan will only be excepted if it gives better or equal performance compared with existing trusted plan otherwise rejected. (If no trusted plan exist then current plan will be accepted)
  

To enable this cool feature we just do the followings

ALTER SYSTEM set optimizer_capture_sql_plan_baselines= TRUE scope=both; [Default value is false]
ALTER SYSTEM set optimizer_user_sql_plan_baselines= TRUE scope=both; [Default value is true, so just check the parameter value]

You are done ! Keep an eye in DBA_SQL_PLAN_BASELINES view and enjoying SPM's magic

TABLE_EXISTS_ACTION

Those who are familiar with oracle data pump may know very well about TABLE_EXISTS_ACTION import (impdp) parameter.

TABLE_EXISTS_ACTION = {SKIP | APPEND | TRUNCATE | REPLACE}

This parameter is used when you import a table which is already exists in import schema. The default value is 'SKIP', so if you not use this parameter and impdp found that the table which to be imported is already exist then impdp skip this table from import list.

Now you may interested about rest of the three values-

APPEND - The import will be done if the table does not have any Primary key or Unique key constraints. If such constraint exists then you need to ensure that append operation does not violate Primary key or Unique key constraints (that means it does not occur data duplication).

TRUNCATE - If the table is not a parent table ( i.e, No other table references it by creating foreign key constraint) then it truncate the existing data and load data from dump file. Otherwise data will not be loaded.

REPLACE - This is the most tricky value of TABLE_EXISTS_ACTION parameter. If the importing table is a parent table ( i.e, other table references it by creating foreign key constraint ) then the foreign key will be deleted from child table. All existing data will be replaced with imported data.