sourcecode

고유 인덱스가 있을 때 Oracle 10g과 11g 사이의 REF CURSOR에 대한 다른 동작?

codebag 2023. 7. 2. 19:28
반응형

고유 인덱스가 있을 때 Oracle 10g과 11g 사이의 REF CURSOR에 대한 다른 동작?

묘사

Oracle 스토어드 프로시저는 개발 인스턴스, Oracle 8을 실행하는 여러 클라이언트 테스트 및 운영 인스턴스에서 로컬로 7년 정도 실행된 후 9, 10, 최근 11을 실행하고 있습니다.Oracle 11g로 업그레이드하기 전까지 지속적으로 작동했습니다.기본적으로 이 절차는 참조 커서를 열고 테이블을 업데이트한 다음 완료합니다.10g에서는 커서가 예상 결과를 포함하지만 11g에서는 커서가 비어 있습니다.11g으로 업그레이드 후 DML 또는 DDL이 변경되지 않았습니다.이 동작은 시도한 모든 10g 또는 11g 인스턴스(10.2.0.3, 10.2.0.4, 11.1.0.7, 11.2.0.1 - 모두 Windows에서 실행됨)에서 일관됩니다.

구체적인 코드는 훨씬 복잡하지만 문제를 다소 현실적인 개요로 설명하면 다음과 같습니다.헤더 테이블에 데이터가 있고 PDF로 출력될 하위 테이블이 있습니다.헤더 테이블에는 해당 데이터가 아직 처리되었는지 여부를 나타내는 부울(0은 거짓, 1은 참) 열이 있습니다.

보기는 처리되지 않은 의 행만 표시하도록 제한됩니다(보기는 일부 다른 테이블에 조인하거나 일부 인라인 쿼리 및 함수 호출 등).따라서 커서가 열리면 보기에 하나 이상의 행이 표시되고 커서가 열린 후 업데이트 문이 실행되어 헤더 테이블에서 플래그를 뒤집고 커밋이 실행된 다음 절차가 완료됩니다.

10g에서는 커서가 열리고 행이 포함된 다음 업데이트 문이 플래그를 뒤집고 절차를 두 번째 실행하면 데이터가 생성되지 않습니다.

11g에서는 커서에 행이 포함되지 않습니다. 마치 업데이트 문이 실행될 때까지 커서가 열리지 않는 것과 같습니다.

11g(바라건대 구성할 수 있는 설정)에서 다른 절차와 다른 응용 프로그램에 영향을 미칠 수 있는 무언가가 변경되지 않을까 우려됩니다.제가 알고 싶은 것은 두 데이터베이스 버전 간에 동작이 다른 이유와 코드 변경 없이 문제를 해결할 수 있는지에 대해 아는 사람이 있는지 여부입니다.

업데이트 1: 문제를 고유한 제약 조건까지 추적할 수 있었습니다.11g에 고유한 제약 조건이 있을 때 실제 개체에 대해 실제 월드 코드를 실행하든 다음과 같은 간단한 예제를 실행하든 상관없이 문제는 100% 재현 가능한 것으로 보입니다.

업데이트 2: 방정식에서 뷰를 완전히 제거할 수 있었습니다.테이블에 대해 직접 쿼리를 할 때도 문제가 있다는 것을 보여주기 위해 간단한 예제를 업데이트했습니다.

간단한 예

CREATE TABLE tbl1
(
  col1  VARCHAR2(10),
  col2  NUMBER(1)
);

INSERT INTO tbl1 (col1, col2) VALUES ('TEST1', 0);

/* View is no longer required to demonstrate the problem
CREATE OR REPLACE VIEW vw1 (col1, col2) 
AS 
SELECT col1, col2 
  FROM tbl1 
 WHERE col2 = 0;
*/

CREATE OR REPLACE PACKAGE pkg1
AS
   TYPE refWEB_CURSOR IS REF CURSOR;

   PROCEDURE proc1 (crs  OUT  refWEB_CURSOR);

END pkg1;

CREATE OR REPLACE PACKAGE BODY pkg1 
IS
   PROCEDURE proc1 (crs  OUT  refWEB_CURSOR)
   IS
   BEGIN

      OPEN crs FOR
        SELECT col1
          FROM tbl1
         WHERE col1 = 'TEST1'
           AND col2 = 0;

      UPDATE tbl1
         SET col2 = 1
       WHERE col1 = 'TEST1';

      COMMIT;

   END proc1;

END pkg1;

익명 블록 데모

DECLARE 
   crs1  pkg1.refWEB_CURSOR;

   TYPE rectype1 IS RECORD (
      col1  vw1.col1%TYPE
   );

   rec1  rectype1;
BEGIN 
   pkg1.proc1 ( crs1 );

   DBMS_OUTPUT.PUT_LINE('begin first test');

   LOOP
      FETCH crs1
       INTO rec1;

      EXIT WHEN crs1%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(rec1.col1);

   END LOOP;  

   DBMS_OUTPUT.PUT_LINE('end first test');

END; 

/* After creating this index, the problem is seen */
CREATE UNIQUE INDEX unique_col1 ON tbl1 (col1);

/* Reset data to initial values */
TRUNCATE TABLE tbl1;

INSERT INTO tbl1 (col1, col2) VALUES ('TEST1', 0);

DECLARE 
   crs1  pkg1.refWEB_CURSOR;

   TYPE rectype1 IS RECORD (
      col1  vw1.col1%TYPE
   );

   rec1  rectype1;
BEGIN 
   pkg1.proc1 ( crs1 );

   DBMS_OUTPUT.PUT_LINE('begin second test');

   LOOP
      FETCH crs1
       INTO rec1;

      EXIT WHEN crs1%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(rec1.col1);

   END LOOP;  

   DBMS_OUTPUT.PUT_LINE('end second test');

END;

10g 출 예 력:
첫 번째 테스트를 시작합니다.
테스트 1
번째 종료하기
두 번째 테스트를 시작합니다.
테스트 1
두 번째 테스트 종료

출 예 력:
첫 번째 테스트를 시작합니다.
테스트 1
번째 종료하기
두 번째 테스트를 시작합니다.
두 번째 테스트 종료

명확화

실제 시나리오에서는 웹 응용 프로그램에서 절차를 호출하기 때문에 COMMIT를 제거할 수 없습니다.프런트 엔드의 데이터 공급자가 절차를 호출하면 데이터베이스와의 연결을 끊을 때 암묵적인 COMMIT를 실행합니다.따라서 절차에서 커밋을 제거하면 예, 익명 블록 데모는 작동하지만 실제 시나리오는 커밋이 계속 발생하기 때문에 작동하지 않습니다.

질문.

11g는 왜 다르게 행동하나요?코드를 다시 쓰는 것 외에 제가 할 수 있는 일이 있나요?

이것은 꽤 최근에 발견된 버그로 보입니다.Metalink Bug 1045196은 정확한 문제를 설명합니다.패치가 곧 출시되기를 바랍니다.Metalink 벽을 통과할 수 없는 분들을 위해 다음과 같은 몇 가지 세부 사항이 있습니다.

메탈링크

Bug 10425196: PL/SQL Returning Ref 커서가 11.1.0.6과 10.2.0.5에서 다르게 작동함

: : 불량
- : 2 - 심각한 서비스 손실
: Created: 12월 22일 작성: 2010년 12월 22일

원본 사례 제출을 통한 진단 분석:
10 예상 10.2.0.4 Windows 버전
예상 10.2.0.5 Solaris 파일
Solaris 치 않은 .1.0.6 Solaris 파일
예기치 않은 .1.0.7 Windows 버전
Solaris 치 않은 .2.0.1 Solaris 파일
Solaris 치 않은 동작 11.2.0.2 Solaris 파일

자세한 내용은 확인할 수 있습니다.
10 예상 10.2.0.3 Windows 버전
치 않은 동작 11.2.0.1 윈도우즈 파일

추가 세부 정보

OPTIMIZER_FEATURES_ENABLE='10.2.0.4' 매개 변수를 변경해도 문제가 해결되지 않습니다.따라서 옵티마이저 수정보다는 11g 데이터베이스 엔진의 설계 변경과 더 관련이 있는 것으로 보입니다.

코드 해결 방법

이는 테이블을 쿼리할 때 인덱스를 사용한 결과이지 테이블 업데이트 및/또는 커밋 작업을 수행한 결과가 아닙니다.위의 예를 사용하여 쿼리가 인덱스를 사용하지 않도록 하는 두 가지 방법이 있습니다.둘 다 쿼리 성능에 영향을 줄 수 있습니다.

쿼리 성능에 영향을 주는 것은 패치가 릴리스될 때까지 일시적으로 허용될 수 있지만 @EdgarChupit가 제안한 대로 FLASHBACK을 사용하면 전체 인스턴스의 성능에 영향을 미칠 수 있으며(또는 일부 인스턴스에서는 사용할 수 없는 경우도 있을 수 있음) 일부에서는 허용되지 않을 수 있습니다.어느 쪽이든 현 시점에서 코드 변경이 유일한 해결 방법으로 나타납니다.

방법 1: 코드를 변경하여 열을 함수로 감싸서 이 열의 고유 인덱스가 사용되지 않도록 합니다.열이 고유하더라도 소문자를 포함하지 않기 때문에 허용됩니다.

    SELECT col1
      FROM tbl1
     WHERE UPPER(col1) = 'TEST1'
       AND col2 = 0;

방법 2: 인덱스 사용을 금지하는 힌트를 사용하도록 쿼리를 변경합니다.NO_INDEX(unique_col1) 힌트가 작동할 것으로 예상할 수 있지만 작동하지 않습니다.규칙 힌트가 작동하지 않습니다.FULL(tbl1) 힌트를 사용할 수는 있지만 방법 1을 사용하는 것보다 쿼리 속도가 더 느려질 수 있습니다.

    SELECT /*+ FULL(tbl1) */ col1
      FROM tbl1
     WHERE col1 = 'TEST1'
       AND col2 = 0;


Oracle의 대응 및 제안된 해결 방법

Oracle 지원이 마침내 다음과 같은 Metalink 업데이트로 응답했습니다.

Oracle 지원 - 2011년 7월 20일 오전 5:51:19 GMT-07:00 [ODM 제안 솔루션]개발팀은 이 문제가 해결해야 할 중요한 문제가 될 것이라고 보고했습니다.에서는 다음 해결 방법을 적용할 것을 제안했습니다.

다음과 같은 문서화되지 않은 매개 변수를 사용하여 init.ora/spfile을 편집합니다.

"_row_cr" = false
Oracle 지원 - 2011년 7월 20일 오전 5:49:20 GMT-07:00 [ODM 원인 정당성]개발 결과 결함으로 판명되었습니다.
Oracle 지원 - 2011년 7월 20일 오전 5:48:27 GMT-07:00 [ODM 원인 파악]원인이 행 소스 커서 최적화로 추적되었습니다.
Oracle 지원 - 2011년 7월 20일 오전 5:47:27 GMT-07:00 [ODM 이슈 검증]개발 결과 11.2.0.1에서 이 문제가 확인되었습니다.

몇 가지 추가적인 서신을 주고받은 결과, 이 문제는 버그로 취급되지 않고 설계 결정이 진행되는 것으로 보입니다.

Oracle 지원 - 2011년 7월 21일 오전 5:58:07 GMT-07:00 [ODM 제안 솔루션 Justif]10.2.0.5 이후(11.2.0.2 포함)에는 다음과 같은 최적화가 있습니다.ROW CR 고유 인덱스를 사용하는 쿼리에만 적용됩니다.표의 행을 결정합니다.

이 최적화에 대한 간략한 개요는 롤백을 방지하기 위해 노력한다는 것입니다.현재 블록에 커밋되지 않은 변경 사항이 없는 경우 CR 블록을 구성합니다.

따라서 11.2.0.2에서 볼 수 있는 차이는 이러한 최적화 때문입니다.그제안된 해결책은 이 최적화를 해제하여 상황을 해결하는 것입니다.10.2.0.4에서 작업하던 것과 동일하게 작업합니다.

이 경우 클라이언트 환경을 고려할 때 단일 저장 프로시저로 격리되어 있으므로 코드 해결 방법을 계속 사용하여 알 수 없는 인스턴스 전반의 부작용이 다른 애플리케이션 및 사용자에게 영향을 미치지 않도록 할 것입니다.

이것은 정말 이상한 문제입니다, 공유해 주셔서 감사합니다!

Oracle 11.1부터 시작하는 Oracle의 동작 변경처럼 보이며, metalink(bug #10425196)에서도 유사한 문제가 확인된 버그가 있습니다.안타깝게도 현재는 주제에 대한 메탈링크에 대한 정보가 많지 않지만, 저는 더 많은 정보를 제공해 달라는 오라클의 요청에 따라 SR을 개설했습니다.

현재로서는 이러한 현상이 발생하는 이유와 이 동작을 10g 스타일로 되돌릴 수 있는 (숨겨진) 매개 변수가 있다면 해결 방법을 제공할 수 있다고 생각합니다.오라클 플래시백 쿼리 기능을 사용하여 오라클이 예상 시점까지 데이터를 검색하도록 할 수 있습니다.

다음과 같이 코드를 변경하는 경우:

OPEN crs FOR 
  SELECT col1
>>> FROM vw1 as of scn dbms_flashback.get_system_change_number
   WHERE col1 = 'TEST1';

결과는 10g과 동일해야 합니다.

그리고 이것은 원래 테스트 사례의 단순화된 버전입니다.

SQL> drop table tbl1;
Table dropped
SQL> create table tbl1(col1 varchar2(10), col2 number);
Table created
SQL> create unique index tbl1_idx on tbl1(col1);
Index created
SQL> insert into tbl1(col1,col2) values('TEST1',0);
1 row inserted
SQL> DECLARE
  2    cursor web_cursor is
  3          SELECT col1
  4            FROM tbl1
  5           WHERE col2 = 0 and col1 = 'TEST1';
  6  
  7    rec1  web_cursor%rowtype;
  8  BEGIN
  9    OPEN web_cursor;
 10  
 11    UPDATE tbl1
 12       SET col2 = 1
 13     WHERE col1 = 'TEST1';
 14  
 15    -- different result depending on commit!
 16    commit;
 17  
 18     DBMS_OUTPUT.PUT_LINE('Start');
 19     LOOP
 20        FETCH web_cursor
 21         INTO rec1;
 22  
 23        EXIT WHEN web_cursor%NOTFOUND;
 24  
 25        DBMS_OUTPUT.PUT_LINE(rec1.col1);
 26     END LOOP;
 27     DBMS_OUTPUT.PUT_LINE('Finish');
 28  END;
 29  /

Start
Finish

PL/SQL procedure successfully completed

16행에서 커밋을 주석 처리하면 출력은 다음과 같습니다.

Start
TEST1
Finish

PL/SQL procedure successfully completed

From Metalink(일명 Oracle 지원)

상태 버그 10425196 : 92 - 닫힘, 버그 아님

문제:

REF CURSOR를 반환하는 저장 프로시저를 호출하는 경우 10.2.0.5 이전 버전과 11.1.0.6 이상 데이터베이스 간에 다른 동작이 나타납니다.

이벤트 순서

  1. 기준 커서를 전달하는 호출 저장 프로시저
  2. 테이블에 대해 참조 커서 열기a
  3. 저장 프로시저 내부에서 표 A 내부의 일부 데이터 업데이트
  4. 업데이트 커밋
  5. 절차 실행이 종료되어 Ref Cursor가 호출자에게 다시 반환합니다.

10.2.0.5 이전 버전

반환된 커서는 데이터가 업데이트되기 전에 열렸기 때문에 업데이트된 데이터를 볼 수 없습니다.이것은 예상된 동작입니다.

11.1.0.6 이상

반환된 커서는 업데이트된 데이터를 보고 10.2.0.5 이전 동작과 다른 업데이트된 데이터를 반환합니다.

진단 분석:

10.2.0.4 Windows 예상 동작 10.2.0.5 Solaris 예상 동작 11.1.0.6 Solaris 예상 동작 11.1.0.7 Windows 예상 동작 11.2.0.1 Solaris 예상 동작 11.2.0.2 Solaris 예상 동작 11.2 Solaris 예상 동작

관련 버그:

찾을 수 없습니다.

필요한 경우 10.2.0.5 이전 동작 설정으로 돌아가서 다음 시작 매개 변수를 설정하고 데이터베이스를 재시작할 수 있습니다.

_row_cr = false

언급URL : https://stackoverflow.com/questions/4598725/different-behavior-for-ref-cursor-between-oracle-10g-and-11g-when-unique-index-p

반응형

'sourcecode' 카테고리의 다른 글

내부 순환; 빌드에서 신뢰할 수 없는 결과를 생성할 수 있음: Xcode 오류  (0) 2023.07.02
int가 짧고 잘린 경우 새 값은 어떻게 결정됩니까?  (0) 2023.07.02
프로그래밍 방식으로 발사 버튼 클릭 이벤트?  (0) 2023.07.02
도커 구성: mariadb - 연결이 거부됨  (0) 2023.07.02
USER()와 SYS_CONTEXT('USERNV', 'CURRENT_USER')의 차이점은 무엇입니까?  (0) 2023.07.02

'sourcecode'의 다른글

  • 현재글고유 인덱스가 있을 때 Oracle 10g과 11g 사이의 REF CURSOR에 대한 다른 동작?

관련글

댓글

티스토리툴바