ORACLE/TUNING2009. 2. 9. 21:33

DML의 INSERT 성능 향상

반응형

생각의 전환이 DB를 최적화 시킨다
모든 DML을 INSERT로 변경하자

 

아직도 수많은 기업에서 성능 최적화를 수행하면 서도 주어진 SQL만을 그대로 최적화하려고 하는 경우가 많다. 물론 주어진 SQL의 튜닝을 통해 성능을 최적화할 수 있는 것은 분명하다. 하지만, 우리가 생각을 전환하여 기존이 방식과 다른 방식으로 수행하여 엄청난 성능 향상을 기대할 수 있는 경우도 많다. 대용량 데이터베이스로 변하고 있는 시점에서 성능 최적화를 위해 사용자 생각의 전환은 반드시 필요한 요소다.

 

권순용 | kwontra@hanmail.net

 

 

우리 주위에는 프로젝트의 성능을 향상시키기 위해 SQL 최적화에 전념하는 사이트들이 많이 있을 것이다. SQL 최적화를 통해 크게 성능 향상을 기대할 수 있는 것은 사실이다. 예전에는 성능 저하가 발생하는 경우 SQL 튜닝 보다도 해당 시스템의 CPU 또는 디스크 등의 자원을 증설하는 부분에 초점을 맞추었었다. 이와 달리 SQL을 튜닝하여 성능을 최적화하고자 하는 것은 매우 고무적인 현상임에는 틀림 없다.

 

그 만큼 관리자들의 생각이 IT 선진화로 가는 것은 아닐까? 하지만 아직도 SQL 튜닝을 고정 관념에 맞춰 그리고 그 자체로 튜닝하고자 하는 경우가 많은 것 같다. 우리가 조금만 다르게 생각한다면 SQL 튜닝의 효과를 배가 시킬 수 있다는 것을 아는가? 주어진 SQL을 그대로 보지않고 다르게 보는 순간 우리에게는 새로운 세상이 펼쳐질 것이다.

 

데이터 삭제를 DELETE로 수행하지 않고 데이터의 갱신을 UPDATE로 수행하지 않는다면 우리에게는 새로운 세상이 펼쳐질 것이다. 이제부터 이와 같은 현상에 대해 하나하나 자세히 확인해 보자.

 

 

DML은 왜 성능을 저하시키는가?

 

 

우리가 데이터를 저장하기 위해서는 INSERT를 수행하게 되고 데이터를 제거하기 위해서는 DELETE를 수행하게 된다. 또한, 기존의 데이터를 변경하기 위해서는 UPDATE를 수행하게 된다. 이와 같은 사실은 개발을 한번이라도 한 사람이라면 아니 SQL에 관련된 책을 한번이라도 본 사람이라면 누구나 알 수 있을 것이다.

 

DML 작업을 수행한다면 그리고 DML 작업을 수행해야 하는 데이터가 매우 많다면 많은 시간이 소요될 거라고 누구나 생각할 것이다. 그렇다면 이와 같이 대용량의 데이터에 대해 DML 작업을 수행하는 경우 어떤 이유에서 많은 시간이 소요되는 것일까? 이에 대해서는 많은 사람들이 정확한 개념을 갖고 있지 못하는 것 같다. ‘나를 알고 적을 안다면 100전 100승이 되듯이’ DML 작업의 성능을 최적화하기 위해서는 DML 작업이 왜 성능을 저하시키는지를 알아야 할 것이다. DML 작업이 왜 성능을 저하시키는지 정확히 이해하지 못한다면 우리는 어떤 방법을 사용해도 성능을 향상시킬 수 없을 것이다.

 

그럼 첫 번째로 INSERT의 성능 저하를 확인해 보자. INSERT는 데이터를 저장하는 SQL 중 하나로 아래와 같은 이유에서 많은 데이터의 저장 시 성능을 저하시키게 된다.

 

·로그 기록
·HWM BUMP UP
·인덱스의 개수
·롤백을 위한 로그 기록
·디스크 I/O

 

INSERT 작업은 위와 같이 4가지 현상에 의해 성능이 저하된다. 이는 어떤 데이터베이스를 이용해도 동일하게 발생하는 현상이다. 데이터베이스는 작업의 수행도 중요하지만 작업이 실패하거나 또는 다른 장애에 의해 시스템이 재기동 되는 등의 데이터베이스 장애에 대해 데이터를 보호해야 하는 중요한 책임을 가지고 있다.

 

이와 같은 이유에서 실제 데이터베이스에서 INSERT 작업을 수행하기 전에 어떤 작업을 수행하는지에 대한 로그를 기록해야 한다. 이와 같은 기법을 선 로그(LOG AHEAD) 기법이라고 한다. 실제 INSERT를 수행하기 전에 로그를 기록하기 때문에 우리는 언제든지 INSERT 작업 중 데이터베이스에 문제가 발생해도 복구가 가능하게 되는 것이다. 실제 INSERT 작업과 관계없는 로그를 기록해야 하기 때문에 INSERT의 성능은 저하된다.

 

그렇다면 HWM BUMP UP에 의한 성능 저하는 무엇을 의미하는 것인가? HWM BUMP UP은 오라클 데이터베이스의 내부적인 요소이다. 실제 INSERT를 수행하게 되면 해당 테이블에 할당되어 있는 공간에 데이터를 저장하게 되며 해당 공간을 익스텐트라고 부르게 된다. 익스텐트에는 HWM가 설정되어 있어 데이터는 HWM 앞의 블록에만 저장된다. 이 뜻은 무엇을 의미하는 것인가?

 

HWM 앞까지 데이터를 저장한 후에는 HWM가 뒤로 후진해야만 데이터를 INSERT할 수 있다는 의미가 된다. 이를 HWM BUMP UP이라 하며 많은 데이터를 INSERT하게 되면 HWM BUMP UP은 많은 횟수가 발생하게 될 것이다. 하지만 HWM BUMP UP은 고비용의 내부적인 작업이다. 따라서 대용량의 데이터를 저장한다면 HWM BUMP UP의 횟수 증가로 INSERT의 성능은 저하된다.

 

INSERT의 속도와 인덱스의 개수는 INSERT의 성능 향상을 위해 매우 중요한 요소이다. 데이터를 테이블에 저장하는 것은 여유 공간을 가지고 있는 데이터 블록에 해당 데이터를 저장하면 된다. 하지만 인덱스에는 정해진 위치가 존재하게 되므로 정해진 위치를 찾는 프로세스가 수행된다. 따라서 해당 테이블에 인덱스가 10개라면 이와 같이 저장되는 데이터에 대해 인덱스에서의 위치를 찾기 위해 정해진 위치를 찾는 프로세스가 10번 수행되어야 할 것이다. 이와 같기 때문에 인덱스의 개수가 많다면 INSERT의 성능이 저하되는 것은 당연한 사실일 것이다.

 

어떤 사이트에서 어떤 테이블에 10개의 인덱스가 존재했으며 이를 최적화하여 5개의 인덱스로 변경한 적이 있다. 단지 10개의 인덱스를 5개로 감소시키는 순간 SQL의 변경 없이 INSERT 작업은 4배정도의 성능이 향상되었다. 이는 4배의 성능 향상이 중요한 것이 아니라 인덱스가 INSERT 작업에 많은 부하를 발생시킨다는 중요한 사실을 우리에게 전해주는 것일 것이다.

 

롤백을 위한 로그 기록은 해당 작업을 수행한 후 작업을 취소하는 경우 이전 데이터로 복구하기 위해 이전 데이터의 값을 저장하는 것을 의미한다. 이와 같은 작업 또한 실제 데이터를 저장하는 작업과는 별개로 수행되므로 INSERT의 성능 저하를 발생시키게 된다.

 

HWM BUMP UP을 제외한 로그 기록, 인덱스 및 롤백을 위한 로그 기록은 모두 디스크 I/O를 발생시킨다. 또한, 실제 데이터를 저장하는 작업에서도 디스크 I/O가 발생하게 된다. 이와 같이 모든 단계에서 디스크 I/O가 발생하기 때문에 INSERT의 성능은 저하될 것이다.

 

두 번째로 UPDATE의 성능 저하를 확인해 보자. UPDATE는 이미 저장되어 있는 데이터에 대해 변경 작업을 수행하는 것이다. 이와 같은 UPDATE는 아래와 같은 요소에 의해 성능 저하가 발생하게 된다.

 

·로그 기록
·UPDATE 컬럼이 사용된 인덱스의 개수
·롤백을 위한 로그 기록
·디스크 I/O

 

UPDATE의 경우에는 HWM BUMP UP은 발생하지 않게 된다. 또한, 인덱스의 개수도 해당 테이블에 존재하는 모든 인덱스는 아니며 UPDATE가 수행되는 컬럼이 사용된 인덱스의 개수를 의미하게 된다. UPDATE가 수행되면 해당 컬럼을 인덱스의 컬럼으로 구성하고 있는 인덱스만을 갱신하게 된다. 그렇기 때문에 UPDATE 컬럼이 사용된 인덱스의 개수에 의해 UPDATE 성능은 저하된다.

 

INSERT에 비해 UPDATE는 성능을 저하시키는 항목이 더 적다. 하지만, 동일한 양에 대해 INSERT와 UPDATE를 수행한다면 UPDATE가 성능 저하를 더 많이 발생시키게 된다. 이와 같은 이유는 왜일까? 분명히 성능 저하의 요소는 INSERT가 더 많기 때문에 INSERT가 더 많은 부하를 발생시킨다고 생각하기 쉽다. 이는 로그 기록과 롤백을 위한 로그 기록의 방식 차이 때문이다. INSERT 작업은 로그에 이전 데이터라는 것은 존재하지 않는다.

 

INSERT 작업이 수행된 데이터의 위치 정보만을 가지게 된다면 우리는 언제든지 롤백을 수행할 수 있으며 장애 시 복구도 어렵지 않게 된다. 하지만, UPDATE의 경우에는 이전 데이터의 값이 존재하기 때문에 이전 데이터를 로그에 기록하게 된다. 따라서 로그 기록 및 롤백을 위한 로그 기록에서 UPDATE가 INSERT에 비해 더 많은 데이터를 기록해야 하므로 디스크 I/O의 증가로 UPDATE의 성능은 INSERT의 성능보다 더욱 저하되게 된다.

 

세 번째로 DELETE 작업은 어떠한가? DELETE는 저장되어 있는 데이터를 삭제하는 기능을 수행하게 된다. 위와 같은 DELETE는 아래와 같은 항목들에 의해 성능이 저하된다.

 

·로그 기록
·인덱스의 개수
·롤백을 위한 로그 기록
·디스크 I/O

 

다른 항목은 INSERT 또는 UPDATE와 동일하다. 차이라면 로그 기록 및 롤백을 위한 로그 기록시 삭제되는 데이터의 이전 데이터와 이후 데이터를 모두 기록해야 한다는 것이다. 물론, 롤백을 위한 로그 기록 시에는 이전 데이터의 값과 위치 정보만을 가지게 된다. 결국, 이와 같은 이유로 동일한 양의 데이터를 DELETE하는 경우 INSERT에 비해 더 많은 디스크 I/O가 발생하게 되므로 성능은 저하되게 된다.

 

다양한 성능 저하의 요소를 가지는 DML 작업에서 대용량의 데이터에 대해서 어떤 방식으로 작업을 수행해야만 성능을 보장 받을 수 있겠는가?

 

 

INSERT의 성능 저하 요소를 감소시키자

 

 

일반적으로 DELETE 또는 UPDATE의 경우에는 INDEX의 개수만을 최적화하여 성능을 향상시킬 수 있다. 하지만, INSERT의 경우에는 위와 같은 성능 저하의 요소 중 아래와 같은 요소에 대해 획기적으로 감소시킬 수 있게 된다.

 

·로그 기록
·HWM BUMP UP
·롤백을 위한 로그 기록

 

위와 성능 저하 요소는 우리가 조금만 깊이 있게 고려한다면 최소화 시킬 수 있으며 위의 항목을 최소화 시킨다면 디스크 I/O는 자동으로 감소하게 된다. 그렇다면 어떻게 위의 성능 저하의 요소를 감소시킬 수 있겠는가?

 

첫 번째로 HWM BUMP UP과 롤백을 위한 로그 기록을 감소시키는 방법을 확인해보자. HWM BUMP UP을 제거하기 위해서는 HWM를 이동시키지 않고 데이터를 HWM 뒤에 존재하는 블록에 저장하면 될 것이다. 이와 같다면 롤백 또한 이전 데이터의 정보를 하나 하나 기록하는 것이 아니라 HWM의 위치 정보 하나만을 기록한다면 롤백 수행 시 HWM 위치 뒤에 존재하는 모든 블록을 제거한다면 롤백을 수행한 것과 동일한 현상이 발생할 것이다.

 

결국, HWM를 고정시키고 데이터를 HWM 뒤에 존재하는 블록에 저장시킨다면 HWM BUMP UP과 롤백을 위한 로그 기록에 의해 발생하는 성능 저하는 해결될 수 있을 것이다. 이와 같이 INSERT를 수행하는 방법이 바로 직접 로딩(DIRECT LOADING) 방식이다. 직접 로딩 방식을 사용한다면 HWM 뒤의 블록에 데이터를 저장하게 되므로 두 가지 문제는 모두 해결될 수 있을 것이다. 그렇다면 직접 로딩은 어떻게 사용하는 것인가?

 

·INSERT /*+ APPEND */ …… SELECT ……

 

위와 같이 SELECT를 수행하여 해당 데이터를 테이블에 INSERT하는 경우에 APPEND 힌트를 사용하여 직접 로딩을 수행하게 된다.

 

두 번째로 로그 기록을 확인해 보자. 우리가 테이블에 데이터를 저장하는 경우 일반적으로는 LOGGING 상태이므로 앞서 언급한 모든 로그를 기록하게 된다. 하지만, 직접 로딩(DIRECT LOADING) 기법을 사용하게 된다면 HWM 뒤에 존재하는 블록에 데이터를 저장하게 되므로 별도의 로그를 기록하지 않아도 복구 시 HWM 뒤의 블록에 존재하는 데이터를 제거하면 될 것이다. 이와 같은 이유에서 직접 로딩 방식의 경우에는 로그를 기록하지 않는 NOLOGGING 방식으로 데이터를 저장할 수 있게 된다.

 

결국, 위와 같이 NOLOGGING 상태에서 직접 로딩을 수행한다면 앞서 언급한 3가지의 성능 저하 요소를 대부분 제거할 수 있으며 이로 인해 디스크 I/O는 감소하게 된다. 상황에 따라 다르지만 대용량의 데이터에 대해 NOLOGGING 상태의 직접 로딩은 일반 INSERT에 비해 10배 이상 성능을 향상시킬 수도 있으며 그 이상의 성능 향상을 기대할 수도 있다.

 

 

DELETE와 UPDATE의 성능을 최적화하자

 

 

대용량의 데이터에 대해 DELETE 또는 UPDATE를 수행한다면 엄청난 성능 저하가 발생할 수 있다. DELETE 또는 UPDATE는 INSERT에 비해 더 많은 자원을 사용하게 되며 직접 로딩 또는 NOLOGGING 상태와 같은 방법이 존재하지 않게 된다. 많은 데이터에 대해서는 과거에나 지금이나 성능 저하를 감수할 수 밖에는 없을 것이다. 하지만, 이와 같은 대용량의 DELETE 또는 UPDATE에 대해서도 성능을 최적화 시키는 방법은 존재한다.

 

우리는 데이터를 변경하기 위해서는 항상 UPDATE를 사용해야 한다고 생각하고 데이터를 삭제하기 위해서는 항상 DELETE를 수행해야 한다고 생각한다. 일반적으로 생각한다면 당연한 사실일 것이다. 하지만, 이와 같은 고정 관념으로는 대용량의 데이터에서 더 이상의 성능 향상을 기대할 수 없을 것이다.

 

대용량의 데이터에 대해 UPDATE 또는 DELETE를 수행하는 경우 최적의 성능을 보장 받기 위해서는 기존의 사고 방식에서 벗어나야 할 것이다. 결국, 생각하는 방식의 전환만이 대용량의 데이터에 대해 UPDATE와 DELETE의 성능을 최적화하는 유일한 방법이 될 것이다.

 

대용량의 데이터에 대해 데이터의 변경에 대해 UPDATE로 작업을 수행하지 말고 데이터의 삭제에 대해 DELETE로 작업을 수행하지 말아야 한다. UPDATE는 INSERT로 변경하고 DELETE 또한 INSERT로 변경하는 순간 최적의 성능을 기대할 수 있을 것이다. UPDATE를 INSERT로 DELETE를 INSERT로 수행하는 것이야말로 우리의 기존 고정 관념을 파괴하는 행위일 것이다. 이제는 이와 같은 기존의 고정 관념을 파괴하여 성능을 최적화해야 할 것이다.

 

그렇다면 어떤 이유에서 UPDATE 또는 DELETE 대신 INSERT를 사용해야 하는가? 이유는 간단하다. INSERT는 UPDATE와 DELETE와는 달리 직접 로딩과 NOLOGGING이 가능하기 때문이다. 이와 같은 성능 향상의 요소는 우리에게 엄청난 혜택을 제공하기 때문이다.

 

그렇다면 어떻게 UPDATE를 INSERT로 변경하고 DELETE를 INSERT로 변경할 수 있겠는가? 물론, 데이터 삭제에 INSERT를 사용해야 하기 때문에 작업 절차는 복잡해 질 수 있다. 예를 들어, TEST 테이블의 크기가 100GB이며 그 중 50GB에 해당하는 데이터를 삭제해야 한다고 가정하자. 그렇다면 DELETE를 수행하는 순간 우리는 엄청난 시간을 기다려야 해당 작업을 종료할 수 있을 것이다.

 

DELETE를 INSERT로 변경한다면 어떻게 되겠는가? 우선, TEST 테이블과 동일 구조의 TEST_IMSI 테이블을 생성한 후 TEST 테이블로부터 데이터가 삭제된 후 남게 되는 데이터만을 조회하여 TEST_IMSI 테이블에 INSERT를 수행한다. 해당 작업에는 TEST_IMSI 테이블을 NOLOGGING 상태로 변경한 후 해당 테이블에 직접 로딩을 수행해야 할 것이다. 고성능의 디스크를 사용하는 시스템이라면 1GB에 1분 정동의 INSERT 시간이 소요된다.

 

50GB의 데이터를 INSERT하면 되므로 최적화된다면 50분 정도의 시간이 소요될 것이다. 이 얼마나 빠른 속도인가? 물론, 경우에 따라 병렬 프로세싱을 이용해야 할 수 도 있다. 이와 같이 작업을 수행했다고 모든 것이 종료되는 것은 아니다. TEST 테이블을 TEST_BACKUP으로 이름을 변경하고 TEST_IMSI 테이블을 TEST 테이블로 이름을 변경해야 할 것이다. 물론, 인덱스가 필요하다면 인덱스도 생성해야 할 것이다.

 

이와 같이 작업 절차는 복잡해 지지만 DELETE를 수행하는 것보다는 10배 아니 그 이상의 성능 향상을 기대할 수 있을 것이다. UPDATE의 경우도 이와 다르지 않다. UPDATE 후의 데이터를 임시 테이블에 INSERT를 수행하고 해당 테이블의 이름을 변경한다면 기존 테이블에는 UPDATE 후의 데이터가 저장되므로 INSERT를 이용하여 UPDATE를 대신할 수 있게 된다.

 

이와 같은 방식으로 기존의 방식에서 벗어날 수 있다면 우리는 최적의 성능을 기대할 수 있을 것이다.

 

우리가 가지고 있는 기존의 방식을 버린다는 것은 쉬운 일이 아닐 것이다. 하지만, 더 좋은 방법이 있다면 과감히 새로운 방법을 선택하는 것도 필요할 것이다. 데이터의 갱신과 제거를 UPDATE와 DELETE로 구현하는 것이 아니라 INSERT로 구현한다면 많은 사람들이 의아해 할지도 모른다. 하지만, 분명히 가능한 일이며 이를 통해 우리는 INSERT의 최고의 아키텍쳐인 NOLOGGING과 직접 로딩을 이용할 수 있다는 것을 명심하길 바란다.

 

 

제공 : DB포탈사이트 DBguide.net

[출처] DML의 INSERT 성능 향상|작성자 놀고놀자


반응형
저작자표시 비영리

'ORACLE > TUNING' 카테고리의 다른 글

Oracle 10g에서 Index 힌트의 변화  (0) 2009.02.09
ORACLE HINT 정리  (0) 2009.02.09
db_file_multiblock_read_count 파라미터의 몰락...  (0) 2009.02.07
[Hint] JOIN plan explain trace (NESTED LOOP, SORT MERGE, HASH JOIN ) use_nl, use_merge, use_hash  (0) 2009.02.06
Nested Loop Join과 Sort Merge Join  (0) 2009.02.06
Posted by [PineTree]
ORACLE/OWI2009. 2. 8. 12:19

Direct path read

반응형

Direct path read

 

 

목차

  • 1 Basic Info
  • 2 Parameter & Wait Time
    • 2.1 Wait Parameters
    • 2.2 Wait Time
  • 3 Check Point & Solution
    • 3.1 Parallel Query의 성능을 높인다
    • 3.2 I/O 시스템L의 성능을 높인다.
      • 3.2.1 I/O 개요
      • 3.2.2 어플리케이션 레이어(Application Layer)
      • 3.2.3 오라클 메모리 레이어(Oracle Memory Layer)
      • 3.2.4 오라클 세그먼트 레이어(Oracle Segment Layer)
      • 3.2.5 OS/디바이스 레이어(Device Layer)
      • 3.2.6 Direct Path I/O
    • 3.3 _DB_FILE_DIRECT_IO_COUNT 의 조정
  • 4 Event Tip
    • 4.1 direct path read와 undo
    • 4.2 데이터 파일에 대한 direct path read의 증명
    • 4.3 DB_FILE_DIRECT_IO_COUNT
    • 4.4 Direct Read I/O크기 알기

 

Basic Info

 

direct path read 이벤트대기는 Parallel Query 수행시 슬레이브 세션(Slave Session)이 수행하는 direct path I/O 에 의해 발생한다. direct path I/O 는 SGA 내의 버퍼캐쉬를 거치지 않고 세션의 PGA 로 직접 블록을 읽어 들이는 것으로 direct read 는 I/O 방식(synchronous I/O, asynchronous I/O)에 상관없이 수행될 수 있으나, 하드웨어 플랫폼과 DISK_ASYNCH_IO 파라미터에 영향을 받는다. Direct read I/O 는 일반적으로 디스크에 위치한 임시(temporary) 세그먼트를 액세스 하는 동안 사용된다. 이러한 작업은 정렬(sort), 병렬조회(parallel query) 및 해쉬조인(hash join)시에 발생한다.

 

슬레이브 세션이 direct path read 를 수행하는 동안 코디네이터 세션(Coordinator Session)은 슬레이브 세션으로부터 응답이 오기를 기다리며 PX Deq: Excute Reply 이벤트를 대기하는 것으로 관찰된다. Parallel Query 수행시 발생하는 direct path read 대기는 필연적인 것이다. 만일 direct path read 이벤트의 대기시간이 지나치게 높게 나온다면 다음과 같은 관점에서 튜닝포인트를 찾아보아야 한다.


이 이벤트의 대기횟수와 대기시간은 오해의 소지가 있을 수 있다. 만일 비동기식 I/O가 사용되지 않았다면, 세션은 I/O 가 완료될 때까지 대기한다. 하지만, I/O 요청이 시작된 시점부터 대기시간을 계산하지 않고, I/O 요청이 완료된 후 데이터를 액세스 할 때 direct path read 대기이벤트를 기다리게 된다. 따라서 대기시간은 상당히 짧게 나타난다.

 

만일 비동기식 I/O 가 사용가능하고 현재 사용 중이라면, 세션은 다수의 direct read 요청을 한 후 , PGA 내부에 캐쉬 된 블록들에 대한 처리를 진행한다. 세션이 PGA내부에 캐쉬 된 블록들이 없어서 처리를 진행하지 못하는 시점에 direct path read 대기이벤트가 발생된다. 따라서, 읽기 요청횟수는 대기횟수와 동일하지 않다. 이러한 불일치 때문에, V$SYSTEM_EVENT 와 V$SESSION_EVENT 뷰에서 보여지는 direct path read 대기이벤트의 수치는 신뢰할 수 없다.

 

LOB 세그먼트를 읽을 때 발생되는 direct path read 대기는, 오라클 8.1.7부터는 direct path read(lob) 대기이벤트로 별도로 구분된다. 보통 direct path read 대기이벤트는 임시(temporary) 또는 일반 테이블스페이스로부터 direct read 오퍼레이션을 수행하는 SQL문에 의해 발생된다. ORDER BY, GROUP BY, UNION, DISTINCT, ROLLUP과 같이 정렬이 필요한 함수를 수행하는 SQL문은, PGA 내부에서 허용할 수 있는 것 보다 더 큰 데이터를 정렬해야 할 때 소트 런(sort run)들을 임시 테이블스페이스에 기록한다. 임시 테이블스페이스의 소트 런들은 최종 결과를 만들기 위해 순차적으로 PGA로 읽혀지고 머지 된다. 소트 런들을 PGA로 읽어 들이는 동안 해당 세션은 direct path read 대기이벤트를 대기한다. Hint또는 옵티마이저의 판단에 의해 MERGE 조인을 수행하는 SQL문 또한 정렬작업이 필요하다.

 

힌트(hint)또는 옵티마이저의 판단에 의해HASH 조인을 수행하는 SQL문은 PGA 내부에서 허용할 수 없는 크기의 해쉬 파티션들을 임시 테이블스페이스에 기록한다. 임시 테이블스페이스에 기록된 해쉬 파티션은 SQL문의 조건에 맞는 레코드를 찾기 위해 다시 PGA로 읽어 들여진다. 해쉬 파티션을 PGA로 읽어 들이는 동안 해당 세션은 direct path read 대기이벤트를 대기한다.

 

V$SESSION_EVENT 뷰의 TOTAL_WAITS 또는 TIME_WAITED 값을 이용하여 direct path read 대기이벤트를 평가하지 않는 것이 좋다. 대신에 아래의 쿼리를 사용하여 대량의 direct read 를 수행하고 있는 세션을 찾아낼 수 있다. physical reads direct 는 parent세션에 의해 시작된 direct reads와 parent세션이 관리하는 슬레이브 프로세스에 의해 발생된 모든 direct reads 의 합으로 구성된다. 

select a.name, b.sid, b.value, 

       round((sysdate - c.logon_time) * 24) hours_connected

from   v$statname a, v$sesstat b, v$session c

where  b.sid        = c.sid

and    a.statistic# = b.statistic#

and    b.value      > 0

and    a.name       = 'physical reads direct'

order by b.value;

NAME                            SID      VALUE   HOURS_CONNECTED

-------------------------     ----     ---------- ------------------------

physical reads direct        2               41             980

physical reads direct        4               41             980

physical reads direct        5        445186             980

 

대량의 direct reads 를 발생시키는 세션을 찾는 것 이외에, 세션이 데이터를 읽어오는 위치 (임시 테이블스페이스, 데이터파일 등), 대기를 발생시키는 SQL문을 파악하여야 한다. 다음의 쿼리는 이것에 대한 답을 줄 수 있다. 임시 테이블스페이스에서 데이터를 읽는 세션은 소트 또는 해쉬 세그먼트를 읽고 있을 것이다. 데이터파일로부터 데이터를 읽고 있는 세션은 병렬 쿼리 슬레이브(parallel query slave)이다. 

select a.event,

       a.sid, 

       c.sql_hash_value hash_value,

       decode(d.ktssosegt,

              1,'SORT', 2,'HASH',    3,'DATA',

              4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',

              null) as segment_type,

       b.tablespace_name,

       b.file_name

from   v$session_wait a, dba_data_files b, v$session c, x$ktsso d

where  c.saddr      = d.ktssoses(+)

and    c.serial#    = d.ktssosno(+)

and    d.inst_id(+) = userenv('instance')

and    a.sid        = c.sid

and    a.p1         = b.file_id

and    a.event      = 'direct path read'

union all

select a.event,

       a.sid,

       d.sql_hash_value hash_value,

       decode(e.ktssosegt,

              1,'SORT', 2,'HASH',    3,'DATA',

              4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',

              null) as segment_type,

       b.tablespace_name,

       b.file_name

from   v$session_wait a, dba_temp_files b, v$parameter c, 

       v$session d, x$ktsso e

where  d.saddr      = e.ktssoses(+)

and    d.serial#    = e.ktssosno(+)

and    e.inst_id(+) = userenv('instance')

and    a.sid        = d.sid

and    b.file_id    = a.p1 - c.value

and    c.name       = 'db_files'

and    a.event      = 'direct path read'

order by 1,2;

EVENT                SID HASH_VALUE  SEGMENT  TABLESPACE_N   FILE_NAME

------------------   ---- ----------------  ----------- ------------------  -----------------

direct path read     8     511952958      SORT      TEMP_BATCH   temp_batch_01.dbf

direct path read     9    3138787393                    ORDERS         orders_01.dbf

direct path read    11   3138787393                    ORDERS         orders_01.dbf

direct path read    12   3138787393                    ORDERS         orders_01.dbf

direct path read    14   3138787393                    ORDERS         orders_01.dbf

 

임시 테이블스페이스로부터 소트 세그먼트를 읽고 있는 세션은, SORT_AREA_SIZE (또는 오라클 9i에서 PGA_AGGREGATE_TARGET 을 사용하는 경우 work area size )가 메모리 소트를 수행할 만큼 충분치 않다는 것을 의미한다. 하지만 이것은 문제가 되지 않는다. 모든 소트가 메모리에서만 수행되는 것은 현실적으로 불가능하기 때문이다. 하지만, 많은 멀티패스( multi pass ) 소트는 가능한 방지해야 한다. 왜냐하면, 멀티패스 소트는 임시 테이블스페이스에 대한 상당한 I/O 를 유발하며 매우 느리기 때문이다. SQL문이 멀티패스 소트를 하는지 어떻게 확인할 수 있을까? 오라클 9i이전 버전에서는 쉽지 않은 일이다. 10032 트레이스 이벤트를 설정한 후 트레이스파일을 확인해야만 한다. 하지만, 오라클 9i부터는, 소트를 수행하는 SQL문의 hash value를 이용하여 V$SQL_WORKAREA 또는 V$SQL_WORKAREA_ACTIVE 뷰를 조회해 보는 것만으로도 확인이 가능하다. 소트에 대한 더욱 자세한 사항은 International Oracle Users Group (IOUG) 2004 conference proceedings (www.ioug.org) 의 “If Your Memory Serves You Right” 기술백서를 참고하라.

 

이러한 경우, 튜닝의 목표는 디스크 소트 횟수를 최소화하는 것이다. SORT_AREA_SIZE (또는 PGA_AGGREGATE_TARGET )을 크기를 증가함으로써 디스크 소트 횟수를 줄일 수 있다. 하지만, 이것은 극단적으로 SORT_AREA_SIZE 가 작게 설정된 경우가 아니라면 근본적인 해결방법이라고는 할 수 없다. 먼저 어플리케이션에서 소트가 반드시 필요한지를 확인해야 한다. 어플리케이션들은 DISTINCT와 UNION 함수를 남용하여 사용하는 경향이 있다. 가능하다면 UNION보다는 UNION ALL을 사용하고, SORT MERGE 보다는 HASH 조인, HASH 조인보다는 NESTED LOOPS조인을 사용하도록 해야 한다. 또한 옵티마이저가 드라이빙 테이블을 올바르게 선택했는지도 확인할 필요가 있다. 결합 인덱스의 컬럼을 자주 쓰이는 ORDER BY절과 잘 맞게 설정해 놓으면 소트를 피할 수 도 있다. 오라클 9i라면 PGA_AGGREGATE_TARGET 을 사용하여 SQL work area 를 자동적으로 할당 받아 사용할 수 있도록 설정하는 것도 고려해 볼 만 하다. 통계적으로 보았을 때, 자동적으로 메모리관리를 하도록 하면 메모리소트의 비율을 더 높여준다.

 

세그먼트를 읽고 있는 세션을 발견했다면, HASH_AREA_SIZE (오라클 9i 에서 PGA_AGGREGATE_TARGET 을 사용하고 있는 경우는 work area size )가 작아서 메모리에 해쉬 테이블을 수용하지 못하는 경우이다. 해결방법은 이미 언급됐던 것과 유사하다. 만약 HASH_AREA_SIZE 가 너무 작은 경우만 아니라면 HASH_AREA_SIZE (또는 PGA_AGGREGATE_TARGET )를 조정하기 전에 먼저 어플리케이션과 SQL문을 튜닝해야 한다.

 

만약에 병렬 쿼리(parallel query) 슬레이브에서 direct reads가 발생한다면, 병렬 스캔(parallel scan)이 parent SQL문에 적합한지와 슬레이브의 개수가 적당한지 확인해야 한다. 또한 쿼리 슬레이브들이 시스템의 CPU와 디스크 자원을 모두 써버리지는 않는지도 확인해야 한다. Parent SQL문의 hash value와 쿼리 슬레이브들이 수행하는 SQL의 hash value가 동일하지 않기 때문에 parent SQL문을 찾는 것은 쉽지 않은 일이다. 오라클 8.1.5에서 V$PX_SESSION 뷰가 소개되기 전까지는 더욱 힘든 일이었다. 아래의 2가지 예제는, 병렬 쿼리가 수행될 때 parent SQL문장을 찾는 방법을 오라클 8.1.5이전 버전과 이후 버전에 대해서 각각 설명한다. 

--오라클 8.1.5이전 버전

--Note: 아래의 쿼리는 SYS 유저에 의해 수행되는 병렬 쿼리 문장들을 구분할 수 없다

--왜냐하면, SYS 유저들은 동일한 AUDSID를 공유하기 때문이다.

select decode(ownerid,2147483644,'PARENT','CHILD') stmt_level,

         audsid,

         sid,

         serial#,

         username,

         osuser,

         process,

         sql_hash_value hash_value,

         sql_address

from  v$session

where  type <> 'BACKGROUND'

and    audsid in (select audsid 

                        from   v$session

                       group by audsid

                      having count(*) > 1)

order by audsid, stmt_level desc, sid, username, osuser;

STMT_L   AUDSID  SID  SERIAL# USERNAME OSUSER  PROCESS HASH_VALUE SQL_ADDR

------       --------   ----  ----------  ------------- ---------- ----------- ---------------- --------

PARENT  3086501   20     779        INTREPID  cdh8455  16537     3663187692   A0938E54

CHILD    3086501   12     841        INTREPID  cdh8455  16544     817802256     A092E1CC

CHILD    3086501   14    2241       INTREPID  cdh8455  16546     817802256     A092E1CC

CHILD    3086501   17    3617       INTREPID  cdh8455  16540     817802256     A092E1CC

CHILD    3086501   21     370        INTREPID  cdh8455  16542     817802256     A092E1CC

아래의 쿼리는 오라클 8.1.5이상 버전에서 사용된다.

select decode(a.qcserial#, null, 'PARENT', 'CHILD') stmt_level,

         a.sid,

         a.serial#,

         b.username,

         b.osuser,

         b.sql_hash_value,

         b.sql_address,

         a.degree,

         a.req_degree

  from   v$px_session a, v$session b

where  a.sid = b.sid

 order by a.qcsid, stmt_level desc;

STMT_L   SID SERIAL# USERNAME OSUSER  HASH_VALUE SQL_ADDR        DEG REQ_DEG

---------   ----- --------  -------------  ---------     -------------    --------------  ------ -------

PARENT    20     779     INTREPID   cdh8455    3663187692    A0938E54

CHILD      17   3617     INTREPID   cdh8455      817802256    A092E1CC       4       4

CHILD      21     370     INTREPID   cdh8455      817802256    A092E1CC      4       4

CHILD      12     841     INTREPID   cdh8455      817802256    A092E1CC      4       4

CHILD      14   2241     INTREPID   cdh8455      817802256    A092E1CC      4       4

Parameter & Wait Time

Wait Parameters

Direct path read 대기이벤트의 대기 파라미터는 다음과 같다.

  • P1 : Absolute File#
  • P2 : Starting Block#
  • P3 : 블록수

Wait Time

 

I/O관련 이벤트이므로 타임아웃이 발생하지 않으며, 세션은 I/O가 완료될 때까지 대기한다

 

Check Point & Solution

 

Parallel Query의 성능을 높인다

 

Parallel Query를 수행하는 과정에서의 direct path read 대기는 필연적인 것으로 이 대기 자체를 튜닝하는 것은 불가능하다. 오히려 SQL 튜닝을 통해 Parallel Query 자체의 성능을 개선시키는 것이 옳은 접근 방법이다. 시스템의 용량에 비해 불필요하게 Parallel Query를 수행하는 것은 오히려 성능을 저하시키는 요인이 된다. 한가지 기억할 것은 데이터 파일에 대해 직접 읽기 작업을 수행하기 전에 읽기의 대상이 되는 객체의 더티 블록이 데이터 파일에 기록이 되어야 한다는 것이다. 즉 체크포인트가 발생하게 된다. 이 작업을 수행하는 동안 코디네이터 세션은 enq: TC - contention 대기를 겪게 된다.

 

I/O 시스템의 성능을 높인다.

 

I/O 개요

 

I/O는 오라클에서 가장 중요한 자원이다. 오라클이 제공하는 모든 기능은 결국 어떻게 하면 데이터를 파일에 잘 쓰고 잘 읽느냐에 관한 것이다. 따라서 자연스럽게 오라클 성능문제 중 많은 수가 I/O와 관련이 있다. I/O와 관련된 오라클의 성능문제를 이해하려면 오라클에서의 I/O 작업은 여러 개의 레이어(Layer)로 이루어져 있다는 사실을 이해해야 한다. 오라클에서의 I/O 작업 레이어를 다음과 같이 나누어 설명해 보기로 하자.

 

1. 어플리케이션 레이어 : select/insert/update/delete/truncate...

2. 오라클 메모리 레이어: Buffer cache | PGA

3. 오라클 세그먼트 레이어: Datafile, tempfile, Tablespace, Segment

4. OS/디바이스 레이어: Asynch I/O, Direct I/O, Raw device, RAID, ...

 

I/O 성능문제의 원인을 파악하는 것과 해결책을 찾는 과정은 항상 1 -> 2 -> 3 -> 4 의 순서를 따라야 한다. 논리적인 면에서뿐만 아니라 경제적인 면에서 더욱 그렇다. 각각의 레이어에 대해 필요한 사전지식과 메커니즘에 대해서 논의해보자.

 

어플리케이션 레이어(Application Layer)

 

어플리케이션을 효과적으로 구현하여, 불필요한 I/O를 최소화해야 한다. 비효율적인 어플리케이션을 그대로 두고 시스템을 튜닝한다는 것은 거의 불가능하다. 오라클은 I/O를 효율적으로 처리하는 다양한 기법들을 제공한다. Parallel Query, Parallel DML, Nologging, Direct load, Direct read 등이 대표적인 예이다. 오라클의 버전이 올라갈수록 더욱 효과적으로 작업을 처리할 수 있는 강력한 SQL 기능이 추가된다. 해석함수( Analytical Function )가 대표적인 경우인데, 이 함수를 잘 이용하면 I/O를 크게 줄이면서 다양한 집계기능을 구현할 수 있다. 이러한 최신의 SQL 문장을 사용하여 I/O부하를 줄이도록 해야 한다. 데이터의 성격에 따라 클러스터(Cluster), IOT, 파티셔닝(Partitioning), 비트맵 인덱스(Bitmap Index)등의 기능을 적절히 사용해서 I/O를 효과적으로 사용하는 것 또한 어플리케이션의 몫이다.

 

오라클 메모리 레이어(Oracle Memory Layer)

 

버퍼 캐시는 오라클 I/O 관리의 핵심이다. 자주 사용하는 블록들을 메모리에 캐시함으로써 물리적인 I/O를 줄일 수 있다. 오라클의 버전이 올라감에 따라 버퍼 캐시를 처리하는 알고리즘이 끊임없이 개선되고, 더불어 새로운 관리 방법들이 제공된다. 버퍼 캐시를 효과적으로 사용하게 되면 물리적 I/O가 줄어들고 자연스럽게 I/O 성능문제가 해결되는 경우가 많다. 오라클이 제공하는 기능들에는 다음과 같은 것들이 있다.

  • 첫째, Touch count 기반의 효율적인 LRU 알고리즘을 제공한다.
  • 둘째, Buffer Pinning 기법을 통해 불필요한 래치 경합을 줄이고, 현재의 읽기 작업에 사용될 확률이 높은 블록들을 메모리에서 밀려나지 않게끔 한다.
  • 셋째, 다중 버퍼 풀( Multiple buffer pool ) 기능을 이용하면 휘발성 블록과 메모리 상주 블록을 구분해서 효과적으로 관리할 수 있다. 시스템에서 보편적으로 자주 사용되는 객체들은 Default 버퍼를 사용한다. 비교적 주기적으로 사용되는 작은 크기의 객체들은 Keep 버퍼에 상주시키는 것이 좋다. 반대로 아주 적은 빈도로 사용되는 큰 크기의 객체들은 Recycle 버퍼를 사용함으로써 중요한 메모리 영역을 낭비하는 일을 방지할 수 있다.
  • 넷째, 오라클 9i부터는 블록 크기를 2K ~ 32K까지 사용 가능하다. 객체의 속성을 고려하여 큰 사이즈의 블록을 사용하는 것이 유리한 경우(가령 로우의 크기가 크고 풀테이블스캔으로 데이터를 읽는 경우가 많은 경우)에는 큰 크기의 블록을 사용함으로써 성능 개선 효과를 얻을 수 있다.
  • 다섯째, 메모리에 올릴 필요가 없는 대용량의 데이터를 처리할 때는 버퍼 캐시를 우회하는 방법을 사용할 수 있다. 이러한 기능을 direct path I/O 라고 부른다. Direct path I/O 를 사용하면 SGA영역을 거치지 않기 때문에 메모리 공유를 위한 동기화 메커니즘이 불필요하고 그만큼 성능이 개선된다. Direct path I/O 의 반대말은 conventional path I/O 로 SGA 즉, 버퍼 캐시를 경유하는 것을 말한다. 오라클은 영구 세그먼트(Permanent Segment)와 임시 세그먼트(Temporary Segment) 모두에 대해 direct path I/O 를 지원한다. Parallel Query나 Parallel DML등은 영구 세그먼트에 대해 direct path I/O 를 사용한다. 정렬 작업은 임시 세그먼트에 대해 direct path I/O 를 사용한다. LOB 세그먼트는 약간 독특한 처리 메커니즘을 가지고 있는데, LOB 컬럼 생성시 부여하는 스토리지(Storage) 속성에 따라 direct path I/O 를 사용할 수도 있고, conventional path I/O 를 사용할 수도 있다.

오라클 세그먼트 레이어(Oracle Segment Layer)

 

일반적인 데이터들은 데이터파일에 저장된다. 임시 테이블스페이스(Temporary tablespace)를 사용할 경우, 기본적으로 데이터 파일이 아닌 임시파일(Temp file)에 데이터를 저장한다. 오라클 7.3 이전 버전에서는 정렬 작업을 위해 영구 테이블스페이스(Permanent Tablespace)를 사용해야 했는데 이 경우 과도한 익스텐트의 할당과 해제로 인해 많은 성능문제가 야기되며, 특히 ST 락 경합으로 인한 성능 저하 현상이 생기는 경우가 많았다. 오라클 8i부터 사용가능한 임시 테이블스페이스(Temporary tablespace)와 임시파일(Tempfile) 기능을 사용하면 ST 락 경합은 더 이상 문제가 되지 않는다. 오라클 8i부터 제공되는 LMT( Locally Managed Tablespace )와 오라클 9i부터 제공되는 ASSM( Automatic Segment Space Management )을 사용하면 익스텐트 및 세그먼트 공간의 부적절한 관리에서 오는 성능문제를 대부분 해결할 수 있다. 대용량의 테이블은 파티션(Partition)을 이용해서 관리하는 것이 유리한 경우가 많다. 관리적인 측면에서뿐만 아니라, 대량의 데이터를 처리하는 경우 원하는 범위만을 스캔하는 것이 가능하므로 필요한 I/O 범위를 줄이는 효과가 있다.

 

OS/디바이스 레이어(Device Layer)

 

오라클은 가능하면 비동기 I/O(Asynchronous IO)를 사용할 것을 권장한다. 비동기 I/O는 읽기 작업뿐만 아니라 특히 DBWR이나 LGWR등 쓰기작업을 수행하는 프로세스가 비동기적으로 작업을 처리할 수 있도록 해줌으로써 I/O 작업의 속도를 전반적으로 개선시켜준다. 불행히도 많은 OS들에서 진정한 비동기 I/O는 로디바이스(Raw device)에서만 사용한 것으로 알려져있다. 비동기 I/O를 사용하는 것이 불가능하다면 OS차원에서 Direct I/O를 사용하는 것이 바람직하다. Direct I/O를 사용하는 경우 OS의 버퍼 캐시를 우회함으로써 불필요한 I/O 작업을 최소화한다. DBWR 프로세스를 복수개로 사용하는 것 또한 방법이 될 수 있다. 컨트롤 파일(control file)의 개수나 리두로그 파일의 개수가 불필요하게 많다면 복구가 가능한 최소한만큼만 유지하는 것도 도움이 된다.


Direct I/O를 사용하는 경우에는 로디바이스를 사용할 필요가 없다는 견해가 많다. Direct I/O를 사용할 경우 OS의 버퍼 캐시를 경유하지 않으므로 그 작동 방식이 로디바이스와 거의 동일하기 때문이다. 하지만, 로디바이스가 I/O 성능개선의 중요한 방법이라는 사실 자체는 의심의 여지가 없는 것으로 보인다.


한가지 유념할 것은 로디바이스나 Direct I/O가 비효율적으로 과다하게 I/O를 수행하는 어플리케이션에 대한 무조건적인 해결책이 아니라는 사실이다. 가령 매우 비효율적인 I/O를 수행하는 어플리케이션의 속도를 개선시키기 위해 기존의 파일 시스템을 로디바이스로 수정했다고 가정하자. 어플리케이션의 성능이 개선될까? 불행하게도 그렇지 않을 확률이 있다. 로디바이스를 사용함으로써 I/O 작업 자체는 빨라지지만, OS에서 제공하는 버퍼 캐시를 사용하지 못하기 때문이다. 가령 파일 시스템을 사용할 경우에는 100만번의 Physical Read중 실제로는 10만번만이 실제 디스크 읽기를 유발하고, 나머지는 90만번은 OS의 버퍼 캐시에서 이루어질 수 있다. 이런 경우에 로디바이스를 사용하면 100만번의 Physical Read가 모두 디스크 읽기로 이어지기 때문에 오히려 어플케이션의 성능이 저하될 수도 있다. 어플리케이션의 적절한 튜닝이 항상 우선이며, I/O 시스템의 성능 개선은 I/O 시스템이 “실제로” 느린 경우에만 필요하다.


RAID로 구성된 I/O 시스템을 사용하는 경우에는 RAID 레벨(level)을 신중하게 선택해야 한다. 리두 로그 파일과 같이 쓰기작업이 왕성한 자원에 대해 RAID-5를 사용하는 것은 성능에 큰 문제를 일으킨다. 데이터파일의 경우에도 RAID-5는 문제를 일으키는 경우가 많다. 가능하면 RAID1+0이나 RAID0+1을 사용하는 것이 기본적인 권고안이다. 파일들을 물리적으로 분리시킴으로써 디스크간의 경합을 피하는 것 또한 중요하다. SAN과 같이 통합된 스토리지 시스템을 이용하는 경우에는 해당 엔지니어와의 협의를 통해 파일들간에 적절한 분산이 이루어지도록 설정할 필요가 있다. 또한 아카이브 모드로 데이터베이스를 운영하는 경우에는 리두 로그와 아카이브 로그간의 경합이 발생하지 않도록 구성해 주어야 한다.
I/O 시스템을 변경하는 것은 많은 시간와 돈을 요구하는 경우가 많기 때문에 항상 최후의 선택사항으로 남겨 두어야 한다. 1~4 단계만으로 문제가 해결되지 않는 경우에만 5단계의 튜닝을 고려하는 것이 바람직하다.

 

RAID의 정의에 대해서만 간단하게 알아보기로 하자. RAID는 Redundant Arrays of Inexpensive(Independent) Disks의 약자로 복수개의 디스크를 조합해서 I/O 시스템을 구성하는 기술을 말한다. 소프트웨어적으로 구현될 수도 있으나 성능 면에서는 하드웨어적으로 구현된 것이 훨씬 유리하다.

  • RAID0 : 스트라이핑(Striping). 데이터를 여러 개의 디스크에 분할 저장하도록 구성하는 것을 말한다. 데이터의 부하가 자동으로 분산되므로 성능면에서 유리하지만, 하나의 디스크만 고장나도 전체 I/O가 불가능해지는 단점이 있다.
  • RAID1 : 미러링(Mirroing). 여러 개의 디스크에 같은 데이터를 저장하는 기법을 말한다. 하나의 디스크가 고장나도 I/O에 아무런 장애가 없다는 점에서 신뢰성이 높다. 하지만 항상 원하는 용량의 2배에 해당하는 디스크가 필요하다는 단점이 있다.
  • RAID5 : 최소한 3개의 디스크를 사용해서 분산 저장하는 방식으로, 데이터 저장시 패리티(Parity) 비트를 저장해서 이후 하나의 디스크가 고장나도 패리티 비트를 이용해서 복구가 가능한 기법을 말한다. 디스크 활용도가 가장 높다고 할 수 있지만 패리티 저장에 따른 부하로 쓰기작업이 왕성한 경우 성능이 저하되는 문제점이 있다.

오라클은 데이터파일이나 컨트롤 파일, 리두 로그 파일에 대해서 RAID5보다는 RAID0+1 또는 RAID1+0을 사용할 것을 권장한다. RAID0+1은 물리적인 스트라이핑에 논리적인 미러링을 구현하는 것을 말하며, RAID1+0은 물리적인 미러링에 논리적인 스트라이핑을 구현하는 것을 말한다.


메타링크 문서번호 30286.1 에서 RAID 구성에 대한 자세한 정보를 제공한다.

 

Direct Path I/O

 

오라클의 I/O는 기본적으로 SGA(버퍼 캐시)를 경유한다. 하지만 특수한 상황에서는 SGA를 우회해서 PGA에 데이터를 올린다. 데이터를 공유할 필요가 없을때는 버퍼 캐시에 데이터를 적재하는 과정에서 발생하는 오버헤드를 피함으로써 성능을 개선하는 것이 가능하다. 버퍼 캐시내의 변경된 블록을 데이터파일에 기록하는 것은 DBWR 고유의 작업이다. 반면 버퍼 캐시를 우회하는 쓰기 작업은 개별 프로세스가 직접 수행하게 된다. 이처럼 버퍼 캐시를 우회하는 I/O 작업을 direct path I/O 라고 부른다. 오라클은 다음과 같은 경우에 direct path I/O 를 사용한다.

  1. 정렬작업을 위해 정렬 세그먼트(Sort segment)를 읽고 쓰는 경우. direct path read temp , direct path write temp 이벤트를 대기한다.
  2. Parallel Query를 위해 데이터파일을 읽는 경우. direct path read 이벤트를 대기한다.
  3. PDML이나 CTAS를 위해 데이터파일을 쓰는 경우. direct path write 이벤트를 대기한다.
  4. NOCACHE 속성으로 생성된 LOB 세그먼트를 읽고 쓰는 경우. direct path read(lob) , direct path write(lob) 이벤트를 대기한다.
  5. I/O 시스템이 데이터를 읽어서 오라클에 반환하는 속도보다 훨씬 빠른 속도로 버퍼를 요구할 때. 이 경우 오라클 성능개선을 위해 readahead I/O (이후에 읽을 것으로 판단되는 데이터를 미리 한꺼번에 읽는 I/O 작업을 말함)를 이용한다. 이 경우 direct path read 이벤트를 대기한다.

Direct path I/O 와 관련된 통계값에 대해 정리하면 다음과 같다.

  • physical reads : 디스크에서 읽은 블록수. Direct path I/O 여부와 무관하게 물리적인 읽기 작업이 발생할 경우에는 항상 증가한다.
  • physical reads direct : Direct path I/O 를 통해 읽은 블록수. LOB 데이터에 대한 direct path I/O 는 포함하지 않는다.
  • physical reads direct (lob) : LOB 데이터를 direct path I/O 를 통해 읽는 블록수
  • physical writes : 디스크에 기록한 블록수. Direct path I/O 여부와 무관하게 물리적인 쓰기 작업이 발생할 경우에는 항상 증가한다.
  • physical writes direct : Direct path I/O 를 통해 기록한 블록수. LOB 데이터에 대한 direct path I/O 는 포함하지 않는다.
  • physical writes direct(lob) : LOB 데이터를 direct path I/O 를 통해 기록한 블록수
  • sort(disk) : 디스크를 이용한 정렬 작업 회수. 디스크를 이용한 정렬 작업이 발생할 경우에는 정렬 세그먼트에 대해 direct path I/O 를 사용한다.
  • sort(memory) : 메모리를 이용한 정렬 작업 회수

물리적인 읽기 작업 중 버퍼 캐시를 경유한( conventional path I/O ) 읽기 작업은 다음 공식으로 계산할 수 있다.

conventional physical reads = physical reads –( physical reads direct + physical reads direct(lob) )


Direct path I/O 의 성능문제는 대부분 I/O 시스템의 성능과 직접적인 관련이 있다. Direct path I/O 는 버퍼 캐시를 경유하지 않기 때문에 동기화에 따른 오버헤드가 없다. 따라서 경합으로 인한 성능저하 현상이 발생하지 않는다. 정렬작업과 같은 경우를 제외하고는 튜닝 작업을 통해 direct path I/O에서의 대기회수 및 대기시간을 줄이는 것은 불가능하다. 만일 direct path I/O 의 수행 성능상에 문제가 생긴다고 판단되면 I/O 자체의 성능을 개선시키는 것에 초점을 맞추는 것이 옳다.

 

오라클의 direct path I/O 는 OS의 direct IO와 다른 개념이라는 것에 유의하자. 오라클의 direct path I/O 는 SGA의 버퍼 캐시를 경유하지 않는 것이고, OS의 direct I/O는 OS의 버퍼 캐시를 경유하지 않는 것이다. 오라클의 캐시와 OS의 캐시를 같이 사용하는 것을 더블 버퍼링(double-buffering)이라고 부르는데, 보통 성능에 이롭지 않은 것으로 알려져 있다.
하지만 더블버퍼링이 성능에 미치는 영향의 정도는 시스템의 특성이나 어플리케이션의 특성에 따라 다를 수 있다.

 

_DB_FILE_DIRECT_IO_COUNT 의 조정

 

_DB_FILE_DIRECT_IO_COUNT 히든 파라미터의 값이 direct path I/O 에서의 최대 I/O 버퍼 크기를 결정한다. 오라클 9i부터 이 값은 기본적으로 1M의 값을 가진다. 하지만 실제로는 O/S나 하드웨어 설정에 따라 최대값이 결정된다. 이 값을 높이면 Parallel Query의 성능이 높아질 수도 있으나, 대부분 실제 사용가능한 값은 1M보다 작은 값이므로 실제로는 변경할 필요가 없다.

 

Event Tip

 

direct path read와 undo

 

direct path read 가 비록 데이터파일에서 직접 데이터를 읽지만, 언두를 참조하는 메커니즘은 동일하다. 즉, direct path read 는 SGA를 경유하지 않을 뿐, 읽기 일관성(Read consistency)을 보장하는 방법은 동일하다. 이것을 증명하는 방법은 크기가 작은 언두 테이블스페이스(Undo tablespace)를 생성한 후, Parallel Query를 수행하면서 다른 세션에서 DML을 과다하게 수행할때 ORA-01555(Snapshot too old) 에러가 나는 것을 관찰하는 것이다.

 

ERROR at line 1:
ORA-12801: error signaled in parallel query server P002
ORA-01555: snapshot too old: rollback segment number 68 with name "_SYSSMU68$" too small

 

위의 에러는 곧 PQ 슬레이브 세션이 데이터파일에 대해 direct read 를 수행하면서 변경된 블록을 발견하면 언두 데이터를 참조하는 것으로 해석할 수 있다.

 

데이터 파일에 대한 direct path read의 증명

 

PQ 수행시 슬레이브 세션에서의 direct path read가 임시 영역이 아닌 데이터파일에 대한 direct path read 인 것을 어떻게 증명할 수 있을까? 하나의 세션에서 PQ를 수행한 후, PQ가 수행되는 동안 다른 세션에서 V$SESSION_WAIT 뷰를 조회해서 P1 값을 얻으면 어떤 파일에 대한 direct path read 인지 알 수 있다. 아래 스크립트를 보자. 

Session A : Degree가 4 인 pq_test 테이블에 대해 PQ를 여러번 수행하면서 direct path read 유발

declare

v_count number;

begin

for idx in 1 .. 100 loop

  select count(*) into v_count from pq_test;

end loop;

end;

/

Session B: Session A에서 발생한 PQ의 슬레이브 세션에 대해 direct path read 이벤트를 캡쳐한다.(Session A의  SID = 162)

set serveroutput on size 100000

declare

begin

           for px in (select * from v$px_session where qcsid = 162) loop

                for wait in (select * from v$session_wait where 

                   sid = px.sid and event like '%direct path read%') loop

                    dbms_output.put_line('SID='||wait.sid || 

', P1=' || wait.P1);

                 end loop;

           end loop;                                         

end;

/

Session B의 수행결과는 아래와 같다.

SID=138, P1=1

SID=152, P1=1

SID=144, P1=1

...

SID=142, P1=1

SID=144, P1=1

SID=138, P1=1

direct path read 대기이벤트의 P1 = file#이므로 해당되는 파일이 실제 데이터파일인지 확인할 수 있다.

SQL>exec print_table('select * from v$datafile where file# = 1'); 을 

FILE#                         : 1

...

BLOCK_SIZE                    : 8192

NAME                          :

C:\ORACLE\PRODUCT\10.1.0\ORADATA\UKJADB\SYSTEM01.DBF

PLUGGED_IN                    : 0

BLOCK1_OFFSET                 : 8192                                   

AUX_NAME                      : NONE

위와 같이 system01.dbf라는 데이터 파일에 대한 direct path read임을 알 수 있다.

 

DB_FILE_DIRECT_IO_COUNT

 

DB_FILE_DIRECT_IO_COUNT 파라미터는 direct path read 성능에 영향을 미칠 수 있다. 해당 파라미터는 direct reads , direct writes 에 대한 최대 I/O 버퍼크기로 설정해야 한다. 오라클 8i까지는 대부분의 플랫폼에서 기본 설정값은 64 블록이었다. 따라서 

DB_BLOCK_SIZE 가 8K인 경우 direct reads , direct writes 에 대한 최대 I/O 버퍼크기는 512K(8K*64)이다. 최대 I/O 버퍼 크기는 하드웨어의 한계 값에 의해서도 제한된다. 오라클 9i에서는 DB_FILE_DIRECT_IO_COUNT 파라미터는 hidden 파라미터로 변경되었고, 블록수가 아니라 바이트(byte)단위로 변경되었다. 오라클 9i의 기본 설정값은 1 MB이다. 실질적인 direct I/O 크기는 하드웨어 환경설정(configuration) 및 한계 값에 의해서도 영향을 받는다

 

Direct Read I/O크기 알기

 

세가지 방법으로 실제적인 direct read I/O 크기를 알 수 있다.

  • direct read 를 수행하는 세션에 대해 10046 트레이스 이벤트를 레벨 8로 설정한다. P3 파라미터는 읽은 블록수를 나타낸다. 아래의 예제의 경우, 블록 크기가 8K 이므로 direct path read I/O 크기는 64K(8K*8블록)이다. 또한, V$SESSION_WAIT 뷰를 조회하여 direct path read 대기이벤트의 P3값을 확인할 수 있다. 
WAIT #1: nam='direct path read' ela= 4 p1=4 p2=86919 p3=8

WAIT #1: nam='direct path read' ela= 5 p1=4 p2=86927 p3=8

WAIT #1: nam='direct path read' ela= 10 p1=4 p2=86935 p3=8

WAIT #1: nam='direct path read' ela= 39 p1=4 p2=86943 p3=8

WAIT #1: nam='direct path read' ela= 5 p1=4 p2=86951 p3=8

WAIT #1: nam='direct path read' ela= 38 p1=4 p2=86959 p3=8

...
  • O/S의 truss, tusc, trace, 또는strace를 이용하여 direct reads 혹은 direct writes 를 수행하는 UNIX 프로세스를 트레이스 한다. 오라클 9i에서 truss 리포트의 일부분으로 direct I/O 크기가 64K임을 알 수 있다. 
9218/1:         kaio(AIOWAIT, 0xFFBECE98)                       = 1

9218/1:         lwp_cond_signal(0xFEB7BFA0)                     = 0

9218/3:         pread64(256, "0602\0\001\0 ~13C19AEE }".., 65536, 0x0FC26000) = 65536

9218/1:         lwp_cond_signal(0xFEB69FA0)                     = 0

9218/4:         pread64(256, "0602\0\001\0 ~1BC19AEFE7".., 65536, 0x0FC36000) = 65536
  • 10357 트레이스 이벤트를 설정하여(예: alter session set events '10357 trace name context forever, level 1') direct I/O를 수행하는 세션에 대한 디버그 정보를 확인한다. 
Unix process pid: 4375, image: oracle@kccdeds73 (P000)

*** SESSION ID:(9.18) 2004-02-08 21:47:01.908

DBA Range Initialized: length is 1570, start dba is 0100602b

kcblin: lbs=fc86c1cc  flag=8 slot_cnt=32 slot_size=65536 state obj=24321224

kcblin: state objects are: Call=243a2210,Current Call=243a2210, Session=24321224

kdContigDbaDrCbk:starting from tsn 5

kdContigDbaDrCbk:starting from rdba 0100602b

kdContigDbaDrCbk:returning 1570 blocks

kcblrs:issuing read on slot : 0

kcbldio:lbs=fc86c1cc slt=fc86408c typ=0 async=1 afn=4 blk=602b cnt=8 buf=fc87fe00

kcblrs:issuing read on slot : 1

kcbldio:lbs=fc86c1cc slt=fc864210 typ=0 async=1 afn=4 blk=6033 cnt=8 buf=fc89fe00

kcblcio: lbs=fc86c1cc slt=fc86408c type=0 afn=4 blk=602b cnt=8 buf=fc87fe00

...

 

이전의 예제에서, 트레이스 파일은 쿼리 슬레이브(query slave)#0 의 수행 내역이다. direct path 오퍼레이션을 위해 32개의 I/O 슬롯이 가용하다(slot_cnt=32). 하나의 슬롯이 I/O 단위이며, 각각의 슬롯은 65536 bytes이다(slot_size=65536). 읽기 오퍼레이션 시 비동기식 I/O가 사용된다(async=1). 쿼리 슬레이브는 데이터파일 #4를 읽는다(afn=4). 읽어 들이는 블록개수는 8개이다(cnt=8). 블록크기는 8K이므로 65536 bytes를 전송한다.

 

이 경우, direct I/O 슬롯크기는 프로세스가 1 MB 전부를 사용하지 못하게 한다.
_DB_FILE_DIRECT_IO_COUNT 파라미터의 기본 설정값은 1 MB이다. 슬롯크기는 10351 이벤트를 사용하여 변경할 수 있다. 또한 슬롯의 개수도 10353 이벤트를 사용하여 변경할 수 있다. Caution : 앞에서 기술한 정보를 이용하여, 자신이 사용하는 시스템에 대한 direct I/O 의 처리량을 파악할 수 있다. 슬롯크기와 direct I/O 슬롯의 개수에 대한 기본 설정값을 쉽게 변경하지 마라. 변경 작업 전에 자신이 사용하는 하드웨어의 한계를 파악해야 하며, 어플리케이션과 SQL문을 최적화시키는 데 초점을 맞추어야 한다.

 

 

제공 : DB포탈사이트 DBguide.net

반응형
저작자표시 비영리

'ORACLE > OWI' 카테고리의 다른 글

Latch의 발생과 경합의 원인  (0) 2009.03.09
Oracle에서 Latch 가 성능 저하의 원인이 아닙니다  (0) 2009.03.01
Enqueue 와 Latch  (0) 2009.03.01
LIBRARY CACHE LOCK WAIT EVENT가 나타날 때의 해결방법  (0) 2008.12.22
oracle lock 종류  (0) 2008.12.22
Posted by [PineTree]
ORACLE/TUNING2009. 2. 7. 22:51

db_file_multiblock_read_count 파라미터의 몰락...

반응형
Oracle 10g R2에서 사람들이 전혀 눈치채지 못한 큰 변화가 있었다.
흔히 MBRC라고 줄여서 부르는 db_file_multiblock_read_count 파리미터가 공식적으로 몰락한 것이다.
물론 여전히 사용 가능하지만, 그 내부 작동 방식으로 보아서 이 파라미터는 사실상 가치가 없어진 셈이다.

사실 이러한 조짐은 이전부터 있었다.
바로 System Statistics가 9i에서 소개된 것이다. 9i에서 System Statistics를 수집하면 다음과 같은 데이터가 sys.aux_stats$ 테이블에 저장된다.

SQL> SELECT * FROM sys.aux_stats$;
SNAME    PNAME    PVAL1    PVAL2
SYSSTATS_INFO    STATUS        COMPLETED
SYSSTATS_INFO    DSTART        12-30-2007 15:21
SYSSTATS_INFO    DSTOP        12-30-2007 15:21
SYSSTATS_INFO    FLAGS    1  
SYSSTATS_MAIN    SREADTIM    1         <-- Single Block Read Time
SYSSTATS_MAIN    MREADTIM    2        <-- Mulit Block Read Time
SYSSTATS_MAIN    CPUSPEED    500     <-- CPU Speed
SYSSTATS_MAIN    MBRC    8               <-- Multi Block Read Count
SYSSTATS_MAIN    MAXTHR    -1  
SYSSTATS_MAIN    SLAVETHR    -1

System Statistics가 없는 상황에서는 db_file_multiblock_read_count, optimizer_index_cost_adj와 같은 파라미터들이 Index Scan과 Table Scan중 어느 것을 선택할지를 결정하는데 큰 역할을 한다.

하지만, System Statistics가 수집된 경우에는?
위의 값들을 보면 알겠지만, System Statistics는 Index Scan과 Table Scan을 결정하는데 필요한 모든 중요한 정보들을 다 가지고 있다. 따라서 Oracle은 SREADTIME, MBREADTIM, CPUSPEED, MBRC 등의 정보를 이용해 비용을 계산한다.

그러면, System Statistics가 활성화되어 있으면 db_file_multiblock_read_count 파라미터는 무시되는 것인가? Optimizer에 의해 실행 계획이 생성될 때는 무시되지만 실제 Fetch과정에는 db_file_multiblock_read_count 크기만큼 Multi Block I/O를 한다.

즉, Optimizer에 의해서는 사용되지 않고 실제 쿼리를 실행하고 Fetch하는 단계에서만 사용된다.

이러한 원리는 Oracle 10g R1에서도 거의 비슷하다. Oracle 9i와 10g의 차이점은 System Statistics가 없을 때의 동작 방식이다. Oracle 10g에서는 System Statistics에 다음과 같은 항목이 추가되었다.

SNAME    PNAME    PVAL1    PVAL2
SYSSTATS_MAIN    CPUSPEEDNW    1845.90945194599  
SYSSTATS_MAIN    IOSEEKTIM    10  
SYSSTATS_MAIN    IOTFRSPEED    4096  

즉, 기본적인 CPU Speed와 IO Seek Time, IO Transfer Speed 값이 추가되었다. 이것을 흔히 "Noworkload" System Statistics라고 부른다. 말 그대로 Oracle이 하는 일과 무관하며 순수하게 Hardware로부터 취득한 정보라는 의미이다. Oracle은 Noworkload에 대해서는 적절한 기본값을 가지고 있으며, 필요하다면 이 값을 이용한다.

Oracle 10g R1은 System Statistics가 없으면 CPUSPEEDNW, IOSEEKTIM, IOTFRSPEED 값과 db_file_multiblock_read_count 값을 함께 이용해서 비용을 계산한다. 즉, db_file_multiblock_read_count 파라미터가 사용되기는 하되 9i와 같이 직접 사용되는 것이 아니라 Noworkload 통계 정보와 함께 조합되어서 사용된다.

하지만, Oracle 10g R2에서 또 한번의 변화가 생겼다. Oracle 10g R2에는 Multi Block I/O와 관련된 파라미터가 다음과 같이 세개로 늘어났다.

    * db_file_multiblock_read_count
    * _db_file_optimizer_read_count
    * _db_file_exec_read_count

즉, db_file_multiblock_read_count라는 파라미터가 Optimizer가 사용할 값(_db_file_optimizer_read_count)과 실행시에 사용할 값(_db_file_exec_read_count)으로 세분화된 것이다.
이 파라미터들의 사용방식은 다음과 같다.

1. _db_file_optimizer_read_count 파라미터는 System Statistics가 없는 경우에 Optimizer가 비용을 계산한기 위해 사용한다(앞서 설명한 바와 같이 Noworkload 통계값과 같이 사용). System Statistics가 수집된 경우에는 이 값은 무시된다.

2. _db_file_exec_read_count 파라미터는 쿼리를 실행하는 과정에서 Multi Block I/O를 수행할 때 한번에 읽을 블록수를 결정한다. 이 값은 System Statistics의 수집 여부와 무관하게 사용된다.

3. db_file_optimizer_read_count 파라미터값을 명시적으로 변경하면 _db_file_optimizer_read_count 값과 _db_file_exec_read_count 값이 모두 같이 변경된다.

전체적인 사용방식은 이전 버전과 동일하지만, db_file_multiblock_read_count 파라미터가 사실상 없어진 것과 마찬가지이며, 목적에 따라 명확하게 구분된 두 개의 히든 파라미터로 나뉘어졌다. 우리가 설사 db_file_multiblock_read_count 값을 지정하더라도 Oracle은 내부적으로 서로 다른 두 개의 히든 파라미터를 사용하는 셈이다.

언뜻 복잡해 보이는 위의 논의들은 사실은 다음과 같은 결론을 위한 것이다.

"System Statistics 정보를 잘 수집하자. 너무나 좋은 기능이다."

System Statistics 정보를 이용하면 기존에 Optimizer의 부족한 판단을 보완해주기 위해 db_file_multiblock_read_count, optimizer_index_cost_adj 같은 파라미터의 값을 변경해줄 수고가 줄어들며, 훨씬 정확한 정보에 기반한 비용 계산이 가능해진다. 더불어 db_file_multiblock_read_count 파라미터는 더 이상 Optimizer에게 영향을 주지 못하고 말 그대로 Multi Block I/O를 실제로 수행할 경우에만 사용되므로 훨씬 직관적이고 오해의 소지가 없는 셈이다.

많은 시스템들이 이 정보를 잘 사용하고 있지만, 특정 시스템은 아직 잘 모르거나 아니면 새로운 기능에 대한 두려움 때문에 사용하지 못하고 있을 것이다. 만일 사용하고 있지 않다면 반드시 테스트해보기 바란다.

Oracle 11g에서는 또 한번의 변화가 있는 것 같다. _db_file_optimizer_read_count 파라미터의 값이 10g R2에서는 8이다. Table Scan을 지나치게 선호하지 않도록 비교적 낮은 값을 지정한 것으로 보인다. 하지만 11g에서는 놀랍게도 기본값이 128로 지정되어 있다. 사용 방식이 바뀐 것인지...? 이 부분은 추후에 테스트가 필요할 것이다.

출처 : http://ukja.tistory.com/85
반응형
저작자표시 비영리

'ORACLE > TUNING' 카테고리의 다른 글

ORACLE HINT 정리  (0) 2009.02.09
DML의 INSERT 성능 향상  (0) 2009.02.09
[Hint] JOIN plan explain trace (NESTED LOOP, SORT MERGE, HASH JOIN ) use_nl, use_merge, use_hash  (0) 2009.02.06
Nested Loop Join과 Sort Merge Join  (0) 2009.02.06
Oracle 10g에서 Shrink 대상 Segment 찾기  (0) 2009.02.03
Posted by [PineTree]
ORACLE/TUNING2009. 2. 6. 14:52

[Hint] JOIN plan explain trace (NESTED LOOP, SORT MERGE, HASH JOIN ) use_nl, use_merge, use_hash

반응형
[Hint]조인 방법 변경(USE_NL)
조인 방법 변경(USE_NL)

테이블을 조인 하는 경우 중첩 루프 조인(Nested Loop Join)이 일어나도록 하는 힌트 문장 입니다. 중첩 루프 조인은 중첩 반복이라고도 하는데 하나의 테이블(outer/driving table)에서 추출된 로우를 가지고 일일이 다른 테이블(inner/probed table)을 반복해서 조회하여 찾아지는 레코드를 최종 데이터로 간주하는 방법 입니다.

즉 조인 입력 한 개를 외부 입력 테이블로 사용하고, 한 개는 내부(최하위) 입력 테이블로 사용하고 외부 루프는 외부 입력 테이블을 행 단위로 사용하고 각 외부 행에 대해 실행되는 내부 루프는 내부 입력 테이블에서 일치되는 행을 검색 하는거죠…  이것을 원시 중첩 루프 조인이라고 하는데 검색에서 인덱스를 사용하는 경우에는 인덱스 중첩 루프 조인이라고 합니다.

예를 들어 EMP 테이블과 DEPT 테이블을 조인하는 경우 dept 테이블이 건수가 작다면 우선 이 테이블을 외부 루프로 해서 하나씩 읽으면서 이에 대응하는 emp 테이블의 데이터를 추출 하는 경우라면 중첩 루프 조인에 해당 합니다. 이때 emp 테이블의 경우 건수가 많다고 가정을 하면 대부분 인덱스를 이용하도록 emp 테이블의 외래키인 deptno 컬럼은 대부분 인덱스를 걸게 되죠^^

중첩 루프 조인은 테이블중 적어도 하나의 조인 컬럼에 대해 인덱스(or Hash Index)가 존재할 때 연관되는 방식으로 이 중첩 루프 조인에서 테이블중 하나의 테이블 또는 중간 결과 셋을 대상으로 FULL SCAN이 일어나게 됩니다. 이 테이블이 드라이빙 테이블이 되는데… 이 테이블의 데이터 건마다 나머지 테이블에서 원하는 데이터를 추출하기 위해 대부분 인덱스를 사용하게 되는 겁니다.

보통 USE_NL 힌트 구문은 ORDERED 힌트 구문과 같이 사용되는데 USE_NL이 취하는 인자는 FROM절에서 두번째 나오는 테이블(비드라이빙 테이블, inner/probed table)을 명시해 주어야 합니다. 안수로 사용되지 않은 첫 번째 테이블은  outer/driving table이 되는 것입니다.

[형식]
/*+ USE_NL ( table [table]... ) */


[예]

아래는 Oracle 10g에서 테스트 한 결과 입니다.

analyze table emp compute statistics
analyze table dept compute statistics

select /*+ORDERED USE_NLe) */
       e.ename,
           d.dname
from   dept d, emp e
where  e.deptno = d.deptno        

------------------------------------------------------------
Operation        Object Name        Rows        Bytes        Cost        
---------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS                14                   4
  TABLE ACCESS BY INDEX ROWID        SCOTT.EMP        4          32          1          
    NESTED LOOPS                14          266          4                                  
      TABLE ACCESS FULL        SCOTT.DEPT        4          44          3                    
      INDEX RANGE SCAN        SCOTT.IDX_EMP_DEPTNO        5                   0  


        FROM절에서 처음 나타나는 테이블이 드라이빙 테이블(DRIVING/OUTER? TABLE)이며 비드라이빙 테이블(PROBE/INNER TABLE)이 USE_NL의 인자로 들어갑니다!!

select /*+ORDERED USE_NL(D) */
       e.ename,
           d.dname
from   emp e, dept d
where  e.deptno = d.deptno        

--------------------------------------------------------------
Operation        Object Name        Rows        Bytes        Cost        
--------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS                14                   3
  NESTED LOOPS                14          266          3                                  
    TABLE ACCESS BY INDEX ROWID        SCOTT.EMP        14          112          2          
      INDEX FULL SCAN        SCOTT.IDX_EMP_DEPTNO        13                   1
    TABLE ACCESS BY INDEX ROWID        SCOTT.DEPT        1          11          1          
      INDEX UNIQUE SCAN        SCOTT.PK_DEPT        1                   0          
                                                    

이번에는 USE_MERGE와 ORDERED가 같이 쓰이는 경우인데 이 경우엔 FROM 절 뒤 테이블의 순서는 실행계획은 다르게 나티날지 모르지만 성능에는 영향을 미치지 않습니다. 왜냐구요? 위 내용을 읽어 보세요!!


select /*+ORDERED USE_MERGE(D) */
       e.ename,
           d.dname
from   emp e, dept d
where  e.deptno = d.deptno        


--------------------------------------------------------------
Operation        Object Name        Rows        Bytes        Cost        
-------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS                14                   6
  MERGE JOIN                14          266          6                                                      
    TABLE ACCESS BY INDEX ROWID        SCOTT.EMP        14          112          2          
      INDEX FULL SCAN        SCOTT.IDX_EMP_DEPTNO        13                   1
    SORT JOIN                4          44          4                                                      
      TABLE ACCESS FULL        SCOTT.DEPT        4          44          3                                                      


select /*+ ORDERED USE_MERGE(E) */
       e.ename,
           d.dname
from   dept D, emp E
where  e.deptno = d.deptno        


----------------------------------------------------------------
Operation        Object Name        Rows        Bytes        Cost        
--------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS                14                   5
  MERGE JOIN                14          266          5                                                      
    TABLE ACCESS BY INDEX ROWID        SCOTT.DEPT        4          44          2          
      INDEX FULL SCAN        SCOTT.PK_DEPT        4                   1          
    SORT JOIN                14          112          3                                                      
      TABLE ACCESS BY INDEX ROWID        SCOTT.EMP        14          112          2          
        INDEX FULL SCAN        SCOTT.IDX_EMP_DEPTNO        13                   1    

http://blog.paran.com/oraclejava/9910752

 

 

========================================================================================================================
========================================================================================================================

 

 

NESTED LOOP JOIN

 

선행적 특징을 작는데 먼저 액세스되는 테이블의 처리범위에 의해 처리량이 결정됨
Driving Table에 의해 범위가 결정되며 Driving Table의 범위가 적을수록 수행속도는 빨라진다
고로 Driving Table을 어던 테이블로 결정하느냐가 중요하다


-. /*+ use_nl (테이블) */
-. 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스하게됨, 즉 값을 받아서 처리범위가 정해짐
-. Driving Table의 인덱스 액세스는 첫번 로우만 Random Access이고, 나머지는 Scan, 연결작업은 Random Access임
-. 연결되는 방향에 따라 사용되는 인덱스들이 달라질 수 있음
-. 연결고리 인덱스 유무에 따라 액세스 방향 및 수행속도에 많은 차이가 있음
-. 연결작업 수행 후 체크되는 조건으로 부분범위처리를 하는 경우에는 조건의 범위가 넓거나 없다면 오히려 빨라짐

-. 전체가 아닌 부분범위 처리를 하는 경우 유리함
-. 조인되는 테이블중 어느 한쪽의 추출된 결과를 받아야 처리범위를 줄일 수 있는 상태라면 항상 유리함
-. Driving Table의 처리량이 많거나 연결 테이블의 Random Access량이 많을 경우에는 분리함
-. 일반적으로 처리량이 적은 경우로서 Random Access를 많이 하므로, 온라인 어플리에서 유리함
-. Driving Table의 선택이 관건임


 

SORT MERGE JOIN

 

일반적으로 배치작업에서 주로 사용되며, 각 테이블을 Sort한 후 Merge 하는 조인을 말한다

 

-. /*+ use_merge(테이블) */
-. 동시에 각각의 테이블이 자신의 처리범위를 액세스하여 정렬해둠
-. 각 테이블은 어떠한 상수값도 서로 영향을 주지 않으며, 주어진 상수값에 의해서만 각자 범위를 줄이게됨
-. 전체범위처리를하며 부분범위처리를 할수 없음
-. 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우에만 Random Access이고, Merge작업은 Scan방식
-. 선택적으로 연결고리가 되는 컬럼은 인덱스를 사용하지 않음
-. 조인의 방향과는 상관없음
-. Equal 조인에서만 가능

-. 처리량이 많은 경우로 Random Access를 하지 않음으로 전체범위처리에 유리
-. 자신의 처리범위를 인덱스를 통해 어떻게 줄이느냐가 관건
-. 상수값을 받아 줄여진 범위가 30%이상이면 Sort Merge가 유리

 

 

HASH JOIN

 

Hash Function을 이용해서 메모리와 CPU를 많이 사용해서 일반적으로 배치작업에서 주로 사용됨


-. /*+ use_hash(테이블) */
-. 적은테이블과 큰테이블의 조인시에 유리
-. Equal 조인에서만 가능
-. Driving Table에 인덱스를 필요로 하지 않고 각 테이블을 한번만 읽음
-. 다른조인방법보다 CPU자원을 많이 소비하며 양쪽 테이블의 scan이 동시에 일어남

 

from) http://www.jakartaproject.com/

[출처] NESTED LOOP, SORT MERGE, HASH JOIN|작성자 은빛간달

반응형
저작자표시 비영리

'ORACLE > TUNING' 카테고리의 다른 글

DML의 INSERT 성능 향상  (0) 2009.02.09
db_file_multiblock_read_count 파라미터의 몰락...  (0) 2009.02.07
Nested Loop Join과 Sort Merge Join  (0) 2009.02.06
Oracle 10g에서 Shrink 대상 Segment 찾기  (0) 2009.02.03
Bind Peeking의 딜레마와 Oracle 11g의 Adaptive Cursor Sharing  (0) 2009.02.03
Posted by [PineTree]
ORACLE/TUNING2009. 2. 6. 14:45

Nested Loop Join과 Sort Merge Join

반응형

Nested Loop Join과 Sort Merge Join

 

Nested Loop Join

 

Nested Loop Join이란 먼저 어떤 테이블의 처리범위를 하나씩 액세스하면서 그 추출된 값으로 연결할 테이블을 조인하는 방식이다.

 

1. 특징

1) 순차적으로 처리된다. 선행테이블(Driving table)의 처리범위에 있는 각각의 로우들이 순차적으로 수행될 뿐만 아니라 테이블간의 연결도 순차적이다.

2) 먼저 액세스되는 테이블(Driving Table)의 처리범위에 의해 처리량이 결정된다.

3) 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스된다. 즉, 자신에게 주어진 상수값에 의해 스스로 범위를 줄이는 것이 아니라 값을 받아서 처리범위가 정해진다.

4) 주로 랜덤 액세스 방식으로 처리된다. 선행 테이블의 인덱스 액세스는 첫번째 로우만 랜덤 액세스이고 나머지는 스캔이며 연결작업은 모두 랜덤 액세스이다.

5) 주어진 조건에 있는 모든 컬럼들이 인덱스를 가지고 있더라도 모두가 사용되는 것은 아니다. 연결되는 방향에 따라 사용되는 인덱스들이 전혀 달라질 수 있다.

6) 연결고리가 되는 인덱스에 의해 연결작업이 수행되므로 연결고리 상태가 매우 중요하다. 연결고리의 인덱스 유무에 따라 액세스 방향 및 수행속도에 많은 차이가 발생된다.

7) 연결작업 수행 후 마지막으로 check되는 조건은 부분범위처리를 하는 경우에는 조건의 범위가 넓을수록, 아예 없다면 오히려 빨라진다.

 

2. 사용기준

1) 부분범위처리를 하는 경우에 주로 유리해진다.

2) 조인되는 어느 한쪽이 상대방 테이블에서 추출된 결과를 받아야 처리범위를 줄일 수 있는 상태라면 항상 유리해진다.

3) 주로 처리량이 적은 경우(많더라도 부분범위처리가 가능한 경우)에 유리해진다. 그것은 처리방식이 주로 랜덤 액세스방식이므로 많은 양의 랜덤 액세스가 발생한다면 수행속도가 당연히 나빠지기 때문이다.

4) 가능한 한 연결고리 이상 상태를 만들지 않도록 주의해야 한다.

5) 순차적으로 처리되기 때문에 어떤 테이블이 먼저 액세스되느냐에 따라 수행속도에 많은 영향을 미치므로 최적의 액세스 순서가 되도록 적절한 조치가 요구된다.

6) 부분범위처리를 하는 경우에는 운반단위 크기가 수행속도에 많은 영향을 미칠 수 있다. 운반단위가 적을 수록 빨리 운반단위를 채울 수 있으나, 폐치(Fetch) 횟수에서는 불리해지는 이중성을 가지고 있다.

7) 선행 테입ㄹ의 처리 범위가 많거나 연결 테이블의 랜덤 액세스의 양이 아주 많다면 Sort Merge 조인보다 불리해지는 경우가 많다.

 

Sort Merge Join

 

Sort Merge Join이란 양쪽 테이블의 처리범위를 각자 액세스하여 정렬한 결과를 차례로 스캔하면서 연결고리의 조건으로 머지해 가는 방식을 말한다. 이 방식은 경우에 따라 Nested Loop Join보다 훨씬 빨라지는 경우도 많이 있으며 랜덤 액세스가 줄어들어 시스템의 부하를 감소시키지만 일반적으로 Nested Loop Join 보다는 사용되는 빈도가 적은 편이다.

이 방식의 가장 큰 특징은 상대방에게 아무런 값도 받지 않고 자신이 가지고 있는 조건만으로 처리범위가 정해지며, 랜덤 액세스를 줄일 수는 있으나 항상 전체범위처리를 한다는 것이다.

 

1. 특징

1) 동시적으로 처리된다. 테이블 각자가 자신의 처리범위를 액세스하여 정렬해 둔다.

2) 각 테이블은 다른 테이블에서 어떠한 상수값도 제공받지 않는다. 즉, 자신에게 주어진 상수값에 의해서만 범위를 줄인다.

3) 결코 부분범위처리를 할 수가 없으며, 항상 전체범위처리를 한다.

4) 주로 스캔방식으로 처리된다. 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우만 랜덤 액세스이고 머지작업은 스캔방식이다.

5) 주어진 조건에 있는 모든 컬럼들이 인덱스를 가지고 있더라도 모두가 사용되는 것은 아니다. 연결고리가 되는 컬럼은 인덱스를 전혀 사용하지 않는다.

6) 조인의 방향과는 전혀 무관하다.

7) 스스로 자신의 처리범위를 줄이기 위해 사용되는 인덱스는 대개 가장 유리한 한가지만 사용되어진다. 그러나 그 외의 조건들은 비록 인덱스를 사용하지 못하더라도 작업대상을 줄여 주기 때문에 중요한 의미를 가진다.

 

2. 사용기준

1) 전체범위처리를 하는 경우에 주로 유리해진다.

2) 상대방 테이블에서 어떤 상수값을 받지 않고도 처리범위를 줄일 수 있는 상태인 경우 주로 유리해 질 수 있다. 상수값을 받아 처리(Nested Loop Join)한 범위의 크기와 처리범위를 줄여 처리(Sort Merge Join)한 범위의 크기를 대비해보아 상수값을 받아 줄여진 범위가 약 30% 이상이라면 Sort Merge Join이 일반적으로 유리해진다. 그러나 부분범위처리가 되는 경우라면 전혀 달라질 수 있다. 이런 경우는 처리할 전체범위를 비교하지 말고 첫번째 운반단위에 도달하기 위해 액세스하는 범위애 대해서 판단해야 한다.

3) 주로 처리량이 많은 경우 (항상 전체범위처리를 해야 하는 경우)에 유리해진다. 그것은 처리방식이 주로 스캔방식이므로 많은 양의 랜덤 액세스를 줄일 수가 있기 때문이다.

4) 연결고리 이상 상태에 영향을 받지 않으므로 연결고리를 위한 인덱스를 생성할 필요가 없을 때 유용하게 사용할 수 있다.

5) 스스로 자신의 처리범위를 어떻게 줄일 수 있느냐가 수행속도에 많은 영향을 미치므로 보다 효율적으로 액세스할 수 잇는 인덱스 구성이 중요한다.

6) 전체범위처리를 하므로 운반단위의 크기가 수행속도에 영향을 미치지 않는다. 가능한 운반단위를 크게 하는 것이 페치(Fetch) 횟수를 줄여준다. 물론 지나치게 큰 운반단위는 시스템에 나쁜 영향을 미친다.

7) 처리할 데이터량이 적은 온라인 애플리케이션에서는 Nested Loop Join이 유리한 경우가 많으므로 함부로 Sort Merge Join을 사용하지 말아야 한다.

8) 옵티마이저 목표(Goal)가  "ALL_ROWS"인 경우는 자주 Sort Merge Join으로 실행계획이 수립되므로 부분범위처리를 하고자 한다면 이 옵티마이져 목표가 어떻게 지정되어 있는지에 주의하여야 한다.

[출처] [ORACLE] Nested Loop Join과 Sort Merge 조인|작성자 다찌

반응형
저작자표시 비영리

'ORACLE > TUNING' 카테고리의 다른 글

db_file_multiblock_read_count 파라미터의 몰락...  (0) 2009.02.07
[Hint] JOIN plan explain trace (NESTED LOOP, SORT MERGE, HASH JOIN ) use_nl, use_merge, use_hash  (0) 2009.02.06
Oracle 10g에서 Shrink 대상 Segment 찾기  (0) 2009.02.03
Bind Peeking의 딜레마와 Oracle 11g의 Adaptive Cursor Sharing  (0) 2009.02.03
오라클 hint 사용법  (2) 2008.11.24
Posted by [PineTree]
ORACLE/ADMIN2009. 2. 6. 10:58

INSERT /*+ APPEND */ VS CTAS

반응형
Zurück

Can INSERT's be tuned?

Overview

The CREATE TABLE ... AS SELECT statement (CTAS) is a powerful tool for manipulating large sets of data. CTAS provides a mechanism for efficiently executing a SQL query and storing the results of that query in a new database table.

The INSERT /*+APPEND*/ ... AS SELECT statement offers the same capabilities with existing database tables.

APPEND into Tables

By using the APPEND hint, you ensure that Oracle always grabs "fresh" data blocks by raising the high-water-mark for the table. If you are doing parallel insert DML, the Append mode is the default and you don't need to specify an APPEND hint.

When you use the APPEND hint for INSERT, data is simply appended to a table above the HWM which has the effect of not creating UNDO. Existing free space in blocks is not used.

High Water Mark

The high water mark is the boundary between used and unused space in a segment. As requests for new free blocks that cannot be satisfied by existing free lists are received, the block to which the high water mark points becomes a used block, and the high water mark is advanced to the next block. In other words, the segment space to the left of the high water mark is used, and the space to the right of it is unused.

Suggest Way for fast INSERTs

  • Mark indexes unuasble
  • Disable primary key
  • Alter table nologging
  • Do an insert /*+ append */ into table (select ..)
  • Enable primary key
  • Rebuild indexes nologging

Example

1.  First create a Big Table for this Test

create table bigtab

as

select rownum id, a.*

  from all_objects a

 where 1=0;


alter table bigtab nologging;


declare

    l_cnt number;

    l_rows number := 1000000;

begin

    insert /*+ append */

    into bigtab

    select rownum, a.*

      from all_objects a;


    l_cnt := sql%rowcount;


    commit;


    while (l_cnt < l_rows)

    loop

        insert /*+ APPEND */ into bigtab

        select rownum+l_cnt,

               OWNER, OBJECT_NAME, SUBOBJECT_NAME,

               OBJECT_ID, DATA_OBJECT_ID,

               OBJECT_TYPE, CREATED, LAST_DDL_TIME,

               TIMESTAMP, STATUS, TEMPORARY,

               GENERATED, SECONDARY

          from bigtab

         where rownum <= l_rows-l_cnt;

        l_cnt := l_cnt + sql%rowcount;

        commit;

    end loop;

end;

/

Now we have a Table with 1'000'000 Rows. Now delete some Rows, to force Oracle to refill this freespace using the FREELISTS in a normal INSERT. However in APPEND Mode the FREELISTS are not used and the freespace are not reoccupied.

DELETE FROM bigtab WHERE id between 1000 and 2500;
DELETE FROM bigtab WHERE id between 3500 and 6500;
DELETE FROM bigtab WHERE id between 15000 and 20000;
DELETE FROM bigtab WHERE id between 350000 and 370000;
COMMIT;

CREATE TABLE insert_test AS SELECT * FROM bigtab;

2.  Test with normal Insert

SQL> CREATE TABLE insert_test AS SELECT * FROM bigtab;
Table created.

SQL> INSERT INTO insert_test SELECT rownum, a.* FROM all_objects a;
49483 rows created.

Elapsed: 00:00:03.92   <==================== !

SQL> DROP TABLE insert_test;
Table dropped.

3.  Test with APPEND Hint and NOLOGGING

SQL> CREATE TABLE insert_test AS SELECT * FROM bigtab;
Table created.

SQL> ALTER TABLE insert_test NOLOGGING;
Table altered.

SQL> INSERT /*+ append */ INTO insert_test SELECT rownum, a.* FROM all_objects a;
49483 rows created.

Elapsed: 00:00:02.54    <==================== !

As you can see, only to insert about 50'000 Rows, the APPEND Insert is much faster, due the free space in the Oracle blocks are not refilled, the Rows are appended and the Highwater Mark is moved.
반응형
저작자표시 비영리

'ORACLE > ADMIN' 카테고리의 다른 글

PCTFREE, PCTUSED  (0) 2009.02.25
oracle em 구성  (0) 2009.02.12
Query Rewrite와 MView Refresh.  (0) 2009.02.05
MView의 생성  (0) 2009.02.05
Materialized View  (0) 2009.02.05
Posted by [PineTree]
ORACLE/ADMIN2009. 2. 5. 15:01

Query Rewrite와 MView Refresh.

반응형

◈ Query Rewrite(질의 재작성)


 1) Query Rewrite(질의 재작성)란?
 
A란 사용자가 자주사용되는 복잡한 Query 문장을 MView로 생성해 놓았을 경우..
 
그 사실을 모르는 B가 A가 만든 뷰와 똑같은 결과를 조회하는 Query문을 MView가 아닌
일반 SQL문장으로 실행했을 경우 B는 SQL문장을 수행했지만. 같은 문장에 MView가 존재하면
B가 실행한 문장이 A가 생성해놓은 MView를 실행하는 것으로 자동 전환이 됩니다.

이러한 기능을 Query Rewrite라고 합니다.

SQL문장을 수행하였어도 미리 정의된 MView가 존재한다면, MView를 조회하도록 Query가 다시 쓰여지는 거죠..


 2) Query Rewrite 예제

Query Rewrite 기능을 사용하기 위해서는 다음의 기능이 필요 필요합니다
 
 - 인스턴스 파라미터인 OPTIMIZER_MODE, QUERY_REWRITE_ENABLED,
    QUERY_REWRITE_INTEGRITY, COMPATIBLE이 설정되어 있어야 합니다.

 - 또한 MView생성시 ENABLE QUERY REWRITE 옵션을 추가되어 있어야 합니다.

 - MView를 생성한 유저는 반드시 QUERY REWRITE의 시스템 권한이 있어야 합니다.


-- sysdba 권한으로 접속 합니다.
SQL>sys/manager@oracle as sysdba


-- QUERY_REWRITE_ENABLED를 TRUE로 변경 합니다.
SQL>ALTER SYSTEM SET QUERY_REWRITE_ENABLED=’TRUE’;
시스템이 변경되었습니다.


-- 테스트 유저로 접속 합니다.
SQL>conn scott/tiger@oracle


-- Query Rewrite가 정상적으로 실행되는지 확인하기 위해서 autotrace를 실행합니다.
-- AUTOTRACE 관련해서는 "SQL*Plus AUTOTRACE" 강좌를 참고해 보세요
SQL>SET AUTOTRACE ON


-- MView를 생성했던 SQL문장 실행
SQL> SELECT SUM(a.sal), a.deptno
     FROM emp a, dept b
     WHERE a.deptno = b.deptno
     GROUP BY a.deptno;


-- OPTIMIZER_MODE가 CHOOSE인 상태에서 Analyze를 실행하지 않아 QueryRewrite가
    실행되지 않았습니다.
-- OPTIMIZER_MODE가 CHOOSE일 경우는 모든 테이블을 ANALYZE 시켜 줘야 합니다.
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (GROUP BY)
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF ’EMP’
   4    2       INDEX (UNIQUE SCAN) OF ’PK_DEPT’ (UNIQUE)


-- OPTIMIZER_MODE를 변경해 봤습니다.
SQL> ALTER SESSION SET OPTIMIZER_MODE=’FIRST_ROWS’;
세션이 변경되었습니다.
 
 
-- OPTIMIZER_MODE 변경후 다시 실행
SQL> SELECT SUM(a.sal), a.deptno
     FROM emp a, dept b
     WHERE a.deptno = b.deptno
     GROUP BY a.deptno;
 
SUM(A.SAL)     DEPTNO
---------- ----------
      8750         10
     10875         20
      9400         30


-- DEPT_SAL이라는 MView로 Query Rewrite를 실행한 것을 알 수 있습니다.
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=82 Bytes=2132)
   1    0   TABLE ACCESS (FULL) OF ’DEPT_SAL’ (Cost=2 Card=82 Bytes=2132)


※ Query Rewrite와 Hint 사용
 
Index 관련 Hint를 사용하는 것 처럼, query rewite 관련 Hint를 사용하여 제어할 수도 있습니다.
 
NOREWRITE : SELECT /*+ NOREWRITE */...
REWRITE:  SELECT /*+ REWRITE(mv1) */...

 

◈ MView를 수동으로 Refresh 하기


DBMS_MVIEW 패키지를 이용해서 수동적으로 MView의 Data를 최근의 데이터로 변경할 수 있습니다.

DBMS_MVIEW 패키지의 REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT를 call
하면 됩니다.


-- 아래와 같이 emp테이블에 임이의 데이터를 INSERT한 후 MView를 갱신해 보세요..
SQL>INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO )
        VALUES ( 7935, ’KIM’, ’MANAGER’, 7839,  TO_Date( ’12/17/1980 12:00:00 오전’, ’MM/DD/YYYY HH:MI:SS AM’), 3000, NULL, 20);
1 개의 행이 만들어졌습니다.


SQL> commit;
커밋이 완료되었습니다.


-- DEPT_SAL은 이전 MView생성 강좌에서 생성한 MView입니다.
-- DEPT_SAL MView는 ON DEMAND로 생성을 했기 때문에 데이타가 변경되지 않은 것을 확인
    할 수 있습니다.
-- DEPT_SAL MView를 ON COMMIT로 생성했을 경우 위에 commit시점에서 dept_sal이 변경이 됩니다.
SQL> SELECT * FROM DEPT_SAL;


--수동으로  dept_sal 하나의 MView만 갱신 한 후 다시 조회하면 변경된 것을 확인 할 수 있습니다.
SQL>BEGIN
       DBMS_MVIEW.REFRESH(’DEPT_SAL’);    
    END;
    /
PL/SQL 처리가 정상적으로 완료되었습니다.



-- BASE 테이블에 EMP테이블이 들어간 모든 MView를 갱신
BEGIN
    DBMS_MVIEW.REFRESH_DEPENDENT(’EMP’);    
END;



-- 모든 MView를 모두 갱신
BEGIN
    DBMS_MVIEW.REFRESH_ALL_MVIEWS;    
END;



<<참고문헌>>
 - Oracle Technical Note Materialized View  글 / 박경희
 - Oracle Technical Bulletins  No.12181 MATERIALIZED VIEW 활용방법
 - Oracle 8i Tuning 정식 교재

반응형
저작자표시 비영리

'ORACLE > ADMIN' 카테고리의 다른 글

oracle em 구성  (0) 2009.02.12
INSERT /*+ APPEND */ VS CTAS  (0) 2009.02.06
MView의 생성  (0) 2009.02.05
Materialized View  (0) 2009.02.05
ALTER ～ SHRINK SPACE  (0) 2009.02.05
Posted by [PineTree]
ORACLE/ADMIN2009. 2. 5. 14:55

MView의 생성

반응형

◈ MView의 생성

MView를 생성하고 테스트 하기 위해서는,  sysdba에서 Query Rewrite권한과  
CREATE MATERIALIZED VIEW 권한을 MView를 생성하는 유저에게 부여해야 합니다.


-- sysdba 권한으로 접속 합니다.
SQL> conn sys/manager as sysdba
연결되었습니다.
 
 
-- QUERY REWRITE 권한을 부여 합니다.
SQL> GRANT QUERY REWRITE TO SCOTT;
권한이 부여되었습니다.
 
 
-- CREATE MATERIALIZED VIEW 권한을 부여 합니다.
SQL> GRANT CREATE MATERIALIZED VIEW TO SCOTT;
권한이 부여되었습니다.
 
 
-- MATERIALIZED VIEW를 생성할 유저로 접속 합니다.
SQL> conn scott/tiger
연결되었습니다.
 
 
-- MATERIALIZED VIEW 생성
SQL> CREATE MATERIALIZED VIEW dept_sal
     -- PCTFREE 0 TABLESPACE mviews
     -- STORAGE (initial 16k next 16k pctincrease 0)
     BUILD IMMEDIATE -- BUILD IMMEDIATE, BUILD DEFERRED 선택.
     REFRESH
     COMPLETE       -- FORCE, COMPLETE, FAST, NEVER 선택.
     ON DEMAND      -- ON DEMAND, ON COMMIT 선택.
     ENABLE QUERY REWRITE
     AS
     SELECT SUM(a.sal), a.deptno
     FROM emp a, dept b
     WHERE a.deptno = b.deptno
     GROUP BY a.deptno;
구체화된 뷰가 생성되었습니다.
 
 
-- MATERIALIZED VIEW 조회
SQL> SELECT * FROM DEPT_SAL;
 
SUM(A.SAL)     DEPTNO
---------- ----------
      8750         10
     10875         20
      9400         30

 

 ◈ 위에서 생성한 CREATE MATERIALIZED VIEW 구문을 살펴 보겠습니다.


 - BUILD IMMEDIATE : MView 생성과 동시에 데이터들도 생성되는 옵션입니다.. 

 - BUILD DEFERRED : MView를 생성은 하지만, 그 안의 데이터는 추후에 생성하도록 하는 기능 입니다.
   .위에 MView 생성시 BUILD IMMEDIATE 대신 BUILD DEFERRED 옵션을 사용하면 조회된
     데이터가 없겠죠.. 


 - REFRESH 절은 오라클이 MView의 데이터를 언제, 어떻게 Refresh 하는지를 결정 하는 방법입니다.
   .Refresh 방법에는 ON COMMIT 방법과, ON DEMAND 방법 2 가지가 있습니다.
 
   .ON COMMIT 은 기초 테이블에 Commit 이 일어날 때 Refresh 가 일어나는 방안이며,
    이는 1 개의 테이블에 COUNT(*), SUM(*)과 같은 집합 함수를 사용하거나, MView에 조인만이
    있는 경우,  Group By 절에 사용된 컬럼에 대해 COUNT(col) 함수가 기술된 경우만
    사용이 가능 합니다.
 
   .ON DEMAND는 사용자가 DBMS_MVIEW 패키지 (REFRESH, REFRESH_ALL_MVIEWS,
    REFRESH_DEPENDENT) 를 실행 한 경우 Refresh 되는 경우 입니다.

 
 - Refresh를 하는 방법에는 FORCE, COMPLETE, FAST, NEVER의 4가지가 존재 합니다.

   .COMPLETE : MView의 정의에 따라 MView의 데이터 전체가 Refresh 되는 것으로
                          ATOMIC_REFRESH=TRUE와 COMPLETE으로 설정한 경우 입니다.

   .FAST : 새로운 데이터가 삽입될 때마다 점진적으로 Refresh 되는 방안으로 Direct Path나
                Mview log를 이용 합니다.

   .FORCE : 이 경우 먼저 Fast Refresh가 가능한지 점검 후 가능하면 이를 적용하고,
                  아니면 Complete Refresh를 적용 합니다.(디폴트)

   .NEVER : MView의 Refresh를 발생시키지 않습니다
 
 
 - ENABLE QUERY REWRITE : MView 생성시 이 옵션을 주어야만 임의의 SQL문장을을 처리시
        Query Rewrite를 고려 합니다.
   만일 MView 생성시 이를 지정하지 않은 경우는 ALTER MATERIALIZED VIEW를 이용하여
   수정하면 됩니다.
 
 - 마지막으로 일반 View나 Snapshot처럼 AS 구문 뒤에 필요한 컬럼과 조건들을 기술 하면 됩니다



 <<참고문헌>>
 - Oracle Technical Note Materialized View  글 / 박경희
 - Oracle Technical Bulletins  No.12181 MATERIALIZED VIEW 활용방법
 - Oracle 8i Tuning 정식 교재

반응형
저작자표시 비영리

'ORACLE > ADMIN' 카테고리의 다른 글

INSERT /*+ APPEND */ VS CTAS  (0) 2009.02.06
Query Rewrite와 MView Refresh.  (0) 2009.02.05
Materialized View  (0) 2009.02.05
ALTER ～ SHRINK SPACE  (0) 2009.02.05
oracle tablespace 사용량 확인 sql  (0) 2008.12.30
Posted by [PineTree]
ORACLE/ADMIN2009. 2. 5. 14:52

Materialized View

반응형

◈  Materialized View란 ?

Materialized View(이하 MView로 표시) 이것은 제목 그대로 View 입니다.
 
하지만 일반 View는 논리적인 테이블이고, MView는 물리적으로 존재하는 테이블 입니다.
물리적으로 존재한다는 것은 Data가 일정 공간을 차지하고 있다는 거죠.. 
 
MView는 어떤 결과를 뽑아 내는 쿼리가 너무나도 빈번히 사용 될 경우, Query 실행 시간의 수행속도
향상을위하여 , 여러 가지의 Aggregate View를 두어, 미리 비용이 많이 드는 조인이나,
Aggregate Operation 을 처리하여야 하는 SQL을 위해, 데이터베이스의 한 테이블로 저장 하며,
그 테이블을 조회 하도록 하는 것 입니다.


간단하게 설명하면 대용량의 데이터를 SUM, MIN, MAX, AVG, COUNT(*)이런 명령어를 사용해
너무나도 자주 조회하는 Query를 수행속도를 향상을 위해서,  Query의 결과 만큼의 새로운 테이블을
생성해 놓는 벙법 입니다.

자주사용되는 View의 결과를 디스크에 저장해서 Query 속도를 향상시키는 개념 이죠.

 

◈ Materialized View의 특징

 - MView를 만들어두면 QUERY의 수행속도를 증가 시킬 수 있습니다.

 - SQL 응용프로그램에서 MView 사용시 DBA는 프로그램에 영향을 끼치지 않고 언제든지 생성 및
    제거가 가능 합니다.

 - MView는 실행의 결과 행과 뷰 정의 모두 저장이 되고, 실행 결과 행으로 만들어진 테이블은 일정
   공간을 차지 합니다.

 - MView관련 기초 테이블을 변경하면, MView로 생성된 Summary 테이블도 변경 되어 집니다.

 

◈ Materialized View와 일반 View의 차이점

 - 가장 큰 차이점은 MView의 결과값은 물리적으로 존재하는 것이고,
   일반 View의 결과값은 물리적으로 존재하지 않습니다.

   즉 SELECT * FROM USER_SEGMENTS 하면 MView는 나오지만 일반 View는 나오지 않습니다.

 - MView는 MView를 생성할때의 Query로 물리적으로 이미 데이타가 생성되어 있기 때문에
   조회 속도가 빠릅니다.   
   하지만 View는 단지 쿼리정보가 딕셔너리에 저장되어 있고 사용될때 그 SQL이 다시 실행되는
   것이기 때문에 MView보다 느립니다.

   MView로 생성된 결과값이 일반 View로 조회하는 Data의 결과값 보다 훨씬 적은 Row를 조회하게 되죠.

 

◈  MView 관련 파라미터

   - OPTIMIZER_MODE
     MView를 사용하기 위해서는 Cost-Based 옵티마이져 여야 하므로 ALL_ROWS, CHOOSE,
     혹은 FIRST_ROWS 중의 어느 하나를 사용 합니다.
    "CHOOSE"인 상태에서는 모든 테이블을 ANALYZE 시켜 줘야 합니다.

   - QUERY_REWRITE_ENABLED :  Query Rewrite 사용을 위해서는 TRUE로 설정하면 됩니다.

   - QUERY_REWRITE_INTEGRITY : 오라클이 Query Rewrite의 정확성을 제어하는 파라미터로,
     "STALE_TOLERATED", "TRUSTED", "ENFORCED" 로 지정할 수 있습니다.

      STALE_TOLERATED : 사용되어진 기초테이블과 Consistent 하지 않은 View를 이용한
                                      Query Rewrite를 허용 합니다

     TRUSTED : Optimizer에서 MView의 데이터가 정확하다고 간주하고 질의 수행.
                      Integrity 확인을 하지 않습니다.

     ENFORCED: QUERY_REWRITE_INTEGRITY 의 기본값으로, 사용자가 Integrity Constraint를
                       확인하여야 합니다.

   - COMPATIBLE :  사용할 수 있는 오라클 함수들의 Compatibility를 결정하는 값으로 8.1.0 또는
      그 이상으로 설정 해야 합니다.


<<참고문헌>>
 - Oracle Technical Note Materialized View  글 / 박경희
 - Oracle Technical Bulletins  No.12181 MATERIALIZED VIEW 활용방법
 - Oracle 8i Tuning 정식 교재


  ================================================
    * 오라클 정보공유 커뮤니티 oracleclub.com
    * http://www.oracleclub.com
    * http://www.oramaster.net
    * 강좌 작성자 : 김정식 (oramaster _at_ naver.com)
  ================================================
※ oracleclub 강좌를 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
※ oracleclub 강좌는 개인의 학습용으로만 사용 할 수 있습니다. 학원 홍보용이나 수익을 얻기 위한 용도로
    사용을 하시면 안됩니다. ^
반응형
저작자표시 비영리

'ORACLE > ADMIN' 카테고리의 다른 글

Query Rewrite와 MView Refresh.  (0) 2009.02.05
MView의 생성  (0) 2009.02.05
ALTER ～ SHRINK SPACE  (0) 2009.02.05
oracle tablespace 사용량 확인 sql  (0) 2008.12.30
Oracle Flashback  (0) 2008.12.18
Posted by [PineTree]
ORACLE/ADMIN2009. 2. 5. 11:25

ALTER ～ SHRINK SPACE

반응형

ALTER ～ SHRINK SPACE

결론으로부터 말하면 SHRINK에 세그먼트(segment)의 축소(HWM 의 저하)의 효과를 기대한다면 행 이행·행 연쇄를 해소하고 나서 실행하는 편이 좋을 것이다.(행 연쇄는 지울 수 없기 때문 ASSM 에서는 어디에 연쇄할까는 운나름 ???)

 

SHRINK 조작은 Oracle 10g부터의 기능이며 ONLINE에서 실행할 수 있는 등 매우 유용하지만 약점이 도 존재한다. Oracle 10g R1에서 SHRINK에 대한 효과가 상당히 향상되었다고 생각된다. 최신 릴리스에선 이러한 약점도 반드시 해결되어 있을 것이라 생각한다.(Oracle 10g R2 는 미검증)

 

SHRINK 와 MOVE 의 특징

■ ALTER TABLE MOVE 의 동작 이미지는 다이렉트·패스·인서트로 복제, 오리지날을 TRUNCATE TABLE(그리고 메뉴얼 조작으로 색인을 REBUILD).


■ ALTER TABLE SHRINK 는 단편화의 해소를 도모하는 SQL를 사용해 레코드 단위에 위탁하는 DELETE, INSERT 커서 처리라고 하는 느낌이다.

ALTER TABLE SHRINK 와 ALTER TABLE MOVE 의 대표적인 특징

비교 내용 SHRINK MOVE
ONLINE(다른 처리와 병행) 실행할 수 있을까
 네 아니오
·SHRINK 하행 단위에 처리를 완료시키고 있으므로 RX 락으로 OK.
·MOVE 는 색인 구성표 이외에서는 X 락이 필요하게 된다
참고: 표 락의 종류와 상호 관계
행 이행은 해소할까 아니오(입력 데이터에 의존) 네
·SHRINK는 단편화의 해소하는 프로세스에 있어서 연쇄하고 있는 상태가 일부(운이 좋다면 전부) 해소될 가능성이 있다.⇒ SHRINK 시의 행 이행의 해소에 대해
·MOVE 는 완전한 재구축이므로 행 이행은 모두 해소된다.
하이워타마크는 내려갈까 데이터 분포에 의존 네
SHRINK는 대량의 행 이행 및 행 연쇄가 방치된 상태에서는 별로 효과를 기대할 수 없다.⇒ SHRINK 시의 세그먼트(segment) 축소에 대해
·MOVE 는 HWM를 저하시킬 수 있다.
작업에 큰 빈영역(데이터 세그먼트)이 필요한가 아니오 실데이터분 이상의 빈영역이 필요
·SHRINK는 처리의 방식이 행 단위로 이동이 행해지기 때문에 불필요.
·MOVE 는 복제와 삭제라고 하는 구조가 되므로 레코드 건수에 비례한 빈영역이 필요.
작업에는 큰 일시표 영역(템프세그먼트)이 필요한가 아니오 네(색인 상태에 의존)
·SHRINK 는 처리의 방식이 행 단위로 이동이 행해지기 위해 불필요.
·MOVE 는 복제와 삭제라고 하는 구조가 되므로 인덱스의 컬럼수와 레코드 건수에 비례한 일시표 영역이 사용된다.
대규모 색인의 메인터넌스가 발생할까 아니오 네
·SHRINK 하행 단위에 인덱스도 처리되므로 발생하지 않는다.
·MOVE는 ROWID가 모두 변경된다. 테이블에 색인이 존재하는 경우에는 모든 색인의 재구축 (REBUILD)이 필요하다(수동으로 실시할 필요가 있다).

 

 

SHRINK시의 행 이행의 해소에 대해

SHRINK 조작에 의해서 행 이행이 해소하는 일이 있다. 이것은 단편화를 해소할 경우에 행해지는 레코드 데이터의 이동에 의하는 것으로 항상 연쇄한 상태가 해소하는 것은 아니다. 단편화의 해소(행 데이터의 이동)는 물리 ROWID 에 의해서 핸들링 되고 있는 것처럼 보인다. (연쇄행 단편 ROWID 는 사용하지 않는다?? )

 

세그먼트(segment)의 축소, HWM의 저하에 대해

적당한 스크립트를 만들어 동작을 지켜본 결과 (덤프 한 것은 아니고 ROWID를 작업 전후로 트레이스 했다)있던 SHRINK는 물리 ROWID 를 단편화를 해소하기 위한 중요한 값으로 이용하고 있는 것 같다.(행 단편에 있어서의 연쇄행 단편 ROWID는 사용하지 않는것 처럼 보인다. 데이터·딕셔너리에 정보가 존재하지 않을것이다.)
어느 테이블이 연속한 데이터 블록으로 구성되어 있다고 가정했을 경우, 그 테이블에 단편화가 발생하고 있는 경우의 SHRINK 의 동작은 ROWID가 큰 값으로부터 차례로 전방의 빈영역에 채우고 있는 것은 아닐까 생각 할수도 있다.
중 요한 점은 세그먼트(segment)의 선두 근처에서 행 이행(또는 행 연쇄)이 발생하고, 실제의 격납 위치가 세그먼트(segment)의 마지막(HWM)에 가까운 위치에 연쇄하고 있을 때, 그 행 이행이 그대로 남아 버리는 일(※)이 있다고 하는 점이다.
이것은 다른 모든 레코드가 단편화를 해소해 연속한 빈영역이 발생해도 HWM의 행 이행에 의한 데이터 블록의 점유에 의해서 HWM 의 위치를 거의 낮추지 못하는 것을 나타낸다.⇒ 테이블·풀 스캔시에 엑세스 블럭이 많아짐. 

 

(※) Oracle 10g R1 에 대한 실험의 행동으로부터의 예상

 

세그먼트(segment)의 축소 효과가 낮을 때의 SHRINK 의 동작 이미지

 

또 SHRINK 하행 단위로 처리를 행하기 때문에 MOVE 동작과는 달리 자동 세그먼트(segment) 영역 관리에 의한 PCTUSED 에 해당하는 한계치의 영향을 받고 있을 가능성도 생각할 수 있다.(미검증).

이러한 점으로부터 격납 효율과 하이워타마크를 내리는 것에 있어서 MOVE 에 상당한 우위성이 있다고 생각된다.

 

 

SHRINK 실행시의 주의점

사용하기 위한 전제

  • 테이블 영역이 로컬 관리 테이블 영역, 자동 세그먼트(segment) 영역 관리인 것
  • LONG 열을 포함하지 않는 것
  • 클러스터화 테이블이 아닌 것
  • 압축테이블이 아닌 것

세그먼트(segment)의 축소를 할 수 없는 케이스

  • 펑션 색인, 비트 맵 결합 색인을 사용하고 있는 장소
  • ON COMMIT 마테리아라이즈드·뷰의 마스터 테이블의 경우

그외

  • ROWID 가 변경되기 위해 ROWID 마테리아라이즈드·뷰는 재구축 하지 않으면 정합성이 맞지 않게 된다

 

관련사항

 

테이블에 있어서의 SHRINK 의 사용예
행 이행·행 연쇄를 검출한다
SHRINK 가 실행 가능한 schema 테이블(파티션표를 포함한다), 인덱스 구성표(오버플로우를 포함한다), 인덱스LOB 세그먼트(segment), 마테리아라이즈드·뷰(·로그 포함한다)

 

참고사이트 : http://biz.rivus.jp/technote507120.html

[출처] ALTER TABLE 에 있어서의 SHRINK 와 MOVE (10g)|작성자 hirokorea


반응형
저작자표시 비영리

'ORACLE > ADMIN' 카테고리의 다른 글

MView의 생성  (0) 2009.02.05
Materialized View  (0) 2009.02.05
oracle tablespace 사용량 확인 sql  (0) 2008.12.30
Oracle Flashback  (0) 2008.12.18
DICTIONARY(DICT) 뷰  (0) 2008.12.03
Posted by [PineTree]

티스토리툴바