Oracle StatsPack

Oracle StatsPack Version 19c

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 in North America 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.

Primary StatsPack Creation Scripts

Description	Script
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	SQL> @?/rdbms/admin/sbcreate.sql (calls sbcusr.sql, sbctab.sql, and sbaddins.sql)
Create User and Required Privileges	SQL> @?/rdbms/admin/sbcusr.sql
Create Required Tables	SQL> @?/rdbms/admin/sbctab.sql
Add a Standby Database to the Configuration	SQL> @?/rdbms/admin/sbaddins.sql (calls sbcpkg.sql)
Create Standby Statistics Package	SQL> @?/rdbms/admin/sbcpkg.sql
List Standby Instances	SQL> @?/rdbms/admin/sblisins.sql
Report Creation	SQL> @?/rdbms/admin/sbreport.sql (calls sbrepcon.sql and sbrepins.sql)
Return Report Configuration	SQL> @?/rdbms/admin/sbrepcon.sq
Report Creation	SQL> @?/rdbms/admin/sbrepins.sql
Purge a Set of Snapshots	SQL> @?/rdbms/admin/sbpurge.sql
Remove Standby Instance	SQL> @?/rdbms/admin/sbdelins.sql
Drops the User and Tables	SQL> @?/rdbms/admin/sbdrop.sql (calls sbdtab.sql and sbdusr.sql)
Drops the Tables	SQL> @?/rdbms/admin/sbdtab.sql
Drops the User	SQL> @?/rdbms/admin/sbdrop.sql

Dependencies

CDB$ROOT

PDBDEV

c:\users\oracle>sqlplus sys@pdbdev as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 11 17:50:43 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options