간단하게 Statspack Install 방법에 대해서 말씀드리겠습니다.

1. timed_statistics 값이 TRUE로 설정되어 있어야 함.
$ sqlplus '/as sysdba'
SQL> show parameter timed_statistics 확인후 값 변경

2. spcreate.sql을 실행하여 Statspack Install
SQL> @?/rdbms/admin/spcreate 을 수행하면 (8i 기준)
Default tablespace : tools 입력
Default Temporary table : temp 입력

Creating Package STATSPACK...
Package created.
No errors.
Creating Package Body STATSPACK...
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

==> 8.1.7.4 버젼부터는 PERFSTAT 암호를 설치시 입력해줘야 하지만 그 이전버젼이라면 PERFSTAT란 기본암호를 가지고 생성된다.

3. STATSPACK Test


oracle:/oracle/ora817/kovait_statspack> sqlplus perfstat/perfstat
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Nov 24 17:31:06 2005
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

PERFSTAT> execute statspack.snap
PL/SQL procedure successfully completed.

PERFSTAT> execute statspack.snap
PL/SQL procedure successfully completed.

4. spreport를 수행하여 Report 보기
PERFSTAT> @?/rdbms/admin/spreport
      DB Id      DB Name Inst Num Instance
----------- ------------ -------- ------------
2467847496      KOVAIT8      1 KOVAIT8


Completed Snapshots

                          Snap                    Snap
Instance    DB Name        Id  Snap Started    Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
KOVAIT8      KOVAIT8          1 24 Nov 2005 17:31    5
                              2 24 Nov 2005 17:32    5


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End  Snapshot Id specified: 2

==> 이렇게 입력하시면 기본적으로 SP_1_2.lst 라고 생성될 것입니다. Report명을 주시는게 좋겠죠?

Enter value for report_name: kovait_statspack_1-2