안녕하세요 이번글에서는 oracle db 성능 분석을 위해 라이센스가 standard edition인 고객사에서 AWR report 사용 불가로 statspack 사용하게되는데 snapshot 주기 및 설정 방법을 포스팅 하도록 하겠습니다.
Statspack이란
Oracle DB내에서 특정 시간대의 데이터베이스에 대한 성능과 관련 데이터를 수집하여 database에 저장하여 두고 이로 부터 성능 분석 report를 생성해 내는 script들로 구성이 되어 있습니다.
- 한 시점의 성능 data들은 snapshot이라고 불려집니다.
- Statspack report는 두 시점의 snapshots들로부터 얻어집니다.
사전 확인
## 추출한 Report에 시간 정보 추가
SQL> show parameter timed_statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE
## 최소 statistics Level 지정
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_statistics_level string TYPICAL
statistics_level string TYPICAL
## 최소 1보다 큰 값으로 설정
'JOB_QUEUE__PROCESSES'의 값이 '0'이면 스케쥴러가 작동하지 않습니다.(기본셋팅)
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 0
SQL> alter system set job_queue_processes=1000;
System altered.
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
■ Statspack 생성
Statspack snapshot 전용 테이블 스페이스 생성
SQL> create tablespace stats datafile '+DATA' size 30G;
Tablespace created.
SQL> define default_tablespace='stats'
SQL> define temporary_tablespace='temp'
SQL> define perfstat_password='oracle'
SQL> @?/rdbms/admin/spcreate
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
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
----------------------------
STATS PERMANENT
SYSAUX PERMANENT
*
USERS PERMANENT
Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Using tablespace STATS 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.
Using tablespace temp as PERFSTAT temporary tablespace.
... Creating PERFSTAT user
... Installing required packages
... Creating views
... Granting privileges
NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.
SQL>
SQL> -- Next two scripts run as perfstat user
SQL> ALTER SESSION SET CURRENT_SCHEMA = PERFSTAT;
Session altered.
SQL>
SQL> -- Create statspack tables
SQL> @@spctab
SQL> Rem
SQL> Rem $Header: rdbms/admin/spctab.sql /main/56 2017/05/28 22:46:10 stanaya Exp $
SQL> Rem
SQL> Rem spctab.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2017, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem spctab.sql
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem SQL*PLUS command file to create tables to hold
SQL> Rem start and end "snapshot" statistical information
SQL> Rem
SQL> Rem NOTES
SQL> Rem Should be run as STATSPACK user, PERFSTAT
SQL> Rem
SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/spctab.sql
SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/spctab.sql
SQL> Rem SQL_PHASE: UTILITY
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem END SQL_FILE_METADATA
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem pjotawar 09/16/16 - Bug 23481673 Support Integrated Replicat
SQL> Rem zhefan 11/06/14 - Bug #19933671
SQL> Rem pmurthy 02/20/14 - To Fix Bug - 18284201 and 18273117
SQL> Rem kchou 10/30/13 - Bug# 17504669: Add New Column
SQL> Rem remaster_type to STATS$DYNAMIC_REMASTER_STATS
SQL> Rem shsong 06/29/11 - shsong 06/28/11 - Bug 12702106: display
SQL> Rem v$IOSTAT_FUNCTION_DETAIL
SQL> Rem traney 04/06/11 - 35209: long identifiers dictionary upgrade
SQL> Rem kchou 01/10/11 - Forward Merge of Bug Fix 9800868 to 12.1 Mainline
SQL> Rem kchou 08/11/10 - Bug#9800868 - Add Missing Idle Events for
SQL> Rem 11.2.0.2for Statspack & Standby Statspack
SQL> Rem kchou 08/11/10 - Bug#9800868 - Add missing idle events to 11.2.0.2
SQL> Rem kchou 01/10/11 - XbranchMerge kchou_bug-9800868 from
SQL> Rem st_rdbms_11.2.0
SQL> Rem cgervasi 05/13/09 - add idle event: cell worker idle
SQL> Rem cgervasi 04/02/09 - bug8395154: missing idle events
SQL> Rem rhlee 02/22/08 -
> Rem cdgreen 03/14/07 - 11 F2
SQL> Rem shsong 06/14/07 - Add idle events
SQL> Rem cdgreen 02/28/07 - 5908354
SQL> Rem cdgreen 04/26/06 - 11 F1
SQL> Rem cdgreen 06/26/06 - Increase column length
SQL> Rem cdgreen 05/10/06 - 5215982
SQL> Rem cdgreen 05/24/05 - 4246955
SQL> Rem cdgreen 04/18/05 - 4228432
SQL> Rem cdgreen 03/08/05 - 10gR2 misc
SQL> Rem vbarrier 02/18/05 - 4081984
SQL> Rem cdgreen 10/29/04 - 10gR2_sqlstats
SQL> Rem cdgreen 07/16/04 - 10gR2
SQL> Rem cdialeri 03/25/04 - 3516921
SQL> Rem vbarrier 02/12/04 - 3412853
SQL> Rem cdialeri 12/04/03 - 3290482
SQL> Rem cdialeri 11/05/03 - 3202706
SQL> Rem cdialeri 10/14/03 - 10g - streams - rvenkate
SQL> Rem cdialeri 08/05/03 - 10g F3
SQL> Rem cdialeri 02/27/03 - 10g F2: baseline, purge
SQL> Rem vbarrier 02/25/03 - 10g RAC
SQL> Rem cdialeri 11/15/02 - 10g F1
SQL> Rem cdialeri 09/27/02 - sleep4
SQL> Rem vbarrier 03/20/02 - 2143634
SQL> Rem vbarrier 03/05/02 - Segment Statistics
SQL> Rem cdialeri 02/07/02 - 2218573
SQL> Rem cdialeri 01/30/02 - 2184717
SQL> Rem cdialeri 01/11/02 - 9.2 - features 2
SQL> Rem cdialeri 11/30/01 - 9.2 - features 1
SQL> Rem cdialeri 04/22/01 - Undostat changes
SQL> Rem cdialeri 03/02/01 - 9.0
SQL> Rem cdialeri 09/12/00 - sp_1404195
SQL> Rem cdialeri 04/07/00 - 1261813
SQL> Rem cdialeri 03/20/00 - Support for purge
SQL> Rem cdialeri 02/16/00 - 1191805
SQL> Rem cdialeri 01/26/00 - 1169401
SQL> Rem cdialeri 11/01/99 - Enhance, 1059172
SQL> Rem cmlim 07/17/97 - Added STATS$SQLAREA to store top sql stmts
SQL> Rem gwood 10/16/95 - Version to run as sys without using many views
SQL> Rem cellis.uk 11/15/89 - Created
SQL> Rem
SQL>
SQL> set showmode off echo off;
If this script is automatically called from spcreate (which is
the supported method), all STATSPACK segments will be created in
the PERFSTAT user's default tablespace.
Using stats tablespace to store Statspack objects
... Creating STATS$SNAPSHOT_ID Sequence
Sequence created.
Synonym created.
... Creating STATS$... tables
Table created.
(중략)
1 row created.
Commit complete.
Synonym created.
Synonym created.
NOTE:
SPCTAB complete. Please check spctab.lis for any errors.
SQL>
SQL> -- Create the statistics Package
SQL> @@spcpkg
SQL> Rem
SQL> Rem $Header: rdbms/admin/spcpkg.sql /main/56 2017/05/28 22:46:10 stanaya Exp $
SQL> Rem
SQL> Rem spcpkg.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2017, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem spcpkg.sql
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem SQL*PLUS command file to create statistics package
SQL> Rem
SQL> Rem NOTES
SQL> Rem Must be run as the STATSPACK owner, PERFSTAT
SQL> Rem
SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/spcpkg.sql
SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/spcpkg.sql
SQL> Rem SQL_PHASE: UTILITY
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem END SQL_FILE_METADATA
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem kchou 11/04/13 - Bug# 17504669:Add New Column remaster_type to
SQL> Rem STATS$DYNAMIC_REMASTER_STATS
SQL> Rem shsong 06/29/11 - shsong 06/28/11 - Bug 12702106: display
SQL> Rem v$IOSTAT_FUNCTION_DETAIL
SQL> Rem arogers 01/23/08 - 6523482 - change VM_IN/OUT_BYTES id numbers
SQL> Rem cdgreen 03/14/07 - 11 F2
SQL> Rem shsong 06/14/07 - Fix BUFFER_GETS
SQL> Rem cdgreen 04/05/07 - 5691086
SQL> Rem cdgreen 03/02/07 - use _FG for v$system_event
SQL> Rem cdgreen 03/02/07 - 5913378
SQL> Rem cdgreen 05/16/06 - 11 F1
SQL> Rem cdgreen 05/10/06 - 5215982
SQL> Rem cdgreen 05/24/05 - 4246955
SQL> Rem cdgreen 04/18/05 - 4228432
SQL> Rem cdgreen 02/28/05 - 10gR2 misc
SQL> Rem vbarrier 02/18/05 - 4081984
SQL> Rem cdgreen 01/25/05 - 4143812
SQL> Rem cdgreen 10/29/04 - 10gR2_sqlstats
SQL> Rem cdgreen 10/25/04 - 3970898
SQL> Rem cdgreen 07/16/04 - 10g R2
SQL> Rem vbarrier 03/18/04 - 3517841
SQL> Rem vbarrier 02/12/04 - 3412853
SQL> Rem cdialeri 12/04/03 - 3290482
SQL> Rem cdialeri 11/05/03 - 3202706
SQL> Rem cdialeri 10/14/03 - 10g - streams - rvenkate
SQL> Rem cdialeri 08/05/03 - 10g F3
SQL> Rem cdialeri 07/31/03 - 2804307
SQL> Rem vbarrier 02/25/03 - 10g RAC
SQL> Rem cdialeri 01/28/03 - 10g F2: baseline, purge
SQL> Rem cdialeri 11/15/02 - 10g F1
SQL> Rem cdialeri 10/29/02 - 2648471
SQL> Rem cdialeri 09/11/02 - 1995145
SQL> Rem vbarrier 04/18/02 - 2271895
SQL> Rem vbarrier 03/20/02 - 2184504
SQL> Rem spommere 03/19/02 - 2274095
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 cdialeri 11/30/01 - 9.2 - features 1
SQL> Rem hbergh 08/23/01 - 1940915: use substrb on sql_text
SQL> Rem cdialeri 04/26/01 - 9.0
SQL> Rem cdialeri 09/12/00 - sp_1404195
SQL> Rem cdialeri 04/07/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> set echo off;
Creating Package STATSPACK...
Package created.
No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.■ Perfstat 유저 확인(자동 생성)
SQL> select username,default_tablespace from dba_users where username='PERFSTAT';
USERNAME
--------------------------------------------------------------------------------
PERFSTAT
1 row selected.
■ Statspack snapshot 확인
SQL> SELECT SNAP_ID, to_char(SNAP_TIME,'yyyy-mm-dd hh24:mi:ss') as SNAP_TIME, SNAP_LEVEL FROM STATS$SNAPSHOT;
no rows selected
■ snapshot 생성
SQL> execute statspack.snap;
PL/SQL procedure successfully completed.
SQL> SELECT SNAP_ID, SNAP_TIME, SNAP_LEVEL FROM STATS$SNAPSHOT;
SNAP_ID SNAP_TIME SNAP_LEVEL
---------- --------- ----------
1 08-MAR-22 5
1 row selected.
■ Statspack Job 생성
statspack 자동 수집 dbms_job 생성 스크립트 확인
[oracle@jh-test]vi $ORACLE_HOME/rdbms/admin/spauto.sql
Rem
Rem $Header: rdbms/admin/spauto.sql /main/5 2017/05/28 22:46:10 stanaya Exp $
Rem
Rem spauto.sql
Rem
Rem Copyright (c) 1999, 2017, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem spauto.sql
Rem
Rem DESCRIPTION
Rem SQL*PLUS command file to automate the collection of STATPACK
Rem statistics.
Rem
Rem NOTES
Rem Should be run as the STATSPACK owner, PERFSTAT.
Rem Requires job_queue_processes init.ora parameter to be
Rem set to a number >0 before automatic statistics gathering
Rem will run.
Rem
Rem BEGIN SQL_FILE_METADATA
Rem SQL_SOURCE_FILE: rdbms/admin/spauto.sql
Rem SQL_SHIPPED_FILE: rdbms/admin/spauto.sql
Rem SQL_PHASE: UTILITY
Rem SQL_STARTUP_MODE: NORMAL
Rem SQL_IGNORABLE_ERRORS: NONE
Rem END SQL_FILE_METADATA
Rem
Rem MODIFIED (MM/DD/YY)
Rem cdialeri 02/16/00 - 1191805
Rem cdialeri 12/06/99 - 1059172, 1103031
Rem cdialeri 08/13/99 - Created
Rem
spool spauto.lis
--
-- Schedule a snapshot to be run on this instance every hour, on the hour
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
commit;
end;
/
prompt
prompt Job number for automated statistics collection for this instance
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt Note that this job number is needed when modifying or removing
prompt the job:
print jobno
prompt
prompt Job queue process
prompt ~~~~~~~~~~~~~~~~~
prompt Below is the current setting of the job_queue_processes init.ora
prompt parameter - the value for this parameter must be greater
prompt than 0 to use automatic statistics gathering:
show parameter job_queue_processes
prompt
prompt
prompt Next scheduled run
prompt ~~~~~~~~~~~~~~~~~~
prompt The next scheduled run for this job is:
select job, next_date, next_sec
from user_jobs
where job = :jobno;
spool off;
## default Interval은 1시간으로 설정되어 있으나, 상세한 분석을 위해 10분 권장
## 10분 간격으로 Snapshot Interval 변경 ※SYSDATE+1/24/6 -> 10분 의미 SYSDATE+1/24/3 -> 20분 의미
37 line -> dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'SYSDATE+1/24/6', TRUE, :instno);
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
----------
1
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 1000
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
JOB NEXT_DATE NEXT_SEC
---------- --------- --------------------------------
1 08-MAR-22 06:00:00
SQL>
set linesize 200
set pagesize 20000
col what for a20
select job,NEXT_DATE,NEXT_SEC,WHAT from user_jobs;
JOB NEXT_DATE NEXT_SEC WHAT
---------- --------- -------------------------------- --------------------
1 08-MAR-22 06:00:00 statspack.snap;
※ 권한 에러 발생시 sys유저로 perfstat유저에게 권한 할당
SQL> grant create any job to perfstat;
SQL> grant manage scheduler to perfstat;
'DataBase > Oracle' 카테고리의 다른 글
| [Oracle - Statspack 그래프 출력 use Python] part 3 (0) | 2022.03.10 |
|---|---|
| [Oracle - Statspack manual / Snapshot설정] part 2 (0) | 2022.03.10 |
| [Oracle - killed 세션] (0) | 2022.03.10 |
| [Oracle - PDB접속 방법 use TNS] (0) | 2022.03.10 |
| [Oracle - RESTART 19c install] part 2 (0) | 2022.01.22 |