Enabling Automatic Shared Memory Management using the Memory Advisor

Click the Server 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.

Select Specify Thresholds in the Space Used (%) section. Enter 60 for the Warning%, and the critical threshold level of 68%. Click Apply.

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

Select the Schema tab. 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 Server 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 60%.

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, the Schema tab 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.

Select the EMPLOYEES1 table. 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. Scroll down to the Space Summary section of the Home page. Locate a red x and a number 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. (Note: Your system may display slightly different data).

The Tablespaces page is displayed. Note that recommendations are given on how to resolve the issue by clicking on the Segment Advisor Recommendations button. However, for the purposes of this exercise, do not make any changes at this time.

Remain on this page to continue with the next exercise.

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

Once the snapshot is created, click the Database Instance breadcrumb and then the Home tab .

Click Home. A performance finding is now detected and displayed as an alert in the ADDM Performance Analysis section.

Click the finding 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.

Note: If Blocking Sessions does not appear in the Additional Monitoring Links, click Home to return to the Database Home page, and then click the Performance tab again.

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.

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.

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

Execute the start_dinas.sh script as follows:

./start_dinas.sh

Return to Enterprise Manager and observe the Performance page for six minutes.

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 ADDM Performance Analysis section of the Database Control home page.

Click the finding with the highest impact on the database time. It should correspond to a SQL Tuning recommendation.

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 the SQL text.

The SQL Details page is displayed. Click Schedule SQL Tuning Advisor.

The Schedule Advisor page is displayed. Click Submit.

The SQL Tuning Advisor task is scheduled. The page will advance when the task completes.

10.

Recommendations are displayed. In this case, the recommendation is to create a SQL Profile in order to get a better execution plan. Click Implement.

On the Confirmation window, click Yes.

The SQL Profile is created.

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

Return to Enterprise Manager and access the Performance page to see the benefit of your tuning.

Return to your terminal window. Clean up your environment by executing the following commands:

./stop_dinas.sh

./cleanup_dina.sh

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;

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

Click SQL Access Advisor.

Select Recommend new access structures 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 SQLACCESS<Today's Date>, select Standard for the Schedule Type and click Next.

At the summary window, click Submit.

A confirmation message appears.

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 implement the recommendations, click Schedule Implementation.

Enter SQLACCESSIMPL<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 Schema.

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

Confirm that Automatic Shared Memory Management is enabled. If you would like to disable Automatic Shared Memory Management, click Disable.