ORACLE TRANSLATOR SHEET

Translator Information

Name	Oracle Performance
Full Path on Server	/usr/local/APT/ORA/aptx-ora
Directory of Data Files	ORACLE

Available Metrics

Metric name	Units
Buffer Cache Hit Ratio	percent
Read IO Requests	Number per collection period
Write IO Requests	Number per collection period
Full Table Scans	Number per collection period
Continuous Row Fetches (Chain)	Number per collection period
Logon Count	Number per collection period
Sorts in Memory	Number per collection period
Sorts on Disk	Number per collection period
Redo Space Requests (Cumulative)	Number, cumulative, since the start of instance
Library Cache Hit Ratio	percent

Installation

Run autoconfig.ksh in /usr/local/APT/ORA

Data Source Information

Uses Oracle Statspack provided with Oracle Server 8 and higher.

Required Permissions

Execute on sqlplus.

Configuration and Installation of the StatsPack Package.

--------------------------------------------------------

- StatsPack Database Space Requirements

Oracle does not recommend installing the package in the SYSTEM tablespace. A

more appropriate tablespace (if it exists) would be a "TOOLS" tablespace. If

you install the package in a locally-managed tablespace, storage clauses are

not required, as the storage characteristics are automatically managed. If you

install the package in a dictionary-managed tablespace, Oracle suggests you

monitor the space used by the objects created, and adjust the storage clauses

of the segments, if required.

The default initial and next extent size is 1MB for all tables and indexes

which contain changeable data. The minimum default space requirement is

approximately 35MB.

The amount of database space required by the package will vary considerably

based on the frequency of snapshots, the size of the database and instance, and

the amount of data collected (which is configurable). It is therefore

difficult to provide general storage clauses and space utilization predictions

which will be accurate at each site.

- Installing the StatsPack package

Interactive Installation:

========================

During the installation process, the user PERFSTAT will be created, default

password PERFSTAT. This user will own all PL/SQL code and database objects

created (including the STATSPACK tables, constraints and STATSPACK package).

The installation SQL script will prompt for the PERFSTAT user's default and

temporary tablespaces and also for the tablespace in which the tables and

indexes will be created

NOTE:

o Do not specify the SYSTEM tablespace for the PERFSTAT users

DEFAULT or TEMPORARY tablespaces; if SYSTEM is specified the

installation will abort with an error indicating this is the

problem. This is enforced as Oracle do not recommend using

the SYSTEM tablespace to store statistics data, nor for sorting.

Use a TOOLS tablespace to store the data, and your instance's

TEMPORARY tablespace for sorting.

o During the installation, the dbms_shared_pool and dbms_job

PL/SQL packages are created. dbms_shared_pool is used to

pin the Statspack package in the shared pool; dbms_job

is created on the assumption the DBA will want to schedule

periodic snapshots automatically using dbms_job.

o The installation grants SELECT privilege to PUBLIC for all

of the Statspack owned tables (prefixed by STATS$).

Even though SVRMGRL is available with Oracle8i releases 8.1.6 and 8.1.7,

installation of StatsPack can only be done with SQL*Plus do to special

formatting commands that are used in the scripts.

To install the package, using SQL*Plus and based on the correct platform and

RDBMS version, do the following:

Oracle 8.1.6:

on Unix:

SQL> connect / as sysdba

SQL> @?/rdbms/admin/statscre

on NT:

SQL> connect / as sysdba

SQL> @%ORACLE_HOME%\rdbms\admin\statscre

on VMS

SQL> connect / as sysdba

SQL> @ora_rdbms_admin:statscre

Oracle8i 8.1.7 and Oracle9i 9.0.1

on Unix:

SQL> connect / as sysdba

SQL> @?/rdbms/admin/spcreate

on NT:

SQL> connect / as sysdba

SQL> @%ORACLE_HOME%\rdbms\admin\spcreate

on VMS

SQL> connect / as sysdba

SQL> @ora_rdbms_admin:spcreate

Gathering a StatsPack snapshot

------------------------------

The simplest interactive way to take a snapshot is to login to SQL*Plus as the

PERFSTAT user, and execute the procedure statspack.snap:

e.g.

SQL> connect perfstat/perfstat

SQL> execute statspack.snap;

Note: In an OPS environment, you must connect to the instance you wish to

collect data for. A snapshot must be taken on each instance so that later

comparisons can be made. A snapshot taken on one instance can only be

compared to another snapshot taken on the same instance.

This will store the current values for the performance statistics in the

StatsPack tables, and can be used as a baseline snapshot for comparison with

another snapshot taken at a later time.

How to automatically gather StatsPack snapshots:

------------------------------------------------

To be able to make comparisons of performance from one day, week or year to

the next, there must be multiple snapshots taken over a period of time. A

minimum of two snapshots are required before any performance characteristics of

the application and database can be made.

The best method to gather snapshots is to automate the collection on a

regular time interval. It is possible to do this:

- Within the database, using the Oracle dbms_job procedure to schedule the

snapshots

- Using Operating System utilities (such as 'cron' on Unix or 'at' on NT) to

schedule the snapshot. Please contact the System Administrator for more

information about using the OS utilities for automating this data collection.

- Scheduling StatsPack snapshots using DBMS_JOB package

To use an Oracle-automated method for collecting statistics, you can use

dbms_job. A sample script on how to do this is supplied in statsauto.sql,

which schedules a snapshot every hour, on the hour.

In order to use dbms_job to schedule snapshots, the job_queue_processes

initialization parameter must be set to greater than 0 in the configuration

file used to start the instance for the job to be run automatically.

Example of an init<SID>.ora entry:

# Set to enable the job queue process to start. This allows dbms_job

# to schedule automatic statistics collection using STATSPACK

job_queue_processes=1

If using statsauto.sql in OPS environment, the statsauto.sql script must be

run once on each instance in the cluster. Similarly, the job_queue_processes

parameter must also be set for each instance.

Changing the interval of statistics collection

-----------------------------------------------

To change the interval of statistics collection use the dbms_job.interval

procedure

e.g.

execute dbms_job.interval(1,'SYSDATE+(1/48)');

Where 'SYSDATE+(1/48)' will result in the statistics being gathered each 1/48

hours (i.e. every half hour).

To force the job to run immediately,

execute dbms_job.run(<job number>);

To remove the autocollect job,

execute dbms_job.remove(<job number>);