Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

I/O 대기 시간 분석을 통한 성능 향상

Oracle Database Tips by Donald Burleson

 

I/O 대기 시간 분석을 통한 성능 향상

저자 : Donald K. Burleson

물리적 I/O 대기 시간 분석은 성능 향상을 위한 중요한 방법입니다.

튜닝 프로젝트의 중요한 분야는 물리적 읽기 대기 이벤트를 분석하고 향상시키는 작업입니다. 데이타베이스에 저장된 데이터를 읽는 데에 시간을 많이 소요하기 때문이며 성능에 결정적인 영향을 주기 때문입니다.

S시스템 병목의 대부분은 물리적 I/O 대기 시간으로 발생하기 때문에 데이타베이스에서 시스템 대기 시간 튜닝 프로젝트의 중요성이 높아지고 있습니다. 10046 대기 이벤트(Level 8 이상) 분석 기술을 선호하는 사람도 있고, Oracle MOSC에서 제공하는 trcanzr.sql(see Next Steps) 툴을 사용해서 10046 트레이스 덤프를 통해 병목지점을 알아냅니다. 한편으로 몇몇 전문가들은 덤프와 실시간 대기 이벤트 추적을 선호하기도 합니다.

분석 작업 중에 Oracle 데이타베이스는 대기 이벤트를 지속적으로 발생하는 상황에서 순간적으로 문제가 될만한 시스템 병목 원인을 포착하는 것은 쉬운 일이 아닙니다. 사실 튜닝이 잘된 데이타베이스에서도 병목 지점은 있습니다. 예를 들면 연산 중심의 데이타베이스는 CPU 병목이, 데이터 웨어하우스에서는 디스크 읽기 병목이 있습니다. 이론상으로 대기 시간과 연관된 하드웨어 자원 접근이 빨라진다면 데이타베이스는 빠른 수행능력을 보일 것입니다.

본 문서에서는 데이타베이스 튜닝의 중요한 요소인 대기 분석에 대한 몇 가지 방법을 다루겠습니다. I/O 병목, CPU 병목, 메모리 병목과 같은 대기 시간이 있으며, 병목의 원인이 파악되면 데이타베이스 전문가와 함께 통해 문제점을 해결할 수 있을 것입니다.

대기 이벤트를 파악하기 위해서, Oracle 데이타베이스에서는 v$system_event, v$session_wait 과 같은 뷰를 제공하여 문제 해결을 할 수 있습니다. 어떤 객체가 병목과 관련 있는지 알려주지 않지만, v$system_event 뷰는 I/O 관련 이벤트와 중요한 관련이 있습니다. 이런 점을 보완하기 위해서 Oracle9i Release 2에서 v$segment_statistics 뷰가 추가되었습니다. v$session_wait 뷰를 통해 자세한 파일과 블록 데이터에 대한 정보를 얻고, 블록 위치를 이용해 문제가 되는 해당 객체를 찾을 수 있습니다. 데이타베이스가 대기를 하고 있는 순간이 아니라면 시스템 이벤트는 매우 빠르게 발생하기 때문에 문제되는 객체를 찾기란 쉽지 않습니다. 순간적인 물리적 I/O 대기 정보와 관련된 v$session_wait 뷰를 사용해서 문제 원인을 찾는 방법을 고안해야 합니다.

v$system_event 뷰에는 300 개 이상의 대기 이벤트를 정의하고 있습니다. 본문에서는 두 가지 가장 중요한 물리적 I/O 대기 이벤트에 대해서만 다루겠습니다. 어떤 Oracle 데이타베이스에서도 발생하는 두 가지 중용한 I/O 읽기 대기입니다.

  • db 파일 순차적 읽기 대기: 순차적 읽기 대기는 ROWID로 테이블을 접근하거나, 인덱스 블록 접근할 때와 같이 단일 블록을 읽을 때 발생합니다. 분산(복수 블록) 읽기와 반대되는 개념입니다.
  • db 파일 분산 읽기 대기 : 전체 테이블 스캔이나 정렬 작업을 하게 되면 분산 읽기 대기가 발생합니다.

 

대기 이벤트를 튜닝은 1) 물리적 읽기 대기를 하는 객체를 확인하고, 2)튜닝 기술을 활용해 문제를 해결하는 단계를 거치게 됩니다. 이 방법을 살펴보고 대기 상태를 확인하는 방법에 대해서 알아보겠습니다.

물리적 읽기 대기 대처 방법

물리적 읽기 대기를 하는 객체를 확인할 때, Statspack 패키지를 사용해 대기와 관련된 SQL 문을 확인하고, 다음과 같은 순서로 문제를 해결합니다. 모든 상황에 최적의 방법은 아니므로 환경에 맞게 사용해야 합니다

 

  • SQL 튜닝 : 디스크 대기 시간을 줄이는 가장 간단하면서도 가장 강력한 방법입니다. 전체 테이블 스캔을 하는 구문에 인덱스를 사용하는 것처럼, SQL 문장 튜닝을 통해 디스크 I/O를 줄이는 것은 가장 효과적으로 문제 해결을 하는 방법일 것입니다. 다음과 같은 세부적인 기술을 사용할 수 있습니다.
    • 조인 순서 변경 : ORDERED 힌트를 이용해 조인되는 테이블의 순서를 변경하는 것과 같은 경우 유리합니다.
    • 인덱스 변경 : 함수 기반 인덱스를 추가하거나 I/O 이벤트가 덜 발생하도록 INDEX 힌트를 이용해서 SQL 튜닝을 할 수 있습니다.
    • 테이블 조인 방법 변경 : 순차적 읽기에서 해쉬 조인보다 중첩된 조인에서 I/O 대기가 적게 발생합니다. USE_NL 과 같은 힌트를 사용해서 조인 방법을 변경할 수 있습니다. Oracle9i에서 새롭게 추가된 pga_aggregate_target를 사용하지 않는다면 hash_area_size 매개변수를 변경해서 해쉬 조인을 튜닝을 할 수 있습니다.

     

     

  • 문제가 있는 SQL 수행시간 변경 : 특정시간대 특정 SQL 수행으로 디스크 대기가 반복적으로 발생하면, 다른 시간대로 수행 시간을 재 조정해 보십시오.

     

     

  • 스키마 재 분석 : dbms_utility.analyze_schema 패키지에서 생성된 dbms_statsIn은 최선의 SQL 실행 계획을 리턴하지 않는 경우도 있습니다. dbms_stats 패키지를 사용해 스키마 분석을 하십시오. 만일 컬럼 데이터 값이 비대칭이라면 막대그래프를 추가하는 작업이 필요합니다.

     

     

  • 다중 스핀들을 이용한 디스크 I/O 분산 : iostat이나 EMC Symmetrics Manager 같은 디스크 모니터링 툴에서 확인한 바에 의하면 디스크 채널 경합으로 디스크 읽기 대기가 발생하는 경우가 있습니다. 하드웨어 문제로 인해 디스크 대기가 발생한다면, 문제되는 객체의 MINEXTENTS와 NEXT 인자를 변경해 복수 디스크에 복수 데이터 파일로 스트립하여 다중 디스크 스핀들(Spindles) 접근이 가능하게 설정할 수 있습니다.

     

     

  • KEEP Pool 사용 : 데이터가 적은 테이블을 전체 스캔하는 경우 KEEP Pool을 구현하여 분산 읽기를 줄일 수 있습니다. 자세한 내용은 "Advanced Tuning with Statspack" (Next Steps참고)을 참고하십시오.

     

     

  • 데이터 버퍼 캐쉬 크기 증가 : 더 많은 데이터 블록을 RAM에 두면 읽기 대기가 발생할 확률이 줄어 듭니다.

 

위 방법 중에 SQL 튜닝이 물리적 읽기 대기를 줄이는 가장 중요합니다. "Diagnosing Performance with Statspack" (Oracle Magazine, 2000년 4,5월; Next Steps참고)을 참고하면, Statspack을 이용해 문제가 발생하는 SQL문을 찾고 분석하는 방법과 stats$sqltext을 사용해 SQL 기록을 보관하며, 읽기 대기가 발생했을 때 해당 SQL 문을 얻는 방법 그리고 SQL 문의 실행 계획 정보를 얻고 최적의 실행 계획을 적용하는 방법을 자세히 설명하고 있습니다.

비효율적인 SQL 실행으로 불필요한 전체 테이블 스캔할 수 있습니다. 예들 들어 10 개 행만 리턴하는 질의를 수행하는데, 100 블록 테이블을 전체 스캔할 수 있습니다.

이제 솔루션을 알아보았고, 물리적 읽기 대기의 원인을 수정하는데 필요한 데이터 수집 방법을 알아보겠습니다.

실시간 대기 이벤트 정보 수집

문서에서는 Oracle 튜닝에 대한 포괄적인 방법 보다, 디스크 I/O 문제 해결을 위한 통찰력에 중점을 두겠습니다. v$views는 정보를 저장하는 곳이고 Oracle 인스턴스를 시작한 이후에 대기 발생 회수를 단지 확인할 수 있습니다. 대기와 관련된 객체를 확인하기 위해서 다른 방법을 취해야 합니다.

v$session_wait 뷰를 사용해 질의를 수행한 시점의 디스크 대기 정보를 확인 할 수 있습니다. 대기 정보가 일시적으로 저장되고 사라지기 때문에 모든 실행 대기 현상을 포착하는 것은 쉽지 않습니다. 따라서 v$session_wait 뷰를 이용해서는 반복적으로 샘플 데이터를 얻고, 분석을 통해 추후에 자세한 정보를 확인하는 방법을 사용합니다.

대기가 발생한 시점을 포착할 수 있고 대기가 발생한 파일과 블록 넘버를 v$session_wait를 통해 확인 할 수 있으며, 그 정보를 이용하면 어느 테이블, 인덱스에서 문제가 발생했는지 확인할 수 있습니다.

v$session_wait 뷰에서 필요한 정보를 저장하는 테이블을 먼저 정의합니다. 리스트 1은 이벤트 이름, 대기 시간, 파일 ID(P1) 블록 넘버(P2) 등의 v$session_wait 뷰의 컬럼 정보 저장할 테이블 구조를 보여줍니다.

v$session_wait의 정보를 저장할 테이블을 정의했고, 다음 단계는 특정 이벤트가 발생할 때 시스템 대기 정보를 INSERT 하는 테이블을 구성합니다. 한시적인 데이터를 유지하는 뷰의 특성상 INSERT가 수행되어도 값이 저장되지 않을 수도 있습니다. UNIX의 crontab에 INSERT 문(리스트 2 스크립트 참조)을 등록해서 60초 마다 실행되도록 설정합니다.

MS-Windows에서는 AT 명령어를 사용 합니다. cron 서비스가 수행될 때 v$session_wait를 조회하고 테이블에 저장합니다.

마지막으로 UNIX 서버의 crontab 목록에 60초마다 스크립트가 실행되도록 합니다. 런타임 I/O 대기에 대한 모든 정보를 수집하기 위한 전부입니다.

 

# ************************************************************
# Run 60-second check for run-time waits
# ************************************************************
* * * * * /opt/oracle/oracheck/get_waits.ksh mysid > /tmp/wt.lst

 

몇 일간 스크립트가 실행하고, 결과를 분석합니다. 분석작업에서는 리스트 4와 같이 테이블 스페이스 이름, 블록 이름을 확인하며 진행합니다. 다음과 같이 I/O 대기가 발생한 시점의 시간정보와 블록 ID를 확인 합니다.

아래는 스크립트 실행 결과입니다. I/O 대기가 발생한 정확한 시간과 블록 ID를 확인할 수 있습니다.

 

System               Wait                            File         Block
Date                 Event                             ID            ID
-------------------- ------------------------------ ----- -------------
23/01/2003 17:40:02  db file sequential read            6       300,929
23/01/2003 18:00:03  db file sequential read            8        35,936
23/01/2003 21:04:02  db file sequential read            3        65,162
23/01/2003 21:08:02  db file sequential read            6        23,031
23/01/2003 21:09:01  db file sequential read            5        40,585
23/01/2003 21:10:01  db file sequential read            6       512,663
23/01/2003 21:11:02  db file sequential read            5        26,609
23/01/2003 21:12:01  db file sequential read            5        40,584

 

대기 타입과 시간 정보로 구성된(리스트 5 참조) 대기 회수에 대한 정보를 얻었습니다. 언제 I/O 튜닝이 필요한 시간대를 알 수 있기 때문에 대기 분석에 매우 중요합니다.

아래는 스크립트 결과를 보여줍니다. 다음 샘플 결과는 날짜별 I/O 대기 시간의 합을 확인하여, 시간의 변화에 따라 병목 현상이 틀리게 발생한다는 점을 알 수 있을 것입니다. 9:00PM-10:00PM 사이에 병목이 주로 발생한다고 분석할 수 있습니다.

 

                     Wait                            Wait
Date        Hr.      Event                          Count
-------------------- ------------------------------ -----
23-jan-2003 17       db file sequential read            1
23-jan-2003 18       db file sequential read            1
23-jan-2003 21       db file sequential read           53
23-jan-2003 22       db file sequential read            8
23-jan-2003 23       db file sequential read            7
24-jan-2003 00       db file sequential read            1
24-jan-2003 03       db file sequential read            1
24-jan-2003 05       db file sequential read            1
24-jan-2003 09       db file sequential read            4
24-jan-2003 11       db file sequential read            2
24-jan-2003 13       db file scattered read             1
24-jan-2003 13       db file sequential read            2
24-jan-2003 14       db file sequential read            2
24-jan-2003 17       db file sequential read            1
24-jan-2003 21       db file sequential read           75

 

특정 테이블이나 인덱스에 대한 I/O 대기 추적하기

파일 넘버와 블록 넘버를 테이블 이름과 인덱스 이름으로 변환하면 분석작업을 좀더 용이하게 할 수 있을 것입니다. 이를 위해서는 dba_extents 뷰를 사용해서 테이블의 시작 블록과 마지막 블록을 계산할 수 있습니다. 어느 객체가 읽기 대기, 버퍼 대기가 있었는지 확인할 수 있습니다. 다음과 같이 dba_extents 뷰를 조인해서 세그먼트 이름을 추가하면 분석이 조금 더 용이할 것입니다. (리스트 6 참조)

스크립트를 수행하면 다음과 같은 결과를 얻을 수 있고, 모든 세그먼트에 10회 이상의 디스크 읽기 대기가 있었다는 것을 알 수 있습니다.

 

Wait       Segment                             Segment            Wait
Event      Name                                Type              Count
---------- ----------------------------------- ---------- ------------
SEQ_READ   SYSPRD.S_EVT_ACT_F51                INDEX                72
SEQ_READ   SYSPRD.S_ACCNT_POSTN_M1             INDEX                41
SEQ_READ   SYSPRD.S_ASSET_M3                   INDEX                24
SEQ_READ   SYSPRD.S_ASSET_M51                  INDEX                19
SEQ_READ   SYSPRD.S_COMM_REQ_U1                INDEX                11

 

여기까지 우리는 순차적 읽기 대기가 발생한 인덱스를 찾는 방법과 SQL 튜닝, 분산 저장 방법에 대해 알아보았습니다. 다음 장에서는 "hot 블록"에 대해서 알아보겠습니다.

분석을 종료하기위해서 'Hot blocks"에 대한 모든 것을 알아보겠습니다. stats$real_time_waits 테이블을 이용해서, 복수의 대기를 하고 있는 데이터 블록을 확인하는 방법을 알아보겠습니다.(리스트 7 참고)

대기가 발생한 세그먼트, 블록, 대기 이벤트 회수를 알 수 있습니다.

 

                                                                Multiple
                                                                   Block
Wait       Segment                        Segment         Block     Wait
Event      Name                           Type           Number    Count
---------- ------------------------------ ---------- ---------- --------
SEQ_READ   SYSPRD.S_EVT_ACT_F51           INDEX         205,680        7
SEQ_READ   SYSPRD.S_EVT_ACT               TABLE         401,481        5
SEQ_READ   SYSPRD.S_EVT_ACT_F51           INDEX         471,767        5
SEQ_READ   SYSPRD.S_EVT_ACT               TABLE           3,056        4
SEQ_READ   SYSPRD.S_EVT_ACT_F51           INDEX         496,315        4
SEQ_READ   SYSPRD.S_DOC_ORDER_U1          INDEX          35,337        3
복수 대기를 하고 있는 데이터 블록을 식별할 수 있기 때문에 매우 중요합니다. 각 데이터 블록을 확인해서 세그먼트 헤더에 대한 경합을 알 수 있습니다.

 

트랜드 기반 이벤트 대기 분석

이벤트 대기에 대한 자세한 정보를 확인하면, 데이터 블록을 확인하고 트랜드 보고서를 생성할 수 있습니다. 리스트 8의 SQL 문을 사용해서 시간 단위의 순차적 읽기 지연 평균을 알아낼 수 있습니다.

주기적인 스케줄 작업을 하는 데이타베이스는 일종의 특질을 갖고 있습니다. 특질을 분석하기 위해 Statspack 패키지를 사용해 SQL 문을 생성하고 적절히 최적화할 수 있습니다.

읽기 대기가 계속 발생한다면 스케줄을 변경해서 다른 시간대에 문제가 되는 SQL 문을 수행하는 방법을 사용합니다. 실행 시간을 변경하면서 I/O 변화를 살펴보십시오.

일 단위로 트랜드를 확인하기 위해서는 리스트 9와 같이 순차적 읽기 대기 평균 정도를 발생하는 질의를 수행할 수도 있습니다.

그림 1과 같이 I/O 대기 데이터를 그래픽으로 나타내 보면 데이타베이스에서 반복되는 대기 지연 트랜드를 찾을 수 있습니다.(여기서는 Microsoft Excel 차트 위저드를 사용했습니다) 문제를 발생시키는 테이블과 인덱스를 찾기 위한 모든 정보는 stats$real_time_waits 뷰에 있습니다. Statspack 패키지와 함께 사용하면 stats$sql_summary와 같은 SQL 문을 얻어, SQL 레벨에서 디스크 대기 이벤트 분석을 할 수 있습니다.

 

Plotting real-time waits by hour
<< 그림 1. 시간별 실시간 대기 평균 그래프 >>

 

위 경우를 분석하면, db 파일 순차적 읽기 대기는 주로 2:00AM-3:00AM과 9:00PM-12:00PM 시간대에 최대값을 확인하고 Statspack 패키지를 이용해 그 시간대에 수행된 SQL 문을 조사합니다.

U본문에서 제공하는 스크립트를 이용해 요일별 읽기 대기를 요약할 수 있습니다.(그림 2 참조) 매 화요일과 목요일에 분산 읽기 대기가 최대값을 나타내며 이 날짜에 수행된 SQL 문을 역시 Statspack을 이용해 확인 합니다.

 

Plotting real-time waits by day
<< 그림 2. 요일별 실시간 대기 평균 그래프 >>

 

대기 현상에 대한 소스를 확인 할 수 없기 때문에 유용하지 않을 수 도 있습니다. (Oracle9i Release2 사용자는 statistics_level을 7이상으로 설정하면, v$ segment_statistics 뷰를 통해 정보를 확인 할 수 있습니다.)

하지만 stats$real_time_waits에 저장된 대기 원인을 알고 있기 때문에 쉽게 문제 객체를 확인할 수 있습니다. (리스트 10 참고)

문제가 있는 테이블과 인덱스는 다음과 비슷한 형식으로 파악될 수 있습니다.

 

                                                                Block
                     Segment                        Segment     Wait
Date        Hr.      Name                           Type        Count
-------------------- ------------------------------ ----------  --------
23-jan-2003 21       SYSPRD.S_COMM_REQ_SRC_U1       INDEX       23
23-jan-2003 21       SYSPRD.S_EVT_ACT               TABLE       44 
23-jan-2003 21       SYSPRD.S_EVT_ACT_F51           INDEX       16
23-jan-2003 22       SYSPRD.S_EVT_ACT               TABLE       32

 

물리적 읽기 대기가 발생한 객체를 확인하면, 문제 해결을 위해 추가적인 디스크 스핀들 기법으로 객체를 분산할 수 있습니다.

검색과 튜닝

문서에서 사용한 실시간 대기 측정 방법을 사용하면 쉽게 물리적 읽기 대기가 발생한 객체를 확인 할 수 있습니다. 문제의 객체를 찾으면, Statspack을 사용해 SQL 레벨에서 방안을 찾고 튜닝을 합니다. 물리적 읽기 대기에서 튜닝은 리소스에 대한 경합을 줄이거나 데이터 버퍼 캐쉬 크기를 늘리는 방법으로, 실질적으로는 SQL 튜닝, 복수 디스크로 객체 스트립하기, KEEP Pool 사용하기, SQL 문 수행시간 조정을 하는 것입니다.

Graham Wood, Oracle사, Statspack 개발자와 Oracle Guru에게 감사합니다. 정확하고 실용적인 글을 쓰게 도와주신 Graham에게 특별한 감사의 마음을 전합니다.

Donald K. Burleson Oracle 데이타베이스 최고 전문가 중 한명. 저서 17권, 100회 이상 잡지 기고를 했고, Oracle Internal 편집장으로 활동 중. 최근 저서로 Rampant Techpress에서 "Creating a self-tuning Oracle Database"을 출판.http://www.dba-oracle.com , http://www.remote-dba.net/사이트 참조

 

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational