미리 말해두지만 분석방법은 설명하지 않겠다.
사용방법만 언급하겠다.
참조
$ORACLE_HOME/rdbms/admin/spdoc.txt
expert one-on-one oracle - tom kyte
Oracle 9i DBA Handbook - kevin loney
Using Statspack in Oracle 8i and 9i to Identify Problems - Ian Jones(www.dbspecialists.com)
설치: @?/rdbms/admin/spcreate.sql
제거: @?/rdbms/admin/spdrop.sql
통계모으기: exec statspack.snap
통계분석: @?/rdbms/admin/spreport.sql
설치
[oracle@gw1 oracle]$ cd $ORACLE_HOME
[oracle@gw1 9.2.0]$ cd rdbms
[oracle@gw1 rdbms]$ cd admin
[oracle@gw1 rdbms]$ sqlplus "/ as sysdba"
SQL> create tablespace ts_stat
2 datafile '/u01/app/oracle/oradata/dev/ts_stat_01.dbf' size 200m
3 extent management local uniform size 128k;
$ORACLE_HOME/rdbms/admin/spcreate.sql파일은 perfstat라는 사용자를 생성한다.
그리고, 이 사용자에 여러개의 stat$ 시리즈 테이블을 생성한다.
성능분석데이터는 이 테이블에 저장되게 된다.
중간에 perfstat유저의 암호와 테이블스페이스를 입력하는 부분이 있다.
나머지는 객체를 생성하는 것이므로 생략한다.
SQL> @spcreate
중략..
Choose the PERFSTAT user's password.
Not specifying a password will result in the installation FAILING
Specify PERFSTAT password
Enter value for perfstat_password: xxxxxx
중략..
Below are the list of online tablespaces in this database.
Decide which tablespace you wish to create the STATSPACK tables
and indexes. This will also be the PERFSTAT user's default tablespace.
Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for performance data is not supported.
TABLESPACE_NAME CONTENTS
------------------------------ ---------
ACCOUNT PERMANENT
INDX PERMANENT
OEM_REPOSITORY PERMANENT
TEMP TEMPORARY
TOOLS PERMANENT
TS_P200401 PERMANENT
TS_P200402 PERMANENT
TS_P200403 PERMANENT
TS_P200404 PERMANENT
TS_P200405 PERMANENT
TS_P200406 PERMANENT
TS_QUEST PERMANENT
TS_SM_DATA_IDX PERMANENT
TS_STAT PERMANENT
UNDOTBS1 UNDO
USERS PERMANENT
16 rows selected.
Specify PERFSTAT user's default tablespace
Enter value for default_tablespace: ts_stat
Using ts_stat for the default tablespace
PL/SQL procedure successfully completed.
중략..
Choose the PERFSTAT user's temporary tablespace.
Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for the temporary tablespace is not recommended.
Specify PERFSTAT user's temporary tablespace.
Enter value for temporary_tablespace: temp
통계 모으기(여러번 주기적으로 수행한다.)
보통 dbms_job에 등록하여 주기적으로 수행되도록 한다.
처음에는 정상적인 운영상태에서 2일정도 돌려주어 DB상태에 대한 기준을 잡기 위한 용도로 사용하도록 한다.
비정상적인 Peak load시에는 앞서 수행한 것을 기준으로 어느 부분이 문제인지 파악하도록 한다.
수동
SQL> exec statspack.snap
..
SQL> exec statspack.snap
..
SQL> exec statspack.snap
통계분석
SQL> @spreport
statspack데이터 분석
SQL> exec DBMS_UTILITY.analyze_schema('PERFSTAT', 'COMPUTE');
오래된 데이터 삭제
SQL> set transaction use rollback segment roll_large;
SQL> @sppurge.sql
제거
SQL> conn / as sysdba
Connected.
SQL> @spdrop
사용자 제거
SQL> @spdusr
스키마 제거
SQL> @sptrunc (perfstat유저에서 작업)
아래는 spreport를 수행한 예를 보여준다.
단지 처리과정을 예로써 수행했기 때문에 그다지 심각하게 생각할 데이터는 아님을 미리 알려둔다.
실제 결과는 /tmp/sp_1_2.txt 파일에 저장된다.
솔직히 statspack의 수행과정은 그리 중요한 것이 아니라 결과로 나온 리포트파일(예에서는 /tmp/sp_1_2.txt)을
잘 분석할 줄 알아야 한다. 이 분석방법은 웹상에서 찾아보기를 권하겠다.
서적으로는 Thomas Kyte의 export on-on-one Oracle을 추천한다.
Oracle DB Programmer의 필독서이므로 없다면 구입하는 것이 좋을 것이다. 가격은 만만치 않다.
SQL> exec statspack.snap
PL/SQL procedure successfully completed.
SQL> exec statspack.snap
PL/SQL procedure successfully completed.
SQL> @?/rdbms/admin/spreport
SQL> Rem
SQL> Rem $Header: spreport.sql 22-apr-2001.15:44:01 cdialeri Exp $
SQL> Rem
SQL> Rem spreport.sql
SQL> Rem
SQL> Rem Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem spreport.sql
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script defaults the dbid and instance number to that of the
SQL> Rem current instance connected-to, then calls sprepins.sql to produce
SQL> Rem the standard Statspack report.
SQL> Rem
SQL> Rem NOTES
SQL> Rem Usually run as the STATSPACK owner, PERFSTAT
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem cdialeri 03/20/01 - 1747076
SQL> Rem cdialeri 03/12/01 - Created
SQL>
SQL> --
SQL> -- Get the current database/instance information - this will be used
SQL> -- later in the report along with bid, eid to lookup snapshots
SQL>
SQL> column inst_num heading "Inst Num" new_value inst_num format 99999;
SQL> column inst_name heading "Instance" new_value inst_name format a12;
SQL> column db_name heading "DB Name" new_value db_name format a12;
SQL> column dbid heading "DB Id" new_value dbid format 9999999999 just c;
SQL>
SQL> prompt
SQL> prompt Current Instance
Current Instance
SQL> prompt ~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~
SQL>
SQL> select d.dbid dbid
2 , d.name db_name
3 , i.instance_number inst_num
4 , i.instance_name inst_name
5 from v$database d,
6 v$instance i;
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3593162000 DEV 1 dev
SQL>
SQL> @@sprepins
SQL> Rem
SQL> Rem $Header: sprepins.sql 21-mar-2003.18:36:19 vbarrier Exp $
SQL> Rem
SQL> Rem sprepins.sql
SQL> Rem
SQL> Rem Copyright (c) 2001, 2003, Oracle Corporation. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem sprepins.sql - StatsPack Report Instance
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem SQL*Plus command file to report on differences between
SQL> Rem values recorded in two snapshots.
SQL> Rem
SQL> Rem This script requests the user for the dbid and instance number
SQL> Rem of the instance to report on, before producing the standard
SQL> Rem Statspack report.
SQL> Rem
SQL> Rem NOTES
SQL> Rem Usually run as the STATSPACK owner, PERFSTAT
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem vbarrier 03/21/03 - 2726042
SQL> Rem vbarrier 03/20/02 - Module in SQL reporting + 2188360
SQL> Rem vbarrier 03/05/02 - Segment Statistics
SQL> Rem spommere 02/14/02 - cleanup RAC stats that are no longer needed
SQL> Rem spommere 02/08/02 - 2212357
SQL> Rem cdialeri 02/07/02 - 2218573
SQL> Rem cdialeri 01/30/02 - 2184717
SQL> Rem cdialeri 01/09/02 - 9.2 - features 2
SQL> Rem ykunitom 12/21/01 - 1396578: fixed '% Non-Parse CPU'
SQL> Rem cdialeri 12/19/01 - 9.2 - features 1
SQL> Rem cdialeri 09/20/01 - 1767338,1910458,1774694
SQL> Rem cdialeri 04/26/01 - Renamed from spreport.sql
SQL> Rem cdialeri 03/02/01 - 9.0
SQL> Rem cdialeri 09/12/00 - sp_1404195
SQL> Rem cdialeri 07/10/00 - 1349995
SQL> Rem cdialeri 06/21/00 - 1336259
SQL> Rem cdialeri 04/06/00 - 1261813
SQL> Rem cdialeri 03/28/00 - sp_purge
SQL> Rem cdialeri 02/16/00 - 1191805
SQL> Rem cdialeri 11/01/99 - Enhance, 1059172
SQL> Rem cgervasi 06/16/98 - Remove references to wrqs
SQL> Rem cmlim 07/30/97 - Modified system events
SQL> Rem gwood.uk 02/30/94 - Modified
SQL> Rem densor.uk 03/31/93 - Modified
SQL> Rem cellis.uk 11/15/89 - Created
SQL> Rem
SQL>
SQL> clear break compute;
SQL> repfooter off;
SQL> ttitle off;
SQL> btitle off;
SQL> set timing off veri off space 1 flush on pause off termout on numwidth 10;
SQL> set echo off feedback off pagesize 60 linesize 80 newpage 1 recsep off;
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
3593162000 1 DEV dev gw1.skybada.
com
Using 3593162000 for database Id
Using 1 for instance number
Completed Snapshots
Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
dev DEV 1 11 Feb 2004 17:11 5
2 11 Feb 2004 17:12 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
End Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: /tmp/sp_1_2.txt
중략..
End of Report
SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
[oracle@gw1 admin]$ vi /tmp/sp_1_2.txt |
This article comes from dbakorea.pe.kr (Leave this line as is)
|
