| # 요약 | |
| ■ Serial direct read ■ Cluster Factor |
|
■ Serial direct read
테이블이나 인덱스에 대한 full scan이 발생할 경우 multi block I/O가 발생한다.
이때 데이터 버퍼 캐시 영역의 부담을 줄 수 있다.
데이터 파일의 블록을 메모리로 읽어 오려면 latch : cache buffers LRU chain 이벤트가 발생할 수 있다.
latch를 잡고 LRU 리스트의 cold 영역 끝쪽에 블록을 읽어 들인다. 그런데 그곳에 있던 블록이 age out 될 수 있다.
그런데 age out 된 블록을 다른 세션에서 필요할 때는 다시 물리적 I/O가 발생할 수 있다는 문제점들이 있다.
그래서 full table scan이 발생하는 테이블의 블록들을 데이터 버퍼 캐시에 올리지 말고 서버프로세스 안에 있는
PGA 영역안에 cursor에 데이터파일에 바로 읽어오자라는 기능이다.
원래는 direct read 방식은 parallel 처리시에 발생하는 기능인데 11g부터 병렬처리 힌트를 사용하지 않았더라도
direct read 방식이 구현된다.
# 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 in ('_small_table_threshold','_serial_direct_read');
PARAMETER SESSION_VALUE INSTANCE_VALUE
------------------------------ -------------------- --------------------
_small_table_threshold 525 525
_serial_direct_read auto auto
풀 테이블 스캔이라고 무조건 다이렉트 리드로 가는건 아니다
_small_table_threshold 값이 기준 525 block 수보다 많으면 다이렉트로 커서로 읽어들인다.
HR@ora11g> exec dbms_application_info.set_client_info('sess_1')
PL/SQL procedure successfully completed.
HR@ora11g> select count(*) from ind_random where owner = 'SYS';
COUNT(*)
----------
29853
HR@ora11g> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 0bpvu28df5xzp, child number 0
-------------------------------------
select count(*) from ind_random where owner = 'SYS'
Plan hash value: 3056395443
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 275 (100)| |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| IND_RANDOM | 29744 | 493K| 275 (1)| 00:00:04 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
Note
-----
- dynamic sampling used for this statement (level=2)
SYS@ora11g> select client_info, sid from v$session where client_info = 'sess_1';
CLIENT_INFO SID
-------------------- -------
sess_1 180
SYS@ora11g> select sid, event, total_waits, time_waited from v$session_event where sid = 180;
SID EVENT TOTAL_WAITS TIME_WAITED
------- ----------------------------------- ----------- -----------
180 Disk file operations I/O 1 0
180 log file sync 1 0
180 direct path read 24 2
180 SQL*Net message to client 17 0
180 SQL*Net message from client 16 253132
db file sequential read : single block i/o 발생 -> 일부 데이터가 메모리에 있어서 블록을 하나씩 읽은 것
db file scattered read : multi block i/o 발생
>> 메모리를 지워서 풀테이블스캔을 깔끔히 해보자 -> 실무에서는 하지말자
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
HR> 재접속
HR@ora11g> exec dbms_application_info.set_client_info('sess_1')
PL/SQL procedure successfully completed.
HR@ora11g> select count(*) from ind_random where owner = 'SYS';
COUNT(*)
----------
29853
SYS@ora11g> select client_info, sid from v$session where client_info = 'sess_1';
CLIENT_INFO SID
-------------------- -------
sess_1 183
SYS@ora11g> select sid, event, total_waits, time_waited from v$session_event where sid = 183;
SID EVENT TOTAL_WAITS TIME_WAITED
------- ----------------------------------- ----------- -----------
183 Disk file operations I/O 3 0
183 log file sync 1 0
183 db file sequential read 13 0
183 direct path read 24 0
183 SQL*Net message to client 15 0
183 SQL*Net message from client 14 683
> 헤더블록 읽느라 sequential read가 발생할 수 있음
HR> 재접속
HR@ora11g> exec dbms_application_info.set_client_info('sess_2')
PL/SQL procedure successfully completed.
# direct path read 방식을 안하겠다는 이벤트 : 10949
HR@ora11g> alter session set events '10949 trace name context forever, level 1';
Session altered.
HR@ora11g> select count(*) from ind_random where owner = 'SYS';
COUNT(*)
----------
29853
SYS@ora11g> select client_info, sid from v$session where client_info = 'sess_2';
CLIENT_INFO SID
-------------------- -------
sess_2 183
SYS@ora11g> select sid, event, total_waits, time_waited from v$session_event where sid = 183;
SID EVENT TOTAL_WAITS TIME_WAITED
------- ----------------------------------- ----------- -----------
183 Disk file operations I/O 1 0
183 log file sync 1 1
183 db file scattered read 30 0
183 SQL*Net message to client 16 0
183 SQL*Net message from client 15 9646
# 이벤트 종료
HR@ora11g> alter session set events '10949 trace name context off';
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
HR@ora11g> alter session set events '10949 trace name context forever, level 1';
Session altered.
HR@ora11g> alter session set tracefile_identifier = 'hr';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
> level 8 : wait 이벤트까지 포함
HR@ora11g> select count(*) from ind_random where owner = 'SYS';
COUNT(*)
----------
29853
HR@ora11g> alter session set events '10046 trace name context off';
Session altered.
HR@ora11g> alter session set events '10949 trace name context off';
Session altered.
HR@ora11g> !
[oracle@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ls *hr.trc
ora11g_ora_2683_hr.trc
[oracle@oracle trace]$ tkprof ora11g_ora_2683_hr.trc hr2683.txt sys=no
[oracle@oracle trace]$ cat hr2683.txt
...
SQL ID: 0bpvu28df5xzp Plan Hash: 3056395443
select count(*)
from
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.00 0 0 0 0
Fetch 2 0.03 0.03 985 989 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.03 985 989 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=989 pr=985 pw=0 time=35509 us)
29853 29853 29853 TABLE ACCESS FULL IND_RANDOM (cr=989 pr=985 pw=0 time=5084 us cost=275 size=505648 card=29744)
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 30 0.00 0.02
SQL*Net message from client 2 2.11 2.12
********************************************************************************
SYS@ora11g> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
db_file_multiblock_read_count integer 81
SYS@ora11g> select extent_id, block_id, bytes, blocks from dba_extents where owner = 'HR' and segment_name = 'IND_RANDOM';
EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 152 65536 8 0번 extent의 블록ID 152, 64byte, block 8개
1 17864 65536 8
2 17880 65536 8
3 17888 65536 8
4 17896 65536 8
5 17904 65536 8
6 17912 65536 8
7 18944 65536 8
8 18952 65536 8
9 18960 65536 8
10 18968 65536 8
11 18976 65536 8
12 18984 65536 8
13 18992 65536 8
14 19000 65536 8
15 19008 65536 8 8개 블록 16번
16 19072 1048576 128 79개씩 2번 = 14번 => 30번
17 19200 1048576 128
18 19328 1048576 128
19 19456 1048576 128
20 19584 1048576 128
21 19712 1048576 128
22 19840 1048576 128
23 rows selected.
extent 결정? -> 테이블스페이스 생성 시 -> 부서별 구성, 테이블마다 사용량이 상이하여 쉽지않지만
기본 1M~2M 많이 구성
유니폼 사이즈 명시 안하면 자동
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
HR@ora11g> alter session set db_file_multiblock_read_count = 128;
Session altered.
HR@ora11g> alter session set events '10949 trace name context forever, level 1';
Session altered.
HR@ora11g> alter session set tracefile_identifier = 'hr';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
HR@ora11g> select count(*) from ind_random where owner = 'SYS';
COUNT(*)
----------
29853
HR@ora11g> alter session set events '10046 trace name context off';
Session altered.
HR@ora11g> alter session set events '10949 trace name context off';
Session altered.
HR@ora11g> !
[oracle@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ls *hr.trc
ora11g_ora_2496_hr.trc
[oracle@oracle trace]$ tkprof ora11g_ora_2496_hr.trc hr2496.txt sys=no
[oracle@oracle trace]$ cat hr2496.txt
...
SQL ID: 0bpvu28df5xzp Plan Hash: 3056395443
select count(*)
from
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.00 0 0 0 0
Fetch 2 0.03 0.04 985 989 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.04 985 989 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=989 pr=985 pw=0 time=41393 us)
29853 29853 29853 TABLE ACCESS FULL IND_RANDOM (cr=989 pr=985 pw=0 time=4336 us cost=177 size=505648 card=29744)
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.03
SQL*Net message from client 2 3.26 3.26
********************************************************************************
- cr=989 pr=985 -> 4번은 메모리
> db file scattered read -> full scan 한다는 거 -> 다른 업무에 영향을 줄 수 있다
> 자주사용하는 칼럼인데 인덱스가 없네?
HR@ora11g> create index hr.ind_random_ix on hr.ind_random(owner);
Index created.
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
HR@ora11g> alter session set tracefile_identifier = 'hr';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
HR@ora11g> select count(*) from ind_random where owner = 'SYS';
COUNT(*)
----------
29853
HR@ora11g> alter session set events '10046 trace name context off';
Session altered.
HR@ora11g> !
[oracle@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ls *hr.trc
ora11g_ora_3201_hr.trc
[oracle@oracle trace]$ tkprof ora11g_ora_3201_hr.trc hr3201.txt sys=no
[oracle@oracle trace]$ cat hr3201.txt
...
SQL ID: 0bpvu28df5xzp Plan Hash: 2556419480
select count(*)
from
ind_random where owner = 'SYS'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 275 73 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 154 166 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.02 429 239 0 1
Misses in library cache during parse: 1
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=166 pr=154 pw=0 time=8777 us)
29853 29853 29853 INDEX FAST FULL SCAN IND_RANDOM_IX (cr=166 pr=154 pw=0 time=7175 us cost=48 size=505648 card=29744)(object id 88758)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 5 0.00 0.00
SQL*Net message to client 2 0.00 0.00
db file scattered read 16 0.00 0.00
SQL*Net message from client 2 3.58 3.58
********************************************************************************
- count(*) 건수만 세는거라 INDEX FAST FULL SCAN - db file scattered read
extent의 헤드블럭~HWM까지 읽는데 scattered read까진 사용 안함
- index full scan = 싱글
- full table scan = 멀티
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
HR@ora11g> alter session set tracefile_identifier = 'hr';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
HR@ora11g> select * from ind_random where owner = 'SYS';
... 많다
HR@ora11g> alter session set events '10046 trace name context off';
Session altered.
HR@ora11g> !
[oracle@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ll *hr.trc
ora11g_ora_5350_hr.trc
[oracle@oracle trace]$ tkprof ora11g_ora_5350_hr.trc hr5350.txt sys=no
[oracle@oracle trace]$ cat hr5350.txt
...
SQL ID: g4wmy06ts7k0j Plan Hash: 780424712
select *
from
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.00 0 0 0 0
Fetch 1992 0.03 0.03 985 2946 0 29853
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1994 0.03 0.03 985 2946 0 29853
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
---------- ---------- ---------- ---------------------------------------------------
29853 29853 29853 TABLE ACCESS FULL IND_RANDOM (cr=2946 pr=985 pw=0 time=7473 us cost=275 size=3011540 card=30730)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1992 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.00 0.00
direct path read 24 0.00 0.00
SQL*Net message from client 1992 1.75 2.48
********************************************************************************
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
HR@ora11g> alter session set tracefile_identifier = 'hr';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
HR@ora11g> select /*+ index(i ind_random_ix) */ * from ind_random i where owner = 'SYS';
...
HR@ora11g> alter session set events '10046 trace name context off';
Session altered.
HR@ora11g> !
[oracle@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ll *hr.trc
ora11g_ora_5556_hr.trc
[oracle@oracle trace]$ tkprof ora11g_ora_5556_hr.trc hr5556.txt sys=no
[oracle@oracle trace]$ cat hr5556.txt
...
SQL ID: cyrh7t4010y3x Plan Hash: 334209613
select /*+ index(i ind_random_ix) */ *
from
ind_random i 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.00 0 0 0 0
Fetch 1992 0.06 0.07 1081 4960 0 29853
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1994 0.06 0.07 1081 4960 0 29853
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
---------- ---------- ---------- ---------------------------------------------------
29853 29853 29853 TABLE ACCESS BY INDEX ROWID IND_RANDOM (cr=4960 pr=1081 pw=0 time=168451 us cost=2046 size=3011540 card=30730)
29853 29853 29853 INDEX RANGE SCAN IND_RANDOM_IX (cr=2051 pr=73 pw=0 time=128863 us cost=72 size=0 card=30730)(object id 88758)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1992 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 135 0.00 0.01
SQL*Net message from client 1992 1.54 2.37
********************************************************************************
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
HR@ora11g> alter session set tracefile_identifier = 'hr';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
HR@ora11g> select /*+ index(i ind_random_ix) */ * from ind_random i
where owner = 'SYS' and object_name = 'ALL_OBJECTS';
> 1건
HR@ora11g> alter session set events '10046 trace name context off';
Session altered.
HR@ora11g> !
[oracle@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ll *hr.trc
ora11g_ora_5913_hr.trc
[oracle@oracle trace]$ tkprof ora11g_ora_5913_hr.trc hr5913.txt sys=no
[oracle@oracle trace]$ cat hr5913.txt
...
select /*+ index(i ind_random_ix) */ * from ind_random i
where owner = 'SYS' and object_name = 'ALL_OBJECTS'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 15 55 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.02 1081 1050 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.02 1096 1105 0 1
Misses in library cache during parse: 1
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 TABLE ACCESS BY INDEX ROWID IND_RANDOM (cr=1050 pr=1081 pw=0 time=18495 us cost=2046 size=98 card=1)
29853 29853 29853 INDEX RANGE SCAN IND_RANDOM_IX (cr=65 pr=73 pw=0 time=3606 us cost=72 size=0 card=30730)(object id 88758)
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 135 0.00 0.01
SQL*Net message from client 2 3.65 3.65
********************************************************************************
오너 칼럼 인덱스 ->
오브젝트 네임 칼럼 인덱스X -> 필터
데이터는 29853행, 결과 1건
버퍼피닝 덕분에 io가 줄었지만 cr=1050 pr=1081 비효율 = 랜덤 io 문제
=> 조합 인덱스 활용하자
HR@ora11g> drop index ind_random_ix;
Index dropped.
HR@ora11g> create index ind_random_ix on hr.ind_random(owner, object_name);
Index created.
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
SYS@ora11g> alter system flush buffer_cache;
HR@ora11g> alter session set tracefile_identifier = 'hr';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
HR@ora11g> select /*+ index(i ind_random_ix) */ * from ind_random i
where owner = 'SYS' and object_name = 'ALL_OBJECTS';
> 1건
HR@ora11g> alter session set events '10046 trace name context off';
Session altered.
HR@ora11g> !
[oracle@oracle ~]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ll *hr.trc
ora11g_ora_6167_hr.trc
[oracle@oracle trace]$ tkprof ora11g_ora_6167_hr.trc hr6167.txt sys=no
[oracle@oracle trace]$ cat hr6167.txt
...
select /*+ index(i ind_random_ix) */ * from ind_random i
where owner = 'SYS' and object_name = 'ALL_OBJECTS'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 3 39 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 18 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 21 44 0 1
Misses in library cache during parse: 1
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 TABLE ACCESS BY INDEX ROWID IND_RANDOM (cr=5 pr=18 pw=0 time=259 us cost=4 size=98 card=1)
1 1 1 INDEX RANGE SCAN IND_RANDOM_IX (cr=4 pr=10 pw=0 time=196 us cost=3 size=0 card=1)(object id 88772)
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 scattered read 2 0.00 0.00
db file sequential read 2 0.00 0.00
SQL*Net message from client 2 2.60 2.60
********************************************************************************
# db file sequential read
- single block i/o 방식으로 i/o 요청시에 발생할 수 있는 이벤트
- single block i/o : 한번의 i/o call에 하나의 데이터 블록만 읽어 메모리에 적재한다.
- 인덱스를 통해 테이블을 액세스할 때 발생한다.
- index range scan, index full read
# db file scattered read
- multi block i/o 방식으로 i/o요청 시에 발생할 수 있는 이벤트
- milti block i/o : 한번의 i/o call에 인접한 블록들을 같이 메모리를 읽어 들안다.
- full table ttsan, index fast full scan
- db_file_multiblock_read_count = 128
multi block i/o 시 한번에 읽을 수 있는 블록의 수 128개이고
블록의 사이즈가 8k 기준으로 1,048,576(1M)씩 읽어 들인다.
■ Cluster Factor
- 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여 있는 정도
- clustering factor가 좋은 컬럼에 인덱스를 사용하면 i/o를 줄일 수 있다.
buffer pinning이 수행되기 때문에 latch 점유 시간이 없을 수 있다.
- clustering factor 계산
1. count 변수를 선언
2. leaf 블록을 처음부터 끝까지 스캔하면서 rowid로 부터 block 번호를 찾는다.
3. 찾은 블록 주소가 바로 직전의 블록주소와 다를 때 마다 count 변수를 1증가 시킨다.
4. 이 작업을 끝까지 수행하면 count 변수에 있는 값을 clustering_factor 인덱스 통계정보에 저장한다.
1 #a -> 1번
2 #a -> 이전값과 동일 패스 -> buffer pinning 발생
3 #a -> 이전값과 동일 패스
4 #a -> 이전값과 동일 패스
5 #b -> 2번
6 #b -> 이전값과 동일 패스
=> I/O 2번 발생
1 #a -> 1번
2 #b -> 2번, 이전값과 다름
3 #a -> 3번, 이전값과 다름
4 #b -> 4번, 이전값과 다름
5 #a -> 5번, 이전값과 다름
6 #b -> 6번, 이전값과 다름
=> I/O 6번 발생
HR@ora11g> create table c_table
nologging
as select * from all_objects
order by object_id;
- nologging : 리두가 생기지 않도록
HR@ora11g> create index c_obj_idx on c_table(object_id);
Index created.
HR@ora11g> create index c_obj_name_idx on c_table(object_name);
Index created.
HR@ora11g> select num_rows, blocks, avg_row_len from user_tables where table_name = 'C_TABLE';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
# 통계 수집
HR@ora11g> execute dbms_stats.gather_table_stats('hr','c_table')
PL/SQL procedure successfully completed.
HR@ora11g> select num_rows, blocks, avg_row_len from user_tables where table_name = 'C_TABLE';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
68164 1009 98
AVG_ROW_LEN 1행 평균 바이트
HR@ora11g> select index_name, blevel, leaf_blocks, clustering_factor
from user_indexes where table_name = 'C_TABLE';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
C_OBJ_IDX 1 152 985
C_OBJ_NAME_IDX 2 352 61801
BLEVEL : 루트~리프까지 사용하는 I/O,
숫자가 크면 리빌드 필요 = 인덱스 재구성
1 : 루트 - 리프
2 : 루트 - 브런치 - 리프
LEAF_BLOCKS : 리프 블럭 수
CLUSTERING_FACTOR 좋지않다 = row 행 수 만큼 i/o 사용한다는 것
HR@ora11g> select /*+ gather_plan_statistics index(c c_obj_idx) */ count(*) from c_table c
where object_id >= 0 and object_name >= ' ';
COUNT(*)
----------
68164
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 2rj9rap0290dr, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(c c_obj_idx) */ count(*) from
c_table c where object_id >= 0 and object_name >= ' '
Plan hash value: 1213134236
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 1138 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 1138 |
|* 2 | TABLE ACCESS BY INDEX ROWID| C_TABLE | 1 | 68164 | 68164 |00:00:00.08 | 1138 |
|* 3 | INDEX RANGE SCAN | C_OBJ_IDX | 1 | 68164 | 68164 |00:00:00.01 | 153 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME">=' ')
3 - access("OBJECT_ID">=0)
1138 - 153 = 985
HR@ora11g> select /*+ gather_plan_statistics index(c c_obj) */ count(*) from c_table c
where object_id >= 0 and object_name >= ' ';
COUNT(*)
----------
68164
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 476cd1zz3crrx, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(c c_obj) */ count(*) from
c_table c where object_id >= 0 and object_name >= ' '
Plan hash value: 3903982377
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 989 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 989 |
|* 2 | TABLE ACCESS FULL| C_TABLE | 1 | 68164 | 68164 |00:00:00.01 | 989 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("OBJECT_ID">=0 AND "OBJECT_NAME">=' '))
'Oracle 11g > Tuning' 카테고리의 다른 글
| 86 PGA, Sort Operation, Join, Table Prefetch, Batch I/O (0) | 2024.02.16 |
|---|---|
| 85 Row Chaining, Row Migration, B*tree, Bitmap Index, session_cached_cursors (0) | 2024.02.15 |
| 83 Explain Plan, AutoTrace, v$sql_plan, Buffer Pinning, SQL TRACE (0) | 2024.02.13 |
| 82 TX LOCK, Full Table Scan, Rowid Scan, Index Scan (1) | 2024.02.08 |
| 81 Trans, Lock, Redo, Append Hint (1) | 2024.02.07 |