대용량 데이터 베이스 솔루션 I
Ch4. 시스템의 진단과 튜닝.
> MONITOR 다양한 시스템활동과,그통계정보를 모니터 할 수 있는 SQL*DBA 기능 .
> SQL_TRACE 수행된 각종 SQL의 결과를 TRACE 파일로 출력해주는 기능.
> TKPROF 트레이스 파일을 이용해서 실행계획과 그출력을 해주는 기능.
> EXPLAIN PLAN SQL을 분석해서 실행계획을 수립한후 유저를 위해 Plan_TABLe에 저장 하도록 해주는 명령어.
> ANALYZE 비용기준 (COST-BASED) 옵티마이져 가 최소 비용을 가지는 수행경로를 수립하기윈한 각종 통계정보를 컴파일하는 명령어.
> UTLBSTAT(begin) 과 UTLESTAT(end) 데이터 베이스에 대한 실행상태에 대한 통계정보를 보여주는 스냅샷을 생성해주는 SQL SCRIPT 들.
> 기타 스크립트들 오라클에 제공해 주는 추가적인 진단과 튜닝 스크립트들.
> 사용자 스크립트들 사용자가 직접만든 진단과 튠닝 스크립트들.
> SQL area 튜닝 SQL 공휴율(hit ratio)을 향상 시키고 메모리 활용을 위해 적용하는 투닝 방법들.
> 병렬처리 튜닝 만읗양의 데이타의경우 효과적으로 병렬처리를 효율화 시키기 위한 튜닝 방법들.
> MONITOR ( 시스템 활동감시)
>> SQL*DBA MONITOR기능은 플랫폼에 따라 사용방법은 조금씩 틀리지만 그 기능은 동일하다.
>> SYSTEM 또는 INTENAL 유저 그룹에서 로그인해야 사용할수있다.
% > MON FILEIO
> MON CYCLE number
> SQL_TRACE (TRACE 파일 생성)
>> SQL 파싱 ,샐행, 펫치를 수행한 횟수
>> 수행한 CPU 시간과 경과한 시간
>> 물리적, 논리적 인 읽기를 수행한 횟수
>> 추출된 로우싀 수
>> 유용한 정보를 제공하지만 SQL_TRACE 를 수행시키면 성능은 20%~30% 감소하게 된다.
>> SQL_TRACE 파라미터 들.
>>> TIMED_STATISTICS = TRUE RDBMS 가 SQL 실행에 대한 추가적인 시간 통계정보를 표시하게한다.
>>> SQL_TRACE = TRUE SQL_TRACE 오라클 에세션을 갖는 모든 사용자의 트레이스를 수행한다.
>>> USER_DUMP_DEST TRACE 파일이 생성되는 경로를 지정한다.
>>> MAX_DUMP_FILE_DEST SQL_TRACE 파일의 크기
% SQL>@D:\oracle\ora92\rdbms\admin\utlxplan.sql
SET AUTOTRACE ON;
SHOW PARAMETER USER_DUMP_DEST;
ALTER SESSION SET SQL_TRACE= TRUE;
ALTER SYSTEM SET USER_DUMP_DEST ='d:\sqltrace' scope=both;
ALTER system SET max_dump_file_size = 10000 scope=both;
ALTER SYSTEM SET timed_statistics=TRUE scope=both;
> TKPROF
분석법
만약 I/O병목 현상 때문이라면 과련된 테이블을 여러 개의 디스크에 분할 해 병목 현상을 해결해야 한다.
>> TRACE 파일의 데일 끝에 나오는 OVERALL TOTALS FOR ALL NON-RECURISIVE STATEMENTS 에대한 분석
>>> Parse 라인에 있는 CPU ,ELASPED, DISK 항목의 값이 EXCUTE 나 FETCH 라인의 값에 비해 비교적높으면 메모리 내의 자료사전 정보를 더 저장
할수 있도록 자료사전 캐쉬를 튜닝해야 한다. (시스템이 자동적으로 조절하므로 , sql 공유 역역을 늘려줌으로 해결한다.)
>>> Parse 의 COUNT 값이 높으면 애플리케이션이 정반적으로 많은 SQL 을 반복 수행 했다는 것이 된다, 보관커서를 사용해서 효율을 높이거나
SQL 자체를 튜닝해야한다.
>>>( Excute의 disk + fetch의 disk )의 값이( Excute의 query + Excute의 current +fetch 의 query + fetch의 current )의 10%이상이라면
메모리의 hit ratio 가 너무 낮은 것이므로 SQL database buffer cache 를 늘려주어야 한다.
>>> Pl/SQL 을 사용한 경우에 fetch의 count 가 fetch 의 rows 보다 두배 가까이 커진다면 내부에서 내부 커서가 사용되고 있을 확률이 높다.
즉 declare 한 sql한 문장이 실행되 한번씩 fetch 될때마다 SELECT .. INTO 가 수행되고 있다는 것을 의미하므로 declare 된 SQL 에서 join을 해
FETCH ..INTO 의 형태가 되게 하거나 반복수행되는 SQL 들을 모아서 조인을 해주어 해결한다.
>>> 다음은 아주빠른 응답 시간이 요구되는 온라인 프로세싱 시스템의 경우에서만 적용되는 규칙들이다.
>>>> 모든 Excute 의 cpu 가 1초보다 적어야 한다.
>>>> parse cpu 시간이 parse 당 0.01초 보다 적어야 한다.
>>>> 작은 테이블에서만 전체 테이블 스캔이 일어나게 한다 , 약 200 로우 이상인 테이블과 여러 테이블을 조인할때 많이 쓰이는 테이블은 전체
테이블을 스캔하지 않도록 해야한다.
>>>> sysdate 만 찾아 오거나 연산만 하거나 select .. Into 로 값을 복사하는 경우를 위해 여미 테이블을 불필요하게 사용하는것은 모두 없애
주어야 한다.
>>>> 동시에 작업되는 sql 들은 가능한 pl/sql 을 사용하는것이 좋다.
>>>> 조인시에 옵티마이져가 적절한 드라이빙 테이블을 선택하는지 확인하거나 처리범위와 분포도에 따라 인덱스를 변경 해주어야 한다.
> EXPLAIN PLAN (실행계획 )
>>ANALYZE (통계정보 생성)
>>> ANALYZE 는 인덱스 테이블 클러스터의 통계정보를 생성하여 옵티마이저가 가장 효율적인 실행계획을 세울수 있도록 도와준다.
>>>> COMPUTE 레벨 느리지만 정확한 통계값을 계산해낸다.
>>>> ESTIMATE 레벨 자료사전과 견본테이자를 가지고 통계를 산출한다.
>>>> DELETE 테이블의 모든 통계정보를 삭제한다.
>>> 산출되는 통계 데이터
>>>> 테이블 총로우의수,총블럭의수,비어있는 블록에 쓰여질수있는 빈공간의평균, 체인이발생한로우의 수 , 로우의 평균 길이
>>>> 인덱스 인덱스의 깊이,LEAF block 의 수 , DISTINCT KEY 의 수 , LEAF BLOCKs/KEY 의 평균
DATA BLOCK/KEY 의 평균, CLUSTERING FACTOR , 가장 크고,가장 작은 KEY 값 (compute 만)
>>>> 컬럼 distinct 한 값의수
>>>> 클러스터 CLUSTER KEY 당 길이의 평균
>> 실행계획을 위한 지침 사항
>>> 주기적인 ANALYZE 작업을 수행시켜야 한다.
>>> 액세스 경로가 자주 바뀌면 APP 의 SQL을 추출하여 액세스 경로를 고정시킬 필요가 있다.
>>> 총로우수가 많은 테이블은 ANALYZE 가 부담이 되므로 가능한 작업 주기를 길게하고,부담이 크다면 인덱스를 재구성한다.
>>> 테이블을 재생성 하였거나 , 새로 클러스터링을 한경우 는 작업 주기에 상관없이 ANALYZE를 수행 시켜주는 것이 좋다.
>> UTLTSTAT.sql UTLESTAT.sql
위의 두 스크립트는 데이터 베이스가 일정 기간 동안 실행되고 있는 상태를 알려주는 스냅샷을 생성한다 이를 보면 튜닝할 대상과
발생한 문제점을 알수있다.
>>> UTLBSTAT.SQL은 테이블에 통계정보를 쓰기 시작하라고 DBMS에 알림.
>>> UTLESTAT.SQL은 쓰기작업을 멈추고 리포트를 보여주는 스크립트이다.
>>> 단 사용자가 없을 시간대에 적용시키면 비현실적인 판단이 도출되므로 오전 10시에서 정오까지, 오후2시오후4시,를 실행시킨다.
>>> 여기서도 TIMED_STATISTICS 를 TRUE 로 한다. 해야 자세한 시간 통계정보를 얻을수있다.
% SQL>@UTLBSTAT
SELECT n1.name "statistic" , n1.change "total" , trunc (n1.change /n2.change,2) "per trans"
FROM stats$stats n1 , stats$stats n2
WHERE n2.name = 'user commits'
ORDER BY n1.name;
>>> ①에서Enqueue waits > 0 이면 INIT.ora 파일의 ENQUEUE_RESOURCE 파라메터의 값을 증가시켜야 한다.
>>> ② Recursive calls / user call 의 값이 0.1 보다 크면 딕셔너리 캐쉬 가 부족하다는 것을 의미하므로 SQL 공유 영역을 증가시켜야 한다.
>>> ③위의 세가지 SORT 통계는 SORT_AREA_SIZE 와 SORT_AREA_RETAINED_SIZE 의 값을 증가시켜야 할것인지를 판단하는데 사용한다.
>>> 메모리 버퍼 캐쉬 적중률 (buffer cache hit ratio ) 튜닝
이튜닝은 디스크의 I/o를 줄이고 메모리에서 최대한의 데이터를 액세스 하기위한 것이다. BUFFER CACHE의 효율을 높이면 DB의 성늘을 탁월하게
높일수 있다 정중률 100%를 목표로 한다.
이 결과 값이 90%보다 적으면 공유영역에 대한 튜닝이 되어야 하고,사용자가
physical read 가장 많이 사용할때 버퍼의 영역이 5%남아 남을때까지 버퍼의 값을 늘려야 한다.
INIT.ORA 파일의 DB_BLOCK_BUFFERS 를 증가하거나 감소시킨후 DBMS를 재가동 시킨다 , 그러나 메모리 5%정도는 꼭 남겨둬야 한다.
>>> 버퍼 캐쉬의 튜닝(buffer cache)
버퍼캐쉬는 테이블 , 인덱스 , 롤백 세그먼트, 클러스터 들의 데이터 베이스 블록에 대한 복사본을 저장하는 메모리 영역이다. 버퍼 캐쉬는 디스크 I/O를 현저히
줄여주고 효율을 향상시킨다. INIT.ORA 파라메터의 DB_BLOCK_BUFFER 값을 증가 시키면 오랫동안 수행되는 갱신(UPDATE)작업등은 경우에 따라 50%이상의
효율 향상을 가질수있다.
>>>> 버퍼 캐쉬 증가후에 나타날 캐쉬적중의 효과를 측정하려면 INIT.ORA 파일의 DB_BLOCK_LRU_EXTENDED_STATISTICS 파라메터에 원하는 버퍼 캐쉬
증가치를 지정한다. 예를 들어 1000 을 지정하고 250마다 증가시킨 효과를 보기위해서는
% SELECT 250*TRUNC(INDX/250)+1 || 'to' || 250*(TRUNC(INDX/250)+1) INTERVAL, SUM(COUNT) CACHE_HITS
FROM SYS.X$KCBRBH
GROUP BY TRUNC(INDX/250);
결과 INTERVAL CACHE_HITS
1 to 250 21000
251 to 500 15000
501 to 750 3000
751 to 1000 35000
>>>> 버퍼 캐쉬를 감소 시켰을 때의 측정결과
% SELECT 250*TRUNC(INDX/250)+1 || 'to' || 250*(TRUNC(INDX/250)+1) INTERVAL, SUM(COUNT) CACHE_HITS
FROM SYS.X$KCBRBH
WHERE INDX > 0
GROUP BY TRUNC(INDX/250);
>>> 롤백 세그먼트와 오라클 데이터블럭간의 경합을 줄이는 튜닝
>>>> UTLBSTAT/UTLESTAT의 통계를 사용하여 다음과 같은 방버븡로 계산한다.
BUFFER Busy waits ratio = (buffer busy waits) / (logical reads)
= (1) /39414 = 0.00002
계산 결과가 0이 아니면 블록 경합이 발생하는것을 가르킨다. 만약 0.05 이상이면 상태가 안좋은 것임,
결과가 0.05~ 0.1 이라면 V$WAITSTAT 를 조회하여 (TIMED_STAISTICS=TRUE) Undo Block 과 undo egment Header 이 기다리는 시간이 많으면
롤백 세그먼트 더 많이 생성해야 하며 Data block 과 segment header 항목에 기다리는 시간이 많다면 프리 리스트를 더욱 크게 생성 해야 한다.
>>> DBWR의 튜닝
>>>> UTLBSTAT/UTLESTAT의 통계중 DBWR free needed 가 0이 아니면 당장 개선해야한다.
>>>>> INIT.ORA 파일의 DB_WRITER 와 _DB_BLCOK_WRITE_BATCH 의 값을 증가시켜 버퍼수를 늘려 쓰기 작업의 효율을 늘린다.
>>>> DBWR Checkpoints DBWRdp 보내진 CHECK POINT 의 수이다 ,체크 포인트의 주기가 길어지면 대개의 경우 성능은 향상되지만 복구에는 약해지게 된다.
>>>>> 체크포인트수를 줄이려면 INIT.ORA 에서 LOG_CHECKPOINT_INTERVAL 값을 증가시킨다.
>>>> LOG_CHECKPOINT_TIMEOUT 다음 체크 포인트까지의 시간을 지정한다 0이면 사용불가설정이된다.
>>>> DBWR make free requests DBWR 가 LRU 를 위해서 사용가능버퍼를 더 만들도록 요구하는 메시지를 받은 횟수.
>>>>. DBWR free buffer found 사용가능버퍼에대한 요구에대해서 찾은 사용가능한 버퍼수이다, DBWR make free request 와 나우어서 LRU 이후 다시
사용할수 있는 평균버퍼정도를 알아낸다.
>>>> DBWR lru scans DBWR 가 더 많은 사용가능 버퍼르 요구하기 위해 LRU 를 스캔한 수이다.
>>>> DBWR summed scan depth DBWR 가 변경버퍼(dirty buffer)를 찾기위해 스캔할때마다 현재의 스캔깊이가 이 통계에 더해진다.
>>>> DBWR buffers scans 변경된 버퍼를 처리하기위해 LRU를 스캔한 버퍼의수 .
>>>> summed dirty queue length 모둔 쓰기 요구가 끝난뒤 LRU 큐 길이의 합. Write requests 와 나누어서 평균 큐의 길이를 구할수 있다.
>>>> free buffer inspected 사용자 프로세스가 버퍼 를 필요로 했을때 버퍼 캐쉬를 초과한 수이다. 이값이 free buffer scans 와 비교해 높다면
버퍼캐쉬에 너무 많은 변경 블록이 있다는것이므로 버퍼 캐쉬의 크기를 증가시켜야 한다.
>>>> free buffe waits 프로세스가 사용가능 버퍼를 찾지못해 기다림이 발생한 횟수이다. Free buffer waits / free buffer scans dl 10% 보다크면
init .ora 에 _DB_BLOCK_WRITE_BATCH 를 증가 시켜야 한다.
>>> 액세스 방법의 튜닝
인덱스를 이용한 데이터 액세스의 효율성을 확인한다.
tables scans (long tables) / (table scans(short tables) + table scans (long tables)) 가 10% 보다 크면 인덱스의 활용에 대해 다시 검토해본다.
>>> I/O 분포의 튜닝
UTLBSTAT/UTLESTAT temporary table 를 조회하여 본다.
% SELECT * FROM STATS$FILES;
위이 쿼리의 결과를 토대로 가장 활종적인 차일을 찾는다 만약 SYSTEM 이가장 활동적이라면 좋은것이 아니다 TEMP가 활동하지 않으면 사용자 그룹별로
TEMP 가 지정이 되어있지 않기 때문이므로 지정해준다.
% ALTER USER username TEMPORARY TABLESPACE tablespacename;
를 사용해 각사용자에게 TEMP 테이블 스패이스를 할당한다.
>>> 데이터 파일별 디스크 I/O 튜닝
% SELECT NAME, PHYRDS,PHYWRTS
FROM V$DATAFILE DF , V$FILESTAT FS
WHERE DF.FILE# = FS.FILE#;
시스템 테이블 스패이스에 부하가 걸릴경우 자료사전의 참조를 하는 파싱을 줄여주는 SQL 을 사용하거나 정렬작업이 있어나는 곳을 TEMPORARY 로
바꾸어준다.
>>> SORT duddur 증가에 의한 I/O 감소
SQL 문장에 GROUP BY, ORDER BY, DISTINCT 를 사용했거나 소트 머지 조인,집합처리,,인덱스 생성 서브 쿼리를 하는 SQL 문은 처리과저에서
로우의 정렬이 필요하다
% SELECT NAME, VALUE FROM V$SYSSTAT
WHERE NAME IN('sorts(disk)','sorts(memory)','sorts(rows)');
결과 sorts(disk) 543
sorts(memory) 587318
sorts(rows) 11268045
>>>> sorts(disk) 는 INIT.ORA 의 SORT_AREA_RETAINED_SIZE 로 지정된블럭보다 정렬되는 데이터가 필요할때 TEMPORARY SEGMENT를 생성하
므로 발생한다. 디스크 와 메모리 정렬은 전체의 5%미만이 적당하다, 아니면 SORT_AREA_SIZE 를 증가시킨다.
SORT_AREA_RETAINED_SIZE 를 1M , SORT_AREA_SIZE 를 10M 로 지정했을떄
>>>>> 1M 이내의 SORT는 디스크와 관계 없이 메모리 에서 정렬한다.
>>>>> 1M~10M의 SORT 는 SORT_AREA_SIZE를 넘지 않으므로 단한번의 메모래내 정렬작업으로 완료한다. 그러나
SORT_ZREA_RETAINED_SIZE 를 넘었기때문에 정렬한 결과를 즉시 디스크에 쓰고 메모리에서 지운다.
>>>>> 10M이상의 SORT 는 두가지 파라미터를 모두 넘으므로 SORT_AREA_SIZE 만큼만 메모리 작업을 하여 디스크에 쓰는 방식을 반복한다.
>>>>> 대용량 OLTP의 처리가 있는 경우에는 SORT_AREA_SIZE 를 일시적으로 증사시키고 작업이 종료하면 다시 줄이는 것도 좋은 방법이다.
>>>>다양한 정렬작업을 위해 , 임시 테이블 스패이스의 STORAGE 값을 이용, 사용시간에따라 조정해준다.
>>>> 온라인 작업이 많은 낮시간대에는..
% ALTER TABLESPACE TEMP_TSPACE DEFAULT STORAGE (INITIAL 260K NEXT 260K PCTINCREASE 0);
>>>> 배치 작업이 많은 밤에는
% ALTER TABLESPACE TEMP_TSPACE DEFAULT STORAGE (INITIAL 5M NEXT 5M PCTINCREASE 0);
>>>> SORT_AREA_RETAINED_SIZE 는 SORT_AREA_SIZE 의 반으로 하는것이 추천 사항이다.
>>> 내부적인 래취의 경합 감소
사용자 프로세스가 SGA의 정보를 접근하려면 일종의 LOCK 과 유사한 내부적인 LATCH 를 소유해야 한다. 래취가 부족해서 경합이 일어나면
수행속도가 느려지고 CPU 가 더 많이 필요하게 된다.
% SELCT NAME LATCH_NAME , GETS, MISSES, ROUND(DECODE(GET-MISSES,0,1,GET-MISSES) / DECODE(GETS,0,1,GETS),3),HITS_RATIO,SLEEPS
, ROUND(SLEEPS/DECODE(MISSES,0,1,MISSES),3) "SLEEPS/MISS"
FROM STATS$LATCHES
WHERE GETS != 0
ORDER BY NAME;
>>>> 위의 SQL 문의 결과중에서 HIT_RATIO 에 있는 래취 적중율이 0.97 이하가 되면 래취에 대한 경합이 발생하고 있다는것이다.
래취에 대해서 나중에 정리하자.
>>> ROLLBACK 에 관련된 처리의 감소
일관성을 가지도록 갱신을 하기 위해서는 서열을 부여해야 하므로 메모리를 래취하는것이 반드시 필요하다,
>>>> 큰롤백 세그먼트는 몇 개만 만들고 데이터 베이스가 가지고있는 가장 큰 테이블이 충분히 들어갈수있는 크기로 한다,
>>>> 따로 사용할 롤백 세그먼트를 지정할수있다.
% SET TRANSACTION USE ROLLBACK SEGMENT rollback_segment_name
% SELECT * FROM stats$roll;
>>>> 위의 SQL 의 실행결과에서 TRANSACTION table wait ratio = TRANS_TBLWAITS / TRANS_TBL_GETS 의 비율이 0.5%보다 크면 롤백
세그먼트를 추가 시켜야 한다.
<Oracle Hint>
// 힌트를 사용한 오더바이 부분범위처리로의 유도.
select /*+ INDEX_DESC(TABLE_NAME INDEX_NAME) */ DEPT,ENAME
FROM EMP
WHERE DEPT = "SALES"
select /*+ INDEX(TABLE_NAME INDEX_NAME) */ DEPT,ENAME
FROM EMP
WHERE DEPT = "SALES"
// 클러스터의 강제 이용
select /*+CLUSTER(A) */ SUM(count(*) FROM A
WHERE B between '1' and '10'
and RTRIM(결합인덱스컬럼 이름) = 'ㅁㄴ'
