SQLServer publication from Oracle

Question

One of our customer have one Oracle 10.2.0.5 RAC (HPUX) and two SQL Server 2012 (Windows server 2008R2). And we are helping them to publish data from Oracle to SQL Server. They also need to know which rows are added, updated and deleted, but they do not want to modify their apps.

The whole thing works like this :

Oracle as publisher -> SQL Server A as distributor -> SQL Server B as subscriber

Our DBA had configured all the DBs through SSMS(SQL Server Management Studio) like this Create a Publication from an Oracle Database. It worked very well for several days. But the performance of Oracle is getting worse and worse. At last, we have to stop the data publish of Oracle.

It turns out that, SSMS will create one package called "HREPL" in Oracle, which has a procedure called "PollEnd". "PollEnd" will be executed in a very high frequency to delete data in table "HREPL_ARTICLE1LOG_1". But the execution time of "PollEnd" increases through time. At last, the execution time is longer than the time span to execute, and the table is locked, and the performance of Oracle will be very bad.

And we stuck here.

Does anybody have any idea how to fix this? Please help!

The "PollEnd" procedure:

-----------------------------------------------------------------------------------
--
--  Name:    PollEnd
--  Purpose: PollEnd request signifies that the change entries identified with the current
--           interval have been successfully entered into the store and forward database
--           and can be deleted from the article log tables.
--  Input:
--           argLSN         IN RAW(10)      LSN from distributor that was associated
--                                          with this poll interval
--  Output:
--  Notes:   This request causes those entries of the article log tables represented in the
--           Poll Table and having the current pollid to be deleted from both their article log
--           tables and from the Poll Table. The last request value is updated to reflect a
--           PollEnd request.
--
-----------------------------------------------------------------------------------
PROCEDURE PollEnd
(
    argLSN      IN RAW
)
AS
    SQLCommand      VARCHAR2(500);
    LogTable        VARCHAR2(255);
    CurrentPollID   NUMBER;
    TableIDs        number_tab;
    InstanceIDs     number_tab;
    IDCount         BINARY_INTEGER;
    PublisherLSN    RAW(10);

BEGIN
    -- Put the published tableIDs in a PL/SQL table of IDs
    HREPL.GetTableIDs(TableIDs, InstanceIDs);

    -- Get the current Poll ID
    SELECT Publisher_CurrentPollid INTO CurrentPollID FROM HREPL_Publisher;

    IDCount := TableIDs.COUNT;
    -- For each table represented in the ID list
    FOR id_ind IN 1 .. IDCount
    LOOP

        LogTable := REPLACE( REPLACE(ArticleLogTemplate, MatchString, TO_CHAR(TableIDs(id_ind))),
                                                         MatchStringY, TO_CHAR(InstanceIDs(id_ind)));

        BEGIN
            -- Generate command to delete from the article log those entries appearing in the
            -- Poll Table with the current PollID
            SQLCommand := 'DELETE FROM ' || LogTable || ' l ' ||
                              'WHERE EXISTS (SELECT p.POLL_POLLID FROM HREPL_POLL p ' ||
                              '              WHERE CHARTOROWID(l.ROWID) = p.Poll_ROWID '  ||
                              '              AND p.Poll_PollID = :Pollid)';

            HREPL.ExecuteCommandForPollID(SQLCommand, CurrentPollID);

        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END LOOP;

    FOR POLLID IN (SELECT CurrentPollid FROM DUAL)
    LOOP
        -- Delete from HREPL_Event those entries appearing in the Poll Table
        -- with the current PollID.
        DELETE FROM HREPL_Event e
        WHERE EXISTS (SELECT p.POLL_POLLID FROM HREPL_POLL p
                      WHERE CHARTOROWID(e.ROWID) = p.Poll_ROWID
                      AND p.Poll_PollID = POLLID.CurrentPollID);

        -- Delete entries from the Poll Table having the current Pollid
        DELETE FROM HREPL_Poll
        WHERE Poll_PollID = POLLID.CurrentPollID;
    END LOOP;

    -- Drop all views associated with articles that are marked as UnPublishPending.
    -- Note:  We cannot perform these drops in UnPublish table, since UnPublish
    --        table can execute concurrently with PollBegin and the querying
    --        of published tables by the log reader.  PollEnd, however, executes
    --        synchronously with respect to these activities, so can be used
    --        to cleanup log tables and views that are no longer needed.
    HREPL.CleanupLogsandViews;

    -- Mark the last request as PollEnd, and update the Publisher LSN
    -- to reflect the LSN committed at the publisher.
    UPDATE  HREPL_Publisher
    SET Publisher_PollInProcess = NoPollInProcess,
        Publisher_LSN = argLSN;

    -- Commit transaction
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;

END PollEnd;

Edit 01:

The full package is here: HREPL

Edit 02:

At last we give up. MS and Oracle blame each other.

We tried to use ogg to copy data from oracle to sql server, which is a mess too.

Now we are trying to use ogg to copy data from oracle to oracle.

Thanks for all the help.

Answer 1

Convert your DELETE ... WHERE EXISTS (...) queries to use multiple table delete syntax.

SQLCommand := 'DELETE l' || 
    ' FROM HREPL_POLL, ' || LogTable ||
    ' l WHERE CHARTOROWID(l.ROWID) = p.Poll_ROWID '  ||
    ' AND p.Poll_PollID = :Pollid)';

Create a function index on each table involved:

CREATE INDEX MYTABLE_CHARTOROWID ON MYTABLE(CHARTOROWID(ROWID));

And then further down:

DELETE e
FROM HREPL_POLL p, HREPL_Event e
WHERE CHARTOROWID(e.ROWID) = p.Poll_ROWID
  AND p.Poll_PollID = POLLID.CurrentPollID;

Finally, delete entirely the LOOP over dual - it does absolutely nothing whatsoever; just execute the code inside it using CurrentPollid directly.

Answer 2

Some join conditions seem unreasonable, you might be more lucky with this version - if you try it on production, you do that on your very own risk!

-----------------------------------------------------------------------------------
--
--  Name:    PollEnd
--  Purpose: PollEnd request signifies that the change entries identified with the current
--           interval have been successfully entered into the store and forward database
--           and can be deleted from the article log tables.
--  Input:
--           argLSN         IN RAW(10)      LSN from distributor that was associated
--                                          with this poll interval
--  Output:
--  Notes:   This request causes those entries of the article log tables represented in the
--           Poll Table and having the current pollid to be deleted from both their article log
--           tables and from the Poll Table. The last request value is updated to reflect a
--           PollEnd request.
--
-----------------------------------------------------------------------------------
PROCEDURE PollEnd
(
    argLSN      IN RAW
)
AS
    SQLCommand      VARCHAR2(500);
    LogTable        VARCHAR2(255);
    CurrentPollID   NUMBER;
    TableIDs        number_tab;
    InstanceIDs     number_tab;
    IDCount         BINARY_INTEGER;
    PublisherLSN    RAW(10);

BEGIN
    -- Put the published tableIDs in a PL/SQL table of IDs
    HREPL.GetTableIDs(TableIDs, InstanceIDs);

    -- Get the current Poll ID
    SELECT Publisher_CurrentPollid INTO CurrentPollID FROM HREPL_Publisher;

    IDCount := TableIDs.COUNT;
    -- For each table represented in the ID list
    FOR id_ind IN 1 .. IDCount
    LOOP

        LogTable := REPLACE( REPLACE(ArticleLogTemplate, MatchString, TO_CHAR(TableIDs(id_ind))),
                                                         MatchStringY, TO_CHAR(InstanceIDs(id_ind)));

        BEGIN
            -- Generate command to delete from the article log those entries appearing in the
            -- Poll Table with the current PollID
            SQLCommand := 'DELETE FROM ' || LogTable || ' l ' ||
                              'WHERE l.ROWID IN (SELECT chartorowid(p.Poll_ROWID) FROM HREPL_POLL p ' ||
                              '              WHERE p.Poll_PollID = :Pollid)';

            HREPL.ExecuteCommandForPollID(SQLCommand, CurrentPollID);

        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    END LOOP;

   -- Delete from HREPL_Event those entries appearing in the Poll Table
   -- with the current PollID.
   DELETE FROM HREPL_Event e
       WHERE ROWID in (SELECT chartorowid(p.Poll_ROWID) FROM HREPL_POLL p
                      WHERE p.Poll_PollID = CurrentPollID);

        -- Delete entries from the Poll Table having the current Pollid
    DELETE FROM HREPL_Poll
        WHERE Poll_PollID = CurrentPollID;

    -- Drop all views associated with articles that are marked as UnPublishPending.
    -- Note:  We cannot perform these drops in UnPublish table, since UnPublish
    --        table can execute concurrently with PollBegin and the querying
    --        of published tables by the log reader.  PollEnd, however, executes
    --        synchronously with respect to these activities, so can be used
    --        to cleanup log tables and views that are no longer needed.
    HREPL.CleanupLogsandViews;

    -- Mark the last request as PollEnd, and update the Publisher LSN
    -- to reflect the LSN committed at the publisher.
    UPDATE  HREPL_Publisher
    SET Publisher_PollInProcess = NoPollInProcess,
        Publisher_LSN = argLSN;

    -- Commit transaction
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;

END PollEnd;