2024/01/24
SGA(System Global Area)
- 디스크에서 읽어온 데이터를 저장하는 메모리 영역이다.
- 저장된 데이터를 읽거나 변경하는 작업에서 사용되는 공용 메모리 영역이다.
- 메모리 사용을 최소화하면서 처리성능을 최대화하기 위해 SGA를 사용하는 것이다.
■ shared pool
- library cache
: SQL, PL/SQL 문이 저장되어 있는 메모리, 실행계획을 공유하기 위해사용, LRU 알고리즘을 사용하여 관리한다- database dictionary cache
: 테이블, 인덱스, 뷰, 함수 및 트리거 등의 사용자, 구조, 권한 등의 dictionary data 정보를 저장한다
- db buffer cache 의 block을 매번 access하는것은 부담이간다. 그래서 row단위로 shared pool의 data dictionary에 쌓아 놓는다.
- session 정보
- shared server 환경을 사용할 경우 UGA(User Global Area)가 포함된다.
<-> dedicated 서버환경
: 나의 서버프로세스를 공유해서 쓰겠다.- shared_pool_size
user process
: tool
■ db buffer cache
디스크에있는 data를 access할떄 data block들을 메모리에 올려놓는 공간
- LRU 알고리즘을 사용하여 관리한다
■ select문 처리단계
DML은 3단계이다.
select * from hr.emp
where id=100;- 실행 계획이 없는 상태에서는 절대 SQL문 수행하지 않습니다.
- 데이터 처리결정
- rowid scan
- by user rowid
- by index rowid
- full table scan
- rowid scan
- 조인 방법
- hash
- nested loop
- sort merge
- 조인 순서
- from 절에 나열되어 있는 테이블의 갯수만큼 순서를 결정해야 한다.
- 만약에 조인수행해야할 테이블의 갯수가 n개이면 n! 경우의 수만 큼 결정
- 동일한sql문이란?
대소문자,띄어쓰기,힌트(실행계획제어하는 명령어),테이블의 소유자이름
■ 1. parse
- 문법체크, 의미분석 체크(유저/오브젝트 유뮤, 오브젝트타입, 컬럼), 권한 체크
- 의미분석/권한 체크는 data dictionary cache를 먼저 본다.
- 동일한 sql문이 shared pool 메모리안에 library cache 있는 조회
왜? 동일한 sql문이 있으면 실행계획을 공유하기 때문에
- library cache의 latch 잡고 검색 해야한다.
- latch : libary cache wait event 발생한다. (cpu5 -> latch6개)
- soft parsing 이 실패 즉 동일한 sql문이 shared pool 메모리안에 libarary cache 존재 하지 않습니다. 이때가 바로 hard parsing 발생할때
실행계획이 변경될때
: 테이블에 index를 생성할때, 컬럼을 추가할때
- hard parsing 이 발생하면 실행계획을 새롭게 생성해야한다.
- 메모리 공간 확보
- latch : shared pool wait event 발생
-> 만약에 프리공간이 안될경우 ORA-04031 오류 발생할 수 있다. : 하드파싱이 너무 많이 발생해서, free공간이없거나, 필요한만큼의 공간이 없을때하드파싱
- 예를 들어 프리조각(청크)이 7k,10k,1k,100byte 이렇게 나있을경우 내가 필요한 프리공간은 8k 일 경우
프리로 있는 10k 프리공간을 8k로 확보하고 2k는 프리공간으로 남겨 놓는다.
다시 7k,2k,1k,100byte 이렇게 프리공간이 있을경우 8k프리 공간을 확보하기 위해서 latch를 잡고 프리공간을 조회하는데 없어서 ORA-04031 오류가 발생한다.- 프리를 확보한 후 LCO(Library Cache Object)를 생성해야한다. (실행계획생성)
LCO를 생성하기 위해서는 latch를 잡아야한다.
- library cache lock (exclusive) 획득해야한다.
한쪽은 LCO를 exclusive 모드로 볼때 다른쪽에서 LCO를 share모드로 보면 wait event가 걸린다.
LCO가 생성되는 latch : library cache lock (null) 모드로 변환된다.- library cache pin(exclusive) 모드로 변환한 후 실행계획을 생성한다. => 생성되어있으면 shared 모드인거다.
-> library cache pin : 실행계획을 무효화할 곳에 pin을 박아논다.- 실행계획 생성이 끝난경우 execute 단계로 간다.
- latch
"Latch: library cache" 대기 이벤트가 발생하는 경우, 여러 세션들이 공유 코드에 접근하려고 할 때 래치를 획득하기 위해 대기하고 있는 것을 의미합니다. 이는 동시성 제어를 위한 메커니즘으로 작용하며, 다수의 세션이 동시에 동일한 리소스에 액세스하려고 할 때 발생할 수 있습니다.
■ 2. bind(옵션)
sql문에 변수 처리가 되어 있을 경우 변수에 실제값이 입력되는 단계
- data의 분포도가 차이가 많이 나면 실행계획을 하드파싱
■ 3. execute
- library cache lock과 library cache pin를 shared 모드로 변환하고 sql문을 실행한다.
- 블록 I/O 발생, Database Buffer Cache 영역을 본다.
■ 4. fetch
- library cache lock null 모드로 변환하고 library cache pin 해제한다.
- active set 결과를 user process에 전달한다.
- shared pool 안의 내용을 없앤다.
SYS@ora11g> alter system flush shared_pool;
System altered.
SYS@ora11g> /
System altered.HR@ora11g> select * from hr.employees where employee_id=100;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
100 Steven King
SKING 515.123.4567 17-JUN-03 AD_PRES 24000
90- 가장많이쓰인 SQL문을 볼수있다.
SYS@ora11g> SELECT sql_id, sql_text, parse_calls, loads, executions
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
----------- ---------- ----------
31d96zzzpcys9 select * from hr.employees where employee_id=100 2 1 2HR@ora11g> select * from hr.employees where employee_id=101;
HR@ora11g> select * from hr.employees where employee_id=102;
HR@ora11g> select * from hr.employees where employee_id=104;
HR@ora11g> select * from hr.employees where employee_id=105;- 실행계획 보기
SELECT * FROM table(dbms_xplan.display_cursor('31d96zzzpcys9'));
---------------------------------------------------------------------------------------------
SQL_ID 31d96zzzpcys9, child number 0
-------------------------------------
select * from hr.employees where employee_id=100
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"=100)- plan_hash_value가 같으면 실행계획도 똑같다. hash_value 값의 다르므로 hash bucket도 다르다.
SELECT sql_id, sql_text, parse_calls, loads, executions,
plan_hash_value, hash_value
FROM v$sql
WHERE sql_text like '%hr.employees%'
AND sql_text not like '%v$sql%';
--------------------------------------
31d96zzzpcys9 select * from hr.employees where employee_id=100 4 1 4 1833546154 4283857673
ctp9fgq111pfu select * from hr.employees where employee_id=105 1 1 1 1833546154 2182141402- plan_hash_value은 같으면서 hash_value 값이 10개 이상인것
SELECT plan_hash_value, count(hash_value)
FROM v$sql
group by plan_hash_value
having count(hash_value) >= 10
order by 2 desc;- var b_id
var b_id number
execute :b_id:=100
SELECT * FROM hr.employees WHERE employee_id = :b_id;
-- soft parsing
execute :b_id:=101
SELECT * FROM hr.employees WHERE employee_id = :b_id;- 실행
SYS@ora11g> alter system flush shared_pool;
System altered.
SYS@ora11g> alter system flush shared_pool;
System altered.
SYS@ora11g> select count(*) from hr.emp;
COUNT(*)
----------
108
SYS@ora11g> select sal from hr.emp where id=100;
SAL
----------
24000
SYS@ora11g> SELECT sql_id, sql_text, parse_calls, loads, executions,
plan_hash_value, hash_value
FROM v$sql
WHERE sql_text like '%hr.emp%'
AND sql_text not like '%v$sql%'; 2 3 4 5
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
PARSE_CALLS LOADS EXECUTIONS PLAN_HASH_VALUE HASH_VALUE
----------- ---------- ---------- --------------- ----------
atv111bju937r
select sal from hr.emp where id=100
1 1 1 856241057 3819212023
4jhds8wyvdrx6
select count(*) from hr.emp
1 1 1 631333232 1035394982
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
PARSE_CALLS LOADS EXECUTIONS PLAN_HASH_VALUE HASH_VALUE
----------- ---------- ---------- --------------- ----------
SELECT * FROM table(dbms_xplan.display_cursor('atv111bju937r'));SYS@ora11g> select num_rows, blocks, avg_row_len from dba_tables where owner='HR'
and table_name = 'EMP'; 2
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
108 8 17
- 무효화
execute dbms_stats.gather_table_stats('hr','emp',no_invalidate=>false)
select num_rows, blocks, avg_row_len from dba_tables where owner='HR'
and table_name = 'EMP';
SELECT sql_id, sql_text, parse_calls, loads, executions,
plan_hash_value, hash_value
FROM v$sql
WHERE sql_text like '%hr.emp%'
AND sql_text not like '%v$sql%';
- 인덱스생성
create unique index 인덱스이름 on 테이블(컬럼);
create unique index hr.emp_idx on hr.emp(id);- unique 인덱스를 생성하고 그것을 primary key로 변경할려면
using index 인덱스이름을 넣어줘야한다.
alter table hr.emp add constraint emp_id_pk primary key(employee_id)
using index hr.emp_idx;- 무효화발생했는지 보기
alter table hr.emp modify last_name varchar2(30);
SELECT sql_id, sql_text, parse_calls, loads, executions,
plan_hash_value, hash_value
FROM v$sql
WHERE sql_text like '%hr.emp%'
AND sql_text not like '%v$sql%';실행계획이 변경될때
: 테이블에 index를 생성할때, 컬럼을 추가할때
invalid로 떨어진다. 따라서 조심해서 해야한다.
2024/02/05
■ Library Cache Lock
: Library Cache Lock은 LCO(Library Cache Object) 를 접근하거나 변경하는 경우 handle에 대해 획득하는 Lock 이다.
-
여러 세션에서 동시에 동일한 LCO를 변경하는 것을 방지하기 위해서 사용한다.
-
Library cache lock을 획득하지 못해 대기하는 경우 library cache lock wait event가 발생한다.
-
LCO 모드
: null, shared, exclusive
DML : shared mode
DDL : exclusive mode
SELECT문 hard parsing
- sql hard parsing
: library cache lock을 exclusive mode로 획득해야한다. -> wait event 발생할수 있음
- LCO를 생성하면 library cache lock을 null mode로 변환한다.
- sql문 execute 단계
: library cache lock을 shared mode로 획득해애한다.- sql문 fetch 단계
: library cache lock을 null mode로 획듣해야한다.
- alter 문
: library cache lock을 exclusive mode로 획득해야한다.- create or replace procedure(function,package)
: library cache lock exclusive mode로 획득해야한다.
- wait event
[sess_1]
HR@ora11g> execute dbms_application_info.set_client_info('sess_1')
PL/SQL procedure successfully completed.[sess_2]
HR@ora11g> execute dbms_application_info.set_client_info('sess_2')
PL/SQL procedure successfully completed.[sys_1]
SYS@ora11g> select client_info, sid
from v$session where client_info in ('sess_1', 'sess_2'); 2
CLIENT_INFO SID
---------------------------------------------------------------- ----------
sess_1 21
sess_2 24
SYS@ora11g> select sid, event, wait_class, wait_time,
seconds_in_wait, state
from v$session_wait
where sid in (21,24); 2 3 4
SID EVENT
---------- ----------------------------------------------------------------
WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
-------------------- ---------- --------------- -------------------
21 SQL*Net message from client
Idle 0 613 WAITING
24 SQL*Net message from client
Idle 0 607 WAITING
SYS@ora11g> select sid, event, total_waits, time_waited
from v$session_event
where sid in (21,24); 2 3
SID EVENT
---------- ----------------------------------------------------------------
TOTAL_WAITS TIME_WAITED
----------- -----------
21 SQL*Net message to client
14 0
21 SQL*Net message from client
13 17489
21 SQL*Net break/reset to client
2 0
SID EVENT
---------- ----------------------------------------------------------------
TOTAL_WAITS TIME_WAITED
----------- -----------
24 SQL*Net message to client
13 0
24 SQL*Net message from client
12 17338
- session 1,2 가 동시에 프로시저를 실시하여 wait event나오게 한다. (v$session_wait)
[sess_1]
HR@ora11g> begin
for i in 1..10000 loop
execute immediate 'create or replace procedure p1 is begin null; end;';
end loop;
end;
/
[sess_2]
HR@ora11g> begin
for i in 1..10000 loop
execute immediate 'create or replace procedure p1 is begin null; end;';
end loop;
end;
/
[sys_2]
SYS@ora11g> select sid, event, wait_class, wait_time,
seconds_in_wait, state
from v$session_wait
where sid in (21,24);
SID EVENT
---------- ----------------------------------------------------------------
WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
-------------------- ---------- --------------- -------------------
21 library cache lock
Concurrency 0 0 WAITING
24 library cache lock
Concurrency 0 0 WAITING- v$session_event
SYS@ora11g> select sid, event, total_waits, time_waited
from v$session_event
where sid in (21,24); 2 3
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ------------------------------ ----------- -----------
21 Disk file operations I/O 3 0
21 log file sync 2 2
21 db file sequential read 121 0
21 db file scattered read 1 0
21 latch: row cache objects 5 0
21 library cache lock 1000 11
21 library cache: mutex X 12 2
21 SQL*Net message to client 16 0
21 SQL*Net message from client 15 121833
21 SQL*Net break/reset to client 2 0
21 events in waitclass Other 1 0
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ------------------------------ ----------- -----------
24 Disk file operations I/O 1 0
24 log file switch completion 2 2
24 log file sync 3 1
24 db file sequential read 167 0
24 latch: row cache objects 7 0
24 latch: shared pool 5 0
24 library cache lock 1425 13
24 library cache: mutex X 19 9
24 SQL*Net message to client 16 0
24 SQL*Net message from client 15 120784
24 events in waitclass Other 2 0
22 rows selected.library cache: mutex X
: library cache에 exclusive 모드에 경합이 발생했다.
만약 S 이면 -> shared mode에 경합 발생
latch: row cache objects
: dictionary cache 경합이 발생했다.
- v$session을 이용하여 sql문 확인
SYS@ora11g> select prev_sql_id from v$session where sid in (21,24);
PREV_SQL_ID
-------------
at6dq8sx8nsfg
at6dq8sx8nsfg
SYS@ora11g> select sql_text from v$sql where sql_id='at6dq8sx8nsfg';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
begin for i in 1..10000 loop execute immediate 'create or replace procedure p1 is begin null; end;'; end loop; end;■ 두가지 세션에서 작업
- sess_1
HR@ora11g> create table hr.test(id number, name char(10));
Table created.
HR@ora11g> insert into hr.test select rownum, lpad(rownum,10,'0') from dual connect by level <= 1000000;
1000000 rows created.
HR@ora11g> commit;
Commit complete.
HR@ora11g> execute dbms_application_info.set_client_info('sess_1')
PL/SQL procedure successfully completed.
HR@ora11g> alter table hr.test modify name char(20);- sess_2
HR@ora11g> execute dbms_application_info.set_client_info('sess_2')
PL/SQL procedure successfully completed.
HR@ora11g> select count(*) from hr.test;- sys
SYS@ora11g> select client_info, sid
from v$session where client_info in ('sess_1', 'sess_2');
CLIENT_INFO SID
---------------------------------------------------------------- ----------
sess_2 182
sess_1 184
- library cache lock 확인
SYS@ora11g> select sid, event, wait_class, wait_time,
seconds_in_wait, state
from v$session_wait
where sid in (182,184); 2 3 4
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ------------------------------ -------------------- ---------- --------------- -------------------
182 library cache lock Concurrency 0 11 WAITING
184 control file parallel write System I/O 0 0 WAITING- session_event
SYS@ora11g> select sid, event, total_waits, time_waited
from v$session_event
where sid in (182,184); 2 3
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ------------------------------ ----------- -----------
182 Disk file operations I/O 2 0
182 db file sequential read 58 0
182 db file scattered read 10 0
182 db file parallel read 1 1
182 library cache lock 1 5862
182 SQL*Net message to client 15 0
182 SQL*Net message from client 14 16146
184 Disk file operations I/O 255 87
184 Data file init write 376 5
184 control file sequential read 2407 1
184 control file parallel write 581 213
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ------------------------------ ----------- -----------
184 log buffer space 177 986
184 log file switch (checkpoint in 23 2960
complete)
184 log file switch completion 14 122
184 log file sync 1 1
184 db file sequential read 37976 52
184 db file scattered read 5 3
184 db file single write 83 11
184 flashback log file sync 83 30
184 SQL*Net message to client 14 0
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ------------------------------ ----------- -----------
184 SQL*Net message from client 13 20198
184 events in waitclass Other 93 400
22 rows selected.- 순간적인 wait event 조회
select
h.sid,
substr(s.sql_text, 1 , 40) as sql_text,
k.kglhdnsp,
K.kglnaobj,
decode(k.kgllkmod, 3 , '3(X)', 2, '2(S)' , 1, '1(N)', k.kgllkmod) as lkmode
from sys.x$kgllk k,v$session_wait w, v$session h, v$sqlarea s
where w.event = 'library cache lock'
and h.saddr = k.kgllkuse
and h.sql_id = s.sql_id(+)
and k.kgllkhdl = ( case when w.p1raw like '00000000%' then substr(w.p1raw,9,8) else w.p1raw ||'' end)
and k.kgllkmod > 0;
■ Library cache pin
- sql문 hard parsing
: library cache pin 을 exclusive 획득한 후 실행계획 생성- sql문 execute 단계
: library cache pin 을 shared 획득- sql문 fetch 단계
: library cache pin 을 해제한다.- procedure(function,parckage) 실행단계
: library cache pin 을 shared 획득- alter procedure(function,package)..compile
: library cache pin 을 exclusive 획득
▶ Library cache pin 경합 시나리오
[sys sess]
SYS@ora11g> grant execute on dbms_lock to hr;
Grant succeeded.[hr sess1]
HR@ora11g> create or replace procedure pin_proc(p_time in number)
is
begin
dbms_lock.sleep(p_time);
end;
/ 2 3 4 5 6
Procedure created.- sess_1 에서 execute
HR@ora11g> execute dbms_application_info.set_client_info('sess_1')
HR@ora11g> execute pin_proc(100)- sess_2 에서 alter
HR@ora11g> execute dbms_application_info.set_client_info('sess_2')
HR@ora11g> alter procedure pin_proc compile;- 경합 확인
SYS@ora11g> select client_info, sid, blocking_session, event
from v$session where client_info in ('sess_1', 'sess_2'); 2
CLIENT_INFO SID BLOCKING_SESSION EVENT
---------------------------------------------------------------- ---------- ---------------- ----------------------------------------------------------------
sess_1 184 PL/SQL lock timer
sess_2 185 184 library cache pin- library cache pin event를 볼수있다.
SYS@ora11g> select sid, event, total_waits, time_waited
from v$session_event
where sid in (184,185); 2 3
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ------------------------------ ----------- -----------
184 Disk file operations I/O 3 0
184 log file sync 1 0
184 db file sequential read 157 2
184 SQL*Net message to client 16 0
184 SQL*Net message from client 15 48589
184 SQL*Net break/reset to client 2 0
184 PL/SQL lock timer 1 10000
185 Disk file operations I/O 2 0
185 log file sync 1 0
185 db file sequential read 16 0
185 library cache pin 1 9947
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ------------------------------ ----------- -----------
185 SQL*Net message to client 17 0
185 SQL*Net message from client 16 40039
185 SQL*Net break/reset to client 6 0
14 rows selected.- sql
select
h.sid,
substr(s.sql_text, 1 , 40) as sql_text,
k.kglhdnsp,
K.kglnaobj,
decode(n.kglpnmod, 3 , '3(X)', 2, '2(S)' , 1, '1(N)', n.kglpnmod) as lkmode
from sys.x$kgllk k,sys.x$kglpn n, v$session_wait w, v$session h, v$sqlarea s
where h.saddr = n.kglpnuse
and k.kglhdpar = n.kglpnhdl
and h.sql_id = s.sql_id(+)
and n.kglpnhdl = ( case when w.p1raw like '00000000%' then substr(w.p1raw,9,8) else w.p1raw ||'' end)
and k.kgllkmod > 0;◆ 용어정리
library cache latch
: library cache 영역에서 내가 수행한 sql문 또는 pl/sql을 찾을때 사용한다, 탐색을 동기화하는데 사용
- 동일한 sql,pl/sql문이 shared pool안에 library cache에 있는지 탐색하는 latch
- cpu 수에 종속된다.
- latch: library cache -> 누가 cpu수 만큼 탐색하고 있으면 latch를 잡을수 없기때문에 wait event가 발생
ibrary cache lock, library cache pin
: handle, LCO에 대한 보호하는 역할에 사용
shared pool latch
: hard parsing이 발생하는 경우 shared pool의 library cache 메모리를 할당 받기위해서 shared pool latch 를 획득해야한다.
- 9i 버전부터는 shared pool을 여러개의 서브풀로 최대 7개까지 나누워서 관리하며 오라클은 cpu개수가 4개 이상이고 shared pool 크기가 250m 이상인 경우 _kghdsidx_count의 값만큼 서브풀을 생성해서 관리한다.
shared pool latch 의 경합을 줄이기 위해서이다.
- shared pool latch 하나만 사용한다.
SYS@ora11g> select name, gets
from v$latch_children where name ='shared pool'; 2
NAME GETS
---------------------------------------------------------------- ----------
shared pool 52
shared pool 52
shared pool 52
shared pool 52
shared pool 52
shared pool 52
shared pool 4685223- 2개라고 되어있지만 1개라고 생각
SYS@ora11g> show parameter cpu_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 2- hidden parameter
SYS@ora11g> SELECT a.ksppinm Parameter, b.ksppstvl Session_Value, c.ksppstvl Instance_Value
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm = '_kghdsidx_count'; 2 3 4 5
PARAMETER SESSION_VALUE INSTANCE_VALUE
-------------------- -------------------- --------------------
_kghdsidx_count 1 1version count 가 높아지는 사례
- version count 높아지면
똑같은 실행계획인데 latch를 잡고있으므로 CPU사용량 증가하고
SCN을 만드므로 메모리 사용량 증가
▶ 1. 실행계획이 같지만 bind변수 varchar2 사이즈가 작은것 -> 큰것
● 1. 작업
bind 변수 varchar2 size(32,128,2000,4000) 4가지 크기로 결정난다.(bind변수 사용이유 : 글로벌 변수를 선언하기 위해서)
- bind변수 사용불가능
: 프로시저,함수,패키지- bind변수 사용가능
: 익명블록, 트리거
- var name varchar2(10)
SYS@ora11g> alter system flush shared_pool;
System altered.
SYS@ora11g> var name varchar2(10)
SYS@ora11g> exec :name :='King'
PL/SQL procedure successfully completed.
SYS@ora11g> print name
NAME
--------------------------------
King
SYS@ora11g> select last_name,salary from hr.employees where last_name=:name;
LAST_NAME SALARY
------------------------- ----------
King 10000
King 24000- var name varchar2(4000)
SYS@ora11g> var name varchar2(4000)
SYS@ora11g> exec :name :='Grant'
PL/SQL procedure successfully completed.
SYS@ora11g> select last_name,salary from hr.employees where last_name=:name;
LAST_NAME SALARY
------------------------- ----------
Grant 2600
Grant 7000● 2.
- version count 2개가 되었다.
SYS@ora11g> SELECT sql_id, sql_text, version_count
FROM v$sqlarea
WHERE sql_text like '%hr.employees%'
AND sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT VERSION_COUNT
------------------------------ ------------------------------ -------------
0wq1wkrg2mqf9 select last_name,salary from h 2
r.employees where last_name=:n
ame- v$sql_shared_cursor 확인
SYS@ora11g> select address, child_address, child_number,
bind_length_upgradeable
from v$sql_shared_cursor
where sql_id = '0wq1wkrg2mqf9'; 2 3 4
ADDRESS CHILD_AD CHILD_NUMBER B
-------- -------- ------------ -
3F4EFEE8 3F51A420 0 N -- 처음에만들어진 커서
3F4EFEE8 217B0170 1 Y -- child 커서
- 똑같은 실행계획인데 커서가 만들어졋다
-> shared pool 잘못 사용할수 있다.
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('0wq1wkrg2mqf9',0));
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('0wq1wkrg2mqf9',1));select address, child_address, datatype_string,
max_length, value_string
from v$sql_bind_capture
where
SYS@ora11g> desc v$sql_shared_cursor
▶ 2. 사이즈큰거부터
SYS@ora11g> alter system flush shared_pool;
System altered.
select address, child_address, child_number,
bind_length_upgradeable
from v$sql_shared_cursor
where sql_id = '4zrqd8214b8wt';SYS@ora11g> var b_name varchar2(1000)
SYS@ora11g> exec :b_name :='King'
PL/SQL procedure successfully completed.
SYS@ora11g> select last_name,salary from hr.employees where last_name=:b_name;
LAST_NAME SALARY
------------------------- ----------
King 10000
King 24000
SYS@ora11g> var b_name varchar2(10)
SYS@ora11g> exec :b_name :='Grant'
PL/SQL procedure successfully completed.
SYS@ora11g> select last_name,salary from hr.employees where last_name=:b_name;
LAST_NAME SALARY
------------------------- ----------
Grant 2600
Grant 7000
SYS@ora11g> SELECT sql_id, sql_text, version_count
FROM v$sqlarea
WHERE sql_text like '%hr.employees%'
AND sql_text not like '%v$sql%'; 2 3 4
SQL_ID SQL_TEXT VERSION_COUNT
------------------------------ ------------------------------ -------------
bpyw8g9ntj2kp select last_name,salary from h 1
r.employees where last_name=:b
_name
select address, child_address, child_number,
bind_length_upgradeable
from v$sql_shared_cursor
where sql_id = 'bpyw8g9ntj2kp';row cache lock
-
dictionary cache 는 oracle dictionary 정보에 대한 cache 영역
-
유저,테이블,인덱스,시퀀스,컬럼,함수,프로시저,패키지,트리거..
-
DDL 작업을 수행하면 딕셔너리 정보 입력, 수정, 삭제
-
SQL문 수행시에 semantic, 권한 체크시에 딕셔너리 정보를 이용해서 체크한다.
-
row cache lock은 dictionary object 를 보호하는 시스템 lock 입니다.
SYS@ora11g> select pool, name, bytes
from v$sgastat where name ='row cache'; 2
POOL NAME BYTES
------------ -------------------------- ----------
shared pool row cache 4323536SYS@ora11g> select cache#, type, parameter from v$rowcache;▶ 1. sequence를 nocache로 생성할때 row cache lock
● 1. 작업 sequence를 nocache 로 생성
HR@ora11g> create sequence seq_1 nocache;
Sequence created.
SYS@ora11g> select * from dba_sequences
where sequence_owner ='HR' and sequence_name='SEQ_1'; 2
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
HR SEQ_1 1 1.0000E+28 1 N N 0 1- session 1,2 작업
[sess_1]
HR@ora11g> exec dbms_application_info.set_client_info('sess_1')
PL/SQL procedure successfully completed.
[sess_2]
HR@ora11g> exec dbms_application_info.set_client_info('sess_2')
PL/SQL procedure successfully completed.- session 1,2 확인
SYS@ora11g> select client_info, sid
from v$session where client_info in ('sess_1', 'sess_2'); 2
CLIENT_INFO SID
---------------------------------------------------------------- ----------
sess_1 22
sess_2 23- sid 22,23 session wait 보기
SYS@ora11g> select sid, event, wait_class, wait_time,
seconds_in_wait, state
from v$session_wait
where sid in (22,23); 2 3 4
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ------------------------------ -------------------- ---------- --------------- -------------------
22 SQL*Net message from client Idle 0 256 WAITING
23 SQL*Net message from client Idle 0 237 WAITING
● 2. lock 확인
- session 1,2 에서 sequence 작업
[sess_1], [sess_2]
declare
v_value number;
begin
for idx in 1..100000 loop
select seq_1.nextval into v_value from dual;
end loop;
end;
/
- row cache lock wait event 발생
: 2개의 sequence
SYS@ora11g> 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
ADDRESS SADDR SID LOCK_MODE
-------- -------- ---------- ----------
3F4556B8 3F320BD8 23 5
SYS@ora11g> /
ADDRESS SADDR SID LOCK_MODE
-------- -------- ---------- ----------
3F4556B8 3F32388C 22 5- row cache lock 이 걸려있는 sid sql문 확인
어떤시퀀스를 사용하는지, 문제되는 시퀀스정보
select sql_text
from v$sql
where address = (select prev_sql_addr from v$session where sid=23);row cache lock 경합중에 sequence nocache 속성으로 인해 많이 발생한다.
- nextval 수행 할때 마다 dictionary 정보를 변경하기 위해서 ssx(shared sub exclusive) 모드를 획득해야함으로 이때 경합이 발생한다.
- 해결방법
: cache 속성으로 수정해야한다.alter sequence seq_1 cache 100; create sequence seq_1 cache 20;
- cache 크기가 작을 경우 enq : SQ - contention wait event 발생
-
library cache
hash 함수 인자값은 sql문,pl/sql문 -
buffer cache
hash 함수 인자값은 DBA(data block address), class(종류)
db buffer cache
- database 에서 읽어 들인 block을 메모리에 올려놓는 영역
- 물리적인 I/O 를 최소화 하기 위한 메모리 영역
- parse
- bind
3. execute
- library cache lock과 library cache pin shared 모드로 변환하고 sql문을 실행한다.
- 실행계획을 통해서 요청한 블록의 DBA(data block address) 와 Block class(블록 종류)을 인자값으로 하여 hash 함수를 이용해서 hash 값을 생성하고 해시 값에 해당하는 hash bucket을 찾는다.
- 현재 data buffer cache에 올라온 블록의 정보를 shared pool의 data buffer 에서 관리한다.
- hash 값에 해당하는 hash bucket을 검색하기 위해서는 latch를 획득해야 한다.
그런데 이미 누군가가 latch를 획득하고 있으면 나느 기다려야 한다.그때 발생한 이벤트 -> latch : cache buffers chains
- 현재 latch의 수
SYS@ora11g> select count(*) from v$latch_children
where name = 'cache buffers chains'; 2
COUNT(*)
----------
4096- 현재 latch의 수
SELECT a.ksppinm Parameter, b.ksppstvl Session_Value, c.ksppstvl Instance_Value
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm = '_db_block_hash_latches';
PARAMETER SESSION_VALUE INSTANCE_VALUE
-------------------- -------------------- --------------------
_db_block_hash_latch 4096 4096
es
- 현재 hash bucket의 수
SELECT a.ksppinm Parameter, b.ksppstvl Session_Value, c.ksppstvl Instance_Value
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm = '_db_block_hash_buckets';
PARAMETER SESSION_VALUE INSTANCE_VALUE
-------------------- -------------------- --------------------
_db_block_hash_bucke 131072 131072
ts
- 하나의 latch 당 담당하는 hash bucket 수는 32개이다.
- hash bucket을 보호하는 cache buffers chains latch를 획득해야 한다.
읽기 작업을 할때 : shared mode (같이 볼수 있으므로)
쓰기 작업을 할때 : exclusive mode (나혼자만 써야하므로)이과정에서 경합이 발생 -> latch : cache buffers chains
- 논리적인 I/O 발생
latch 를 잡고 hash bucket 에 블록에 해당하는 버퍼 헤더가 존재하면 (DBA+class) 해당 블록이 버퍼 캐시에 올라와 있는 상태일때 논리적인 I/O 발생
- 물리적 I/O 발생
latch 를 잡고 hash bucket에 블록에 해당하는 버퍼 헤더가 없으면 물리적 I/O 발생
2024/02/06
wait event
- latch : cache buffers chains
buffer busy wait
: 동시에 select , DML
shared mode, exclusive mod 동시에 불가
논리적 블록은 결국 os 블록으로 만들어졌으므로
page 안에
서로 다른 row들이라도 같은 블록에 있으면 동시에 DML 불가능
-> buffer busy wait
db_cache_size
non-standard block 크기를 설정하면
관리해야 할 파라미터가 하나라도 적으므로 현장에서 사용한다
- 관리할것
: db_nk_cache_size, log buffer size 관리 등등..
이럴때 buffer busy wait이 많이 발생하면
pct_free의 크기를 크게 해준다- pct_free
: 1. update 증가분을 위해 2. tansaction slot 생성 공간 확보
하지만, 메모리 사용량이 증가할수있다
따라서, 오라클권고 사항은 standard block 설정하는것이다.
- Row Migration
: Row 의 update 가 발생했을 경우 해당 block 에 빈 공간이 부족해서 다른 block 으로 이사를 가는 경우- row migration 을 줄이려면 PCTFREE 값을 많이 주거나 테이블을 재생성 하는 작업을 해서 경우를 줄여야함 - Row Chaining
: 데이터가 너무 길어서 하나의 블록에 모두 기록할 수 없는 경우 다른 블록과 연결해서 데이터를 저장하는 경우- block 의 크기보다 큰 데이터가 들어와서 생기는 경우가 많으므로 block의 크기를 크게 생성하는것이 좋다.
■ buffer busy wait
같은 블록 안에 서로 다른 행을 조회하는데(shared) 다른 유저가 변경하는(exclusive) 경우 동시에 이루어져서는 안된다.
같은 블록 안에 있는 서로 다른 행을 변경하려고 하는데 (exclusive) 다른 유저가 변경하는 (exclusive) 경우 동시에 이루어져서는 안된다.
이런 상황이 buffer busy wait 이 발생한다.
-
각 사용자는 행을 변경하기 위해서 tx lock (row level lock)을 exclusive 하게 획득했다고 하더라도 현재 자신만 블록안에 있는 행을 변경해야하는것을 보장 받아야한다.
-
이때 블록 헤더에 exclusive lock을 설정해야 한다. 이 lock이 block lock 개념이다.
▶ data buffer cache의 select,DML
● 1. 변경하고자 하는 행에 해당하는 블록이 data buffer cache에 있는지 실행 계획을 통해서 찾아 간다.
- latch : cache buffers chains
● 2. block을 찾아서 block header에 block lock(shared[select], exclusive[DML])을 획득하게 되면 latch 해제한다.
● 3.
- select를 수행했을 경우 active set 결과를 만들면 blcok lock 해제
- DML 작업이 끝나면 block lock 해제
● 4. tx(transaction) lock 을 획득하고 dml 작업이 끝나면 block lock 해제
tx lock 을 획득 못하면 wait event(enq: TX - row lock contention) 발생하게 되고 block lock 해제 된다.
waiting 단계에서 해제 되면 다시 block header에 exclusive 을 획득하고 작업을 수행한다.
select * from hr.emp where emp_id = 100; 실행
- parse 단계에서
semantic,권한 체크할때 data dictionary cache를 보는 이유
- access할때마다 디스크의
system tablespace 안에
user$, tab$, col$, obj$, ind$(EMP_idx의 root block의 DBA정보가지고 있다.)정보들을 access할수 없으므로
디스크에 존재하는 데이터를 읽어 저장하는 메모리 공간인
data buffer cache에 올리고
또, data buffer cache에서 항상 access 하면
항상 latch를 잡아야 하므로 성능이 안좋아진다.
따라서 data dictionary cache에 올려놓는다.
library cache에 같은 실행계획이 있다면
-
bind 스킵
-
execute
-
hash 함수를 던졌을때
hash bucket의 block에
lco도 shared mode -
data dictionary cache에
ind$(EMP_idx의 root block의 DBA정보가지고 있다.)가 있으므로
emp_idx테이블 access하여 root에는 branch정보, branch에는 leaf정보를 가지고 있으므로 찾을수 있다
이 인자값(leaf블록의 DBA+class)을 가지고
buffer cache hash 에 넣어서 active set 결과를 가져온다.
▶ 물리적 I/O 발생
- latch를 잡고 hash bucket 에 블록에 해당하는 버퍼 헤더가 있는지를 조회해 보고 없으면 I/O 발생
■ LRU(Least Recently Used) LIST
- 가장 최근에 사용되거나 사용하지 않은 버퍼들의 리스트
- free buffer, 사용중이거나 사용된 버퍼, 아직 LRUW 리스트로 옮겨지지 않은 dirty buffer
- 메인리스트 : 사용된 버퍼들의 리스트 hot region (자주 사용된 buffer(자주 access한 buffer)), cold regin
- 보조리스트 : free buffer list, 미사용된 buffer, dbwr에 의해 기록된 버퍼들의 리스트
■ LRUW(Least Recently Used Write) LIST
- 아직 디스크로 기록되지 않은 변경된 buffer(dirty buffer) 들을 관리하는 리스트
- dirty list, write list 라고도 부른다.
- 메인리스트 : 변경된 buffer 들의 리스트
- 보조리스트 : 현재 dbwr에 의해 기록중인 버퍼들의 리스트
물리적 I/O가 발생하면 latch를 잡고 data buffer cache의 free buffer를 찾는다.
- 이때 latch : cache buffers lru chain wait event가 발생한다.
select count(*) from v$latch_children where name = 'cache buffers lru chain';check point 발생할때
- db 정상종료
- begin 백업
- alter system checkpoint;
- tablespace 레벨로 offline
- temporary -> 가능한것만 checkpoint
- immediate -> 체크포인트 하지않고 offline
- log switch
- drop table
- truncate
- parallel 처리할때
- free buffer를 못찾았을때
● 1.
LRU 리스트의 보조 리스트에서 free buffer를 찾는다.
● 2.
만약 보조 리스트의 버퍼가 모두 사용된 경우에는 메인 리스트의 cold region 제일 뒤에서 부터 free buffer 를 찾는다
찾는 도중에 touch count 1 이하인 버퍼가 프리버퍼로 사용한다.
만약에 touch count 2 이상인 buffer 를 만나면 hot region 앞으로 (head) 옮기고 해당 버퍼의 touch count 0 으로 초기화 시킨다.
프리 버퍼를 찾는 도중 dirty buffer가 발견되면 LRUW 리스트로 이동한다.
block을 access 한후 3초안에 access 하면 touch count 증가한다.
● 3.
프리 버퍼를 찾게 되면 해당 버퍼에 대해 buffer lock읉 exclusive mode 로 획득하고 데이터 파일의 블록을 해당 버퍼로 읽어 들인다.
물리적으로 읽어 들이고 있는 중에 다른 세션에서 같은 블록을 조회하려고 하는 경우 기다리는 작업이 발생한다.
read by other session wait event 발생
● 4.
만약에 LRU 리스트에서 free buffer를 40% scan 을 하고도 프리버퍼를 찾지 못하면 서버프로세스는 DBWR에게 dirty buffer를 파일에 기록하고 free buffer를 확보할 것을 요청한다.
DBWR free buffer 가 확보 될때까지 free buffer wait event가 발생한다.
select a.bp_blksz,
c.child#,
a.bp_name,
c.gets,
c.MISSES,
c.SLEEPS
from x$kcbwbpd a, x$kcbwds b, v$latch_children c
where b.set_id between a.bp_lo_sid and a.bp_hi_sid
and c.ADDR = b.set_latch
order by 2;
SELECT a.ksppinm Parameter, b.ksppstvl Session_Value, c.ksppstvl Instance_Value
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm = '_db_block_max_scan_pct'; - touch count 2이상인 buffer를 만나면 hot region 앞으로 (head) 옮기고
SELECT a.ksppinm Parameter, b.ksppstvl Session_Value, c.ksppstvl Instance_Value
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm = '_db_aging_hot_criteria';■ 대기 이벤트 시나리오
HR@ora11g> create table cbc_latch(id number, name char(100));
HR@ora11g> insert into cbc_latch(id,name) select level, 'oracle'||level
from dual
connect by level<=500000
order by dbms_random.value;
HR@ora11g> commit;
HR@ora11g> create index cbc_latch_idx on cbc_latch(id);
HR@ora11g> select index_name, uniqueness from user_indexes where index_name='CBC_LATCH_IDX';
[sess_1]
HR@ora11g> exec dbms_application_info.set_client_info('sess_1');
PL/SQL procedure successfully completed.
[sess_2]
HR@ora11g> exec dbms_application_info.set_client_info('sess_2');
PL/SQL procedure successfully completed.
SYS@ora11g> select client_info, sid
from v$session where client_info in ('sess_1', 'sess_2'); 2
CLIENT_INFO SID
---------------------------------------------------------------- ----------
sess_1 23
sess_2 189
select sid, event, wait_class, wait_time,
seconds_in_wait, state
from v$session_wait
where sid in (23,189);
select sid, event, total_waits, time_waited
from v$session_event
where sid in (23,189);
SYS@ora11g> create tablespace flm_tbs
datafile '/u01/app/oracle/oradata/ora11g/flm_tbs01.dbf' size 100m autoextend on
extent management local uniform size 1m
segment space management manual;
Tablespace created.
HR@ora11g> create table flm_t (id char(1000)) storage(freelists 1) tablespace flm_tbs;
Table created.
----------------
sess_1
----------------
HR@ora11g> exec dbms_application_info.set_client_info('sess_1')
PL/SQL procedure successfully completed.
begin
for i in 1..10000 loop
insert into flm_t values(' ');
end loop;
commit;
end;
/
-------------
sess_2
-------------
HR@ora11g> exec dbms_application_info.set_client_info('sess_2')
PL/SQL procedure successfully completed.
begin
for i in 1..10000 loop
insert into flm_t values(' ');
end loop;
commit;
end;
/
SYS@ora11g> select client_info, sid
from v$session where client_info in ('sess_1', 'sess_2'); 2
CLIENT_INFO SID
---------------------------------------------------------------- ----------
sess_2 9
sess_1 189
- 확인
SYS@ora11g> select sid, event, wait_class, wait_time,
seconds_in_wait, state
from v$session_wait
where sid in (9,189); 2 3 4
SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ------------------------------ -------------------- ---------- --------------- -------------------
9 buffer busy waits Concurrency 0 1 WAITING
189 log file switch (checkpoint in Configuration 0 1 WAITING
complete)
SYS@ora11g> select sid, event, total_waits, time_waited
from v$session_event
where sid in (9,189); 2 3
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ------------------------------ ----------- -----------
9 Disk file operations I/O 21 10
9 Data file init write 18 0
9 control file sequential read 174 0
9 control file parallel write 42 7
9 latch: cache buffers chains 1 0
9 buffer busy waits 25386 1726
9 log buffer space 56 166
9 log file switch completion 2 3
9 log file sync 1 2
9 db file sequential read 15624 8
9 db file single write 6 1
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ------------------------------ ----------- -----------
9 flashback log file sync 7 19
9 enq: HW - contention 2225 171
9 cursor: pin S 7 1
9 cursor: pin S wait on X 1 0
9 SQL*Net message to client 14 0
9 SQL*Net message from client 14 42904
9 events in waitclass Other 19 28
189 Disk file operations I/O 27 6
189 Data file init write 24 0
189 control file sequential read 232 0
189 control file parallel write 56 8
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ------------------------------ ----------- -----------
189 latch: cache buffers chains 1 0
189 buffer busy waits 26199 253
189 log buffer space 49 146
189 log file switch (checkpoint in 5 1527
complete)
189 log file switch completion 7 37
189 log file sync 1 32
189 db file sequential read 13743 8
189 db file single write 8 1
189 flashback log file sync 9 20
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ------------------------------ ----------- -----------
189 enq: HW - contention 2229 175
189 latch: row cache objects 1 0
189 cursor: pin S 4 1
189 SQL*Net message to client 14 0
189 SQL*Net message from client 14 44766
189 events in waitclass Other 16 25
37 rows selected.
enq: HW - contention
-> 이것때문에 동시 insert 작업이 wait event걸린다.
DB 공부를 하고 있는 사람입니다. 글을 읽어주셔서 감사하고 더 좋은 글을 쓰기 위해 노력하겠습니다 :)