automatic DOP: skipped because of IO calibrate statistics are missing

Because I/O calibration is not run to gather the required statistics. I/O calibration statistics can be gathered with the PL/SQL DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure.

SQL> select status from V$IO_CALIBRATION_STATUS;

STATUS
-------------
NOT AVAILABLE

then used DBMS_RESOURCE_MANAGER.CALIBRATE_IO Procedure.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
--DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (28, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || mbps);
end;
/

max_iops = 5944
latency = 9
max_mbps = 75

PL/SQL procedure successfully completed.

if using DBMS_RESOURCE_MANAGER.CALIBRATE_IO and error ORA-56708: Could not find any datafiles with asynchronous i/o capability
Need to enable asynch I/O, set two values in the init.ora file.

disk_asynch_io = true
filesystemio_options = asynch

After used CALIBRATE_IO, then

SQL> select status from V$IO_CALIBRATION_STATUS;

STATUS
-------------
READY

select name,asynch_io from v$datafile f,v$iostat_file i
where f.file#=i.file_no
and (filetype_name='Data File' or filetype_name='Temp File')

Gather_system_stats

The dbms_stats.gather_system_stats procedure is especially useful for multi-mode Oracle shops that run OLTP during the day and DSS at night. This is the typical mode we have and it becomes imperative to have these stats good.

We can gather these stats in three different ways:

1. During heavy workload.

execute dbms_stats.gather_system_stats('Start');
-- one hour delay during high workload
execute dbms_stats.gather_system_stats('Stop');

2. During no workload.

exec DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD')

This actually gathers the IOTFRSPEED and IOSEEKTIM values in addition to CPUSPEEDNW rather than using the default values of 4096 and 10.

3. Setting your own stats.

This is important when testing which stats will be good and then setting them manually.

begin
    dbms_stats.delete_system_stats;
    dbms_stats.set_system_stats('MBRC',        64);
    dbms_stats.set_system_stats('MREADTIM',    10);
    dbms_stats.set_system_stats('SREADTIM',     5);
    dbms_stats.set_system_stats('CPUSPEED',  2000);
    dbms_stats.set_system_stats('MAXTHR',  262144);
    dbms_stats.set_system_stats('SLAVETHR', 65536);
    dbms_stats.set_system_stats('SLAVETHR', 47000);
    dbms_stats.set_system_stats('SLAVETHR', 16384);
end;
/

The output from dbms_stats.gather_system_stats is stored in the aux_stats$ table and you can query it as follows:

select pname, pval1 from sys.aux_stats$;

Here are the data items collected by dbms_stats.gather_system_stats:

No Workload (NW) stats:

• CPUSPEEDNW - CPU speed
• IOSEEKTIM - The I/O seek time in milliseconds
• IOTFRSPEED - I/O transfer speed in milliseconds

Workload-related stats:

• SREADTIM  - Single block read time in milliseconds
• MREADTIM - Multiblock read time in ms
• CPUSPEED - CPU speed
• MBRC - Average blocks read per multiblock read (see db_file_multiblock_read_count)
• MAXTHR - Maximum I/O throughput (for OPQ only)
• SLAVETHR - OPQ Factotum (slave) throughput (OPQ only)  

Sql plan management practice

--To check if optimizer is using baseline or not
select * from v$parameter where name='optimizer_use_sql_plan_baselines'

--Load baseline from cursor cache
--specific plan hash value also givenas it was best plan identified
--Fixed baseline so that no new plan baselines will be captured
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '1b26c3w3un3a4',
    plan_hash_value => '2168508692',
    fixed => 'YES');
END;
/

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '8tx67bgzqwnvc',
    plan_hash_value => '3455307351',
    fixed => 'YES');
END;
/

--to check query etc.
SELECT plan_hash_value FROM V$SQL WHERE SQL_ID='8tx67bgzqwnvc';

--to check existing plan baselines and handle
SELECT * FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%INSERT INTO sim_res%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';


--Check plan
set serveroutput on size unlimited
set line 1000
set pagesize 1000
select * from table(dbms_xplan.display_CURSOR('8tx67bgzqwnvc', 0, 'ADVANCED'));

--change parameter autodrop
SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_830a045284cdac6c',
    plan_name       => 'SQL_PLAN_862h4aa2cvb3c89f28544',
    attribute_name  => 'fixed',
    attribute_value => 'NO');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_8da397267248b10a',
    plan_name       => 'SQL_PLAN_8v8wr4tt4jc8a1931a7ad',
    attribute_name  => 'autopurge',
    attribute_value => 'NO');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/


--To drop baselines
SET SERVEROUTPUT ON
DECLARE
  l_plans_dropped  PLS_INTEGER;
BEGIN
  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle => NULL,
    plan_name  => 'SQL_PLAN_8v8wr4tt4jc8a1931a7ad');
   
  DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/

Sql plan management

SQL plan management provides a mechanism for maintaining consistent SQL performance regardless of changes in optimizer version, optimizer statistics, schema changes, system settings and SQL profile creation.

More theory can be found here:
https://oracle-base.com/articles/11g/sql-plan-management-11gr1

Steps to configure sql plan management:

Check if Automatic Plan Capture is enabled or not. IT IS NOT NECESSARY ALSWAY TO HAVE TO ENABLED AND CAREFULL TESTING NEEDS TO BE DONE TO ENABLE IT IN PROD.

SHOW PARAMETER OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

If not already enabled, enable it.

ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;

The use of SQL plan baselines is controlled by the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter, which is set to TRUE by defaul.

show parameter optimizer_use_sql_plan_baselines;

Access to the DBMS_SPM package requires the ADMINISTER SQL MANAGEMENT OBJECT privilege.

If it is not available, grant it.
grant ADMINISTER SQL MANAGEMENT OBJECT to SCOTT;



Manual Plan Loading:
LOAD_PLANS_FROM_CURSOR_CACHE:
The LOAD_PLANS_FROM_CURSOR_CACHE functions allow SQL statements to be loaded from the cursor cache.
There are four overloads, allowing statements to be identified by a number of criteria, including: SQL_ID, SQL_TEXT, PARSING_SCHEMA_NAME, MODULE and ACTION. Manually loaded plan are by default accpeted
The following example identifies the SQL statement using the SQL_ID.
SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => 'gat6z1bc6nc2d');
   
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/

Note that once this plan loaded it will automatically create baseline if not already present or add it to existing baseline. Manual loading automatically enables and accepts plan.
Now even if this plan is sub-optimal query will continue to use this plan even if better plan exists. To make use of better plan we either need to evolve current plan or accept other plans if automatic plan capture is enabled.

Assume that above baseline uses FTS and latter index added on the column and index scan is optimal. So to make query use better plan we have to evolve this plan.

We can check the baseline created in DBA_SQL_PLAN_BASELINES view.

SELECT sql_handle, plan_name, enabled, accepted
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%spm_test_tab%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';

To evolve sql baseline to accept new plan generated automatically;

SET LONG 10000
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9')
FROM   dual;

LOAD_PLANS_FROM_SQLSET:
alternatively you can load plan from sql tunning set as well.

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(
    sqlset_name => 'my_sqlset');
END;
/

ALTERING PLAN BASELINES:

The ALTER_SQL_PLAN_BASELINE function allows the following attributes of a specific plan, or all plans within a baseline to be altered:

enabled (YES/NO) : If YES, the plan is available for the optimizer if it is also marked as accepted.
fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.
autopurge (YES/NO) : If YES, the SQL plan baseline is purged automatically if it is not used for a period of time.
plan_name : Used to amend the SQL plan name, up to a maximum of 30 character.
description : Used to amend the SQL plan description, up to a maximum of 30 character.
The following shows a specific plan being marked as fixed.

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SYS_SQL_7b76323ad90440b9',
    plan_name       => 'SYS_SQL_PLAN_d90440b9ed3324c0',
    attribute_name  => 'fixed',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

SQL MANAGEMENT BASE:

We can check parameters those can be configured by dbms_spm package for baseline by using below query;
SELECT parameter_name, parameter_value
FROM   dba_sql_management_config;

how to set these parameters:

BEGIN
  DBMS_SPM.configure('space_budget_percent', 11);
  DBMS_SPM.configure('plan_retention_weeks', 54);
END;
/

TRANSFERRING SQL PLAN BASELINES:

The DBMS_SPM package provides functionality for transferring SQL plan baselines between databases. First, a staging table must be created in the source database using the CREATE_STGTAB_BASELINE procedure.

BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
    table_name      => 'spm_stageing_tab',
    table_owner     => 'TEST',
    tablespace_name => 'USERS');
END;
/
The PACK_STGTAB_BASELINE function exports the SQL plan baselines to the staging table. There are several parameters allowing you to limit amount and type of data you export. The following example exports all SQL plan baselines.

SET SERVEROUTPUT ON
DECLARE
  l_plans_packed  PLS_INTEGER;
BEGIN
  l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
    table_name      => 'spm_stageing_tab',
    table_owner     => 'TEST');

  DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/

Then export this table and import it to destination schema. Post importing we can unpack baselines by using UNPACK_STGTAB_BASELINE function

SET SERVEROUTPUT ON
DECLARE
  l_plans_unpacked  PLS_INTEGER;
BEGIN
  l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
    table_name      => 'spm_stageing_tab',
    table_owner     => 'TEST',
    creator         => 'TEST');

  DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/

DROPPING PLANS AND BASELINES:

The DROP_SQL_PLAN_BASELINE function can drop a specific plan from a baseline, or all plans if the plan name is not specified.

CONN sys/password@db11g AS SYSDBA

SET SERVEROUTPUT ON
DECLARE
  l_plans_dropped  PLS_INTEGER;
BEGIN
  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle => NULL,
    plan_name  => 'SYS_SQL_7b76323ad90440b9');
   
  DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/

Oracle materialized views

Materialized views are oracle objects those contains results of the query. They are local copies of the objects located remotely or used to create summary table based on the aggregation of a tables's data.

grant from system:
grant create materialized view, query rewrite to SCOTT;

Create matview:
create materialized view meta_table_info_mview
build immediate refresh complete enable query rewrite as select * from meta_table_info;
--Enable query rewrite needs EE and it will fail if database is XE.
create materialized view md_table_info_mview
build immediate refresh complete as select * from meta_table_info;

Create matview logs:
create materialized view log on meta_table_info;

DML on mat view: DML operations are not permitted on mat views.'
delete from meta_table_info_mview where table_name='SKUIPOPARAM'

ORA-01732: data manipulation operation not legal on this view

Refresh matview:
Execute dbms_mview.refresh('MD_TABLE_INFO_MVIEW');


--Query to check all mviews
select * from user_mviews;

--query to check master table and mview log
select MASTER, LOG_TABLE from USER_MVIEW_LOGS

--Find related mview log tables by using following queries
select TABLE_NAME from USER_TABLES where table_name like '%MD_TABLE_INFO%';
 or
select  master, log, temp_log from sys.mlog$ where mowner = 'WWFMGR' and master = 'MD_TABLE_INFO';

--Update a record in master table and check if mview log table updated or not

update MD_TABLE_INFO set table_name='XXX' WHERE TABLE_NAME='SS';

select * from MLOG$_MD_TABLE_INFO;

HYBRID histograms

Oracle has introduced HYBRID histograms with Oracle 12C.  When number of buckets are more than 254, some almost popular values may get lost , resulting in sub-optimal index usage.

A single bucket can now store popularity as well , effectively increasing number of buckets without actually increasing it.

With oracle 12C, oracle merged Frequency and Height balanced histograms into hybrid histograms(Though original one also co-exists for their original purpose).

If we don’t specify AUTO_SAMPLE_SIZE(but use user defined sampling) as estimate, Hybrid histograms will not be created.If users specify their own percentage, then the database creates frequency or height-balanced histograms.

So when in doubt that issue is due to Hybrid histograms, we can try this approach. 

we can also set hybrid histgrams of by changing preferences.

TOP FREQUENCY is also a concern since it ignores the least occurred values and may generate wrong plan. It’s good to set this one also OFF.

BEGIN

  dbms_stats.set_global_prefs(pname => 'ENABLE_HYBRID_HISTOGRAMS', pvalue => 0);

  dbms_stats.set_global_prefs(pname => 'ENABLE_TOP_FREQ_HISTOGRAMS', pvalue => 0);

END;

Capture changed data from a table.

I had a requirement where for some promotions event client wanted to capture changed data from last 1 hour or 1 day. The table size was huge (750M rows) and this table is getting used for transactions so creating trigger is also not a good option as it may impact performance for application. Also creating materialized views etc. also involve a certain level of changes and still we need to compare.

As making changes in application code is not so quick so we identified that if we create a backup table and compare by using compare utility package, it will be quick.

below code is tested in 5.5 M records in each table and it completed comparison and generating delta in just 1 minute.

CREATE OR REPLACE PACKAGE pkg_comparision
AS
   -- ****************************************************************************
   -- Name: pkg_comparision
   -- What:
   --   Collection of procedures used as utilities to compare tables. This is draft version and latter it can be enhance to compare tables over db link or compare tables from multiple schema's
   -- Also in draft version it will not store historical comparision but it can be enhanced to do the same.
   -- dbms_comparison and DBA_COMPARISON_ROW_DIF is not having direct permissions assigned to users but via roles
   -- so if we want to call it via package
   -- we need to execute below statements from SYS user
   -- grant execute on dbms_comparison to SCPOUSER;
   -- grant select on DBA_COMPARISON_ROW_DIF to SCPOUSER;
   -- Final output will be written to output_table
   -- Changes
   -- When        Who                     What
   -- ----------  ----------------------  ----------------------------------------
   -- 22/06/2017  Anand Yadav, XYZ      v1.0 Initial draft
   -- ****************************************************************************
   comparision_name   VARCHAR2 (30);

   -- *********************************************************
   -- function: get_comparision_name
   -- Purpose:
   --   Create a new comparision name
   --
   -- Input:
   --
   -- *********************************************************
   FUNCTION get_comparision_name (orig_table_name   IN VARCHAR2,
                                  bk_table_name     IN VARCHAR2)
      RETURN VARCHAR2;


   -- *********************************************************
   -- Procedure: drop_comparision
   -- Purpose:
   --   To drop comparision before next iteration
   --
   -- Input:
   --
   -- *********************************************************
   PROCEDURE drop_comparision (comparision_name IN VARCHAR2);

   -- *********************************************************
   -- Procedure: create_comparision
   -- Purpose:
   --   Create a new comparision
   --
   -- Input:
   --
   -- *********************************************************
   PROCEDURE create_comparision (orig_table_name   IN VARCHAR2,
                                 bk_table_name     IN VARCHAR2,
                                 orig_index_name   IN VARCHAR2);

   -- *********************************************************
   -- Procedure: compare
   -- Purpose:
   --   compare tables data based on comparision created
   --
   -- Input:
   --
   -- *********************************************************
   PROCEDURE compare (comparison_name IN VARCHAR2);

   -- *********************************************************
   -- Procedure: generate_delta
   -- Purpose:
   --   generate difference of data and write it to output table
   --
   -- Input:
   --
   --
   -- *********************************************************
   PROCEDURE generate_delta (orig_table_name    IN VARCHAR2,
                             bk_table_name      IN VARCHAR2,
                             output_table         IN VARCHAR2,
                             comparision_name   IN VARCHAR2);

   -- *********************************************************
   -- Procedure: execute_comparision
   -- Purpose:
   --   This is the procedure external system will invoke
   --
   -- Input:
   --
   --
   -- *********************************************************
   PROCEDURE execute_comparision (orig_table_name   IN VARCHAR2,
                                  bk_table_name     IN VARCHAR2,
                                  orig_index_name   IN VARCHAR2,
                                  output_table      IN VARCHAR2);
END pkg_comparision;
/

CREATE OR REPLACE PACKAGE BODY pkg_comparision
AS
   -- ****************************************************************************
   -- Name: pkg_comparision
   -- What:
   -- Collection of procedures used as utilities to compare tables. This is draft version and latter it can be enhance to compare tables over db link or compare tables from multiple schema's
   -- Also in draft version it will not store historical comparision but it can be enhanced to do the same.
   -- dbms_comparison and DBA_COMPARISON_ROW_DIF is not having direct permissions assigned to users but via roles
   -- so if we want to call it via package
   -- we need to execute below statements from SYS user
   -- grant execute on dbms_comparison to SCPOUSER;
   -- grant select on DBA_COMPARISON_ROW_DIF to SCPOUSER;
   -- Final output will be written to output_table
   -- Changes
   -- When        Who                     What
   -- ----------  ----------------------  ----------------------------------------
   -- 22/06/2017  Anand Yadav, XYZ      v1.0 Initial draft
   -- ****************************************************************************

   -- ----------  ----------------------  ----------------------------------------
   -- Usage exec PKG_COMPARISION.EXECUTE_COMPARISION('original table name', 'backup table name', 'original table index', 'output table');
   -- Example exec PKG_COMPARISION.EXECUTE_COMPARISION('ALL_OBJECTS_TEMP1','ALL_OBJECTS_TEMP','ALL_OBJECTS_TEMP1_IDX','MY_OUTPUT1');
   -- Output table can be given anything as per users convinience
   -- ****************************************************************************
 
   -- Function: get_comparision_name
   -- Purpose:
   --   Create a new comparision name
   --
   --
   -- Input:
   --
   -- *********************************************************

   FUNCTION get_comparision_name (orig_table_name   IN VARCHAR2,
                                  bk_table_name     IN VARCHAR2)
      RETURN VARCHAR2
   IS
   --   comparision_name   VARCHAR2 (30) := NULL;
   BEGIN
      SELECT    'COMP_'
             || SUBSTR (UPPER (LTRIM (RTRIM (orig_table_name))), 1, 12)
             || '_'
             || SUBSTR (UPPER (LTRIM (RTRIM (bk_table_name))), 1, 12)
        INTO comparision_name
        FROM DUAL;

      RETURN comparision_name;
   END;

   -- *********************************************************
   -- Procedure: drop_comparision
   -- Purpose:
   --   To drop comparision before next iteration
   --
   -- Input:
   --
   -- *********************************************************

   PROCEDURE drop_comparision (comparision_name IN VARCHAR2)
   IS
   BEGIN
      DBMS_COMPARISON.drop_comparison (comparision_name);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            'Comparision object ' || comparision_name || 'does not exists');
   --raise;
   END;

   -- *********************************************************
   -- Procedure: create_comparision
   -- Purpose:
   --   Create a new comparision
   --
   -- Input:
   --
   -- *********************************************************
   PROCEDURE create_comparision (orig_table_name   IN VARCHAR2,
                                 bk_table_name     IN VARCHAR2,
                                 orig_index_name   IN VARCHAR2)
   IS
   BEGIN
      DBMS_COMPARISON.create_comparison (
         comparison_name      => comparision_name,
         schema_name          => USER,
         object_name          => orig_table_name,
         index_schema_name    => USER,
         index_name           => orig_index_name,
         dblink_name          => '',
         remote_schema_name   => USER,
         remote_object_name   => bk_table_name,
         column_list          => '*',
         scan_mode            => DBMS_COMPARISON.cmp_scan_mode_full);
   END;

   -- *********************************************************
   -- Procedure: compare
   -- Purpose:
   --   compare tables data based on comparision created
   --
   -- Input:
   --
   -- *********************************************************
   PROCEDURE compare (comparison_name IN VARCHAR2)
   IS
      v_scan_info        DBMS_COMPARISON.comparison_type;
      v_compare_result   BOOLEAN;
   BEGIN
      v_compare_result :=
         DBMS_COMPARISON.compare (comparison_name   => comparison_name,
                                  scan_info         => v_scan_info,
                                  perform_row_dif   => TRUE);

      IF v_compare_result = TRUE
      THEN
         DBMS_OUTPUT.put_line (a => 'Tables are synchronized!');
      ELSE
         DBMS_OUTPUT.put_line (
            a   =>    'Warning! Data divergence found!'
                   || CHR (10)
                   || 'Scan id differences: '
                   || v_scan_info.scan_id);
      END IF;
   END;

   -- *********************************************************
   -- Procedure: generate_delta
   -- Purpose:
   --   generate difference of data and write it to output table
   --
   -- Input:
   --
   --
   -- *********************************************************
   PROCEDURE generate_delta (orig_table_name    IN VARCHAR2,
                             bk_table_name      IN VARCHAR2,
                             output_table         IN VARCHAR2,
                             comparision_name   IN VARCHAR2)
   AS
      cnt   NUMBER := 0;
   BEGIN
      --DBMS_OUTPUT.PUT_LINE('CREATE TABLE '||comparision_name|| ' AS select * from DBA_COMPARISON_ROW_DIF where COMPARISON_NAME='''||comparision_name||'''');
      SELECT COUNT (1)
        INTO cnt
        FROM user_tables
       WHERE table_name = comparision_name;

      IF cnt > 0
      THEN
         EXECUTE IMMEDIATE 'DROP TABLE ' || comparision_name;
      END IF;

      EXECUTE IMMEDIATE
            'CREATE TABLE '
         || comparision_name
         || ' AS select * from DBA_COMPARISON_ROW_DIF where COMPARISON_NAME='''
         || comparision_name
         || '''';

      EXECUTE IMMEDIATE
            'CREATE INDEX '
         || SUBSTR (comparision_name, 1, 15)
         || 'IDX1 ON '
         || comparision_name
         || '(LOCAL_ROWID)';

      EXECUTE IMMEDIATE
            'CREATE INDEX '
         || SUBSTR (comparision_name, 1, 15)
         || 'IDX2 ON '
         || comparision_name
         || '(REMOTE_ROWID)';

      cnt := 0;

      SELECT COUNT (1)
        INTO cnt
        FROM user_tables
       WHERE table_name = output_table;

      IF cnt > 0
      THEN
         EXECUTE IMMEDIATE 'DROP TABLE ' || output_table;
      END IF;

      EXECUTE IMMEDIATE
            'creAte table '
         || output_table
         || ' as
select ''INSERT'' AS operation, b.* from '
         || comparision_name
         || ' a, '
         || orig_table_name
         || ' b where a.local_rowid=b.rowid and a.remote_rowid is null
UNION ALL
select ''DELETE'' AS operation, b.* from '
         || comparision_name
         || ' a, '
         || bk_table_name
         || ' b where a.remote_rowid=b.rowid and a.local_rowid is null
UNION ALL
select ''UPDATE'' AS operation, b.* from '
         || comparision_name
         || ' a, '
         || orig_table_name
         || ' b where a.local_rowid=b.rowid and a.local_rowid is not null and a.remote_rowid is not null';
   END;

   -- *********************************************************
   -- Procedure: execute_comparision
   -- Purpose:
   --   This is the procedure external system will invoke
   --
   -- Input:actual table name, backup table name, actual table index, output table
   --
   --
   -- *********************************************************

   PROCEDURE execute_comparision (orig_table_name   IN VARCHAR2,
                                  bk_table_name     IN VARCHAR2,
                                  orig_index_name   IN VARCHAR2,
                                  output_table      IN VARCHAR2)
   IS
      comparision_name   VARCHAR2 (30) := NULL;
   BEGIN
      comparision_name :=
         get_comparision_name (orig_table_name, bk_table_name);
      drop_comparision (comparision_name);
      create_comparision (orig_table_name, bk_table_name, orig_index_name);
      compare (comparision_name);
      generate_delta (orig_table_name,
                      bk_table_name,
                      output_table,
                      comparision_name);
   END;
END pkg_comparision;
/

SHOW ERRORS;