AWR 은 유료 ? STATSPACK 은 무료 !

STATSPACK (Oracle 8.1.6 이상)

STATSPACK 이란 ?

Statspack 은 Oracle 8.1.6 부터 Database 성능에 관련된 data를 수집, 가공, 저장하고 이를 report형태로 보여주는 기능을 제공한다.

과거에 제공되었던 UTLBSTAT/UTLESTAT 와 다른 점은 분석 data를 table내에 저장하여 원하는 기간별로 비교하여 분석할 수 있고, 이전에는 없었던 추가적인 정보를 보여준다.

PERFSTAT 이라는 user가 생성되고, 그 user에 의해 소유되는 table들에는 성능관련 통계정보들이 시간대별로 누적되어 저장되어 지므로 각 시간대별로 비교 분석이 가능하며, dbms_job이나 OS Utility (예를 들면 cron)등을 사용하여 주기적으로 Data의 수집이 가능하다.

 

STATSPACK 인스톨

Database Space Requirements

각 table의 Default INITIAL 값과 NEXT 값은 1M 이며, 최소 35M의 용량이 필요하다. (8.1.7 은 script명이 틀리므로 아래 기타부분을 참조)

Install Script

	$ sqlplus /as sysdba SQL> @$ORACLE_HOME/rdbms/admin/statscre …. Specify PERFSTAT user’s default tablespace: TOOLS …. Specify PERFSTAT user’s temporary tablespace: TEMP …. Enter tablespace where STATSPACK objects will be created: TOOLS ….

위 statscre.sql 을 수행하면 아래의 3개 SQL이 수행된다. 수행도중 tablespace 명을 입력 받는데, SYSTEM tablespace 명을 제외한 관련 tablespace 명을 주면 된다.

1. statscusr.sql – PERFSTAT user 생성 및 권한 부여

2. statsctab – 관련 table 생성

3. statspack – 관련 package 생성

Data 수집

Data 수집 command

$ sqlplus perfstat/perfstat SQL> execute statspack.snap;

§ 만약 환경이라면 data를 수집하고자 하는 instance에서 수행해야 한다.

위의 package를 수행하면 STATSPACK table에 해당 시점의 성능 통계 정보를 저장하고 이후의 특정 시점과 비교하는 기준이 된다.

이때 timed_statistics 를 true 로 준다면 시간 정보를 얻을 수 있다. (init.ora 혹은 ‘alter system set timed_statistics = true’ )

주기적으로 Data 수집하려면 ?

§ database 내에서는 dbms_job procedure를 사용

– $ORACLE_HOME/rdbms/admin/ststsauto.sql 참조

– init.ora 내에 job_queue_processes 가 0 보다 커야 한다.

§ OS utility 사용 (UNIX에서는 ‘cron’, NT에서는 ‘at’)

Report 생성

Report 생성 script

§ Enter beginning Snap Id – 기준이 되는 시점의 snapshot id 입력

§ Enter ending Snap Id – 종료 시점의 snapshot id 입력

PERFSTAT schema의 analyzing

Report를 생성시 성능을 향상시키기 위해서는 PERFSTAT user가 소유한 table과 index를 analyze 해야 한다.

	$ sqlplus internal SQL> execute dbms_utility.analyze_schema(‘PERFSTAT’,’COMPUTE’); 혹은 SQL> execute dbms_stats.gather_schema_stats(‘PERFSTAT’);

STATSPACK 관련 configuration

Snapshot Level

수집되는 정보의 양을 Level로 조절할 수가 있다.

	수집 정보	비고
Level 0	General Performance statistics	Wait stats, system event, system stats, rollback segment data, row cache, SGA, background event, session event, lock stats, buffer pool stats, parent latch stats
Level 5	Level 0 + SQL statement	모든 stats 및 resource 과다 SQL문
Level 10	Level 5 + Child Latches	Data수집에 많은 시간 소요

SQL> execute statspack.snap(i_snap_level => 10, i_modify_parameter => ‘true’);

i_snap_level 은 snapshot level을 지정하며, i_modify_parameter 는 stats$statspack_parameter table에 값을 저장할 것인지를 결정하는 것으로, 위의 예처럼 true 로 준다면 이후 level도 같은 값을 적용받을 것이고, 만약 false로 지정하거나 값을 주지 않는다면 해당 시점만 적용을 받는다.

Snapshot SQL threshold

Threshold를 두어 그 값을 초과하는 SQL만을 수집하기 위해 사용된다.

Default값

SQL 수행 횟수	100
SQL 수행시 disk read 수	1000
SQL 수행시 parse call 수	1000
SQL 수행시 buffer gets 수	10000

위 default 값도 마찬가지로 stats$statspack_parameter table 에 저장되어 있으며, STATSPACK package를 사용하여 변경이 가능하다.

	SQL> execute statspack.modify_statspack_parameter(i_snap_level => 10, i_buffer_gets_th => 10000, i_disk_reads_th => 1000);

위 procedure는 snapshot의 값을 저장하지 않고 단지 parameter만 바꾸는데에 사용이 된다.

Parameter Name	유효한 값의 범위	Default Value	비고
i_snap_level	0, 5, 10	5	Snapshot Level
i_ucomment	Text	Blank	Snapshot 설명
i_executions_th	Integer >= 0	100	SQL 수행 횟수
i_disk_reads_th	Integer >= 0	1000	SQL의 disk read 수
i_parse_calls+th	Integer >= 0	1000	SQL의 parse call 수
i_buffer_gets_th	Integer >= 0	10000	SQL의 buffer gets 수
i_session_id	v$session의 유효 SID	0 (no session)	통계치를 수집할 session ID
i_modify_parameter	True, False	False	변경값의 저장 여부

기타

통계정보를 저장할 특정 Session ID 지정

특정 session 을 지정해서 통계정보를 보고 싶다면

	SQL> execute statspack.snap(i_session_id => 3);

Wait event에 사용되는 Time 단위

Oracle 은 centisecond(1/100초) 를 wait time에 사용하고 있으나 이 statspack report에서는 IO time 관련해서는 millisecond(1/1000초)로 표현하고 있다.

성능 data의 공유

성능 data를 다른 site에서 공유하고자 한다면 PERFSTAT user를 export 받는다. export에 관한 parameter file은 $ORACLE_HOME/rdbms/admin/statsuexp.par 로 제공된다.

	$ exp userid=perfstat/perfstat parfile=statsuexp.par

해당 directory에 statsuexp.dmp file이 생성된다.

UTLBSTAT/UTLESTAT 과의 conflict

statspack과 같은 user에서 수행할 수 없다. stats$waitstat table명이 같기 때문이다.

Package의 삭제

	$ sqlplus internal SQL> @$ORACLE_HOME/rdbms/admin/statsdrp

Script 설명

Script 명(8.1.6)	설명	Script 명(8.1.7)
statscre.sql	STATSPACK 인스톨 script	spcreate.sql
statsdrp.sql	STATSPACK 삭제 script	spdrop.sql
statscusr.sql	STATSPACK user (PERFSTAT)생성	spcusr.sql
statsdusr.sql	STATSPACK user (PERFSTAT)삭제	spdusr.sql
statsctab.sql	STATSPACK table 생성	spctab.sql
statsdtab.sql	STATSPACK table 삭제	spdtab.sql
statspack.sql	STATSPACK package 생성	apcpkg.sql
statsrep.sql	STATSPACK report 생성	spreport.sql
statsauto.sql	자동 STATSPACK 실행(dbms_job)	spauto.sql
statsuexp.par	PERFSTAT user의 export parameter file	spuexp.par