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 comments:
Post a Comment