가. I/O 효율화 튜닝의 중요성
나. 버퍼 캐시 히트율(Buffer Cache Hit Ratio)
BCHR = (버퍼 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수) × 100
다. 네트워크, 파일시스템 캐시가 I/O 효율에 미치는 영향
가. Sequential 액세스에 의한 선택 비중 높이기
-- 테스트용 테이블 생성
create table t
as
select *
from all_objects
order by dbms_random.value;
-- 테스트용 테이블 데이터 건수 : 49,906
SQL> select count(*) from t;
COUNT(*)
--------
49906
select count(*)
from t
where owner like 'SYS%'
Rows Row Source Operation
----- ------------------------------
1 SORT AGGREGATE (cr=691 pr=0 pw=0 time=13037 us)
24613 TABLE ACCESS FULL T (cr=691 pr=0 pw=0 time=98473 us)
select count(*)
from t
where owner like 'SYS%'
and object_name = 'ALL_OBJECTS'
Rows Row Source Operation
---- ------------------------------
1 SORT AGGREGATE (cr=691 pr=0 pw=0 time=7191 us)
1 TABLE ACCESS FULL T (cr=691 pr=0 pw=0 time=7150 us)
create index t_idx on t(owner, object_name);
select /*+ index(t t_idx) */ count(*)
from t
where owner like 'SYS%'
and object_name = 'ALL_OBJECTS'
Rows Row Source Operation
---- ------------------------------
1 SORT AGGREGATE (cr=76 pr=0 pw=0 time=7009 us)
1 INDEX RANGE SCAN T_IDX (cr=76 pr=0 pw=0 time=6972 us)(Object ID 55337)
create index t_idx on t(object_name,owner );
select /*+ index(t t_idx) */ count(*)
from t
where owner like 'SYS%'
and object_name = 'ALL_OBJECTS'
Rows Row Source Operation
---- ------------------------------
1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=44 us)
1 INDEX RANGE SCAN T_IDX (cr=2 pr=0 pw=0 time=23 us)(Object ID 55338)
나.Random 액세스 발생량 줄이기
drop index t_idx;
create index t_idx on t(owner);
select object_id
from t
where owner = 'SYS'
and object_name = 'ALL_OBJECTS'
Rows Row Source Operation
---- ------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=739 pr=0 pw=0 time=38822 us)
22934 INDEX RANGE SCAN T_IDX (cr=51 pr=0 pw=0 time=115672 us)(Object ID 55339)
drop index t_idx;
create index t_idx on t(owner, object_name);
select object_id
from t
where owner = 'SYS'
and object_name = 'ALL_OBJECTS'
Rows Row Source Operation
---- ------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=67 us)
1 INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=51 us)(Object ID 55340)
Single Block I/O 방식
create table t
as select * from all_objects;
alter table t add
constraint t_pk primary key(object_id);
select /*+ index(t) */ count(*)
from t
where object_id > 0
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.26 0.25 64 65 0 1
----- ---- ---- ------ ---- ----- ----- ------
total 4 0.26 0.25 64 65 0 1
Rows Row Source Operation
----- ------------------------------
1 SORT AGGREGATE (cr=65 r=64 w=0 time=256400 us)
31192 INDEX RANGE SCAN T_PK (cr=65 r=64 w=0 time=134613 us)
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
db file sequential read 64 0.00 0.00
SQL*Net message from client 2 0.05 0.05
MultiBlock I/O 방식
-- 디스크 I/O가 발생하도록 버퍼 캐시 Flushing
alter system flush buffer_cache;
-- Multiblock I/O 방식으로 인덱스 스캔
select /*+ index_ffs(t) */ count(*)
from t
where object_id > 0
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.26 0.26 64 69 0 1
----- ----- ---- ------- ---- ----- ------- ----
total 4 0.26 0.26 64 69 0 1
Rows Row Source Operation
----- ------------------------------
1 SORT AGGREGATE (cr=69 r=64 w=0 time=267453 us)
31192 INDEX FAST FULL SCAN T_PK (cr=69 r=64 w=0 time=143781 us)
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
db file scattered read 9 0.00 0.00
SQL*Net message from client 2 0.35 0.36
가. 필요한 최소 블록만 읽도록 SQL 작성
나. 최적의 옵티마이징 팩터 제공
다. 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도
[예제] Oracle
select /*+ leading(d) use_nl(e) index(d dept_loc_idx) */ *
from emp e, dept d
where e.deptno = d.deptno
and d.loc = 'CHICAGO'
[예제] SQL Server
select *
from dept d with (index(dept_loc_idx)), emp e
where e.deptno = d.deptno
and d.loc = 'CHICAGO'
option (force order, loop join)
[J STORY] SQLP - 트랜잭션 (0) | 2021.04.27 |
---|---|
[J STORY] SQLP - LOCK (0) | 2021.04.26 |
[J STORY] SQLP - 데이터베이스 CALL과 네트워크 부하 (1) | 2021.04.25 |
[J STORY] SQLP - SQL 파싱 부하 (0) | 2021.04.25 |
SQLP - 인덱스 기본 (0) | 2021.04.24 |
댓글 영역