Open your browser and enter the following URL (Replace <hostname> with your own host name or IP address):

http://<hostname>:1158/em

Enter sys as User Name, oracle as Password, SYSDBA in the Connect As field, and click Login.

You are now on the Database Home page. Click the Performance tab.

On the Performance page, make sure that the Real Time: 15 Second Refresh value is selected from the View Data field. Wait for a few seconds until the graphic appears. Scroll down to the Average Active Sessions graphic.

You should see a graphic similar to the following one, although there may be larger or smaller spikes and more or less activity displayed.

To start a SQL*Plus session, select Start > Programs > Oracle - OraDb10g_home1 > Application Development > SQL Plus. Enter hr as the User Name, hr as Password and click OK.

Execute the following commands to create a sequence, a simple table, and an index in the HR schema:

@c:\wkdir\addm01

The addm01.sql file includes the following commands:

drop table t purge;
drop sequence s;
create table t(c number,d varchar2(20));
create index it on t(c);
create sequence s 
start with 1
increment by 1
nomaxvalue; 

These objects are used to show the ADDM functionality. Keep the SQL*Plus session open throughout this tutorial.

Click the Administration tab.

Click Edit to change the snapshot interval.

Select 10 minutes from the Interval drop down list and click OK.

When returned to the Automatic Workload Repository page, check the number of snapshots for your system. In the example shown here, the number of snapshots is currently 168. Click Refresh.

Wait until you see that the number of snapshots is increased by one. You may have to click Refresh more than once. After a new snapshot has been generated, click the Database Instance: orcl locator link on the top left corner of the page.

Return to your SQL*Plus session and type in the following text but do not press [Enter]. You press [Enter] in a later step to execute the script.

@c:\wkdir\addm02

The addm02.sql script contains the following commands:

set echo on
set serveroutput on
begin
for i in 1..654321 loop
insert into t values(s.nextval,'first');
commit;
end loop;
end;
/

To start a second SQL*Plus session, select Start > Programs > Oracle - OraDb10g_home1 > Application Development > SQL Plus. Enter hr as the User Name, hr as Password and click OK.

Enter the same command as in the step above and press [Enter] to execute it.

@c:\wkdir\addm02

Immediately switch to the first SQL*Plus session and press [Enter] to execute the script. Each addm02.sql script now inserts rows from a different session.

Switch back to your browser window where the Administration page is displayed. Click the Performance tab.

Scroll down to view the graphs on the page. In all graphs, you should see a noticable increase, a rise in activity.

Note: In all following examples, your actual performance data and graphics look, most likely, different from the ones displayed here. This depends on hardware, software configuration and system activity.

Click the Top Activity link located in the legend area for the Average Active Sessions graphic.

On the Top Activity page, if needed, drag the shaded box over the peak in the graph which you created with the execution of the two addm02.sql scripts.

Notice in the Top Sessions section (bottom right), the two HR users executing SQL scripts from their SQL*Plus sessions. Click the SQL ID link for the INSERT statement in the Top SQL section (bottom left).

On the SQL Details page, you can see the text of the SQL statement, that is responsible for the peak in the performance graph. You see this statement again in the next section through an AWR snapshot. ADDM can identify these performance issues automatically and provide recommended solutions. To proceed, click the Database Instance: orcl locator link.

This script creates the same objects as the addm01.sql script, but the S sequence now has a cache value of 700,000 instead of the default is 20.

Return to your SQL*Plus session and enter the following command:

@c:\wkdir\addm03

The addm03.sql script contains the following commands:

drop table t purge;
drop sequence s;
create table t(c number,d varchar2(20));
create index it on t(c);
create sequence s
start with 1
increment by 1
nomaxvalue
cache 700000;
/

You have now resolved this issue. You had also noticed, that the HR user executed the addm02.sql script from two sessions. To further improve performance, you encourage the user the execute this script sequentially.

Return to your SQL*Plus session and enter the following command:

@c:\wkdir\addm02

Switch back to your browser window. On the Performance page scroll down to the Average Active Sessions graph. After a few minutes you should see some activity but much less than during the first workload period.

Wait until a new snapshot icon appears at the bottom of the Average Active Session graph, then click the snapshot icon to view the ADDM findings.

You may not have any findings at all, but this depends on your overall system activity. Click the Icon Key link and familiarize yourself with the meaning of the icons.

Click the Database tab in the top right corner.

You are on the Database home page.

On the Database home page, click the Performance tab.

Select Historical from the View Data drop down list.

The Performance page for the ORCL instance now displays historical data and the Historical Interval Selection chart. The database activity graphs can show several days worth of performance measurements, depending upon how long the database instance has been running.

For the selected time interval, you can view theCPU Utilization for your Host, Average Active Sessions, Instance Disk I/O and Instance Throughput charts.

If the instance has been running for more than 24 hours, you can drag the shaded box to the desired 24-hour interval for which you want to view historical data, as shown here.

Click the Administration tab.

Click Edit to change the snapshot interval.

Select 1 Hour (or whatever you standard value is) from the Interval drop down list and click OK.

Return to one of your SQL*Plus sessions and enter the following command:

@c:\wkdir\addm_cleanup

The addm_cleanup.sql script contains the following commands:

drop table t purge;
drop sequence s;

Close all tutorial windows.