Performance Tuning : How to configure Statspack Performance Diagnostic Tool

In order to tune the database, oracle provides a diagnostic tool by which you can find out the health of your database. This guide provides beginners and novices how to install configure and use statspack tool and its reports.

1. Installation

 Installing the statspack is an easy step. The only two things that need to be done is to execute the statspack creation script that is located in %OracleHome%\rdbms\admin folder. The script is named as “spcreate.sql” for creating the statspack objects and “spdrop.sql” to drop the statspack user and its associated objects. The second thing though not necessary that you can do is to dedicate a seperate tablespace for statspack, i’ve created a seperate tablespace named STATSPACKTBS for it.

In oder to install the statspack, use the following comand replacing the location where the script is located:

SQL> @c:\oracle\rdbms\admin\spcreate.sql;

During the execution of the script, it will ask three things from the user (1) the password for the username of statspack which is “perfstat”.  (2) Default tablespace for statspack (mine is dedicated STATSPACKTBS), and (3) Temporary tablespace for use.

2.  Generating Snapshots

In order to generate statspack report, first a snapshot of the database needs to be taken so that the analysis can be done. The report is always generated between any two snapshots. To generate a snapshot use the following command:

SQL> execute statspack.snap;

3.  Generating Statspack Report

In order to generate statspack report, we only have to execute another script supplied by oracle located in rdbms\admin folder named as “spreport.sql”. To generate a report, it is necessary that you must have atleast two snapshots of the database otherwise lots of portions of the report would not be analysed or calculated by the statspack tool. To generate a report, use the following command :

SQL> @C:\oracle\rdbms\admin\spreport.sql;

At the start of the report, it will show the list of snapshots currently present and would onwards ask for the beginning and ending snapshot ID to compare and analyse.

Calling a Non-Oracle DB Remote Procedure through Oracle Heterogenous Services

Hi All … sorry for the late posting, have been bz in some of my work and OCP studies. Here is a short post for calling a non-oracle db remote procedure from oracle db environment. This post is also in continuation with my pervious post of Heterogeneous Services connectivity

Although executing sql statements such as select statement and performing DML statements are easy using oracle HS database link. This is not the same case with executing functions and procedures of the remote DB from oracle environment. For executing a procedure, Oracle has provided a DBMS package known as DBMS_HS_Passthrough which actually sends the request to the remote db for execution.

Starting Oracle 9i Instance using SPFile at a Non-Default Location

In oracle 9i database, the only location that the oracle server checks for the server parameter file (spfile) is its Oracle Home\Database (in windows) or Oracle Home\dbs (in unix) folder. Sometimes it becomes necessary to start the instance using spfile from a non-default location. The only way to perform this is through the use of pfile and linking the pfile to point to the non-default location of spfile. Below are the steps of how to perform this task :-

1. Shutdown the database using
sql> shutdown normal

2. Add only one entry parameter namely ‘IFILE’ in a newly created pfile as below

IFILE = ‘F:\BACKUP LOCATION\spfileORCL.ORA’

3. Save the file at the default or any desired location e.g. C:\pfileORCL.ora

4. Start the database using the following command while specifying the pfile location….

sql> startup pfile=’C:\pfileORCL.ora’

5. After database startup, verify whether the instance has been started up with spfile using the below command:
sql> sho parameter spfile

If the value column contains a non null value for the spfile parameter, it would indicate that the instance has used spfile for startup, which in our case is at the non-default location. That is the only option available in oracle 9i to startup the oracle instance from a non-default location, no other option exists.

Thanks…

Happy learning …

Cheers !….

Using Oracle Heterogeneous Services for Connecting to Non-Oracle Databases

Recently I came across an integration requirement of an application using oracle database with another application having a sql server at the back. This short guide provides step by step procedure for connecting a non-oracle database (sql server in my case) using oracle heterogeneous services and database links.

Oracle Heterogeneous services provide a generic method for connecting to any non-oracle ODBC or OLEDB compliant database such as MS Acess, SQL Server etc. One of the other services by oracle is known as Oracle Transparent Gateways which in contrast to Oracle Heterogeneous services provide a more optimized and better functionality for connecting to non-oracle systems. For simple connectivity, I utilized the heterogeneous service that is also simple and easy to configure.

Creating a System DSN

Oracle Export and Import Utilities

This guide provides a basic overview and example of oracle database export and import features. Oracle export / import utility provides a simple way for transfering data between databases even if the databases are on different platforms.  The export / import utility also provides a support towards having backup and recovery of the databases even recovering at the table level. The Oracle export utility generates a binary format dump file that is typically located on a disk or a tape. Some of the uses of export and import utility is as follows:

1. Provides a method of backing and archiving historical data.
2. Provides a way for saving a baseline structure of table definitions that can be used in case of user errors
3. Provides export and import between different versions of Oracle server.
4. Provides method of transporting tablespaces between different databases.

Methods of Utility Invocation