More articles to come on this subject, this is just a small introduction.
To effectively perform reactive tuning, it is vital to have an established baseline for later comparison when the system is running poorly. Without a baseline data point, it becomes very difficult to identify what a new problem is attributable to: Has the volume of transactions on the system increased? Has the transaction profile or application changed? Has the
number of users increased?

Statspack fundamentally differs from the well known UTLBSTAT/UTLESTAT tning scripts by collecting more information, and also by storing the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using the report provided, which includes an ‘instance health and load’ summary page, high resource SQL statements, as well as the traditional wait events and initialization parameters.

Statspack improves on the existing UTLBSTAT/UTLESTAT performance scripts in the following ways:

- Statspack collects more data, including high resource SQL (and the optimizer execution plans for those statements)

- Statspack pre-calculates many ratios useful when performance tuning, such as cache hit ratios, per transaction and per
second statistics (many of these ratios must be calculated manually when using BSTAT/ESTAT)

- Permanent tables owned by PERFSTAT store performance statistics; instead of creating/dropping tables each time, data is inserted into the pre-existing tables. This makes historical data comparisons easier

- Statspack separates the data collection from the report generation. Data is collected when a ’snapshot’ is taken; viewing the data collected is in the hands of the performance engineer when he/she runs the performance report

- Data collection is easy to automate using either dbms_job or an OS utility

NOTE: The term ’snapshot’ is used to denote a set of statistics gathered at a single time, identified by a unique Id which includes the snapshot number (or snap_id). This term should not be confused with Oracle’s Snapshot Replication technology.

How does Statspack work?

Statspack is a set of SQL, PL/SQL and SQL*Plus scripts which allow the collection, automation, storage and viewing of performance data. A user is automatically created by the installation script - this user, PERFSTAT, owns all objects needed by this package. This user is granted limited query-only privileges on the V$views required for performance tuning.

Statspack users will become familiar with the concept of a ’snapshot’. ’snapshot’ is the term used to identify a single collection of performance data. Each snapshot taken is identified by a ’snapshot id’ which is a unique number generated at the time the snapshot is taken; each time a new collection is taken, a new snap_id is generated.

The snap_id, along with the database identifier (dbid) and instance number (instance_number) comprise the unique key for a snapshot (using this unique combination allows storage of multiple instances of a Clustered database in the same tables).

Once snapshots are taken, it is possible to run the performance report. The performance report will prompt for the two snapshot id’s the report will process. The report produced calculates the activity on the instance between the two snapshot periods specified, in a similar way to the BSTAT/ESTAT report; to compare - the first snap_id supplied can be considered the equivalent of running BSTAT; the second snap_id specified can be considered the equivalent of ESTAT. Unlike BSTAT/ESTAT which can by its nature only compare two static data points, the report can compare any two snapshots specified.

This is a two-step process. email if you have questions: fayoubi@gmail.com

STEP ONE : Modify your initSID.ora

Modify your initSID.ora to include the following three lines:

log_archive_start   = true
log_archive_dest    = /mount_point/oradata/SID/ARCHIVE/arch
log_archive_format  = _%s.arc

log_archive_start: This parameter specifies whether archiving should be started automatically when the database is started.

log_archive_dest: This value specifies the default location of the file to be used when archiving redo log files.

log_archive_format: This parameter is used to specify the default file name format for the archive log files, this value is appended to the log_archive_dest value ( %s is the log sequence number ).

STEP TWO : Starting archive logging

The final step to starting archive logging, involves an Oracle SQL*DBA command which is run when the database is mounted but not open.

With the database shut down, use one of the many DBA tools available to manually start the database ( I still prefer to use SQL*DBA ) and execute the following sequence of commands:

startup mount exclusive
connect internal
alter database archivelog;
alter database open;

Now check to ensure archive logging is running. Whilst still connected internal, execute the command archive log list as shown below:

SQLDBA> archive log list
Database log mode              ARCHIVELOG
Automatic archival             ENABLED
Archive destination            /mount_point/oradata/SID/ARCHIVE/arch
Oldest online log sequence     2
Current log sequence           3
SQLDBA>

As usual thanks for reading

Sometimes, you want to run a script in sqlplus but you end up mesing session state parameters in that box and therefore some scripts may end up faling.
Here is how you go about restoring those session state parameters back to there initial settings:

16:15:21 SQL> STORE SET d4_session_state.lst REPLACE
Wrote file d4_session_state.lst
16:15:24 SQL> set serveroutput on
16:15:29 SQL> set define off
16:15:34 SQL> sho arraysize
arraysize 15
16:15:46 SQL> set arraysize 35
16:15:51 SQL> sho arraysize
arraysize 35
16:16:08 SQL> !head d4_session_state.lst
set appinfo OFF
set appinfo “SQL*Plus”
set arraysize 15
set autocommit OFF
set autoprint OFF
set autorecovery OFF
set autotrace OFF
set blockterminator “.”
set cmdsep OFF
set colsep ” ”
16:15:57 SQL> @d4_session_state.lst
16:16:04 SQL> sho arraysize
arraysize 15
16:16:08 SQL>

Once again, thanks for reading