Click the Administration link on the database home page.

Click the Tablespaces link.

Click the Create button.

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

Enter tbsalert01.dbf as the datafile name and 20 MB as the filesize. Click Continue.

Click OK to create the tablespace.

Select your new tablespace, TBSALERT, and click Edit.

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

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.

You receive an update confirmation message. Click the Database breadcrumb to go back to the Administration property page.

Click the Tables link in the Database Objects section.

Click Create.

Accept the default of Standard, Heap Organized and 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. The page is refreshed. Enter select * from hr.employees in the CREATE TABLE AS field. Click Options.

Select Yes in the Enable Row Movement drop-down menu. Click OK to complete the table creation.

Your table has been created. Click the Database breadcrumb.

Open a SQL*Plus session and connect as the SYSTEM user as follows:

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

Return to Enterprise Manager and click the Tablespaces link on the Administration page.

Notice that the TBSALERT tablespace space used percentage has increased.

Return 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 and 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 Edit.

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

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.

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

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.

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

The update was successful. Click the Database breadcrumb to return to the Database Home page.

Click Setup at the top of the Database home page.

Click Notification Methods.

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.

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 to return to the Database Home page.

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 .

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.

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 Instance breadcrumb and then the Home tab .

A performance finding is now detected and displayed as an alert in the Alert section on the Home page.

Click the finding SQL statements were found waiting for row lock waits .

Click the SQL ID in the Rationale section.

Review the information on the SQL Details page. Click the Database Instance breadcrumb.

Click Performance.

Scroll down and select Blocking Sessions under Additional Monitoring Links. Skip to step 8.

Note: If Blocking Sessions does not appear in the Additional Monitoring Links, click Home to return to the Database Home page. Proceed to step 6.

On the Database Home page, check the Alerts section for an alert indicating a blocked session. Click on the alert.

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 Instance breadcrumb and then click Home.

Notice the alert has disappeared.

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;

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;
 

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

Click the SQL Access Advisor link.

Select Use Default Options and click Continue.

Ensure Current and Recent SQL Activity is selected. Expand Filter Options.

Select Filter Workload Based on these Options. Select Include only SQL statements executed by these users. 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.

On the Advisor Central page, ensure your job is selected and click View Result.

The Summary page is displayed. Click Recommendations.

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

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

Click Database to return to the Database Home page. Click Administration.

Click Materialized Views in the Materialized Views section.

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.

If Automatic Shared Memory Management is not enabled, click Enable. If it is enabled skip to Step 5.

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 300 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. Wait a few minutes and then click Refresh.

Note that the Max SGA Size parameter was automatically adjusted to conform to the memory granule size Though you had set it to be 282 MB originally, the Oracle server automatically changed it to 284 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 the PGA tab on the Memory Parameters page.

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. Click Database to return to your Database Home page.