set pages 500
set lines 500
col dbid for 9999999999
col instance_number for 99999
col startup_time for a19
col snap_id for 99999999
col parallel for a3
col version for a17
col db_name for a10
col instance_name for a20
col host_name for a35
col platform_name for a35
SELECT * FROM stats$database_instance;
set pages 500
set lines 500
col dbid for 9999999999
col instance_number for 99999
col session_id for 99999
col snap_level for 99
col num_sql for 99999
col executions_th for 99999
col parse_calls_th for 99999
col disk_reads_th for 999999
col buffer_gets_th for 9999999
col sharable_mem_th for 99999999
col version_count_th for 9999
col pin_statspack for a6
col all_init for a5
col last_modified for a19
col ucomment for a30
col job for 99999
col seg_phy_reads_th for 999999
col seg_log_reads_th for 9999999
col seg_buff_busy_th for 9999
col seg_rowlock_w_th for 9999
col seg_itl_waits_th for 9999
col seg_cr_bks_rc_th for 99999
col seg_cu_bks_rc_th for 99999
col old_sql_capture_mth for a6
SELECT * FROM stats$statspack_parameter;
set pages 500
set lines 500
col snap_id for 99999999
col dbid for 9999999999
col instance_number for 99999
col snap_time for a19
col startup_time for a19
col session_id for 99999
col serial# for 99999
col snap_level for 99
col ucomment for a30
col executions_th for 99999
col parse_calls_th for 99999
col disk_reads_th for 999999
col buffer_gets_th for 9999999
col sharable_mem_th for 99999999
col version_count_th for 9999
col seg_phy_reads_th for 999999
col seg_log_reads_th for 9999999
col seg_buff_busy_th for 9999
col seg_rowlock_w_th for 9999
col seg_itl_waits_th for 9999
col seg_cr_bks_rc_th for 99999
col seg_cu_bks_rc_th for 99999
col seg_cr_bks_sd_th for 99999
col seg_cu_bks_sd_th for 99999
col snapshot_exec_time_s for 9999.99
col all_init for a5
col baseline for a4
SELECT * FROM stats$snapshot;
SQL> SELECT * FROM stats$database_instance;
no rows selected
SQL> SELECT * FROM stats$snapshot;
no rows selected
SQL> SELECT * FROM stats$statspack_parameter;
no rows selected
SQL> set pages 500
SQL> set lines 500
SQL> col dbid for 9999999999
SQL> col instance_number for 99999
SQL> col startup_time for a19
SQL> col snap_id for 99999999
SQL> col parallel for a3
SQL> col version for a17
SQL> col db_name for a10
SQL> col instance_name for a20
SQL> col host_name for SQL> a35
SQL> col platform_name for a35
SQL> SELECT * FROM stats$database_instance;
DBID INSTANCE_NUMBER STARTUP_TIME SNAP_ID PAR VERSION DB_NAME INSTANCE_NAME HOST_NAME PLATFORM_NAME
----------- --------------- ------------------- --------- --- ----------------- ---------- -------------------- ----------------------------------- -----------------------------------
1756285875 1 2026-05-03 06:27:08 1 NO 23.0.0.0.0 ORCL orcl learnbytebybyte Linux x86 64-bit
SQL> set pages 500
SQL> set lines 500
SQL> col dbid for 9999999999
SQL> col instance_number for 99999
SQL> col session_id for 99999
SQL> col snap_level for 99
SQL> col num_sql for 99999
SQL> col version_count_th for 9999
SQL> col pin_statspack for a6
SQL> col all_init for a5
SQL> col last_modified for a19
SQL> col ucomment for a30
SQL> col job for 99999
SQL> SELECT dbid,instance_number,session_id,snap_level,num_sql,pin_statspack,all_init,last_modified,ucomment,job FROM stats$statspack_parameter;
DBID INSTANCE_NUMBER SESSION_ID SNAP_LEVEL NUM_SQL PIN_ST ALL_I LAST_MODIFIED UCOMMENT JOB
----------- --------------- ---------- ---------- ------- ------ ----- ------------------- ------------------------------ ------
1756285875 1 0 5 50 TRUE FALSE 2026-05-04 07:28:26
stats$snapshotは、取得したすべてのスナップショットの履歴があります。
SQL> set pages 500
SQL> set lines 500
SQL> col snap_id for 99999999
SQL> col dbid for 9999999999
SQL> col instance_number for 99999
SQL> col snap_time for a19
SQL> col startup_time for a19
SQL> col session_id for 99999
SQL> col serial# for 99999
SQL> col snap_level for 99
SQL> col ucomment for a30
SQL> col snapshot_exec_time_s for 9999.99
SQL> col all_init for a5
SQL> col baseline for a4
SQL> SELECT snap_id,dbid,instance_number,snap_time,startup_time,session_id,serial#,snap_level,ucomment,snapshot_exec_time_s,all_init,baseline FROM stats$snapshot;
SNAP_ID DBID INSTANCE_NUMBER SNAP_TIME STARTUP_TIME SESSION_ID SERIAL# SNAP_LEVEL UCOMMENT SNAPSHOT_EXEC_TIME_S ALL_I BASE
--------- ----------- --------------- ------------------- ------------------- ---------- ------- ---------- ------------------------------ -------------------- ----- ----
1 1756285875 1 2026-05-04 07:28:26 2026-05-03 06:27:08 0 0 5 1.31 FALSE
SQL> @?/rdbms/admin/spreport
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1756285875 ORCL 1 orcl
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
1756285875 1 ORCL orcl OracleRestar
t26ai
Using 1756285875 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 04 May 2026 19:28 5
11 05 May 2026 00:23 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 11
End Snapshot Id specified: 11
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_11. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: sp_1_11.lis
Using the report name sp_1_11.lis
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1756285875 orcl 1 03-May-26 06:27 23.0.0.0.0 NO
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
learnbytebybyte Linux x86 64-bit 4 2 1 15.2
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 1 04-May-26 19:28:26 1 5.0
End Snap: 11 05-May-26 00:23:44 1 4.0
Elapsed: 295.30 (mins) Av Act Sess: 0.0
DB time: 2.00 (mins) DB CPU: 1.70 (mins)
<省略>
End of Report ( sp_1_11.lis )
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
----------
2
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
80
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
JOB NEXT_DATE NEXT_SEC
---------- --------- --------
2 05-MAY-26 04:00:00
SQL> EXEC statspack.purge(i_num_days=>0);
BEGIN statspack.purge(i_num_days=>0); END;
*
ERROR at line 1:
ORA-20100: Number of days specified (0) should be greater than 0
ORA-06512: at "PERFSTAT.STATSPACK", line 1387
ORA-06512: at "PERFSTAT.STATSPACK", line 1705
ORA-06512: at "PERFSTAT.STATSPACK", line 1739
ORA-06512: at line 1
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'
... Starting truncate operation
Table truncated.
<省略>
17 rows deleted.
2 rows deleted.
Commit complete.
Package altered.
