데이터베이스 성능 진단 및 통계

데이터베이스 성능 진단 및 통계

2008/10/16 22:58

[ORACLE 10G/설정/관리]

1. 튜닝 작업 - 성능 관리의 세가지 작업은 다음과 같다. * 성능계획 : 어느정도의 데이터량이 있을때 어떤작업에서 어느정도의 성능이 필요한지 정확한 수치를 구하기 위해서 세우는 계획으로 하드웨어, 소프트웨어, 운영체제, 네트워크 infrastructure등과 같은 환경을 설정하는 과정이다. * Instance 튜닝 : Instance는 메모리와 프로세스다. 어차피 작업은 메모리에서 일어나기 때문에 메모리와 작업을 수행하는 프로세스를 튜닝하는 것으로 오라클 데이터베이스 파라미터와 운영 체제 파라미터를 실제로 조정하는 것이다. * SQL 튜닝 : SQL, PL/SQL문장을 최적으로 튜닝한다. (보통 문제의 80%는 SQL 문제다.) 2. 성능 계획 - 투자 옵션 : 어떤 작업에 어느정도의 시스템 리소스를 사용할것인지에 대한 옵션 - 시스템 구조 - Scalability(확장성) - 응용 프로그램 설계 원칙 - 작업 로드 테스트, 모델링 및 구현 - 새 응용 프로그램 배치 3. Instance 튜닝 - 명확한 목표를 수립한다. - 데이터베이스 구조에 메모리를 할당한다. - 데이터베이스(파일을 의미 데이터파일, 컨트롤파일, 리두로그파일 등등)의 각 부분에서 I/O 요구 사항을 고려한다. - 데이터베이스가 최적의 성능으로 실행되도록 운영 체제를 튜닝한다. ※ 튜닝 작업을 시작할 때는 구체적인 목표를 가지고 있어야 한다. "작업 능률을 높여서 성능에 확연히 반영하자"와 같은 목포보다는 "분당 500개의 판매 트랜젝션 처리"와 같은 목표가 작업을 진행하기 한결 수월하다. 4. 성능 튜닝 방법론 - Instance를 튜닝하기 전에 OS 통계와 일반 시스템 상태를 확인하여 데이터베이스에 문제가 있는지 확인한다. - 하향식으로 튜닝한다. 설계, 응용 프로그램, Instacne 순으로 튜닝한다. O.S -> Contention (경합) -> I/O -> Memory -> Application -> Design 순서로 튜닝을 하는것이 가장 좋다. - 예를 들어서, 디스크에서 테이블스페이스 레이아웃을 튜닝하게 전에 I/O경합을 유발하는 전체 테이블 스캔을 중지하지 않길 바란다. - 잠재적 이점이 가장 뛰어난 영역을 튜닝한다. 이 과정에서 제시하는 튜닝 방법론은 간단하다. - 가장 큰 병목 지점을 식별하고 이를 튜닝한다. 그리고 이 작업을 반복한다. - 다양한 모든 튜닝 툴에는 가장 많은 시간을 소요하는 SQL문, 리소스 경합 또는 서비스를 식별할 수 있는 방법이 있다. - 오라클 데이터베이스는 병목지점 식별 프로세스를 자동화하는 시간 모델과 metric을 제공한다. - 목표가 충족되면 튜닝을 중지한다. 이를 위해서 튜닝의 목표를 정해야한다. (오라클에 할당된 시스템 리소스는 한정되어 있기 때문에 사용할수 있는 리소스는 한정되어 있기 때문에 적절한 리소스 할당이 필요하기 때문에 적정전에서 튜닝을 중지한다.) 5. 통계 수집 - 성능 튜닝은 정확한 통계 수집이 관건이다. - 통계 유형 * 옵티마이저 통계 * 시스템 통계 -통계 수집 유형 * 자동 : GATHER_STATS_JOB 사용 * 수동 : DBMS_STATS 패키지 사용 * 데이터베이스 초기화 파라미터 설정 * 다른 데이터베이스에서 통계 임포트 - 옵티마이저 통계는 데이터베이스와 데이터베이스의 객채에 대해 자세히 설명하는 데이터의 모음이다. - Query 옵티마이저는 이러한 통계를 사용하여 각 SQL문에 가장 적합한 실행 계획을 선택한다. - 시스템 통계는 I/O및 CPU성능과 활용률 같은 하드웨어 특성을 Query 옵티마이저에 설명한다. - 실행 계획을 선택할 때 옵티마이저는 각 Query에 필요한 I/O및 CPU 리소스를 예측한다. - Query 옵티마이저는 시스템 통계를 통해 I/O및 CPU 비용을 보다 정확하게 예측함으로써 보다 적합한 실행 계획을 선택할 수 있다. - 시스템 통계는 DBMS_STATS.GATHER_SYSTEM_STATS 프로시저를 사용하여 수집한다. - 오라클 데이터베이스는 시스템 통계를 수집할 때 지정된 기간 동안의 시스템 작업을 분석한다. - 시스템 통계는 자동으로 수집되지 않는다. - 오라클에서 시스템 통계 수집에 DBMS_STATS 패키지를 사용할 것을 권장한다. - 권장되는 옵티마이저 통계 수집 방법은 오라클 데이터베이스를 통해 자동으로 통계를 수집하는 것이다. - GATHER_STATS_JOB 작업은 데이터베이스를 생성할 때 자동으로 생성되어 스케줄러에 의해 관리된다. - 이 작업은 누락되거나 오래된 옵티마이저 통계가 있는 데이터베이스의 모든 객체에 대해 통계를 수집한다. - 수동으로 통계를 수집하려면 DBMS_STATS 패키지를 사용한다. 이 PL/SQL 패키지는 통계를 수정, 조회, 엑스포트, 임포트 및 삭제할 때도 사용할 수 있다. - 또한 데이터베이스 초기화 파라미터를 통해 옵티마이저 및 시스템 통계 수집을 관리할 수 있다. 예) * OPTIMIZER_DYNAMIC_SAMPLING 파라미터는 옵티마이저에서 수행하는 동적 샘플링의 레벨을 제어한다. * STATISTICS_LEVEL 파라미터는 데이터베이스의 모든 주요 통계 수집 및 advisory를 제어하고 데이터베이스에 대한 통계 수집 레벨을 설정한다. 이 파라미터의 값은 BASIC, TYPICAL 및 ALL 이다. * TIMED_STATISTICS 파라미터는 오라클 서버에 사용 가능한 대기 수 이외에 이벤트 대기 시간을 수집하도록 지시한다. * STATISTICS_LEVEL 초기화 파라미터가 TYPICAL 또는 ALL로 설정된 경우에는 데이터베이스에 대한 시간별 시스템 통계가 자동으로 수집된다. * STATISTICS_LEVEL이 BASIC로 설정된 경우에는 TIMED_STATISTICS를 TRUE로 설정해야 시간별 통계를 수집할 수 있다. * STATISTICS_LEVEL을 BASIC로 설정하면 많은 자동화 기능이 비활성화되므로 권장하지 않는다. - 초기화 파라미터 파일에서 또는 ALTER SYSTEM이나 ALTER SESSION 명령을 사용하여 TIMED_STATISTICS 또는 TIMED_OS_STATISTICS를 명시적으로 설정한 경우에는 명시적으로 설정한 값이 STATISTICS_LEVEL에서 파생된 값보다 우선한다. - V$STATISTICS_LEVEL뷰를 Query하면 STATISTICAL_LEVEL 파라미터의 영향을 받는 파라미터를 확인할 수 있다. 6. Oracle 대기 이벤트 - 대기 이벤트 모음은 다양한 원인으로 대기해야 했거나 대기해야 할 세션 또는 프로세스에 대한 정보를 제공한다. - 이러한 이벤트는 V$EVENT_NAME 뷰에 나열된다. - 대기 이벤트는 서버 프로세스나 스레드에 의해 증가하는 통계로, 처리를 계속하려면 이벤트가 완료될 때까지 대기해야 함을 나타낸다. - 대기 이벤트 데이터는 래치 경합, 버퍼 경합 및 I/O 경합처럼 성능에 영향을 줄 수 있는 다양한 증상의 문제를 보여준다. - 오라클 데이터베이스에서는 free buffer wait(DBWR에서 더티 블록을 빨리 디스크에 쓰지 못해서 발생함), latch free(메모리에 락을 거는 상황), buffer busy waits, db file sequential read, db file scattered read를 비롯한 800개 이상의 대기 이벤트가 있다. - EM을 사용하여 Performance 페이지를 열고 슬라이드에 표시된 "Sessions: Waiting and Working" 그래프에서 대기 이벤트를 확인할 수 있다. 7. 시스템 통계 ※ V$ 로 시작하는 뷰 들은 Instance가 나서부터의 통계를 가지고 있다. 재부팅되면 날아간다.. - 성능 문제를 효율적으로 진단하려면 통계를 사용할 수 있어야 한다. - 오라클 데이터베이스는 시스템, 세션 및 개별 SL문에 대한 다양한 유형의 누적 통계를 생성하며, 세그먼트와 서브스에 대한 누적 통계도 추적한다. - 이러한 모든 범위에서 성능 문제를 분석하면 일반적으로 관심이 있는 기간 동안의 통계 변화(델타 값)를 확인할 수 있다. - 대기 이벤트 통계 * 가능한 대기 이벤트는 모두 V$EVENT_NAME 뷰에 카탈로그화 된다. * 모든 세션에 대한 누적 통계는 V$SYSTEM_EVENT에 저장된다. * 이 뷰는 Instance 시작 이후 특정 이벤트에 대한 총 대기 수를 보여준다. * 문제를 해결하려면 프로세스가 리소스를 기다렸는지 여부를 알아야 한다. 7-1. 시스템 자원의 통계 - 시스템 차원의 통계는 모두 V$STATNAME 뷰에 카탈로그화 됩니다. 오라클 10g에서는 약 330개의 통계를 제공한다. - 서버는 계산된 모든 시스템 통계를 V$SYSTAT 뷰에 표시한다. 이 뷰를 Query하여 Instance 시작 이후의 누적 합계를 찾아볼 수 있다. 예) SQL> SELECT name, class, value FROM v$sysstat; (실행해보기 바란다.) 7-2. SGA Global 통계 - 서버는 계산된 모든 메모리 통계를 V$SGASTAT 뷰에 표시한다. 이 뷰를 Query하여 Instance 시작 이후의 상세한 SGA 사용량에 대한 누적 합계를 찾아볼 수 있다. 예) SELECT * FROM v$sgastat; POOLNAME BYTES ------------ --------------- -------- fixed_sga7780360 buffer_cache 251265824 log_buffer262144 shared pool sessions 1284644 shared pool sql area 22376876 ...... - 위의 표시된 결과는 출력의 일부만 표시한 것이다. 8. 세션 관련 통계 표시 ※ 세션 정보 : 로그인부터 로그아웃까지의 정보 - V$SESSION을 Query하여 로그온한 각 유저에 대한 현재 세션 정보를 표시할 수 있다. * 예를 들어, V$SESSION을 사용하여 세션이 유저 세션을 나타내는지 데이터베이스 서버 프로세스(BACKGROUND)에 의해 생성 되어 었는지를 확인할 수 있다. - 오라클 서버는 유저 세션 통계를 V$SESSTAT 뷰에 표시한다. V$SESSION_EVENT 뷰는 세션별 이벤트 대기 정보를 나열한다. - 동적 뷰의 누적 값은 데이터베이스 Instance가 종료될 때 재설정된다. - V$MYSTAT 뷰는 현재 세션의 통계를 표시한다. - V$SESSMETRIC 뷰를 Query하여 모든 활성 세션의 성능 metric 값을 표시할 수도 있다. * 이 뷰는 CPU 사용량, 물리적 읽기 수, 하드 구문 분석 수 및 놀리적 읽기 비율과 같은 성능 metric을 나열한다. 9. 문제 해결 및 튜닝 리뷰 10. 딕셔너리 뷰 - 다음 딕셔너리 및 특수 뷰는 DBMS_STATS 패키지를 사용하여 생성되는 유용한 통계를 제공한다. * DBA_TABLES, DBA_TAB_COLUMNS * DBA_CLUSTERS * DBA_INDEXES * DBA_TAB_HISTOGRMS - 이 통계 정보는 DBMS_STATS의 해당 프로시저가 재실행될 때까지 변경되지 않는다. - 데이터 저장영역을 자세히 보려면 DBMS_STATS패키지를 사용한다. - 이 패키지는 통계를 수집하여 일부 DBA_XXX 뷰의 열을 채운다. - DBMS_STATS는 다음과 관련된 뷰의 열을 채운다. * Extent및 블록 내에 있는 테이블 데이터 저장 영역 > DBA_TABLES > DBA_TAB_COLUMNS * Extent 및 블록 내에 있는 클러스터 데이터 저장 영역 > DBA_CLUSTERS * Extent 및 블록 내에 있는 인덱스 데이터 저장 영역과 인덱스 유용성 > DBA_INDEXES - ANALYZE INDEX .... VALIDATE STRUCTURE 명령을 수행하면 인덱스에 대한 통계를 포함하는 INDEX_STATS 및 INDEX_HISTOGRAM 뷰가 채워진다. 11. 정지되었거나 매우 느린 데이터베이스에 대한 진단 - 데이터베이스가 너무 느리게 작동하거나 정지된 경우 문제 분석을 위해 다음 기능을 사용한다. 이 패키지는 통계를 수집하여 일부 DBA_XXX 뷰의 열을 채운다. * 성능 모니터를 위해 SGA에 직접 액세스 (메모리 액세스 모드) > V$SEESION > V$SESSION_WAIT > V$SYSTEM_EVENT > V$SYSSTAT > Enterprise Manager를 사용한 정지(Hang) 분석