[아이티윌 오라클 DBA 과정 91기] 260121 TIL
2026. 1. 21. 18:23ㆍCourses/아이티윌 오라클 DBA 과정
Latch(Cont.)
Library Cache Pin
- LCO에 접근하거나 변경하는 경우 LCO에 대해 획득하는 LOCK
- LCO 실행 정보를 보호 즉 실행 과정 동안 LCO의 실행 정보가 변경되는 것을 방지
- SQL 문 hard parsing : library cache pin을 exclusive mode로 획득 후 실행 계획을 생성
- SQL 문 execute 단계 : library cache pin을 shared mode 획득
- SQL 문 fetch 단계 : library cache pin을 해제
- procedure/function/package 실행 : library cache pin을 shared mode로 획득
- procedure/function/package 변경, 컴파일 : library cache pin을 exclusive mode로 획득
Shared Pool Latch
- hard parsing이 발생하는 경우 free chunk를 찾기 위해 free list를 탐색하고 적절한 free chunk를 할당하고 필요한 경우 free chunk를 분할(split)하는 일련의 작업이 모두 shared pool latch를 획득한 후에만 가능
- shared pool latch를 획득하는 과정에서 경합이 발생하면
latch : shared pool대기 이벤트가 발생 - shared pool latch는 기본적으로 instance당 하나만 존재
- 9i 버전부터는 shared pool 을 여러 개의 서브 풀로 최대 7개까지 나누어서 관리하며 오라클은 CPU 개수가 4개 이상이고 shared pool 크기가 250M 이상인 경우에
_kghdsidx_count히든 파라미터 값만큼 서브 풀을 생성해서 관리함 - 각 서브 풀마다 shared pool latch가 만들어지기 때문에 그만큼 경합을 줄일 수 있음
# _kghdsidx_count 히든 파라미터 조회
SYS@ora19c> select a.ksppinm parameter, b.ksppstvl value
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_kghdsidx_count'; 2 3 4
PARAMETER VALUE
-------------------------------------------------------------------------------- ------------------------------
_kghdsidx_count 1# shared pool latch 조회
SYS@ora19c> select name, gets from v$latch_children where name = 'shared pool';
NAME GETS
-------------------------------------------------- ----------
shared pool 56
shared pool 56
shared pool 56
shared pool 56
shared pool 56
shared pool 56
shared pool 4823526 <<- 사용하고 있는 shared pool latch
7 rows selected.
# cpu 수 조회
SYS@ora19c> show parameter cpu_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 2실습
HR@ora19c> declare
v_cnt number;
begin
for i in 1..5 loop
execute immediate 'select count(*) from dual where dummy = to_char('||i||')' into v_cnt;
end loop;
end;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text like '%select count(*) from dual where dummy%'
and sql_text not like '%v$sql%';
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS
------------- ------------------------------------------------------- ----------- ---------- ---------- -------------
173nctnvn45cu select count(*) from dual where dummy = to_char(5) 1 1 1 0
6fb6af6nhtby1 select count(*) from dual where dummy = to_char(4) 1 1 1 0
3f2sqh4zrpydb select count(*) from dual where dummy = to_char(1) 1 1 1 0
b8v7fvmqny4u7 select count(*) from dual where dummy = to_char(3) 1 1 1 0
250paq2dcuvvj select count(*) from dual where dummy = to_char(2) 1 1 1 0
1g97h5aq5byqj declare v_cnt number; begin for i in 1..5 loop exec 1 1 1 0
ute immediate 'select count(*) from dual where dummy =
to_char('||i||')' into v_cnt; end loop; end;
6 rows selected.
- i 값에 의해 하드 파싱이 각각 일어남
Dynamic SQL
hr 세션 2개 접속
SYS@ora19c> select sid, event, wait_class, wait_time, seconds_in_wait, state from v$session_wait where sid in (select sid from v$session where username = 'HR');
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- -------------------- ---------- --------------- -------------------
40 SQL*Net message from client Idle 0 40 WAITING
163 SQL*Net message from client Idle 0 37 WAITING세션 1, 2에서 아래 익명 블록 동시 수행
HR@ora19c> declare
v_cnt number;
begin
for i in 1..100000 loop
execute immediate 'select count(*) from dual where dummy = to_char('||i||')' into v_cnt;
end loop;
end;
/ 2 3 4 5 6 7 8세션 대기 이벤트 조회
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- -------------------- ---------- --------------- -------------------
40 latch: shared pool Concurrency -1 0 WAITED SHORT TIME
163 latch: shared pool Concurrency 0 0 WAITING
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- -------------------- ---------- --------------- -------------------
40 latch: shared pool Concurrency 0 0 WAITING
163 latch: shared pool Concurrency 0 0 WAITING
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- -------------------- ---------- --------------- -------------------
40 latch: shared pool Concurrency -1 0 WAITED SHORT TIME
163 library cache: bucket mutex X Concurrency 0 0 WAITING
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- -------------------- ---------- --------------- -------------------
40 latch: shared pool Concurrency -1 0 WAITED SHORT TIME
163 latch: shared pool Concurrency 1 0 WAITED KNOWN TIME
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- -------------------- ---------- --------------- -------------------
40 library cache: mutex X Concurrency -1 0 WAITED SHORT TIME
163 library cache: bucket mutex X Concurrency 0 0 WAITING
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- -------------------- ---------- --------------- -------------------
40 latch: shared pool Concurrency -1 0 WAITED SHORT TIME
163 latch: shared pool Concurrency 0 0 WAITING
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- -------------------- ---------- --------------- -------------------
40 row cache mutex Concurrency 0 0 WAITING
163 latch: shared pool Concurrency 0 0 WAITING
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- -------------------- ---------- --------------- -------------------
40 latch: shared pool Concurrency -1 0 WAITED SHORT TIME
163 latch: shared pool Concurrency 0 0 WAITING
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- -------------------- ---------- --------------- -------------------
40 latch: shared pool Concurrency 0 0 WAITING
163 latch: shared pool Concurrency 1 0 WAITED KNOWN TIME
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- -------------------- ---------- --------------- -------------------
40 latch: shared pool Concurrency -1 0 WAITED SHORT TIME
163 latch: shared pool Concurrency -1 0 WAITED SHORT TIME
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- -------------------- ---------- --------------- -------------------
40 latch: shared pool Concurrency 0 0 WAITING
163 latch: shared pool Concurrency 0 0 WAITING
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- -------------------- ---------- --------------- -------------------
40 latch: shared pool Concurrency -1 0 WAITED SHORT TIME
163 latch: shared pool Concurrency -1 0 WAITED SHORT TIME
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- -------------------- ---------- --------------- -------------------
40 latch: shared pool Concurrency 0 0 WAITING
163 latch: shared pool Concurrency 0 0 WAITING
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- -------------------- ---------- --------------- -------------------
40 SQL*Net message from client Idle 0 3 WAITING
163 SQL*Net message from client Idle 0 1 WAITING
SYS@ora19c> select sid, event, total_waits, time_waited from v$session_event where sid in (select sid from v$session where username = 'HR');
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------- ----------- -----------
40 Disk file operations I/O 1 0
40 db file sequential read 17 1
40 db file scattered read 4 0
40 row cache mutex 67 193
40 row cache lock 98 4
40 row cache read 2 0
40 cursor: pin S wait on X 61 175
40 latch: shared pool 16847 484
40 library cache lock 4 0
40 library cache load lock 5 0
40 library cache: mutex X 144 792
40 library cache: bucket mutex X 18 48
40 library cache: dependency mutex X 3 1
40 SQL*Net message to client 9 0
40 SQL*Net message from client 8 6792
40 events in waitclass Other 60 5
163 log file sync 1 1
163 row cache mutex 58 250
163 row cache lock 107 4
163 row cache read 2 0
163 cursor: pin S 1 0
163 cursor: pin S wait on X 64 95
163 latch: shared pool 17298 351
163 library cache lock 6 1
163 library cache load lock 2 0
163 library cache: mutex X 143 872
163 library cache: bucket mutex X 20 75
163 library cache: dependency mutex X 1 0
163 SQL*Net message to client 9 0
163 SQL*Net message from client 8 6549
163 events in waitclass Other 46 47
31 rows selected.
latch: shard pool: Shared Pool 할당 경합cursor: pin S wait on X: 실행하려는 Cursor가 재작성 중
같은 실행 계획을 사용하는데 각각 생성된 커서 조회
SYS@ora19c> select plan_hash_value, count(*)
from v$sql
group by plan_hash_value
having count(*) > 100;
PLAN_HASH_VALUE COUNT(*)
--------------- ----------
2522405774 611
SYS@ora19c> select sql_text, parsing_schema_name from v$sql where plan_hash_value = 2522405774 and rownum = 1;
SQL_TEXT PARSING_SCHEMA_NAME
------------------------------------------------------- --------------------
select count(*) from dual where dummy = to_char(97891) HR- parsing_schema_name 이 보통 업무 팀
- 과도한 하드 파싱을 일으키고 있는 업무 팀을 찾아서 쿼리 변경 요청
Static SQL
# shared pool flush
SYS@ora19c> alter system flush shared_pool;
System altered.
# session cache cursor 수 0개로 설정
SYS@ora19c> alter session set session_cached_cursors = 0;
Session altered.세션 1과 세션 2에서 아래 익명 블록 동시 수행
HR@ora19c> declare
v_cnt number;
begin
for i in 1..100000 loop
select count(*) into v_cnt from dual where dummy = to_char(i);
end loop;
end;
/ 누적 대기 이벤트
SYS@ora19c> select sid, event, total_waits, time_waited from v$session_event where sid in (select sid from v$session where username = 'HR');
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------- ----------- -----------
40 log file sync 1 1
40 cursor: pin S 1 0
40 SQL*Net message to client 10 0
40 SQL*Net message from client 9 9480
40 SQL*Net break/reset to client 2 0
40 events in waitclass Other 13 0
163 Disk file operations I/O 3 0
163 log file sync 1 0
163 cursor: pin S 1 40
163 SQL*Net message to client 10 0
163 SQL*Net message from client 9 12032
163 SQL*Net break/reset to client 2 0
163 events in waitclass Other 41 0
13 rows selected.
- exclusive 경합 발생 X
SQL 수행 정보 조회
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, hash_value, plan_hash_value
from v$sql
where sql_text like '%select count(*) into v_cnt from dual%'
and sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS HASH_VALUE PLAN_HASH_VALUE
------------- ------------------------------------------------------- ----------- ---------- ---------- ---------- ---------------
9m4qjxpjrvz0j declare v_cnt number; begin for i in 1..100000 loop 2 1 2 1669200913 0
select count(*) into v_cnt from dual where dummy = to_
char(i); end loop; end;
- 하드 파싱은 한 번만 일어남
세션 커서
- 오라클은 한 세션 내에서 세번 이상 수행된 SQL 커서들의 Library Cahce 내에서의 위치와 SQL 텍스트를 PGA 영역에 저장
- 저장하는 SQL 커서의 개수는 session_cached_cursors 파라미터에 의해 결정됨
- 이 기능을 사용하면 바로 LCO 위치를 찾아가게 되므로 mutex 점유 시간을 줄일 수 있음
SYS@ora19c> show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 50실습
SYS@ora19c> alter system flush shared_pool;
System altered.세션 1, 2 동시 수행
HR@ora19c> declare
v_cnt number;
begin
for i in 1..100000 loop
select count(*) into v_cnt from dual where dummy = to_char(i);
end loop;
end;
/ 누적 대기 이벤트 조회
SYS@ora19c> select sid, event, total_waits, time_waited from v$session_event where sid in (select sid from v$session where username = 'HR');
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------- ----------- -----------
40 log file sync 1 1
40 cursor: pin S 6 4
40 SQL*Net message to client 9 0
40 SQL*Net message from client 8 5627
40 events in waitclass Other 31 0
44 log file sync 1 1
44 cursor: pin S 1 0
44 SQL*Net message to client 9 0
44 SQL*Net message from client 8 4937
44 events in waitclass Other 11 0
10 rows selected.SQL 수행 정보 조회
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, hash_value, plan_hash_value
from v$sql
where sql_text like '%select count(*) into v_cnt from dual%'
and sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS HASH_VALUE PLAN_HASH_VALUE
------------- ------------------------------------------------------- ----------- ---------- ---------- ---------- ---------------
9fwmdg8qx0vyq declare v_cnt number; begin for i in 1. 2 1 2 768634838 0
.100000 loop select count(*) into v_cnt
from dual where dummy = to_char(i); end loop;
endversion count
- 자식 LCO 수
- version count가 증가되는 경우가 64가지
v$sql_shared_cursor뷰로 모니터링- https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQL_SHARED_CURSOR.html
sql은 같지만 소유자가 다른 경우
hr과 insa 스키마에 동일한 테이블 생성
drop table hr.emp purge;
drop table insa.emp purge;
create table hr.emp tablespace users as select * from hr.employees;
create table insa.emp tablespace users as select * from hr.employees;동일한 쿼리 수행
HR@ora19c> select * from emp where employee_id = 100;
INSA@ora19c> select * from emp where employee_id = 100;SQL 수행 정보 확인 → version_count = 2
SYS@ora19c> select sql_id, sql_text, version_count
from v$sqlarea
where sql_text like '%select * from emp where employee_id = 100%'
and sql_text not like '%v$sqlarea%'; 2 3 4
SQL_ID SQL_TEXT VERSION_COUNT
------------- ------------------------------------------------------- -------------
0t028xxgcphg0 select * from emp where employee_id = 100 2- child cursor가 2개 생성됨
child cursor가 생성된 이유 조회
select * from v$sql_shared_cursor where sql_id = '0t028xxgcphg0';
select sql_id, child_number, auth_check_mismatch, translation_mismatch from v$sql_shared_cursor where sql_id = '0t028xxgcphg0';
실행 계획
SYS@ora19c> select * from table(dbms_xplan.display_cursor('0t028xxgcphg0', 0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0t028xxgcphg0, child number 0
-------------------------------------
select * from emp where employee_id = 100
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 69 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
18 rows selected.
SYS@ora19c> select * from table(dbms_xplan.display_cursor('0t028xxgcphg0', 1));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0t028xxgcphg0, child number 1
-------------------------------------
select * from emp where employee_id = 100
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 69 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
18 rows selected.
- 쿼리 문장은 같지만 소유자가 다르므로 실행 계획이 다르게 생성되어야 함 → parent cursor + 소유자마다 child cursor가 각각 생김 ⇒ cursor 3개 생성
varchar2 size(32, 128, 2000, 4000) 4가지 크기로 결정
HR@ora19c> var name varchar2(10)
HR@ora19c> exec :name := 'King'
PL/SQL procedure successfully completed.
HR@ora19c> print name
NAME
--------------------------------------------------
King
HR@ora19c> select last_name, salary from hr.employees where last_name = :name;
LAST_NAME SALARY
------------------------- ----------
King 10000
King 24000
HR@ora19c> var name varchar2(1000)
HR@ora19c> exec :name := 'Grant'
PL/SQL procedure successfully completed.
HR@ora19c> select last_name, salary from hr.employees where last_name = :name;
LAST_NAME SALARY
------------------------- ----------
Grant 2600
Grant 7000
SYS@ora19c> select sql_id, sql_text, version_count
from v$sqlarea
where sql_text like '%hr.employees%'
and sql_text not like '%v$sqlarea%';
SQL_ID SQL_TEXT VERSION_COUNT
------------- ------------------------------------------------------- -------------
gqgwd5rvu5ftu select last_name, salary from hr.employees where last_n 2
ame = :name- 바인드 변수를 두번 선언하면서 크기를 다르게 설정
- child cursor가 2개 생성됨
child cursor가 생성된 이유 조회
v$sql_shared_cursor를 보고 child cursor가 생성된 이유를 찾아야 함
select * from v$sql_shared_cursor where sql_id = 'gqgwd5rvu5ftu';
바인드 변수 정보 조회
select *
from v$sql_bind_capture
where sql_id = 'gqgwd5rvu5ftu';
실행 계획
SYS@ora19c> select * from table(dbms_xplan.display_cursor('gqgwd5rvu5ftu', 0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gqgwd5rvu5ftu, child number 0
-------------------------------------
select last_name, salary from hr.employees where last_name = :name
Plan hash value: 1600171752
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 12 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_NAME_IX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LAST_NAME"=:NAME)
19 rows selected.
SYS@ora19c> select * from table(dbms_xplan.display_cursor('gqgwd5rvu5ftu', 1));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gqgwd5rvu5ftu, child number 1
-------------------------------------
select last_name, salary from hr.employees where last_name = :name
Plan hash value: 1600171752
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 12 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_NAME_IX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LAST_NAME"=:NAME)
19 rows selected.
- child cursor의 실행 계획이 모두 동일
특정 cursor를 purge(11g)
SYS@ora19c> select sql_id, sql_text, version_count, address, hash_value
from v$sqlarea
where sql_text like '%hr.employees%'
and sql_text not like '%v$sqlarea%'; 2 3 4
SQL_ID SQL_TEXT VERSION_COUNT ADDRESS HASH_VALUE
------------- ------------------------------------------------------- ------------- ---------------- ----------
gqgwd5rvu5ftu select last_name, salary from hr.employees where last_n 2 0000000069B92568 4154637114
ame = :name
SYS@ora19c> exec dbms_shared_pool.purge('0000000069B92568,4154637114', 'c');
PL/SQL procedure successfully completed.
SYS@ora19c> select sql_id, sql_text, version_count, address, hash_value
from v$sqlarea
where sql_text like '%hr.employees%'
and sql_text not like '%v$sqlarea%'; 3 4
no rows selected과도한 하드 파싱 문제점
- 실행 계획 생성 시에 과도한 CPU, 메모리 사용
- Library Cache Chain 확장으로 인한 검색 시간 증가, mutex 점유 시간 증가 → soft parsing 성능 저하
- shared pool fragmentation(단편화) : free chunk가 작은 크기로 쪼개져서 chunk 수가 증가하는 현상 → 체인의 길이가 길어져 검색 속도 저하 유발, 내가 필요한 free chunk를 찾지 못해서 ORA-04031 오류 발생 확률이 높아짐
row cache lock
- data dictionary cache(row cache) 는 오라클 딕셔너리 정보에 대한 캐시 영역
- 유저, 객체, 테이블, 세그먼트, 컬럼, 인덱스, 시퀀스, 뷰, 프로시저, 함수, 패키지, 트리거, …
- row cache lock은 딕셔너리 테이블(객체)을 보호하는 시스템 lock
- DDL, DCL 작업을 수행하면 딕셔너리 정보에 대한 입력, 수정, 삭제 발생 → row cache lock 발생
- 운영 환경에서 DDL, DCL은 잘 안 하기 때문에 보통은 문제 안됨
- 시퀀스 사용 시 캐시 옵션을 사용하지 않으면 row cache lock에 의한 경합이 심할 수 있음
- 시퀀스를 생성할 때는 캐시 옵션을 통해 미리 다음 시퀀스 번호를 여러 개 생성해두고 사용
SYS@ora19c> select pool, name, bytes from v$sgastat where name = 'row cache';
POOL NAME BYTES
-------------- -------------------------------------------------- ----------
shared pool row cache 25864SYS@ora19c> select cache#, type, parameter from v$rowcache;
CACHE# TYPE PARAMETER
---------- ----------- --------------------------------
3 PARENT dc_rollback_segments
1 PARENT dc_free_extents
4 PARENT dc_used_extents
2 PARENT dc_segments
0 PARENT dc_tablespaces
5 PARENT dc_tablespace_quotas
6 PARENT dc_files
10 PARENT dc_users
8 PARENT dc_objects
17 PARENT dc_global_oids
12 PARENT dc_constraints
13 PARENT dc_sequences
16 PARENT dc_histogram_defs
54 PARENT dc_sql_prs_errors
32 PARENT kqlsubheap_object
19 PARENT dc_table_scns
18 PARENT dc_outlines
15 PARENT dc_props
60 PARENT dc_cdbprops
14 PARENT dc_profiles
47 PARENT realm cache
48 PARENT Command rule cache
49 PARENT Realm Object cache
46 PARENT Rule Set Cache
55 PARENT DV Auth Cache
34 PARENT extensible security principal in
33 PARENT Unused
36 PARENT Unused
37 PARENT Unused
38 PARENT XS security class privilege
39 PARENT extensible security midtier cach
43 PARENT AV row cache 1
44 PARENT AV row cache 2
45 PARENT AV row cache 3
35 PARENT triton security name to ID
61 PARENT dc_statistics_status
62 PARENT dc_realtime_colst
63 PARENT dc_realtime_tabst
20 PARENT rule_info
21 PARENT rule_or_piece
64 PARENT key_shard
23 PARENT dc_qmc_ldap_cache_entries
52 PARENT qmc_app_cache_entries
53 PARENT qmc_app_cache_entries
27 PARENT qmtmrcin_cache_entries
28 PARENT qmtmrctn_cache_entries
29 PARENT qmtmrcip_cache_entries
30 PARENT qmtmrctp_cache_entries
31 PARENT qmtmrciq_cache_entries
26 PARENT qmtmrctq_cache_entries
9 PARENT qmrc_cache_entries
50 PARENT qmemod_cache_entries
24 PARENT outstanding_alerts
22 PARENT dc_awr_control
25 PARENT SMO rowcache
40 PARENT sch_lj_objs
41 PARENT sch_lj_oids
56 PARENT dc_cdbfiles
57 PARENT dc_cdbservices
58 PARENT dc_pdbdba
59 PARENT dc_pdbstates
7 SUBORDINATE dc_users
8 SUBORDINATE dc_object_grants
16 SUBORDINATE dc_histogram_data
16 SUBORDINATE dc_histogram_data
19 SUBORDINATE dc_partition_scns
7 SUBORDINATE dc_users
7 SUBORDINATE dc_users
47 SUBORDINATE realm auth
47 SUBORDINATE realm auth
49 SUBORDINATE Realm Subordinate Cache
7 SUBORDINATE dc_users
7 SUBORDINATE dc_users
21 SUBORDINATE rule_fast_operators
7 SUBORDINATE dc_users
75 rows selected.
시퀀스 사용 시 row cache lock 확인
# 시퀀스 생성
HR@ora19c> create sequence hr.seq_1 nocache;
Sequence created.
# 시퀀스 정보 조회
SYS@ora19c> col sequence_owner format a10
SYS@ora19c> col sequence_name format a20
SYS@ora19c> select * from dba_sequences where sequence_owner = 'HR' and sequence_name = 'SEQ_1';
SEQUENCE_O SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S S K
---------- -------------------- ---------- ---------- ------------ - - ---------- ----------- - - - - -
HR SEQ_1 1 1.0000E+28 1 N N 0 1 N N N N N- c :
cycle_flag
세션 1, 2가 동시에 수행
HR@ora19c> declare
v_value number;
begin
for i in 1..100000 loop
select hr.seq_1.nextval into v_value from dual;
end loop;
end;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.row cache lock 조회
SYS@ora19c> select h.address, h.saddr, s.sid, h.lock_mode
from v$rowcache_parent h, v$rowcache_parent w, v$session s
where h.address = w.address
and w.saddr = (select saddr from v$session where event = 'row cache lock' and rownum = 1)
and h.saddr = s.saddr
and h.lock_mode > 0; 2 3 4 5 6
no rows selected
SYS@ora19c> /
no rows selected
SYS@ora19c> /
ADDRESS SADDR SID LOCK_MODE
---------------- ---------------- ---------- ----------
000000006933C100 0000000074133F48 44 5
SYS@ora19c> /
ADDRESS SADDR SID LOCK_MODE
---------------- ---------------- ---------- ----------
000000006933C100 000000007425D000 163 5
SYS@ora19c> /
ADDRESS SADDR SID LOCK_MODE
---------------- ---------------- ---------- ----------
000000006933C100 0000000074133F48 44 5
SYS@ora19c> /
ADDRESS SADDR SID LOCK_MODE
---------------- ---------------- ---------- ----------
000000006933C100 0000000074133F48 44 5
SYS@ora19c> /
ADDRESS SADDR SID LOCK_MODE
---------------- ---------------- ---------- ----------
000000006933C100 000000007425D000 163 5
SYS@ora19c> /
ADDRESS SADDR SID LOCK_MODE
---------------- ---------------- ---------- ----------
000000006933C100 0000000074133F48 44 5
SYS@ora19c> /
no rows selected
누적 대기 이벤트 조회
SYS@ora19c> select sid, event, total_waits, time_waited
from v$session_event
where sid in (select sid from v$session where username = 'HR'); 2 3
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------- ----------- -----------
163 Disk file operations I/O 1 0
163 log file switch completion 4 4
163 log file sync 3 1
163 db file sequential read 3 0
163 db file scattered read 280 17
163 db file parallel read 54 0
163 row cache mutex 16 22
****163 row cache lock 75214 1052
163 library cache: mutex X 6 6
163 SQL*Net message to client 10 0
163 SQL*Net message from client 9 13838
163 events in waitclass Other 16 1
44 Disk file operations I/O 1 0
44 log file switch completion 1 2
44 log file sync 3 2
44 db file sequential read 3 0
44 db file scattered read 263 17
44 db file parallel read 37 0
44 row cache mutex 12 18
44 row cache lock 75227 1061
44 cursor: pin S 2 0
44 library cache: mutex X 12 14
44 SQL*Net message to client 10 0
44 SQL*Net message from client 9 13384
44 events in waitclass Other 18 1
25 rows selected.추적
# sid로 SQL Address 찾기
SYS@ora19c> select prev_sql_addr from v$session where sid = 163;
PREV_SQL_ADDR
----------------
00000000621B03A0
# sql address로 SQL 문장 찾기
SYS@ora19c> select sql_text from v$sql where address = (select prev_sql_addr from v$session where sid = 163);
SQL_TEXT
-------------------------------------------------------
declare v_value number; begin for i in 1..100000 loop
select hr.seq_1.nextval into v_value from dual; end
loop; end;
# 시퀀스 정보 조회 -> 캐시 설정 안됨
SYS@ora19c> select * from dba_sequences where sequence_owner = 'HR' and sequence_name = 'SEQ_1';
SEQUENCE_O SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S S K
---------- -------------------- ---------- ---------- ------------ - - ---------- ----------- - - - - -
HR SEQ_1 1 1.0000E+28 1 N N 0 220001 N N N N N- row cache lock 대기 이벤트가 발생된 원인은 sequence nocache 속성으로 인해 발생
해결방안
- cache 속성으로 수정해야 함
SYS@ora19c> alter sequence hr.seq_1 cache 20;
Sequence altered.
SYS@ora19c> select * from dba_sequences where sequence_owner = 'HR' and sequence_name = 'SEQ_1';
SEQUENCE_O SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S S K
---------- -------------------- ---------- ---------- ------------ - - ---------- ----------- - - - - -
HR SEQ_1 1 1.0000E+28 1 N N 20 220001 N N N N N- cache size를 20으로 설정
세션 1, 2에서 시퀀스 동시 사용
HR@ora19c> declare
v_value number;
begin
for i in 1..100000 loop
select hr.seq_1.nextval into v_value from dual;
end loop;
end;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.row cache lock 조회
SYS@ora19c> select h.address, h.saddr, s.sid, h.lock_mode
from v$rowcache_parent h, v$rowcache_parent w, v$session s
where h.address = w.address
and w.saddr = (select saddr from v$session where event = 'row cache lock' and rownum = 1)
and h.saddr = s.saddr
and h.lock_mode > 0; 2 3 4 5 6
no rows selected
SYS@ora19c> /
no rows selected
SYS@ora19c> /
no rows selected
SYS@ora19c> /
no rows selected누적 대기 이벤트 조회
SYS@ora19c> select sid, event, total_waits, time_waited
from v$session_event
where sid in (select sid from v$session where username = 'HR'); 2 3
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------- ----------- -----------
163 Disk file operations I/O 1 0
163 log file sync 2 1
163 db file scattered read 11 1
163 enq: SQ - contention 4600 155
163 cursor: pin S 1 0
163 library cache: mutex X 14 13
163 SQL*Net message to client 9 0
163 SQL*Net message from client 8 4624
163 events in waitclass Other 19 1
44 Disk file operations I/O 1 0
44 log file sync 2 1
44 db file scattered read 14 1
44 enq: SQ - contention 4618 188
44 cursor: pin S 3 1
44 library cache: mutex X 13 13
44 SQL*Net message to client 9 0
44 SQL*Net message from client 8 4857
44 events in waitclass Other 23 2
18 rows selected.enq: SQ - contention: 시퀀스 캐시 크기가 작아서 발생
해결 방안
- cache 크기 설정을 크게 해줘야 함
SYS@ora19c> alter sequence hr.seq_1 cache 100;
Sequence altered.
SYS@ora19c> select * from dba_sequences where sequence_owner = 'HR' and sequence_name = 'SEQ_1';
SEQUENCE_O SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S S K
---------- -------------------- ---------- ---------- ------------ - - ---------- ----------- - - - - -
HR SEQ_1 1 1.0000E+28 1 N N 100 420001 N N N N N
- 시퀀스 캐시 크기를 100으로 설정
세션 1, 2에서 시퀀스 동시 사용
HR@ora19c> declare
2 v_value number;
begin
for i in 1..100000 loop
select hr.seq_1.nextval into v_value from dual;
end loop;
end;
/ 3 4 5 6 7 8
PL/SQL procedure successfully completed.누적 대기 이벤트 조회
SYS@ora19c> select sid, event, total_waits, time_waited
from v$session_event
where sid in (select sid from v$session where username = 'HR'); 2 3
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------- ----------- -----------
163 Disk file operations I/O 1 0
163 log file sync 2 1
163 db file scattered read 3 0
163 enq: SQ - contention 212 11
163 cursor: pin S 1 1
163 library cache: mutex X 5 5
163 SQL*Net message to client 9 0
163 SQL*Net message from client 8 4097
163 events in waitclass Other 12 0
44 Disk file operations I/O 1 0
44 log file sync 2 0
44 db file scattered read 2 0
44 enq: SQ - contention 203 20
44 cursor: pin S 2 1
44 library cache: mutex X 10 10
44 SQL*Net message to client 9 0
44 SQL*Net message from client 8 4368
44 events in waitclass Other 17 1
18 rows selected.enq: SQ - contention대기 시간이 줄어 들음
'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글
| [아이티윌 오라클 DBA 과정 91기] 260126 TIL (0) | 2026.01.29 |
|---|---|
| [아이티윌 오라클 DBA 과정 91기] 260122 TIL (0) | 2026.01.29 |
| [아이티윌 오라클 DBA 과정 91기] 260120 TIL (1) | 2026.01.20 |
| [아이티윌 오라클 DBA 과정 91기] 260119 TIL (0) | 2026.01.20 |
| [아이티윌 오라클 DBA 과정 91기] 260116 TIL (0) | 2026.01.20 |