데이타의 concurrency를 보장하기 위해 오라클은 lock과 transaction을 사용한다.
Lock은 같은 자원을 access하는 사용자들 사이에 상호간에 해를 끼치는 것을 예방하기
위해서 사용되는 메카니즘이다.
Lock의 종류
- Exclusive : lock이 걸린 자원의 공유를 허용하지 않는다.
- Share : 자원에 대해 수행되는 명령의 유형에 따라 lock된 자원의 공유되는 것을
허용한다.
오라클에서의 Lock의 일반적인 범주
- Data or DML (row locks TX and table locks TM) : 동시에 다중의 사용자에 의해
access되는 테이블 데이타의 보호를 위해 사용된다.
- Dictionary or DDL(TD) : 트랜잭션에서 access되는 테이블과 같은 Object의 정의를
보호하기 위해서 사용한다.
- Internal and Latches (RT, MR) : SGA 영역에서의 내부적인 데이타베이스와 메모리
구조를 보호하기 위해 사용한다.
1. TABLE LOCKS
테이블의 특정한 row를 수정하는 문장은 항상 그러한 row에 대해 exclusive row lock
을 획득하고 테이블 lock을 전유한다.
(1) Row Share Table Locks (RS)
- row를 lock 시키고 lock된 테이블을 UPDATE 할 목적이다.
- 모든 row를 SELECT 하려는 다른 트랜잭션을 허용한다.
- 동일 테이블에서 lock 되지 않은 row를 INSERT, UPDATE, DELETE 하는 다른
트랜잭션을 허용한다.
- 테이블에 대한 ROW SHARE, ROW EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE lock
을 획득하려는 다른 트랜잭션을 허용한다.
- EXCLUSIVE 모드에서의 테이블 locking으로부터 다른 트랜잭션을 예방한다.
(2) Row Exclusive Table Locks (RX)
- row를 lock 시키고 테이블에서 row를 변화시킨다.
- 모든 row를 SELECT 하려는 다른 트랜잭션을 허용한다.
- 동일 테이블에서 lock 되지 않은 row를 INSERT, UPDATE, DELETE 하는 다른
트랜잭션을 허용한다.
- 테이블에 대한 ROW SHARE, ROW EXCLUSIVE lock을 획득하려는 다른 트랜잭션을
허용한다.
- SHARE, EXCLUSIVE, SHARE ROW EXCLUSIVE 모드에서의 테이블 locking으로부터
다른 트랜잭션을 예방한다.
(3) Share Table Locks (S)
- 다른 트랜잭션을 제공하지 않는 테이블에서 row를 INSERT, UPDATE, DELETE
하는 것이 SHARE lock을 홀딩한다.
- 동일 테이블에서 지정된 row를 QUERY 또는 LOCK 하려는 다른 트랜잭션을
허용한다.
- 테이블에 대해 더 나아가 SHARE lock을 얻으려는 다른 트랜잭션을 허용한다.
- EXCLUSIVE 또는 SHARE ROW EXCLUSIVE 모드로 테이블을 locking 하는 것으로
부터 다른 트랜잭션을 보호한다.
(4) Share Row Exclusive Table Locks (SRX)
- 테이블에 row를 INSERT, UPDATE, DELETE 한다.
- 동일 테이블에서 지정된 row를 QUERY 또는 LOCK 하려는 다른 트랜잭션을
허용한다.
- SHARE, EXCLUSIVE 또는 SHARE ROW EXCLUSIVE 모드로 테이블을 locking 하는 것으로
부터 다른 트랜잭션을 보호한다.
(5) Exclusive Table Locks (S)
- 테이블에 row를 INSERT, UPDATE, DELETE 한다.
- 동일 테이블에서 지정된 row를 QUERY하는 것에 대해서만 다른 트랜잭션을
허용한다.
- 어떠한 lock 문장을 내리는 것으로부터 다른 트랜잭션을 보호한다.
2. DICTIONARY LOCKS
Dictionary lock은 Object에 대한 DDL 명령이 수행되는 동안 Object의 정의를
보호한다.
3. INTERNAL LOCKS 과 LATCHES
Latches 와 Internal lock은 메모리 구조를 보호하기 위한 메카니즘이다.
- Latches 는 SGA 영역에 있는 shared data structure를 보호하기 위한 low-level
연속 메카니즘이다.
- Internallock은 data dictionary entry를 보호하고 database file, tablespace,
rollback segment를 lock한다.
Latches 와 Internal locks는 데이타베이스 사용자에 의해 컨트롤 될 수 없다.
LOCK에 대한 모니터
lock contention 과 병목현상은 시스템의 효능을 저하시킨다.
1. 모니터 방법
(1) SQL*DBA 모니터 lock display
(2) V$LOCK view
(3) utllockt.sql 이라는 스크립트 화일의 내용을 확인
(lock이 된 자원을 기다리는 사용자가 있는지를 보여줌)
2. SQL*DBA lock monitor 를 사용한 모니터 방법
Lock 모니터의 Resource ID 1 필드는 DBA_OBJECTS 에서의 object_id 이다.
Lock 모니터의 특정한 Resource ID 1 과 일치하는 테이블명을 찾기 위해서는
다음 Query를 사용한다.
SQL> SELECT owner, object_id, object_name, object_type
FROM dba_objects
WHERE object_id = resource id #;
▶ LOCK monitor의 컬럼들
Username : 연결된 사용자의 명
Session ID : 사용자 연결을 식별하기 위해 오라클에서 제공되는 ID
Serial Number : 세션의 시리얼 번호로 각각의 세션을 유일한 번호로 지정하기 위해
Session ID를 함께 사용한다.
Lock Type : TM은 데이타 조작에 대해 dictionary lock을 표시한다.
Resource ID 1 필드는 lock이 걸린 테이블의 ID이다.
테이블명을 찾아보려면 DBA_OBJECTS 쿼리를 사용한다.
TM lock은 트랜잭션 lock이다.
지시하는 row는 lock이 걸리거나 lock 되기 위해 요청된다.
Resource ID 1 : TM 형태의 lock에 대해 DBA_OBJECTS view로 찾은 테이블 ID를
보여준다. 다른 값은 무시될 수 있다.
Resource ID 2 : 내부적으로 lock의 형태를 식별하기 위해 사용한다.
Mode Held : 자원을 쥐고 있는 lock의 모드를 나타낸다.
Mode Requested : 자원에게 요청된 lock의 모드를 나타낸다.
CONTEXT SIZE & CURSORS
1. Context size 에 관련한 error message
.ora-1046 :can't acquire space to extend context area.
.ora-1050 :can't acquire space to open context area.
.ora-1051 :maximum context area extents exceeded.
2. Context size란 무엇인가?
(1) Cursor의 initial size이다 . 즉, Cursor 에 allocate 되는 user memory이다.
(2) 이는 init.ora 의 CONTEXT_SIZE 에 의해 결정된다.
(3) Cursor 에 할당되는 additional space 는 CONTEXT_INCR 에 의하며 50 extents를
갖는다.
(4) Recommended context size increment 는 4096 bytes(4K)이다.
(5) SQL statement가 수행시마다 cursor 가 open 되며, 같은 cursor가 reuse 되도록
design 되어 SQL*PLUS session은 2-3 개 이상 open 되어지지 않는다.
그러나 SQL*FORMS 는 여러 다른 task 를 수행하므로 많은 cursor를 open한다.
(100 or more)
(6)Cursor 가 hold 하는 item
* the SQL statement
* the parsed SQL statement
* one row of the result
3. ORA-1051 은 무엇이 문제인가?
(1) cursor 의 size 를 줄인다.
(2) CONTEXT_SIZE,CONTEXT_INCR 를 늘린다.
4. OPEN_CURSORS 수를 줄이는 전략
(1) Commit을 자주한다.
(2) Synonym이나 view 를 사용하지 않음으로써 implicit cursor 수를 줄인다.
(3)SQL*FORMS 에서 select 문대신 #COPY 로 바꿔 사용한다.
(4)SQL*FORMS 에서 large forms를 여러개의 작은 forms 로 나눈다.
(5)ASAP, EXEC SQL CLOSE C1; 을 수행한다.
(6)HOLD_CURSOR=NO & RELEASE_CURSOR=YES 를 사용한다.
DB_FILE_MULTIBLOCK_READ_COUNT와 퍼포먼스
db_file_multiblock_read_count 파라미터는 한번의 I/O 작업으로 읽어들이는
최대 블럭 수를 가리키며 Multi Block I/O 에 관련된 파라미터이다. 디폴트 값은
O/S에 따라서 다른데 보통 db_block_buffers와 Processes에 의해서 결정되며 4~32
정도가 많이 사용된다.
이 값을 키우고 SORT/MERGE를 이용하는 것이 Nested Loop를 이용하는 경우 보다
퍼포먼스가 증가하는가 하는 문제는 Query의 종류와 읽어들이는 데이타의 양
(전체에 대한 Percentage)에 따라서 달라지게 된다.
즉, 이 값이 크면 한번의 I/O로 여러개의 블럭을 읽어오므로 Full Table Scan시
에는 퍼포먼스가 증가한다.
하지만 특정 블럭만이 필요한 경우에 불필요한 블럭까지 함께 읽어들이므로
디스크를 읽는 시간이 증가할 뿐 아니라 크기가 한정된 Buffer Pool을 많이 차지해
함으로써 자주 쓰이는 데이타를 밀어내어 오히려 Cache의 효율을 떨어뜨리는 결과를
가져올 수도 있으므로 작업특성에 따라서 적절한 값을 세팅하여야 한다.
오라클의 Optimizer는 이 값이 크면 작업의 효율성을 위해서 Full Table Scan을
선택할 가능성이 커지게 된다.
CONNET INTERNAL에 대해서 PASSWORD 설정
connect internal 시에 password 를 입력하도록 하려면 다음과 같이 하면 된다.
DB Extender와 같은 Client Tool을 이용하여 서버에 접속하는 경우에도
마찬가지로 적용된다.
1. orapwd를 이용해서 password file을 만들어 준다.
orapwd file=orapwORA7 password=manager entries=1
이 화일은 $ORACLE_HOME/dbs 디렉토리에 만들어 주며 ORACLE SID가 ORA7인경우
화일 이름은 orapwORA7 으로 해준다.
2. initORA7.ora 화일에서 다음을 추가한다.
remote_login_passwordfile=exclusive
compatible=7.1 또는 compatible=7.2
이렇게만 해 주면 connect internal 시의 password가 manager로 세팅된다.
DB Extender에서 접속할 때 ORA-9910 에러가 발생하는 경우도 똑같이 이 방법대로
password file을 만들어 주면 된다.
이 경우에는 initORA7.ora 화일에 remote-login_passwordfile을 세팅할 필요는 없고
compatible 만 세팅하고 password file을 만들어 주면 된다.
Posted by clubkona
