Sunday, September 4, 2016

SQLServer publication from Oracle

Leave a Comment

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; 

0 Answers

If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment