제5장 문 처리 SQL 문의 유형에 대한 이해

• DDL(데이터 정의 언어) : 오라클 데이터 사전을 수정, 즉 테이블 생성, 사용자 추가, 테이블로 부터 열삭제, 트리거 생성 등을 수행하기 위하여 실행되는 문
• DML(데이터 조작 언어) : 데이터베이스의 데이터를 액세스하고 수정하기 위하여 실행되는 문으로서 SELECT, INSERT, MERGE, UPDATE, DELETE명령을 포함한다.

문은 어떻게 실행되는가?

• 파싱 : 제출된 문의 유효성을 검증하기 위하여 구문과 의미를 검사한다.
• 최적화 : 데이터베이스에서 문을 수행하기 위하여 사용될 수 있는 최적의 계획을 생성한다.
• 행-원본 생성 : 최적화된 계획을 취하여 실행 계획, 즉 실행을 위한 트리를 만든다.
• 문 실행 : 실제로 쿼리를 수행하기 위하여 행-원본 생성 과정의 결과를 실행한다. DDL또는 수정 DML 문의 경우에는 이 과정이 마지막이지만 쿼리 문의 경우에는 이 과정이 클라이언트 애플리케이션이 데이터를 검색하는데 활용하는 인출 단계의 시작이다.

파싱

• 오라클은 SQL 문의 종류를 결정하며 DDL문과 DML문은 서로 다른 과정을 밟는다.
• DDL문은 오라클 풀에 저장되지 않기 때문에 공유 풀 검사의 대상이 아니다.
• DML문 인 경우 아래 과정을 거친다.
• 구문분석 : 쿼리가 유효한 SQL인가?
• 의미분석 : 쿼리가 유효한 SQL인 경우 이치에 맞는지, 엑세스 가능한 객체를 액세스 하고 있는지등을 판단함
• 공유 풀 검사 : 이전에 누군가에 의해 파싱되었으며 오라클이 이미 수행된 작업을 재 사용할 수 있는가?
• 소프트 파스와 하드 파스로 수행되어 지는데 DDL은 항상 하드파스 형태로 수행되며 결코 재사용 되지 않는다.

최적화와 행-원본 생성

• DML문은 일생동안 최소한 한번은 최적화 된다. (하드파스 과정에서 발생한다.)
• CPU작업을 필요로 하는 절차로서 실제로 문이 실행되는 시간보다 오래 수행될 수도 있다.
• 하드파싱은 CPU사이클을 소비할 뿐만 아니라 공유풀에 높은 래치를 유발한다.

• 규칙기반의 최적화기(RBO) : 쿼리계획이 잘 알려진 규칙의 집합에 근거하여 작성된다. 성문화된 규칙만이 적용된다.
• 비용기반의 최적화기(CBO) : 엑세스되는 실제 데이터에 관한 통계정보에 근거하여 쿼리가 최적화 된다.

실행

• 행-원본 생성기의 출력을 위하여 실제로 문을 실행한다. 이것만이 DML 실행의 필수 과정이다.

오라클의 COMMIT과정

1. 서버 프로세스는 SCN과 함께 커밋 기록을 리두 로그 버퍼에 위치 시킴
2. LGWR은 모든 리두 로그 버퍼 엔트리와 커밋 기록을 리두 로그 파일에 연속적으로 옮김(이후 오라클 서버는 실패하는 경우에도 변경 사항을 유지)
3. 사용자에게 COMMIT이 완료되었다는 것을 알림
4. 서버 프로세스는 트랜잭션이 완료되었으며 자원 잠금(lock)이 해제되었다는 것을 나타내는 정보를 기록

SCN(System Change Number)

• 트랜잭션이 커밋될 때마다 오라클 서버는 트랜잭션에 커밋 SCN(System Change Number)을 지정
• 데이터베이스 내에서 Unique
• SCN을 사용함으로써 오라클 서버는 운영 체제의 날짜나 시간에 의존하지 않고 일관성 검사를 수행가능

Parse(구문분석)

• User Process는 쿼리문 분석 또는 컴파일하라는 요구와 함께 Server Process 로 쿼리문을 보낸다.
• Server Process 는 명령의 유효성을 검사한 후 SGA의 공유풀을 사용하여 문장을 컴파일 하고 실행 계획을 세운다
• Syntax Check
• Semantic Check( 의미론적 Check )
  • Object resolution
  • 권한유무
  • Lock( DDL 방지)
• ParseForm( ParseTree )생성[ 컴파일된 상태 ] + execution plan 생성( Optimazer )
• 여기에서 SQL 문, Parse Form, Execution Plan은 Library Cache 안에 저장됨/ Object resolution, 권한유무는 DD( Data Dictionary )에서 찾음.

Execute( 실행 )

• 서버 프로세스는 데이터를 읽어올 준비가 된다.

Fetch( 인출 )

• 오라클 서버가 쿼리문에의해 읽혀진 행들을 사용자에게 전송한다.
• 전송에 이용하는 메모리양에 따라 사용자에게 쿼리 결과를 전송하기 위해 한번 또는 여러번 인출이 필요할 수 있다.
• 동일 SQL 문으로 인식하는 경우
  • 대소문자 동일
  • 공백(sapce)동일(8i 부터는 더블 공백도 하나의 공백으로 처리)
  • Schema(=User)
  • 변수 : * / 로 재실행할 경우 parsing 은 안하지만 권한 검사는 한다. 동일 문장 실행시에도 같음

수정 DML의 시작과 끝

Data Block & Rollback Block 잡는다

• 데이터베이스 버퍼 캐시에 데이터와 롤백 블록이 존재하지 않다면 서버 프로세스는 데이터 파일에서 읽어들여서 데이터베이스 버퍼 캐시에 블록의 사본을 위치 시킨다.

Lock 을 건다

• 서버프로세스가 수정될 행에 lock 을 건다

Redo Log Buffer에 기록

• 서버 프로세스가 롤백 블록과 데이터에 가해진 변경 사항을 리두 로그 버퍼에 기록한다.

• 롤백 블록에는 데이터가 수정되기 전의 이미지 (Before Image)가 기록된다. 그래서 DML문장에 대해서는 필요한 경우에 롤백이 가능하다.
• 데이터 블록에는 데이터의 새로운 값이 기록된다.

Rollback Block 에 기록후 Data Block 에 쓴다.

• 서버프로세스는 데이터베이스 버퍼 캐시에서 이전 이미지를 롤백 블록에 기록한 후에 데이터 블록을 변경. 변경된 두 블록은 dirty buffer 즉, 디스크의 해당 블록과 같지 않은 버퍼로 표시된다.

• Delete 나 Insert시에도 유사한 과정을 거치며 Delete 의 경우 이전 이미지는 삭제된 행의 컬럼값을 포함하고 Insert의 경우 롤백에 저장된 행 위치 정보만을 포함한다.

DDL 처리

• DDL에 관하여 알아두어야 할 것은 문이 실행되기 직전과 COMMIT OR ROLLBACK직후에 묵시적인 COMMIT이 수행된다는 사실이다.

바인드 변수를 사용하면 무엇이 좋은가?

• 바인드 변수를 사용하지 않으면 성능이 낮다.
• 바인드 변수를 사용하지 않으면 확장성이 낮다.
• 바인드 변수를 사용하지 않으면 코드 작성이 어렵다.
• 바인드 변수가 사용되지 않은 코드의 안전도는 낮다.

모든 규칙에는 예외가 있다

• 바인드 변수는 쿼리를 파싱할 때 애플리케이션이 사용하는 래치(잠금이라고 읽어도 좋다)의 수를 줄이기 위한 훌륭한 방법이다.
• 소프트 파싱은 하드파싱보다 CPU시간을 약간 적게 사용하며 바인드 변수는 소프트 파싱을 성취하기 위한 수단이다.
• SQL문에 바인드 변수를 사용하는 대신 문자열 리터럴을 사용하면 시스템이 SQL삽입에 노출된다.
• SQL문에 문자열 리터럴을 사용하면 사용자가 인용부호와 같은 예기치 못한 문자를 입력할 경우 실행되지 않게 될 수 있다.
• 사용중인 환경에 상관없이 바인드 변수를 사용하는 방안이 바인드 변수를 사용하지 않는것 보다 좀더 빠를 뿐만 아니라 쉽게 작성된다.

가능한 한 적게 파싱하기

• 가능한 한 암묵적이거나 명시적인 커서 형태로 정적 SQL을 사용한다.
• 같은 동적 문을 여러차례 걸쳐 반복적으로 수행해야 하는 경우에는 EXECUTE IMMEDIATE 보다는 DBMS_SQL을 사용한다.
• DBMS_SQL 대신에 배열을 사용하여, 즉 다수의 입력으로 하나의 동적문을 실행하는 방안을 고려한다. 이렇게 하면 실행시간 실행에 극적인 차이가 생기며 확장성이 엄청나게 제고된다.
• 왜냐하면, 필요한 리소스의 양이 감소하고 필요한 래치의 수가 눈에 띄게 줄어들기 때문이다.

DML Lock

• DML Lock은 하나의 행을 한 번에 한 사람이 수정하는 것과 여러분이 일하고 있을 때 테이블을 Drop 할 수 없게 하기 위하여 사용된다. 오라클은 여러분들에게 이런 Lock들을 더욱 더 명확하게 보여줄 것이다.

TX(Transaction) Locks

• TX Lock은 트렌젝션이 처음 변경이 생길 때 획득되고 그것은 트렌젝션이 COMMIT 또는 ROLLBACK이 수행될 때까지 수행된다. 그것은 queuing 메커니즘으로 사용되기 때문에 다른 세션에서는 트렌젝션이 완벽히 수행될 때까지 기다릴 수 있다. 여러분은 각각 모든 행을 수정하거나 트렌젝션 안에 SELECT FOR UPDATE를 트렌젝션 동안에 관련된 TX Lock을 지정할 수 있다. 이것은 비싼 반면에 그렇지 않다. 이유를 이해하기 위해서는 여러분은 "live"가 어디에서 lock이 되는지 근본적인 이유와 그것들을 어떻게 관리하는지 알아야 한다. 오라클에서는, lock들은 데이터의 속성으로 저장된다. 오라클은 시스템 안에 lock이 수행된 모든 행의 긴 리스트를 유지하는데 전형적인 lock * 관리를 가지고 있지 않다. 많은 다른 데이터베이스들은 이 방식을 가지는데 그 이유는 lock들은 모니터링 하는데 사용하는 드문 리소스이기 때문이다. 더 많은 lock들이 사용되고 더 많은 시스템들에서 관리되기 때문에 그것은 만일 "too many" lock들이 사용된다면 시스템에서 주요 관심 대상이 된다.
• 전형적인 메모리 기반의 lock을 관리하는 데이터베이스에서 한 행의 locking 과정은 다음과 유사하다.

1. Lock을 원하는 행의 리스트를 찾는다.
2. Lock manager의 리스트(list)를 얻는다. (그것은 메모리 기반 구조이기 때문에 반드시 번호로 나열되어 있다.)
3. 리스트(list)를 잠근다.
4. 만일 어떤 사람이 또한 이 행을 잠근다면, 찾기 위하여 리스트를 조사한다.
5. 열의 잠금을 성립하기 위하여 리스트 안에 새로운 Entry를 생성한다.
6. 리스트의 잠금을 푼다.
   위와 같이 여러분은 행을 잠글 수 있고 수정할 수 있다. 후에, 변경사항을 commit 할 때 여러분은 다음의 절차를 계속해야 한다.
7. 리스트를 다시 얻는다.
8. lock들의 리스트를 잠근다.
9. 리스트를 조사하고 lock들을 해제한다.
10. 리스트의 잠금을 푼다.

• 여러분이 보는 것과 같이 더 많은 lock들이 획득되고, 더 많은 시간이 데이터를 수정 전/후에 소요된다. 오라클은 이와 같은 방식을 사용하지 않는다. 오라클의 과정은 다음과 같다.

1. Lock을 원하는 행의 리스트를 얻는다.
2. 행에게 간다.
3. 행을 잠근다. (만일 이미 잠금 상태이면 잠금 상태가 종료될 때까지 기다린다. 만일 그렇지 않으면 NOWAIT 기능을 사용한다.)

• Lock이 데이터의 속성으로서 저장되기 때문에 오라클은 전형적인 lock manager가 필요하지 않다. 이 트렌젝션은 단순히 트렌젝션에 수행되고 그것을 잠근다. 이 흥미로운 것은 여러분이 그것을 획득할 때 잠금이 나타날 것이다. 비록 그렇지 않더라도 말이다. 여러분이 데이터의 행들을 잠글 때 행은 데이터를 포함하고 있는 블락을 저장하고 있는 트렌젝션 ID의 복사본을 가르칠 것이다. 그리고 lock이 해제될 때 트랜젝션 ID는 왼쪽에 위치한다. 이 트렌젝션 ID는 여러분의 트렌젝션에서 유일하고 rollback segment number, slot, sequence number를 나타낸다. 여러분은 다른 세션들에서 자신의 데이터라고 말할 수 있는 행을 포함하는 블락을 남긴다. 다른 세션들이 올 때, 그것은 Lock ID와 그것이 트렌젝션을 나타내는 사실의 사용을 보여줄 때, 그것은 만일 트렌젝션을 잡고 있는 lock이 아직도 살아있다면 빠르게 보여줄 수 있다. 만일 lock이 active 상태가 아니면 세션은 lock이 해제되자마자 변경사항을 세션에게 통보할 것이다. 그러므로 여러분은 queuing mechanism을 가진다. Lock를 요청하는 세션은 트렌젝션이 완료될 때까지 대기를 유지할 것이고 그것은 데이터를 획득할 것이다.
• 여기에 이것이 어떻게 일어나는 것에 대하여 V$table를 사용하여 보여줄 예제가 있다.

• V$TRANSACTION은 모든 활성 중인 트렌젝션에 대한 entry를 포함한다.
• V$SESSION은 세션들이 로그인 되는 것을 보여준다.
• V$LOCK은 lock을 기다리는 세션들에 대한 잠금을 유지하고 있는 모든 enqueue lock에 대한 entry를 포함한다. 여러분은 세션에 의하여 이 테이블 인에 잠긴 모든 열에 대한 이 관점에서 볼 수 없을 것이다. 전에 언급한 것처럼, row level lock의 마스터 리스트는 존재하지 않는다. 만일 세션이 잠긴 EMP 테이블의 한 행을 가지고 있다면, 거기에는 세션이 가르치는 관점 안에서 하나의 행들만 있다. 만일 세션이 잠긴 EMP 테이블 안에 백만개의 행들을 가지고 있다면, 거기에는 아직도 단지 하나의 행만 있다. 이 관점은 enqueue lock이 개인적인 세션을 가지고 있는 것을 보여준다.

• 첫 번째, 트렌젝션을 시작한다. (만일, DEPT 테이블이 없다면, 간단히 하나를 만들어라.)

ops$tkyte@ORA10G> update dept set deptno = deptno+10;
4 rows updated.

• 지금, 이 관점에서 시스템의 상태를 본다. 이 예제는 단일 사용자 시스템으로 가정한다. ; 반면에 여러분은 V$TRANSACTION 테이블 안에 많은 행들을 볼 수 있다. 심지어 단일 사용자 시스템에서 V$TRANSACTION 안에 하나의 행보다 많이 본다고 해서 놀라지 마라. 백그라운드 오라클 프로세스들이 수행되어지기 때문일 것이다.

ops$tkyte@ORA10G> select username,
2         v$lock.sid,
3         trunc(id1/power(2,16)) rbs,
4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
5         id2 seq,
6         lmode,
7         request
8  from v$lock, v$session
9  where v$lock.type = 'TX'
10    and v$lock.sid = v$session.sid
11    and v$session.username = USER;
USERNAME   SID RBS SLOT    SEQ LMODE REQUEST
--------- ---- --- ---- ------ ----- -------
OPS$TKYTE  145   4   12  16582     6       0
ops$tkyte@ORA10G> select XIDUSN, XIDSLOT, XIDSQN
2    from v$transaction;
XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
4         12      16582

여기에 기록된 흥미로운 사실은 다음과 같다.

• LMODE V$LOCK 테이블 안에 6이고 요청은 '0'이다. 만일 여러분이 Oracle Server Reference 매뉴얼 안에 V$LOCK의 정의를 참조하고 있다면, 여러분은 LOMODE=6의 exclusive lock을 발견할 수 있다. 요청 안에 '0'의 값은 여러분이 요청을 만들지 않았다는 것을 의미한다. ; 여러분은 lock을 가진다.
• 거기에는 테이블 안에 단지 하나의 행만 있다. 이 V$LOCK 테이블은 lock 테이블 보다 더 많은 queuing table을 가진다. 많은 사람들이 거기에는 우리가 4개의 열이 잠기었기 때문에 V$LOCK 안에 4개의 열들이 있을 것으로 기대한다. 그러나, 오라클은 어디에서나 잠긴 모든 행의 마스터 리스르를 저장하지 않는 것을 기억해야 한다. 만일 행이 잠긴 것을 확인하기 위해서는 우리는 행을 나가야 한다.
• 나는 ID1과 ID2 컬럼을 가졌고 그것 위에서 어떤 조작이 수행되었다. 오라클은 3개의 16 비트 넘버들을 저장하는 것을 필요로 하였을 뿐만 아니라 그것을 하기 위하여 2개의 컬럼들을 가졌다. 그래서 첫 번째 컬럼 ID1은 이 숫자들의 2개응 잡았다. trunc(id1/power(2,16)) rbs 와 함께 2^16에 의하여 나누어지고 bitand(id1, to_number('ffff','xxxx'))+0 slot와 함께 높은 비트가 masking 됨에 따라서 나는 하나의 숫자를 숨겨 2개의 숫자를 돌릴 수 있었다.
• RBS, SLOT와 SEQ 값들은 V$TRANSACTION 정보와 일치한다. 이것이 트렌젝션 ID이다.
  지금 우리는 같은 사용자 이름을 사용하여 다른 세션을 시작할 수 있고 EMP 안에 어떤 행을 수정하고 DEPT 테이블 수정을 시도할 것이다.

ops$tkyte@ORA10G> update emp set ename = upper(ename);
14 rows updated.
ops$tkyte@ORA10G> update dept set deptno = deptno-10;

• 우리는 이 세션 안에 지금 블락되었다. 만일 우리가 V$ 쿼리들을 다시 시도한다면, 우리는 다음을 볼 수 있다.

ops$tkyte@ORA10G> select username,
2         v$lock.sid,
3         trunc(id1/power(2,16)) rbs,
4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
5         id2 seq,
6         lmode,
7         request
8  from v$lock, v$session
9  where v$lock.type = 'TX'
10    and v$lock.sid = v$session.sid
11    and v$session.username = USER;
USERNAME   SID RBS SLOT    SEQ LMODE REQUEST
--------- ---- --- ---- ------ ----- -------
OPS$TKYTE  144   4   12  16582     0       6
OPS$TKYTE  144   5   34   1759     6       0
OPS$TKYTE  145   4   12  16582     6       0

ops$tkyte@ORA10G> select XIDUSN, XIDSLOT, XIDSQN
2    from v$transaction;
XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
5         34       1759
4         12      16582

• 우리는 여기에서 (5,34,1759)의 트렌젝션 ID와 함께 새로운 트렌젝션 ID가 무엇인지 볼 수 있다. 우리의 새로운 세션 SID=144은 이 시점에 V$LOCK 안에 2개의 열을 가진다. 하나의 열은 그것이 (where LMODE=6)을 소유하고 있는 lock들을 나타낸다. 그것은 또한 '6'의 값과 함께 REQUEST를 보여주는 하나의 행을 가진다. 여기에서 흥미로운 사실은 요청된 열의 RBS/SLOT/SEQ가 lock의 holder의 트렌젝션 ID라는 것이다. SID=145와 함께 트렌젝션은 SID=144와 함께 트렌젝션을 막는다. 우리는 V$LOCK의 self-join을 함으로서 명백히 볼 수 있다.

ops$tkyte@ORA10G> select
2        (select username from v$session where sid=a.sid) blocker,
3         a.sid,
4        ' is blocking ',
5         (select username from v$session where sid=b.sid) blockee,
6             b.sid
7    from v$lock a, v$lock b
8   where a.block = 1
9     and b.request > 0
10     and a.id1 = b.id1
11     and a.id2 = b.id2;
BLOCKER    SID 'ISBLOCKING'  BLOCKEE    SID
--------- ---- ------------- --------- ----
OPS$TKYTE  145  is blocking  OPS$TKYTE  144

• 지금 만일 우리가 원래 SID=145의 트렌젝션을 commit 한고, 우리의 lock 쿼리를 재실행 하면 우리는 요청된 열을 발견할 수 있다.

ops$tkyte@ORA10G> select username,
2         v$lock.sid,
3         trunc(id1/power(2,16)) rbs,
4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
5         id2 seq,
6         lmode,
7         request
8  from v$lock, v$session
9  where v$lock.type = 'TX'
10    and v$lock.sid = v$session.sid
11    and v$session.username = USER;
USERNAME   SID RBS SLOT    SEQ LMODE REQUEST
--------- ---- --- ---- ------ ----- -------
OPS$TKYTE  144   5   34   1759     6       0
ops$tkyte@ORA10G> select XIDUSN, XIDSLOT, XIDSQN
2    from v$transaction;
XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
5         34       1759

• 요청된 행은 다른 세션이 그것의 lock을 포기하면 순간 사라진다. 요청된 행은 queuing mechanism이다. 데이터베이스는 트렌젝션이 일시적으로 완결될 때 블락된 세션들의 중요성을 알고 있다. 다양한 GUI 툴들과 함께 무한히 "pretty" 가 있으나, 여러분이 보기를 원하는 테이블의 지식을 획득하는 것은 매우 유용하다.
• 그러나, 우리가 오라클 작업 안에 잠긴 행을 어떻게 잘 이해하는 것을 말할 수 있기 전에, 우리는 반드시 마직만 토픽을 봐야 한다. ; 어떻게 locking과 트렌젝션 정보가 데이터와 함께 관리되는 것을 알아야 한다. 그것은 block overhead 파트이다. 쳅터 9 안에, 우리는 블락 형식의 세부사항을 볼 것이나, 블락을 위하여 트렌젝션 테이블을 저장하기 위하여 "overhead"공간이 데이터베이스 블락의 정상에서 어떤 것을 말하는지 이해를 해야 한다. 이 트렌젝션 테이블은 블락 안에 어떤 데이터가 블락 되었는지 각각 "real" 트렌젝션에 대한 entry이다. 이 구조의 사이즈는 객체에 대하여 CREATE 문장 위에 2개의 물리적인 속성 파라메터들에 위하여 조정된다.

• INITRANS : 초기에 이 구조에 미리 할당된 사이즈. 인덱스와 테이블에 대하여 기본값은 '2'이다.
• MAXTRANS : 이 구조가 최대로 늘어날 수 있는 사이즈이다. 기본 '255'와 최소 '2'이다. 오라클 10g에서 이 설정은 반대되어서, 그것은 더 이상 적용되지 않는다. MAXTRANS은 이 버전에서 불과하고 '255'이다.

• 각각 블락은 기본적으로 2개의 트렌젝션 slots으로 시작한다. 블락이 가질 수 있는 동시에 활성화된 트렌젝션의 숫자는 MAXTRANS의 값과 블락 위에 이용할 수 있는 공간에 의하여 결정된다. 여러분은 만일 거기에 구조를 늘릴 수 있는 충분한 공간이 없다면 블락 위에 255개의 동시 트렌젝션을 성취할 수 없을 것이다.
• 우리는 설정된 MAXTRANS와 함께 테이블을 생성하는 작업 방법을 인의적으로 선언할 수 있다. 우리는 오라클 9i을 사용하는 것이 필요하거나 이 전에 오라클 10g에서는 MAXTRANS가 무시되었다. 오라클 10g에서 비록 MAXTRANS가 설정되었어도 오라클은 블락 위에 공간을 생성할 수 있는 동안 트렌젝션 테이블을 늘릴 수 있을 것이다. 오라클 9i와 그 전에 MAXTRANS 값이 비록 블락에 도달했더라도 트렌젝션은 테이블은 늘어나지 않을 것이다 아래는 그 예제이다.

ops$tkyte@ORA9IR2> create table t ( x int ) maxtrans 2;
Table created.
ops$tkyte@ORA9IR2> insert into t select rownum from all_users;
24 rows created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> select distinct dbms_rowid.rowid_block_number(rowid) from t;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
18

• 그래서 우리는 24개의 열을 가지고 그것들이 모두 같은 데이터베이스 블락 위에 있는 검증하였다,. 지금 우리는 하나의 세션을 이슈 한다.

ops$tkyte@ORA9IR2> update t set x = 1 where x = 1;
1 row updated.

• 그리고 다른 하나, 우리는 이슈 한다.

ops$tkyte@ORA9IR2> update t set x = 2 where x = 2;
1 row updated.

• 마지막으로 세 번째 세션, 우리는 이슈 한다.

ops$tkyte@ORA9IR2> update t set x = 3 where x = 3;

• 지금, 세 개의 행들이 같은 데이터베이스 블락에 있기 때문에 그리고 우리가 MAXTRANS을 '2'로 설정했기 대문에 세 번째 세션을 잠길 것이다.
• Note 오라클 10g에서 이 blocking은 예제 안에서와 같이 일어나지 않을 것을 기억해라. - MAXTRANS은 그럼에도 불구하고 '255'로 설정한다. 이 버전에서는 blocking을 보기 위해서는 트렌젝션 테이블을 늘리기 위하여 블락 위에 불충분한 공간을 가질 것이다.

• 이 예제에서 하나의 MAXTRANS 트렌젝션 보다 많이 동시에 같은 블락에 접근을 시도할 때 무엇이 일어나는지 보여준다. 유사하게 blocking은 또한 만일 INTRANS가 적게 설정되고 거기에 트렌젝션을 동적으로 확장할 충분한 공간이 없으면 발생할 것이다. 이와 같은 대부분의 예제에서 INITRANS에 대한 기본값 '2'는 충분하다. 트렌젝션 테이블이 동적으로 성장하기 때문이다. 그러나, 어떤 환경에서 여러분은 동시성을 증가시키고 waits를 줄이는 설정을 증가할 필요성을 느낄 것이다. 예를 들어 여러분이 테이블을 할 필요성을 느끼거나 심지어는 종종 변경되는 인덱스가 변경되는 필요할 때의 예제이다. 여러분은 예상되는 동시 트렌젝션에 대한 블락 위에 충분한 공간 ㄱ시간에 앞서 위치 한 PCTFREE나 INITRANS를 증가할 필요성을 느낄 것이다. 이것은 만일 여러분이 블락들이 거의 가득 차는 것이 예상 된다면 특별히 사실이다. 거기에는 블락 위에 트렌젝션 테이블의 동적인 확장에 대한 공간이 충분하지 않다는 것을 의미한다.

TM (DML Enqueue) Locks

• TM Locks는 여러분이 내용을 수정하는 동안에 데이터의 구조를 변경되지 않을 때 확인하는데 사용된다. 예를 들어, 만일 여러분이 테이블을 수정한다면, 여러분은 테이블 위에 TM lock을 요구할 것이다. 이것은 테이블 위에 DROP 이나 ALTER 명령어를 실행하는 것으로부터 다른 사용자를 지켜줄 것이다. 만일 다른 사용자가 여러분이 TM lock을 가지고 있는 동안에 테이블 위에 DDL을 수행하는 것을 시도한다면 다음과 같은 에러 메시지를 받을 것이다.

drop table dept
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

• 이것은 거기에는 DROP TABLE 위에 NOWAIT나 WAIT를 명시하기 위한 방법이 없기 때문에 첫 번째 메시지는 혼란된다. 이것은 단지 여러분이 잠긴 동작을 수행을 시도할 때 보는 일반적인 메시지이다. 그러나 작동은 blocking을 금지하지 않는다. 여러분이 전에 본거와 같이 이것은 만일 여러분이 잠긴 열에 대하여 SELECT FOR UPDATE NOWAIT를 설정했을 때 보는 메시지와 같은 것이다.
• 다음은 어떻게 이러한 lock들이 V$LOCK 테이블 안에 나타나는 것을 보여준다.

ops$tkyte@ORA10G> create table t1 ( x int );
Table created.
ops$tkyte@ORA10G> create table t2 ( x int );
Table created.
ops$tkyte@ORA10G> insert into t1 values ( 1 );
1 row created.
ops$tkyte@ORA10G> insert into t2 values ( 1 );
1 row created.
ops$tkyte@ORA10G> select (select username
2                         from v$session
3                        where sid = v$lock.sid) username,
4         sid,
5         id1,
6         id2,
7         lmode,
8         request, block, v$lock.type
9    from v$lock
10   where sid = (select sid
11                  from v$mystat
12                 where rownum=1)
13  /
USERNAME   SID     ID1    ID2 LMODE REQUEST BLOCK TYPE
--------- ---- ------- ------ ----- ------- ----- ----
OPS$TKYTE  161  262151  16584     6       0     0 TX
OPS$TKYTE  161   62074      0     3       0     0 TM
OPS$TKYTE  161   62073      0     3       0     0 TM
ops$tkyte@ORA10G> select object_name, object_id
2    from user_objects
3   where object_name in ('T1','T2')
4  /
OBJECT_NAME   OBJECT_ID
------------ ----------
T1                62073
T2                62074

• 우리가 트렌젝션 당 하나의 TX lock을 가지는 반면, 우리는 우리가 수정할 객체로부터 많은 TM lock들을 얻을 수 있다. 여기에 흥미로운 사실은 TM lock에 대한 ID1 컬럼이 DML-locked object의 object ID 이다. 그래서, 그것을 lock이 설정되어 있는 object를 찾는 것은 쉽다.
• TM lock에 대한 흥미로운 것들 ; 시스템 안에 허용되는 TM lock들의 전체 숫자는 여러분에 의하여 구성된다. 그것은 사실 '0'에서 설정된다. 이것은 여러분의 데이터베이스 read-only 모드로 데이터베이스 되는 것을 의미하지 않는다. 그러나 다소 DDL은 금지되지 않는다. 이것은 매우 특별한 어플리케이션에서 유용하다. RAC 구축과 같이 발생하는 인프라 인스턴스의 조합을 양을 줄이기 위한 경우이다. 여러분은 또한 ALTER TABLE TABLENAME DISABLE TABLE LOCK 명령어를 사용한 object-by-object 기반의 TM lock들을 얻기 위한 능력을 제거할 수 있다. 이것은 여러분이 테이블을 drop 하기 전에 테이블 lock을 이용하지 못하게 하는 것처럼 테이블을 사고로 drop 할 때 "harder"로 만드는 가장 쉬운 방법이다. 이것은 또한 우리가 전에 토론한 unidexed 외래 키의 결과로부터 전체 테이블 lock.을 보호하는데 사용할 수 있다.