에너쓰오라클

library cache lock/pin은 library cache내의 object에 대한 매우 빠른 시간내에 처리되는 lock 메카니즘입니다.

따라서 일반적인 운영상황에서는 library cache lock/pin 등의 contention을 dump 등으로 정보를 수집하기는 엄청어렵죠.
뭐 운영중 DDL을 수행하면 library cache lock/pin을 유발할 순 있겠지만..

다음의 시나리오는 2006년 가을호 오라클 매거진에 있는 hang을 유발하는 시나리오 입니다.
(보고 싶은신 분들은 www.oracle.com에 가면 아마~ 있을 겁니다.)

전통적인 Oracle 접속 방식인 "Dedicated Server"와 "Shared Server" 방식외에 Oracle 11g에서 DRCP (Database Resident Connection Pooling)라는 접속 방식이 추가 되었습니다.


메뉴얼을 보면 각각의 접속 방식에 소모되는 memory를 계산해 놓은 부분이 있는데, 요거 보면 적용할 만 하겠다 싶긴 합니다.

그러나 memory 사용량을 보면 혹할수도 있겠는데, 만약 concurrent가 5000이라고 가정 한다면,
DRCP의 경우는 100개만 접속 해 있고 나머지는 queue에 대기하고 있는 모양이겠죠?

shared server의 경우 100개만 떠있어도 100개의 shared server가 5000개의 세션을 cover 해주고 있으니,
DRCP와 dedicate/ shared server와 비교하는 것 자체가 사실 말이 안될 수 도 있습니다.


설정 방법도 별로 어렵지 않습니다. 서버단에서 process parameter 좀 늘려주고 다음 처럼 수행하고, client 단에서 tnsnaems.ora의 설정 부분에 SERVER를 POOLED라고만 정의하면 되네요. 


딱히 테스트 해보고 올린 post가 아니니 잘못된 부분이 있으면 가차없이 알려주세요. ^^

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



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


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

이 두개의 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를 준 이점이 하나도 없다.

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



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

AWR table map을 찾던 중 "A Tour of the AWR Tables"라는 NOCOUG Summer Conference에서 소개된 자료를 확인할 수 있었습니다. 제목을 보니 AWR관련 table에 대한 여러 힌트를 얻을 수 있을 듯해서 자료를 보니 "load spike"를 찾기 위한 좀 다른 방법을 설명하고 있네요.

이전 " oracle awr 설정 시 고려해야 할 점" post에서 강호동, 이건희 회장까지 등장시키며 AWR 자료 수집 주기를 15 ~20분으로 줄여야한다고 글을 썼는데, 이 글의 저자는 "In my experience, the hourly interval is appropriate." 라고 하더군요. 저자는 DBA_HIST_SYSSTAT의 DB time, elapsed time 등의 load profile 자료를 사용하지 않고 1초마다 수집되는 ASH의 자료를 이용해 "load spike"를 찾는 방법을 제시하고 있습니다.

오옷 .. 그런 방법이.. 근데, 그 외의 성능관련 정보는 어떻게... --;
추가적인 AWR 관련 table을 이용한 문제가 될만한 SQL 찾는 방법 등도 같이 소개합니다.

Average Active Session이 튀는 시간 찾기.

Average Active Session이 튀는 시간대의 특정 분단위 찾기.


 

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

사용자 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의 크기에 따라 변경될 수 있다)

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