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
StatsPack is the tool that existed before the introduction of ASH and AWR. There two substantive differences between StatsPack and the newer two.
StatsPack is free and doesn't do everything the other two do. But ... it is free ... and it does much of what is required though it does not support newer features.
Still, it is something you should check out and be comfortable running when your organization has not licensed Diagnostic and Tuning packs.
For StatsPack installation in a Container Database environment check the
following 2 links which are, hopefully, still functional and relevant.
Create Script: calls spcusr.sql, spectab.sql, and spcpkg.sql
rdbms/admin/sbcreate.sql
Create User and Required Privileges
rdbms/admin/spcusr.sql
Create Required Tables
rdbms/admin/spctab.sql
Creates Statistics Package
rdbms/admin/sbcpkg.sql
Validate Installation
conn perfstat/perfstat@orabase
Automates Statistics Collection
rdbms/admin/spauto.sql
Server Performance AWR RAC Report
rdbms/admin/spawrrac.sql
Purges a Range of Snapshots
rdbms/admin/sppurge.sql
Report Configuration
rdbms/admin/sprepcon.sql
Default StatsPack Report
rdbms/admin/spreport.sql (calls sprepins.sql)
Snapshot Instance Difference Report
rdbms/admin/sprepins.sql
Standard StatsPack Report
rdbms/admin/sprepsql.sql (calls sprsqins.sql)
Resource Usage, SQL Text & Plans Report
rdbms/admin/sprsqins.sql
Truncates the StatsPack Tables
rdbms/admin/sptrunc.sql
Drops the user, tables, and package
rdbms/admin/spdrop.sql
Drops StatsPack Tables
rdbms/admin/spdtab.sql
Drops StatsPack User
rdbms/admin/spdusr.sql
Standby StatsPack Creation Scripts
Description
Script
Driver Create Script
rdbms/admin/sbcreate.sql
(calls sbcusr.sql, sbctab.sql, and sbaddins.sql)
Create User and Required Privileges
rdbms/admin/sbcusr.sql
Create Required Tables
rdbms/admin/sbctab.sql
Add a Standby Database to the Configuration
rdbms/admin/sbaddins.sql (calls sbcpkg.sql)
Create Standby Statistics Package
rdbms/admin/sbcpkg.sql
List Standby Instances
rdbms/admin/sblisins.sql
Report Creation
rdbms/admin/sbreport.sql
(calls sbrepcon.sql and sbrepins.sql)
Return Report Configuration
rdbms/admin/sbrepcon.sq
Report Creation
rdbms/admin/sbrepins.sql
Purge a Set of Snapshots
rdbms/admin/sbpurge.sql
Remove Standby Instance
rdbms/admin/sbdelins.sql
Drops the User and Tables
rdbms/admin/sbdrop.sql (calls sbdtab.sql and sbdusr.sql)
Drops the Tables
rdbms/admin/sbdtab.sql
Drops the User
rdbms/admin/sbdrop.sql
PDB Deployment
Connect to a PDB and create a StatsPack tablespace and schema
[oracle@test21 dbhome_1]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Wed Sep 1 17:53:02 2021
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
ALTER SESSION SET CONTAINER=TEST21P1;
Session altered.
sho con_id
CON_ID
------------------------------
3
-- create tablespace
CREATE TABLESPACE perfstat
DATAFILE '+DATA/TEST21DB_IAD25G/B6355315C77F2BECE0531000000A714D/DATAFILE/perfstat01.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 500M
LOGGING ONLINE PERMANENT BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO;
-- create user
SQL> set define on
SQL> @?/rdbms/admin/spcreate.sql
Session altered.
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: "<complex_pwd_here>"
<complex_pwd_echo'd here>
Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.
Choose the PERFSTAT users's default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.
Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: PERFSTAT
Using tablespace PERFSTAT as PERFSTAT default tablespace.
Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.
Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace: TEMP
Using tablespace TEMP as PERFSTAT temporary tablespace.
... Creating PERFSTAT user
... Installing required packages
... Creating views
... Granting privileges
NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.
SQL> -- Next two scripts run as perfstat user
SQL> ALTER SESSION SET CURRENT_SCHEMA = PERFSTAT;
Session altered.
SQL> -- Create statspack tables
SQL> @@spctab
...
NOTE:
SPCTAB complete. Please check spctab.lis for any errors.
SQL>
SQL> -- Create the statistics Package
SQL> @@spcpkg
...
SQL> set echo off;
Creating Package STATSPACK...
Package created.
No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
SQL>
SQL> -- Bug#25233027: xxx Set this parameter to FALSE for creating common objects in consolidated database
SQL> alter session set "_oracle_script" = FALSE;
Session altered.
SELECT object_type, COUNT(*)
FROM dba_objects
WHERE owner = 'PERFSTAT'
GROUP BY object_type
ORDER BY 1;
OBJECT_TYPE COUNT(*)
-------------- ---------
INDEX 73
PACKAGE 1
PACKAGE BODY 1
SEQUENCE 1
TABLE 73
VIEW 1
PDB Deployment
The table at right is from 2015 but likely still
an accurate mapping
