Statspack Job 스크립트 수정 및 생성에 이어 snapshot 관리 manual을 포스팅하도록 하겠습니다.
사전 구성
part1 URL : https://jhdatabase.tistory.com/48
[Oracle - Statspack manual / Job 생성] part 1
안녕하세요 이번글에서는 oracle db 성능 분석을 위해 라이센스가 standard edition인 고객사에서 AWR report 사용 불가로 statspack 사용하게되는데 snapshot 주기 및 설정 방법을 포스팅 하도록 하겠습니다. S
jhdatabase.tistory.com
Snapshot 삭제(delete)
SQL> show user;
USER is "PERFSTAT"
SQL> @?/rdbms/admin/sppurge
Database Instance currently connected to
========================================
Instance
DB Id DB Name Inst Num Name
----------- ---------- -------- ----------
1558213234 DB0225 1 DB0225
Snapshots for this database instance
====================================
Base- Snap
Snap Id Snapshot Started line? Level Host Comment
-------- --------------------- ----- ----- --------------- --------------------
1 08 Mar 2022 04:45:51 5 jh-test
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
Using 1 for lower bound.
Enter value for hisnapid: 1
Using 1 for upper bound.
Deleting snapshots 1 - 1.
Number of Snapshots purged: 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Purge of specified Snapshot range complete.
SQL> select snap_id, snap_time, snap_level from stats$snapshot;
no rows selected
Snapshot 모두 삭제(truncate)
- sppurge.sql은 내부적으로 table Delete를 수행하므로 Snapshot이 많을 경우 delete 속도가 느리며 Resource를 과도하게 사용할 수 있습니다.
- sptrunc.sql은 내부적으로 table을 truncate를 수행하므로 부하가 없고 매우 빠르지만 Snapshot 범위를 지정할 수 없습니다.
SQL> show user;
USER is "PERFSTAT"
SQL> execute statspack.snap;
SQL> execute statspack.snap;
SQL> execute statspack.snap;
SQL> execute statspack.snap;
SQL> execute statspack.snap;
PL/SQL procedure successfully completed.
SQL> select snap_id, to_char(snap_time,'yyyy-mm-dd hh24:mi:ss') as snap_time, snap_level from stats$snapshot order by 2;
Snap Id SNAP_TIME SNAP_LEVEL
-------- ------------------- ----------
2 2022-03-08 06:08:51 5
3 2022-03-08 06:08:52 5
4 2022-03-08 06:08:56 5
5 2022-03-08 06:08:58 5
4 rows selected.
SQL> @?/rdbms/admin/sptrunc
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: <Enter>
Entered at the 'begin_or_exit' prompt
... Starting truncate operation
Table truncated.
Table truncated.
Table truncated.
...(중략)
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 Interval 변경 방법
■ snapshot 수집, next time 확인
SQL> select job,NEXT_DATE,NEXT_SEC,WHAT from user_jobs;
JOB NEXT_DATE NEXT_SEC WHAT
---------- --------- -------------------------------- --------------------
6 08-MAR-22 07:00:00 statspack.snap;
SQL> execute dbms_job.next_date(6,SYSDATE+1/24/3); => 20분 간격으로 수정, 6은 job번호 의미. 한시간으로 수정 방법 => execute dbms_job.next_date(22,SYSDATE+1/24);
PL/SQL procedure successfully completed.
SQL> select job,NEXT_DATE,NEXT_SEC,WHAT from user_jobs;
JOB NEXT_DATE NEXT_SEC WHAT
--------------------------------------------------------------------------------
6 08-MAR-22 07:01:22 statspack.snap;
SQL> !date
Tue Mar 8 06:41:45 UTC 2022
Snapshot 보관 주기 32일 설정. 이후 snapshot모두 삭제
한 달전까지 모든 Snapshot을 비교하기 위해서는 snapshot 보관주기를 32일까지로 설정해서 관리해야 합니다.
##Snapshot 삭제 Package Spec 생성
CREATE OR REPLACE PACKAGE sppurpkg
IS
PROCEDURE purge(in_days_older_than IN INTEGER);
END sppurpkg;
/
##Snapshot 삭제 Package Body & Procedure 생성
CREATE OR REPLACE PACKAGE BODY sppurpkg
IS
PROCEDURE purge(in_days_older_than IN INTEGER)
IS
CURSOR get_snaps(in_days IN INTEGER) IS
SELECT s.rowid,
s.snap_id,
s.dbid,
s.instance_number
FROM stats$snapshot s,
sys.v_$database d,
sys.v_$instance i
WHERE s.dbid = d.dbid
AND s.instance_number = i.instance_number
AND s.snap_time < TRUNC(SYSDATE) - in_days;
errcontext VARCHAR2(100);
errmsg VARCHAR2(1000);
save_module VARCHAR2(48);
save_action VARCHAR2(32);
BEGIN
errcontext := 'save settings of DBMS_APPLICATION_INFO';
dbms_application_info.read_module(save_module, save_action);
dbms_application_info.set_module('SPPURPKG.PURGE', 'begin');
errcontext := 'open/fetch get_snaps';
dbms_application_info.set_action(errcontext);
FOR x IN get_snaps(in_days_older_than)
LOOP
errcontext := 'delete (cascade) STATS$SNAPSHOT';
dbms_application_info.set_action(errcontext);
DELETE
FROM stats$snapshot
WHERE ROWID = x.rowid;
errcontext := 'delete "dangling" STATS$SQLTEXT rows';
dbms_application_info.set_action(errcontext);
DELETE
FROM stats$sqltext
WHERE (old_hash_value, text_subset) not in
(SELECT /*+ hash_aj (ss) */old_hash_value, text_subset
FROM stats$sql_summary ss
);
errcontext := 'delete "dangling" STATS$DATABASE_INSTANCE rows';
dbms_application_info.set_action(errcontext);
DELETE
FROM stats$database_instance i
WHERE i.instance_number = x.instance_number
AND i.dbid = x.dbid
AND NOT EXISTS
(SELECT 1
FROM stats$snapshot s
WHERE s.dbid = i.dbid
AND s.instance_number = i.instance_number
AND s.startup_time = i.startup_time
);
errcontext := 'delete "dangling" STATS$STATSPACK_PARAMETER rows';
dbms_application_info.set_action(errcontext);
DELETE
FROM stats$statspack_parameter p
WHERE p.instance_number = x.instance_number
AND p.dbid = x.dbid
AND NOT EXISTS
(SELECT 1
FROM stats$snapshot s
WHERE s.dbid = p.dbid
AND s.instance_number = p.instance_number
);
errcontext := 'fetch/close get_snaps';
dbms_application_info.set_action(errcontext);
END LOOP;
errcontext := 'restore saved settings of DBMS_APPLICATION_INFO';
dbms_application_info.set_module(save_module, save_action);
EXCEPTION
WHEN OTHERS THEN
errmsg := sqlerrm;
dbms_application_info.set_module(save_module, save_action);
raise_application_error(-20000, errcontext || ': ' || errmsg);
END purge;
END sppurpkg;
/
SET TERMOUT ON
SHOW ERRORS
##생성한 Package Job 생성(30일이 지난 Snapshot 삭제) 매일 06:58:05초에 수행
VARIABLE jobno number;
BEGIN
dbms_job.submit(:jobno, 'sppurpkg.purge(30);', sysdate+(1/1440), 'SYSDATE+1', TRUE);
COMMIT;
END;
/
SQL> print jobno
JOBNO
----------
7
SQL> select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') next_date,next_sec,what from user_jobs;
JOB NEXT_DATE NEXT_SEC WHAT
--------------------------------------------------------------------------------
6 2022-03-08 07:01:22 07:01:22 statspack.snap;
7 2022-03-08 06:58:05 06:58:05 sppurpkg.purge(30);
SQL> SELECT SNAP_ID, to_char(SNAP_TIME,'yyyy-mm-dd hh24:mi:ss') as SNAP_TIME, SNAP_LEVEL FROM STATS$SNAPSHOT;
SNAP_ID SNAP_TIME SNAP_LEVEL
---------- ------------------- ----------
6 2022-03-08 06:30:48 5
■ 삭제된 Snapshot 확인
SQL> SELECT SNAP_ID, to_char(SNAP_TIME,'yyyy-mm-dd hh24:mi:ss') as SNAP_TIME, SNAP_LEVEL FROM STATS$SNAPSHOT;
SNAP_ID SNAP_TIME SNAP_LEVEL
---------- ------------------- ----------
6 2022-03-08 06:30:48 5
Statspack 삭제
SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/spdrop.sql
(중략)
NOTE:
SPDUSR complete. Please check spdusr.lis for any errors.
SQL> select username,default_tablespace from dba_users where username='PERFSTAT';
no rows selected
SQL> select tablespace_name from dba_data_files where tablespace_name='STATS';
TABLESPACE_NAME
------------------------------
STATS
SQL> drop tablespace STATS including contents and datafiles;
Tablespace dropped.
'DataBase > Oracle' 카테고리의 다른 글
| [Oracle - Shrink Space] (0) | 2022.07.06 |
|---|---|
| [Oracle - Statspack 그래프 출력 use Python] part 3 (0) | 2022.03.10 |
| [Oracle - Statspack manual & Job 생성] part 1 (0) | 2022.03.10 |
| [Oracle - killed 세션] (0) | 2022.03.10 |
| [Oracle - PDB접속 방법 use TNS] (0) | 2022.03.10 |