메뉴 건너뛰기

bysql.net

8._IO_효율화_원리

2012.06.05 20:22

운영자 조회 수:4426

[오라클성능고도화원리와해법I] ch06. IO 효율화 원리 - 08 I/O 효율화 원리

I/O 효율화 튜닝의 핵심
 논리적인 I/O요청 횟수 최소화

하드웨어적인 방법
 RAW 디바이스  
 비동기 I/O사용
 스트라이핑 방식 변경
 고대역폭 인터커넥터 사용

애플리케이션 튜닝 방법
 SQL 튜닝을 통해 I/O 발생 횟수 자체를 줄이는 것
 필요한 최소 블록만 읽도록 쿼리 작성
 최적의 옵티마이징 팩터를 제공
 옵티마이저 힌트를 사용하여 최적의 액세스 경로로 유도





(1) 필요한 최소 블록만 읽도록 쿼리 작성
동일한 레코드를 반복하지 않고, 필요한 최소 블록만 읽도록 쿼리 작성 필요.
옵티마이저가 쿼리 최적화를 수행나 SQL 자체 요구사항/작업의 크기를 줄이는 것이 더욱 효과적

-> 불필요한 데이터 영역을 사용하여 Join 을 처리함

-> 출력이 돼어야만 할 요소를 추출한뒤에 join 을 처리함

※ 논리 집합의 재구성을 통하여 읽어야할 데이터의 양을 최소화 할 수 있음을 보여줌.





(2) 최적의 옵티마이징 팩터 제공


논리집합을 적절하게 구성한 이후에 쿼리 수행자가 요구하는대로 DBMS가 데이터를 접근하도록 함.

* 전략적인 인텍스 구성
 DBMS 최적화는 옵티마이저가 수행하지만 해당 작업을 수행할 수 있도록 적당한 수단 제공 필요.
 ex) 인덱스 구성

* DBMS가 제공하는 다양한 기능 활용
 파티션
 클러스터
 IOT (Index Organized Table)
 MView
 FBI (Function Based Index)
 분석함수

* 옵티미이저모드 설정

예제)

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 5 20:02:20 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters and Real Application Testing options

SQL> alter session set current_schema=DBADMIN
 2  ;

Session altered.

SQL> create table t
as
select * from all_objects
order by dbms_random.value;
 2    3    4  

Table created.

SQL> SQL> create index t_idx on t (owner, created);

Index created.

SQL> begin
dbms_stats.gather_table_stats
  (ownname        => 'DBADMIN'
,tabname   => 'T'
,estimate_percent => 100
,block_sample      => true
,method_opt        => 'for all columns size auto'
);
end;
/
 2    3    4    5    6    7    8    9   10  

PL/SQL procedure successfully completed.

SQL> SET LINES 512 PAGES 9999
SQL> set autotrace traceonly exp;
alter session set optimizer_mode='ALL_ROWS';
SQL>
Session altered.

SQL>   select *
       from t
  where owner='DBADMIN'
order by created;  2    3    4  

Execution Plan
----------------------------------------------------------
Plan hash value: 3672837129

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  4578 |   433K|       |   344   (1)| 00:00:05 |
|   1 |  SORT ORDER BY     |      |  4578 |   433K|   616K|   344   (1)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| T    |  4578 |   433K|       |   239   (1)| 00:00:03 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("OWNER"='DBADMIN')

- 정렬된 결과 집합 전체를 Fetch하여 최적화를 수행 (ALL_ROWS)

- 인덱스를 사용하지 않고 table을 full scan후 정렬하는 방식으로 동작

 인덱스를 이용해서 접근할 경우 FULL SCAN 으로 데이터 영역만 접근하는 것 보다 더 많은 블록 IO를 일으킴.



SQL> alter session set optimizer_mode ='FIRST_ROWS';

 select *
       from t
  where owner='DBADMIN'
order by created;
Session altered.

SQL> SQL>   2    3    4  

Execution Plan
----------------------------------------------------------
Plan hash value: 1020776977

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  4578 |   433K|  4362   (1)| 00:00:53 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  4578 |   433K|  4362   (1)| 00:00:53 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |  4578 |       |    17   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("OWNER"='DBADMIN')

전체 결과 집합에서 처음 일부만 Fetch 한다는 가정으로 옵티마이저가 동작함.

ORDER BY 절의 CREATED 절을 보고 옵티마이저는 인덱스를 사용할 경우 별도의 결과 집합 SORT가 없을 것으로 예상하고 인덱스 스캔을 시작함.

쿼리 결과를 끝까지 Fetch한다면 Full Scan보다 더 느리게 동작함.





* 통계정보의 중요성


dbms_stats.gather_table_stats : 테이블에대한 object통계수집
dbms_stats.gather_system_stats : 시스템통계수집
 cpu속도
 평균적인 single block 읽기속도
 평균적인 Multiblock 읽기 숙도
 평균적인 multiblock i/o개수

* 정리

1) 옵티마이저 모드를 포함해서 필요한 초기화 파라메터 설정, 적절한 통계 정보 수집

2) 전략적인 인덱스 구성 필요

3) 기타 다양한 DBMS 기능 활






(3) 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도


기본적으로 상기의 내용들을 충족한 상태에서는 옵티마이저의 판단에 맡기는 것이 바람직함.
하지만 옵티마이저가 최적의 수행계획으로 동작하지 않을 경우에는 힌팅을 이용해서 진행함.

통계정보나 실행환경 변화에 따라서 실행 계획이 동적으로 바뀔경우 심각한 장애가 발생할때에는 힌트를 적극 활용함.


CBO (cost-based optimizer) 기술이 발전하고 있지만 여러가지 이유로 옵티마이저 힌트의 사용을 불가피.
이 게시물을
목록
목록
쓰기
태그