Database/Oracle
[Oracle] 19c Statspack(perfstat) 사용 가이드
이상한금요일
2025. 3. 17. 14:19
728x90
statspack 설치
statspack 용 테이블스페이스 생성(선택사항)
statspack을 설치하기에 앞서 statspack 스냅샷을 보관할 테이블스페이스를 생성
(존재하는 테이블스페이스를 사용해도 무방)
SQL>
create tablespace perfstat
datafile '/oradata/ORCL/perfstat01.dbf' size 100m
autoextend on;
statspack 설치
SQL> @?/rdbms/admin/spcreate.sql
Session altered.
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: [패스워드 입력]
oracle
Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.
Choose the PERFSTAT users's default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.
TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
------------------------------ --------------------- ----------------------------
PERFSTAT PERMANENT
SYSAUX PERMANENT *
USERS PERMANENT
Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: [perfstat 용 테이블스페이스 입력, 엔터시 default 테이블스페이스로 지정됨]
Using tablespace PERFSTAT as PERFSTAT default tablespace.
Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.
Choose the PERFSTAT user's Temporary tablespace.
TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
------------------------------ --------------------- --------------------------
TEMP TEMPORARY *
Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace: [perfstat 용 템프 테이블스페이스 입력 , 엔터시 default 템프 테이블스페이스로 지정됨]
Using tablespace TEMP as PERFSTAT temporary tablespace.
... Creating PERFSTAT user
... Installing required packages
...
* perfstat 이라는 스키마가 생성됨.
SQL> select username,default_tablespace from dba_users where username='PERFSTAT';
USERNAME DEFAULT_TABLESPACE
------------------------------------------------------- ------------------------------
PERFSTAT PERFSTAT
* perfstat 소유로 statspack 테이블스페이스(위에서 지정한 default tablespace)에 STAT$ 테이블이 생성됨.
이곳에 성능 분석 데이터가 저장됨.
스냅샷 생성
아래는 스냅샷 자동/수동 생성 방법들을 기술함.
1) statspack 스냅샷 자동 생성 1 - spauto.sql
오라클 내장 스크립트(spauto.sql)를 사용해 일정 주기로 스냅샷을 자동 생성하는 방법
spauto.sql 스크립트 수정(선택 사항)
(기본값 : 1시간, 46번 줄 수정)
$ vi $ORACLE_HOME/rdbms/admin/spauto.sql
...
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
...
- JOB : JOB QUEUE에 등록된 작업 번호, 자동으로 생성됨
- WHAT : 실행 대상, 쿼리나 프로시저로 세미콜론(;)으로 끝나야 함
- NEXT_DATE : 다음 작업이 실행될 시간, default는 SYSDATE
- INTERVAL : 실행 주기, 초 단위까지 지정 가능, default는 NULL
- 일/시/분/초 형태
- SYSDATE+7 : 7일에 한 번
- SYSDATE+1/24 : 1시간에 한 번
- SYSDATE+1/24/60 : 1분에 한 번
- SYSDATE+1/24/60/10 : 6초에 한 번
- NO_PARSE : 실행 시 파싱 여부
- TRUE로 설정되어 있다면 JOB과 연관된 프로시저를 JOB 첫 실행 시점에 파싱한다.
예를 들어 관련 테이블이 생성되기 전에 SUBMIT하기 원한다면 TRUE로 설정하면 됨.
default는 FALSE
- TRUE로 설정되어 있다면 JOB과 연관된 프로시저를 JOB 첫 실행 시점에 파싱한다.
만약 1분 단위로 수집하고 싶다면 아래와 같이 수정
dbms_job.submit(:jobno, 'statspack.snap;', sysdate+1/24/60, 'SYSDATE+1/24/60', TRUE, :instno);
perfstat에 권한 할당
SQL> grant create any job to perfstat;
SQL> grant manage scheduler to perfstat;
스냅샷 자동 생성 job 실행
SQL> conn perfstat/perfstat
SQL> @?/rdbms/admin/spauto.sql
PL/SQL procedure successfully completed.
Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:
JOBNO
----------
10
Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 20
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
JOB NEXT_DATE NEXT_SEC
---------- --------- --------------------------------
10 17-MAR-25 15:42:29
스냅샷 자동 생성 job 조회
SQL> conn perfstat/perfstat
SQL> set lines 300 pages 200
SQL> col interval for a40
SQL> select job, next_date, next_sec, interval from user_jobs;
JOB NEXT_DATE NEXT_SEC INTERVAL
---------- --------- -------------------------------- ----------------------------------------
10 17-MAR-25 15:42:29 SYSDATE+1/24/60
2) statspack 스냅샷 자동 생성 2 - crontab
crontab(root 또는 oracle)에 등록해 일정 주기로 스냅샷을 자동 생성하는 방법
-- statspack.sql 파일 생성
$ cat statspack.sql
execute statspack.snap;
exit
-- 실행권한 부여
$ chmod +x statspack.sql
-- crontab 등록(root)
-- 1시간 주기로 스크립트 실행(1시간 주기 스냅샷 생성)
# crontab -e
...
0 * * * * /usr/bin/su - oracle -c "/oracle/product/19.3/db/bin/sqlplus -s perfstat/perfstat @/home/oracle/statspack.sql"
3) statspack 스냅샷 수동 생성
SQL> conn perfstat/perfstat
Connected.
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
statspack 스냅샷 조회
SQL> select snap_id, snap_time from stats$snapshot order by 1;
SNAP_ID SNAP_TIME
---------- ---------
1 17-MAR-25
2 17-MAR-25
statspack 리포트 생성
SQL> conn perfstat/perfstat
Connected.
SQL> @?/rdbms/admin/spreport.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1705367826 ORCL 1 ORCL
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
1705367826 1 ORCL ORCL dev1
Using 1705367826 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
ORCL ORCL 1 17 Mar 2025 13:33 5
2 17 Mar 2025 13:34 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
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:
Using the report name sp_1_2
...
스냅샷 삭제
스냅샷 삭제 방법에 대해 기술함.
1) 불필요한 스냅샷 삭제
SQL> @?/rdbms/admin/sppurge.sql
Database Instance currently connected to
========================================
Instance
DB Id DB Name Inst Num Name
----------- ---------- -------- ----------
323838205 ORCL19 1 ORCL19
Snapshots for this database instance
====================================
Base- Snap
Snap Id Snapshot Started line? Level Host Comment
-------- --------------------- ----- ----- --------------- --------------------
1 28 Jul 2020 23:21:03 5 ORACLE19
2 28 Jul 2020 23:23:37 5 ORACLE19
Warning
~~~~~~~
sppurge.sql deletes all snapshots ranging between the lower and
upper bound Snapshot Id''s specified, for the database instance
you are connected to. Snapshots identified as Baseline snapshots
which lie within the snapshot range will not be purged.
It is NOT possible to rollback changes once the purge begins.
You may wish to export this data before continuing.
Specify the Lo Snap Id and Hi Snap Id range to purge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for losnapid: 1 [삭제하고픈 스냅샷 시작점 입력(스냅샷 ID)]
Using 1 for lower bound.
Enter value for hisnapid: 2 [삭제하고픈 스냅샷 종료점 입력(스냅샷 ID)]
Using 2 for upper bound.
Deleting snapshots 1 - 2.
Number of Snapshots purged: 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Purge of specified Snapshot range complete.
2) statspack 스냅샷 일괄 삭제
SQL> @?/rdbms/admin/sptrunc.sql
Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables. You may
wish to export the data before continuing.
About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If would like to exit WITHOUT truncating the tables, enter any text at the
begin_or_exit prompt (e.g. 'exit'), otherwise if you would like to begin
the truncate operation, press <return>
Enter value for begin_or_exit: [엔터]
Entered at the 'begin_or_exit' prompt
... Starting truncate operation
스냅샷 자동 생성 job 삭제
spauto.sql을 통해 job을 생성했을 시
job 조회 후 job번호(JOB 컬럼) 확인
SQL> conn perfstat/perfstat
SQL> set lines 300 pages 200
SQL> col interval for a40
SQL> select job, next_date, next_sec, interval from user_jobs;
JOB NEXT_DATE NEXT_SEC INTERVAL
---------- --------- -------------------------------- ----------------------------------------
10 17-MAR-25 15:47:29 SYSDATE+1/24/60
job 삭제
SQL> EXEC DBMS_JOB.REMOVE(job번호);
SQL> commit;
statspack 삭제
statspack 삭제 스크립트 실행
SQL> @?/rdbms/admin/spdrop.sql
* perfstat 스키마는 자동 삭제됨
SQL> select username,default_tablespace from dba_users where username='PERFSTAT';
no rows selected
statspack 용 테이블스페이스 삭제(선택 사항)
SQL> drop tablespace perfstat including contents and datafiles;
728x90