Move your mouse over this icon to show all screenshots.
You can also move your mouse over each individual icon to see only the screenshot
associated with it.
Alerts help you monitor your database proactively. Most alerts
are notifications when particular metrics thresholds are crossed. For each alert,
you can set critical and warning threshold values. These threshold values are
meant to be boundary values that when crossed indicate that the system is in
an undesirable state.
In this section, you will perform the following tasks:
First, you create a new tablespace with a 20 MB data file.
This tablespace should be locally managed, and use Automatic Segment Space Management
(ASSM). You will then create a new table in this new tablespace. This table
will have the Enable Row Movement option set to yes to allow for space reclamation
in the table. Perform the following:
1.
Click the Administration link on the database
home page.
2.
Click the
Tablespaces link.
3.
Click the Create button.
4.
Enter TBSALERT
as the tablespace name an d then click Add to define a datafile
for the tablespace.
5.
Enter tbsalert.dbf
as the datafile name and the 20
MB as the size. Click Continue.
6.
Click Thresholds to specify the space used warning
and critical threshold levels.
7.
Click Specify Thresholds, by percent used. Set
the database wide default threshold values for space usage. Enter 60
for the Warning%, and 68 for the Critical%. Click OK.
8.
Click the Database breadcrumb to go back to the
Administration property page.
9.
Click the Tables link.
10.
Click Create.
11.
Click Continue.
12.
Enter employees1 in the Name field. Specify SYSTEM
as the schema and TBSALERT
as the tablespace. Click on the Define Using drop-down list and select
SQL. Enter select *
from hr.employees in the Create Table As field, then click
Options.
13.
Select Yes for Enable Row Movement and click
OK to complete creation of the table.
14.
Your table has been created. Click the Database
breadcrumb.
You will now update the table to trigger a space utilization
alert. Perform the following:
1.
Open a SQL*Plus session and execute the following
command:
sqlplus system/oracle
2.
Copy and paste the following SQL commands into your
SQL*Plus session to
simulate user activity on the EMPLOYEES1
table:
begin
for i in 1..1000 loop
insert into employees1
select * from hr.employees;
commit;
end loop;
end;
/
3.
Go to the Enterprise Manager window. Click the
Database breadcrumb to return to the Administration page
then click the Tablespaces link.
4.
Notice that the TBSALERT
tablespace space used percentage has increased.
5.
Switch back to the SQL*Plus window and copy and
paste the following commands into your SQL*Plus sessionto simulate more user activity on the EMPLOYEES1
table:
delete employees1 where department_id = 50;
begin
for i in 1..500 loop
insert into employees1
select * from hr.employees;
commit;
end loop;
end;
/
6.
Go to the Enterprise Manager window. Refresh
your browser (for Linux Mozilla, select View from the menubar then
select Reload). Notice that the TBSALERT
tablespace space usage percentage has increased.
7.
Switch back to the SQL*Plus window and copy and
paste the following commands into your SQL*Plus sessionto simulate more user activity on the EMPLOYEES1
table:
begin
for i in 1..500 loop
insert into employees1
select * from hr.employees;
commit;
end loop;
end;
/
8.
Copy and paste the following SQL commands into your
SQL*Plus session to
simulate user activity on the EMPLOYEES1
table:
delete employees1 where department_id = 30;
commit;
delete employees1 where department_id = 100;
commit;
delete employees1 where department_id = 50;
commit;
delete employees1 where department_id = 80;
commit;
exit
9.
Go to the Enterprise Manager window. Refresh
your browser (for Linux Mozilla, select View from the menubar then
select Reload). Notice that the TBSALERT
tablespace space usage percentage has now exceeded the critical threshold
level of 68%.
10.
While you are waiting for the space usage alert to be
displayed on the Enterprise Manager home page, review the table segment
statistics. Click the Database breadcrumb then click the Tables
link.
11.
To locate the SYSTEM.EMPLOYEES1
table, enter system
in the Schema field and emp
in the Object Name field. Click Go.
12.
Click the EMPLOYEES1
link.
13.
Click Segments.
14.
Notice the percentage of wasted space in the EMPLOYEES1table. You may be able to resolve the tablespace space usage alert
by reclaiming unused space in this table.
On this same page, you can project the EMPLOYEES1
table's future space usage by specifying a date range for Space Usage
Trend and clicking the Refresh button. Because there has not
been a enough activity history on the EMPLOYEES1
table, you will not see very meaningful data in the space usage analysis
graph. Click the Database breadcrumb and then click the Home
page tab.
15.
Click the Refresh button a few times until you
see a red x and the number 1 next to Problem Tablespaces.
Scroll down to the Alerts table.
16.
You should see a Tablespaces Full alert. Click
on the Tablespace [TBSALERT] is [70 percent] full link.
Oracle provides a set of predefined metrics, some of which
initially have thresholds defined for them. You previously defined a metric
for Tablespace Usage for the TBSALERT
tablespace. To review all the metrics, perform the following:
1.
Click Manage Metrics in the Related Links
region.
2.
Click Edit Thresholds.
3.
Scroll down to the Tablespace Space Used (%)
and select this metric. Scroll back up to the top of the window.
4.
Click Specify Multiple Thresholds.
5.
For the TBSALERT
tablespace, change the Warning Threshold to 70 and the Critical
Threshold to 80. Click OK.
6.
The change has been made. Click OK to save the
data in the database.
7.
The update was successful. Click the Database
breadcrumb.
You can optionally provide notification when events that require
your intervention arise. By default, alerts in critical state such as Database
Down, Generic Alert Log Error Stats, and Tablespace Used are set up for notification.
Perform the following:
1.
Click Setup at the top of the Database home page.
2.
Click Notification Methods.
3.
Enter <your mailserver> in the Outgoing
Mailserver field, dbaalert in the Identify Sender As field and
notify01@oracle.com in the Sender's Email field and click Apply.
4.
Your update was successful. Click Preferences
at the top of the page.
5.
click Add Another Row for E-mail Addresses the
General option.
6.
Enter notify01@oracle.com as the email address
and click Apply. Then click Database.
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
60 minutes by default and reports its findings on the Oracle Enterprise Manager
Home page. ADDM runs automatically every 60 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 will create a performance finding.
In this case, you will create a session waiting on a row lock. To perform certain
operations such as updates and deletes, the session must obtain a lock on the
row. Perform the following steps to create a performance finding:
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.
Click Performance in your Enterprise Manager
window .
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 will now 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 and then home.
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 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 this
finding in the list.
4.
You see the action that needs to be taken to resolve
the performance issue. Click 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 Performance.
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 and then click Home.
You will first initiate several workload sessions. Then
you will investigate the database workload. Create a directory named $HOME/wkdir.
Download the perflab.tar file and unzip the file
into the $HOME/wkdir directory.
Perform the following steps:
1.
Open a command line window, and run the following OS
script:
./setup_perflab.sh
Note: This script will take approximately four minutes
to run.
2.
Switch back to your Enterprise Manager window. Select
the Administration link.
3.
In the section titled Workload, click on the
Automatic Workload Repository link.
4.
Determine how many snapshots have already been collected
for this database. Look under Snapshots for the count and the time
the last ADDM snapshot was taken. There should be at least three snapshots.
Click the Database breadcrumb.
5.
Click the Performance link.
6.
In the Performance Window, review the Sessions: Waiting
and Working graph. A chart representing the current workload of your
database is shown. (It may take a minute for the chart to be populated
with data) This chart is populated with data collected by the ADDM snapshots.
To the side of the graph is the legend. Each legend entry is coded to
a different color. You can determine quickly from the graph that the item
with the largest time is yellow, or User I/O. Click on the User I/O
link.
7.
Below the Active Sessions Waiting: User I/O chart, there
are two pie charts. Investigate the pie chart on the left, Top Waiting
SQL. This shows that the overwhelming majority of waits, 53%, were
caused by one SQL statement. Investigate the pie chart on the right, Top
Waiting Sessions. This pie chart shows that the current top active
sessions are waiting about the same percentage of time.
As was shown in the previous task, there is one SQL statement
causing the majority of the database waits. In this task you will drill down
to find the root cause. Perform the following:
1.
From within the Active Sessions Waiting: User I/O page,
click Top SQL in the middle of the page.
2.
On the detail page that appears, view the Wait Events
for Top SQL table, which is ordered by Activity (%). You can see the
Top SQL statement spent most of its time on the activity 'db file scattered
read'. Click on the SQL ID of the SQL statement with the highest percentage
of activity.
3.
The execution plan for this SQL statement is displayed.
Click the Current Statistics tab.
4.
The statistics for this SQL statement is displayed.
Click the Execution History tab.
5.
The statistical analysis chart for this SQL statement
is displayed. The CPU and Elapsed Time chart shows the amount of CPU used
by all executions of this SQL statement over a period of time. Select
Seconds Per Execution and click on Go to display the time
and resources used for each execution of this SQL statement.
6.
From the displayed charts, it can be determined that
CPU resource usage is increasing, and the time it takes to execute this
SQL statement is also increasing. Click the Tuning History tab.
7.
The previous tuning recommendations for this SQL statement
is displayed. At this time, there are none. You are now ready to tune
the SQL statement using the SQL Tuning Advisor.
As determined in the previous section on reactive tuning,
the targeted SQL statement needs tuning. The SQL Tuning Advisor will tune the
execution plan for you. Perform the following:
1.
Click Run SQL Tuning Advisor.
2.
At the Schedule Advisor window, make sure the Scope
Comprehensive is selected and the job will be scheduled Immediately.
Click OK.
3.
The SQL Tuning Advisor will create a task to analyze
the SQL statement, and upon completion of this task, display a set of
tuning recommendations.
4.
Click View Recommendations.
5.
Click the New Explain Plan button to view the
suggested change.
6.
As can be seen, the new explain plan removes the full
table scans. Click the browser back button to return to the previous page.
7.
Click the Implement button to implement the tuning
recommendation.
8.
A confirmation page appears indicating that the SQL
Profile was successfully created. Click the Database breadcrumb.
Reviewing SQL Execution Details for
a SQL Statement
The SQL Access Advisor provides a number of procedures which
can be called to help decide which materialized views and indexes to create
and drop. It makes this decision using either a hypothetical workload, which
it bases on your schema, or from an actual workload which can be provided by
the user, from Oracle Trace or from the contents of the SQL cache.
Workloads may also be filtered according to different criteria,
such as only use queries containing these tables or queries which have a priority
between this range.
To prepare the environment for using the SQL Access Advisor,
perform the steps below. Materialized views and indexes can be present when
the advisor is run, but for the purposes of this example they are removed so
that you can see what the advisor will recommend. You need to also set up the
cache so that the SQL Access Advisor can generate recommendations. Perform the
following:
1.
Open a terminal window and execute the following commands
to clean up your environment:
sqlplus system/<password>
DROP MATERIALIZED VIEW all_cust_sales_mv; DROP MATERIALIZED VIEW costs_mv; DROP MATERIALIZED VIEW costs_pm_mv; DROP MATERIALIZED VIEW cust_sales_mv; DROP MATERIALIZED VIEW some_cust_sales_mv; DROP MATERIALIZED VIEW cust_id_sales_aggr; DROP MATERIALIZED VIEW sales_cube_mv; DROP MATERIALIZED VIEW sales_gby_mv; DROP MATERIALIZED VIEW CUST_TOTAL_SALES_MV; DROP MATERIALIZED VIEW CUST_SALES_TIME_MV;
2.
Now you need to create the cache. Execute the following
commands:
alter system flush shared_pool; grant advisor to sh;
connect sh/sh; SELECT c.cust_last_name, sum(s.amount_sold) AS dollars, sum(s.quantity_sold) as quantity FROM sales s , customers c, products p WHERE c.cust_id = s.cust_id AND s.prod_id = p.prod_id AND c.cust_state_province IN ('Dublin','Galway') GROUP BY c.cust_last_name;
SELECT c.cust_id, SUM(amount_sold) AS dollar_sales FROM sales s, customers c WHERE s.cust_id= c.cust_id GROUP BY c.cust_id;
select sum(unit_cost) from costs group by prod_id;
Bookmark