Using diagnostic tool to measure the performance

[Adriana_andros]

Hi,
I am doing one project, which is related to oracle workload detection. I have to find which workload is running now.I want to know about how we can take the data from oracle views(instances) as the performance of oracle workload by dynamic table that are easily as by select query also I want to know that how to retrieve data from oracle diagnostic tool(performance of workload).If any one knows please help me.

[Praetor]

There are so many facilities are available to tune the oracle database and the tools are updated and facilitated to oracle with every versions.

You can easily take the example of oracle 9i,That is middle version of the every oracle database.You can investigate your database with the following tools which are very popular to use :

1. Statspack
2. Dynamic view(Instance level information)

[Katty]

If you are going to tune the database,there should be a baseline through which you can find out the status for different positions of database.Before sometime your database is running well but now because of some workload,the performance is going down.
For these things,you need a diagnostic report which will tell you the difference between both position and which one component is causing the bad performance.

This task can be happened using statspack diagnostic tool of oracle database.It stores all the diagnostic in a system defined table which is used in reporting the performance later.

[kelfro]

Installation of statspack:

For the installation of statspack you need to follow these steps:

1. Open your database and login as sysdba

SQL> CONNECT / AS SYSDBA

2.Execute the spcreate file :

SQL>ORACLE_HOME/rdbms/admin/spcreate

3. Enter the valid information about statspack user and default and temporary table spaces with the associated password.

[Zecho]

You can use the dynamic view which will give you the fluctuation of database.but they can't explore that much.

There are three different category of views are available to measure the performance :

1. Current State Views
This catagory will introduce with the dynamic performance view which will show you the current information of system.

2. Counter/Accumulator Views
It will show that how many times the events occur at instance or session start up.

3. Information Views
It will show you the fluctuation of instance with different sub-components.