Oracle Openworld Directory Index

Presentation Goal
A closer look at STATSPACK
Installation
Data Collection
Reporting
Levels & Thresholds
Report Analysis
Installation
Two Installation Modes
Interactive Mode
Timed_statistics should = TRUE
Connect / as SYSDBA
@?/rdbms/admin/spcreate
Executes SPCUSR, SPCTAB & SPCPKG
Create tablespace before running SPCREATE
You will be prompted for tablespace info
Will need 64MB for install
Monitor your tablespace – growth intensive




Installation
Two Installation Modes
Batch Mode
Timed_statistics should = TRUE
Connect / as SYSDBA
Define default_tablespace=‘tools’
Define temporary_tablespace=‘temp’
@?/rdbms/admin/spcreate




STATSPACK Utility
Collects statistical data about your instance

High resource SQL
Data is stored in permanent tables
Written in PL/SQL



PERFSTAT User
Who is PERFSTAT?

Owns all objects
Executes snapshot

Change the default password!!!



STATSPACK Snapshot
Unique collection of statistical data

Every day, hour, x minutes
Random collections due to performance
Degradation
New application or job
Automate collections - spauto




STATSPACK Snapshot
Executing a snapshot

Data collected at that point in time
Wait events, cache sizes, file stats

Data stored in PERFSTAT owned tables
Analyze immediately or later
Compare performance by day, hour, etc.




STATSPACK ID
Sequence generated snapshot ID

Compound ID comprised of:
Snapshot id = snap_id column
Instance number = instance_number
Database identifier = dbid

Supports OPS Environments



Snapshot Execution
Login as PERFSTAT user

SQLPLUS perfstat/perfstat
Execute statspack.snap




Thresholds & Levels
Determine how much data is collected
Settings stored in:
Stats$statpack_parameter
To change a threshold
Execute statspack.snap(I_snap_level=>6);



Thresholds & Levels
Level 5 Thresholds
Changing Default Parameters:
Reports
Login as PERFSTAT
@?/rdbms/admin/spreport
Prompted for starting snapshot ID
Prompted for ending snapshot ID
Spool output file name has a default