오라클 튜닝

오라클 튜닝  오라클  2012/02/20 21:06   http://blog.naver.com/koleekr7/100151588998   1. 튜닝이란?    응답시간을 줄이는 조치 리소스의 사용을 줄이는 조치   2. INDEX란? 인덱스는 테이블이나 클러스터에서 쓰여지는 선택적인 객체 TABLE내에 원하는 레코드를 빠르게 찾아갈수 있도록 만들어진 데이터구조 B* TREE 기반임   3. INDEX사용은 항상 빠른가? DBMS는 BLOCK단위의 IO를 수행하며 INDEX를 사용하지 않을때는 MULTI BLOCK IO를 수행함. db_file_multiblock_read_count 만약 10개의 BLOCK을 읽어야 한다면, 인덱스를 사용하면 10번의 I/O를 수행하지만 인덱스를 사용하지 않으면 한번의 I/O로 10개의 BLOCK를 읽을수 있음.   결론 : 꼭 인덱스를 사용한다고 빠른건 아니다.   4. INDEX분포도   분포도 = (컬럼의값의종류의개수/테이블의총ROW수) * 100   예) (15/100) * 100 = 15%   총 100개의 ROW가 존재하고 테이터의 종류는 15개나 됨.   분포도의 백분율이 10~15% 이내인 경우 INDEX적용   결론 : 만약 전체의 테이블의 ROW수가 10000개 일 경우1000개 읽어낼려고 1000개의 BLOCK을 다 뒤지는거 보다 10000개를 읽을려고 1000개만 뒤지는게 더 효율적이다.   5. 인덱스 선정 기준 분포도가 좋은 컬럼을 고를것 자주 조합되어 조건식에 사용되어 SELECT되는 컬럼은 결합인덱스생성 기본키 및 외래키 결합인덱스 선정 순서에 주의할것 LOOP로 검색되는 조건은 가장 빠른 속도를 내게 할 것   6.  인덱스 활용시 고려사항   새로추가된 인덱스는 기존 액세스 경로에 영향을 미침 지나치게 많은 인덱스는 오히려 오버헤드 발생 넓은 범위의 인덱스도 오히려 오버헤드 발생 조인시에 인덱스 사용여부 주의함   7. 인덱스가 소용없어지는 경우   INDEX_COLUMN 의 변형 NOT OPERATOR NULL, NOT NULL 비교 OMTIMIZER의 자동선택   예) SELECT * FROM EMP WHERE SUBSTR(ENAME,1,3) = 'ABC';   위와 같은 쿼리는 인덱스르 사용하지 않는다.   SELECT * FROM EMP WHERE ENAME LIKE 'ABC%';   로 바꾸면 인덱스를 사용하게 된다.   8. INDEX종류   A. BITMAP INDEX   BIT연산으로 데이터 처리 저장공간을 절약 DML작업이 빈번할시에는 매우 비효율적   B.REVESE KEY INDEX   BIT를 반대로 배열해서 작성 DML에 유리해짐(BYTE를 거꾸로 저장하면 INDEX에 값이 골고루 분산됨) RANGE 검색은 불가능함(순서대로 정렬되지 않기때문에)   C.FUNCTION BASED INDEX 함수를 적용한 상태로 생성되는 INDEX TABLE내에 HIDDEN컬럼을 생성후 그 컬럼을 적용하고 값이 변경될때마다 관리 DATA구조상 함수가 꼭 사용되는 경우 매우 유용하게 사용됨   9.EXECUTION PLAN   EXECUTION PLAN은 옵티마이저에 의해서 생성된 TABLE 및INDEX 접근 경로이다.   10.DATA ACCESS METHOD   A.FULL TABLE SCAN TABLE전체를 SCAN 1회 작업시 DB_FILE_MULTIBLOCK_READ_COUNT에 설정된 값만큼 READ작업 수행 모든 자료를 READ하므로 속도가 떨어짐   B.INDEX UNIQUE SCAN 1건의 자료를 INDEX를 통해 접근 효율적인 방법임 INDEX가 UNIQUE하게 생성되어 있어야함   C.INDEX RANGE SCAN 1건 또는 그 이상의 자료를 INDEX를 통해 접근 INDEX 가 NON UNIQUE인 경우 모두 해당 INDEX가 UNIQUE하더라고 INDEX 컬럼 조건에 들어오지 않거나 모든 값이 입력되더라도 조건이 '='이 아니면 INDEX RANGE SCAN을 수행함   D.INDEX FULL SCAN INDEX의 모든 내용을 접근 1 BLOCK I/O INDEX컬럼에 정의된 순서대로 결과가 출력됨(정렬된다는 소리임)   E.INDEX FAST FULL SCAN INDEX 블럭을 접근 MULTI BLOCK I/O 병렬 프로세싱 가능 병렬 프로세싱 할 경우 결과가 INDEX컬럼 순서와 다르게 나올수 있음   F.INDEX SKIP SCAN INDEX의 LEADING COLUMN을 SKIP하고 다음 컬럼을 찾아 접근 FULL SCAN보다는 우수한 성능 하지만 LEADING컬럼이 있는 경우보다는 성능 안좋음     11. JOIN METHOD   A.NESTED LOOP JOIN 두개의 ROW SET중 첫번째 ROW SET의 내용을 1개 읽은후 그것을 두번째 ROW SET에서 검색 주어진 조건을 만족하거나 첫번째 ROW SET의 내용이 소진될때까지 반복 수행 JOIN되는 컬럼에 인덱스가 있는것이 바람직함. 먼저처리되는 TABLE을 driving table 혹은 outer table이라고 함. 나중에 처리되는 TABLE을 inner table이라고 함. 오라클 힌트 USE_NL(inner table)을 적절히 이용해야함.   B.HASH JOIN 첫번째 TABLE을 읽어 HASH AREA상에 HASH TABLE을 구성함 두번째 TABLE을 읽어 JOIN KEY에 대한 HASH VALUE를 구하여 HASE AREA상의 HASH VALUE와 비교 주어진 조건을 만족하거나 두번째 TABLE을 모두 읽을때까지 수행 EQUAL조건에서만 사용가능 오라클 힌트 USE_HASH(probe table)사용함   C.Sort Merge Join 첫번째 테이블을 읽어 JOIN KEY로 SORTING 두번째 테이블을 읽어 JOIN KEY로 SORTING SORT된 두개의 테이블을 비교하여 일치하는 자료를 리턴 일반적으로 성능이 떨어짐 대용량 처리시 NESTED 보다 우수한 성능 하지만 HASH보다 성능이 떨어짐 하지만 EQUAL조건이 아니어도 이용이 가능함   D.CARTESIAN PRODUCT   M:M형태의 조인(조인되는 컬럼이 키가 아닌경우) 성능을 떨어뜨리는 주범이 됨. 특별한경우가 아니면 EXECUTION PLAN상에 나타나면 안됨   E.OUTER JOIN OUTER JOIN 대상 TABLE의 자료가 존재할 경우 그 정보를 출력하고 없으면 기존의 자료만 출력   F.SEMI JOIN 한쪽 테이블의 자료가 다른쪽 테이블에도 존재하는지 여부만 조사함.   G.ANTI JOIN 한쪽 테이블의 자료가 다른쪽 테이블에 존재하지 않는지의 여부를 조사함   12.OPTIMIZER   A.개요   옵티마이저는 수행할 SQL문을 DBMS가 이해할수 있는 형태로 변환하는 과정에서 수행 계획을 결정짓는 것이다.   크게 RULE BASED OPTIMIZER와 COST BASED OPTIMIZER로 나뉜다.   B. 옵티마이저모드   CHOOSE : 대상 테이블에 통계정보가 있으면 CBO 통계정보가 없는경우는 RBO로 수행 ALL_ROWS : HASH JOIN위주로 실행계획 수립 (가장 빨리 처리할수 있는 방향으로 실행계획수립) FIRST_ROWS : 첫 ROW가 가장 빨리 출력될수 있는 방향으로 실행계획 수립(NESTED위주) FIRST_ROWS_N : 처음 출력되는 N건이 각장 빨리 출력될수 있는 방향으로 실행계획 수립 RULE : 이미 지정된 RULE에 따라 실행 계획 수립   C. 옵티마이저 방식   C-1.CBO  COST가 가장 낮은 실행계획 사용 파라미터 : CHOOSE, ALL_ROWS, FIRST_ROWS, FIRST_ROWS_N   C-2.RBO 미리정의된 15가지 룰에 따라 우선순위가 높은 OPERATION중 적용가능한 OPERATION을 적용해 실행계획을 생성함 파라미터 : RULE   D. HINT   D-1.개요 옵티마이저의 행동양식을 제어함   D-2.용도 옵티마이저 성격 규정 FIRST_ROWS(N) -> 첫번째 혹은 N번째 자료가 빨리 나올수 있도록 설정 접근 경로 제어 INDEX -> 지정한 테이블의 지정한 INDEX를 사용하도록 제어(없으면 무시) INDEX_DESC ->지정한 테이블의 지정한 INDEX를 뒤에서 부터 사용하도록 제어 쿼리 변형 제어 USE_CONCAT ->IN 또는 OR이 사용된 경우 각각을 별도로 수행후 UNION ALL로 연결하도록 제어 NO_EXPAND -> IN또는 OR이 사용된 경우 별도로 수행되는것을 막음 NO_MERGE -> MERGE가 가능한 VIEW에 대해 MERGE하지 않도록 함 조인 순서 제어 ORDERED -> FROM절 이후에 기술된 순서대로 TABLE을 접근하도록 제어 조인 동작 제어 USE_NL -> NESTED LOOP방식으로 JOIN하도록 설정 USE_HASH -> HASH JOIN방식으로 JOIN하도록 설정 LEADING -> 지정한 테이블을 가장 먼저 접근하도록 설정 병렬 수행 방식 제어 PARALLEL -> 지정한 테이블을 지정한 DEGREE로 병렬로 수행하도록 설정   13. TRACE   A.개요 TRACE는 SQL문 수행에 따른 자원 사용량과 소요시간 정보를 수집해 사용자에게 제공하는 역할을 수행함.   B.사용방법 파라미터 설정 -> TRACE설정 -> tkprof을 통한 결과 분석   C. 파라미터 설정   TIMED_STATISTICS, MAX_DUMP_FILE_SIZE, USER_DUMP_DEST   D. TRACE 설정하기   alter session set sql_trace=true;  exec dbms_session.set_sql_trace(true) exec sys.dbms_support.start_trace_in_session(sid,serial#,binds,waits) alter session set events '10046 trace name context forever, level 12'; dbms_system.set_ev(sid,serial#,10046,level) dbms_monitor.serv_mod_act_trace_enable(service_name, module_name, action_name, waits, binds, instance_name) dbms_monitor.serv_mod_act_trace_disable(service_name, module_name, action_name)  oradebug setospid oradebug ServerProcessID oradebug event 10046 trace name context forever, level 12 oradebug event 10046 trace name context off   E. TKPROF   Usage: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]     [출처] 오라클 튜닝|작성자 이경오