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 and then click Add to define a datafile
for the tablespace.
5.
Enter tbsalert01.dbf
as the datafile name and 20
MB as the filesize. Click Continue.
Click OK to create the tablespace.
6.
Select your new tablespace, TBSALERT, and click Edit.
Click Thresholds to specify the space used warning
and critical threshold levels.
7.
Click Specify Thresholds in the Space Used (%) section. Set the database wide
default threshold values for space usage. Enter 60 for the Warning%,
and 68 for the Critical%. Click Apply.
8.
You receive an update confirmation message. Click the Database breadcrumb to go back to the
Administration property page.
9.
Click the Tables link in the Database Objects section.
10.
Click Create.
11.
Accept the default of Standard, Heap Organized and 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. The page is refreshed. Enter select
* from hr.employees in the CREATE TABLE AS field. Click Options.
13.
Select Yes in the Enable Row Movement drop-down
menu. Click OK to complete the table creation.
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 connect as the SYSTEM user as follows:
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.
Return to Enterprise Manager and click theTablespaces
link on the Administration page.
4.
Notice that the TBSALERT
tablespace space used percentage has increased.
5.
Return 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 and 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 Edit.
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 Refresh. Because there has not been 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 browser refresh/reload 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
the Tablespace TBSALERT is 70 percent full link.
17.
The Tablespaces page is displayed. Note that
recommendations are given on how to resolve the issue. For the purposes
of this exercise, do not make any changes at this time.
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 repository.
7.
The update was successful. Click the Database
breadcrumb to return to the Database Home page.
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
Mail (SMTP) Server field, dbaalert in the Identify Sender As field and
notify01@oracle.com in the Sender's E-mail Address 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 to return to the Database
Home page.
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.
Click Application in the Average Active Sessions section.
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 Instance
breadcrumb and then the Home tab .
10.
A performance finding is now detected and displayed as an alert
in the Alert section on the Home page.
Create a directory named $HOME/wkdir.
Download the sqltune.tar file and unzip the file
into the $HOME/wkdir directory.
Perform the following steps:
1.
Connect as SYSDBA through Database Control and navigate
to the Performance tab of the Database Control Home page. On the
Performance page, make sure that the View Data field is
set to Real Time: 15 second Refresh.
2.
Open a terminal emulator window connected as user oracle.
Change your current directory to your wkdir directory. Then, enter the
following command from the OS prompt:
./setup_dina.sh
3.
Execute the start_dinas.sh script as follows:
./start_dinas.sh
4.
Return to Enterprise Manager and observe the Performance
page for six minutes.
5.
Return to your Database Home page. You will now determine
the problem. If the time corresponding to the problematic time period
corresponds with the latest ADDM run detected by Database Control, you
should find the link corresponding to the correct performance analysis
directly in the Diagnostic Summary section of the Database Control home
page. If there is a link, click the link and proceed to step 10.
If there is no link in ADDM Findings, access
the Advisor Central page and search for the ADDM task as outlined in steps 6-9.
6.
Click the Advisor Central link in the Related
Links section.
7.
Select ADDM in the Advisory Type drop-down menu.
Select Last 24 Hours in the Advisor Runs drop-down menu. Click
Go.
8.
Select the ADDM task corresponding to the problematic
time period. Click ViewResult.
9.
On the ADDM page you see the results in the Performance
Analysis section. Click the finding with the highest impact on the database
time. It should correspond to a SQL Tuning recommendation.
10.
On the Performance Finding Details page you see the high-load SQL statement captured by the ADDM analysis. The information provided indicates that there will be a significant benefit if you tune this statement. Click Run Advisor Now for the highest high-load SQL statement detected. Skip to step 14.
11.
If there is no Run Advisor Now button, click the SQL text.
12.
The SQL Details page is displayed. Click Schedule SQL Tuning Advisor.
13.
The Schedule Advisor page is displayed. Click OK.
14.
The SQL Tuning Advisor task is scheduled. The page will advance when the task completes.
15.
Recommendations are displayed. In this case, the recommendation
is to create a SQL Profile in order to get a better execution plan. Click
Implement.
16.
The SQL Profile is created.
17.
Return to your terminal window. To see the changes you
implemented, you must re-execute the SQL. Stop and start the workload
by executing the following commands:
./stop_dinas.sh
./start_dinas.sh
18.
Return to Enterprise Manager and access the Performance
page to see the benefit of your tuning.
19.
Return to your terminal window. Clean up your environment
by executing the following commands:
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/oracle
SELECT * FROM user_objects
WHERE object_type = 'MATERIALIZED VIEW';
If any of the following materialized views exist, delete them as shown:
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;
If the SH account is locked, unlock the SH account and enter the password SH for
the account. Refer to the Administering Users and Security chapter for
additional information.
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;
Now you can look at the results and implement them if you
wish. Perform the following:
1.
On the Advisor Central page, ensure your job is selected and click View Result.
2.
The Summary page is displayed. Click Recommendations.
Click on the Recommendation ID 1 to see the details
of the Recommendations.
3.
Here you can customize the Object Name, Schema and Tablespace
to implement the recommendations. Scroll down and change the Schema Name
for the Create Materialized View to SH and click OK.
4.
To see the SQL Script that will be executed when you
schedule the implementation, click Show SQL.
Bookmark
