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
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.
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);
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);
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');
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);
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);
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;
/
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
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
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;