Oracle STATSPACK
Version 23c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose The Oracle documentation states: "SQL*PLUS command file to create statistics package." At one time that was probably an adequate description. Now, with AWR, the more accurate definition might be that this is a PL/SQL package of utilities support StatsPack and unfortunately also named StatsPack. For information about StatsPack functionality, which was the original AWR and which can still be deployed and used for much of what AWR does ... one of the largest distinguishing characteristics is that StatsPack does not require additional licensing such as Diagnostic and Tuning pack.

We strongly advise installing the PERFSTAT schema and objects in a PDB to avoid having to replicate the PERFSTAT tablespace in every PDB.
AUTHID DEFINER
Dependencies SELET name FROM dba_dependencies WHERE referenced_name = 'STATSPACK' UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'STATSPACK';

Returns 149 rows.
Documented No
Exceptions
Error Code Reason
ORA-20100 Purge Before Date (03-SEP-2021 13:16:40) is in the future
ORA-20200 Database Instance specified (DB Id/Instance 2140826538/1) does not exist in STATS$DATABASE_INSTANCE
ORA-20200 No snapshots exist for DB Id/Instance Number 2140826538/1
First Available Not known
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/spcpkg.sql
conn / as sysdba

ALTER SESSION SET CONTAINER=FREEPDB1;

SQL> set define on

SQL> @?/rdbms/admin/spcusr.sql
-- PERFSTAT / SYSAUX / TEMP /
Note: SPCUSR complete. Please check spcusr.lis for any errors

SQL> @?/rdbms/admin/spcreate.sql
SQL> @?/rdbms/admin/spctab.sql

SQL> @?/rdbms/admin/spcpkg.sql
Creating Package STATSPACK...

SP2-0808: Package created with compilation warnings

Errors for PACKAGE STATSPACK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1 PLW-05018: unit STATSPACK omitted optional AUTHID clause; default
value DEFINER used

23/9 PLW-07203: parameter 'BBC' may benefit from use of the NOCOPY compiler hint

23/3PLW-07203: parameter 'EBC' may benefit from use of the NOCOPY compiler hint

24/9 PLW-07203: parameter 'BSP' may benefit from use of the NOCOPY compiler hint

24/3PLW-07203: parameter 'ESP' may benefit from use of the NOCOPY compiler hint

25/9 PLW-07203: parameter 'BLB' may benefit from use of the NOCOPY compiler hint

26/9 PLW-07203: parameter 'BS' may benefit from use of the NOCOPY compiler hint

37/9 PLW-07203: parameter 'TIMSTAT' may benefit from use of the NOCOPY compiler hint

37/3PLW-07203: parameter 'STATLVL' may benefit from use of the NOCOPY compiler hint

171/9 PLW-07203: parameter 'O_UCOMMENT' may benefit from use of the NOCOPY compiler hint

186/9 PLW-07203: parameter 'O_ALL_INIT' may benefit from use of the NOCOPY compiler hint

187/9 PLW-07203: parameter 'O_OLD_SQL_CAPTURE_MTH' may benefit from use of the NOCOPY compiler hint

188/9 PLW-07203: parameter 'O_PIN_STATSPACK' may benefit from use of the NOCOPY compiler hint

273/1PLW-06010: keyword "PURGE" used as a defined name
283/14 PLW-06010: keyword "PURGE" used as a defined name
292/1PLW-06010: keyword "PURGE" used as a defined name

LINE/COL ERROR
-------- -----------------------------------------------------------------
  301/15 PLW-06010: keyword "PURGE" used as a defined name
  309/1  PLW-06010: keyword "PURGE" used as a defined name
  317/14 PLW-06010: keyword "PURGE" used as a defined name
  324/1  PLW-06010: keyword "PURGE" used as a defined name
  332/14 PLW-06010: keyword "PURGE" used as a defined name
Creating Package Body STATSPACK...

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY STATSPACK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
260/9 PLW-07203: parameter 'O_UCOMMENT' may benefit from use of the NOCOPY compiler hint

275/9 PLW-07203: parameter 'O_ALL_INIT' may benefit from use of the NOCOPY compiler hint

276/9 PLW-07203: parameter 'O_OLD_SQL_CAPTURE_MTH' may benefit from use of the NOCOPY compiler hint

277/9 PLW-07203: parameter 'O_PIN_STATSPACK' may benefit from use of the NOCOPY compiler hint

303/10 PL/SQL: SQL Statement ignored
307/17 PL/SQL: ORA-00942: table or view does not exist
338/12 PL/SQL: SQL Statement ignored
378/19 PL/SQL: ORA-00942: table or view does not exist
384/14 PL/SQL: SQL Statement ignored
384/26 PL/SQL: ORA-00942: table or view does not exist
484/12 PL/SQL: SQL Statement ignored
484/19 PL/SQL: ORA-00942: table or view does not exist
552/16 PL/SQL: SQL Statement ignored
552/28 PL/SQL: ORA-00942: table or view does not exist
706/31 PLS-00201: identifier 'STATS$SNAPSHOT.SNAP_ID' must be declared

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
Subprograms
 
CLEAR_BASELINE
Undocumented

Overload 1
statspack.clear_baseline(
i_begin_snap      IN NUMBER,
i_end_snap        IN NUMBER,
i_snap_range      IN BOOLEAN DEFAULT TRUE,
i_dbid            IN NUMBER  DEFAULT NULL,
i_instance_number IN NUMBER  DEFAULT NULL)
RETURN INTEGER;
TBD
Overload 2 statspack.clear_baseline(
i_begin_snap      IN NUMBER,
i_end_snap        IN NUMBER,
i_snap_range      IN BOOLEAN DEFAULT TRUE,
i_dbid            IN NUMBER  DEFAULT NULL,
i_instance_number IN NUMBER  DEFAULT NULL);
TBD
Overload 3 statspack.clear_baseline(
i_begin_date      IN DATE,
i_end_date        IN DATE,
i_dbid            IN NUMBER DEFAULT NULL,
i_instance_number IN NUMBER DEFAULT NULL)
RETURN INTEGER;
DECLARE
 retVal INTEGER;
BEGIN
  retVal := perfstat.statspack.clear_baseline(SYSDATE-60, SYSDATE-31);
END;
/

PL/SQL procedure successfully completed.
Overload 4 statspack.clear_baseline(
i_begin_date      IN DATE,
i_end_date        IN DATE,
i_dbid            IN NUMBER DEFAULT NULL,
i_instance_number IN NUMBER DEFAULT NULL);
TBD
 
GET_SNAP_ID
Undocumented statspack.get_snap_id(
i_snap_time       IN DATE,
i_dbid            IN NUMBER DEFAULT NULL,
i_instance_number IN NUMBER DEFAULT NULL)
RETURN INTEGER;
DECLARE
 retVal INTEGER;
BEGIN
  retVal := perfstat.statspack.get_snap_id(SYSDATE);
END;
/

PL/SQL procedure successfully completed.
 
MAKE_BASELINE
Undocumented

Overload 1
statspack.make_baseline(
i_begin_snap      IN NUMBER,
i_end_snap        IN NUMBER,
i_snap_range      IN BOOLEAN DEFAULT TRUE,
i_dbid            IN NUMBER  DEFAULT NULL,
i_instance_number IN NUMBER  DEFAULT NULL)
RETURN INTEGER;
TBD
Overload 2 statspack.make_baseline(
i_begin_snap      IN NUMBER,
i_end_snap        IN NUMBER,
i_snap_range      IN BOOLEAN DEFAULT TRUE,
i_dbid            IN NUMBER  DEFAULT NULL,
i_instance_number IN NUMBER);
TBD
Overload 3 statspack.make_baseline(
i_begin_date      IN DATE,
i_end_date        IN DATE,
i_dbid            IN NUMBER DEFAULT NULL,
i_instance_number IN NUMBER DEFAULT NULL)
RETURN INTEGER;
DECLARE
 retVal INTEGER;
BEGIN
  retVal := perfstat.statspack.make_baseline(SYSDATE-30, SYSDATE-23);
END;
/

PL/SQL procedure successfully completed.
Overload 4 statspack.make_baseline(
i_begin_date      IN DATE,
i_end_date        IN DATE,
i_dbid            IN NUMBER  DEFAULT NULL,
i_instance_number IN NUMBER DEFAULT NULL);
TBD
 
MODIFY_STATSPACK_PARAMETER
Undocumented statspack.dbms_modify_statspack_parameter(
i_dbid                IN NUMBER   DEFAULT NULL,
i_instance_number     IN NUMBER   DEFAULT NULL,
i_snap_level          IN NUMBER   DEFAULT NULL,
i_session_id          IN NUMBER   DEFAULT NULL,
i_ucomment            IN VARCHAR2 DEFAULT NULL,
i_num_sql             IN NUMBER   DEFAULT NULL,
i_executions_th       IN NUMBER   DEFAULT NULL,
i_parse_calls_th      IN NUMBER   DEFAULT NULL,
i_disk_reads_th       IN NUMBER   DEFAULT NULL,
i_buffer_gets_th      IN NUMBER   DEFAULT NULL,
i_sharable_mem_th     IN NUMBER   DEFAULT NULL,
i_version_count_th    IN NUMBER   DEFAULT NULL,
i_seg_phy_reads_th    IN NUMBER   DEFAULT NULL,
i_seg_log_reads_th    IN NUMBER   DEFAULT NULL,
i_seg_buff_busy_th    IN NUMBER   DEFAULT NULL,
i_seg_rowlock_w_th    IN NUMBER   DEFAULT NULL,
i_seg_itl_waits_th    IN NUMBER   DEFAULT NULL,
i_seg_cr_bks_rc_th    IN NUMBER   DEFAULT NULL,
i_seg_cu_bks_rc_th    IN NUMBER   DEFAULT NULL,
i_all_init            IN VARCHAR2 DEFAULT NULL,
i_old_sql_capture_mth IN VARCHAR2 DEFAULT NULL,
i_pin_statspack       IN VARCHAR2 DEFAULT NULL,
i_modify_parameter    IN VARCHAR2 DEFAULT 'TRUE');
TBD
 
PURGE
Undocumented

Overload 1
statspack.purge(
i_begin_snap      IN NUMBER,
i_end_snap        IN NUMBER,
i_snap_range      IN BOOLEAN DEFAULT TRUE,
i_extended_purge  IN BOOLEAN DEFAULT FALSE,
i_dbid            IN NUMBER  DEFAULT NULL,
i_instance_number IN NUMBER  DEFAULT NULL)
RETRUN INTEGER;
TBD
Overload 2 statspack.purge(
i_begin_snap      IN NUMBER,
i_end_snap        IN NUMBER,
i_snap_range      IN BOOLEAN DEFAULT TRUE,
i_extended_purge  IN BOOLEAN DEFAULT FALSE,
i_dbid            IN NUMBER  DEFAULT NULL,
i_instance_number IN NUMBER);
TBD
Overload 3 statspack.purge(
i_begin_date      IN DATE,
i_end_date        IN DATE,
i_extended_purge  IN BOOLEAN DEFAULT FALSE,
i_dbid            IN NUMBER  DEFAULT NULL,
i_instance_number IN NUMBER  DEFAULT NULL)
RETURN INTEGER;
TBD
Overload 4 statspack.purge(
i_begin_date      IN DATE,
i_end_date        IN DATE,
i_extended_purge  IN BOOLEAN DEFAULT FALSE,
i_dbid            IN NUMBER  DEFAULT NULL,
i_instance_number IN NUMBER  DEFAULT NULL);
TBD
Overload 5 statspack.purge(
i_purge_before_date IN DATE,
i_extended_purge    IN BOOLEAN DEFAULT FALSE,
i_dbid              IN NUMBER  DEFAULT NULL,
i_instance_number   IN NUMBER  DEFAULT NULL)
RETURN INTEGER;
DECLARE
 retVal INTEGER;
BEGIN
  retVal := perfstat.statspack.purge(SYSDATE);
  dbms_output.put_line(retVal);
END;
/

PL/SQL procedure successfully completed.
Overload 6 statspack.purge(
i_purge_before_date IN DATE,
i_extended_purge    IN BOOLEAN DEFAULT FALSE,
i_dbid              IN NUMBER  DEFAULT NULL,
i_instance_number   IN NUMBER  DEFAULT NULL,
TBD
Overload 7 statspack.purge(
i_num_days        IN NUMBER,
i_extended_purge  IN BOOLEAN DEFAULT FALSE,
i_dbid            IN NUMBER  DEFAULT NULL,
i_instance_number IN NUMBER  DEFAULT NULL)
RETURN INTEGER;
TBD
Overload 8 statspack.purge(
i_num_days        IN NUMBER,
i_extended_purge  IN BOOLEAN DEFAULT FALSE,
i_dbid            IN NUMBER  DEFAULT NULL,
i_instance_number IN NUMBER  DEFAULT NULL);
TBD
 
QAM_STATSPACK_PARAMETER
Undocumented statspack.qam_statspack_parameter(
i_dbid                IN  NUMBER   DEFAULT NULL,
i_instance_number     IN  NUMBER   DEFAULT NULL,
i_snap_level          IN  NUMBER   DEFAULT NULL,
i_session_id          IN  NUMBER   DEFAULT NULL,
i_ucomment            IN  VARCHAR2 DEFAULT NULL,
i_num_sql             IN  NUMBER   DEFAULT NULL,
i_executions_th       IN  NUMBER   DEFAULT NULL,
i_parse_calls_th      IN  NUMBER   DEFAULT NULL,
i_disk_reads_th       IN  NUMBER   DEFAULT NULL,
i_buffer_gets_th      IN  NUMBER   DEFAULT NULL,
i_sharable_mem_th     IN  NUMBER   DEFAULT NULL,
i_version_count_th    IN  NUMBER   DEFAULT NULL,
i_seg_phy_reads_th    IN  NUMBER   DEFAULT NULL,
i_seg_log_reads_th    IN  NUMBER   DEFAULT NULL,
i_seg_buff_busy_th    IN  NUMBER   DEFAULT NULL,
i_seg_rowlock_w_th    IN  NUMBER   DEFAULT NULL,
i_seg_itl_waits_th    IN  NUMBER   DEFAULT NULL,
i_seg_cr_bks_rc_th    IN  NUMBER   DEFAULT NULL,
i_seg_cu_bks_rc_th    IN  NUMBER   DEFAULT NULL,
i_all_init            IN  VARCHAR2 DEFAULT NULL,
i_old_sql_capture_mth IN  VARCHAR2 DEFAULT NULL,
i_pin_statspack       IN  VARCHAR2 DEFAULT NULL,
i_modify_parameter    IN  VARCHAR2 DEFAULT 'FALSE',
o_snap_level          OUT NUMBER,
o_session_id          OUT NUMBER,
o_ucomment            OUT VARCHAR2,
o_num_sql             OUT NUMBER,
o_executions_th       OUT NUMBER,
o_parse_calls_th      OUT NUMBER,
o_disk_reads_th       OUT NUMBER,
o_buffer_gets_th      OUT NUMBER,
o_sharable_mem_th     OUT NUMBER,
o_version_count_th    OUT NUMBER,
o_seg_phy_reads_th    OUT NUMBER,
o_seg_log_reads_th    OUT NUMBER,
o_seg_buff_busy_th    OUT NUMBER,
o_seg_rowlock_w_th    OUT NUMBER,
o_seg_itl_waits_th    OUT NUMBER,
o_seg_cr_bks_rc_th    OUT NUMBER,
o_seg_cu_bks_rc_th    OUT NUMBER,
o_all_init            OUT VARCHAR2,
o_old_sql_capture_mth OUT VARCHAR2,
o_pin_statspack       OUT VARCHAR2);
TBD
 
SNAP
Undocumented

Overload 1
statspack.snap(
i_snap_level          IN NUMBER   DEFAULT NULL,
i_session_id          IN NUMBER   DEFAULT NULL,
i_ucomment            IN VARCHAR2 DEFAULT NULL,
i_num_sql             IN NUMBER   DEFAULT NULL,
i_executions_th       IN NUMBER   DEFAULT NULL,
i_parse_calls_th      IN NUMBER   DEFAULT NULL,
i_disk_reads_th       IN NUMBER   DEFAULT NULL,
i_buffer_gets_th      IN NUMBER   DEFAULT NULL,
i_sharable_mem_th     IN NUMBER   DEFAULT NULL,
i_version_count_th    IN NUMBER   DEFAULT NULL,
i_seg_phy_reads_th    IN NUMBER   DEFAULT NULL,
i_seg_log_reads_th    IN NUMBER   DEFAULT NULL,
i_seg_buff_busy_th    IN NUMBER   DEFAULT NULL,
i_seg_rowlock_w_th    IN NUMBER   DEFAULT NULL,
i_seg_itl_waits_th    IN NUMBER   DEFAULT NULL,
i_seg_cr_bks_rc_th    IN NUMBER   DEFAULT NULL,
i_seg_cu_bks_rc_th    IN NUMBER   DEFAULT NULL,
i_all_init            IN VARCHAR2 DEFAULT NULL,
i_old_sql_capture_mth IN VARCHAR2 DEFAULT NULL,
i_pin_statspack       IN VARCHAR2 DEFAULT NULL,
i_modify_parameter    IN VARCHAR2 DEFAULT 'FALSE');
TBD
Overload 2 statspack.snap(
i_snap_level          IN NUMBER   DEFAULT NULL,
i_session_id          IN NUMBER   DEFAULT NULL,
i_ucomment            IN VARCHAR2 DEFAULT NULL,
i_num_sql             IN NUMBER   DEFAULT NULL,
i_executions_th       IN NUMBER   DEFAULT NULL,
i_parse_calls_th      IN NUMBER   DEFAULT NULL,
i_disk_reads_th       IN NUMBER   DEFAULT NULL,
i_buffer_gets_th      IN NUMBER   DEFAULT NULL,
i_sharable_mem_th     IN NUMBER   DEFAULT NULL,
i_version_count_th    IN NUMBER   DEFAULT NULL,
i_seg_phy_reads_th    IN NUMBER   DEFAULT NULL,
i_seg_log_reads_th    IN NUMBER   DEFAULT NULL,
i_seg_buff_busy_th    IN NUMBER   DEFAULT NULL,
i_seg_rowlock_w_th    IN NUMBER   DEFAULT NULL,
i_seg_itl_waits_th    IN NUMBER   DEFAULT NULL,
i_seg_cr_bks_rc_th    IN NUMBER   DEFAULT NULL,
i_seg_cu_bks_rc_th    IN NUMBER   DEFAULT NULL,
i_all_init            IN VARCHAR2 DEFAULT NULL,
i_old_sql_capture_mth IN VARCHAR2 DEFAULT NULL,
i_pin_statspack       IN VARCHAR2 DEFAULT NULL,
i_modify_parameter    IN VARCHAR2 DEFAULT 'FALSE')
RETURN INTEGER;
DECLARE
 retVal INTEGER;
BEGIN
  retVal := perfstat.statspack.snap;
  dbms_output.put_line(retVal);
END;
/

PL/SQL procedure successfully completed.
 
STAT_CHANGES
Undocumented statspack.stat_changes(
bid       IN  NUMBER,
eid       IN  NUMBER,
db_ident  IN  NUMBER,
inst_num  IN  NUMBER,
parallel  IN  VARCHAR2,
lhtr      OUT NUMBER,
bfwt      OUT NUMBER,
tran      OUT NUMBER,
chng      OUT NUMBER,
ucal      OUT NUMBER,
urol      OUT NUMBER,
rsiz      OUT NUMBER,
phyr      OUT NUMBER,
phyrd     OUT NUMBER,
phyrdl    OUT NUMBER,
phyrc     OUT NUMBER,
phyw      OUT NUMBER,
ucom      OUT NUMBER,
prse      OUT NUMBER,
hprse     OUT NUMBER,
recr      OUT NUMBER,
gets      OUT NUMBER,
slr       OUT NUMBER,
rlsr      OUT NUMBER,
rent      OUT NUMBER,
srtm      OUT NUMBER,
srtd      OUT NUMBER,
srtr      OUT NUMBER,
strn      OUT NUMBER,
lhr       OUT NUMBER,
bbc       OUT VARCHAR2,
ebc       OUT VARCHAR2,
bsp       OUT VARCHAR2,
esp       OUT VARCHAR2,
blb       OUT VARCHAR2,
bs        OUT VARCHAR2,
twt       OUT NUMBER,
logc      OUT NUMBER,
prscpu    OUT NUMBER,
tcpu      OUT NUMBER,
exe       OUT NUMBER,
prsela    OUT NUMBER,
bspm      OUT NUMBER,
espm      OUT NUMBER,
bfrm      OUT NUMBER,
efrm      OUT NUMBER,
blog      OUT NUMBER,
elog      OUT NUMBER,
bocur     OUT NUMBER,
eocur     OUT NUMBER,
bpgaalloc OUT NUMBER,
epgaalloc OUT NUMBER,
bsgaalloc OUT NUMBER,
esgaalloc OUT NUMBER,
bnprocs   OUT NUMBER,
enprocs   OUT NUMBER,
timstat   OUT VARCHAR2,
statlvl   OUT VARCHAR2,
bncpu     OUT NUMBER,     -- OS Stat
encpu     OUT NUMBER,
bpmem     OUT NUMBER,
epmem     OUT NUMBER,
blod      OUT NUMBER,
elod      OUT NUMBER,
itic      OUT NUMBER,
btic      OUT NUMBER,
iotic     OUT NUMBER,
rwtic     OUT NUMBER,
utic      OUT NUMBER,
stic      OUT NUMBER,
vmib      OUT NUMBER,
vmob      OUT NUMBER,
oscpuw    OUT NUMBER,
dbtim     OUT NUMBER,     -- time model
dbcpu     OUT NUMBER,
bgela     OUT NUMBER,
bgcpu     OUT NUMBER,
prstela   OUT NUMBER,
sqleela   OUT NUMBER,
conmela   OUT NUMBER,
dmsd      OUT NUMBER,     -- begin RAC
dmfc      OUT NUMBER,
dmsi      OUT NUMBER,
pmrv      OUT NUMBER,
pmpt      OUT NUMBER,
npmrv     OUT NUMBER,
npmpt     OUT NUMBER,
dbfr      OUT NUMBER,
dpms      OUT NUMBER,
dnpms     OUT NUMBER,
glsg      OUT NUMBER,
glag      OUT NUMBER,
glgt      OUT NUMBER,
gccrrv    OUT NUMBER,
gccrrt    OUT NUMBER,
gccrfl    OUT NUMBER,
gccurv    OUT NUMBER,
gccurt    OUT NUMBER,
gccufl    OUT NUMBER,
gccrsv    OUT NUMBER,
gccrbt    OUT NUMBER,
gccrft    OUT NUMBER,
gccrst    OUT NUMBER,
gccusv    OUT NUMBER,
gccupt    OUT NUMBER,
gccuft    OUT NUMBER,
gccust    OUT NUMBER,
msgsq     OUT NUMBER,
msgsqt    OUT NUMBER,
msgsqk    OUT NUMBER,
msgsqtk   OUT NUMBER,
msgrq     OUT NUMBER,
msgrqt    OUT NUMBER);    -- end RAC
TBD
 
V1024
Undocumented statspack.v1024(
i_num       IN NUMBER,
i_unit      IN VARCHAR2,
i_min_unit  IN VARCHAR2,
i_precision IN INTEGER,
i_mag       IN INTEGER)
RETURN VARCHAR2;
TBD
 
VERIFY_DBID_INSTANCE_NUMBER
Undocumented statspack.verify_dbid_instance_number(
i_dbid            IN NUMBER DEFAULT NULL,
i_instance_number IN NUMBER DEFAULT NULL);
exec perfstat.statspack.verify_dbid_instance_number(2140826538, 1);
BEGIN perfstat.statspack.verify_dbid_instance_number(2140826538, 1); END;
      *
ERROR at line 1:
ORA-20200: Database Instance specified (DB Id/Instance 2140826538/1) does not exist in STATS$DATABASE_INSTANCE
ORA-06512: at "PERFSTAT.STATSPACK", line 773
ORA-06512: at line 1


SELECT * FROM stats$database_instance;

no rows selected
 
VERIFY_SNAP_ID
Undocumented statspack.verify_snap_id(
i_snap_id         IN NUMBER,
i_dbid            IN NUMBER DEFAULT NULL,
i_instance_number IN NUMBER DEFAULT NULL);
exec perfstat.statspack.verify_snap_id(2);

PL/SQL procedure successfully completed.
 
PERFSTAT Queries
Idle Events SELECT * FROM perfstat.stats$idle_event;

EVENT
------------------------------------------
AQ Cross Master idle
AQ Proxy Cleanup Wait
AQ: 12c message cache init wait
AQPC idle
ASM background timer
ASM cluster membership changes
Archiver: redo logs
Backup Appliance Comm SGA setup wait
Backup Appliance Servlet wait
Backup Appliance Surrogate wait
Backup Appliance waiting for work
Backup Appliance waiting restore start
Blockchain apply clean
Blockchain reader process idle
...
wakeup event for builder
wakeup event for preparer
wakeup event for reader
wakeup time manager
watchdog main loop

206 rows selected.
Snap Level Descriptions SELECT * FROM perfstat.stats$level_description;

SNAP_LEVEL DESCRIPTION
---------- ------------------------------------------------------------------------
         0 This level captures general statistics, including rollback segment, row
           cache, SGA, system events, background events, session events, system
           statistics, wait statistics, lock statistics, and Latch information

         5 This level includes capturing high resource usage SQL Statements, along
           with all data captured by lower levels

         6 This level includes capturing SQL plan and SQL plan usage information for
           high resource usage SQL Statements, along with all data captured by lower
           levels

         7 This level captures segment level statistics, including logical and
           physical reads, row lock, itl and buffer busy waits, along with all data
           captured by lower levels

        10 This level includes capturing Child Latch statistics, along with all data
           captured by lower levels
Tables With Rows SELECT table_name, num_rows
FROM dba_tables
WHERE owner = 'PERFSTAT'
AND num_rows <> 0
ORDER BY 1;

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_WORKLOAD_REPOSITORY
What's New In 21c
What's New In 23c