At times database performance problems arise that require
your diagnosis and correction. Sometimes problems are brought to your attention
by users who complain about slow performance. Other times you might notice performance
spikes in the Host CPU chart on the home page.
In all cases, these problems are flagged by the Automatic
Database Diagnostics Monitor (ADDM), which does a top-down system analysis every
half hour by default and reports its findings on the Oracle Enterprise Manager
Home page. ADDM runs automatically every 30 minutes to coincide with the snapshots
taken by the Automatic Workload Repository (AWR). Its output consists of a description
of each problem it has identified, and a recommended action.
To show how ADDM works, you need to create a performance finding.
In this case, you will create a session waiting on a row lock. Perform the following:
1.
Open a terminal window and execute the following
commands:
sqlplus hr/hrcreate table emp as select * from employees;
delete emp;
2.
Open another terminal window and execute the following
commands to create a row locking conflict:
sqlplus hr/hrdelete emp;
3.
Open your browser and enter the following URL:
http://<hostname>:5500/em
Enter sys/<password> as SYSDBA and
click Login.
3.
From your Enterprise Manager browser window, click on
the Performance tab.
4.
You see that the sessions waiting is very high. Wait
about 10 minutes and scroll down to the bottom of the window.
5.
You want to create a snapshot to capture the performance
finding. Click on Snapshots.
6.
Click Create to create a snapshot.
7.
Click Yes to create a Manual Snapshot.
8.
A snapshot is now being taken.
9.
Once the snapshot is created, click the database
breadcrumb then the home tab.
10.
A performance finding is now detected through an alert
in the Alert section of the Home page.
When a performance finding is encountered, you can use ADDM
to resolve it. Perform the following:
1.
Click on the Alert Database Time Spent Waiting.
2.
You notice that the recommended action is to run ADDM
to get more performance analysis about your system. Click Additional
Advice.
3.
Make sure the snapshot you took is selected from the
list. Notice that SQL statements were found waiting for row lock waits
impacting your system for more than 86% of your database time. Click on
this finding in the list.
4.
You see the action that needs to be taken to resolve
the performance issue. Click on View Rationale.
5.
You see the particular SQL ID that is causing the problem.
Click Close.
6.
To resolve the performance finding, click the Database
breadcrumb.
7.
Click the Performance tab.
8.
Scroll down and select Blocking Sessions under
Additional Monitoring Links.
9.
Make sure the highest level HR is selected and click
Kill Session.
10.
Click Yes to kill the session.
11.
The session has been killed. Click the Database
breadcrumb then click the Home tab.
12.
Notice the alert has disappeared.
Place the cursor on this icon to hide all screenshots.
Bookmark
