CLOB 상에서의 Substr 성능

CLOB 상에서의 Substr 성능

많은 작업을 수행하는 PL/SQL 절차가 있습니다.SUBSTR아들아VARCHAR2매개 변수길이 제한을 없애고 싶어서 다음으로 바꾸려고 했습니다.CLOB.

작동은 괜찮지만 성능이 저하되기 때문에 몇 가지 테스트를 수행했습니다(2005년의 테스트를 기반으로 함).

---

업데이트: Oracle 버전과 하드웨어가 서로 다른 여러 인스턴스에서 이를 재현할 수 있습니다.dbms_lob.substr항상 보다 눈에 띄게 느립니다.substr(CLOB)그리고 훨씬 더 느립니다.SUBSTR(VARCHAR2).

Bob의 결과와 위 링크의 테스트는 다른 이야기를 합니다.

누가 이것을 설명하거나 적어도 밥이나 제 결과를 재현할 수 있습니까?감사합니다!

---

테스트 결과:

+0000000000:00:00.0040000(VARCHAR2)
+000000000 00:00:00.298000000(클로브 기판)
+000000000 00:00:00.356000000(DBMS_LOB.SUBSTR)

테스트 코드:

DECLARE
  l_text   VARCHAR2(30) := 'This is a test record';
  l_clob   CLOB := l_text;
  l_substr VARCHAR2(30);
  t TIMESTAMP;
BEGIN
  t := SYSTIMESTAMP;
  FOR i IN 1..100000 LOOP
    l_substr := SUBSTR(l_text,1,14);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (VARCHAR2)');

  t := SYSTIMESTAMP;
  FOR i IN 1..100000 LOOP
    l_substr := SUBSTR(l_clob,1,14);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (CLOB SUBSTR)');

  t := SYSTIMESTAMP;
  FOR i IN 1..100000 LOOP
    l_substr := DBMS_LOB.SUBSTR(l_clob,14,1);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (DBMS_LOB.SUBSTR)');
END;

(거짓말, 빌어먹을 거짓말, 기준...)

테스트를 10번 다시 실행하여 문자열을 30자로 확장하고 다음과 같은 평균 결과를 얻었습니다.

+000000000 00:00:00.011694200 (VARCHAR2)
+000000000 00:00:00.901000600 (CLOB SUBSTR)
+000000000 00:00:00.013169200 (DBMS_LOB.SUBSTR)

그런 다음 하위 문자열 범위를 5,14(DBMS_LOB.SUBSTR의 경우 14,5)로 변경하여 다음을 얻었습니다.

+000000000 00:00:00.011731000 (VARCHAR2)
+000000000 00:00:01.010840000 (CLOB SUBSTR)
+000000000 00:00:00.011427000 (DBMS_LOB.SUBSTR)

그런 다음 범위를 17,14(DBMS_LOB.SUBSTR의 경우 14,17)로 변경하고,

+000000000 00:00:00.013578900 (VARCHAR2)
+000000000 00:00:00.964527400 (CLOB SUBSTR)
+000000000 00:00:00.011416800 (DBMS_LOB.SUBSTR)

마지막으로 범위를 25,14(DBMS_LOB.SUBSTR의 경우 14,25)로 변경하여 받았습니다.

+000000000 00:00:00.011210200 (VARCHAR2)
+000000000 00:00:00.916439800 (CLOB SUBSTR)
+000000000 00:00:00.013781300 (DBMS_LOB.SUBSTR)

결론적으로 CLOB에 대해 작업할 때 DBMS_LOB.SUBSTR을 사용하는 것이 "정상적인" VARCHAR2에 대해 SUSTR을 사용하는 것에 비해 성능 저하가 거의 없는 것처럼 보이기 때문에 DBMS_LOB.SUBSTR을 사용하는 것이 가장 좋습니다.참고로 - OS = HP/UX(Unix 버전), Oracle 버전=11.1, 프로세서=HP Itanium 2-플렉스, YMMV.

공유하고 즐기세요.

---

그리고 만약 그것을 할 가치가 있다면, 너무 많이 할 가치가 있기 때문에, 여기 32767자로 확장된 문자열을 가진 몇 가지 더 결과가 있습니다.각 결과 집합과 함께 주어진 하위 문자열 범위:

1, 25000
+000000000 00:00:00.198466400 (VARCHAR2)
+000000000 00:00:02.870958700 (CLOB SUBSTR)
+000000000 00:00:00.174490100 (DBMS_LOB.SUBSTR)

1000, 25000
+000000000 00:00:00.253447900 (VARCHAR2)
+000000000 00:00:02.491790500 (CLOB SUBSTR)
+000000000 00:00:00.193560100 (DBMS_LOB.SUBSTR)

10000, 25000
+000000000 00:00:00.217812000 (VARCHAR2)
+000000000 00:00:02.268794800 (CLOB SUBSTR)
+000000000 00:00:00.222200200 (DBMS_LOB.SUBSTR)

같은 날, 같은 결론.

Cthulhu htagn.

---

(다시 한 번, 사랑하는 친구들, 다시 한 번...)

벤치마크를 다시 실행하여 CLOB의 크기를 3276700으로 변경하고, 길이 25000에 대해 2475000에서 시작하는 부분 문자열을 중간에서 사용하면 다음과 같은 결과를 얻을 수 있습니다.

+000000000 00:00:00.176883200 (VARCHAR2)
+000000000 00:00:02.069482600 (CLOB SUBSTR)
+000000000 00:00:00.175341500 (DBMS_LOB.SUBSTR)

(변경 사항은 마지막 두 검정에만 영향을 미칩니다.)

그리고... 같은 결과, 다른 날.

YMMV.

다음 시스템에서 스크립트를 세 번 실행했습니다.

Oracle Database 11g Enterprise Edition 릴리스 11.1.0.7.0 - 64비트 운영

결과는 다음과 같습니다.

+000000000 00:00:00.007787000 (VARCHAR2)
+000000000 00:00:03.093258000 (CLOB SUBSTR)
+000000000 00:00:00.340017000 (DBMS_LOB.SUBSTR)

+000000000 00:00:00.019460000 (VARCHAR2)
+000000000 00:00:03.302425000 (CLOB SUBSTR)
+000000000 00:00:00.336915000 (DBMS_LOB.SUBSTR)

+000000000 00:00:00.007773000 (VARCHAR2)
+000000000 00:00:03.210619000 (CLOB SUBSTR)
+000000000 00:00:00.336689000 (DBMS_LOB.SUBSTR)

DBMS_LOB.substr의 경우 11gR1 테스트가 원활하게 실행되었지만 11gR2의 경우 기능이 느립니다.

가▁on에 대한 내 시험 .Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionAIX6에 있습니다.

+000000000 00:00:00.009440000 (VARCHAR2)
+000000000 00:00:00.749113000 (CLOB SUBSTR)
+000000000 00:00:01.177685000 (DBMS_LOB.SUBSTR)

이것이 매우 오래된 것이라는 것은 알지만, 여전히 오래된 시스템에 있는 사람들과 관련이 있을 수 있습니다.이것은 데이터 형식 변환 문제처럼 보입니다.@bernhard 효과를 보고 깨달은 것이 있습니다.Weingartner saw에서 오프셋 및 양 인수의 데이터 유형은 큰 차이를 만드는 것으로 보입니다.

이는 Linux(OEL 5.6)에서 11.2.0.3에서 실행되며, 차이를 더욱 명확히 하기 위해 100만 번 반복으로 증가합니다.

DECLARE
  l_text   VARCHAR2(30) := 'This is a test record';
  l_clob   CLOB := l_text;
  l_substr VARCHAR2(30);
  t TIMESTAMP;
BEGIN
  t := SYSTIMESTAMP;
  FOR i IN 1..1000000 LOOP
    l_substr := SUBSTR(l_text,1,14);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (VARCHAR2)');

  t := SYSTIMESTAMP;
  FOR i IN 1..1000000 LOOP
    l_substr := SUBSTR(l_clob,1,14);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (CLOB SUBSTR)');

  t := SYSTIMESTAMP;
  FOR i IN 1..1000000 LOOP
    l_substr := DBMS_LOB.SUBSTR(l_clob,14,1);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (DBMS_LOB.SUBSTR with 14,1)');

  t := SYSTIMESTAMP;
  FOR i IN 1..1000000 LOOP
    l_substr := DBMS_LOB.SUBSTR(l_clob,14.0,1.0);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (DBMS_LOB.SUBSTR with 14.0,1.0)');

  t := SYSTIMESTAMP;
  FOR i IN 1..1000000 LOOP
    l_substr := DBMS_LOB.SUBSTR(l_clob,cast(14 as number), cast(1 as number));
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (DBMS_LOB.SUBSTR with casts)');
END;
/

+000000000 00:00:00.043019000 (VARCHAR2)
+000000000 00:00:03.671621000 (CLOB SUBSTR)
+000000000 00:00:05.704337000 (DBMS_LOB.SUBSTR with 14,1)
+000000000 00:00:00.040097000 (DBMS_LOB.SUBSTR with 14.0,1.0)
+000000000 00:00:00.040907000 (DBMS_LOB.SUBSTR with casts)

11gR2 문서는 형식 매개 변수를 INTEGER 유형으로 표시하지만 실제로 정수(또는 pls_integer 또는 binary_double)를 전달하는 속도는 느리고 명시적으로 숫자를 전달하는 속도는 빠릅니다.

당신의 원래 질문과 Bob의 결과를 보면 이것은 11.1과 11.2 사이에 바뀐 것처럼 보입니다.저는 테스트할 12c 인스턴스가 없어서 다시 변경되었는지 모르겠습니다.▁의 변화 때문인지dbms_lob또는 PL/SQL이 기본적으로 숫자 값을 처리하는 방식에 대한 광범위한 변경은 명확하지 않습니다.MOS에서 관련성이 있어 보이는 것을 찾지 못했습니다.

언급URL : https://stackoverflow.com/questions/10331912/performance-of-substr-on-clob