Click the Administration link on the database home page.

Click the Tablespaces link.

Click the Create button.

Enter TBSALERT as the tablespace name an d then click Add to define a datafile for the tablespace.

Enter tbsalert.dbf as the datafile name and the 20 MB as the size. Click Continue.

Click Thresholds to specify the space used warning and critical threshold levels.

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.

Click the Database breadcrumb to go back to the Administration property page.

Click the Tables link.

Click Create.

Click Continue.

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.

Select Yes for Enable Row Movement and click OK to complete creation of the table.

Your table has been created. Click the Database breadcrumb.

Open a SQL*Plus session and execute the following command:

sqlplus system/oracle

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;
/
          

Go to the Enterprise Manager window. Click the Database breadcrumb to return to the Administration page then click the Tablespaces link.

Notice that the TBSALERT tablespace space used percentage has increased.

Switch back to the SQL*Plus window and copy and paste the following commands into your SQL*Plus session to 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;
/
           

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.

Switch back to the SQL*Plus window and copy and paste the following commands into your SQL*Plus session to 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;
/

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

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%.

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.

To locate the SYSTEM.EMPLOYEES1 table, enter system in the Schema field and emp in the Object Name field. Click Go.

Click the EMPLOYEES1 link.

Click Segments.

Notice the percentage of wasted space in the EMPLOYEES1 table. 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.

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.

You should see a Tablespaces Full alert. Click on the Tablespace [TBSALERT] is [70 percent] full link.

Click Manage Metrics in the Related Links region.

Click Edit Thresholds.

Scroll down to the Tablespace Space Used (%) and select this metric. Scroll back up to the top of the window.

Click Specify Multiple Thresholds.

For the TBSALERT tablespace, change the Warning Threshold to 70 and the Critical Threshold to 80. Click OK.

The change has been made. Click OK to save the data in the database.

The update was successful. Click the Database breadcrumb.

Click Setup at the top of the Database home page.

Click Notification Methods.

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.

Your update was successful. Click Preferences at the top of the page.

click Add Another Row for E-mail Addresses the General option.

Enter notify01@oracle.com as the email address and click Apply. Then click Database.

Open a terminal window and execute the following commands:

sqlplus hr/hr
create table emp as select * from employees;
delete emp; 

Open another terminal window and execute the following commands to create a row locking conflict:

sqlplus hr/hr
delete emp;

Click Performance in your Enterprise Manager window .

You see that the sessions waiting is very high. Wait about 10 minutes and scroll down to the bottom of the window.

You will now create a snapshot to capture the performance finding. Click on Snapshots.

Click Create to create a snapshot.

Click Yes to create a Manual Snapshot.

A snapshot is now being taken.

Once the snapshot is created, click the database breadcrumb and then home.

A performance finding is now detected through an alert in the Alert section of the Home page.

Click the Alert Database Time Spent Waiting.

You notice that the recommended action is to run ADDM to get more performance analysis about your system. Click Additional Advice.

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.

You see the action that needs to be taken to resolve the performance issue. Click View Rationale.

You see the particular SQL ID that is causing the problem. Click Close.

To resolve the performance finding, click the Database breadcrumb.

Click Performance.

Scroll down and select Blocking Sessions under Additional Monitoring Links.

Make sure the highest level HR is selected and click Kill Session.

Click Yes to kill the session.

The session has been killed. Click the Database breadcrumb and then click Home.

Notice the alert has disappeared.

From within the Active Sessions Waiting: User I/O page, click Top SQL in the middle of the page.

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.

The execution plan for this SQL statement is displayed. Click the Current Statistics tab.

The statistics for this SQL statement is displayed. Click the Execution History tab.

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.

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.

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.

Click Run SQL Tuning Advisor.

At the Schedule Advisor window, make sure the Scope Comprehensive is selected and the job will be scheduled Immediately. Click OK.

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.

Click View Recommendations.

Click the New Explain Plan button to view the suggested change.

As can be seen, the new explain plan removes the full table scans. Click the browser back button to return to the previous page.

Click the Implement button to implement the tuning recommendation.

A confirmation page appears indicating that the SQL Profile was successfully created. Click the Database breadcrumb.

Click Performance.

Scroll down to the Sessions: Waiting and Working chart. Wait for about one minute and observe how the User I/O is decreasing.

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;

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;
 

Open your browser and enter the following URL:

http://<hostname>:5500/em

Enter sys/<password> as SYSDBA and click Login.

Scroll to the bottom of the Home page and click on Advisor Central under Related Links.

Click on the SQL Access Advisor link.

Make sure Current and Recent SQL Activity is checked and click on Show Advanced Options.

Scroll down. Under Filter Options, select Filter workload based on these options. Select Only the top resource consuming SQL statements and Only SQL statements executed by the following users. Then enter SH in the Users field and click Next.

Select Both Indexes and Materialized Views and click Next.

Enter the task name OBE<Today's Date>, select Standard for the Schedule Type and click Next.

At the summary window, click Submit.

Make sure your job is selected and click View Result.

Click on the Recommendation ID 1 to see the details of the Recommendations.

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.

To see the SQL Script that will be executed when you schedule the implementation, click Show SQL.

Scroll down to the bottom and you will see the statements to create the materialized view with the change you just made. Click OK.

To implement the recommendations, click Schedule Implementation.

Enter OBEIMPL<today's date> for the Job Name and click Submit.

Your implementation job was created and is now running. Click the Run History tab.

Make sure your job is selected and click View.

Review the summary and click your database breadcrumb.

Click Materialized View.

Enter SH in the schema field and click Go.

Notice that the newly created Materialized View appears in the list. Click the Database breadcrumb then click the Home tab.

Scroll down to the bottom of the home page and click on Advisor Central under Related Links.

Select Memory Advisor.

Click Enable for Automatic Shared Memory Management.

Click OK to enable Automatic Shared Memory Management.

The Oracle server will now automatically adjust the settings for the various pools and caches according to the requirements of the workload.

Scroll down the page. Change Maximum SGA Size to 261 MB and click Apply. Note: If you receive an error, click the Refresh button and try it again.

Click Yes to confirm the change.

Supply the host credentials and the database credentials. Click OK.

When you changing the Max SGA Size parameter, the instance must be restarted. Click Yes to confirm restart of the database.

The database restart process will start. Click Refresh.

Enter sys/<password>as SYSDBA and click Login.

Note that the Max SGA Size parameter was automatically adjusted to conform to the memory granule size Though you had set it to be 261 MB originally, the Oracle server automatically changed it to 264 MB.

Once you receive the confirmation notice that the parameter was changed successfully, you'll also notice that memory allocation to some of the SGA components was adjusted automatically.

Click PGA.

Click Advice....

The PGA Aggregate Target Advice graph shows the frequency in which data is found in cache so that you do not have to access disk. In this case, it should be noted that the current PGA Aggregate Size is set to approximately 24 MB, and over 88% of all the requested services are gotten from memory. This also shows the overflow range which starts around 12 MB. At 12 MB, the PGA requests hit the cache around 90%. The PGA Aggregate Size implies that (based on current workloads and the number of sessions in the database), no more than 24 MB should be allocated for all PGAs in this database. Click OK.

Click PGA Memory Usage Details.

This graph shows the usage details in memory size requests and executions percentages for various PGA memory requests. Click OK.