Chapter 10: Monitoring and Tuning the Database

Purpose

This chapter introduces you to some of the monitoring and tuning operations as performed through Enterprise Manager.

Topics

This chapter discusses the following:

Proactively Monitoring your Database
Diagnosing and Resolving Performance Problems
Using the SQL Tuning Advisor
Using the SQL Access Advisor
Using the Memory Advisor

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.

Proactively Monitoring your Database

Back to Topic List

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:

Creating a Tablespace and Table with a Specified Threshold
Triggering a Tablespace Space Usage Alert
Setting Metric Thresholds
Setting up Notifications

Creating a Tablespace and a Table with a Specified Threshold

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.

Move your mouse over this icon to see the image

 

2.

Click the Tablespaces link.

Move your mouse over this icon to see the image

 

3.

Click the Create button.

Move your mouse over this icon to see the image

 

4.

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

Move your mouse over this icon to see the image

 

5.

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

Move your mouse over this icon to see the image

Click OK to create the tablespace.


Move your mouse over this icon to see the image

 

6.

Select your new tablespace, TBSALERT, and click Edit.

Move your mouse over this icon to see the image

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

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

8.

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

Move your mouse over this icon to see the image

 

9.

Click the Tables link in the Database Objects section.

Move your mouse over this icon to see the image

 

10.

Click Create.

Move your mouse over this icon to see the image

 

11.

Accept the default of Standard, Heap Organized and click Continue.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

13.

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

Move your mouse over this icon to see the image

 

14.

Your table has been created. Click the Database breadcrumb.

Move your mouse over this icon to see the image

 

Triggering a Tablespace Space Usage Alert

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

Move your mouse over this icon to see the image

 

3.

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

Move your mouse over this icon to see the image

 

4.

Notice that the TBSALERT tablespace space used percentage has increased.

Move your mouse over this icon to see the image

 

5.

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

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

7.

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

Move your mouse over this icon to see the image

 

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

Move your mouse over this icon to see the image

 

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

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

11.

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

Move your mouse over this icon to see the image

 

12.

Click Edit.

Move your mouse over this icon to see the image

 

13.

Click Segments.

Move your mouse over this icon to see the image

 

14.

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

16.

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

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

Setting Metric Thresholds

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.

Move your mouse over this icon to see the image

 

2.

Click Edit Thresholds.

Move your mouse over this icon to see the image

 

3.

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

Move your mouse over this icon to see the image

 

4.

Click Specify Multiple Thresholds.

Move your mouse over this icon to see the image

 

5.

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

Move your mouse over this icon to see the image

 

6.

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

Move your mouse over this icon to see the image

 

7.

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

Move your mouse over this icon to see the image

 

Setting Up Notification

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.

Move your mouse over this icon to see the image

 

2.

Click Notification Methods.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

4.

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

Move your mouse over this icon to see the image

 

5.

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

Move your mouse over this icon to see the image

 

6.

Enter notify01@oracle.com as the email address and click Apply. Then click Database to return to the Database Home page.

Move your mouse over this icon to see the image

 

Back to Topic List

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.

Creating a Performance Finding
Resolving the Performance Finding using ADDM

Creating a Performance Finding

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/hr
create table emp as select * from employees;
delete emp; 

Move your mouse over this icon to see the image

 

2.

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

sqlplus hr/hr
delete emp;

Move your mouse over this icon to see the image

 

3.

Click Performance in your Enterprise Manager window .

Move your mouse over this icon to see the image

 

4.

Click Application in the Average Active Sessions section.

Move your mouse over this icon to see the image

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

Move your mouse over this icon to see the image

 

5.

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

Move your mouse over this icon to see the image

 

6.

Click Create to create a snapshot.

Move your mouse over this icon to see the image

 

7.

Click Yes to create a Manual Snapshot.

Move your mouse over this icon to see the image

 

8.

A snapshot is now being taken.

 

 

9.

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

Move your mouse over this icon to see the image

 

10.

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

Move your mouse over this icon to see the image

 

Resolving the Performance Finding using ADDM

When a performance finding is encountered, you can use ADDM to resolve it. Perform the following:

1.

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

Move your mouse over this icon to see the image

 

2.

Click the SQL ID in the Rationale section.

Move your mouse over this icon to see the image

 

3.

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

Move your mouse over this icon to see the image

 

4.

Click Performance.

Move your mouse over this icon to see the image

 

5.

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.

Move your mouse over this icon to see the image

 

6.

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

Move your mouse over this icon to see the image

 

7.

Scroll down and select Blocking Sessions under Additional Monitoring Links.

Move your mouse over this icon to see the image

 

8.

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

Move your mouse over this icon to see the image

 

9.

Click Yes to kill the session.

Move your mouse over this icon to see the image

 

10.

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

Move your mouse over this icon to see the image

 

11.

Notice the alert has disappeared.

Move your mouse over this icon to see the image

 

Back to Topic List

 

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.

Move your mouse over this icon to see the image

 

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

Move your mouse over this icon to see the image

 

3.

Execute the start_dinas.sh script as follows:

./start_dinas.sh

Move your mouse over this icon to see the image

 

4.

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

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

6.

Click the Advisor Central link in the Related Links section.

Move your mouse over this icon to see the image

 

7.

Select ADDM in the Advisory Type drop-down menu. Select Last 24 Hours in the Advisor Runs drop-down menu. Click Go.

Move your mouse over this icon to see the image

 

8.

Select the ADDM task corresponding to the problematic time period. Click View Result.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

11.

If there is no Run Advisor Now button, click the SQL text.

Move your mouse over this icon to see the image

 

12.

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

Move your mouse over this icon to see the image

 

13.

The Schedule Advisor page is displayed. Click OK.

Move your mouse over this icon to see the image

 

14.

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

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

16.

The SQL Profile is created.

Move your mouse over this icon to see the image

 

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

Move your mouse over this icon to see the image

./start_dinas.sh

Move your mouse over this icon to see the image

 

18.

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

Move your mouse over this icon to see the image

 

19.

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

./stop_dinas.sh

Move your mouse over this icon to see the image

./cleanup_dina.sh

Move your mouse over this icon to see the image

 

 

Back to Topic List

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.

Preparing the Environment
Using the SQL Cache to Get Recommendations
Reviewing and Implementing the Recommendations

Preparing the Environment

Back to List

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

Move your mouse over this icon to see the image

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; Move your mouse over this icon to see the image

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;

Move your mouse over this icon to see the image

 

Back to List

You will use the SQL Cache you just set up to obtain recommendations from the SQL Access Advisor. Perform the following:

   
1.

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

Move your mouse over this icon to see the image

 

2.

Click the SQL Access Advisor link.

Move your mouse over this icon to see the image

 

3.

Select Use Default Options and click Continue.

Move your mouse over this icon to see the image

 

4.

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

Move your mouse over this icon to see the image

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.

Move your mouse over this icon to see the image

 

5.

Select Both Indexes and Materialized Views and click Next.

Move your mouse over this icon to see the image

 

6.

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

Move your mouse over this icon to see the image

 

7.

At the summary window, click Submit.

Move your mouse over this icon to see the image

 

Back to List

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.

Move your mouse over this icon to see the image

 

2.

The Summary page is displayed. Click Recommendations.

Move your mouse over this icon to see the image

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

Move your mouse over this icon to see the image

 

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.

Move your mouse over this icon to see the image

 

 

4.

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

Move your mouse over this icon to see the image

 

5.

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

Move your mouse over this icon to see the image

 

6.

To implement the recommendations, click Schedule Implementation.

Move your mouse over this icon to see the image

 

7.

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

Move your mouse over this icon to see the image

 

8.

Your implementation job was created and is now executing.

Move your mouse over this icon to see the image

 

9.

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

Move your mouse over this icon to see the image

 

10.

Click Materialized Views in the Materialized Views section.

Move your mouse over this icon to see the image

 

11.

Enter SH in the schema field and click Go.

Move your mouse over this icon to see the image

 

12.

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

Move your mouse over this icon to see the image

 

 

Back to Topic List

In this section, you will proactively manage and automate some of the tasks related to Oracle Instance memory configuration. By automating memory configuration, you have more time to deal with real application or business issues that affect your enterprise.

The Memory Advisor is an intelligent expert system within the Oracle database that proactively determines optimal settings for various SGA and PGA components. When automated, Oracle will automatically adjust the settings for the various pools and caches according to the requirements of the workload.

Enabling Automatic Shared Memory Management
Change the Total SGA Size
Using the PGA Advisor

Enabling Automatic Shared Memory Management

In this section you will verify that automatic shared memory management is enabled. If it is disabled, you can enable it by following the steps outlined below.

1.

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

Move your mouse over this icon to see the image

 

2.

Select Memory Advisor.

Move your mouse over this icon to see the image

 

3.

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

Move your mouse over this icon to see the image

 

4.

Click OK to enable Automatic Shared Memory Management.

Move your mouse over this icon to see the image

 

5.

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

Move your mouse over this icon to see the image

 

Changing the Total SGA Size

To change the total SGA size when in automatic shared memory management mode, you will need to make sure the maximum SGA Size is large enough. Perform the following:

1.

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.

Move your mouse over this icon to see the image

 

2.

Click Yes to confirm the change.

Move your mouse over this icon to see the image

 

3.

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

Move your mouse over this icon to see the image

 

4.

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

Move your mouse over this icon to see the image

 

5.

The database restart process will start. Wait a few minutes and then click Refresh.

Move your mouse over this icon to see the image

 

6. Supply your Enterprise Manager login information. The Database Home page is displayed. Scroll down and click Advisor Central.

Move your mouse over this icon to see the image

 

7. Click Memory Advisor.

Move your mouse over this icon to see the image

 

8. Change the Total SGA Size parameter to 282 MB. Click Apply.

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.

Move your mouse over this icon to see the image

 

9.

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.

Move your mouse over this icon to see the image

 

Using the PGA Advisor

To allocate memory associated with the PGA, perform the following:

1.

Click the PGA tab on the Memory Parameters page.

Move your mouse over this icon to see the image

 

2.

Click Advice.

Move your mouse over this icon to see the image

 

3.

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.

Move your mouse over this icon to see the image

 

4.

Click PGA Memory Usage Details.

Move your mouse over this icon to see the image

 

5.

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.

Move your mouse over this icon to see the image

 

Move your mouse over this icon to hide all screenshot