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 


 

 

 


 

 

Oracle Technology Network

STATSPACK을 이용한
오라클 데이타베이스 트랜드 분석

도날드 K. 벌레슨(Donald K. Burleson)


모든 오라클 데이타베이스를 정밀하게 튜닝하기 위해서는 과거 데이타 모음 처리 방법과 데이타를 시간 경과에 따라 데이타베이스 성능에 영향을 미치는 변화들을 보여주는 보고서로 번역하는 기능이 필요하다. 오라클의 새로운 STATSPACK 유틸리티는 STATSPACK 테이블에 대한 트랜드 분석 보고서를 작성하는 기능과 함께 필요한 데이타를 수집할 있는 기능을 제공한다.



Oracle8 위한 STATSPACK 유틸리티

STATSPACK 유틸리티는 Oracle8i(릴리스 8.1.)에서 처음으로 소개됐지만, 오라클은 패치를 다운로드해 STATSPACK Oracle8 데이타베이스와 함께 사용할 있도록 지원하고 있다.

오라클 매거진(본사 Oracle Magazine) 2000 3 호를 시작으로 STATSPACK pre-Oracle8i 버전을 상세하게 다룬 기사가 시리즈로 연재되어 실려있다. 일단 Oracle8 설치되면 DBA 언제든 유용한 성능 데이타를 수집해 분석할 있는 준비를 갖추었다고 있다.

STATSPACK 운영 방법

STATSPACK 유틸리티는 Oracle Begin-Statistics(BSTAT) End-Statistics(ESTAT) 유틸리티의 특별 버전을 실행하는 스크립트 세트이다. 유틸리티들은 100 이상의 성능 메트릭스에 대해 경과된 시간 통계를 포착한다. 그러나 BSTAT/ESTAT 유틸리티와 달리 STATSPACK 성능 데이타를 포착해 특정한 오라클 테이블에 데이타를 저장한다.

모든 STATSPACK 테이블은 PERFSTAT 사용자가 소유하며, 모든 테이블은 STAT$ 시작된다. 부록에 전체 STATSPACK 테이블 이름 리스트를 실었다.

리스트 1(image 대체)- STATSPACK 테이블

STATS$BG_EVENT_SUMMARY
STATS$BUFFER_POOL
STATS$BUFFER_POOL_STATISTICS
STATS$DATABASE_INSTANCE
STATS$ENQUEUESTAT
STATS$FILESTATXS
STATS$IDLE_EVENT
STATS$LATCH
STATS$LATCH_CHILDREN
STATS$LATCH_MISSES_SUMMARY
STATS$LEVEL_DESCRIPTION
STATS$LIBRARYCACHE
STATS$PARAMETER
STATS$ROLLSTAT
STATS$ROWCACHE_SUMMARY
STATS$SESSION_EVENT
STATS$SESSTAT
STATS$SGASTAT_SUMMARY
STATS$SGAXS
STATS$SNAPSHOT
STATS$SQL_SUMMARY
STATS$STATSPACK_PARAMETER
STATS$SYSSTAT
STATS$SYSTEM_EVENT
STATS$WAITSTAT



테이블에 있는 열들을 보려면 리스트 2에서 예시된 것처럼 간단히 테이블 이름을 선택하고 SQL*Plus 'DESCRIBE' 명령을 사용하면 된다.

리스트 2 (image 대체)
-------------------------------------------
SQL> desc STATS$BUFFER_POOL_STATISTICS;
Name Null? Type
-------------------------------------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
ID NOT NULL NUMBER
NAME VARCHAR2(20)
SET_MSIZE NUMBER
CNUM_REPL NUMBER
CNUM_WRITE NUMBER
CNUM_SET NUMBER
BUF_GOT NUMBER
SUM_WRITE NUMBER
SUM_SCAN NUMBER
FREE_BUFFER_WAIT NUMBER
WRITE_COMPLETE_WAIT NUMBER
BUFFER_BUSY_WAIT NUMBER
FREE_BUFFER_INSPECTED NUMBER
DIRTY_BUFFERS_INSPECTED NUMBER
DB_BLOCK_CHANGE NUMBER
DB_BLOCK_GETS NUMBER
CONSISTENT_GETS NUMBER
PHYSICAL_READS NUMBER
PHYSICAL_WRITES NUMBER



리스트 2 통해 buffer_pool_statistics 전체 데이타베이스에 대한 물리적인 읽기와 쓰기 합계를 포함한 많은 정보가 포함되어 있다는 것을 있다. 먼저 테이블 구조를 이해한 다음, 용량 계획에 대한 커스터마이즈된 트랜드 보고서를 검토하게 된다. 그러면 트랜드 분석에 대해 포괄적으로 살펴본 이들 보고서가 작성되는 방법에 대해 살펴보기로 하겠다.

 

STATSPACK 사용한 트랜드 분석

STATSPACK 최근에 소개됐기 때문에 오라클은 'STATSREP80.SQL'이라는 단일 보고서 만을 지원하고 있다. 이는 지정된 2 시점 사이에서 일어나는 변화를 검토할 있는 우수한 보고서이기는 하지만 트랜드 보고서를 만드는데 필요한 데이타를 제공하지는 않는다. 숙련된 DBA 시간 경과에 따른 성능 데이타 측정을 통해 시간별, 날짜별, 그리고 주별 "표시(signature)"등을 자세히 파악할 있다는 것을 알고 있다.

모든 사람이 저마다 고유의 표시기호를 가지고 있는 것처럼 데이타베이스도 I/O, 정렬, 데이타 버퍼 적중률(hit ratio) 등에 대한 고유의 표시기호을 가지고 있다. 따라서 이러한 표시기호들은 특정한 데이타베이스 작업이 데이타베이스에 압력을 가하게 경우 이를 DBA에게 알리게 된다.

시계열(time series) 플로팅의 목적은 다양한 데이타베이스 메트릭스를 위한 표시기호들을 개발하기 위한 것이다. 예를 들어, I/O 항상 화요일 오전 9시에 높다거나, 데이타 버퍼 적중률이 항상 수요일 오후에 낮아진다는 사실을 발견하게 수도 있다. DBA 이러한 표시기호 정보를 확보함으로써 데이타베이스 성능의 최고점과 최저점에 맞는 합당한 계획을 수립할 있게 된다. 가지 실제 STATSPACK 플롯을 살펴봄으로써 이러한 개념을 파악해 보도록 하겠다.


토탈 시계열 보고서

이들 보고서들은 지정된 기간 중에 발생한 데이타베이스의 전반적인 트랜드를 보여주고 있다. MS-Excel 작성한 <그림 1> 예제에서는 데이타베이스 증가율을 나타내기 위해 추가한 선형 회귀선으로 표시한 데이타베이스의 바이트 수에 대한 리포트이다.

<그림 1> 데이타베이스 크기 시계열 보고서




일간 보고서

일간 보고서는 시간별로 묶은 시간별 트랜드를 나타내는 것이다. <그림 2> 예제에서는 날의 시간별로 평균을 평균 읽기 I/O 쓰기 I/O 대한 표시기호를 있다. 예제에서는 데이타베이스가 오전 7시에 최고점의 읽기 I/O 표시기호를 갖고 있으며, 다시 오후 8시에 최고점에 도달했다는 사실을 확인할 있다. 또한 데이타베이스는 상대적으로 일정한 쓰기 표시기호를 가지고 있다는 사실도 있다.

<그림 2> 시간별 평균 I/O

 

 

주간 보고서

<그림 3>에서는 날짜별로 평균을 디스크 정렬을 있다. 그림에서는 디스크 정렬이 월요일에 높고, 화요일에 최고점에 도달하며, 수요일에 높았다가 목요일과 금요일에는 떨어지는 확실한 표시를 있다. 보고서는 DBA에게 잠재적으로 TEMP 테이블스페이스에 높은 영향을 미칠 있는 시간대를 신속하게 알려줄 있다.

<그림 3> 일간 트랜드 보고서



이제 트랜드 보고서의 유용성을 확신할 있기 때문에 STATSPACK로부터 추출한 가지 일반 메트릭스를 점검해 보기로 하겠다.

그렇다면 어떤 방법으로 시간별 혹은 날짜별로 평균을 있을까? 사실 이는 매우 간단하다. 날짜별로 평균 디스크 I/O 프린트한 리스트 3 살펴보기로 하자.

리스트 3(image 대체) - '날짜별' 평균 STATSPACK Query
--------------------------------------------------------------

set pages 9999;
column reads format 999,999,999
column writes format 999,999,999
select
to_char(snap_time,'day'),
avg(physical_reads) reads,
avg(physical_writes) writes
from
perfstat.stats$buffer_pool_statistics fs,
perfstat.stats$snapshot sn
where
fs.snap_id = sn.snap_id
group by
to_char(snap_time,'day')
;
---------------------------------------------------------------------
* 스크립트를 ' 날의 시간별' 평균으로 변경하려면 간단히 'day'' 'HH24' 변경하기만 하면 된다.

 

트랜드 분석에 가장 적합한 STATSPACK 데이타

STATSPACK에서 사용할 있는 통계가 개이기 때문에 과연 어떤 메트릭스가 가장 유용한가라는 의문이 제기된다. 본인이 집필한 "High Performance Oracle8 Tuning"에서 제시한 있는 어드바이스를 토대로 다음과 같은 메트릭스를 선정했다.

데이타 버퍼 적중률

이는 init.ora 파일의 db_block_buffers 파라미터에 의해 정의된 대로 오라클 데이타 버퍼의 효율성을 측정한다.

정렬

데이타베이스에서의 정렬 규모를 모니터링하는 것이 중요하며 특히 TEMP 테이블스페이스(정렬(디스크))에서 수행된 정렬 규모를 더욱 철저하게 모니터링해야 한다.

물리적 디스크 읽기

I/O 감축의 일차적 목적은 오라클 튜닝이기 때문에 시간별 디스크 읽기를 보여주는 트랜드 보고서는 재순차(re-sequence) 행을 인덱스 순서로 테이블을 재구성하는데 따른 영향을 측정하는데 매우 유용할 있다.

물리적 디스크 쓰기

디스크 쓰기 규모는 오라클 데이타베이스의 전체 처리 성능에 상당한 영향을 미치게 되며, 롤백 세그먼트 활동이 중요한 경우 특히 중요하다.

I/O 대기

이는 I/O 경합(contention) 확인하고 수정하는데 사용될 있는 매우 중요한 메트릭스이다. 높은 I/O 대기 상태는 2 이상의 데이타 파일에 동시에 액세스되는 경우에 발생하며, DASD 읽기-쓰기 헤드 이동은 디스크가 I/O 종료가 대기되도록 하는 원인이 되기도 한다. 자세한 내용은 Turning Table on Disk I/O(본사 오라클 매거진, 2000 1월호) Tuning Disk I/O in Oracle8(본사 오라클 매거진 1999 11월호) 참조.

버퍼 비지 웨이트(Buffer Busy Waits)

버퍼 버스트(bust) 대기는 오라클 데이타 블록이 버퍼에 상주하지만 필요한 프로그램이 데이타 블록을 읽어 들이기 위해 대기해야만 하는 경우 일어난다. 버퍼 비지 웨이트는 일반적으로 1 테이블이 동시에 'UPDATE' 혹은 'INSERT DML' 갖고 있으며 단지 1 개의 프리리스트(freelist) 만이 테이블용으로 정의돼 있는 경우 일어날 있다.

다시하기(Redo) 로그 공간 요구

다시하기(Redo) 로그 공간 요구는 로그 버퍼가 다시하기 로그 활동 볼륨을 수용하기에는 너무 작을 발생한다.

Latch pin hit ratio

데이타베이스 인스턴스의 적중률은 init.ora 파일의 shared_pool_size 크기 조정에 대한 좋은 아이디어를 제공한다.

Table Fetch Continued Row

메트릭스는 데이타베이스와 연결된 행들에 대한 전반적인 지시를 제공한다. 연결이 과도한 경우 질의 서비스에 추가 I/O 요청되며 DBA 테이블의 PCTFREE 늘리고 테이블이 체인들을 해제하도록 재구성해야 한다.

STATSPACK 테이블의 구조 이해와 약간의 연습을 통해 사용자 정의형 보고서를 손쉽게 작성할 있게 된다.

 

스프레드시트를 사용한 오라클 트랜드 그래프 플로팅

일단 성능 데이타가 포착되면, STATSPACK 보고서를 테이블에 대해 실행시킬 있다. 또한 선형 회귀와 예측 모델을 결합하고 있기 때문에 DBA 이전 소모율을 토대로 디스크 메모리를 주문해야 하는 시기를 예측할 있다.

간단한 예제를 제시하기 위해 간단한 추출을 실행하고 이를 MS 엑셀에서 차트 위저드를 사용해 그려보기로 하겠다. 단계는 다음과 같다.

  1. SQL*Plus에서 STATSPACK 데이타에 대한 질의를 실행시킨다.
  2. 결과를 잘라내어 스프레드시트에 붙여 넣는다.
  3. MS 엑셀에서 바로 전에 붙여 넣어 하이라이트로 표시된 데이타를 이용해 드롭다운 메뉴에서 'DATA' 선택한 다음 'Text to Columns' 선택한다. 이렇게 하면 열들이 별도의 셀들로 분리될 것이다.
  4. 차트 위저드 버튼을 눌러 차트를 작성한다.


단계별 상세 설명 ( 단계별 이미지 대체)


1 단계. 우선 C 사용해 데이타를 복사한다.



2 단계. 다음 MS 엑셀을 열고, 사용해 데이타를 붙여 넣는다.



3 단계. 하이라이트로 강조된 데이타 열을 사용해 드롭다운 메뉴에서 'DATA' 선택한 'Text to Columns' 선택한다. 고정 구분 기능으로 'Text Wizard' 실행시킨다.



4 단계. 다음은 차트 위저드를 눌러 차트를 선택한다.



5 단계. 그래프가 거의 완성됐다.




이제 그래픽 툴도 알게 됐으니 STATSPACK 데이타를 사용해 자동으로 DBA에게 발생하게 문제를 경고해주는 일반적인 경고 스크립트에 대해 살펴보기로 하겠다.

 

DBA 위한 커스터마이즈된 예외 경고 보고서

STATSPACK 데이타에 대한 가장 효율적인 보고서 하나는 통계가 사전 정의된 임계값을 초과할 마다 아웃 바운드(out-of-bounds) 경보를 만들어 있는 기능이다. 부록에 있는 'alert.sql' 스크립트는 쉽게 단위로 실행되도록 스케줄링 있으며, DBA에게 모든 초과된 임계값을 보여주는 보고서를 제공하게 된다.

'ALERT.SQL' 의해 측정된 임계값들:

* Buffer hit ratio < 90%
* Redo log space requests > 20/hr
* Disk sorts > 20/hr
* Buffer bus waits > 10/hr
* Table fetch continued rows > 10,000/hr
* High I/O waits > 5,000/hr

f
일부 업체들은 경고 보고를 위해 STATSPACK 확장시키고 유닉스 성능 메트릭스를 저장하고 있다. 기술에 대한 세부사항은 Automating Oracle Tuning(본사 오라클 매거진 1996 7월호)에서 제공되고 있다. 물론, DBA 자체적인 목적에 따라 'ALERT.SQL' 손쉽게 정의할 있다. 일부 업체들은 관리자가 데이타베이스를 적절하게 운영하는데 있어서의 DBA 효율성을 모니터링하기 위해 스크립트를 설치해 사용하고 있다.

유닉스 서버가 e-메일과 연결되어 있는 업체의 경우 일간 경보 보고서가 DBA 메일로 전송된다. 이를 통해 DBA 지속적으로 모니터링을 해야 하는 지루한 작업으로부터 해방되어 보다 전문적인 작업에 집중할 있게 것이다.


부록

'STATSPACK_ALERT.SQL' 스크립트


도날드 벌레슨은 유명한 오라클 DBA 저널인 Oracle Internals 편집장이며, High-Performance Oracle8 Tuning, High-Performance Oracle Data Warehousing(Coriolis Publishing, 1997), 그리고 Oracle SAP Administration 등을 포함해 수많은 데이타베이스 관련 서적의 저자이기도 하다. 도날드의 웹사이트는 www.dba-oracle.com이다.


 

 

 

��  
 
 
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