에너쓰오라클

오라클의 통계정보는 다른 DB나 계정으로 옮겨질 수 있습니다.
개발장비에서 수집된 통계정보를 옮긴다거나, 기존의 운영장비의 통계정보를 migration 할 대상으로 옮겨 동일한 SQL 수행을 유도할 수 있습니다.

다음은 현재 SCOTT 계정의 통계정보를 다른 Database의 SCOTT 계정으로 옮기는 예제입니다.


기존의 통계정보를 다른 Database의 동일 계정으로 옮기는 작업에 대한 내용들은 많이 기술 되어 있는데,
이름이 다른 계정은 어떻게 옮길까요?

다른 계정으로 통계정보를 옮기는 작업은 다른 데이터베이스내의 동일 계정으로 통계정보를 옮기는 작업처럼 procedure로만은 불가능합니다.
물론 작업 자체가 불가능하다는 이야기는 아닙니다.


통계정보를 export 받기 전에 생성하는 statistic 정보를 저장하는 stat table에는 통계정보가 만들어진 계정의 이름이 지정되어 있어,
이를 다른 계정으로 import하려고 하면 import는 성공했다고 나오나 통계정보는 입력 되지 않습니다.

따라서 수집된 통계정보가 있는 table의 owner column의 owner를 바꿔줘야 합니다.


정리해 보자면 SCOTT 계정의 SD table의 통계정보를 JBAARLOW 계정으로 옮긴다면 아래와 같이 수행해야 합니다.

Oracle INVISIBLE index는 Oracle 11g new feature 입니다.

말 그대로 보이지 않는 index 입니다. 여기서 보는 주체는 oracle optimizer가 됩니다.
즉 index는 존재하지만 optimizer는 이를 기준으로 plan을 생성하지 않습니다.
그러나 해당 index의 table에 대한 DML 변경 내역은 모두 index에 적용되게 됩니다.

이는 index 생성에 따른 혹은 index 삭제에 따른 전체 성능, 일부 성능 측정에 도움이 될 만한 feature가 아닌가 싶습니다.

AWR을 보다 보니 낯선 enqueue 2개가 자주 보여 요거 좀 정리합니다.

RO-Multiple Object Reuse (fast object reuse)

RO enqueue는 "multi object reuse" enqueue로 알려져 있습니다. 이 Enqueue는 foreground process와 background process 간의 sync 하는데 사용되는 enqueue 입니다. Background는 주로 DBWR나 CKPT를 말합니다.

특별히 object drop이나 table truncate 할 때 많이 사용됩니다.

oracle database내에서 truncate나 drop이 발생하면 다음과 같은 내부 작업들이 수행됩니다.


사실상 이 enqueue 작업은 truncate/drop operation을 순차적으로 수행되기 때문에 자주 drop/truncate가 발생하면 "RO" enqueue contention이 발생할 수 있겠죠..

KO-Multiple Object Checkpoint (fast object checkpoint)

얼마전에 PDML 관련해 posting 한 내용 중 다음과 같은 글을 쓴적 있습니다.

"direct-path read가 발생하면 변경되었거나 disk에 반영되지 않은 데이터를 buffer cache에서 disk로 강제로 flush 한다. 그후 data를 direct path I/O로 읽는다."
Parallel Query Execution

oracle 10g R2이전엔 direct-path read가 발생하면 관련 segment가 저장된 tablespace를 대상으로 flush를 하였습니다. 그러나 oracle 10g R2이후엔 관련 object만 disk로 flush하게 바뀌었습니다.
tablespace 단위로 flush 할 경우 해당 tablespace에 많은 데이터가 저장되어 있거나 한 tablespace에 데이터를 몰아 넣었다면 엄청 불필요한 dirty buffer write가 발생할 수 있겠죠.

KO enqueue는 10g R2 부터 바뀐 이러한 동작에 발생하는 enqueue lock type 입니다. 즉 object에 대한 checkpoint 시 대기할 때 발생하는 enqueue lock입니다.


다음은 일반적인 Enqueue 성능을 확인하는 script 들 입니다.
Enqueue는 v$system_event  v$session_wait  v$enqueue_stat을 통해 각종 statistic 정보를 확인할 수 있습니다.

Oracle Data Type에 대한 Technical iSeminar 입니다.

1. crs 버전


2. crs 설치 정보


3. crs 상태 확인


4. network 구성


5. voting , ocr 위치


6. callout 디렉토리

사례 1) Database를 매뉴얼하게 생성 후 netca, srvctl add 명령으로 서비스 등록 후, srvctl start 명령 시 node 2번에서만 listener, instance가 시작되지 않고 crs_satat-t로 보았을 때 target =online, status UNKNOWN으로 나타난다.

사례 2) srvctl로 instnace startup시 다음과 같은 에러가 발생한다. Sqlplus로 메뉴얼하게 startup하면 정상적으로 startup된다.

사례 3) Veritas clusterware에 10gR2 CRS 설치 후 ons와 gsd가 구동되지 않는다.

Range Partitioning Method

column 값의 범위에 따라 partition을 나누는 가장 일반적인 방법이다. 이 방식은 대개 날짜와 같은 범위로 많이 나뉘게 되는데, AP의 성격에 따라 데이터가 특정 partition에 몰리는 경우 hash partition 방식이나, list partition 방식 등 다른 partition 방식을 통해 sub-partition을 나누기도 한다.


Hash Partitioning Method

partition key의 hash 값에 의해 partition으로 나눠진다. 일반적으로 균등한 분포를 가지며 성능상의 이유로 hash partition을 사용하기도 한다.


Composite Partitioning Method

Oracle 10g 이전엔 Range-List, Range-Hash composite partition 만 지원했으나, Oracle 11에서는 List-List, List-Hash, List-Range and Range-Range composite partition이 지원된다. 또 interval partition 방식에 대해서 Interval-Range, Interval-List and Interval-Hash가 가능하다.


   

List Partitioning Method

Oracle 9i에 소개된 partition 방식으로 partition key의 값 자체에 의해 분리된 partition으로 데이터가 저장된다. 값에 의해 데이터가 partition 되므로 전혀 관계없는 데이터 값을 하나의 partition에 저장할 수 있다.


Interval Partitioning Method

Interval partition은 range partition 기능의 확장으로 개별 범위를 명시적으로 정의하지 않아도 해당 interval에 속하는 데이터가 입력 될 때 partition이 자동으로 생성된다.


Reference Partitioning Method

Oracle 11g에서 소개된 partition 방식으로 “모-자” 관계의 table 간에 모 table의 partition key column의 복제 없이 모 table의 partition 구조를 따라가게 됩니다.


System Partitioning Method

oracle 11g에서 소개된 partition 방식으로 다른 partition 방식과의 가장 큰 차이는 partition key를 정의하지 않는데 있다. 즉, system이 알아서 partition을 해준다.


Virtual column-based Partitioning Method

virtual column partition 방식은 partition key의 값이 물리적으로 존재하지 않은 경우에도 virtual column 값에 의해 partition을 할 수 있게 한다.

 CREATE TABLE employees
  (employee_id  number(6) not null, first_name varchar2(30), 
   last_name varchar2(40) not null, email varchar2(25),
   phone_number varchar2(20), hire_date  date not null,
   job_id  varchar2(10) not null, salary number(8,2),
   commission_pct number(2,2), manager_id  number(6),
   department_id number(4),
   total_compensation as (salary *( 1+commission_pct))
   )
    PARTITION BY RANGE (total_compensation)
     (
       PARTITION p1 VALUES LESS THAN (50000),
       PARTITION p2 VALUES LESS THAN (100000),
       PARTITION p3 VALUES LESS THAN (150000),
       PARTITION p4 VALUES LESS THAN (MAXVALUE)
     );

oracle은 데이터베이스 사용자들이 수행한 관련 정보를 SGA라는 메모리에 저장하여 공유합니다.
SGA에는 buffer cache, shared pool, log buffer 등의 많은 구성요소들이 있습니다.



library cache는 shared pool내에서 sql 수행 정보등을 저장하는 가장 중요한 구성요소 중 하나라고 할 수 있습니다.
library cache의 구조는 다음과 같습니다.


SQL이 수행되면 해당 구조에 어떻게 정보가 저장될까요?
sql이 수행되었을 때 library cache내에서 어떻게 저장되는지, LIBRARY_CAHCE dump와 구조를 비교해 보았습니다.
(할일이 없어 그런건 아닙니다 --;)

다음은 oracle korea에서 만든(?) oracle 9i의 new feature인 data block prefetching에 대한 내용을 발췌했습니다..



다음은 위의 white-paper의 원문입니다.
영어는 잼병이지만, 아래의 원문을 보기전까지는 위의 말이 뭔소린지 이해가 안됬다능..
(한글 공부를 다시해야 하나 .. 고민)

오라클은 DB link와 synonym을 통해서 remote oracle database에 있는 table을 local database 서버에 있는 것처럼 query가 가능합니다. 하지만 내부적으로는 remote database로 관련 query를 수행해 return 되는 데이터를 이용해 결과 값을 출력해줍니다. (당연하죠..ㅎㅎ) 그러나 remote database의 table에 대한 정보가 없기 때문에 일반적인 role을 이용해 query를 생성, 수행 요청하게 됩니다.

(아무 상관없는 그림이지만 내용이 작아 붙여 봅니다. ^^;)


아래 box 안의 role은 remote database로 query를 수행하는 일반적인 규칙입니다. 참조한 문서가 오래전꺼긴 한데, 큰 차이는 없으리라.. 생각되는 군요.

간단히 보자면 ..

MAX() 같은 group 함수는 전달 되지 않으니, 테이블의 범위에 따라서는 엄청난 데이터가 local로 전송된 후 MAX() 값 등을 구할 수도 있겠군요.

또 상수 조건이 있는 table은 remote로 해당 조건이 전달 되겠지만, 그렇지 않은 경우는 FULL TABLE SCAN을 할 수 도 있겠고요..

ORDER BY도 local에서 수행된다니, 대상이 많으면 이것도 성능에 영향을 줄 수 있겠네요.


이 내용은 oracle metalink "Note 1004553.6 DISTRIBUTED QUERY ANALYSIS"를 참조했습니다.

이 두개의 OPTION은 Precompile된 모든 program의 implicit, exeplicit cursor 에 모두 영향을 준다. (pro*ada의 경우는 약간의 예외가 있다.)

CURSOR에는 program cursor와 oracle cursor 두 가지가 있다.

program cursor는 SQL문으로 인해 생기는 data 구조이다. program cursor는 procompiler에 의해 발견된 각 SQL문 마다 선언된다. 다음의 문장이 program 안에 있다고 하자.


그렇다면 c1과 c2의 두개의 program cursor가 선언될 것이다.

Oracle cursor ( context area 라고도 한다.)는 실행 중에 생성이 된다. 이 공간은 parse된 문장과, host 변수의 주소값, 그 외에 SQL문을 실행하기 위해 필요한 정보를 가지고 있다.

이 두 개의 cursor는 cursor cache를 통해서 서로 연결되어 있다. 이 cursor cache의 초기 크기는 MAXOPENCURSORS option에 의해 결정이 된다. 아래의 그림은 이러한 연관 관계를 설명한다.




이러한 관점에서 HOLD_CURSOR와 RELEASE_CURSOR는 연관 관계는 다음과 같다.

HOLD_CURSOR option는 program cursor와 해당 cache와의 관계를 다룬다. 반면, RELEASE_CURSOR는 Oracle cursor와 cache와의 관계를 다룬다. 만약 자주 재사용되는 SQL문이 보다 빠른 속도를 갖게 하기 위해서는 이 SQL문과 예상되는 Oracle cursor가 붙어 있도록 하는 것이 좋다.

SQL문이 Oracle cursor와 붙어있다는 말은 SQL문과 해당 Oracle cursor 사이의 연결이 지속적으로 유지되고 있는것을 의미한다. 위에서 언급한 바와 같이 Oracle cursor(context area)에서는 parse된 문장이나 host변수의 주소값 등과 같이 중요한 정보가 들어 있게 되므로 SQL문장과 Oracle cursor(context area) 를 붙여두는 것이 유용하다.


이와 같이 문장과 cache를 붙여두기 위해서 HOLD_CURSOR와 RELEASE_CURSOR가 사용된다.

HOLD_CURSOR=YES option이 사용되면 cache들은 재사용이 가능하도록 flag을 표시 할 수 없도록 한다.
이것은 매우 중요한 의미를 가지는데, 만약 cache들이 사용이 되어지고 새로운 SQL문마다 각기 새로운 cache들이 할당이 된다면, MAXOPENCURSORS에 의해 결정된 수만큼의 cache가 할당된 후에 추가의 cache는 재사용이 가능하도 flag가 표시 된 cache를 사용하기 때문이다.

위의 그림을 참조로 예를 들면 다음과 같다.
C(1) cache가 재사용이 사능하도록 표시가 되어있고 EXEC SQL SELECT 문이 실행이 된다고 가정을 할 경우, program cursor P(MAXOPENCURSORS+1)가 생성되고 이것은 cache와 Oracle cursor가 필요하다. 그러나 이때 MAXOPENCURSORS에 의해 결정된 값만큼의 숫자의 cache가 이미 사용되어졌다면, 이 문장은 C(1)의 cache와 거기에 해당하는 Oracle cursor를 할당받는다. 그리고 이 cache과 Oracle cursor는 비워지고 새로운 SQL문으로 다시 parse가 되어진다.


위의 option과 함께 사용되는 것이 RELEASE_CURSOR=NO이다. 이것은 cache들과 Oracle cursor사이의 관계를 규정한다. 이 option은 parse된 문장이 실행하고 난후의 상태를 관리한다. 이때 할당된 memory는 사용가능한 상태로 유지 되어진다. 이 memory를 풀어주기 위해서는 RELEASE_CURSOR=YES를 사용한다. 이 option을 사용 하면 이 cache에 연결되어 있는 다음 문장들은 추가적으로 parse를 다시 해야하는 부담이 있다.



HOLD_CURSOR와 MAXOPENCURSORS는 밀접한 관계를 가진다.

만약 모든 cursor cache들이 현재 "재사용 불가"로 표시되어있다면 ( 이런 경우는 explicit하게 열린 cursor가 close되지 않은 상태로 fetch를 진행하는 경우와 같이 cursor cache를 사용하는 모든 문장이 실행중인 경우와 HOLD_CURSOR option을 사용한 경우등이 있다.),  새로운 cursor를 위해서는 실행중에 cursor cache를 확장해야 한다. (즉,  MAXOPENCURSORS가 10이라면, 11번째의 cursor cache를 생성한다.) 이때 11 번째 생성된 cache는 cursor가 close되어도 제거되지 않는다.

MAXOPENCURSOR를 작게 잡는 것은 memory를 절약 할수있으나 cache가 추가될때는 비용이 많이든다. 반면, 높게 잡을 경우는 추가 비용에 대한 부담이 줄어드는 많큼 필요이상의 memory를 사용하게 된다. 그리고 무조건 가장 오래된 cache를 재사용하도록 허용하는 것이 옳바른 것은 아니다.

만약 10개의 explicit cursor를 선언해서 open한 user가 11번째의 cursor를 사용하기 위하여 가장 오래된 program cursor를 재사용하게 된다면 user는 첫번째 cursor에 대한 위치를 잃어버리게 되어 이 곳에서 fetch를 수행할 수 없게 된다.

만약 program 안에서 문장의 재사용이 일어나지 않을 경우는 HOLD_CURSOR=NO, RELEASE_CURSOR=YES를 사용한다. HOLD_CURSOR=NO 는 cache들이 필요에 따라 자동적으로 "재사용"으로 표시되게 한고, RELEASE_CURSOR=YES는 Oracle cursor 가 자동적으로 해제되고 parse된 문장을 잃어 버리게 한다. site의 memory에 대한 문제로 인해 Oracle cursor들의 숫자가 제한을 받을 경우는 이 option을 사용 해야 한다.

이 때 만약 RELEASE_CURSOR=YES를 사용하게되면 자동적으로 HOLD_CURSOR=YES는 사용할수 없게 된다. RELEASE_CURSOR=YES가 Oracle cursor와 cache사이의 연결 을 끊어 버리고 Oracle cursor를 해제 시켜 버린다. 그러므로 심지어 program cursor가 cache와 HOLD_CURSOR=YES에 의해 연결되어 있어도 memory를 다시 할당하고 다시 parse를 해야 한다.그러므로 RELEASE_CURSOR=YES를 주면 HOLD_CURSOR=YES를 준 이점이 하나도 없다.

load balance는 client-side load balance와 server-side load balance로 나눌 수있다.

client-side load balance는 tnsnames.ora내의 tns alias 설정시 load balnace 설정에 의해 가능하며 기술된 listener list에 대해 random 하게 connection을 이루게 된다. 따라서 connection의 개수는서버의 resource 사용량이나 기존의 connection 갯수와는 상관없이 random 하게 이루어 진다.



server-side load balnace는 listener로 들어오는 client connection을 oracle background process인 pmon의 dynamic service registration에 의해 수집된 profile statistic 정보를 이용해 덜 바쁜 노드, 덜 바쁜 instance, 덜 바쁜 dispatcher 순으로 connection을 전이 시켜 준다.

server-side load balance는 init file내의 service_name, local_listener와 remote_listener 등록이 필요하다.

oracle 11g에서 제공하는 result cache 기능에 대한 설명입니다.
shared pool내에 결과가 저장되는 영역이 새로 할당되는 군요.
세션 레벨에서 hint를 통해 지정할 수 있답니다..



자세한 내용과 example은 OTN에서..
Improving Application Performance Using Result Cache

BFT (BigFileTablespace)는 oracle 10g에서 사용가능한 tablespace type 이다. Bigfile Tablespace는 하나의 매우 큰 datafile을 갖는 tablespace를 말한다. Bigfile tablespace의 개념이 만들어 지면서 기존의 일반적인 tablespace는 자동으로 smallfile tablespace로 불린다.

SMALLFILE tablespace와 BIGFILE tablespace는 database내에 공존할 수 있다.


BIGFILE tablespace는 하나의 datafile만을 사용하게 된다. 이는 datafile에 대한 관리가 간편해 질 수 있다는 것을 의미하며, 일반적인 SMALLFILE tablespace에서 사용하던 다음과 같은 option도 역시 사용가능하다.


BIGFILE tablespace는 다음과 같이 사용될 수 있다.


만약 default로 BIGFILE tablespace를 생성하고 싶다면 다음과 같이 tablespace 생성 option을 바꿀 수 있다.


앞서 말한 것 처럼 BIGFILE tablespace에는 datafile 추가는 불가능하다.



여러 datafile을 사용해 큰 공간을 확보하는 방법에 비해 하나의 큰 파일로 tablespace를 구성하는 이 방법은 관리상의 이점이 있으나, backup이나 recovery 단계에서는 큰 file에 대한 backup과 file 손상이 발생할 경우 전체를 restore해야 하기 때문에 오히려 부담이 될 수 도 있다. 따라서 각각의 tablespace type에 대한 이점과 단점을 구분하고 적용해야할 것이다.


약간 더 자세한 내용

일반적으로 IN operation은 특정 table(view) data의 row 값에 따른 다른 table의 데이터를 추출해내고자 할 때 자주 사용되는데, 가끔 IN operation을 row가 있는지 check하는 용도로 사용하기도 한다. 그러나 row가 존재하는지에 대해서는 EXISTS라는 근사한 operation을 따로 제공하고 있다. 

주의해야 할 점은 EXISTS와 IN은 다른 점이 존재하므로 이에 대해 유의해야 한다.  EXISTS는 단지 해당 row가 존재하는지만 check하고 더이상 수행되지 않으나 IN은 실제 존재하는 데이터들의 모든 값까지 확인한다. 따라서 일반적으로 EXISTS operation이 더 좋은 성능을 보이므로 가능하면 EXISTS를 사용하는 것이 바람직해 보인다.

또한가지 EXISTS와 IN 사용시 주의해야 할 점은 join 되는 column에 NULL을 갖는 row가 존재한다면, NOT EXISTS는 true값을, NOT IN은 false 가 return 된다. 즉, NOT IN을 사용하면 조건에 맞는 데이터가 있다고 하더라도 NULL이 존재하면 "no rows selected"라고 나오게 된다. 따라서 NVL을 이용한 NULL 처리가 꼭 필요하다.

다음은 NOT EXISTS operation을 이용한 방법이다.
예제의 products table의 product_type_id column 데이터 중 일부가 NULL로 입력되어 있다.


다음은 동일한 데이터에 대해 NOT IN을 사용했을 경우다. NULL data에 의해 조건 자체가 false가 되어 "no rows selected"라는 결과가 발생한다.


다음은 NVL()을 이용해 NULL값을 처리한 후의 결과이다.


NOT IN operation의 경우 위와 같은 사실을 미리 인지하고 있지 않다면 나중에 이러한 경우를 찾기는 매우 어려울 수 있다. 따라서 NULL에 대한 operation이나 table의 default column 값등의 지정 등의 세심한 주의가 필요하다.

Enqueue는 오라클에서 사용되는 locking 메커니즘이다.
Enqueue는 동시에 여러 프로세스가 기존의 자원에 대해서 다른 정도(degree)로 공유할 수 있는 방법을 제공한다. 


Enqueue의 가장 대표적인 예가 테이블에 대한 Lock(TM)이라 할 수 있겠다.
즉 하나의 테이블에 대해서 두개의 프로세스가 share 모드나 share update 모드로 Lock을 잡을 수 있다.

Enqueue는 O/S의 locking 메커니즘을 이용하여 사용자가 요구한 lock의 모드에 관한 정보를 갖고  있고 O/S lock 관리자는 Lock에 걸린 자원를 계속해서 추적한다.　만약 어떤 프로세스가  요구한 Lock 모드가 현재 허용될 수 없다면 O/S는 Lock을 요구하는 프로세스를 wait queue에 넣게 된다.



Latches 와 Enqueues의 차이는 latch는 wait하기위한 queue의  순서가 없는 반면 enqueue에는 queue의 순서가 있다는 것이다. Latch waiter는 wake up 하기위한 timer를 이용하거나, retry 또는 spin (multiprocessors 환경)을 이용한다. 모든 waiter가 동시에 retry(scheduller dependent)하기 때문에 누구든지 latch를 얻을 수 있고, 어느 경우에는 처음 시도한 프로세스가 가장 나중에 latch를 획득(get) 할 수도 있다.

ENQUEUE_RESOURCES 는 lock manager 에 의해 lock 되는 자원(resource)의 개수를 의미한다. 

이의 초기값은 SESSIONS 파라미터에 의하며, 적절히 부여하기 위해 DML_LOCKS+20 보다 크게 주어야 한다.
예를 들어 3-4 개의 세션(session) 인 경우 default 값은 20 이다.
또 4-10 세션(session) 인 경우 default 값은 ((SESSIONS - 3) * 5) + 20,  
10개 이상의 세션(session)에서는 ((SESSIONS - 10) * 2) + 55 이다.
만일 ENQUEUE_RESOURCES 를 DML_LOCKS + 20 보다 크게 한 경우 그 값이 이용된다.

만일 테이블이 많은 경우 이 값은 증가한다.
이는 한 lock 당 부여되는 것이 아니고, 자원을 사용하는 세션(session)의 개수나 cursor 개수에 관계없이 각 자원(resource) 마다 부여되기 때문이다.

Oracle8i의 경우 하나의 테이블 파티션은 병렬 삽입(PDML) 중 오직 하나의 슬래이브 만을 따른다. 이에 따라 파티 션 간의 데이타 스큐가 발생할 경우, 로드 밸런싱은 불가능하게 된다. 예를 들어 여러 애플리케이션에서 테이블은 날 짜 열 범위에 의해 파티셔닝되며 행은 주로 마지막 파티션에 삽입되다고 할 경우를 마지막 파티션에서 운영되는 슬래 이브는 다른 슬래이브에 비해 훨씬 많은 작업을 해야 한다.

Oracle9i는 인트라 파티션 병렬 기능(intra-partition parallelism)을 채용했다.

여러 슬래이브가 하나의 파티션에서 작업하도록 지원할 경우 성능 병목을 완화하는데 도움이 된다.
이는 병렬 실행의 동적 로드 밸런싱 기능의 이점을 활용하는 것이다. 이는 PDML이 지원될 경우 내부에 서 모두 자동으로 처리된다.

인서트 인트라 파티션 병렬 기능(intra-partition parallelism of Inserts)의 이점은 많은 데이타 스큐가 있을 경우 그 진가가 최대한 발휘된다. 삽입된 데이타가 균일할 경우 일반 병렬 삽입에 비해 이점이 거의 없거나 전혀 없다.

샘플 문제:
Operational Data Store 테이블 SALES_ODS의 행이 월별로 분할되는 fact 테이블 SALES_1999 에 삽입되어야 한다. 데이타 배포는 스큐가 되며 아래와 같은 그래프로 나타난다.


구현:
Oracle8i와 Oracle9i, 모두에서 이는 다음과 같이 구현된다.


다음 표는 증가하는 데이타 양에 따른 병렬 삽입의 경과 시간을 비교한 것이다.




데이타 스큐를 증가시켜 1999년 11월에 거의 스파이크에 도달하게 된다면 이보다 더 큰 성능 이점을 기대할 수 있다. Oracle8i의 병렬 삽입의 성능은 스큐의 증가에 따라 저하되지만 Oracle9i를 이용할 경우 그 성능은 일정하 다. 다음 결과는 보다 많은 스큐가 적용된 데이타 분배에 대한 것이다(아래의 배포 그래프 참조).




Oracle9i에서 인서트 인트라 파티션 병렬 기능(intra-partition parallelism of Inserts)은 다음과 같은 이점을 통해 Oracle8i Parallel Insert(PDML) 보다 나은 성능을 발휘할 수 있다.



이 POST는 "오라클 기술백서 : Oracle 9i와 DSS 환경에서의 성능과 확장성"의 일부 내용을 발췌한 것입니다.

Parallel DML은 기본적으로 session에 "enable" 되어 있지 않습니다. PDML과 serial DML의 locking, transaction, disk space requirement 등의 차이에 의해 PDML mode의 "enable"이 요구됩니다.

ALTER SESSION ENABLE PARALLEL DML;

따라서 Parallel DML이 "disable"되있을 경우 parallel hint나 table/index에 degree가 설정되어 있어도 이는 무시되게 됩니다. 물론 PDML mode가 "enable"되어 있어도 parallel hint나 table/index에 대한 degree가 설정되어 있어야 PDML로 수행 가능하다.


한 Transaction은 서로 다른 table에 대해 여러 PDML이 수행될 수 있습니다. 그러나 PDML로 변경된 table에 대해 해당 transaction 내에서 serial/parallel 명령(DML or Query)으로 access를 할 수 없습니다. 즉 commit/rollback 등으로 transaction을 완료 후에야 동일 table에 대한 operation이 가능합니다.
(참조 Note 201978.1 PDML Restrictions on Parallel DML)


Oracle 9i 이후에 intra-partition parallelism 개념이 소개되었습니다.
이 개념은 partition당 한개씩만 수행되는 parallel execution server의 제한을 완화(?) 시키는 개념입니다.
(참고 Note 241376.1 What is Intra-partition parallelism )

특정 세션의 PDML의 "enable" 여부는 v$session의 PDML_STATUS, PDDL_STATUS, PQ_STATUS column으로 확인 할 수 있습니다.

SQL> SELECT SID,PDML_STATUS, PDDL_STATUS, PQ_STATUS FROM V$SESSION;

SID	PDML_STATUS	PDDL_STATUS	PQ_STATUS
----------	------------	------------	------------
141	DISABLED	DISABLED	DISABLED
143	DISABLED	ENABLED	ENABLED
145	DISABLED	ENABLED	ENABLED
148	DISABLED	ENABLED	ENABLED
150	DISABLED	DISABLED	DISABLED

이 POST는 metalink note를 참고하여 작성되었습니다.
Note 201457.1- Introduction to PDML

Block split은 새로운 index key가 들어왔을때 기존에 할당된 block내에 저장할 영역이 없어 새로운 block을 할당 받는 index segment관련 operation 입니다.

Index Block Split은 새로 들어오는 index key 데이터에 따라 2개의 다른 방식으로 이루어 집니다.

1. index key 값이 기존의 index key 값에 비해 제일 큰 값이 아닐 경우 50/50  
   block split이 발생한다. 50/50 split은 기존에 존재하던 old block과 새로
   만들어진 new block에 50%의 데이터씩 채워져 split이 발생하게 된다.

2. index key 값이 기존의 index key 값에 비해 제일 큰값이 들어올 경우 99/1
   block split 이 발생한다. 99/1 split은 기존에 존재하던 old block에 99%의
   데이터가 있고 new block엔 새로운 데이터가 저장되게 된다.

이러한 index key의 저장 영역의 확인은 "analyze .. validate structure" 수행 후 index_stats view의 조회를 통해 확인 할 수 있습니다.

다음의 예는 간단한 테스트 입니다.

1. 계속 증가되는 데이터에 의해 99/1 split이 발생하는 예제 입니다.

SQL> drop table t1;
SQL> create table t1 (name varchar2(10),nr number) pctfree 0;
SQL> create index i1 on t1(nr);
SQL> declare
     i number;
     begin
     for i in 1..50000
     loop
     insert into t1 values('XX',i);
     end loop;
     end;
/

SQL> analyze index i1 validate structure;
SQL> select blocks, lf_blks, pct_used from index_stats;

Results:

BLOCKS: 104 LF_BLKS: 99 PCT_USED: 99

2. 데이터의 입력 순서를 바꿔 50/50 split이 발생하는 예제입니다.

SQL> drop table t2;
SQL> create table t2 (name varchar2(10),nr number) pctfree 0;
SQL> create index i2 on t2(nr);
SQL> declare
     i number;
     begin
     for i in 25000..50000
     loop
      insert into t2 values('XX',i);
     end loop;
     for i in 1..25000
     loop
      insert into t2 values('XX',i);
     end loop;
     end;
/

SQL> analyze index i2 validate structure;
SQL> select blocks, lf_blks, pct_used from index_stats;

Results:

BLOCKS: 256 LF_BLKS: 146 PCT_USED: 68

마지막의 blocks의 결과를 보시면 데이터의 입력 순서에 따라 데이터의 저장영역이 2배 이상 차이가 나는 것을 확인 할 수 있습니다.

이거 보시고 99/1로 index split을 하면 저장역역을 아낄 수 있겠지만,
예제1의 t1이 OLTP에서 여러 세션에 의해 insert 되는 table의 index라면
t1에 대한 insert 세션들이 index split을 기다리는 "enq: TX - index contention"라는
block contention event를 오랫동안 만나실 수도 있습니다. ㅎㅎ

이 내용은 metalink note 183612.1을 참고했습니다.

요 몇일은 listener 관련해서 알아볼일이 많네요.
마지막으로 trace 설정하는 방법입니다.

Bulletin no : 12060 SQL*NET V2 최적화하기 [펌]

Listener 접속 단계 및 성능 측정

client쪽에 trace 설정 방법입니다.

TRACE_LEVEL_CLIENT = 0
TRACE_DIRECTORY_CLIENT = $ORACLE_HOME/network/trace (UNIX)
TRACE_FILE_CLIENT = client

server쪽에 trace 설정 방법입니다.

TRACE_LEVEL_SERVER = 0
TRACE_DIRECTORY_SERVER = $ORACLE_HOME/network/trace (UNIX)
TRACE_FILE_SERVER = SERVER

listener re-start 나 reload가 필요합니다...

OTN의 sql*net 최적화 하는 방법에 대한 bulletin 입니다.
나온지 꽤 지난 문서 같지만 엇그제 포스팅한 Listener 접속 단계 및 성능 측정 와 연관된 내용이라
같이 올립니다.


Bulletin no : 12060  SQL*NET V2 최적화하기


1) PING
TCP/IP 네트워크상에서 ping을 사용해서 client와 server간에 걸리는 시간을 check할 수 있다.
만일 이 시간이 오래 걸리면 SQL*Net 보다 이 문제를 먼저 해결해야 한다.

사용 방법 :
ping 호스트이름
(NT의 경우에는 dos command상태에서)

2) TNSPING
이 tool은 기본적으로 설치가 되어 있으며 이 tool을 가지고 user가 client에 setting한 TNS alias(tnsnames.ora 파일안에 설정)
가 정상적으로 동작하는지를 테스트해 볼 수 있습니다.
TNSPING은 접속하고자하는 database가 있는 machine의 listener에 접속을하고 걸리는 시간을 miliseconds로 표시해 줍니다.
(실제 db와 connection을 맺는 것은 아닙니다.)

사용 방법 :
tnsping TNSalias이름
(NT의 경우에는 dos command상태에서)

3) 모든 logging 과 tracing 막기
Tracing 은 client와 server에 모두 가능하게 할 수 있습니다. 다음 parameter를 SQLNET.ORA파일과 LISTENER.ORA파일 ( $ORACLE_HOME/network(또는 net80)/admin에 위치합니다 )
에 setting하고 listener를 restart (lsnrctl stop, lsnrctl start) 하면 SQL*Net의 모든 tracing을 막을 수 있습니다.

   SQLNET.ORA:
   -----------
   TRACE_LEVEL_CLIENT =OFF
   TRACE_LEVEL_SERVER =OFF
   TNSPING.TRACE_LEVEL=OFF

   'OFF'대신에 '0'을 사용해도 됩니다.

   LISTENER.ORA:
   -------------
   TRACE_LEVEL_LISTENER=OFF
   LOGGING_LISTENER=OFF

4) Listener log 파일들 지우기
만 일 listener의 logging이 설정되어 있는 상태라면 LISTENER.LOG 파일이 이 생깁니다. listener는 connection이 맺어질대 마다 이 파일에 lock을 걸고 write하기 때문에 size가 계속 증가하게 되어 문제가 생길 수
있습니다. 만일 LISTENER.LOG 파일의 size가 너무 크게 되면 rename을 하시기 바랍니다. 그리고 listener를 restart하면 새로운 log file이 만들어 집니다.

5) SQLNET.ORA에 AUTOMATIC_IPC를 OFF로 설정
   AUTOMATIC_IPC = { ON | OFF }
위 parameter는 "SQLNET.ORA"파일에 설정할 수 있으며 ON으로 되어 있는경우 SQL*Net이 같은 alias정보를 가진 local database가 있는지 check하게 됩니다.
만일 local database가 있다면 connection은 network layer를 건너뛰고 local 'Inter Process Communication'(IPC)  connection을 맺게 됩니다.
따라서 이 setting은 database server쪽에 사용할 수 있는 것이지 client machine SQL*Net에는 아무 쓸모 없습니다.
database server쪽에 사용하더라도 local database에 SQL*Net connection이 반드시 필요한 경우가 아니라면 사용하시 않는 것(OFF로 설정)이 좋습니다.

6) SQLNET.ORA에 NAMES.DIRECTORY_PATH 설정
   NAMES.DIRECTORY_PATH = (ONAMES,TNSNAMES)
이 parameter는 TNS aliases를 찾는 경로를 지정할때 사용합니다. Oracle*Names가 설정이 안되어 있는 경우 ONAMES을 지우시는 것이 좋습니다.


7) SDU와 TDU
SDU('Session Data Unit')는 네트워크를 통해 보내는 packet의 size입니다. 이 size는 MTU(Maximum Transmission Unit)를 넘어서는 안됩니다. MTU는 네트워크상에 고정된 값입니다.
TDU('Transport Data Unit')는 SQL*Net이 data를 함께 묶는 기본 packet size 이며 SDU의 배수여야 합니다.

다음에서 예를 들어 보겠습니다.
   * SDU=1024, TDU=1536:

SQL*Net은 buffer에 1536 byptes까지 저장했다가 네트워크로 보냅니다. 낮은 network layer에서 이것을 다시 두개의 physical packets(1024,512 bytes)로 나누어 보냅니다.

   * SDU=1514, TDU=1000:
SQL*Net은 buffer에 1000 byptes까지 저장했다가 네트워크로 보냅니다. SDU는 request당 514 bytes를 더 담을 수 있는데도 불구하고 보내지기 때문에 network resource의 낭비를 초래합니다.

표준 Ethernet network에서 MTU의 default size는 1514 bytes입니다.
표준 token ring network에서 MTU의 default size는 4202 bytes입니다.

SDU와 TDU를 설정하려면 TNSNAMES.ORA 과 LISTENER.ORA 를 다음과 같이
바꾸어야 합니다.

   TNSNAMES.ORA:
   -------------
   ORCL.WORLD =
      (DESCRIPTION =
         (SDU=1514)
         (TDU=1514)
         (ADDRESS =
            (PROTOCOL = TCP)
            (HOST = fu.bar)
            (PORT = 1521)
         )
         (CONNECT_DATA = (SID = ORCL))
      )

   LISTENER.ORA:
   -------------
   SID_LIST_LISTENER =
      (SID_LIST =
         (SID_DESC =
            (SDU = 1514)
            (TDU = 1514)
            (SID_NAME = ORCL)
            (GLOBAL_DBNAME = ORCL.WORLD)
         )
      )

SDU와 TDU는 modem을 사용하는 환경에서는 줄여주는 것이 좋고 fiber나 T3 line을 사용하는 환경에서는 늘려주는 것이 좋습니다.
SDU와 TDU의 default값은 2048이고 maximum값은 32768입니다.


8) PROTOCOL.ORA의 tcp.no_delay 설정
기본적으로 SQL*Net은 SDU buffer가 찰때가지 request를 전송하지 않고 기다립니다. 다시 말해 request가 도착지점으로 바로 전송되지 않는 다는것을 의미 합니다.
그 런데 'no_delay'를 설정함으로써 data buffering을 하지 않게 할 수 있습니다. 따라서 'no_delay'를 설정하게 되면 작은 size의 patckets의 전송이 늘게되어 network traffic이 증가하게 됩니다.
따라서 이 parameter는 TCP timeout이 발생했을 경우에만 사용하셔야 합니다.

9) LISTENER.ORA의 QUEUESIZE 설정
QUEUESIZE는 listener가 저장할 수 있는 request의 수를 의미 합니다. 만일 들어오는 reqeusts의 수가 buffer의 size를 넘게 되면, 접속을 시도한 client는 접속을 실패하게 됩니다.
이 buffer의 size는 예상되는 동시 접속 수를 설정해 주는 것이 좋습니다.

   LISTENER =
     (ADDRESS_LIST =
           (ADDRESS =
             (PROTOCOL = TCP)
             (HOST = fu.bar)
             (PORT = 1521)
             (QUEUESIZE = 32)
           )
     )

이 parameter는  TCP/IP나 DECNET protocol이 사용될때만 사용됩니다.

10) SQLNET.ORA의 BREAK_POLL_SKIP 설정
이 parameter는 user break을 check하는 사이의 packet수를 지정합니다. 다시 말해 만일 이 parameter의 값이 높으면 CTRL-C checking은 덜 자주 일어나게되며 CPU overhead는 줄게 됩니다.
만일 이 parameter의 값이 낮으면 CTRL-C checking이 자주 발생되어 CPU overhead가 늘게 됩니다. 기본값은 4이며 client SQL*NET에만 사용됩니다.

11) SQLNET.ORA의 DISABLE_OOB 설정
Out of band break check를 enable시키거나 disable시킬때 사용하는 parameter입니다.
기본값은 off입니다.

여기서 잠간만 !
Out of Band Breaks란 무엇인가 ?
네트워크 통신상에서 받아들여지는 interrupt signals은 일반적으로 다른 data(예를 들어 select 문장)과 같이 도착하게 됩니다.
이것을 In-band Breaks라고 합니다. 그런데 이 interrupt signals을 connection과는 다른 channel을 통해 전달할 수 있습니다 이것을 Out of Band Breaks라고 하며 이 방식은
interrupt signal을 훨씬 빠르게 그리고 효과적으로 전달 할 수 있습니다. (예를 들어 deadlock을 break하기위해 control-C를 사용하는 것)

12) PROCESS.DAT와 REGID.DAT
7.3.2 버전에서 Oracle Server Tracing은 기본적으로 enabled되어 있습니다. 따라서 모든 connection과 request가 PROCESS.DAT와 REGID.DAT에
기록이됩니다. database의 사용기간이 길어지면 이 파일들은 접속속도를 현저히 떨어뜨리게 됩니다.
이러한 trace 파일들을 사용하지 않기위해 listener.ora파일에 'EPC_DISBLED=TRUE'를 설정해야 합니다.



Oracle Korea Customer Support Technical Bulletins

다음의 내용은 listener 접속시 지연현상에 대해 분석할 만한 방법에 대한 내용입니다.
요건 oracle metalink Note 214022.1를 참조한 내용입니다.

우선 SQLNET 관련 performance를 측정하기 위해서는 우선 sqlnet trace를 설정하고 그에 따라 소요 시간을 측정해야 합니다.

client에서 server side의 listener로 접속이전에 client 내의 connect descriptor에 대한 해석이 먼저 이루어 져야 합니다. oracle net trace file내에서 이 시점을 측정하기 위해서는 trace file이 생성된 시점과 "niotns: setting up interrupt handler"가 나오는 부분까지로 보시면 됩니다.

일반적으로 connect descriptor는 tnsnames.ora에 기술하여 사용합니다만.. 가장 빠른 방법은 command line에 다음과 같이 기술하는 방법입니다.


이 방법은 AP에 hard coding이 필요하므로 권장되는 방법은 아니지만, 성능 측정에서는 사용할만 합니다.

다음 방법은 잘 알려진 tnsnames.ora에 connect descriptor를 기술하는 방법입니다. 이때 NAMES.DEFAULT_DOMAIN 확장없이 속도를 측정해 볼 수 있습니다.

sqlplus scott/tiger@iasdb

다음은 NAMES.DEFAULT_DOMAIN 확장을 사용한 방법입니다.

sqlplus scott/tiger@iasdb.us.oracle.com

여기까지로 connect descriptor의 해석 과정을 측정해 볼수 있습니다.


또한가지 client에서 connect descriptor를 network protocol location이나 database service name으로 변경하는 단계에 영향을 줄 수 있는 것이 하나 더 있습니다. 그게 NAMES.DIRECTORY_PATH 입니다.

oracle net은 sqlnet.ora 내에 정의된 NAMES.DIRECTORY_PATH에 기술된 여러 방법을 참고해 connect descriptor를 가져오게 됩니다. 물론 default는 tnsnames.ora를 확인하는 방법이죠.

만약 oracle internet directory 등의 external naming method를 사용할 경우 external naming source에 대한 network round-trip이 필요합니다. 이러한 external naming source에 대한 속도는 network bandwidth나 system resource 등의 많은 부분에 영향을 받을 수 있습니다.

이러한 변수를 줄이기 위해서 oracle internet directory server가 위치한 동일 시스템에서 다음의 테스트를 진행해 볼수 있습니다.

Oracle Net이 connect descriptor를 network protocol-specific 정보로 변환하였다면 이제 드디어 oracle listener로 접속을 하게 됩니다.

일반적인 Network protocol은 응답이나 처리에서 오류가 발생할 경우를 대비한 응답 mechanism을 가지고 있습니다. 또 packet 실패의 경우 해당 packet을 재전송하는데, 이러한 network lose나 drop 등 network protocol 요소에 대한 tuning이 우선되어야 합니다.
(network protocol은 아는게 전혀 없으므로 통과 ~)

network protocol이 oracle listener로 접속된 이후에 listener는 여러가지 작업을 수행하는데, 그 중 부하가 높은 부분 중 하나가 server process를 생성하는 작업입니다.

일반적으로 process의 생성은 많은 메모리와 CPU, 시간을 소모하죠. 따라서 server process가 미리 생성되어 있다면 속도향상을 꾀할 수 있습니다. 이러한 환경이 pmon에 의해 미리 띄워지는 shared server 환경이죠..
그러나 요즘 shared server 환경은 많이 사용하지 않으므로 생략 합니다.
(왠지 날로 먹는 듯한... --;)

여기까지는 환경 설정 단계에서 잘설정하고 network에 큰 문제가 없으면 굳이 문제될 부분은 아닙니다만 주로 문제는 listener에 대한 동시 접속이 몰리는 경우에 발생합니다. 이런 경우 listener에 동시에 접속할 수 있는 queue 설정과 multi-listener를 설정합니다.

queue size의 경우 listener가 동시에 처리할수 있는 request 수를 지정해 줍니다. 이 값은 각 protocol의 설정값에 따라, system에 따라 달라지므로 적정값을 확인해 봐야 겠죠. multi-listener의 설정은 multi-cpu 환경에서 listener 가용성을 키우는 좋은 방법입니다.

   LISTENER =
     (ADDRESS_LIST =
           (ADDRESS =
             (PROTOCOL = TCP)
             (HOST = marvin)
             (PORT = 1521)
             (QUEUESIZE = 32)
           )
     )

oracle sequence는 순차적으로 특정 interval로 번호를 부여해 주는 object 입니다. 

sequence 생성시 cache option을 줄수 있는데, 이는 지정한 갯수의 sequence를 미리 
일정 부분 생성해 library cache영역에 저장해 두는 기능을 합니다. 

cache option을 사용하지 않을 경우 매번 next value를 참조할 때 disk I/O를 발생하게 
됩니다. 또 sequence관련 row cache lock이 발생하는 경우도 있죠. 

이러한 현상을 완화 시키기 위해 자주 사용되는 sequence에 대해서는 cache 기능을 부여하게됩니다. 

alter sequence seq cache 100;

그러나 sequence 생성시 cache 기능을 사용할 경우  "cache aging out"과 "db re-start" 
event로 인해 sequence number가 skip 될 수 있습니다. 더구나 sequence cache는 oracle 7.2 이전엔
row cache에 저장되었으나 7.3 이후 library cache에 저장되어 더 자주 aging-out 현상이 발생하게 
됩니다. 

이러한 현상을 최소화하기 위해 sequence를 pining 할 수 있습니다. 
sequence pin은 (물론 다른 object도 마찬가지지만) dbms_shared_pool package의 keep procedure를 
이용할 수 있습니다. 이 package는 default로 설치되지 않으며, dbmspool.sql 수행으로 설치 할 수 있습니다. 

dbms_shared_pool.keep('seq', 'Q'). 

참조 : oracle metalink Note 62002.1 Caching Oracle Sequences

가끔 RAC나 CRS 관련해 문서를 보다보면 I/O fencing이라는 말이 나오는데, 이게 무슨 말일까요?

다음은 Cluster I/O fencing에 대한 설명입니다.

There will be some situations where the leftover write operations from failed database instances reach the storage system after the recovery process starts, such as when the cluster function failed on the nodes, but the nodes are still running at OS level.

Since these write operations are no longer in the proper serial order, they can damage the consistency of the stored data. Therefore, when a cluster node fails, the failed node needs to be fenced off from all the shared disk devices or disk groups.

This methodology is called I/O Fencing, sometimes called Disk Fencing or failure fencing.

정리해 보자면, cluster 구성에서 떨어져 나간 노드의 I/O를 방지하는 방법?

사용자 process가 SGA내의 database buffer cache나 dirty buffer를 포함하는 LRU chain을 scan하기 위한 latch이다.

+ cache buffer handles - buffer cache내의 buffer header에는 다음의 두 가지 list를 포함하고 있다.
 1. user list: doubly linked list로 연결되어 있는 "handle"을 포함한다. 여기에서 handle은 해당 buffer를 사용하고 있는 oracle process를 가리키는 정보를 담고 있다.
 2. waiter list: 이 list도 doubly linked 되어 있는 "handle"의 list를 포함하고 있는데, 여기에서의 handle은 해당 buffer를 사용하기 위해 기다리고 있는 oracle process에 대한 정보를 담고 있다.
cache buffer handle latch를 확보한 뒤, process에 buffer handle을 할당하고 나서 latch를 푼다.

+ cache buffers chains -  foreground process가 buffer를 변경하기 전에 잡아야 하는latch로 복수 사용자에 의해 동시에 변경되는 것을 막아준다. 하나의 latch에 대해서 여러 개의buffer가 DBA를 이용하여 hash되어진다.
    latch contention이 심한 경우, 특정한 particular hash list가 크게 증가하였거나, 하나의 block에 대해서 CR copy가 여러 개 존재하는 경우이다. 다음과 같은 query를 이용하여 그러한 경우인지를 확인한다.
    select dbarfil "File #", dbablk "Block #",count(*)
    from x$bh
    group by dbarfil, dbablk
    having count(*) > 1 ;

+ cache buffers lru chain - LRU list를 보호하기 위한 latch이다. buffer를 이 list에 옮기려면 일단 이 latch를 잡아야 한다. LRU latch이 개수는 {_}db_block_lru_latches로 결정된다. 이 값은 기본적으로 Oracle8.0이전은 CPU_COUNT/2, Oracle8i의 경우 CPU_COUNT, Oracle9i의 경우 CPU_COUNT*4 이다. ( Buffer Cache의 크기에 따라 변경될 수 있다)

Latch는 SGA의 공유 데이터구조를 보호하기 위해서 사용되는 기법으로 빨리 획득되고 풀리는 lock의 일종이다. 일반적으로, Latch는 한 순간에 하나 이상의 프로세스가 동시에 같은 실행코드(code)를 수행하는 것을 방지하는데 사용된다. 그렇게 함으로서 SGA의 공유 데이터 구조를 보호하게 된다.
Latch에는 Wait latch (“willing-to-wait”)와 Nowait latch (immediate) 2종류가 있다. Latch의 모든 항목은 99%이상의 Hit Ratio를 유지해야 한다. 그렇지 않은 경우 Latch Contention이 발생되고 있다고 판단 할 수 있다.

+ Wait latches (“willing-to-wait”)
 - Gets -  Latch획득에 성공한 횟수.
 - Misses -  최초 Latch의 획득을 실패한 횟수.
 - Sleeps  - 최초 Latch의 획득을 실패한 이후로 Latch획득을 시도한 횟수.
 - Latch get hit ratio = (gets - misses) / gets, 이 수치는 1에 가까워야 한다.

+ Nowait latches (immediate)
 - immediate gets - immediate call에 의한 Latch획득에 성공한 횟수.
 - Immediate misses - immediate call에 의한 최초 Latch의 획득을 실패한 횟수.
 - Nowait latches와 관련된 Sleeps은 없다.
 - Nowait hit ratio = (nowait_gets - nowait_misses) / nowait_gets, 이 수치는 또한 1에 가까워야 한다.

Oracle은 Buffer Cache 를 관리하기 위해서는 세 가지의 내부적인 structure를 사용하는데, 그 각각은 cache buffer chain, LRUW(dirty list), LRU(Least Recently Used list)이다.  buffer cache management의 기본을 이루는 이 세가지 list를 관리하면서 사용자에게 필요한 buffer를 사용가능 하도록 제공하여 주는 역할을 하는 것은 DBWR이다.  DBWR은 데이터화일에 대한 대부분의 Write 작업을 수행하는 프로세스이기도하다.(일부 CKPT(Checkpoint Process)에 의해 데이터화일의 Header는 주기적으로 Write된다). 또한, DBWR은 startup시 각 online datafile에 대해서 Media Recovery (MR) lock을 획득하는 등 데이타베이스 file의 관리자로도 간주된다.

Cache Buffer Chain
cache buffer chain list는 hashed chain list라고도 하며, doubly-linked hash list로 연결된 hash table (또는 hash bucket)로 구성되어진다. 이 has bucket은 instance startup시에 할당되며, 실제 buffer block으로 구성된 것은 아니고 buffer header만을 포함하게 된다.
hash bucket의 개수는 기본적으로 Oracle8.0 이하에서는 db_block_buffers/4, Oracle8i 에서는 db_block_buffers*2, Oracle9i에서는 _db_block_buffers*2 보다 큰 최소의 소수(prime number)가 되며, init$ORACLE_SID.ora에 명시적으로 _db_block_hash_buckets parameter에 의해 지정할 수 있다.
이 cache buffer chain에 존재하는 buffer들은 LRU list나 LRUW list (dirty list) 중의 하나에 위치하게 되며, 두 list 모두에 포함되지는 않는다. buffer들은 data block address(DBA)에 의해서 hash되어 hash table에 할당된다.
이상의 Cache Buffer Chain 을 관리하는 Latch는 cache buffers chains latch이고, _db_block_hash_latchesd에 의해 제어되며, default로 _db_block_buffers /128 값보다 크거나 같은 2의 승수로 결정된다. (_db_block_buffers 또는 db_block_buffers) < 4096 이면, _db_block_hash_latches는 1024로 결정된다. 대부분의 경우 기본값으로 충분한 성능을 발휘한다.
Oracle8i까지는 이러한 Cache 관련 List 등이 Shared Pool 에 존재했으나, Oracle9i에서부터는 Buffer Cache 영역에 존재한다.

LRU list
least recently used list 혹은 replacement list라 불리는 것으로, 이 LRU list의 head부분에는 가장 최근에 사용된 MRU buffer들을 포함한다. 특별한 경우를 제외하고는, 모든 새로운 block들은 모두 MRU end에 위치하며, LRU의 끝부분은 최근에 사용되지 않은, 곧 재사용될 buffer들이 위치한다. 그러므로 foreground process는 빈 buffer를 얻기 위해 LRU의 끝부분부터 찾기 시작한다. 이 LRU의 buffer들은 free, pinned, dirty 세가지 중 하나를 가지며 각각은 다음과 같은 특성을 가진다.

- pinned buffer: 현재 user에 의해 사용 중이어서, 재사용될 수 없는 상태의 buffer이며, pinned clean 혹은 pinned dirty로 다시 나뉘어질 수 있다.
- free buffer: 사용되지 않은 buffer, 즉 disk block이 읽혀져서 할당되어 사용될 buffer이다.
- dirty buffer: dirty buffer는 pinned dirty buffer와 마찬가지로 user가 사용하여 내용이 변경된 buffer이다. 그러나 pinned buffer가 현재 사용중이서 재사용될 수 없는 반면에, dirty buffer는 현재 사용중인 user나 waiter는 없기 때문에 LRUW list로 옮겨질 수 있고, 결국은 disk로 write될 buffer이다.

위에서 언급한 새로 읽어 들인 block중 MRU end부분에 위치하지 않는 특별한 경우란, 바로  Full Table Scan이다. Full Table Scan으로 읽은 table은 LRU list의 끝부분에 위치하게 된다. 이렇게 LRU end에 위치시키는 이유는 Full Table Scan으로 읽은 block은 다시 access할 확률이 적어서 곧 다시 재사용될 수 있도록 한 것이다.
그러나 Full Table Scan의 경우에도 매우 중요하고 자주 사용되어 MRU end에 전체 table의 내용을 모두 cache시키고 싶은 경우도 있을 수 있다. 이러한 경우에는 다음과 같이 CACHE을 사용하면 된다.
SQL> alter table dept cache;
SQL> select /*+ cache(a) */ * from emp a;
이러한 CACHE절은 작은 table에만 사용하는 것이 바람직하며, 큰 table에 사용하게 되면 buffer의 MRU end쪽의 대부분의 buffer를 이 하나의 table이 차지하게 되는 현상이 발생 가능하다. 그리고 이 CACHE절로 MRU end에 위치한 table도 이후 다른 TABLE이 계속 사용되어짐에 따라 LRU end쪽으로 점차 이동하다 disk로 write되고 Buffer Cache에서 사라질 수 있다.

LRUW list
dirty list라고도 불리며, DBWR는 이 list의 buffer의 내용을 disk에 write하여 빈 buffer로 만든다. buffer를 LRUW list로 옮기고 결국은 disk에 write하는 것이 DBWR의 기본 기능이라 할 수 있다.

모든 사용자 프로세스는 먼저 Redo Log Buffer가 생성 되어야만 Redo record Block을 변경할 수 있다.  즉, 첫 redo allocation latch를 할당 받고 난 다음 redo copy latch를 요구한다. 사용자 프로세스가 redo log buffer를 할당 받기 위해선 ‘redo allocation latch’ 를 먼저 할당 받고 ‘redo copy latch’를 획득 하여야 한다. ‘redo allocation latch’는 하나의 instance에 단지 하나만이 존재 하며, 'redo copy latch'는 Default로 CPU*2로 설정되어 있다. 다중 사용자 환경에서 이 redo allocation latch에 대한 경합을 줄이는 것(즉 사용자 프로세스당 redo allocation latch의 사용 시간을 최대한으로 줄이는 것)이 성능 향상에 도움이 된다.

l    Redo entry의 기록절차
1.    redo allocation latch획득으로 Log Buffer Position 확보
2.    redo copy latch획득
3.    redo log buffer allocation
4.    release redo allocation latch
5.    redo entry를 redo log buffer로 복제
6.    release redo copy latch

오라클에서 완벽하게(!) 최적화된(!) 새로운 storage 제품이 나왔답니다.
한번 읽어보세요..

Exadata Architecture

ORACLE EXADATA STORAGE SERVER