[아이티윌 오라클 DBA 과정 91기] 260120 TIL
2026. 1. 20. 21:34ㆍCourses/아이티윌 오라클 DBA 과정
SGA(System Global Area)
- 모든 오라클 프로세스가 액세스하는 공유 메모리
- SGA 영역에 있는 구성 요소들은 DB 운영 중에 동적으로 설정할 수 있음
- SGA 메모리는 SGA_MAX_SIZE 파라미터로 설정
SYS@ora19c> show sga
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 683671552 bytes
Database Buffers 130023424 bytes
Redo Buffers 7872512 bytes
- Fixed Size
- 오라클에서 내부적으로 사용되는 알고리즘이 수행하는 영역
- 백그라운드 프로세스가 액세스해야 하는 데이터베이스 및 인스턴스 상태에 대한 정보
- 오라클이 관리
- Variable Size = Shared Pool + Large Pool + Streams Pool
Shared Pool
- Library Cache
- 실행 가능한 SQL 및 PL/SQL 코드 저장
- shared sql area : SQL 문장, Parse tree, 실행 계획
- plsql area : PL/SQL 문장, 컴파일 정보, Source Code, P(Parse)-Code, M(Machine)-Code, Error
- handel + LCO(Library Cache Obejct)
- handle = name(SQL 문, PL/SQL문) + 메타정보
- LCO = 종속 관계 정보, child table, 데이터 블록(실행 계획 정보)
- Data Dictionary Cahce(row cache)
- 딕셔너리 정보들이 저장되어 있는 메모리
- session, process, enqueue, transaction 정보
- shared server 환경을 사용하는 경우 UGA(User Global Area)
shared_pool_size파라미터를 통해 관리
SYS@ora19c> select count(*) from v$sgastat where pool = 'shared pool';
COUNT(*)
----------
1446SELECT 문 처리 단계
select * from hr.employees where employee_id = 100;
- parse
- syntax check(문법 체크)
- semantic check(의미 분석 체크)
- 데이터 딕셔너리 정보 이용(Data Dictionary Cache 조회)
- user$, obj$, tab$, seg$, col$, objauth$, sysauth$…
- soft parsing
- hard parsing
- bind(옵션)
- SQL 문에 변수 처리가 되어 있으면 실제 값이 입력되는 단계
- execute
- library cache lock과 library cache pin을 shared 모드로 변환하고 블록 I/O 발생
- active set(결과 집합 ) 생성
- fetch
- library cache lock은 null 모드로 변환하고 library cache pin은 해제
- active set결과를 user process에 전달
Soft Parsing
- 동일한 SQL 문에 shared pool 메모리 안 library cache에 있는지 조회
- 동일한 SQL문 : 공백 문자, tab key, enter key, 대소문자, 소유자명, 주석(--, /* /), 힌트(/+ */), 상수 값
- 동일한 SQL문이 있으면 실행 계획을 공유하기 위해서 사용
- library cache를 조회하기 위해서 latch(9i) 또는 mutex(10g)를 잡고 검색
Hard Parsing
- Soft Parsing 실패 시 발생
- 메모리 할당
- shared pool latch를 획득한 후 적절한 크기의 free chunk를 free list에서 찾음
- 만약에 latch에 경합이 발생하면
latch : shared pool대기 이벤트 발생 - 만약에 프리 조각이 7k, 10k, 1k, 100byte 있고, 필요한 프리 공간은 8k라면 이 중에 10k 프리 공간을 8k로 확보하고 남은 2k는 프리 공간으로 나둠
- 후에 프리 조각이 7k, 2k, 1k, 100byte로 남은 상태에서 필요한 프리 공간이 9k라면 프리 공간은 있지만 필요한 크기 만큼의 프리 조각이 없으면 ORA-04031 오류 발생
- ORA-04031 발생하는 경우 shared pool을 flush해야 함
alter system flush shared_pool;
- 프리 메모리를 확보하게 되면 library cache handle에 대해 library cache lock을 exclusive 모드로 획득하고 LCO(Library Cache Object)를 생성
- LCO가 생성되면 library cache lock은 NULL 모드로 변환
- library cache pin을 exclusive 모드로 획득한 후 실행 계획 생성
- 이 때 shared mode로 접근하는 세션이 있다면
library cache pinwait event 발생
- 이 때 shared mode로 접근하는 세션이 있다면
- Shared Cursor 무효화
- DDL로 인한 테이블 구조 변경, 삭제 시 LCO가 무효화 되어야 함
- Library cache lock을 exclusive 모드로 잡아야 함
- 이미 shared mode로 잡고 LCO를 사용 중인 세션이 있는 경우 무효화 시키려는 세션이 기다려야 함
- 실행 계획(execution plan)
- 데이터 처리 방법 결정
- full table scan
- rowid scan(by user rowid, by index rowid)
- 조인 방법
- nested loop join
- sort merge join
- hash join
- 조인 순서
- from 절에 나열되어 있는 테이블의 수만큼 순서를 결정(n!)
- 데이터 처리 방법 결정
Latch, Mutex, Lock
- 동시 작업으로부터 오라클의 자원을 보호하는 매커니즘 필요
- 동시에 수많은 프로세스가 동일 리소스를 사용할 수 있음
- 리소스를 보호할 정밀한 동기화 매커니즘이 없다면 리소스의 일관성을 손쉽게 깨어지기 때문에 이를 보호하기 위해 사용
Latch
- 가벼운 락(lightweigh lock), lock 중에서도 아주 빠른 속도로 작동하게끔 구현된 경량화된 락
- 유닉스, 리눅스 OS에서 제공하는 Mutex(mutually exclusive)와 유사
- 특정 리소스에 대한 동기화를 구현하기 위해 고안된 객체
- shared pool 영역에 존재하는 일종의 메모리 구조체
- 래치를 획득(get)하고 해제(release)하는 작업
- 래치 획득의 순서를 보장하지 않음
- 오라클은 순서가 보장되어야 하는 작업에 대해서는 lock을 사용해서 동기화 수행
- SGA 보호
- library cache 영역을 탐색하고자 하는 모든 프로세스는 반드시 해당 해시 버킷을 보호하는 latch(9i)/mutex(10g)를 획득해야 함
실습
실행 계획 조회
상수 값 사용
SYS@ora19c> alter system flush shared_pool;
System altered.
HR@ora19c> select * from hr.employees where employee_id = 100;
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, hash_value, plan_hash_value
from v$sql
where sql_text like '%hr.employees%'
and sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS HASH_VALUE PLAN_HASH_VALUE
------------- -------------------------------------------------- ----------- ---------- ---------- ---------- ---------------
2sgjc8u8ha0m4 select * from hr.employees where employee_id = 100 1 1 1 2433024612 1833546154
HR@ora19c> select * from hr.employees where employee_id = 100;
SYS@ora19c> /
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS HASH_VALUE PLAN_HASH_VALUE
------------- -------------------------------------------------- ----------- ---------- ---------- ---------- ---------------
2sgjc8u8ha0m4 select * from hr.employees where employee_id = 100 2 1 2 2433024612 1833546154- parse call과 executions 함께 증가
HR@ora19c> select * from hr.employees where employee_id = 100;
HR@ora19c> select * from hr.employees where employee_id = 101;
HR@ora19c> select * from hr.employees where employee_id = 102;
HR@ora19c> select * from hr.employees where employee_id = 103;
SYS@ora19c> /
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS HASH_VALUE PLAN_HASH_VALUE
------------- -------------------------------------------------- ----------- ---------- ---------- ---------- ---------------
82mnzcywm53rs select * from hr.employees where employee_id = 101 1 1 1 3107098360 1833546154
czmhpuznkxr1r select * from hr.employees where employee_id = 102 1 1 1 3912162359 1833546154
2sgjc8u8ha0m4 select * from hr.employees where employee_id = 100 2 1 2 2433024612 1833546154
crmr8navwm6mf select * from hr.employees where employee_id = 103 1 1 1 3083442798 1833546154- 상수 값에 따라 실행 계획이 각각 생김
v$sql_plan
select * from v$sql_plan where sql_id = '82mnzcywm53rs';
- 실행 계획 정보를 가지는 뷰
- 보기 어려움
dbms_xplan.display_cursor
- 실행 계획을 보기 좋게 보여줌
SYS@ora19c> select * from table(dbms_xplan.display_cursor('82mnzcywm53rs'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 82mnzcywm53rs, child number 0
-------------------------------------
select * from hr.employees where employee_id = 101
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=101)
19 rows selected.
SYS@ora19c> select * from table(dbms_xplan.display_cursor('czmhpuznkxr1r'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID czmhpuznkxr1r, child number 0
-------------------------------------
select * from hr.employees where employee_id = 102
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=102)
19 rows selected.바인드 변수 사용
HR@ora19c> var b_id number
HR@ora19c> exec :b_id := 100
PL/SQL procedure successfully completed.
HR@ora19c> select * from hr.employees where employee_id = :b_id;
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, hash_value, plan_hash_value
from v$sql
where sql_text like '%hr.employees%'
and sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS HASH_VALUE PLAN_HASH_VALUE
------------- -------------------------------------------------- ----------- ---------- ---------- ---------- ---------------
gdh3251n9spny select * from hr.employees where employee_id = :b_ 1 1 1 1755076254 1833546154
id
82mnzcywm53rs select * from hr.employees where employee_id = 101 1 1 1 3107098360 1833546154
czmhpuznkxr1r select * from hr.employees where employee_id = 102 1 1 1 3912162359 1833546154
2sgjc8u8ha0m4 select * from hr.employees where employee_id = 100 2 1 2 2433024612 1833546154
crmr8navwm6mf select * from hr.employees where employee_id = 103 1 1 1 3083442798 1833546154
HR@ora19c> exec :b_id := 101;
HR@ora19c> select * from hr.employees where employee_id = :b_id;
HR@ora19c> exec :b_id := 102
HR@ora19c> select * from hr.employees where employee_id = :b_id;
HR@ora19c> exec :b_id := 103;
HR@ora19c> select * from hr.employees where employee_id = :b_id;
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, hash_value, plan_hash_value
from v$sql
where sql_text like '%hr.employees%'
and sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS HASH_VALUE PLAN_HASH_VALUE
------------- -------------------------------------------------- ----------- ---------- ---------- ---------- ---------------
gdh3251n9spny select * from hr.employees where employee_id = :b_ 4 1 4 1755076254 1833546154
id
82mnzcywm53rs select * from hr.employees where employee_id = 101 1 1 1 3107098360 1833546154
czmhpuznkxr1r select * from hr.employees where employee_id = 102 1 1 1 3912162359 1833546154
2sgjc8u8ha0m4 select * from hr.employees where employee_id = 100 2 1 2 2433024612 1833546154
crmr8navwm6mf select * from hr.employees where employee_id = 103 1 1 1 3083442798 1833546154- 실행 계획을 공유하기 때문에 parse call과 executes 수만 늘어남
실행 계획 무효화
테이블 생성
SYS@ora19c> drop table hr.emp purge;
Table dropped.
SYS@ora19c> create table hr.emp tablespace users as select * from hr.employees;
Table created.
# ctas는 통계 정보 수집됨
SYS@ora19c> select num_rows, avg_row_len, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed
from dba_tables where owner = 'HR' and table_name = 'EMP';
NUM_ROWS AVG_ROW_LEN LAST_ANALYZED
---------- ----------- -------------------
107 69 2026-01-20 14:13:29SQL 수행
SYS@ora19c> alter system flush shared_pool;
System altered.
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text like '%hr.emp%'
and sql_text not like '%v$sql%'; 2 3 4
no rows selected
HR@ora19c> select last_name, salary from hr.emp where employee_id = 100;
LAST_NAME SALARY
------------------------- ----------
King 24000
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text like '%hr.emp%'
and sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS
------------- -------------------------------------------------- ----------- ---------- ---------- -------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe 1 1 1 0
e_id = 100
SYS@ora19c> select * from table(dbms_xplan.display_cursor('8qhp0dmv9q94d'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8qhp0dmv9q94d, child number 0
-------------------------------------
select last_name, salary from hr.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 | 16 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
18 rows selected.
- 처음으로 수행한 쿼리이기 때문에 하드 파싱 발생
제약 조건 추가
SYS@ora19c> alter table hr.emp add constraint emp_id_pk primary key(employee_id);
Table altered.
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text like '%hr.emp%'
and sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS
------------- -------------------------------------------------- ----------- ---------- ---------- -------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe 1 1 1 1
e_id = 100- 제약 조건을 추가함에 따라 기존 실행 계획은 사용할 수 없게 됨 → 무효화
- invalidations가 1 증가함
HR@ora19c> select last_name, salary from hr.emp where employee_id = 100;
LAST_NAME SALARY
------------------------- ----------
King 24000
SYS@ora19c> /
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS
------------- -------------------------------------------------- ----------- ---------- ---------- -------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe 1 2 1 1
e_id = 100
SYS@ora19c> select * from table(dbms_xplan.display_cursor('8qhp0dmv9q94d'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8qhp0dmv9q94d, child number 0
-------------------------------------
select last_name, salary from hr.emp where employee_id = 100
Plan hash value: 1252232671
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 16 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_ID_PK | 1 | | 0 (0)| |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
19 rows selected. - sql 수행 시 loads 가 증가함 → 하드 파싱이 일어남
- table full scan → index scan 방식으로 실행 계획이 바뀜
- access through : 데이터의 위치를 알고 찾아가는 방식
- 실행 계획이 다시 생성되면 parse call과 executions는 1부터 다시 시작
제약 조건 삭제
SYS@ora19c> alter table hr.emp drop primary key;
Table altered.
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text like '%hr.emp%'
and sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS
------------- -------------------------------------------------- ----------- ---------- ---------- -------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe 1 2 1 2
e_id = 100
HR@ora19c> select last_name, salary from hr.emp where employee_id = 100;
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text like '%hr.emp%'
and sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS
------------- -------------------------------------------------- ----------- ---------- ---------- -------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe 1 3 1 2
e_id = 100
SYS@ora19c> select * from table(dbms_xplan.display_cursor('8qhp0dmv9q94d'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8qhp0dmv9q94d, child number 0
-------------------------------------
select last_name, salary from hr.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 | 16 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
18 rows selected. 인덱스 생성
HR@ora19c> create unique index hr.emp_idx on hr.emp(employee_id) tablespace users;
Index created.
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text like '%hr.emp%'
and sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS
------------- -------------------------------------------------- ----------- ---------- ---------- -------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe 1 3 1 3
e_id = 100
HR@ora19c> select last_name, salary from hr.emp where employee_id = 100;
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text like '%hr.emp%'
and sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS
------------- -------------------------------------------------- ----------- ---------- ---------- -------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe 1 4 1 3
e_id = 100
HR@ora19c> select last_name, salary from hr.emp where employee_id = 100;
SYS@ora19c> /
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS
------------- -------------------------------------------------- ----------- ---------- ---------- -------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe 2 4 2 3
e_id = 100 기존 인덱스를 제약 조건으로 추가
HR@ora19c> alter table hr.emp add constraint emp_id_pk primary key(employee_id) using index hr.emp_idx;
Table altered.
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text like '%hr.emp%'
and sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS
------------- -------------------------------------------------- ----------- ---------- ---------- -------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe 2 4 2 4
e_id = 100
HR@ora19c> select last_name, salary from hr.emp where employee_id = 100;
SYS@ora19c> /
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS
------------- -------------------------------------------------- ----------- ---------- ---------- -------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe 1 5 1 4
e_id = 100 컬럼 변경
HR@ora19c> alter table hr.emp modify last_name varchar2(40);
Table altered.
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text like '%hr.emp%'
and sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS
------------- -------------------------------------------------- ----------- ---------- ---------- -------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe 1 5 1 5
e_id = 100
HR@ora19c> select last_name, salary from hr.emp where employee_id = 100;
SYS@ora19c> /
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS
------------- -------------------------------------------------- ----------- ---------- ---------- -------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe 1 6 1 5
e_id = 100통계 정보 수집
SYS@ora19c> select num_rows, avg_row_len, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed
from dba_tables where owner = 'HR' and table_name = 'EMP'; 2
NUM_ROWS AVG_ROW_LEN LAST_ANALYZED
---------- ----------- -------------------
107 69 2026-01-20 14:13:29
SYS@ora19c> exec dbms_stats.gather_table_stats('hr','emp')
PL/SQL procedure successfully completed.
SYS@ora19c> select num_rows, avg_row_len, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed
from dba_tables where owner = 'HR' and table_name = 'EMP'; 2
NUM_ROWS AVG_ROW_LEN LAST_ANALYZED
---------- ----------- -------------------
107 69 2026-01-20 15:05:19
SYS@ora19c> select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text like '%hr.emp%'
and sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT PARSE_CALLS LOADS EXECUTIONS INVALIDATIONS
------------- -------------------------------------------------- ----------- ---------- ---------- -------------
8qhp0dmv9q94d select last_name, salary from hr.emp where employe 1 6 1 5
e_id = 100- 통계 정보를 수집한 경우에도 무효화가 발생해야 하지만 천천히 수행함
SYS@ora19c> select a.ksppinm parameter, b.ksppstvl value
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_optimizer_invalidation_period';
PARAMETER VALUE
-------------------------------------------------------------------------------- ------------------------------
_optimizer_invalidation_period 18000
- 통계 수집을 하게 되면
_optimizer_invalidation_period히든 파라미터 설정되어 있는 값 동안 천천히 LCO를 invalidation함(초 단위) - 18000초 → 5시간
Library Cache Lock 확인
- library cache lock : LCO(Library Cache Obejct)를 접근하거나 변경하는 경우 handle에 대해 획득하는 lock
- 여러 세션에서 동시에 동일한 LCO를 변경하는 것을 방지하기 위해서 사용
- SQL Hard Pasing : library cache lock은 exclusive mode로 획득
- LCO를 생성하면 library cache lock은 null mode로 반환
- SQL Execute 단계 : library cache lock은 shared mode로 획득
- SQL Fetch 단계 : library cache lock은 null mode로 획득
- DDL 문장 : library cache lock은 exclusive mode로 획득
- CREATE OR REPLACE PROCEDURE | FUNCTION | PACKAGE : library cache lock은 exclusive mode로 획득
session1
HR@ora19c> exec dbms_application_info.set_client_info('sess_1')
PL/SQL procedure successfully completed.
session2
HR@ora19c> exec dbms_application_info.set_client_info('sess_2')
PL/SQL procedure successfully completed.대기 이벤트
- 작업을 수행하지 못하고 기다리는 상태
SYS@ora19c> select client_info, sid from v$session where client_info in('sess_1', 'sess_2');
CLIENT_INFO SID
---------------------------------------------------------------- ----------
sess_2 137
sess_1 172
SYS@ora19c> col event format a50
SYS@ora19c> col wait_class format a10
SYS@ora19c> select sid, event, wait_class, wait_time, seconds_in_wait, state from v$session_wait where sid in (137, 172);
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- -------------------------------------------------- ---------- ---------- --------------- -------------------
137 SQL*Net message from client Idle 0 80 WAITING
172 SQL*Net message from client Idle 0 77 WAITINGv$session_wait: 세션이 현재 대기하고 있는 이벤트 정보, 누적 정보가 아닌 실시간 정보이므로 반복적으로 조회해야 의미 있는 정보를 얻을 수 있음sid: 세션 idevent: 대기 이벤트wait_class: 대기 이벤트 종류wait_time: 대기 시간, state 컬럼의 값이 waited known time일 경우에만 의미 있음, 단위는 1/100초second_in_wait: 대기 시간, state 컬럼의 값이 waiting일 경우에만 의미 있음 단위는 1초state: 대기 상태- waiting : 실제 대기하고 있는 중
- waited unknown time : 알 수 없는 시간만큼 대기한 후 현재는 cpu를 점유하고 어떤 작업을 하고 있는 중
- waited short time : 1/100초 이하의 짧은 시간을 대기한 후 현재는 cpu를 점유하고 어떤 작업을 하고 있는 중
- waited known time : 1/100초 이상의 시간을 대기한 후 현재는 cpu를 점유하고 어떤 작업을 하고 있는 중
SYS@ora19c> select sid, event, total_waits, time_waited from v$session_event where sid in (137, 172);
SID EVENT TOTAL_WAITS TIME_WAITED
---------- -------------------------------------------------- ----------- -----------
137 Disk file operations I/O 3 0
137 log file sync 5 2
137 db file sequential read 11 1
137 db file scattered read 1 0
137 db file parallel read 4 1
137 index (re)build lock or pin object 4 0
137 SQL*Net message to client 40 0
137 SQL*Net message from client 39 519088
137 SQL*Net break/reset to client 3 0
137 events in waitclass Other 67 0
172 Disk file operations I/O 1 0
172 log file sync 1 0
172 db file sequential read 7 0
172 SQL*Net message to client 9 0
172 SQL*Net message from client 8 8904
172 events in waitclass Other 37 0
16 rows selected.
v$session_event: 세션 별 대기 이벤트의 누적 정보sid: 세션 idevent: 대기 이벤트total_waits: 전체 대기 횟수time_waited: 전체 대기 시간, 1/100초 단위
두 세션에서 프로시저 생성 동시 수행
SYS@ora19c> col event format a50
SYS@ora19c> col wait_class format a10
SYS@ora19c> select sid, event, wait_class, wait_time, seconds_in_wait, state from v$session_wait where sid in (137, 172);
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- -------------------------------------------------- ---------- ---------- --------------- -------------------
137 SQL*Net message from client Idle 0 80 WAITING
172 SQL*Net message from client Idle 0 77 WAITING
# session 1
HR@ora19c> begin
for i in 1..10000 LOOP
execute immediate 'create or replace procedure p1 is begin null; end;';
end loop;
end;
/
PL/SQL procedure successfully completed.
# session 2
HR@ora19c> begin
for i in 1..10000 LOOP
execute immediate 'create or replace procedure p1 is begin null; end;';
end loop;
end;
/
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- -------------------------------------------------- ---------- ---------- --------------- -------------------
137 library cache lock Concurrency 0 0 WAITING
172 library cache lock Concurrency 0 0 WAITING
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- -------------------------------------------------- ---------- ---------- --------------- -------------------
137 SQL*Net message from client Idle 0 5 WAITING
172 SQL*Net message from client Idle 0 5 WAITING- library cache lock 발생
누적 이벤트 조회
SYS@ora19c> select sid, event, total_waits, time_waited from v$session_event where sid in (137, 172);
SID EVENT TOTAL_WAITS TIME_WAITED
---------- -------------------------------------------------- ----------- -----------
137 Disk file operations I/O 3 0
137 log file sync 2 1
137 db file sequential read 35 2
137 db file scattered read 10 2
137 db file parallel read 65 1
137 row cache mutex 20 111
137 cursor: pin S 3 1
137 library cache lock 1566 147
137 library cache: mutex X 25 57
137 library cache: bucket mutex X 5 5
137 SQL*Net message to client 12 0
137 SQL*Net message from client 11 26309
137 SQL*Net break/reset to client 4 0
137 events in waitclass Other 26 0
172 Disk file operations I/O 1 0
172 log file sync 2 1
172 db file sequential read 1 0
172 db file scattered read 10 0
172 db file parallel read 87 1
172 row cache mutex 21 25
172 cursor: pin S 1 0
172 library cache lock 1533 148
172 library cache: mutex X 32 65
172 SQL*Net message to client 12 0
172 SQL*Net message from client 11 26003
172 SQL*Net break/reset to client 3 0
172 events in waitclass Other 12 0
27 rows selected.다시 수행
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
---------- -------------------------------------------------- ---------- ---------- --------------- -------------------
137 SQL*Net message from client Idle 0 70 WAITING
162 SQL*Net message from client Idle 0 66 WAITING
# session 1
HR@ora19c> begin
for i in 1..10000 LOOP
execute immediate 'create or replace procedure p1 is begin null; end;';
end loop;
end;
/ 2 3 4 5 6
# session 2
HR@ora19c> begin
for i in 1..10000 LOOP
execute immediate 'create or replace procedure p1 is begin null; end;';
end loop;
end;
/ 2 3 4 5 6
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- -------------------------------------------------- ---------- ---------- --------------- -------------------
137 library cache lock Concurrency 1 0 WAITED KNOWN TIME
162 library cache lock Concurrency 0 0 WAITING
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- -------------------------------------------------- ---------- ---------- --------------- -------------------
137 library cache: mutex X Concurrency 0 0 WAITING
162 db file parallel read User I/O -1 0 WAITED SHORT TIME
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- -------------------------------------------------- ---------- ---------- --------------- -------------------
137 library cache lock Concurrency 0 0 WAITING
162 library cache lock Concurrency -1 0 WAITED SHORT TIME
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- -------------------------------------------------- ---------- ---------- --------------- -------------------
137 library cache lock Concurrency -1 0 WAITED SHORT TIME
162 library cache lock Concurrency -1 0 WAITED SHORT TIME
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- -------------------------------------------------- ---------- ---------- --------------- -------------------
137 SQL*Net message from client Idle 0 0 WAITING
162 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
---------- -------------------------------------------------- ----------- -----------
162 Disk file operations I/O 1 0
162 log file sync 2 1
162 db file sequential read 2 0
162 db file scattered read 6 1
162 db file parallel read 73 1
162 row cache mutex 18 29
162 cursor: pin S 3 1
162 library cache lock 1813 166
162 library cache: mutex X 34 24
162 SQL*Net message to client 9 0
162 SQL*Net message from client 8 8773
162 events in waitclass Other 14 0
137 Disk file operations I/O 1 0
137 log file switch completion 1 6
137 log file sync 2 1
137 db file sequential read 1 0
137 db file scattered read 14 1
137 db file parallel read 61 1
137 row cache mutex 19 46
137 library cache lock 1778 153
137 library cache: mutex X 27 42
137 library cache: bucket mutex X 1 1
137 SQL*Net message to client 9 0
137 SQL*Net message from client 8 9154
137 events in waitclass Other 12 0
25 rows selected. Library Cache Pin 확인
SYS@ora19c> grant execute on dbms_lock to hr;
Grant succeeded.
SYS@ora19c> create or replace procedure hr.pin_proc(p_time in number)
is
begin
dbms_lock.sleep(p_time);
end pin_proc;
/ 2 3 4 5 6
Procedure created.
SYS@ora19c> exec hr.pin_proc(3)
PL/SQL procedure successfully completed.
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
---------- -------------------------------------------------- ---------- ---------- --------------- -------------------
137 SQL*Net message from client Idle 0 214 WAITING
162 SQL*Net message from client Idle 0 202 WAITIN
session 1 : 프로시저 실행
HR@ora19c> exec hr.pin_proc(5)
PL/SQL procedure successfully completed.session 2 : 프로시저 컴파일
HR@ora19c> alter procedure hr.pin_proc compile;
Procedure altered.v$session_wait 조회
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- -------------------------------------------------- ---------- ---------- --------------- -------------------
137 PL/SQL lock timer Idle 0 1 WAITING
162 library cache pin Concurrency 0 1 WAITING
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- -------------------------------------------------- ---------- ---------- --------------- -------------------
137 PL/SQL lock timer Idle 0 4 WAITING
162 library cache pin Concurrency 0 3 WAITING
SYS@ora19c> /
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- -------------------------------------------------- ---------- ---------- --------------- -------------------
137 SQL*Net message from client Idle 0 1 WAITING
162 SQL*Net message from client Idle 0 1 WAITING- library cache pin 발생
누적 이벤트 조회
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
---------- -------------------------------------------------- ----------- -----------
162 Disk file operations I/O 2 0
162 log file sync 1 1
162 db file sequential read 2 0
162 db file parallel read 1 0
162 library cache pin 1 463
162 SQL*Net message to client 9 0
162 SQL*Net message from client 8 22724
162 events in waitclass Other 17 0
137 log file sync 1 0
137 SQL*Net message to client 9 0
137 SQL*Net message from client 8 23892
137 PL/SQL lock timer 1 523
137 events in waitclass Other 8 0
13 rows selected.'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글
| [아이티윌 오라클 DBA 과정 91기] 260122 TIL (0) | 2026.01.29 |
|---|---|
| [아이티윌 오라클 DBA 과정 91기] 260121 TIL (1) | 2026.01.21 |
| [아이티윌 오라클 DBA 과정 91기] 260119 TIL (0) | 2026.01.20 |
| [아이티윌 오라클 DBA 과정 91기] 260116 TIL (0) | 2026.01.20 |
| [아이티윌 오라클 DBA 과정 91기] 260115 TIL (0) | 2026.01.15 |