ORACLE - DAY 14

BUMSOO·2024년 8월 19일
아키텍처오라클 DBA

복습

[문제] LISTENER PORT를 수정해주세요. 1522 -> 1523

<정적 서비스 등록>

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = ORA19C)
(ORACLE_HOM = /u01/app/oracle/product/19.3.0/dbhome_1)
(GLOBAL_DBNAME = ORA19C)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c)(PORT = 1523))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

<동적 서비스 등록>

  • 디스크립션 작성 방법
ALTER SYSTEM SET local_listener = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19c)(PORT=1523)))';

ALTER SYSTEM REGISTER;
  • tns 이름 등록 방법
ALTER SYSTEM SET local_listener = 'LISTENER_ORA19C';

ALTER SYSTEM REGISTER;

PGA(Program Global Area)

  • 서버 프로세스에 대한 데이터 및 제어정보를 포함하는 전용(private) 메모리 영역

UGA(User Global Area)

  • 유저 세션 데이터 : 보안, 리소스 사용 정보를 포함
  • cursor state : SQL문 실행 메모리 영역
  • SQL 작업영역 : 정렬 영역, 해시 영역, 비트맵 인덱스 생성, 병합
  • stack space : 프로세스 로컬 변수를 포함

Dedicated Server

  • user process : server process = 1:1

Shared Server

  • user process : server process = N:1
  • PGA메모리를 공유 하지만 stack space를 제외한 나머지 UGA는 공유해서는 안된다.
  • UGA는 SGA 영역에 Large pool이 설정되어 있으면 UGA가 이곳에 생성되고,Large pool이 설정되어 있지 않으면 Shared pool 공간에 UGA가 생성된다.
  • Dispatcher(배차원) 프로세스가 사용된다.
  • Dispatcher 프로세스가 각 유저 프로세스로 부터 sql문을 받아가지고 SGA의 LARGE POOL안에 요청 메모리 공간에 적재 하고 server process가 요청메모리에 있는 sql문을 가지고 처리 한 후 응답 메모리 공간에 결과물을 적재한다. 그 후 Dispatcher 프로세스가 결과물을 해당 user process에 돌려주는 과정이다.
  • 결과적으로 기존의 server process가 하던 fetch 작업을 dispatcher process가 하게 된다.
  • Shared Server를 사용하여 수행하지 않아야하는 작업
    • 데이터베이스 관리
    • backup & recovery
    • 일괄처리 및 대량 로드 작업
    • 데이터웨어하우스 작업

<출처 최홍일>

- 4번 작업은 server process가 parse, execute, sort, hash 작업등을 하기 위해 SGA영역을 이용해야 하기 때문에 일어난다. sort, hash 작업은 Large pool 안의 UGA를 이용한다.
- Dispatcher의 수많큼 Response Queue가 생겨난다.

Shared Server 환경 이점

  • server process 수를 줄일 수 있다.
  • 메모리 사용량과 시스템 오버헤드를 줄일 수 있다.
  • 허용되는 user 수를 증가시킬 수 있다.

Shared Server 실습

  • dispatcher 관련 파라미터는 2개가 있다.
    • dispatchers
    • max_dispatchers

show parameter dispatchers

  • 처음에 시작되는 dispatcher 수를 지정
    ALTER SYSTEM SET dispatchers = '(PROTOCOL=TCP)(DISPATCHERS=5)';
  • 최대로 생성되는 dispatcher 수 지정
    ALTER SYSTEM SET max_dispatchers = 10;

  • shared 환경에서 생성되는 server process 파라미터 확인
    show parameter shared_servers

  • instance 시작될때 생성할 server process 수 지정
    ALTER SYSTEM SET shared_servers = 2;

  • 최대로 생성되는 server process 수 지정
    ALTER SYSTEM SET max_shared_servers = 10;

  • listener를 reload 실행
    lsnrctl reload

  • listener의 service 확인
    lsnrctl service

- dispatcher의 수를 5개로 설정하여 5개가 생성된걸 확인할 수 있다.

  • 클라이언트가 shared server로 접속하기 위해서는 클라이언트쪽에 있는 tnsnames.ora 파일 수정

- shared server는 SERVER = DEDICATED 구문만 삭제해주면 된다.

  • DBA계정으로 접속 SESSION 확인
    • 이전에 INSA 계정은 DEDICATED 환경으로 접속, INSA_BUHA계정은 SHARED SERVER로 접속
    • DBA계정은 무조건 DEDICATED 환경으로 접속해야 한다.
      - shared server로 접속하게 되면 SERVER 컬럼이 NONE으로 표시되는걸 확인할 수 있다.

SEECT * FROM v$dispatcher; : 생성된 dispatcher 수 확인
SELECT * FORM v$shared_server; : 생성된 server process 수 확인

shared server 환경 해지

ALTER SYSTEM SET shared_servers = 0; : shared_server의 수만 0으로 해줘도 해지가 가능하다
ALTER SYSTEM SET dispatchers = ''; : 다만 shared server가 해지 되었는데도 dispatcher가 있는건 보기 싫으니 빈칸 처리를 해줬다.

lsnrctl reload : 리스너 재시작
lsnrctl service

- 클라이언트쪽의 tns 파일을 따로 수정하지 않고 shared server로 접속해도 자동으로 dedicated 서버로 접속된다.

LOCK

  • 다중 세션에서 동일한 데이터를 동시에 변경하는것을 방지
  • 주어진 명령문에 대해 가능한 가장 낮은 레벨에서 자동으로 획득

LOCK 메커리즘

  • insert, update, delete, merge에 대한 행레벨 lock
  • select문은 lock이 필요없다. lock을 수행하려면 select .. for update절을 수행하면 행 레벨 lock이 수행된다.
  • 자동 queue 알고리즘으로 수행된다.
  • 트랜잭션이 종료 될때 까지 lock 보유

DML

  • 행 레벨 lock : TX(lock 모드)
    - TX lock이 걸린 sql문은 wait단계로 빠져 shared pool에 있는 queue 메모리안에 저장되어있다.
    - TX 락은 트랜잭션 락이라고도 하며, 주로 데이터 변경 작업(INSERT, UPDATE, DELETE)에서 발생합니다.
    - 이는 트랜잭션이 특정 자원을 사용하는 동안 다른 트랜잭션이 해당 자원에 접근하지 못하게 함으로써 데이터 일관성을 유지하는 데 중요한 역할을 합니다.
  • 테이블 레벨 lock : TM(lock 모드)
    - TM lock은 아예 불허해 버린다.(NOWAIT)
    - TM 락은 테이블 락으로, 주로 테이블 자체에 대한 구조적 변경(DML, DDL)을 보호하기 위해 사용됩니다.
    - 이는 테이블에 대한 동시성을 관리하고, 데이터 무결성을 보장하는 데 중요한 역할을 합니다.

lock이 발생한 wait event 확인

  • session 확인
SELECT sid, serial#, username, blocking_session,event,sql_id, prev_sql_id
FROM v$session 
WHERE event LIKE '%TX%';

  • SQL_ID를 이용한 lock 발생한 sql문 확인
    - 수행한 세션에서의 lock 발생 sql확인은 v$session의 sql_id로 확인해야한다.
SELECT sql_text FROM v$sql WHERE sql_id = '8suw0gu1fumpv';

  • lock을 걸리게한 세션을 확인 하는 방법
    - blocking_session 번호가 lock을 걸리게한 세션의 SID 번호이다
SELECT * FROM v$session WHERE sid = 297;

  • lock을 걸리게 한 sql문을 알고 싶으면 lock을 걸리게한 세션의 prev_sql_id를 확인해야 한다.
SELECT sql_text FROM v$sql WHERE sql_id = '36ya1fd1x3pgm';

LOCK 딕셔너리 뷰

SELECT sid, type, id1, id2, lmode, request, block
FROM v$lock 
WHERE sid IN (289,41) 
AND TYPE IN ('TX','TM');

  • TYPE : TM
    ID1 : OBJECT ID

  • TYPE : TX
    ID1 : UNDO SEGMENT 번호 + TRANSACTION SLOT 번호
    ID2 : TRANSACTION SLOT SEQUENCE 번호

  • SID : LOCK을 보유중이거나 요청 중인 SESSION ID

LMODE > 0 이면 LOCK을 보유중인 세션이고, REQUEST > 0 이면 LOCK 요청 중인 세션을 의미한다.

  • LMODE : LOCK을 보유하고 있는 경우 모드
    • 2번 - ROW SHARE(RS) : LOCK된 테이블에 대한 동시 액세스를 허용하지만 세션이 베타적 액세스를 위해 전체 테이블을 LOCK하는 것은 금지한다.
    • 3번 - ROW EXCLUSIVE(RX) : ROW SHARE와 동일하지만 SHARE 모드에서도 LOCK을 금지한다. ROW EXCLUSIVE LOCK은 데이터에 대해 갱신,삭제,입력시 자동으로 획득된다.
    • 4번 - SHARE(S) : 동시 QUERY는 허용하지만 LOCK된 테이블에 대한 변경은 금지한다. 테이블에서 인덱스를 생성하려면 SHARE LOCK이 필요하며 자동으로 요청된다.
    • 5번 - SHARE ROW EXCLUSIVE(SRX) : 전체 테이블을 QUERY하는데 사용되며 다른 유저가 테이블의 행을 QUERY하는 것은 허용하지만 해당 테이블을 SHARE 모드에서 LOCK하거나 행을 갱신하는 것은 금지한다.
    • 6번 - EXCLUSIVE(X) : LOCK된 테이블에서의 QUERY는 허용하지만 해당 테이블에서 다른 작업은 금지한다.
  • REQUEST : LOCK을 요청 중인 경우 모드
  • BLOCK : 현재 LOCK이 다른 LOCK을 블로킹하고 있는지 여부
    • 1 : 다른 LOCK을 블로킹중이다
    • 0 : 다른 LOCK을 블로킹하지 않는다.

ID1 전환

  • TX 모드 lock은 ID1이 UNDO SEGMENT 번호와 TRANSACTION SLOT 번호가 조합되어 있기 때문에 변환해줘야 알 수 있다.
SELECT sid, type, id1, id2, lmode, request, block,
    to_char(trunc(id1/power(2,16))) as UNDO_SEGMENT_NUMBER,
    bitand(id1,to_number('ffff','xxxx'))+0 as TRANSACTION_SLOT_NUMBER,
    id2 as TRANSACTION_SLOT_SEQUENCE_NUMBER
FROM v$lock 
WHERE sid IN (289,41) 
AND TYPE IN ('TX','TM');

  • undo segment에서 segment number 확인
SELECT * FROM dba_rollback_segs WHERE segment_id = 4;

ROW ID 만들기

  • TX 모드 락이 걸린 세션 확인
SELECT sid, serial#, username, blocking_session,row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
FROM v$session 
WHERE event LIKE '%TX%';


row_wait_obj# : object ID
row_wait_file# : data file ID
row_wait_block# : block ID
row_wait_row# : ROW SLOT ID

SELECT 
    dbms_rowid.rowid_create(0,74084,3,66012,2) as "restricted rowid", 
    dbms_rowid.rowid_create(1,74084,3,66012,2) as "extended rowid" 
FROM dual;

- 0번 옵션, 7i 버전때 사용하던 rowid, 6byte, #block.#rowslot.#file
- 1번옵션, 요즘 사용하는 rowid, 10byte, #data object id(6)#file(3)#block(6)#rowslot(3)

KILL SESSION

  • lock을 유발하는 blocking_session의 sid와 serial#을 확인한다
SELECT sid, serial#, username
FROM v$session
WHERE  sid IN (SELECT blocking_session FROM v$session);

  • KILL 하기
    ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
    - IMMEDIATE를 해주지 않으면 오라클이 천천히 KILL 하기 때문에 적용해주도록 하자
profile
BUMSOO
이전 포스트

ORACLE - DAY 13

다음 포스트

ORACLE - DAY 15

0개의 댓글