【RDS for Oracle 】StatsPack設定 

使用しているインスタンスがSE2なので、StatsPackを設定してみる。

参考サイトはこちら

- [Oracle DB インスタンスのオプション](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.html)

- [Oracle Statspack](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.Statspack.html)

- [RDS for OracleのStatspackでデフォルト表領域を任意の場所へ変更する](https://dev.classmethod.jp/cloud/aws/rds-for-oracle-chage-tablespace-statspack/) 

# オプショングループの設定
今回はオプショングループがすでにあるため、そこにStatsPackのオプションを追加する。
RDSのコンソールから`オプショングループ`→`オプションに追加`

<img width="1568" alt="1.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/5df74430-f1b4-6e88-fe82-675237c8b89a.png">

オプションに`STATSPACK`を選択
<img width="844" alt="2.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/dd2e8256-9b90-8204-c44e-2566492e617f.png">

既存のオプショングループに追加される。
今回、既存のオプショングループはすでにインスタンス（ORCL1）に付与されているため、インスタンスにも反映される。
<img width="1585" alt="3.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/f85ee400-39f0-953d-ccc5-edba1ba65f29.png">

インスタンスのステータスが`変更中`になる。
<img width="1569" alt="4.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/833bfd9c-8676-e72f-87f3-c770c5db9380.png">

`利用可能`となったらインスタンスにPERFSTATSユーザができている
<img width="1580" alt="5.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/7f780ce4-4d2e-b73d-ccf2-282300337216.png">

# StatsPackの設定
インスタンスにマスターユーザでログイン。

PERFSTATユーザを確認すると、デフォルトでアカウントステータスは`LOCKED`、表領域は`SYSAUX`となっている。

表領域がSYSAUXだと、この表領域にスナップショットが取得されるため、SYSAUXの負荷が上がってしまう。

そのため、PERFSTAT用の表領域を作成する。

```sql
SQL> set pages 100 line 200
SQL> col username for a10
SQL> col account_status for a10
SQL> col default_tablespace for a10
SQL> col profile for a10
SQL> select username,account_status,default_tablespace,temporary_tablespace,profile from dba_users where username = 'PERFSTAT';

USERNAME   ACCOUNT_ST DEFAULT_TA TEMPORARY_TABLESPACE		PROFILE
---------- ---------- ---------- ------------------------------ ----------
PERFSTAT   LOCKED     SYSAUX	 TEMP				DEFAULT
```

既存の表領域の確認

```sql
SQL> col file_name for a60
SQL> select tablespace_name,file_name,status,bytes/1024/1024 mbytes,increment_by,autoextensible,online_status from dba_data_files;

TABLESPACE_NAME 	       FILE_NAME						    STATUS	  MBYTES INCREMENT_BY AUT ONLINE_
------------------------------ ------------------------------------------------------------ --------- ---------- ------------ --- -------
SYSTEM			       /rdsdbdata/db/ORCL1_A/datafile/o1_mf_system_gf76cbh1_.dbf    AVAILABLE	     400	12800 YES SYSTEM
SYSAUX			       /rdsdbdata/db/ORCL1_A/datafile/o1_mf_sysaux_gf76d1l1_.dbf    AVAILABLE	 548.625	12800 YES ONLINE
UNDO_T1 		       /rdsdbdata/db/ORCL1_A/datafile/o1_mf_undo_t1_gf76df41_.dbf   AVAILABLE	     300	 1280 YES ONLINE
USERS			       /rdsdbdata/db/ORCL1_A/datafile/o1_mf_users_gf76dh0r_.dbf     AVAILABLE	     100	12800 YES ONLINE
RDSADMIN		       /rdsdbdata/db/ORCL1_A/datafile/o1_mf_rdsadmin_gf77glhp_.dbf  AVAILABLE	       7	  128 YES ONLINE
```

PERFSTAT用の表領域としてSTATSPACK表領域を作成。

```sql
SQL> CREATE TABLESPACE STATSPACK DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Tablespace created.
```
表領域の確認

```sql
select tablespace_name,file_name,status,bytes/1024/1024 mbytes,increment_by,autoextensible,online_status from dba_data_files where tablespace_name='STATSPACK';

TABLESPACE_NAME 	       FILE_NAME						    STATUS	  MBYTES INCREMENT_BY AUT ONLINE_
------------------------------ ------------------------------------------------------------ --------- ---------- ------------ --- -------
STATSPACK		       /rdsdbdata/db/ORCL1_A/datafile/o1_mf_statspac_gktwxh9z_.dbf  AVAILABLE	     100	12800 YES ONLINE
```
アカウントロックを解除

```sql
SQL> alter user PERFSTAT identified by perfstat ACCOUNT UNLOCK;

User altered.
```
PERFSTATユーザのデフォルト表領域をSYSAUX→`STATSPACK`に変更

```sql
SQL> alter user PERFSTAT default tablespace STATSPACK;

User altered.
```

PERFSTATユーザのSTATSPACK表領域の使用容量の制限値を`無制限`に変更

```sql
SQL> alter user PERFSTAT QUOTA UNLIMITED ON STATSPACK;

User altered.
```

確認すると、アカウントステータスが`OPEN`、デフォルト表領域が`STATSPACK`になっている

```sql
SQL> select username,account_status,default_tablespace,temporary_tablespace,profile from dba_users where username = 'PERFSTAT';

USERNAME   ACCOUNT_ST DEFAULT_TA TEMPORARY_TABLESPACE		PROFILE
---------- ---------- ---------- ------------------------------ ----------
PERFSTAT   OPEN       STATSPACK  TEMP				DEFAULT
```

PERFSTATユーザが所有しているテーブルの表領域を確認すると、72個のテーブルの表領域が`SYSAUX`となっている。

```sql
SQL> col table_name for a30
SQL> select table_name, tablespace_name from dba_tables where owner = 'PERFSTAT';

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
STATS$DATABASE_INSTANCE        SYSAUX
STATS$LEVEL_DESCRIPTION        SYSAUX
STATS$SNAPSHOT		       SYSAUX
STATS$DB_CACHE_ADVICE	       SYSAUX
STATS$FILESTATXS	       SYSAUX
STATS$TEMPSTATXS	       SYSAUX
STATS$LATCH		       SYSAUX
STATS$LATCH_CHILDREN	       SYSAUX
STATS$LATCH_PARENT	       SYSAUX
STATS$LATCH_MISSES_SUMMARY     SYSAUX
STATS$LIBRARYCACHE	       SYSAUX
STATS$BUFFER_POOL_STATISTICS   SYSAUX
STATS$ROLLSTAT		       SYSAUX
STATS$ROWCACHE_SUMMARY	       SYSAUX
STATS$SGA		       SYSAUX
STATS$SGASTAT		       SYSAUX
STATS$SYSSTAT		       SYSAUX
STATS$SESSTAT		       SYSAUX
STATS$SYSTEM_EVENT	       SYSAUX
STATS$SESSION_EVENT	       SYSAUX
STATS$WAITSTAT		       SYSAUX
STATS$ENQUEUE_STATISTICS       SYSAUX
STATS$SQL_SUMMARY	       SYSAUX
STATS$SQLTEXT		       SYSAUX
STATS$SQL_STATISTICS	       SYSAUX
STATS$RESOURCE_LIMIT	       SYSAUX
STATS$DLM_MISC		       SYSAUX
STATS$CR_BLOCK_SERVER	       SYSAUX
STATS$CURRENT_BLOCK_SERVER     SYSAUX
STATS$INSTANCE_CACHE_TRANSFER  SYSAUX
STATS$UNDOSTAT		       SYSAUX
STATS$SQL_PLAN_USAGE	       SYSAUX
STATS$SQL_PLAN		       SYSAUX
STATS$SEG_STAT		       SYSAUX
STATS$SEG_STAT_OBJ	       SYSAUX
STATS$PGASTAT		       SYSAUX
STATS$PARAMETER 	       SYSAUX
STATS$INSTANCE_RECOVERY        SYSAUX
STATS$STATSPACK_PARAMETER      SYSAUX
STATS$SHARED_POOL_ADVICE       SYSAUX
STATS$SQL_WORKAREA_HISTOGRAM   SYSAUX
STATS$PGA_TARGET_ADVICE        SYSAUX
STATS$JAVA_POOL_ADVICE	       SYSAUX
STATS$THREAD		       SYSAUX
STATS$FILE_HISTOGRAM	       SYSAUX
STATS$EVENT_HISTOGRAM	       SYSAUX
STATS$TIME_MODEL_STATNAME      SYSAUX
STATS$SYS_TIME_MODEL	       SYSAUX
STATS$SESS_TIME_MODEL	       SYSAUX
STATS$STREAMS_CAPTURE	       SYSAUX
STATS$STREAMS_APPLY_SUM        SYSAUX
STATS$PROPAGATION_SENDER       SYSAUX
STATS$PROPAGATION_RECEIVER     SYSAUX
STATS$BUFFERED_QUEUES	       SYSAUX
STATS$BUFFERED_SUBSCRIBERS     SYSAUX
STATS$RULE_SET		       SYSAUX
STATS$OSSTATNAME	       SYSAUX
STATS$OSSTAT		       SYSAUX
STATS$PROCESS_ROLLUP	       SYSAUX
STATS$PROCESS_MEMORY_ROLLUP    SYSAUX
STATS$SGA_TARGET_ADVICE        SYSAUX
STATS$STREAMS_POOL_ADVICE      SYSAUX
STATS$MUTEX_SLEEP	       SYSAUX
STATS$DYNAMIC_REMASTER_STATS   SYSAUX
STATS$IOSTAT_FUNCTION_NAME     SYSAUX
STATS$IOSTAT_FUNCTION	       SYSAUX
STATS$IOSTAT_FUNCTION_DETAIL   SYSAUX
STATS$MEMORY_TARGET_ADVICE     SYSAUX
STATS$MEMORY_DYNAMIC_COMPS     SYSAUX
STATS$MEMORY_RESIZE_OPS        SYSAUX
STATS$INTERCONNECT_PINGS       SYSAUX
STATS$IDLE_EVENT	       SYSAUX
STATS$TEMP_SQLSTATS                     ★これだけSYSAUXではない

73 rows selected.
```

テーブルの表領域をSYSAUXから`STATSPACK表領域`に変更する。
select文でSQLを作成。

```sql
SQL> select  'alter table ' || table_name || ' move tablespace STATSPACK;'  from dba_tables where owner = 'PERFSTAT';

'ALTERTABLE'||TABLE_NAME||'MOVETABLESPACESTATSPACK;'
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter table STATS$DATABASE_INSTANCE move tablespace STATSPACK;
alter table STATS$LEVEL_DESCRIPTION move tablespace STATSPACK;
alter table STATS$SNAPSHOT move tablespace STATSPACK;
alter table STATS$DB_CACHE_ADVICE move tablespace STATSPACK;
alter table STATS$FILESTATXS move tablespace STATSPACK;
alter table STATS$TEMPSTATXS move tablespace STATSPACK;
alter table STATS$LATCH move tablespace STATSPACK;
alter table STATS$LATCH_CHILDREN move tablespace STATSPACK;
alter table STATS$LATCH_PARENT move tablespace STATSPACK;
alter table STATS$LATCH_MISSES_SUMMARY move tablespace STATSPACK;
alter table STATS$LIBRARYCACHE move tablespace STATSPACK;
alter table STATS$BUFFER_POOL_STATISTICS move tablespace STATSPACK;
alter table STATS$ROLLSTAT move tablespace STATSPACK;
alter table STATS$ROWCACHE_SUMMARY move tablespace STATSPACK;
alter table STATS$SGA move tablespace STATSPACK;
alter table STATS$SGASTAT move tablespace STATSPACK;
alter table STATS$SYSSTAT move tablespace STATSPACK;
alter table STATS$SESSTAT move tablespace STATSPACK;
alter table STATS$SYSTEM_EVENT move tablespace STATSPACK;
alter table STATS$SESSION_EVENT move tablespace STATSPACK;
alter table STATS$WAITSTAT move tablespace STATSPACK;
alter table STATS$ENQUEUE_STATISTICS move tablespace STATSPACK;
alter table STATS$SQL_SUMMARY move tablespace STATSPACK;
alter table STATS$SQLTEXT move tablespace STATSPACK;
alter table STATS$SQL_STATISTICS move tablespace STATSPACK;
alter table STATS$RESOURCE_LIMIT move tablespace STATSPACK;
alter table STATS$DLM_MISC move tablespace STATSPACK;
alter table STATS$CR_BLOCK_SERVER move tablespace STATSPACK;
alter table STATS$CURRENT_BLOCK_SERVER move tablespace STATSPACK;
alter table STATS$INSTANCE_CACHE_TRANSFER move tablespace STATSPACK;
alter table STATS$UNDOSTAT move tablespace STATSPACK;
alter table STATS$SQL_PLAN_USAGE move tablespace STATSPACK;
alter table STATS$SQL_PLAN move tablespace STATSPACK;
alter table STATS$SEG_STAT move tablespace STATSPACK;
alter table STATS$SEG_STAT_OBJ move tablespace STATSPACK;
alter table STATS$PGASTAT move tablespace STATSPACK;
alter table STATS$PARAMETER move tablespace STATSPACK;
alter table STATS$INSTANCE_RECOVERY move tablespace STATSPACK;
alter table STATS$STATSPACK_PARAMETER move tablespace STATSPACK;
alter table STATS$SHARED_POOL_ADVICE move tablespace STATSPACK;
alter table STATS$SQL_WORKAREA_HISTOGRAM move tablespace STATSPACK;
alter table STATS$PGA_TARGET_ADVICE move tablespace STATSPACK;
alter table STATS$JAVA_POOL_ADVICE move tablespace STATSPACK;
alter table STATS$THREAD move tablespace STATSPACK;
alter table STATS$FILE_HISTOGRAM move tablespace STATSPACK;
alter table STATS$EVENT_HISTOGRAM move tablespace STATSPACK;
alter table STATS$TIME_MODEL_STATNAME move tablespace STATSPACK;
alter table STATS$SYS_TIME_MODEL move tablespace STATSPACK;
alter table STATS$SESS_TIME_MODEL move tablespace STATSPACK;
alter table STATS$STREAMS_CAPTURE move tablespace STATSPACK;
alter table STATS$STREAMS_APPLY_SUM move tablespace STATSPACK;
alter table STATS$PROPAGATION_SENDER move tablespace STATSPACK;
alter table STATS$PROPAGATION_RECEIVER move tablespace STATSPACK;
alter table STATS$BUFFERED_QUEUES move tablespace STATSPACK;
alter table STATS$BUFFERED_SUBSCRIBERS move tablespace STATSPACK;
alter table STATS$RULE_SET move tablespace STATSPACK;
alter table STATS$OSSTATNAME move tablespace STATSPACK;
alter table STATS$OSSTAT move tablespace STATSPACK;
alter table STATS$PROCESS_ROLLUP move tablespace STATSPACK;
alter table STATS$PROCESS_MEMORY_ROLLUP move tablespace STATSPACK;
alter table STATS$SGA_TARGET_ADVICE move tablespace STATSPACK;
alter table STATS$STREAMS_POOL_ADVICE move tablespace STATSPACK;
alter table STATS$MUTEX_SLEEP move tablespace STATSPACK;
alter table STATS$DYNAMIC_REMASTER_STATS move tablespace STATSPACK;
alter table STATS$IOSTAT_FUNCTION_NAME move tablespace STATSPACK;
alter table STATS$IOSTAT_FUNCTION move tablespace STATSPACK;
alter table STATS$IOSTAT_FUNCTION_DETAIL move tablespace STATSPACK;
alter table STATS$MEMORY_TARGET_ADVICE move tablespace STATSPACK;
alter table STATS$MEMORY_DYNAMIC_COMPS move tablespace STATSPACK;
alter table STATS$MEMORY_RESIZE_OPS move tablespace STATSPACK;
alter table STATS$INTERCONNECT_PINGS move tablespace STATSPACK;
alter table STATS$IDLE_EVENT move tablespace STATSPACK;
alter table STATS$TEMP_SQLSTATS move tablespace STATSPACK;

73 rows selected.
```

上記のalter table文を実行。
このテーブルは一時テーブルのため、エラーは無視。

```
alter table STATS$TEMP_SQLSTATS move tablespace STATSPACK
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table
```

確認すると`STATSPACL表領域`に変更されている。

```sql
SQL> select table_name, tablespace_name from dba_tables where owner = 'PERFSTAT';

TABLE_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
STATS$SYSTEM_EVENT	       STATSPACK
STATS$SESSION_EVENT	       STATSPACK
STATS$WAITSTAT		       STATSPACK
STATS$ENQUEUE_STATISTICS       STATSPACK
STATS$SQL_SUMMARY	       STATSPACK
STATS$SQLTEXT		       STATSPACK
STATS$SQL_STATISTICS	       STATSPACK
STATS$RESOURCE_LIMIT	       STATSPACK
STATS$DLM_MISC		       STATSPACK
STATS$CR_BLOCK_SERVER	       STATSPACK
STATS$CURRENT_BLOCK_SERVER     STATSPACK
STATS$INSTANCE_CACHE_TRANSFER  STATSPACK
STATS$UNDOSTAT		       STATSPACK
STATS$SQL_PLAN_USAGE	       STATSPACK
STATS$SQL_PLAN		       STATSPACK
STATS$SEG_STAT		       STATSPACK
STATS$SEG_STAT_OBJ	       STATSPACK
STATS$PGASTAT		       STATSPACK
STATS$PARAMETER 	       STATSPACK
STATS$INSTANCE_RECOVERY        STATSPACK
STATS$STATSPACK_PARAMETER      STATSPACK
STATS$SHARED_POOL_ADVICE       STATSPACK
STATS$SQL_WORKAREA_HISTOGRAM   STATSPACK
STATS$PGA_TARGET_ADVICE        STATSPACK
STATS$JAVA_POOL_ADVICE	       STATSPACK
STATS$THREAD		       STATSPACK
STATS$FILE_HISTOGRAM	       STATSPACK
STATS$EVENT_HISTOGRAM	       STATSPACK
STATS$TIME_MODEL_STATNAME      STATSPACK
STATS$SYS_TIME_MODEL	       STATSPACK
STATS$SESS_TIME_MODEL	       STATSPACK
STATS$STREAMS_CAPTURE	       STATSPACK
STATS$STREAMS_APPLY_SUM        STATSPACK
STATS$PROPAGATION_SENDER       STATSPACK
STATS$PROPAGATION_RECEIVER     STATSPACK
STATS$BUFFERED_QUEUES	       STATSPACK
STATS$BUFFERED_SUBSCRIBERS     STATSPACK
STATS$RULE_SET		       STATSPACK
STATS$OSSTATNAME	       STATSPACK
STATS$OSSTAT		       STATSPACK
STATS$PROCESS_ROLLUP	       STATSPACK
STATS$PROCESS_MEMORY_ROLLUP    STATSPACK
STATS$SGA_TARGET_ADVICE        STATSPACK
STATS$STREAMS_POOL_ADVICE      STATSPACK
STATS$MUTEX_SLEEP	       STATSPACK
STATS$DYNAMIC_REMASTER_STATS   STATSPACK
STATS$IOSTAT_FUNCTION_NAME     STATSPACK
STATS$IOSTAT_FUNCTION	       STATSPACK
STATS$IOSTAT_FUNCTION_DETAIL   STATSPACK
STATS$MEMORY_TARGET_ADVICE     STATSPACK
STATS$MEMORY_DYNAMIC_COMPS     STATSPACK
STATS$MEMORY_RESIZE_OPS        STATSPACK
STATS$INTERCONNECT_PINGS       STATSPACK
STATS$IDLE_EVENT	       STATSPACK
STATS$DATABASE_INSTANCE        STATSPACK
STATS$LEVEL_DESCRIPTION        STATSPACK
STATS$SNAPSHOT		       STATSPACK
STATS$DB_CACHE_ADVICE	       STATSPACK
STATS$FILESTATXS	       STATSPACK
STATS$TEMPSTATXS	       STATSPACK
STATS$LATCH		       STATSPACK
STATS$LATCH_CHILDREN	       STATSPACK
STATS$LATCH_PARENT	       STATSPACK
STATS$LATCH_MISSES_SUMMARY     STATSPACK
STATS$LIBRARYCACHE	       STATSPACK
STATS$BUFFER_POOL_STATISTICS   STATSPACK
STATS$ROLLSTAT		       STATSPACK
STATS$ROWCACHE_SUMMARY	       STATSPACK
STATS$SGA		       STATSPACK
STATS$SGASTAT		       STATSPACK
STATS$SYSSTAT		       STATSPACK
STATS$SESSTAT		       STATSPACK
STATS$TEMP_SQLSTATS

73 rows selected.
```

索引も表領域が`SYSAUX`となっている

```sql
SQL> col INDEX_NAME for a30
SQL> select INDEX_NAME,TABLE_NAME,TABLESPACE_NAME from dba_indexes where owner = 'PERFSTAT';

INDEX_NAME		       TABLE_NAME		      TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
STATS$IDLE_EVENT_PK	       STATS$IDLE_EVENT 	      SYSAUX
STATS$INTERCONNECT_PINGS_PK    STATS$INTERCONNECT_PINGS       SYSAUX
STATS$MEMORY_RESIZE_OPS_PK     STATS$MEMORY_RESIZE_OPS	      SYSAUX
STATS$MEMORY_DYNAMIC_COMPS_PK  STATS$MEMORY_DYNAMIC_COMPS     SYSAUX
STATS$MEMORY_TARGET_ADVICE_PK  STATS$MEMORY_TARGET_ADVICE     SYSAUX
STATS$IOSTAT_FUNC_PK	       STATS$IOSTAT_FUNCTION_DETAIL   SYSAUX
STATS$IOSTAT_FUNCTION_PK       STATS$IOSTAT_FUNCTION	      SYSAUX
STATS$IOSTAT_FUNCTION_NAME_PK  STATS$IOSTAT_FUNCTION_NAME     SYSAUX
STATS$DYNAMIC_REM_STATS_PK     STATS$DYNAMIC_REMASTER_STATS   SYSAUX
STATS$MUTEX_SLEEP_PK	       STATS$MUTEX_SLEEP	      SYSAUX
STATS$STREAMS_POOL_ADVICE_PK   STATS$STREAMS_POOL_ADVICE      SYSAUX
STATS$SGA_TARGET_ADVICE_PK     STATS$SGA_TARGET_ADVICE	      SYSAUX
STATS$PROCESS_MEMORY_ROLLUP_PK STATS$PROCESS_MEMORY_ROLLUP    SYSAUX
STATS$$PROCESS_ROLLUP_PK       STATS$PROCESS_ROLLUP	      SYSAUX
STATS$OSSTAT_PK 	       STATS$OSSTAT		      SYSAUX
STATS$OSSSTATNAME_PK	       STATS$OSSTATNAME 	      SYSAUX
STATS$RULE_SET_PK	       STATS$RULE_SET		      SYSAUX
STATS$BUFFERED_SUBSCRIBERS_PK  STATS$BUFFERED_SUBSCRIBERS     SYSAUX
STATS$BUFFERED_QUEUES_PK       STATS$BUFFERED_QUEUES	      SYSAUX
STATS$PROPAGATION_RECEIVER_PK  STATS$PROPAGATION_RECEIVER     SYSAUX
STATS$PROPAGATION_SENDER_PK    STATS$PROPAGATION_SENDER       SYSAUX
STATS$STREAMS_APPLY_SUM_PK     STATS$STREAMS_APPLY_SUM	      SYSAUX
STATS$STREAMS_CAPTURE_PK       STATS$STREAMS_CAPTURE	      SYSAUX
STATS$SESS_TIME_MODEL_PK       STATS$SESS_TIME_MODEL	      SYSAUX
STATS$SYS_TIME_MODEL_PK        STATS$SYS_TIME_MODEL	      SYSAUX
STATS$TIME_MODEL_STATNAME_PK   STATS$TIME_MODEL_STATNAME      SYSAUX
STATS$EVENT_HISTOGRAM_PK       STATS$EVENT_HISTOGRAM	      SYSAUX
STATS$FILE_HISTOGRAM_PK        STATS$FILE_HISTOGRAM	      SYSAUX
STATS$THREAD_PK 	       STATS$THREAD		      SYSAUX
STATS$JAVA_POOL_ADVICE_PK      STATS$JAVA_POOL_ADVICE	      SYSAUX
STATS$PGA_TARGET_ADVICE_PK     STATS$PGA_TARGET_ADVICE	      SYSAUX
STATS$SQL_WORKAREA_HIST_PK     STATS$SQL_WORKAREA_HISTOGRAM   SYSAUX
STATS$SHARED_POOL_ADVICE_PK    STATS$SHARED_POOL_ADVICE       SYSAUX
STATS$STATSPACK_PARAMETER_PK   STATS$STATSPACK_PARAMETER      SYSAUX
STATS$INSTANCE_RECOVERY_PK     STATS$INSTANCE_RECOVERY	      SYSAUX
STATS$PARAMETER_PK	       STATS$PARAMETER		      SYSAUX
STATS$SQL_PGASTAT_PK	       STATS$PGASTAT		      SYSAUX
STATS$SEG_STAT_OBJ_PK	       STATS$SEG_STAT_OBJ	      SYSAUX
STATS$SEG_STAT_PK	       STATS$SEG_STAT		      SYSAUX
STATS$SQL_PLAN_PK	       STATS$SQL_PLAN		      SYSAUX
STATS$SQL_PLAN_USAGE_HV        STATS$SQL_PLAN_USAGE	      SYSAUX
STATS$SQL_PLAN_USAGE_PK        STATS$SQL_PLAN_USAGE	      SYSAUX
STATS$UNDOSTAT_PK	       STATS$UNDOSTAT		      SYSAUX
STATS$INST_CACHE_TRANSFER_PK   STATS$INSTANCE_CACHE_TRANSFER  SYSAUX
STATS$CURRENT_BLOCK_SERVER_PK  STATS$CURRENT_BLOCK_SERVER     SYSAUX
STATS$CR_BLOCK_SERVER_PK       STATS$CR_BLOCK_SERVER	      SYSAUX
STATS$DLM_MISC_PK	       STATS$DLM_MISC		      SYSAUX
STATS$RESOURCE_LIMIT_PK        STATS$RESOURCE_LIMIT	      SYSAUX
STATS$SQL_STATISTICS_PK        STATS$SQL_STATISTICS	      SYSAUX
STATS$SQLTEXT_PK	       STATS$SQLTEXT		      SYSAUX
STATS$SQL_SUMMARY_PK	       STATS$SQL_SUMMARY	      SYSAUX
STATS$ENQUEUE_STATISTICS_PK    STATS$ENQUEUE_STATISTICS       SYSAUX
STATS$WAITSTAT_PK	       STATS$WAITSTAT		      SYSAUX
STATS$SESSION_EVENT_PK	       STATS$SESSION_EVENT	      SYSAUX
STATS$SYSTEM_EVENT_PK	       STATS$SYSTEM_EVENT	      SYSAUX
STATS$SESSTAT_PK	       STATS$SESSTAT		      SYSAUX
STATS$SYSSTAT_PK	       STATS$SYSSTAT		      SYSAUX
STATS$SGASTAT_U 	       STATS$SGASTAT		      SYSAUX
STATS$SGA_PK		       STATS$SGA		      SYSAUX
STATS$ROWCACHE_SUMMARY_PK      STATS$ROWCACHE_SUMMARY	      SYSAUX
STATS$ROLLSTAT_PK	       STATS$ROLLSTAT		      SYSAUX
STATS$BUFFER_POOL_STATS_PK     STATS$BUFFER_POOL_STATISTICS   SYSAUX
STATS$LIBRARYCACHE_PK	       STATS$LIBRARYCACHE	      SYSAUX
STATS$LATCH_MISSES_SUMMARY_PK  STATS$LATCH_MISSES_SUMMARY     SYSAUX
STATS$LATCH_PARENT_PK	       STATS$LATCH_PARENT	      SYSAUX
STATS$LATCH_CHILDREN_PK        STATS$LATCH_CHILDREN	      SYSAUX
STATS$LATCH_PK		       STATS$LATCH		      SYSAUX
STATS$TEMPSTATXS_PK	       STATS$TEMPSTATXS 	      SYSAUX
STATS$FILESTATXS_PK	       STATS$FILESTATXS 	      SYSAUX
STATS$DB_CACHE_ADVICE_PK       STATS$DB_CACHE_ADVICE	      SYSAUX
STATS$SNAPSHOT_PK	       STATS$SNAPSHOT		      SYSAUX
STATS$LEVEL_DESCRIPTION_PK     STATS$LEVEL_DESCRIPTION	      SYSAUX
STATS$DATABASE_INSTANCE_PK     STATS$DATABASE_INSTANCE	      SYSAUX

73 rows selected.
```

索引も移動させる。
次のSQLで`alter index文`を作成

```sql
SQL> select 'alter index perfstat.' || index_name || ' rebuild tablespace STATSPACK;' from dba_indexes where OWNER = 'PERFSTAT';

'ALTERINDEXPERFSTAT.'||INDEX_NAME||'REBUILDTABLESPACESTATSPACK;'
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter index perfstat.STATS$IDLE_EVENT_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$INTERCONNECT_PINGS_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$MEMORY_RESIZE_OPS_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$MEMORY_DYNAMIC_COMPS_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$MEMORY_TARGET_ADVICE_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$IOSTAT_FUNC_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$IOSTAT_FUNCTION_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$IOSTAT_FUNCTION_NAME_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$DYNAMIC_REM_STATS_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$MUTEX_SLEEP_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$STREAMS_POOL_ADVICE_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SGA_TARGET_ADVICE_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$PROCESS_MEMORY_ROLLUP_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$$PROCESS_ROLLUP_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$OSSTAT_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$OSSSTATNAME_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$RULE_SET_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$BUFFERED_SUBSCRIBERS_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$BUFFERED_QUEUES_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$PROPAGATION_RECEIVER_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$PROPAGATION_SENDER_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$STREAMS_APPLY_SUM_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$STREAMS_CAPTURE_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SESS_TIME_MODEL_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SYS_TIME_MODEL_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$TIME_MODEL_STATNAME_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$EVENT_HISTOGRAM_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$FILE_HISTOGRAM_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$THREAD_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$JAVA_POOL_ADVICE_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$PGA_TARGET_ADVICE_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SQL_WORKAREA_HIST_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SHARED_POOL_ADVICE_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$STATSPACK_PARAMETER_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$INSTANCE_RECOVERY_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$PARAMETER_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SQL_PGASTAT_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SEG_STAT_OBJ_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SEG_STAT_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SQL_PLAN_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SQL_PLAN_USAGE_HV rebuild tablespace STATSPACK;
alter index perfstat.STATS$SQL_PLAN_USAGE_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$UNDOSTAT_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$INST_CACHE_TRANSFER_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$CURRENT_BLOCK_SERVER_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$CR_BLOCK_SERVER_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$DLM_MISC_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$RESOURCE_LIMIT_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SQL_STATISTICS_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SQLTEXT_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SQL_SUMMARY_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$ENQUEUE_STATISTICS_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$WAITSTAT_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SESSION_EVENT_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SYSTEM_EVENT_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SESSTAT_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SYSSTAT_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SGASTAT_U rebuild tablespace STATSPACK;
alter index perfstat.STATS$SGA_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$ROWCACHE_SUMMARY_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$ROLLSTAT_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$BUFFER_POOL_STATS_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$LIBRARYCACHE_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$LATCH_MISSES_SUMMARY_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$LATCH_PARENT_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$LATCH_CHILDREN_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$LATCH_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$TEMPSTATXS_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$FILESTATXS_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$DB_CACHE_ADVICE_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$SNAPSHOT_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$LEVEL_DESCRIPTION_PK rebuild tablespace STATSPACK;
alter index perfstat.STATS$DATABASE_INSTANCE_PK rebuild tablespace STATSPACK;

73 rows selected.
```

上記のSQLを実行。
索引を確認すると表領域が`STATSPACK`に変わった。

```sql
SQL> col INDEX_NAME for a30
SQL> select INDEX_NAME,TABLE_NAME,TABLESPACE_NAME from dba_indexes where owner = 'PERFSTAT';

INDEX_NAME		       TABLE_NAME		      TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
STATS$IDLE_EVENT_PK	       STATS$IDLE_EVENT 	      STATSPACK
STATS$INTERCONNECT_PINGS_PK    STATS$INTERCONNECT_PINGS       STATSPACK
STATS$MEMORY_RESIZE_OPS_PK     STATS$MEMORY_RESIZE_OPS	      STATSPACK
STATS$MEMORY_DYNAMIC_COMPS_PK  STATS$MEMORY_DYNAMIC_COMPS     STATSPACK
STATS$MEMORY_TARGET_ADVICE_PK  STATS$MEMORY_TARGET_ADVICE     STATSPACK
STATS$IOSTAT_FUNC_PK	       STATS$IOSTAT_FUNCTION_DETAIL   STATSPACK
STATS$IOSTAT_FUNCTION_PK       STATS$IOSTAT_FUNCTION	      STATSPACK
STATS$IOSTAT_FUNCTION_NAME_PK  STATS$IOSTAT_FUNCTION_NAME     STATSPACK
STATS$DYNAMIC_REM_STATS_PK     STATS$DYNAMIC_REMASTER_STATS   STATSPACK
STATS$MUTEX_SLEEP_PK	       STATS$MUTEX_SLEEP	      STATSPACK
STATS$STREAMS_POOL_ADVICE_PK   STATS$STREAMS_POOL_ADVICE      STATSPACK
STATS$SGA_TARGET_ADVICE_PK     STATS$SGA_TARGET_ADVICE	      STATSPACK
STATS$PROCESS_MEMORY_ROLLUP_PK STATS$PROCESS_MEMORY_ROLLUP    STATSPACK
STATS$$PROCESS_ROLLUP_PK       STATS$PROCESS_ROLLUP	      STATSPACK
STATS$OSSTAT_PK 	       STATS$OSSTAT		      STATSPACK
STATS$OSSSTATNAME_PK	       STATS$OSSTATNAME 	      STATSPACK
STATS$RULE_SET_PK	       STATS$RULE_SET		      STATSPACK
STATS$BUFFERED_SUBSCRIBERS_PK  STATS$BUFFERED_SUBSCRIBERS     STATSPACK
STATS$BUFFERED_QUEUES_PK       STATS$BUFFERED_QUEUES	      STATSPACK
STATS$PROPAGATION_RECEIVER_PK  STATS$PROPAGATION_RECEIVER     STATSPACK
STATS$PROPAGATION_SENDER_PK    STATS$PROPAGATION_SENDER       STATSPACK
STATS$STREAMS_APPLY_SUM_PK     STATS$STREAMS_APPLY_SUM	      STATSPACK
STATS$STREAMS_CAPTURE_PK       STATS$STREAMS_CAPTURE	      STATSPACK
STATS$SESS_TIME_MODEL_PK       STATS$SESS_TIME_MODEL	      STATSPACK
STATS$SYS_TIME_MODEL_PK        STATS$SYS_TIME_MODEL	      STATSPACK
STATS$TIME_MODEL_STATNAME_PK   STATS$TIME_MODEL_STATNAME      STATSPACK
STATS$EVENT_HISTOGRAM_PK       STATS$EVENT_HISTOGRAM	      STATSPACK
STATS$FILE_HISTOGRAM_PK        STATS$FILE_HISTOGRAM	      STATSPACK
STATS$THREAD_PK 	       STATS$THREAD		      STATSPACK
STATS$JAVA_POOL_ADVICE_PK      STATS$JAVA_POOL_ADVICE	      STATSPACK
STATS$PGA_TARGET_ADVICE_PK     STATS$PGA_TARGET_ADVICE	      STATSPACK
STATS$SQL_WORKAREA_HIST_PK     STATS$SQL_WORKAREA_HISTOGRAM   STATSPACK
STATS$SHARED_POOL_ADVICE_PK    STATS$SHARED_POOL_ADVICE       STATSPACK
STATS$STATSPACK_PARAMETER_PK   STATS$STATSPACK_PARAMETER      STATSPACK
STATS$INSTANCE_RECOVERY_PK     STATS$INSTANCE_RECOVERY	      STATSPACK
STATS$PARAMETER_PK	       STATS$PARAMETER		      STATSPACK
STATS$SQL_PGASTAT_PK	       STATS$PGASTAT		      STATSPACK
STATS$SEG_STAT_OBJ_PK	       STATS$SEG_STAT_OBJ	      STATSPACK
STATS$SEG_STAT_PK	       STATS$SEG_STAT		      STATSPACK
STATS$SQL_PLAN_PK	       STATS$SQL_PLAN		      STATSPACK
STATS$SQL_PLAN_USAGE_HV        STATS$SQL_PLAN_USAGE	      STATSPACK
STATS$SQL_PLAN_USAGE_PK        STATS$SQL_PLAN_USAGE	      STATSPACK
STATS$UNDOSTAT_PK	       STATS$UNDOSTAT		      STATSPACK
STATS$INST_CACHE_TRANSFER_PK   STATS$INSTANCE_CACHE_TRANSFER  STATSPACK
STATS$CURRENT_BLOCK_SERVER_PK  STATS$CURRENT_BLOCK_SERVER     STATSPACK
STATS$CR_BLOCK_SERVER_PK       STATS$CR_BLOCK_SERVER	      STATSPACK
STATS$DLM_MISC_PK	       STATS$DLM_MISC		      STATSPACK
STATS$RESOURCE_LIMIT_PK        STATS$RESOURCE_LIMIT	      STATSPACK
STATS$SQL_STATISTICS_PK        STATS$SQL_STATISTICS	      STATSPACK
STATS$SQLTEXT_PK	       STATS$SQLTEXT		      STATSPACK
STATS$SQL_SUMMARY_PK	       STATS$SQL_SUMMARY	      STATSPACK
STATS$ENQUEUE_STATISTICS_PK    STATS$ENQUEUE_STATISTICS       STATSPACK
STATS$WAITSTAT_PK	       STATS$WAITSTAT		      STATSPACK
STATS$SESSION_EVENT_PK	       STATS$SESSION_EVENT	      STATSPACK
STATS$SYSTEM_EVENT_PK	       STATS$SYSTEM_EVENT	      STATSPACK
STATS$SESSTAT_PK	       STATS$SESSTAT		      STATSPACK
STATS$SYSSTAT_PK	       STATS$SYSSTAT		      STATSPACK
STATS$SGASTAT_U 	       STATS$SGASTAT		      STATSPACK
STATS$SGA_PK		       STATS$SGA		      STATSPACK
STATS$ROWCACHE_SUMMARY_PK      STATS$ROWCACHE_SUMMARY	      STATSPACK
STATS$ROLLSTAT_PK	       STATS$ROLLSTAT		      STATSPACK
STATS$BUFFER_POOL_STATS_PK     STATS$BUFFER_POOL_STATISTICS   STATSPACK
STATS$LIBRARYCACHE_PK	       STATS$LIBRARYCACHE	      STATSPACK
STATS$LATCH_MISSES_SUMMARY_PK  STATS$LATCH_MISSES_SUMMARY     STATSPACK
STATS$LATCH_PARENT_PK	       STATS$LATCH_PARENT	      STATSPACK
STATS$LATCH_CHILDREN_PK        STATS$LATCH_CHILDREN	      STATSPACK
STATS$LATCH_PK		       STATS$LATCH		      STATSPACK
STATS$TEMPSTATXS_PK	       STATS$TEMPSTATXS 	      STATSPACK
STATS$FILESTATXS_PK	       STATS$FILESTATXS 	      STATSPACK
STATS$DB_CACHE_ADVICE_PK       STATS$DB_CACHE_ADVICE	      STATSPACK
STATS$SNAPSHOT_PK	       STATS$SNAPSHOT		      STATSPACK
STATS$LEVEL_DESCRIPTION_PK     STATS$LEVEL_DESCRIPTION	      STATSPACK
STATS$DATABASE_INSTANCE_PK     STATS$DATABASE_INSTANCE	      STATSPACK

73 rows selected.
```

# スナップショット
マスターユーザからPERFSTATユーザに、DBMS_SCHEDULERパッケージを使用する権限を付与。

`※DBMS_JOBパッケージは、DBMS_SCHEDULERパッケージによって置き換えられているため、DBMS_SCHEDULERパッケージを使用する。`
[Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース1 (12.1)](https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_job.htm)

```sql
SQL> GRANT SCHEDULER_ADMIN TO PERFSTAT;

Grant succeeded.

SQL> col grantee for a10
SQL> col granted_role for a20
SQL> select grantee,granted_role from dba_role_privs where GRANTEE = 'PERFSTAT';

GRANTEE    GRANTED_ROLE
---------- --------------------
PERFSTAT   SELECT_CATALOG_ROLE
PERFSTAT   SCHEDULER_ADMIN  ★付与された
```

PERFSTATユーザでログイン

```sql
SQL> conn perfstat/perfstat@orcl1
Connected.
SQL>
SQL> sho user
USER is "PERFSTAT"
```

自動的にスナップショットを取得するジョブを作成
（例）スナップショットレベルを7、10分間隔で取得

```sql
SQL> BEGIN
      DBMS_SCHEDULER.CREATE_JOB (
       job_name        => 'STATSPACK_SNAPSHOT',
       job_type        => 'PLSQL_BLOCK',
       job_action      => 'BEGIN STATSPACK.SNAP(I_SNAP_LEVEL=>7); END;',
       start_date      => NULL,
       repeat_interval => 'FREQ=MINUTELY;INTERVAL=10;',
       end_date        => NULL,
       auto_drop       => FALSE,
       enabled         => TRUE
      );
    END;
    /

PL/SQL procedure successfully completed.
```

- DBMS_SCHEDULER.CREATE_JOBプロシージャのパラメーター

| パラメーター       | 内容| 
|:-----------------|------------------|
| job_name            | ジョブ名|
| job_type           | 実行するジョブの種類を指定する。<br>STORED_PROCEDURE：プロシージャ<br>EXECUTABLE：外部プログラム（exeなど）<br>PLSQL_BLOCK：PL/SQLブロック|
|job_action            | ジョブの実行対象（job_typeによって変わる）<br>STORED_PROCEDURE：プロシージャ名<br>EXECUTABLE：プログラム名<br>PLSQL_BLOCK：PL/SQLブロック|
| start_date           | ジョブの開始日時（日付型で指定する）|
| repeat_interval            | ジョブの実行間隔<br>FREQ=<br>年：YEARLY<br>月：MONTHLY<br>週：WEEKLY<br>日：DAILY<br>時：HOURLY<br>分：MINUTELY<br>秒：SECONDLY<br><br>INTERVAL=<br>数値：指定した回数毎に実行<br>（例）'FREQ=HOURLY;INTERVAL=5　：５時間毎に実行する<br><br>BYMONTH：実行月を指定する（1～12）<br>（例）'FREQ=DAILY;BYMONTH=8'　8月に毎日実行する<br><br>BYWEEKNO：実行週を指定する（1～53）<br>（例）'BYWEEKNO=30'　年の30週目に実行する<br><br>BYYEARDAY：実行日を指定する（1～366）<br>（例）'BYYEARDAY=100'　年の100日目に実行する<br><br>BYDATE：実行日を指定する（MMDD形式）<br>（例）'BYDATE=100'　年の100日目に実行する<br><br>BYMONTHDAY：実行日を指定する（1～31）<br>（例）'FREQ=MONTHLY;BYMONTHDAY=25'　毎月25日に実行する<br><br>BYHOUR：実行時間を指定する（0～23）<br>（例）'FREQ=DAILY;BYHOUR=3'　毎日3時に実行する<br><br>BYMINUTE：実行分を指定する（0～59）<br>（例）'FREQ=HOURLY;BYMINUTE=30'　毎時30分に実行する<br><br>BYSECOND：実行秒を指定する（0～59）<br>（例）'FREQ=SECONDLY;BYSECOND=15'　毎分15秒に実行する|
| end_date           | ジョブの終了日時（日付型で指定する）|
| auto_drop            | ジョブ完了時に削除する/しないの設定<br>true：削除する（デフォルト）<br>false：削除しない|
| enabled           | ジョブが有効/無効の設定<br>true：有効<br>false：無効（デフォルト）|

- StatsPackのスナップショットレベル

| レベル       | 内容| 
|:-----------------|------------------|
| レベル0                       | 全体的なパフォーマンス統計|
| レベル5                       | 下位の内容 ＋ SQL ステートメント (※デフォルト)|
| レベル6                       | 下位の内容 ＋ SQL 実行計画および SQL 実行計画の使用率|
| レベル7                       | 下位の内容 ＋ セグメントレベルの統計|
| レベル10                      | 下位の内容 ＋ 親ラッチおよび子ラッチ|

スケジューラ・ジョブの確認

```sql
SQL> set pages 100 line 2000
SQL> col owner for a10
SQL> col job_name for a20
SQL> col job_action for a45
SQL> col start_date for a40
SQL> col repeat_interval for a30
SQL> col end_date for a30
SQL> SELECT OWNER,JOB_NAME,JOB_TYPE,JOB_ACTION,START_DATE,REPEAT_INTERVAL,END_DATE,AUTO_DROP,ENABLED FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME='STATSPACK_SNAPSHOT';

OWNER	   JOB_NAME		JOB_TYPE	 JOB_ACTION				       START_DATE				REPEAT_INTERVAL 	       END_DATE 		      AUTO_ ENABL
---------- -------------------- ---------------- --------------------------------------------- ---------------------------------------- ------------------------------ ------------------------------ ----- -----
PERFSTAT   STATSPACK_SNAPSHOT	PLSQL_BLOCK	 BEGIN STATSPACK.SNAP(I_SNAP_LEVEL=>7); END;   04-JUL-19 05.58.18.028588 AM ETC/UTC	FREQ=MINUTELY;INTERVAL=10;				      FALSE TRUE
```

スナップショットの取得の確認

```sql
SQL> select snap_id, to_char(snap_time,'YYYY/MM/DD HH24:MI:SS') from stats$snapshot order by 1;

   SNAP_ID TO_CHAR(SNAP_TIME,'
---------- -------------------
	 1 2019/07/04 15:41:13
```

今回は10分間隔で取得するので、10分待つと2つ目のスナップショットが取得される

```sql
SQL> select snap_id, to_char(snap_time,'YYYY/MM/DD HH24:MI:SS') from stats$snapshot order by 1;

   SNAP_ID TO_CHAR(SNAP_TIME,'
---------- -------------------
	 1 2019/07/04 15:41:13
	 2 2019/07/04 15:51:17
```


スケジューラ・ジョブのログからジョブの実行が成功したか確認

```sql
SQL> select job_name,log_id, to_char(log_date,'YYYY/MM/DD HH24:MI:SS'), job_name, status, to_char(actual_start_date,'YYYY/MM/DD HH24:MI:SS'), run_duration from dba_scheduler_job_run_details
2    where job_name = 'STATSPACK_SNAPSHOT' order by log_date;
  
JOB_NAME		 LOG_ID TO_CHAR(LOG_DATE,'Y JOB_NAME		 STATUS 			TO_CHAR(ACTUAL_STAR RUN_DURATION
-------------------- ---------- ------------------- -------------------- ------------------------------ ------------------- ---------------------------------------------------------------------------
STATSPACK_SNAPSHOT	   4480 2019/07/04 15:41:19 STATSPACK_SNAPSHOT	 SUCCEEDED			2019/07/04 06:41:13 +000 00:00:06
STATSPACK_SNAPSHOT	   4486 2019/07/04 15:51:24 STATSPACK_SNAPSHOT	 SUCCEEDED			2019/07/04 06:51:16 +000 00:00:08
```

スナップショットの自動削除も設定しておく。
（例）スナップショットを7日間保持(7日前は削除)、毎日01:00:00に実行

```sql
SQL> BEGIN
      DBMS_SCHEDULER.CREATE_JOB (
       job_name        => 'STATSPACK_SNAPSHOT_PURGE',
       job_type        => 'PLSQL_BLOCK',
       job_action      => 'BEGIN STATSPACK.PURGE(i_purge_before_date=>SYSDATE-7); END;',
       start_date      => NULL,
       repeat_interval => 'FREQ=DAILY; BYHOUR=1; BYMINUTE=0; BYSECOND=0;',
       auto_drop       => FALSE,
       enabled         => TRUE
     );
    END;
    /

PL/SQL procedure successfully completed.
```

※ジョブスケジュールの変更方法
（例）スナップショット取得間隔を１時間に変更

```sql
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name        => 'STATSPACK_SNAPSHOT',
    attribute   => 'repeat_interval',
    value       => 'FREQ=HOURLY;INTERVAL=1;'
  );
END;
/
```
```sql
SQL> SELECT OWNER,JOB_NAME,JOB_TYPE,JOB_ACTION,START_DATE,REPEAT_INTERVAL,END_DATE,AUTO_DROP,ENABLED FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME='STATSPACK_SNAPSHOT';
OWNER	   JOB_NAME		JOB_TYPE	 JOB_ACTION				       START_DATE				REPEAT_INTERVAL 	       END_DATE 		      AUTO_ ENABL
---------- -------------------- ---------------- --------------------------------------------- ---------------------------------------- ------------------------------ ------------------------------ ----- -----
PERFSTAT   STATSPACK_SNAPSHOT	PLSQL_BLOCK	 BEGIN STATSPACK.SNAP(I_SNAP_LEVEL=>7); END;   04-JUL-19 06.41.13.633620 AM ETC/UTC	FREQ=HOURLY;INTERVAL=1; 				      FALSE TRUE
```

StatsPackレポート作成

```
SQL> exec RDSADMIN.RDS_RUN_SPREPORT(1,2);

PL/SQL procedure successfully completed.
```

RDSのコンソール上から、StatsPackのレポートを確認。
`ダウンロード`からレポートをダウンロードできます。
<img width="1272" alt="スクリーンショット 2019-07-04 18.26.26.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/66cfe570-da8f-2b07-6c4e-9f3ec33044ff.png">