오라클 체크항목

오라클 진단시 여러가지 체크항목을 나열해 보았습니다.

사실 guide가 절대적이라고 볼순없습니다.

환경마다 성격은 다다르므로 절대적인 기준이 아니고 참고정도로만 생각해주셨으면 좋겠네요.. 

Resource Limit

Purpose 데이타베이스의 시스템 리소스의 할당내역과 소모현황을 확인한다.

Description 인스턴스 시작시 할당되는 시스템 자원에 대하여 할당값과 소모현황을 모니터링하여 할당자원이 부족할 경우 적절한 조치를 취해야 한다.

대부분의 경우 대응되는 초기화 파라미터를 통해 설정값을 변경할 수 있다. 이 정보는 오렌지의 Database Information 툴에서 수시로 확인할 수 있다.

*Guide Resource Name별로 Current Util 이나 Max Util 값이 Limit 값에 근접할 경우에는 init.ora 파일에서 해당 리소스를 설정하는 초기화 파라미터의 값을 늘려주어야 한다.

오라클 버전에 따라 processes 파라미터의 값을 크게 설정할 경우 log file sync 대기 이벤트가 증가하는 문제가 발생하기도 한다.

따라서, 필요 이상으로 크게 설정하지 않는 것이 바람직하다.

Wait Event

System Wait Event

Purpose 인스턴스 시작 이후 모든 세션에서 발생한 모든 대기 이벤트의 누적 통계치를 보여준다.

Description Total Waits 는 해당 이벤트를 대기한 횟수를 나타낸다. Time Waited는 해당 이벤트에 대한 총 대기 시간을 나타낸다. Avg Wait Time은 평균 대기 시간을 나타내며 Total Waits와 Time Waited 값을 이용하여 계산된다.

대기 시간의 측정 단위는 1/100초 이다. 초기화 파라미터 TIMED_STATISTICS 가 FALSE로 설정되어 있으면 time 관련 항목이 0 으로 표시된다.

Total Timeouts는 지정된 대기 시간동안 대기한 후에도 요청한 리소스를 획득하는데 실패한 횟수를 의미한다. I/O 관련한 대기 이벤트는 I/O가 완료될 때까지 무한정 대기하므로 Timeout이 발생하지 않는다.

*Guide 대기 횟수가 아닌 대기 시간에 근거하여 판단해야 하며 I/O 관련한 대기 이벤트들은 평균 대기 시간이 I/O 서브 시스템의 속도를 가늠하는 기준이 되므로 주의깊게 봐야 한다. 이 정보는 Orange의 Database Information 툴의 system events 탭에서도 확인이 가능하다.

    

?  db file single write : DBWR에 의해서만 발생한다. 일반적으로 체크포인트가 발생하여 데이타 파일 헤더의 내용을 수정해야 할 때 발생한다. 데이타베이스에 많은 데이타 파일이 있을 경우 자주 발생할 수 있다.

    

?  db file parallel write : DBWR에 의해서만 발생한다. 느린 DBWR는 사용자 프로세스에 영향을 준다. 이 대기 이벤트의 대기 시간은 대부분 I/O 문제이다.

    

?  log file sequential read : ARCH 프로세스가 온라인 리두로그 파일로부터 블록을 읽어들일 때 발생한다.

    

?  log file parallel write : LGWR에 의해서만 발생한다. 느린 LGWR는 사용자 프로세스의 commit 시간에 영향을 준다. 과다한 log file parallel write 대기시간은 대부분 I/O 문제이다.

    

?  control file parallel write : 컨트롤 파일에 대한 쓰기 I/O 요청이 대기할 때 발생한다. 일반적으로 잦은 로그 스위치에 의해서 발생한다. 로그 스위치, 데이타 파일 추가, 삭제등과 같은 작업은 컨트롤 파일의 변경이 필요하다.

3초마다 CKPT 프로세스는 온라인 리두 로그 안의 체크포인트 위치를 컨트롤 파일안에 기록한다. 일반적인 환경에서는 CKPT 프로세스가 이 대기 이벤트를 가장 오래 대기한다.

ARCH 프로세스는 아카이브 로그와 관련된 정보를 컨트롤 파일에 기록하며, LGWR 프로세스는 로그 스위치가 발생할 때마다 컨트롤 파일을 변경한다.

만약, 사용자 프로세스가 이 대기 이벤트의 대기시간이 길다면 애플리케이션이 nologging LOB 에 대한 변경작업을 수행하는지 점검하여야 한다. 데이타 파일이 nologging 작업에 의해 변경되면, 컨트롤 파일이 변경되어야 한다.

Session Wait Event (I/O)

Purpose 현재 접속중인 Session들의 I/O 관련 Wait Event 정보를 확인한다.

Description v$session_event를 조회하여 정보를 가져온다. Wait Time Ratio(%)는 해당 세션이 idle wait event 를 제외한 전체 대기 이벤트 중에서 해당 이벤트로 대기한 시간의 비율을 알려준다. Connected Time(hours)은 해당 세션이 접속되어 있는 시간을 나타낸다.

*Guide I/O 관련 대기 이벤트 중에서 흔히 나타나는 db file sequential read 와 db file scattered read 같은 대기 이벤트는 대기한 시간이 많더라도 직접적으로 문제가 있다고 판단할 수는 없으며 오래 접속되어 있는 세션은 I/O 관련 대기 이벤트의 대기 시간이 누적되어 클 수 밖에 없으므로 Connected Time을 고려해야 하며 다른 대기 이벤트와 비교하여 총 대기시간에서 차지하는 비율이 높은 경우에 문제가 있다고 볼 수 있다.

이들 대기 이벤트의 대기 시간이 과다한 것은 대부분 애플리케이션 문제이다. 대기 시간을 최소화하기 위해서는 SQL 문장을 튜닝하여 I/O 횟수를 줄여야 한다.

    

?  db file sequential read : 인덱스, 롤백(언두) 세그먼트, 테이블(ROWID에 의한 접근), 컨트롤 파일 및 데이타 파일 헤더에 대한 싱글 블록 I/O를 요청하는 SQL 문장에 의해 발생된다.

또한, 평균 대기 시간은 0.01초 (1 cs) 이하여야 한다. 평균 대기 시간이 1cs 이상이 걸린다면 I/O 서브 시스템에 문제가 있는 것은 아닌지 검토해 보아야 한다.

    

?  db file scattered read : 여러 개의 연속된 블록(초기화 파라미터 db_file_multiblock_read_count 파라미터에 정의된 값)을 디스크로부터 읽어들일 때 발생하는 대기 이벤트이다. 멀티 블록 I/O 요청은 full table scan 및 index fast full scan 수행과 관련이 있다.

    

?  direct path read : direct read operation과 관계가 있다. 오라클의 direct read operation은 SGA내의 버퍼캐쉬를 경유하지 않고 세션의 PGA로 직접 블록을 읽어 들이는 것이다.

    

?  direct path write : PGA의 데이타를 데이터 파일 또는 임시 테이블스페이스에 기록하는 direct writes operation과 관계가 있다. direct data load(append 힌트를 사용한 insert, CTAS), parallel DML시에 발생한다.

    

?  db file parallel read : db 복구 수행 시, 복구해야 하는 블록들을 여러 개의 데이타파일로부터 동시에 읽어들일 때 발생한다.

Session Wait Event (Lock)

Purpose 현재 접속중인 Session들의 Lock/Latch 관련 Wait Event정보를 확인한다.

Description 현재 접속중인 Session들의 Lock/Latch 관련 Wait Event정보이며, 세션이 접속된이후 누적된 값이다. v$session_event를 조회한다.

*Guide    

?  enqueue : 데이터베이스 리소스에 대한 순차적인 접근을 보장하기 위해 오라클은 enqueue 라는 공유메모리 구조를 사용한다. 다른 세션이 리소스에 대한 락을 소유하고 있는 경우, 해당 리소스에 대한 enqueue 락을 획득하려는 세션은 enqueue 대기 이벤트를 대기해야 한다.

    

?  latch free : 현재 다른 프로세스에 의해 사용되고 있는 래치를 획득하기 위해 대기할 때 발생하는 대기 이벤트이다. 가장 일반적인 래치들은 cache buffers chains, library cache, shared pool 래치이다.

    

?  buffer busy waits : 다른 세션에서 사용하고 있는 버퍼 캐쉬의 데이타 블록을 접근하려고 할 때 발생한다. SGA 내부의 버퍼를 읽거나 변경하려는 세션은 우선 cache buffers chains 래치를 획득한 후 필요한 버퍼 헤더를 찾을 때까지 버퍼 리스트를 스캔해야 한다. 그런 다음 버퍼 헤더에 대해서 shared 또는 exclusive 모드로 버퍼 락 또는 핀을 획득해야 한다.

일단 버퍼 헤더가 핀 되면, 세션은 cache buffers chains 래치를 릴리즈하고 버퍼에 대해 의도한 operation을 수행한다. 만일 핀을 획득하지 못하면, 세션은 buffer busy waits 이벤트를 대기한다.

    

?  library cache pin : 세션이 라이브러리 캐쉬 안에 있는 오브젝트를 변경하거나, 확인하려고 할때, 동시에 다른 세션에 의해 오브젝트가 변경되는 것을 막기 위해서 핀을 획득해야 하는데 이 때 발생하는 대기 이벤트이다. 오브젝트에 대한 DLL 명령을 수행할 경우 이 대기 이벤트가 많이 발생한다.

    

?  library cache lock : 라이브러리 캐쉬 안에 있는 오브젝트에 대한 동시 접근을 방지하거나, 상당히 긴 시간동안 dependency를 관리하거나, 라이브러리 캐쉬로 오브젝트를 적재하려는 세션은 해당 오브젝트 핸들에 대한 락을 획득해야 하는데 이 때 발생하는 대기 이벤트이다.

Session Wait Event (Latency)

Purpose 현재 접속중인 Session들의 Latency 관련 Wait Event정보를 확인한다.

Description 현재 접속중인 Session들의 Latency 관련 Wait Event정보이며, 세션이 접속된이후 누적된 값이다. v$session_event를 조회한다.

*Guide    

?  log file sync : 사용자 세션이 commit 이나 rollback에 의해서 트랜잭션을 완료한 후 다음 처리를 수행하기 위해서는 세션의 리두 정보가 LGWR에 의해 리두로그 파일에 기록되어야 한다. LGWR 프로세스가 리두로그 파일에 기록할 때 까지 대기하는 이벤트이다.

이 대기 이벤트의 주요 원인으로는 잦은 commit, 느린 서브 I/O 시스템, 로그 버퍼 크기가 너무 크게 설정된 경우이다.

    

?  log buffer space : log file sync 대기 이벤트와는 반대로 로그 버퍼 크기가 너무 작아서 리두 엔트리를 복사할 수 없을 때 log buffer space 대기 이벤트가 발생한다.

    

?  free buffer waits : 블록이 버퍼 캐쉬로 적재되기 전에 서버 프로세스는 해당 블록을 로드하기 위한 프리 블록을 획득해야 한다. 만일 LRU 리스트에서 임계치에 도달할 때까지 프리 버퍼를 찾을 수 없다면 서버 프로세스는 DBWR에게 dirty buffer를 디스크에 기록하여 free buffer를 만들어 줄 것을 요청하고 자신은 free buffer waits 대기 이벤트를 대기한다.

    

?  write complete waits : DBWR 프로세스가 디스크에 기록중인 블록을 서버 프로세스가 변경하려고 할 때 발생한다. 이 대기 이벤트의 원인으로는 느린 I/O 서브 시스템, 너무 짧은 MTTR 이 될 수 있다.

    

?  log file switch completion : 이 대기 이벤트로 장시간 대기하였다면, 생성되는 리두 엔트리의 양에 비해서 리두 로그 파일이 너무 작아서 많은 로그 스위치가 발생된다는 것을 의미한다. 로그 파일의 크기를 늘리도록 한다.

    

?  log file switch (checkpoint incomplete) : log file switch completion과 동일한 원인으로 발생되며 체크포인트가 완료되지 않아서 로그 파일에 더 이상 기록할 수 없을 때 발생한다. 로그 파일을 더 큰 것으로 교체하거나 로그 그룹을 더 늘려야 한다.

WorkLoad Breakdown

Purpose 세션별로 서비스 시간과 Wait 시간의 비율 및 서비스 시간의 구성현황을 점검한다.

Description 세션별로 소모한 전체 CPU Time과 대비하여 Idle Event를 제외한 Wait Event에 대한 Wait Time의 비율을 보여준다. 또한 CPU Time을 Parse Time, Recursive Time, Other Time으로 구분하여 각각의 작업에 소모된 CPU Time 비율을 보여준다. [Service Time], [Wait Time]은 cs(Centi Second, 1/100초) 단위이다.

*Guide 서비스 시간(CPU TIme)에 대한 Wait 시간의 비율은 시스템의 특성에 많이 좌우되므로 명백한 기준은 없으며, 다만 서비스 시간에 비해 Wait 시간이 지나치게 많다면 어떤 Wait Event에 대하여 Wait가 많이 발생하였는지 점검하여 해당 Wait Event를 최소화하는 방안을 찾아야 한다.

또한 CPU TIme 가운데 Parse Time이 차지하는 비율이 높다면(일반적으로 20%이상) Literal SQL이 많이 수행되고 있다고 판단할 수 있으므로 해당 세션에서 수행되는 SQL에 대한 튜닝을 수행할 필요가 있다. [Service(Other)]는 Free Buffer를 찾고, Locking하고, 대상 레코드를 Fetch하는 등의 작업에 사용되는 CPU소모량으로서 일반적으로 CPU Time의 80% 이상을 유지해야 하며, 이 비율이 낮으면 SQL을 점검한다.

Buffer Busy Wait Ratio

Purpose Buffer Busy Wait 비율을 확인한다.

Description 이 대기 이벤트는 buffer cache에 여러개의 프로세스가 동시에 Access 중인 buffer들이 있다는 것을 의미한다. 각각의 버퍼 클래스를 위한 대기 통계 정보를 확인하기 위해 v$waitstat를 조회해야 한다. buffer busy waits 가 주로 발생하는 버퍼 클래스는 데이타 블록, 세그먼트 헤더 블록, 언두 헤더, 언두 블록이다.

Criteria 5.0000 % Maximum

Buffer Busy Wait Ratio(%)

0

*Guide Buffer Busy Wait Ratio가 5% 이상일 경우에는 Block level 경합이 있다. 이 경우 v$waitstat를 이용하여 더욱 상세하게 Block type별 정보를 검사하도록 한다.

Undo(Rollback) block/header 경합이 발생시 Undo(Rollback) 관리를 수동으로 하고 있는 경우 롤백 세그먼트 추가를 고려한다.

Data Block에 경합을 유발하는 문장이 쿼리면 SQL문을 튜닝하여 logical reads와 physical reads의 수를 줄이도록 하고 high concurrency 테이블에 대해 복수의 freelist 를 할당한다.

Data Block에 경합을 유발하는 문장이 DML이라면 파티셔닝 방법을 변경하거나 블록당 레코드 수를 줄여야 한다. 오라클 버전이 9i 이상이라면 작은 블록 사이즈를 가진 테이블스페이스로 오브젝트를 이동시켜야 한다.

Data Header Block에 경합이 발생한다면 오브젝트의 freelists와 freelist group의 개수를 증가시켜야 한다. pctfree와 pctused 간의 간격을 좁게 하지 말아야 한다. Next Extent 크기를 너무 작게 하지 말아야 한다. 오라클 9i 이상이라면 ASSM(Automatic Segment Space Management)의 사용을 고려해야 한다.

Block Waits

Purpose 블록 클래스별로 Wait 발생내역을 확인한다.

Description 블록 클래스별로 발생한 총 Wait횟수와 총 Wait시간을 점검한다. timed_statistics 파라미터가 TRUE 로 설정되어 있는 경우에만 이 값이 업데이트된다. 

*Guide Wait가 심하게 발생한 블록 클래스가 존재할 경우 해당 클래스의 특성에 따라 원인을 분석하여 조치하여준다. 예를 들어, 'data block' 클래스에 대한 수치가 높다면 동시 Insert가 많은 세그먼트를 찾아 freelists를 증가시켜주는 것을 고려해볼 수 있으며, 'segment header'나 'free list' 항목이 높다면 경합이 잦은 세그먼트를 찾아 freelist groups를 증가시켜주는 것을 고려할 수 있다. 또한 'undo header'나 'undo block' 항목에 Wait가 많다면 롤백 세그먼트에 대한 경합이 존재함을 의미하므로 롤백 세그먼트의 갯수를 증가시켜 주는 것을 고려할 수 있다.

일반적으로 wait가 빈번하게 발생하는 클래스는 'data block'인데, 이 경우 wait 횟수가 전체 request 횟수(db block gets + consistent gets)의 1%를 넘지 않으면 정상이라고 판단할 수 있다.

Shared Pool

Library Cache Get Hit Ratio

Purpose 라이브러리 캐쉬의 [Get] 적중율을 확인한다.

Description 오라클은 SQL 파싱 작업에 따른 CPU를 비롯한 자원사용을 최소화하고, SQL의 실행시간의 단축과 메모리 절약 등의 목적을 위하여 SGA 가운데 일부인 Shared Pool내에 SQL을 공유하고 있는데, 이 영역을 라이브러리 캐쉬라고 한다. 여기서 라이브러리 캐쉬의 Get Hit Ratio는 gethits 값을 gets 횟수로 나눈 비율로 계산된다.

Criteria 95.0 % Minimum

Time Library Cache Get Hit Ratio

2016/04/05 14:03:35 99.625

*Guide 라이브러리 캐쉬의 [Get] 적중율은 90~95% 이상을 유지할 것을 권장한다. 이 값이 권장값보다 낮을 경우에는 초기화 파라미터 shared_pool_size를 사용하여 Shared Pool의 크기를 증가시켜 개선시킬 수 있다. 더욱 효과적인 것은, 사용되는 SQL의 공유 정도를 높이기 위해 Literal SQL(조건절에 상수값을 사용하는 SQL)을 바인드 변수를 사용하도록 수정해 주는 것이다.

Library Cache Hit Ratio (Namespace)

Purpose Namespace별 라이브러리 캐쉬의 적중률을 점검한다.

Description [Gets]는 Library Cache Lock을 요청한 횟수이며, [Pins]는 Library Cache Pin을 요청한 횟수이다. Library Cache Lock은 라이브러리 캐쉬내에서 원하는 오브젝트(또는 SQL커서)를 찾아가는 과정에서 요청되며 Library Cache Pin은 찾아낸 오브젝트(또는 SQL커서)의 내용을 접근하거나 변경하기 위해 요청된다. Lock은 라이브러리 캐쉬 오브젝트의 핸들을, Pin은 라이브러리 캐쉬 오브젝트의 데이터영역(heap)을 보호한다. Pin은 Lock을 획득한 후 획득할 수 있으며 상대적으로 짧은기간 지속된다. Lock을 잡고있는 동안은 다른 프로세스가 해당 라이브러리 캐쉬 오브젝트에 접근할 수 없고, pin을 잡고 있는 동안은 해당 오브젝트는 ageout되지 않는다. [Invalidations]는 오브젝트의 구조가 변경되어 오브젝트 또는 SQL커서가 Invalid 되거나 SQL커서가 참조하는 오브젝트에 Analyze가 수행되어 Invalid 된 횟수이다. 

*Guide 모든 Namespace별 적중률이 90% 이상을 유지할 것을 권장한다. 특히, SQL AREA Namespace의 적중률이 낮을 경우에는 빈번히 사용되는 SQL 가운데 Literal SQL이 있는지 조사하여 바인드 변수를 사용하도록 수정하여 주는 것이 좋다. 바인드 변수를 사용할 경우 히스토그램 정보를 이용할 수 없게 되며 문자상수를 사용할 때와 다른 실행계획이 나올 수도 있다. Reloads-to-Pins Ratio는 1% 이하여야 한다.

Dictionary Cache Hit Ratio

Purpose 딕셔너리 캐쉬 전체의 적중률을 점검한다.

Description 딕셔너리 캐쉬는 데이터 딕셔너리 정보를 캐쉬시켜 두는 영역으로 Shared Pool에서 할당한다. 딕셔너리 캐쉬의 크기를 사용자가 따로 지정할 수는 없으며, 오라클은 Shared Pool가운데 딕셔너리 캐쉬를 우선적으로 할당하므로 라이브러리 캐쉬의 적중률이 양호하다면 당연히 딕셔너리 캐쉬의 적중률 또한 양호할 것이다. 한가지 고려할 점은, 인스턴스가 기동된 지 얼마간의 시간이 지난 후에야 이 값이 의미가 있다는 점이다.

Criteria 90.0 % Minimum

Time Dictionary Cache Hit Ratio

2016/04/05 14:03:35 99.581

*Guide Dictionary Cache의 적중률은 90%이상을 유지할 것을 권장한다. 적중률이 기준값 이하일 경우에는 초기화 파라미터 shared_pool_size를 변경하여 Shared Pool의 크기를 증가시켜 적중률을 높일 수 있다. 

Dictionary Cache Hit Ratio (Parameter)

Purpose Parameter별 딕셔너리 캐쉬의 적중률을 점검한다.

Description 딕셔너리 캐쉬내에서 오브젝트별로 적중률을 점검하고, 문제가 되는 파라미터 항목에 대하여 원인분석 및 조치를 수행한다.

*Guide 파라미터별 적중률이 각각 90%이상을 유지할 것을 권장한다. 유난히 적중률이 낮은 파라미터 항목에 대하여 각각의 특성에 따라 원인을 판단하여 볼 필요가 있다. 예를 들어, dc_free_extents, dc_used_extents의 적중률이 낮다면 저장공간의 실시간 할당이 잦다는 것을 의미하므로 오브젝트의 저장옵션을 검토하거나, Locally Managed tablespace의 운영을 검토해볼 수 있으며, dc_sequence가 문제되는 경우는 Sequence의 Cache 사이즈를 증가시키는 방안을 검토해볼 수 있고, dc_synonym의 적중률이 비정상적으로 낮다면 많은 유저가 각자의 Private Synonym을 사용하고 있지 않은지 점검해볼 필요가 있다. 

Shared Pool Free Space

Purpose Shared Pool내의 Free Memory 크기를 확인한다

Description Shared Pool은 라이브러키 캐쉬, 딕셔너리 캐쉬, UGA 영역 등으로 구성된다. 여기에서 알 수 있듯이 Shared Pool은 기본적으로 캐쉬를 위한 영역이므로 여유공간이 남아있는 한 기존 오브젝트의 ageout 없이 계속 소모하려는 속성이 있다. 따라서 라이브러리 캐쉬나 딕셔너리 캐쉬의 적중률이 양호하다면 일반적인 운영시간대에 free memory 크기가 낮은 상태를 유지하는 것은 정상이다. 오히려, 항상 큰 free memory가 남아 있다면 Shared Pool이 불필요하게 크게 설정되어 있다는 것을 의미하며, 일시적으로 갑자기 free memory 크기가 증가하는 경우는 Fragmentation이 많이 발생하여 많은 오브젝트가 한꺼번에 flush된 것을 의미하므로 문제를 겪고 있다는 증상일 수 있다.

일반적으로 Shared Pool에서 문제가 되는 것은 시간이 지남에 따라 메모리 영역이 작은 조각으로 분할(Fragmentation)되어 사이즈가 큰 오브젝트의 로드를 위한 memory할당에 어려움을 겪거나 심하게는 필요한 연속된 메모리 공간의 확보에 실패하여 ora-4031에러가 나는 경우이다. 특히, MTS로 운영되는 경우 Large Pool을 설정하지 않으면 UGA 영역이 Shared Pool에 할당되므로 Fragmentatio이 발생할 가능성이 더 높다. 오라클은 크기가 큰 오브젝트(5000 bytes 이상)들을 위해서 기본적으로 shared pool의 5% 정도를 따로 확보해서 관리하는데, 이 크기는 shared_pool_reserved_size 파라미터로 조정할 수 있다.

Shared Pool Total(MB) Shared Pool Free(MB) Shared Pool Reserved(MB) Shared Pool Other(MB)

6,002.25 1,638.45 Free : 420.33, Used : 2.21 3,941.26

*Guide Shared Pool 메모리의 조각화를 예방하기 위한 조치로는 다음과 같은 방법이 있다.

    

?  사이즈가 큰 PL/SQL 블럭의 사용을 자제한다.

    

?  불가피하게 사이즈가 큰 PL/SQL 오브젝트는 DBMS_SHARED_POOL.KEEP을 사용하여 KEEP한다.

    

?  Literal SQL을 바인드 변수를 사용하도록 수정해 준다.

    

?  shared_pool_reserved_size의 증가를 고려한다.

    

?  MTS의 경우 Large Pool을 설정하여 Multi-Threaded Server(9i이상에서는 Shared Server)를 위한 사용자 세션 정보와 커서 상태 정보를 보관하고 있는 UGA영역이 Shared Pool에 생기지 않도록 한다.

Shared Pool Advice

Purpose Shared Pool에 대하여 오라클이 제공하는 Advice를 확인한다.

Description Shared Pool 크기에 따라 적재되는 오브젝트의 수와 예상되는 parse time을 보여준다. 그 크기는 현재의 Shared Pool 크기 혹은 핀된(pinned) 라이브러리 캐시 메모리 양의 10% 에서 200% 까지의 범위를 일정한 간격으로 보여준다. 그 간격은 현재의 Shared Pool 크기에 달려 있다.

*Guide Shared Pool 크기가 증가함에 따라 예상되는 Saved Time과 Object Hits 값이 효율적으로 증가한다면 Shared Pool의 크기를 늘리는 것을 고려해야 한다.

Buffer Cache

Buffer Cache Hit Ratio

Purpose 데이터베이스 버퍼캐쉬의 적중률을 점검한다.

Description Buffer Cache의 기본적인 기능은 여러 프로세스에 의해 공통으로 자주 액세스 되는 데이터베이스 블록을 메모리에 캐쉬하여 물리적인 디스크 I/O를 최소화함으로써 더 빠른 액세스 속도를 제공하기 위한 것이다. 버퍼캐쉬의 적중률이란 애플리케이션이 액세스한 메모리 블럭 가운데 이미 캐쉬가 되어 있어 물리적 I/O 없이 액세스 할 수 있었던 블럭의 비율을 나타낸다. 만약 데이터베이스 버퍼의 적중률이 권장값 미만일 경우에는 할당된 버퍼 캐쉬의 크기가 너무 적거나, 또는 지나치게 많은 I/O를 유발하는 애플리케이션이 존재한다는 것을 의미한다.

Criteria 90.0 % Minimum

Time Logical Reads Physical Reads Physical Reads Direct Physical Reads Direct (LOB) Buffer Cache Hit Ratio

2016/04/05 14:03:35 279,493,372,082 5,014,500,570 3,217,304,047 26,165,122 99.366

*Guide 데이터베이스 버퍼캐쉬의 적중률은 OLTP, DSS, DW등 데이터베이스를 사용하는 애플리케이션의 특성에 따라 권장값이 다르다. 일반적으로, OLTP시스템은 90%이상은 되어야 하고 DSS, DW 시스템은 80~85%이상이면 좋다고 할 수 있으나, 순수하게 배치(Batch) 처리업무 위주의 시스템인 경우는 50%를 밑돌더라도 그 자체로 문제가 된다고 단정할 순 없다.

데이터베이스 버퍼캐쉬의 적중률이 기준값보다 낮은 경우에는 8i 이하일 경우는 초기화 파라미터 db_block_buffers의 값을 늘려 버퍼캐쉬의 크기를 증가시킬 수 있다. 9i 이상의 경우에는 db_cache_size 파라미터의 값을 늘리면 된다. 이때 O/S상에 메모리가 여유 있는지 확인 하여야 하며 메모리 부족으로 Paging, Swapping이 발생하지 않는 한도내에서 증가시켜 주어야 한다. 그러나 디스크상의 모든 데이터 블럭을 캐쉬시키는 것은 현실적으로 불가능하므로 일반적으로 버퍼캐쉬의 증가가 최선의 해결책이 될 수는 없다. 특히, 다량의 디스크 블록을 빈번히 Full Scan하는 애플리케이션이 존재한다면 적중률을 높은 수준으로 유지하는 것은 불가능하므로 액세스 패턴이 비효율적인 애플리케이션을 추출하여 지속적인 튜닝을 수행해 주어야 한다.

Free Buffer Scan Ratio

Purpose Free Buffer Scan 비율을 확인한다.

Criteria 5.0000 % Maximum

Free Buffer Scan Ratio

71.72

*Guide 이 값이 5% 이상일 경우 DBWR는 사용자 프로세스들이 요청하는 작업에 대한 병목현상을 보이고 있다는 것이므로 복수의 DBWR 사용이나 async I/O 를 검토할 수 있다.

Buffer Cache Advice

Purpose 버퍼 캐시 크기에 대한 오라클의 Advice을 확인한다.

Description 버퍼 캐시 크기를 변경함에 따라 예상되는 물리적인 I/O 활동을 시뮬레이션하여 보여준다. Size Factor가 1 인 row의 Cache Size(MB)가 현재 버퍼 캐시 크기이다.

*Guide 이 데이타를 얻기 위해서는 DB_CACHE_ADVICE 초기화 파라미터의 값을 ON 으로 설정하여야 한다. DB_CACHE_ADVICE 파라미터는 동적으로 변경이 가능하다. 예상되는 물리적 읽기 값(Estd Phys Reads)이 현저히 줄어드는 크기까지 Buffer Cache 사이즈를 늘려주는 것이 좋다.

Sort

Memory Sort Ratio

Purpose 정렬작업이 메모리에서 수행된 비율을 확인한다.

Description Order by, Group by 구문처리, Join수행, Index 생성 등을 처리하기 위해서는 정렬작업을 필요로한다. 이러한 정렬작업은 가능한 메모리(Sort Area)내에서 수행되는 것이 바람직하나, 처리량이 많아 메모리에서 수행될 수 없는 경우 오라클은 Temporary Segment를 사용한다.

Criteria 99.0 % Minimum

Sort(memory) Sort(disk) Memory Sort Ratio(%)

1,347,069,645 912 100

*Guide 메모리에서 정렬작업을 수행한 비율이 99%가 넘지 않을 경우에는 init.ora의 sort_area_size를 증가한다. 보통의 경우 OLTP 시스템에서는 500K ~ 10M 까지 지정하고 DW 시스템에서는 1M ~ 50M 까지 지정한다. 또한 sort_area_retained_size는 sort_area_size의 1/2이나 1/4로 지정한다.

9i 이상의 경우에는 workarea_size_policy 파라미터의 값이 auto로 설정된 경우 pga_aggregate_target 파라미터의 값을 증가시키면 된다.

주의 : 이 메모리 공간은 SGA내에 존재하는 것이 아니라 각각의 프로세스별로 할당되기 때문에 O/S의 전체 메모리의 여유가 있을 경우에 증가시킨다.

Rows per Sort

Purpose 한 번의 소트작업으로 처리된 건수를 확인한다.

Criteria 30.0000 Maximum

Rows per Sort

22.64

*Guide 이 비율은 특히 OLTP 시스템에서는 낮아야 한다. (가능한 10~30 이하) DSS 시스템에서는 적정 수준의 비율을 유지해야 한다.

Sort Segment Usage

Purpose Sort Segment의 사용량을 보여 준다.

Description 앞서 언급한 바와 같이, 정렬대상이 sort_area_size보다 클 경우에 오라클은 디스크상의 Temporary 영역을 사용하게 되는데, 이때 생성하여 사용하는 것이 Sort Segment이다. v$sort_segment를 조회한다.

Tablespace Name Extent Size Current Users Total Exts Used Exts Free Exts Max Used Exts

TEMP 128 310 39,998 310 39,688 39,998

*Guide Sort Segment의 최대 사용량, 현재 사용량등을 확인하여 sort segment를 저장하는 Tablespace의 공간 예측이 가능하다.

PGA Statistics

Purpose 인스턴스 레벨에서 PGA 사용 현황을 확인한다.

Description PGA 사용과 자동화된 메모리 관리자의 인스턴스 레벨에서의 통계를 확인한다.

*Guide aggregate PGA auto target 은 오라클이 automatic 모드로 실행중인 작업 영역을 위해서 사용할 수 있는 PGA 메모리 양이다. 이것은 PGA_AGGREGATE_TARGET 파라미터 값과 현재 작업 영역 부하 정도에 따라 동적으로 결정된다.

그러므로, 이 값은 끊임없이 오라클에 의해서 조정된다. 만약, 이 값이 초기화 파라미터 PGA_AGGREGATE_TARGET 값보다 작다면, 많은 PGA 메모리가 시스템의 다른 컴포넌트에(예를 들어, PL/SQL 혹은 Java 메모리) 의해 사용되고 있으며 정렬을 위한 작업 영역을 위해서는 적게 사용되고 있다는 것을 의미한다. automatic 모드로 실행 중인 작업 영역에 충분한 PGA 메모리가 남도록 해야 한다.

또한, session pga memory max 의 값이 PGA_AGGREGATE_TARGET 값보다 크면 PGA_AGGREGATE_TARGE 파라미터의 값을 늘리는 것을 고려해야 한다. 이 파라미터는 ALTER SYSTEM 문장으로 변경될 수 있다.

SQL Workarea Statistics

Purpose SQL Workarea의 실행통계를 확인한다.

Description 인스턴스가 시작된 이후 PGA 영역 중 SQL Workarea 에서 optimal, onepass, multipass 모드로 실행된 횟수와 퍼센트를 확인한다.

Profile Count Percentage

workarea executions - optimal 1,203,159,227 100

workarea executions - onepass 25,693 0

workarea executions - multipass 0 0

*Guide multipass 실행 횟수가 0 이 아닌 경우에는 PGA_AGGREGATE_TARGE 파라미터의 값을 늘리는 것을 고려해야 한다. onepass 실행이 optimal 실행보다 훨씬 많다면, PGA_AGGREGATE_TARGET 파라미터를 증가시켜야 한다. 이 파라미터는 ALTER SYSTEM 문장으로 변경될 수 있다.

PGA Target Advice

Purpose 최적의 PGA_AGGREGATE_TARGET 값을 결정하기 위해 오라클이 제공하는 Advice를 확인한다.

Description workarea_size_policy 초기화 파라미터의 값이 AUTO 일때 초기화 파라미터 pga_aggregate_target 값을 변경할 경우 예상되는 PGA CACHE HIT RATIO를 예상하여 보여준다. Target Factor가 1인 row의 Target Size(MB)가 현재 PGA 크기이다.

Target Size(MB) Target Factor PGA Cache Hit(%) Over-allocation Count

256 0.125 76 197,284

512 0.25 76 193,764

1,024 0.5 79 154,977

1,536 0.75 88 66,370

2,048 1 99 1,802

2,458 1.2 100 0

2,867 1.4 100 0

3,277 1.6 100 0

3,686 1.8 100 0

4,096 2 100 0

6,144 3 100 0

8,192 4 100 0

12,288 6 100 0

16,384 8 100 0

*Guide pga_aggregate_target은 over allocation count가 발생하는 메모리 크기로 설정하는 것을 피해야 한다. PGA Cache 적중율은 60% 이상이어야 한다.

Redo Contention

Redo Entry Average Size

Purpose Redo Entry 평균 크기를 확인한다.

Redo Entry Average Size(bytes)

411.6

*Guide Redo Entry 평균 크기는 Redo Log File 크기와 Checkpoint 주기를 최적화 하는데 유용한 판단 자료로 활용한다.

Log Space Request Ratio

Purpose Log Space Request Ratio(%)를 확인한다.

Description Redo Log Buffer는 일반적으로 큰 문제를 유발하는 영역은 아니나, 경합이 존재할 경우 데이터베이스 성능 전체의 병목구간으로 작용할 수 있다. Space Request 이벤트를 감소시키기 위해서는 redo log buffer를 늘리거나, 생산되는 Redo의 양을 줄이거나 LGWR의 작업효율을 높여주어야 한다.

Criteria 0.0100 % Maximum

Log Space Request Ratio(%)

0.000147

*Guide Log Space Request Ratio가 0.01% 보다 크고 계속적으로 증가한다면 redo log buffer가 너무 작다는 것이므로 initSID.ora 파일의 log_buffer를 증가시켜야 한다. 로그 버퍼 크기는 대략 1~10M 로 한다.

Log Buffer Retry Ratio

Purpose Log Buffer Retry Ratio(%)를 확인한다.

Description Log Buffer Retry Ratio는 redo entry가운데 log buffer space를 할당받기 위해 Wait한 비율을 의미하며 Redo Log Buffer는 일반적으로 큰 문제를 유발하는 영역은 아니나, 경합이 존재할 경우 데이터베이스 성능 전체의 병목구간으로 작용할 수 있다.

Criteria 0.0100 % Maximum

Log Buffer Retry Ratio(%)

0.002429

*Guide Log Buffer Retry Ratio가 0.01% 보다 크고 계속적으로 증가한다면 redo log file의 크기를 늘려주는 것을 고려할 수 있다.

Redo Log Switch Count(Hourly)

Purpose 시간별 Redo Log Switch의 횟수를 확인한다

Description 최근 3일간에 대하여 매시간별 발생한 Log Switch의 발생횟수를 보여준다.

*Guide Log Switch 횟수로 데이타베이스의 Transaction양을 예측할 수 있으며 하루 중에 가장 Transaction이 많은 시간을 파악할수 있다. 이렇듯 많은 Transaction이 발생하는 시간에는 대량의 Batch Job을 가능한 한 다른시간에 수행토록 하는 것이 바람직하다.

Redo Log Switch Count(Daily)

Purpose 일자별 Redo Log Switch의 횟수를 확인한다.

Description 최근 15일간에 대하여 매일 발생한 Log Swtich의 발생횟수를 보여준다.

*Guide redo log의 Switch 횟수로 데이타베이스의 Transaction양을 예측할 수 있으며 한달중에 가장 Transaction이 많은 날짜을 파악할수 있다.

Redo Log Switch Time Interval

Purpose Redo Log Switch의 Time Interval을 확인한다. 

Description 최근 3일간에 대하여 Log Switch가 발생한 간격을 시간당 평균수치로 보여준다.

Criteria 10.0 min Minimum

*Guide Redo Log Switch의 Time Interval이 10분 이하로 계속된다면, redo log file의 size를 증가시켜서 redo log switch가 너무 자주 발생하지 않도록 하여 checkpoint에 의한 I/O를 예방할 수 있다, 또한 생략할 수 있는 데이터베이스내의 Transaction을 없애는 방식으로 작업을 하여 성능의 개선효과를 볼수 있다

예: CREATE TABLE AS SELECT … NOLOGGING, CREATE INDEX ..... NOLOGGING등.

Latch Contention

Latch Hit Ratio

Purpose SGA내의 Hit Ratio가 낮은 순서대로 상위 10개의 Latch에 대한 Hit Ratio를 확인한다.

Description (I)Gets, (I)Misses 항목은 각각 Gets(또는 Immediate gets)와 Misses(또는 Immediate misses) 값을 의미하며,Gets와 Immediate gets 가운데 큰 값을 보여준다. Gets는 Willing-to-wait 모드로 요청된 경우, Immediate gets는 No-Wait 모드로 요청된 경우인데, Willing-to-Wait 모드는 latch획득에 실패하는 경우 spin과 sleep을 하면서 획득에 성공할 때까지 재시도하는 것이며, No-Wait 모드는 원하는 latch 획득에 실패하는 경우 해당 latch를 위해 Wait하지 않는다. redo copy latch처럼 동일한 기능의 latch가 여러 개인 경우, 또는 deadlock을 피하기 위해 No-Wait 모드가 사용된다. redo copy latch를 제외하면 모든 latch들은 대부분 Willing to Wait 모드로 요청된다. 

*Guide 모든 Latch의 Hit Ratio는 98% 이상이 되어야 한다. 각각의 Latch별로 Hit Ratio가 좋지 않은 것은 각각의 Latch에 대한 Resource를 늘려주거나, 해당 Latch를 많이 사용하는 Application의 사용 정도를 감소시킨다.

이 중에서 DBA가 관심을 가져야 하는 Latch는 다음과 같다.

    

?  shared pool latch

    

?  library cache latch

    

?  cache buffers chains latch

    

?  cache buffers lru chain latch

    

?  redo allocation latch

    

?  redo copy latch

Shared Pool Latch Hit Ratio

Purpose Shared Pool Latch Hit Ratio 를 확인한다.

Criteria 99.0 % Minimum

Shared Pool Latch Hit Ratio

99.966

*Guide 이 래치는 Shared Pool의 메모리 할당을 보호하기 위한 래치이다. 이 래치에 대한 경합은 바인드 변수를 사용하지 않거나 cursor cache를 사용하지 않아서 SQL,PL/SQL 문장이 재사용되지 않고 있다는 것을 의미한다. 이 래치 문제를 해결하기 위해서는 Shared Pool 혹은 쿼리를 튜닝하여야 한다.

Shared Server를 사용하는 경우 Large Pool을 사용하지 않으면 UGA 영역이 Shared Pool에 잡혀서 이 래치 문제가 발생할 수 있다.

Cache Buffers Chains Latch Hit Ratio

Purpose Cache Buffers Chains Latch Hit Ratio 를 확인한다.

Criteria 99.0 % Minimum

Cache Buffers Chains

99.927

*Guide cache buffers chains 래치는 서버 프로세스가 buffer를 변경하기 전에 잡아야 하는 래치로 복수 사용자에 의해 동시에 변경되는 것을 막아준다. 이 래치 경합은 특정 블록을 반복적으로 접근하고 있다는 것을 의미한다.

Cache Buffers LRU Chain Latch Hit Ratio

Purpose Cache Buffers LRU Chain Latch Hit Ratio 를 확인한다.

Criteria 99.0 % Minimum

Cache Buffers LRU Chain

99.98

*Guide cache buffers lru chain 은 버퍼 캐시의 LRU 리스트를 보호하기 위한 latch이다. buffer를 이 리스트에 옮기려면 일단 이 래치를 잡아야 한다. 이 래치는 dirty block이 디스크에 기록될 때 혹은 서버 프로세스가 기록할 블록을 검색할 때 필요하다.

이 래치 경합은 많은 캐쉬 기반 정렬, 부정확한 인덱스를 반복적으로 사용하는 비효율적인 SQL 또는 Full Table Scan 과 같은 버퍼 캐시 처리 능력 초과를 의미한다. 또한, DBWR가 데이타 블록의 변경 속도를 따라가지 못해서 free bufffer를 찾는 동안 서버 프로세스가 이 래치를 잡고서 오래동안 대기하는 경우도 있다.

Redo Allocation Latch Hit Ratio

Purpose Redo Allocation Latch Hit Ratio 를 확인한다.

Criteria 99.0 % Minimum

Redo Alloc Latch Hit Ratio

99.988

*Guide 이 래치는 리두 로그 버퍼에 리두 엔트리를 위한 리두 공간의 할당을 관리하기 위한 래치이다. 서버 프로세스가 redo log buffer를 할당받기 위해서는 redo allocation latch 획득하고 나서 redo copy latch를 획득하여야 한다. 단지, log_small_entry_max_size 보다 작은 엔트리들만 redo copy latch를 획득할 필요가 없다.

Redo Allocation Latch Hit Ratio 는 100% 에 가까워야 한다. 이 값이 낮다면 초기화 파라미터 log_small_entry_max_size 의 값을 작게 하여 Redo Allocation Latch 의 경합을 줄일 수 있다.

Redo Copy Latch Hit Ratio

Purpose Redo Copy Latch Hit Ratio 를 확인한다.

Criteria 99.0 % Minimum

Redo Copy Latch Hit Ratio

99.986

*Guide 이 래치는 리두 엔트리를 리두 로그 버퍼에 기록하는 데 사용된다. 서버 프로세스가 redo log buffer를 할당받기 위해서는 redo allocation latch 획득하고 나서 redo copy latch를 획득하여야 한다. 단지, log_small_entry_max_size 보다 작은 엔트리들만 redo copy latch를 획득할 필요가 없다.

Redo Copy Latch Hit Ratio 는 100% 에 가까워야 한다. 이 값이 낮다면 초기화 파라미터 log_simultaneous_copies 의 값을 증가시켜야 한다.

User Session

Current Session Count

Purpose 대상 데이타베이스에 접속되어 있는 모든 Session과 현재 작업중인 Session의 수를 확인한다.

Time Total Sessions Active Sessions

2016/04/05 14:03:36 559 40

Cursors opened per transaction

Purpose 트랜잭션당 오픈된 커서의 수를 확인한다.

Description Cursor는 SQL 영역에 대한 handle 또는 name을 나타내며, 문장을 처리하기 위한 parsed SQL문장 및 관련 정보를 가지고 있다.

Criteria 10.0000 Maximum

Cursors opened per transaction

89

*Guide Transaction 당 Cursor Open 수는 OLTP 시스템에서는 가능한 5 ~ 10 정도를 유지하게 하고 Batch 작업의 경우는 Application 설계에 종속적이다.

Recursive to User Call Ratio

Purpose user call에 대한 recursive call의 비율을 확인한다.

Description Recursive call 이란 Dictionary Cache (rowcache) misses 나 Dynamic Storage Extension (테이블에 next extent 추가)를 나타낸다. 또한 SQL문 등의 실행에 대한 내부적으로 관련된 Constraints, Trigger, Index, Sequences 등의 내부 처리에 관여된 결과라 할 수 있다.

Criteria 10.0000 % Maximum

Recur to User Call Ratio(%)

553

*Guide 이 비율은 10% 이하로 유지되는 것이 효과적이다. 그렇지 않다면 사용되는 Application 내의 PL/SQL block 및 사용되는 각 SQL에 대한 검증이 필요하다.(불필요한 Binding/Define 등)

Parse Count per User Calls

Purpose User Call에 대한 parse count 비율을 확인한다.

Description User Call은 Kernel에 대한 System Call 을 의미한다. System에 대한 Call은 가능한 줄여야 한다. Application에서의 Array Processing 등이 Kernel에 대한 System Call을 줄이는 하나의 방법이 될 것이다.

Parse Count per User Calls

0

*Guide 이 비율은 User Call 당 구문 분석된 SQL 명령들의 수에 대한 비율로서 증가되는 추세일 경우는 SQL명령이나 PL/SQL Application 성능 측면에서 문제가 있다고 보아야 한다.

Parsing Rate

Purpose 전체 SQL Call 가운데 Hard Parse Rate 와 Session Cursor Cache Hit Ratio를 확인한다.

Description 동일한 커서로 3번이상 파싱되면 해당 SQL은 Session cache에 등록되어 이후에 동일 세션에서 요청되면 별도의 parsing(soft,hard parse)없이 바로 재사용된다. 이와 같은 session cache 기능을 사용하기 위해서는 초기화 파라미터 session_cached_cursors가 설정되어야 하며 일반적으로 50 이상의 값으로 설정하는 것이 좋다. 모든 SQL call은 세션 캐쉬로부터 재사용(session cursor cache hit, no parse), 라이브러리 캐쉬로부터 재사용(soft parse), 새로 파싱하여 사용(hard parse)의 세가지 가운데 하나이다. 

Criteria 30.0 % Minimum

Soft Parses(%) Hard Parses(%) No Parse(%)

15.588 0.184 84.228

*Guide 일반적으로 hard parse 비율은 10% 이하여야 하며 session cursor cache hit ratio는 30% 이상으로 유지되는 것이 바람직하다. 기준값 이하일 경우에는 init.ora의 session_cached_cursors의 값을 늘려 준다. 보통의 경우 크면 클 수록 좋으나 128 이하로 지정하여 튜닝한다.

Transaction Rate

Purpose 초당 Transaction 갯수를 확인한다.

Transaction Rate

2.14

*Guide 이 자료는 Database 성능을 검증해 볼 수 있는 측정 기준으로 확장 용량 예측과 부하 분산을 위한 근거 자료로 활용할 수 있다.

Access Type

Index Scan Ratio

Purpose Index Scan의 비율을 확인한다.

Description 항상 인덱스 스캔이 유리한 것은 아니지만 일반적으로 높은 인덱스 스캔비율을 유지하는 것이 바람직하다.

Criteria 90.0 % Minimum

Index Scan Ratio

17.24

*Guide 이 비율은 Full Table Scan에 대한 상대적 Index Scan의 이용도를 나타낸다. OLTP에서는 Index를 이용한 Row의 처리비율이 높아야 한다.

Chained Row Ratio

Purpose Chained Row를 Access한 비율을 확인한다.

Description Table Full Scan 또는 Index Scan에 의해 액세스된 모든 Row 가운데 Row Chaining에 의해서 추가로 액세스가 발생한 비율을 확인한다.

Criteria 1.0000 % Maximum

Chained row Access Index Access Table Scan Access Ratio

28,241,554,467 511,480,106,738 2,456,195,319,462 0.95164

*Guide Chained Row를 Access한 비율이 1% 이상의 값이 나올 경우에는 자주 사용되는 Object에 Chaining이 많이 발생하였음을 의미하므로 이를 Reorg하여서 Chaining을 없애주는 것이 좋다.

Top-20 SQL(by Buffer Gets)

Purpose 버퍼블록을 많이 읽은 상위 20개의 SQL을 찾는다.

*Guide Buffer Gets란 버퍼캐쉬에서 읽은 블록의 갯수를 의미한다. 과도한 논리적읽기는 버퍼캐쉬와 관련된 래치의 경합을 유발하며 buffer busy wait 이라는 대기 이벤트를 유발하기도 한다. 따라서, Buffer Gets가 많은 SQL을 튜닝하는 것은 단위 SQL의 응답시간을 줄일 수 있을 뿐만 아니라 시스템 공유 자원을 덜 소비함으로써 시스템 전체적인 성능향상을 기대할 수 있게 된다.

Top-20 SQL(by Buffer Gets per Execution)

Purpose 단위 실행당 읽은 버퍼블록이 많은 상위 20개의 SQL을 찾는다.

*Guide 모니터링 유형의 SQL은 단위 실행당 Buffer Gets는 낮지만 자주 실행되어 높은 Buffer Gets를 보일 수 있다. 따라서 실제로 튜닝이 필요한 SQL을 찾는데는 단위 실행당 Buffer Gets 가 높은 SQL을 찾는 것이 바람직 할 수 있다.

Top-20 SQL(by Disk Reads)

Purpose 디스크 I/O가 많은 상위 20개의 SQL을 찾는다.

*Guide 메모리에서 블록을 찾지 못하여 디스크에서 블록을 읽어들이는 물리적 I/O 가 높은 SQL은 튜닝의 대상이 되어야 한다. 이러한 SQL은 full table scan을 유발하는 경우가 많다. full table scan으로 메모리에 올라온 블록들은 버퍼 캐시의 공간이 부족할 경우 free buffer를 만들기 위해 DBWR에 의해 가장 먼저 다시 디스크에 쓰여져서 다시 실행될 경우 또 다시 물리적 I/O를 유발하기 때문이다.

Top-20 SQL(by Elapsed Time)

Purpose 응답시간이 오래 걸린 상위 20개의 SQL을 찾는다.

Description 사용자는 응답시간을 기준으로 판단하므로 사용자 관점에서 문제가 되는 SQL을 찾는 가장 편리한 방법이다.

Literal SQL

Purpose SGA내에 사용된 Literal SQL을 확인한다

Description OLTP 시스템의 경우 조회 조건의 일부만 변하면서 빈번히 수행되는 SQL이 많은데 이러한 SQL들이 Literal SQL로 작성되면 라이브러리 캐쉬 내에서 항상 다른 SQL로 인식되어 공유될 수 없으므로 수행 시마다 매번 Parsing을 수행해야 한다. 이와 같은 Literal SQL이 시스템 성능을 저하시키는 주범이 되는 경우가 많으므로 수시로 점검하여 적절한 튜닝작업을 수행해 주는 것이 좋다. 

*Guide Literal SQL을 추출하여 가능한 바인드 변수를 사용하도록 수정해 주어야 한다.

I/O

File Activity (Top-30)

Purpose 디스크 I/O 가 많은 상위 30개 데이터 파일의 I/O 정도 및 시간을 확인한다.

Description Read(%), Write(%), Total IO(%)는 데이터베이스내의 모든 파일에 대한 Read, Write, Read+Write 가운데 해당 파일이 차지하는 비율의 의미한다.

*Guide 평균 I/O 시간이 긴 데이터 파일에 대해서는 해당 데이터 파일이 위치한 Disk의 성능을 고려해 봐야 하며, 많은 데이터 파일이 같은 위치에 있으므로 해서 발생하는 I/O 병목현상이 발생하는지를 확인해야 한다. 또한, I/O가 많이 발생하는 데이터 파일은 여러 device에 분산하도록 권유하고 있으나 RAID-5가 보편화된 요즘에는 큰 의미는 없다.

데이타 파티셔닝을 고려해 보아야 하며 row migration/chaining 정도를 조사해 보아야 한다.

Tablespace Activity (Top-30)

Purpose full table scan 이 많은 상위 30개의 테이블스페이스를 확인한다.

Description full table scan 이 많이 발생되는 테이블스페이스 순으로 상위 30개를 보여준다. Blocks/Read는 한번의 I/O 동안에 평균적으로 읽은 Block 수를 나타낸다.

*Guide Blocks/Read 값은 index scan 이 많은 경우 1에 근접한 수치를 나타내며 full table scan 이 많은 경우 InitSID.ora 의 db_file_multiblock_read_count 초기화 파라미터 값에 근접한 수치가 나온다.

Storage

Objects With Too Many Extents

Purpose 데이타베이스 내에 Extent가 많이 발생한 Object을 확인한다.

Description Extent가 50개 이상 발생한 오브젝트 가운데 Top-N개를 보여준다. Extents의 수가 많은 것이 반드시 심각한 성능저하로 연결되지는 않지만 Extent의 잦은 실시간으로 할당으로 인해 성능상 이롭지 못한 것은 분명하다. 주기적인 Extent 발생현황의 점검과 오브젝트별 적절한 저장옵션 설정작업을 수행하여 주는 것이 바람직하다. 

*Guide Extent가 지나치게 많이 발생한 오브젝트에 대해서는 Next Extents의 크기를 데이터 증가수준에 맞게 조정하여 자주 Extent가 발생하지 않도록 하고, 필요한 경우 Reorg작업을 수행한다.

Tablespace Usage and Fragmentation

Purpose 테이블 스페이스의 사용현황과 Fragmentation을 점검한다

Description 테이블 스페이스의 총 할당량과 사용량을 점검하고, Free Space의 총 크기와 Max Free Extent의 크기, Free Extent 갯수 등 Fragmentation 내역을 확인한다. 테이블 스페이스가 Fragmentation이 많이 발생했을 경우에는 새로운 Extent를 생성할 때 남은 공간이 있음에도 연속적인 크기의 Free 공간이 없어서 Error가 발생할 수 있다. 

*Guide 만약 Free space들이 서로 연속되어 있지 않아 병합이 불가능한 상태일 경우에는 Object를 Export한 후 Coalesce하고 다시 import하여 Fragmentation을 해소 할 수 있다. 바람직한 것은 Fragmentation을 사전에 예방하는 것이며, 이를 예방하기 위해서 테이블 스페이스 단위로 생성되는 오브젝트의 extent의 크기를 단계별로 1M, 10M, 50M, 100M등으로 정하여 생성하는 방법도 있다. 테이블스페이스와 세그먼트의 물리적 저장상태는 오렌지의 Space Manager를 사용하여 쉽게 확인할 수 있다.

Rollback Segment Hit Ratio

Purpose Rollback Segment의 Hit Ratio를 확인한다

Description Rollback Segment의 갯수보다 동시 Transaction의 수가 많은 경우 Rollback segment의 할당시 Waiting이 발생하므로 Online Rollback Segment의 개수를 늘려주어야 한다.

Criteria 99.0 % Minimum

*Guide Rollback Segment의 개수는 일반적으로 아래의 공식에 따라 설정하되 최대 50개를 넘지 않는 것이 좋다.

N < 16 --> 4 rbs

16 <= N <= 32 --> 8 rbs

32 < N --> N/4 rbs

( N: 동시 트랜잭션의 수 )

또한, 롤백 세그먼트는 Online 트랜잭션 용과 배치 작업용으로 구분해서 생성하는 것이 좋다. Online 트랜잭션 용으로는 대부분 initial 1M, next 1M 이면 충분하며 배치 작업용은 작업의 양에 따라 크기를 크게 생성한다. 물론 배치작업을 수행 할 때는 배치 작업용 롤백세그먼트를 사용토록 트랜잭션의 시작 부분에서 'set transaction use rollback segment big_rbs01;'를 먼저 수행하고 배치작업을 수행한다

User Objects in SYSTEM Tablespace

Purpose System Tablespace내의 일반 User의 Object를 확인한다

Description System Tablespace는 데이타베이스 운영을 위해 오라클에 의해 빈번하게 사용되는 영역이므로 일반 유저의 Object는 다른 Tablespace로 옮겨야 한다.

*Guide SYSTEM 테이블스페이스에 저장된 오브젝트들은 Export를 한후 새로운 Tablespace에 Create하고 데이터를 Import하거나 'Alter table move …' 등의 명령어를 이용하여 옮긴다.

Validity

Invalid Object

Purpose 오브젝트의 유형별로 Invalid상태인 오브젝트의 소유자와 이름을 확인한다.

Description Invalid Object가 생기는 원인은 다양한데, 일반적으로 권한이나 Synonym의 누락에 의해 발생하거나, 뷰 또는 프로시져, 트리거 등이 참조하고 있는 오브젝트의 변경 및 삭제 등에 의해 발생한다.

*Guide Invalid Object를 컴파일하여 주는 것은 PC에서 Scandisk를 수행하는 것과 마찬가지로 데이터베이스의 관리에 있어 가장 기본적인 작업에 해당하므로 이를 주기적으로 점검하고 해결하여 주는 것이 좋다. 특히, Export/Import 작업수행 후, 또는 오라클 버전 upgrade나 patch적용 후에 다수의 오브젝트들이 Invalid상태로 빠져버리는 경우가 많이 발생하므로 이러한 작업 후에는 반드시 Invalid Object의 존재여부를 점검하여 컴파일을 수행하여 주어야 한다.