How to know if STATSPACK is installed

 

Statspack is important tool for dba to investigate performance issue in oracle database, it makes DBA's life easy in performance issues cases. Most of the time, statspack is not installed automatically unless it is specified in custom script prepared from database creation.

The question is how to check if statspack is installed or not? There are number of ways to detect that, we will discuss few here

 

1.  PERFSTAT user

statspack uses PERFSTAT user to perform its operation, so check in DBA_USERS whether user is present of not using select * from dba_users where USERNAME='PERFSTAT';

2. Presence of table STATS$DATABASE_INSTANCE

statspack generates snap id's and save information in table STATS$DATABASE_INSTANCE, and table is created during installation of statspack, check using desc STATS$DATABASE_INSTANCE if table is present, if present then statspack is installed otherwise not

3. Running report using spreport.sql

For DBA's convenience, there are number of scripts provided by oracle in admin folder under oracle home directory, spreport.sql is such script used to generate statspack report , try running this sql file, if it asks for begin snap id and end snap id then assume statspack is installed otherwise it will throw error.

 

 

Read More

Oracle : STATISTICS_LEVEL parameter

In Oracle as we gather and view statistics though different views or tables can be controlled by instance level parameter STATISTICS_LEVEL
This particular parameter has below three possible settings

• BASIC
• TYPICAL
• ALL

The parameter controls automatic gathering of statistics at two levels 

Instance statistics : statistics accumulate withing instance regarding activity

Object statistics : stats accumulated on objects within database

The instance statistics accumulated in memory and flushed to AWR ( automatic workload repository) by MMON (manageability monitor) background process

Object statistics are gathered by DMBS_STATS procedure calls

Now we will discuss about parameter setting 

TYPICAL : The default setting is TYPICAL, this setting will gather all statistics needed by self management and tuning capabilities of the database and will also enable automatic object analysis task that runs daily in maintenance window

BASIC: The BASIC setting disable  AWR statistics and disable daily analysis

ALL : ALL setting gathers all possible statistics, these include operating system statistics and very detailed statistics on SQL statement execution. This shortcoming with this method is it will impact performance adversely.

Suggestion : this parameter should always be kept default and of ALL setting is required should be kept for short term.

This parameter can be altered runtime using simple alter system command

alter system set STATISTICS_LEVEL= BASIC;

alter system set STATISTICS_LEVEL= TYPICAL;

alter system set STATISTICS_LEVEL= ALL;

and same can be viewed using

show parameter  STATISTICS_LEVEL

Read More