#) 한번에 읽을 수 있는 블록의 개수 확인
show parameter db_file_multiblock_read_count
- direct path read: 병렬처리할 경우 발생하는 대기 이벤트, 22 번 올렸다.
■ Serial direct read
1. full scan 및 multi block i/o : 데이터베이스에서 전체 테이블 또는 인덱스를 스캔할 때, 여러 블록을 한 번에 읽는 Multi Block I/O가 발생.
2. Data buffer cache 부담 : 데이터 버퍼 캐시에 부하 가능성
3. Latch: cache buffers lru chain 이벤트 : 데이터 파일의 블록을 메모리로 읽어올 때 `cache buffers lru chain` 이벤트에 따른 Latch를 사용
4. LRU 리스트의 COLD 영역에서 블록 읽기 : Latch를 획득하고 LRU 리스트의 끝에서 블록을 읽어온다.
- 여기서 COLD 영역은 오랫동안 사용되지 않은 블록이 위치한 부분을 나타낸다.
5. Age Out 문제 : 읽어들인 블록이 메모리에서 밀려나게 되면(Age Out), 해당 블록이 다시 필요한 경우 물리적 I/O가 발생 할 수 있다.
6. Direct Read 기능: 이러한 문제를 해결하기 위해, "serial direct read" 방식을 제안한다.
- 이는 데이터를 데이터 버퍼 캐시에 올리지 않고, 대신 서버 프로세스의 PGA(Private Global Area) 영역에 있는 커서(cursor)에서 바로 데이터 파일에서 읽어오는 방식
7. Parallel 처리와의 연관: 원래는 병렬 처리 시에 발생하는 기능이었지만, Oracle Database 11g부터는 병렬 처리 힌트를 사용하지 않아도 "serial direct read" 방식이 구현되었다.
※ serial direct read의 총 정리.
풀 스캔 시 발생하는 블록 읽기 작업에서 데이터 버퍼 캐시 부하와 관련된 문제를 해결하기 위해 도입된 기능으로, 데이터를 직접 읽어와서 메모리에 저장하는 것이 아니라 PGA의 커서를 통해 바로 읽어오는 방식.
SELECT a.ksppinm Parameter, b.ksppstvl session_value, c.ksppstvl lnstance_value
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm in ('_small_table_threshold', '_serial_direct_read');
#1) _small_table_threshold : 기준치
<hr sess>
exec dbms_application_info.set_client_info('sess_1')
select count(*) from ind_random where owner = 'SYS';
<sys sess>
#) sess sid 확인
select client_info, sid from v$session where client_info = 'sess_1';
#) 모니터링 쿼리문
- 대기 이벤트 확인
select sid, event, wait_class, wait_time, seconds_in_wait, state
from v$session_wait
where sid = 28;
select sid, event, total_waits, time_waited from v$session_event where sid = 28;
alter system flush buffer_cache;
alter system flush buffer_cache;
#) hr 계정 재접속
exec dbms_application_info.set_client_info('sess_1')
select count(*) from ind_random where owner = 'SYS';
<sys sess>
select client_info, sid from v$session where client_info = 'sess_1';
#) 대기 이벤트 확인
select sid, event, total_waits, time_waited from v$session_event where sid = 28; SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------------------------------- ----------- -----------
28 Disk file operations I/O 3 0
28 db file sequential read 11 6
28 direct path read 22 0
28 SQL*Net message to client 15 0
28 SQL*Net message from client 14 3332
<new sess_hr>
exec dbms_application_info.set_client_info('sess_2')
# direct path read 방식을 안하겠다는 이벤트
alter session set events '10949 trace name context forever, level 1';
select count(*) from ind_random where owner = 'SYS';
<sys sess>
select client_info, sid from v$session where client_info = 'sess_2';CLIENT_INFO SID
------------------------------ ----------
sess_2 151
#) db file scattered read
- (해석)멀티블록 i/o 발생 -> data buffer cache 건드리고 있다.
select sid, event, total_waits, time_waited from v$session_event where sid = 151; SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------------------------------- ----------- -----------
151 Disk file operations I/O 1 0
151 log file sync 1 2
151 db file scattered read 23 0
151 SQL*Net message to client 16 0
151 SQL*Net message from client 15 1937#) 이벤트 취소
alter session set events '10949 trace name context off';alter system flush buffer_cache;
alter system flush buffer_cache;
<시나리오 _trace 및 대기 이벤트 생성>
<hr sess>
#) trace 하기
alter session set events '10949 trace name context forever, level 1';
alter session set tracefile_identifier = 'hr';
alter session set events '10046 trace name context forever, level 8';
select count(*) from hr.ind_random where owner = 'SYS';
alter session set events '10046 trace name context off';
alter session set events '10949 trace name context off';
[oracle1@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle1@oracle trace]$ ls *hr.trc
ora11g_ora_2305_hr.trc
[oracle1@oracle trace]$ tkprof ora11g_ora_2305_hr.trc ex03.txt sys=no
[oracle1@oracle trace]$ vi ex03.txt
...
********************************************************************************
SQL ID: 49g6ju0tm1s80 Plan Hash: 3056395443
select count(*)
from
hr.ind_random where owner = 'SYS'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 2 0.00 0.00 986 990 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.02 986 990 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=990 pr=986 pw=0 time=8412 us)
29855 29855 29855 TABLE ACCESS FULL IND_RANDOM (cr=990 pr=986 pw=0 time=3592 us cost=276 size=377366 card=22198)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.00 0.00
db file scattered read 23 0.00 0.00
SQL*Net message from client 2 4.10 4.10
********************************************************************************
<sys sess>
show parameter db_file_multiblock_read_countNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 126select extent_id, block_id, bytes, blocks
from dba_extents
where owner = 'HR'
and segment_name = 'IND_RANDOM'; EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 19240 65536 8
1 19248 65536 8
2 19256 65536 8
3 19264 65536 8
4 19272 65536 8
5 19280 65536 8
6 19288 65536 8
7 19296 65536 8
8 19304 65536 8
9 19312 65536 8
10 19320 65536 8
11 19328 65536 8
12 19336 65536 8
13 19344 65536 8
14 19352 65536 8
15 19360 65536 8
16 19456 1048576 128 /* => 2번씩의 i/o 발생 */
17 19584 1048576 128
18 19712 1048576 128
19 19840 1048576 128
20 19968 1048576 128
21 20096 1048576 128
22 20224 1048576 128
<시나리오 재진행>
alter system flush buffer_cache;
alter system flush buffer_cache;
<hr sess 재접속>
alter session set db_file_multiblock_read_count = 128;
alter session set events '10949 trace name context forever, level 1';
alter session set tracefile_identifier = 'ran';
alter session set events '10046 trace name context forever, level 8';
select count(*) from hr.ind_random where owner = 'SYS';
alter session set events '10046 trace name context off';
alter session set events '10949 trace name context off';
[oracle1@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle1@oracle trace]$ ls *ran.trc
ora11g_ora_3088_ran.trc
[oracle1@oracle trace]$ tkprof ora11g_ora_3088_ran.trc ex01.txt
[oracle1@oracle trace]$ vi ex01.txt
...
index full scan => 싱글 블록
<시나리오 재진행_trace>
<sys sess>
alter system flush buffer_cache;
alter system flush buffer_cache;
<hr sess 재접속>
#) index 생성
create index hr.ind_random_ix on hr.ind_random(owner);
alter session set tracefile_identifier = 'hr';
alter session set events '10046 trace name context forever, level 8';
select count(*) from hr.ind_random where owner = 'SYS';
alter session set events '10046 trace name context off';
#) trace 확인
[oracle1@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle1@oracle trace]$ ls *hr.trc
ora11g_ora_3619_hr.trc
[oracle1@oracle trace]$ tkprof ora11g_ora_3964_hr.trc ex02.txt
[oracle1@oracle trace]$ vi ex02.txt
...
<시나리오 재진행_trace>
<sys sess>
alter system flush buffer_cache;
alter system flush buffer_cache;
<hr sess 재접속>
alter session set tracefile_identifier = 'hr';
alter session set events '10046 trace name context forever, level 8';
select * from hr.ind_random where owner = 'SYS';
alter session set events '10046 trace name context off';
[oracle1@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle1@oracle trace]$ ls *hr.trc
ora11g_ora_4043_hr.trc
[oracle1@oracle trace]$ tkprof ora11g_ora_4043_hr.trc ex03.txt
[oracle1@oracle trace]$ vi ex03.txt
...
<시나리오 재진행_trace>
<sys sess>
alter system flush buffer_cache;
alter system flush buffer_cache;
<hr sess 재접속>
alter session set tracefile_identifier = 'hr';
alter session set events '10046 trace name context forever, level 8';
select /*+ index(i ind_random_ix)*/ * from hr.ind_random i where owner = 'SYS';
alter session set events '10046 trace name context off';
<OS 에서 확인>
[oracle1@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle1@oracle trace]$ ls *hr.trc
ora11g_ora_4220_hr.trc
[oracle1@oracle trace]$ tkprof ora11g_ora_4220_hr.trc ex04.txt
[oracle1@oracle trace]$ vi ex04.txt
...
<시나리오 재진행_trace>
<sys sess>
alter system flush buffer_cache;
alter system flush buffer_cache;
<hr sess 재접속>
alter session set tracefile_identifier = 'hr';
alter session set events '10046 trace name context forever, level 8';
select /*+ index(i ind_random_ix)*/ * from hr.ind_random i where owner = 'SYS' and object_name = 'ALL_OBJECTS';
alter session set events '10046 trace name context off';
[oracle1@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle1@oracle trace]$ ls *hr.trc
ora11g_ora_5653_hr.trc
[oracle1@oracle trace]$ tkprof ora11g_ora_5653_hr.trc ex05.txt
[oracle1@oracle trace]$ vi ex05.txt
...
=> 쓸데없는 i/o 발생 -> random i/o 발생 | buffer pinning 발생했으나 너무 많은 i/o 발생 => 조합 인덱스 실행
#) 위에 대한 해결방안 -> 조합인덱스
<시나리오 재진행_trace>
<hr sess>
drop index ind_random_ix;
create index hr.ind_random_ix on hr.ind_random(owner, object_name);
<sys sess>
alter system flush buffer_cache;
alter system flush buffer_cache;
<hr sess 재접속>
alter session set tracefile_identifier = 'hr';
alter session set events '10046 trace name context forever, level 8';
select /*+ index(i ind_random_ix)*/ * from hr.ind_random i where owner = 'SYS' and object_name = 'ALL_OBJECTS';
alter session set events '10046 trace name context off';
[oracle1@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle1@oracle trace]$ ls *hr.trc
ora11g_ora_5653_hr.trc
[oracle1@oracle trace]$ tkprof ora11g_ora_5896_hr.trc ex06.txt
[oracle1@oracle trace]$ vi ex06.txt
...
※ 설명)
1. db file sequential read
1) 발생 시점: index range scan, index full scan
2) I/O 방식: Single Block I/O로, 한 번의 I/O 호출에 하나의 데이터 블록만 읽어온다.
3) 발생 시점 기준: 주로 인덱스를 사용하여 원하는 행을 찾을 때 발생한다. 인덱스가 있는 열을 검색하는 쿼리에서 발생할 가능성이 높다.
4) 주로 인덱스를 통해 테이블을 액세스할 때 발생하며, 인덱스의 범위를 스캔할 때 사용된다.
2. db file scattered read
1) 발생시점: full table scan, index fast full scan
2) I/O방식: Multi-Block I/O로, 한 번의 I/O 호출에 인접한 블록들을 메모리에 읽어온다.
3) 발생 시점 기준: `db_file_multiblock_read_count의 값에 의해 결정되며, 해당 값은 데이터베이스 설정에 의해 결정된다.4) 대량의 데이터를 검색할 때 사용되며, 한 번에 여러 블록을 읽어올 수 있다. `db_file_multiblock_read_count`를 조절하여 한 번에 읽을 수 있는 블록 수를 설정할 수 있다. 적절한 값을 유지하는 것이 성능에 도움이 된다.
'Data Base > SQL 튜닝' 카테고리의 다른 글
| row chaining, row migration (1) | 2024.02.17 |
|---|---|
| CLUSTERING FACTOR (0) | 2024.02.17 |
| v$sql_plan (0) | 2024.02.14 |
| auto trace _ set autotrace on, set autotrace on explain, set autotrace on statistics, set autotrace traceonly, set autotrace traceonly explain, set autotrace traceonly statistics (1) | 2024.02.14 |
| buffer pinning, SQL TRACE (1) | 2024.02.14 |