IT

Oracle에서 SQL Server 게시

itgroup 2023. 9. 20. 20:17
반응형

Oracle에서 SQL Server 게시

우리 고객 중 한 명이 하나를 가지고 있습니다.Oracle 10.2.0.5RAC(HPUX) 및 SQL Server 2012 2개(Windows server 2008R2(Oracle에서 SQL Server로 데이터를 퍼블리싱할 수 있도록 지원하고 있습니다.또한 어떤 행이 추가되고 업데이트되고 삭제되는지도 알아야 하지만 앱을 수정하고 싶지는 않습니다.

이 모든 것은 다음과 같이 작동합니다.

게시자로서의 Oracle -> 배포자로서의 SQL Server A -> 구독자로서의 SQL Server B

저희 DBA는 이렇게 모든 DB를 SSMS(SQL Server Management Studio)를 통해 Oracle Database에서 Create a Publication과 같이 구성했습니다.며칠 동안 아주 잘 작동했습니다.그러나 Oracle의 성능은 점점 더 나빠지고 있습니다.드디어 오라클의 데이터 공개를 중단해야 합니다.

SSMS는 오라클에서 "HREPL"이라는 하나의 패키지를 만들 것이며, 이 패키지는 ""이라는 절차를 가지고 있습니다.PollEnd". "PollEnd" 테이블의 데이터를 삭제하기 위해 매우 높은 빈도로 실행됩니다."HREPL_ARTICLE1LOG_1". 하지만 "의 실행 시간은.PollEnd" 시간이 지남에 따라 증가합니다.결국 실행 시간이 실행 시간보다 길어지고 테이블이 잠겨 오라클의 성능이 매우 나빠질 것입니다.

그리고 우리는 여기에 있었습니다.

이걸 어떻게 고치는지 아는 사람?도와주세요!

"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와 오라클은 서로를 비난합니다.

ogg를 사용하여 오라클에서 sql 서버로 데이터를 복사하려고 했는데 이 또한 엉망입니다.

이제 ogg를 사용하여 오라클에서 오라클로 데이터를 복사하려고 합니다.

도와주셔서 감사합니다.

변환합니다.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;

마지막으로, 완전히 삭제합니다.LOOPover dual - 그것은 전혀 아무것도 하지 않습니다; 단지 그것 안의 코드를 사용하여 실행합니다.CurrentPollid직접적으로.

일부 가입 조건은 불합리한 것 같습니다. 이 버전을 사용하는 것이 더 운이 좋을 수도 있습니다. 프로덕션에 사용해 보면 위험 부담을 감수해야 합니다!

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

언급URL : https://stackoverflow.com/questions/39142910/sqlserver-publication-from-oracle

반응형

'IT' 카테고리의 다른 글

소수점 2자리 PHP float: .00  (0) 2023.09.25
Path variables in Spring WebSockets @SendTo mapping  (0) 2023.09.25
Pandas 데이터 프레임의 목록을 기준으로 색인된 행의 순서를 변경하는 방법  (0) 2023.09.20
MySQL 쿼리를 예약하는 방법은?  (0) 2023.09.20
wc_empty_cart_message 기능을 wcoCommerce 3.1에서 변경  (0) 2023.09.20

'IT'의 다른글

  • 현재글Oracle에서 SQL Server 게시

관련글

댓글

티스토리툴바