sql-server - Oracle 的 SQLServer 发布

Question

我们的一位客户拥有一台 Oracle 10.2.0.5 RAC (HPUX) 和两台 SQL Server 2012 (Windows server 2008R2)。我们正在帮助他们将数据从 Oracle 发布到 SQL Server。他们还需要知道添加、更新和删除了哪些行，但他们不想修改他们的应用程序。

整个事情是这样的:

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

我们的DBA已经通过SSMS(SQL Server Management Studio)配置了所有数据库，如下Create a Publication from an Oracle Database 。几天来效果非常好。但Oracle的性能却越来越差。最后，我们不得不停止Oracle的数据发布。

事实证明，SSMS会在Oracle中创建一个名为“HREPL”的包，其中有一个名为“PollEnd”的过程。 “PollEnd”将以非常高的频率执行，以删除表“HREPL_ARTICLE1LOG_1”中的数据。但是“PollEnd”的执行时间随着时间的推移而增加。最后执行时间超过了执行的时间跨度，表就被锁住了，Oracle的性能就会很差。

我们被困在这里。

有人知道如何解决这个问题吗？请帮忙!

“PollEnd”过程:

-----------------------------------------------------------------------------------
--
--  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;

编辑01:

完整的包在这里:HREPL

编辑02:

最后我们放弃了。 MS 和 Oracle 互相指责。

我们尝试使用ogg将数据从oracle复制到sql server，这也很困惑。

现在我们正在尝试使用ogg将数据从oracle复制到oracle。

感谢您的帮助。

Answer 1

将您的 DELETE ... WHERE EXISTS (...) 查询转换为使用多表删除语法。

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

在涉及的每个表上创建函数索引:

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

然后再往下:

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

最后，完全删除对偶上的LOOP - 它绝对没有任何作用；只需直接使用 CurrentPollid 执行其中的代码即可。