The Database Home page enables you to monitor the state and workload of your database. It provides a central place for general database state information and is updated periodically.

Description of the illustration exp_db_home_page2.gif

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. (Optional) Click the Refresh icon to the right of the selected refresh interval for the Auto Refresh list to update the information displayed.

   The time that the Database Home page was last collected from the database appears near the top right corner of the page.

   By default, the Database Home page automatically refreshes every 60 seconds. You can prevent automatic refresh by selecting Off in the Auto Refresh list at the top right-hand corner of the page. You must then click the Refresh icon to view the latest information.

3. Get a quick overview of the database state in the Status section, which includes the following information:

   • Up Time

     Information about how long the database has been up

   • Type

     The database type. The type can be a single instance database (CDB or non-CDB) or an Oracle RAC database (or cluster database).

     If the database type is a CDB, the next line will identify the database as a CDB and specify the number of PDBs in the CDB. The CDB (n PDBs) line is a link to the Containers page, which shows a list of containers in the CDB (not including PDB$SEED), as well as status, performance, and resource information about the containers.

   • Version

     The database version number

   • Database name

     The database name

   • Instance name

     The name of the database instance

   • Platform name

     The platform on which the database is running

   • Host name

     The name of the host system on which the database is running

   • Thread

     The redo log threads for the database

   • Archiver

     The status of the archiver process

4. View active session information in the Performance section. The Performance section shows trend information for the past hour.

   The Activity Class chart shows the average number of database sessions active for the past hour. The chart shows the type of activity for each session (on CPU, waiting for I/O, or waiting for another resource).

   The Services chart shows the average number of database sessions active for the past hour for database services.

   For Oracle RAC, the Activity Class chart shows activity aggregated across all instances in the cluster. Also, an Instances chart appears for Oracle RAC that shows Average Active Sessions per instance.

5. View resource utilization for the latest data point (the last minute) in the Resources section. The Resources section includes the following information:

   • Host CPU chart

     This chart shows the percentage of CPU time used by the database instance and other processes during the last minute. Place your cursor over the instance data to see the percentage of CPU used by foreground and background instance processes.

     If other processes are taking up most of your CPU time, then this indicates that some other application running on the database host computer could be causing performance problems.

   • Active Sessions chart

     This chart shows the average number of active sessions during the last minute, broken out by wait, user I/O, and CPU.

     • Waits

       This is the value for all wait classes combined, excluding user I/O and idle wait events. Wait classes are groupings of wait events based on the type of wait.

       Go to the Performance Hub and click the Activity tab to view more information about waits.

     • User I/O

       This is the average number of active sessions waiting for user I/O. User I/O means that the workload originating from the user causes the database to read data from disk or write data to disk.

       Go to the Performance Hub and click the Summary tab to view more information about I/O.

     • CPU

       This is the average active sessions using CPU.

       Go to the Performance Hub and click the Summary tab to view more information about CPU usage.

   • Memory (GB)

     This chart shows the current memory utilization (as of the latest refresh time) broken out by the database shared pool, java pool, buffer cache, PGA, and other SGA components.

   • Data Storage (GB)

     This chart shows the current space usage (as of the latest refresh time) broken out by user data, database log files, undo tablespaces, and temporary, SYSAUX, and SYSTEM tablespaces.

6. View SQL activity in the SQL Monitor section:

   The table in this section displays information about monitored SQL statement executions. If there is a green spinning icon in the Status column, then the monitored statement is still running. If there is a check mark in the Status column, then the statement has completed its execution.

   SQL statements are monitored only if they have consumed at least 5 seconds of CPU or I/O time.

   For each SQL statement, the table provides information in the Status, Duration, SQL ID, Session ID, Parallel, Database Time, and SQL Text columns.

   Click a SQL ID to display the SQL Details page with more information about that SQL statement.

7. The Incidents - Last 24 Hours section displays a table that provides information about database incidents that have occurred in the past 24 hours. The table has the Instance, Time, Incident, Problem, and Error columns.

   An incident is an occurrence of a critical error in the database. Each incident in the Incidents - Last 24 Hours table is recorded in the Automatic Diagnostic Repository (ADR), a directory structure located outside the database, which is available for problem diagnosis even when the database is down. You can investigate critical errors using the ADR Command Interpreter (ADRCI) utility.

8. The Running Jobs section displays a table that shows database jobs that are currently running. The table has the Instance, Owner, Name, Elapsed, and Started columns.

   See Also:

   • Oracle Database SQL Tuning Guide for more information about the Enterprise Manager pages for monitored SQL executions

   • "About the SQL Tuning Advisor" for an introduction to SQL Tuning Advisor

   • "About the Automatic SQL Tuning Advisor" for an introduction to Automatic SQL Tuning Advisor

   • "Running the SQL Tuning Advisor" for an example of tuning a SQL statement using SQL Tuning Advisor

   • Oracle Database Administrator’s Guide for information about investigating problems using ADRCI.

The Performance Hub allows you to view all the performance data available for a specified time period. Once a time period is selected, the performance information is collected and presented based on performance subject areas.

This section includes the following topic:

• Specifying the Time Period for Which to Display Statistics

When real-time data is selected, more granular data is presented (because data points are available every minute).

When historical data is selected, more detailed data (broken down by different metrics) is presented, but the data points are averaged out to the Automatic Workload Repository (AWR) interval (usually an hour).

Different tabs are available in the Performance Hub, depending on whether real-time or historical data is selected for the time period.

The following table describes the Performance Hub tabs, and indicates whether the tab is available when real-time data is selected or historical data is selected, or both.

Performance Hub Tab Name	Description	Available When
Summary	The Summary tab provides an overall view of the performance of the system for the specified time period. When real-time data for the last hour is displayed in the Performance Hub page, this tab shows a summary of running processes, memory allocation, database activity by category, and I/O data during the last hour. When historical data is displayed in the Performance Hub page, this tab shows a summary of average active session waits by category, load profile per second, active session activity, host CPU usage by the database instance and other processes, I/O read and write requests per second, and memory usage during the selected time period.	Real-time data or historical data is selected in the Select Time Period field for a non-CDB, CDB, or PDB
RAC	The RAC tab appears only when EM Express is being used with an Oracle RAC database (or cluster database). When real-time data is selected, this tab shows global cache activity information and a breakdown of activity (average active sessions) and resource usage (CPU, I/O, memory) per instance. When historical data is selected, this tab shows global cache activity information and a breakdown of activity (average active sessions) and resource usage (CPU, I/O, memory) per instance during the selected time period.	Real-time data or historical data is selected in the Select Time Period field
Activity	The Activity tab shows Active Session History (ASH) analytics. It allows detailed drilldown into average active sessions for ASH over the selected time period. This tab enables you to select an average active sessions dimension and view the top activity for that dimension for a selected time period. For example, you can view the SQL statements and user sessions that had the top average active sessions activity for the selected time period.	Real-time data or historical data is selected in the Select Time Period field for a non-CDB, CDB, or PDB
Workload	The workload profile charts show the pattern of user calls, parse calls, Redo Size and SQL*Net over the last 60 minutes in real-time mode. The Sessions chart show the logon rate, current logons and open cursors. Clicking a SQL_ID displays the SQL Details page with more information about that SQL statement.	Real-time data or historical data is selected in the Select Time Period field for a non-CDB, CDB, or PDB
Monitored SQL	This tab enables you to view information about monitored SQL statements that were executing or that completed during the selected time period. The table displays information about monitored SQL statement executions. If there is a green spinning icon in the Status column, then the monitored statement did not complete during the selected time period. A red cross indicates that the SQL did not complete either due to an error or due to the session getting terminated. If there is a check mark in the Status column, then the statement completed its execution during the selected time period. SQL statements are monitored only if they have consumed at least 5 seconds of CPU or I/O time. You can view information such as the status of a statement, its duration, its type (SQL, PL/SQL, or DBOP), its SQL ID, its SQL plan hash, the user who issued it, whether it executed as a serial or parallel statement, the time the database spent performing CPU activity, I/O, or other activity for the statement, the read and write requests and bytes associated with the statement, and the start and end time for the statement. Click a SQL ID to display the SQL Details page with more information about that SQL statement.	Real-time data or historical data is selected in the Select Time Period field for a non-CDB, CDB, or PDB
ADDM	The ADDM tab enables you to view performance findings and recommendations that have been found by Automatic Database Diagnostics Monitor (ADDM) for tasks performed in the database during the selected time period. See "Performance Self-Diagnostics: Automatic Database Diagnostic Monitor" for more information about ADDM features.	Real-time data or historical data is selected in the Select Time Period field for a non-CDB or CDB. This tab is available for a PDB only when a CDB administrator logs into a CDB and navigates (drills down) to the PDB. The tab is not available when a PDB administrator is logged directly into the PDB.
Database Time	The Database Time tab enables you to view wait events by category for various metrics, and to view time statistics for various metrics for the selected time period.	Historical data is selected in the Select Time Period field for a non-CDB, CDB, or PDB.
Resources	The Resources tab enables you to view operating system resource usage statistics, I/O resource usage statistics, and memory usage statistics for the selected time period.	Historical data is selected in the Select Time Period field for a non-CDB, CDB, or PDB.
System Statistics	The System Statistics tab enables you to view database statistics by value, per transaction, or per second for the selected time period.	Historical data is selected in the Select Time Period field for a non-CDB, CDB, or PDB.
Containers	The Containers tab enables you to view open time, active sessions, memory used, I/O requests, and I/O throughput information for the PDBs in the CDB.	Real-time data or historical data is selected in the Select Time Period field for a CDB.

The following figure shows the Performance Hub when Real Time - Last Hour data is selected.

Description of the illustration perf_hub_real_time.gif

The following figure shows the Performance Hub when historical data is selected.

Description of the illustration perf_hub_summary_hist.gif

1. At the top of the Database Home page, from the Performance menu, select Performance Hub.

   The Performance Hub page appears, with the Summary tab displayed. By default, real time data for the last hour appears in the Performance Hub.

   You can select a different time period in the Select Time Period field if you would like to view historical data in the Performance Hub instead of real-time data.

   In the figure above, Historical - All is selected in the Select Time Period field.

2. The time picker appears below the Select Time Period field.

   The shaded block area in the time picker identifies the period of time for which performance statistics are currently being displayed in the Performance Hub. This is a subset of the period of time you selected in the Select Time Period field.

   When historical data is displayed in the Performance Hub, you can increase or decrease the size of the shaded block area by clicking and dragging the user control on either end of the shaded block area.

   The shaded block area is the time period for which statistics are displayed on all of the Performance Hub tabs, not just on the currently selected tab.

3. Click any of the tabs that appear in the Performance Hub to view the performance data on the tab.
4. Click the PerfHub Report button to generate a Performance Hub active report, which will include the contents of the Performance Hub tabs in an HTML file. After you click PerfHub Report, you are prompted to choose one of these report types for the Performance Hub active report:

   • Basic: The basic information for all the tabs is saved in the report.

   • Typical: All the information for the basic report is saved. Also, the SQL Monitor information for the top SQL in the Monitored SQL tab is saved, and ADDM reports are saved.

   • All: All the information for the basic report is saved. Also, the SQL Monitor information for all of the SQL in the Monitored SQL tab is saved (not just for the top SQL), and all the detailed reports in all the tabs are saved.

   You are then prompted for a location and file name for the active report, and the report is generated in that file and location. You use a web browser to view the report and navigate the Performance Hub tabs in the report.

5. When historical data is selected in the Performance Hub, you can click the AWR Report button to generate an AWR report for the selected time period.

   You are prompted for a location and file name for the AWR report, and the report is generated in that file and location. You use a web browser to view the report.

   See Also:

   Oracle Database Performance Tuning Guide for more information about AWR

Oracle Database includes a self-diagnostic engine called Automatic Database Diagnostic Monitor (ADDM). ADDM makes it possible for Oracle Database to diagnose its own performance and determine how identified problems can be resolved.

To facilitate automatic performance diagnosis using ADDM, Oracle Database periodically collects snapshots of the database state and workload. Snapshots are sets of historical data for specific time periods that are used for performance comparisons by ADDM. Snapshots provide a statistical summary of the state of the system at a point in time. These snapshots are stored in Automatic Workload Repository (AWR), residing in the SYSAUX tablespace. The snapshots are stored in this repository for a set time (8 days by default) before they are purged to make room for new snapshots.

ADDM analyzes data to determine the major problems in the system, and may recommend solutions and quantify expected benefits. ADDM analysis results are represented as a set of findings.

EM Express provides two types of ADDM findings.

ADDM analysis results consist of a description of each finding and a recommended action. You can view a summary of findings and their impacts on the system.

1. In EM Express, from the Performance menu, select Performance Hub.

   The Performance Hub page appears in the list of tabs on the Performance Hub page.

2. In the Select Time Period field, select one of the time period values to view performance data for that time period. The title of the page changes to indicate the selected time period.
3. Click the ADDM tab.

   The ADDM tasks for the selected time period appear in the ADDM Tasks table on the ADDM tab.

   
   Description of the illustration perf_hub_period_addm.gif
4. Click the link in the Task Name column for a ADDM task to view more information about that task.

   See Also:

   "Performance Self-Diagnostics: Automatic Database Diagnostic Monitor"

You can act upon the recommendations that accompany ADDM performance findings.

1. In the ADDM Tasks table on the ADDM tab of the Performance Hub page, click the link in the Task Name column to view the performance findings for that task.

   The Automatic Database Diagnostic Monitor (ADDM) page appears.

   
   Description of the illustration perf_hub_paddm_perf.gif

   The performance findings for the ADDM task and the performance impact of each finding (%) are listed in the Findings table.

2. Click the link in the Finding column for a particular finding to view the finding and recommendations (if any) associated with it.

   The Performance Finding Details page appears.

   
   Description of the illustration perf_paddm_findings_det.gif

   The performance findings table lists the performance findings for the ADDM task. For each finding, the table shows the finding category, recommended action, rationale for the recommendations, and the expected benefit of implementing the recommendation.

   When you select a finding that has a recommendation that you can implement using ADDM, the Implement button becomes available for that finding.

3. For a finding that has a recommendation that you want to implement using ADDM, select the finding and click Implement.

   For this example, the Schedule SQL Tuning Advisor page appears, on which you are prompted for the information necessary to run the SQL Tuning Advisor on the selected SQL statement.

Real-Time ADDM results consist of a description of each finding and recommended actions for some findings at the point in time when the Real-Time ADDM report was generated. You can view a summary of findings and their impacts on the system.

1. In EM Express, from the Performance menu, select Performance Hub.

   The Performance Hub page appears in the list of tabs on the Performance Hub page.

2. In the Select Time Period field, select one of the time period values to view performance data for that time period. The title of the page changes to indicate the selected time period.
3. Click the ADDM tab.

   If there are Real-Time ADDM reports for the selected time period, the reports appear in the Real-Time ADDM Reports table at the bottom of the ADDM tab.

   
   Description of the illustration perf_hub_rt_addm_report.gif
4. To view a particular report in the table, click the link for the report in the Report Name field, or select the table row and then click Show Performance Report.

You can view the findings in a Real-Time ADDM report.

1. To view a particular report in the Real-Time ADDM Reports table, click the link for the report in the Report Name field, or select the table row and then click Show Performance Report.

   The Real-Time ADDM Report page appears.

   
   Description of the illustration perf_hub_rt_addm_report_det.gif
2. In the Findings table, the findings for the report, and the impact of each finding are displayed. When you select a finding in the Findings table, the recommendation details and finding details for that finding are displayed in the Recommendation Details and Finding Details sections below the Findings table.

Advisors are provided to help you improve database performance. These advisors include Automatic Database Diagnostic Monitor (ADDM), SQL advisors, and memory advisors. For example, the SGA Advisor graphically displays the impact on performance of changing the size of the System Global Area (SGA).

You can run a performance advisor when faced with the following situations:

• You want to resolve a problem in a specific area, for example, to determine why a given SQL statement is consuming 50 percent of CPU time and what to do to reduce its resource consumption.You can use the SQL Tuning Advisor.

• You are planning to add memory to your system. You can use the Memory Advisor to determine the database performance impact of increasing your SGA or PGA (Program Global Area).

You can also invoke some of the advisors from the Performance Hub page, or through recommendations from ADDM.

Table 10-2 describes the performance advisors.

You use the SQL Tuning Advisor to tune a single SQL statement or multiple SQL statements. Typically, you run the SQL Tuning Advisor in response to an ADDM performance finding that recommends its use. You can also run it periodically on the most resource-intensive SQL statements, and on a SQL workload.

When tuning multiple SQL statements, the SQL Tuning Advisor does not recognize interdependencies between the SQL statements. It solves SQL performance problems by identifying problems with individual SQL statements, such as a poorly performing optimizer plan or the mistaken use of certain SQL structures.

You can run the SQL Tuning Advisor against the following sources:

• Activity—The most resource-intensive SQL statements executed during the last hour that appear on the Activity tab of the Performance Hub that might have caused recent performance problems.

• Historical SQL—A SQL statement from the last day, week, or month that appears on the Activity tab of the Performance Hub when one of the historical settings is selected in the Select Time Period field. Use this option for proactive tuning of SQL statements.

• Historical SQL from ADDM—A resource-intensive SQL statement from an ADDM task that you discover when analyzing a task on the ADDM tab of the Performance Hub.

• SQL statement in SQL Tuning Advisor—A resource-intensive SQL statement that appears as a tuning task in SQL Tuning Advisor.

• SQL tuning sets (STS)—A set of SQL statements you provide. An STS can be created from SQL statements captured by AWR snapshots or from a SQL workload.

The Automatic SQL Tuning Advisor can be configured to automatically implement SQL profile recommendations. A SQL profile contains additional SQL statistics that are specific to the SQL statement and enable the query optimizer to generate a significantly better execution plan at run time. If you enable automatic implementation, then the advisor creates SQL profiles for only those SQL statements where the performance improvement would be at least threefold. Other types of recommendations, such as the creation of new indexes, refreshing optimizer statistics, or restructuring SQL, can only be implemented manually. DML statements are not considered for tuning by the Automatic SQL Tuning Advisor.

You can view a summary of the results of automatic SQL tuning, and a detailed report about recommendations made for all SQL statements that the SQL Tuning Advisor has processed. You can then implement selected recommendations. You can also view the recommendations that were automatically implemented.

You can disable the Automatic SQL Tuning Advisor, if desired.

The following are some configuration tasks that you might want to perform for the Automatic SQL Tuning Advisor:

1. Log into EM Express as a user who has the EM_EXPRESS_ALL role.

   See "Granting Access to EM Express for Nonadministrative Users" for more information about the EM_EXPRESS_ALL role.

2. In EM Express, from the Performance menu, select SQL Tuning Advisor.

   The SQL Tuning Advisor page appears, with the Automatic tab showing.

   The SQL tuning task that appears on the Automatic tab is the SYS_AUTO_SQL_TUNING_TASK. This tuning task is created daily by the Automatic SQL Tuning Advisor. The task includes any high-load SQL queries for which the Automatic SQL Tuning Advisor has generated tuning recommendations.

   
   Description of the illustration auto_sqlt_advisor.gif
3. Click the Configuration button.

   The SQL Tuning Settings dialog box appears.

   
   Description of the illustration sql_tuning_settings_dibox.gif
4. (Optional) To disable the Automatic SQL Tuning Advisor, remove the check mark for the the Automatic SQL Tuning option.

   Note:

   If you disable the Automatic SQL Tuning option, none of the other options in the SQL Tuning Settings dialog box have any effect.

5. (Optional) In the Time Limit per Statement (seconds) field, enter the maximum time that SQL Tuning Advisor should take to tune any single SQL statement (in seconds).
6. (Optional) In the Automatic Implementation of SQL Profiles field, enter a check mark to enable the automatic implementation of SQL profiles, or remove the check mark to disable the automatic implementation of SQL profiles.

   Note:

   If you disable the Automatic Implementation of SQL Profiles option, the settings for the Maximum SQL Profiles Implemented per Execution and Maximum SQL Profiles Implemented (Overall) options have no effect.

7. (Optional) In the Maximum SQL Profiles Implemented per Execution field, enter the total number of SQL profiles that can be implemented during a single daily run of the SQL Tuning Advisor. The default value is 20.
8. (Optional) In the Maximum SQL Profiles Implemented (Overall) field, enter the total number of SQL profiles that can be implemented overall. The default value is 10000.
9. Click OK.

   A confirmation page appears.

You can track the activities of the Automatic SQL Tuning Advisor with EM Express.

1. In EM Express, from the Performance menu, choose SQL Tuning Advisor.

   The SQL Tuning Advisor page appears, with the Automatic tab showing.

   The SQL tuning task that appears on the Automatic tab is the SYS_AUTO_SQL_TUNING_TASK. This tuning task is created daily by the Automatic SQL Tuning Advisor. The task includes any high-load SQL queries for which the Automatic SQL Tuning Advisor has generated tuning recommendations.

   
   Description of the illustration auto_sqlt_advisor.gif

   If you configured Automatic SQL Tuning Advisor to automatically implement SQL profile recommendations, then the SQL Profile Potential DB Time Benefit chart on the Automatic tab of the SQL Tuning Advisor page will include an Implemented bar. Click the Implemented bar to see all the SQL profiles that were automatically implemented.

2. In the Top SQL Statements table, select a row that includes a SQL statement for which you want to view tuning recommendations, and then click View Details.

   In this example, the SELECT statement with a SQL ID of ffy1dpzphwuud was selected and View Details was clicked.

   The Tuning Result for SQL page appears, which shows a summary of the tuning recommendations for the selected SQL statement.

   
   Description of the illustration sqlta_auto_start.gif
3. The Select Recommendation section advises that only one recommendation on the page should be implemented.

   To implement a recommendation, select it in the table and click Implement. You will be prompted to provide the necessary information to implement the recommendation.

   To help you decide which (if any) of the recommendations to implement, you may want to view more details about each of the recommendations.

   To view more details about a recommendation, select it in the table, then click View Details. In this example, the SQL Profile recommendation is selected.

   The Recommendation Details page appears.

4. The top section of this page describes the performance recommendation, and the section is named after the type of recommendation. Some possible names for this section are "Stale or Missing Statistics," "Restructure SQL," and "SQL Profile." This section provides an overview of the recommendation.
   
   Description of the illustration sqlta_auto_1_stmt_recomd.gif

   The Compare Explain Plans section at the bottom of the page includes tabs that that enable you to view one or more execution plans for the selected statement. The four tabs that can appear are the Original Plan, Original Plan with Adjusted Cost, Plan Using SQL Profile, and Alternative Plan tabs. The Graphical and Tabular buttons enable you to display an execution plan in graphical or tabular format. In this example, the execution plan is displayed in tabular format.

   For recommendations that do not include a potentially better execution plan, only the Original Plan tab appears, and the operations for the original plan are shown on the tab.

   When you click the Original Plan with Adjusted Cost tab, the execution plan steps are the same as the Original Plan steps, but the Original Plan with Adjusted Cost steps have different costs for the steps (as shown in the Operation Cost column).

   If you click the Plan Using SQL Profile tab, the steps are different than the Original Plan steps, and the steps have different costs (as shown in the Operation Cost column).

   The Alternative Plan button appears when the execution history for the original plan cannot be found. In this case, if you know that the alternative plan suggested by SQL Tuning Advisor is better than the original plan, you can create a SQL plan baseline for the alternative plan so that the Oracle optimizer will pick the alternative plan for the statement in the future.

   Click the Implement button at the top of the Recommendation Details page to implement a recommendation.

1. In EM Express, from the Performance menu, choose Performance Hub.

   The Performance Hub page appears.

2. In the Select Time Period field, select the desired time period.

   In this example, Historical - All has been selected in the Select Time Period field.

3. Select Activity.

   The Activity tab appears.

4. In the table at the bottom of the Activity tab, select the row that includes the SQL statement that you want to tune, and then click the Tune SQL button.

   In this example, the SQL statement in the sixth row of the table is selected.

   
   Description of the illustration row6_sqlta.gif
5. The Schedule SQL Tuning Advisor wizard appears, with the Task Information page showing.
   
   Description of the illustration row6_sqlta_sched1.gif

   On the Task Information page, you can accept the tuning task name generated by the system, or enter a name of your choosing for the tuning task that will be created for the selected SQL statement. You also have the option of entering a description for the tuning task.

   Click the right arrow button.

6. The Scope page appears. Specify the total time SQL Tuning Advisor should spend analyzing the statement (the default value is Unlimited), and the scope of the analysis (Comprehensive or Limited).
   
   Description of the illustration row4_sqlta_sched2.gif

   Click the right arrow button.

7. The Schedule page appears. On this page, you can schedule SQL Tuning Advisor to analyze the statement immediately or later. If you choose to have SQL Tuning Advisor analyze the statement later, specify the time that you want SQL Tuning Advisor to analyze the statement.
   
   Description of the illustration row4_sqlta_sched3.gif

   Click OK to begin the tuning task creation by SQL Tuning Advisor for the selected SQL statement.

8. When the SQL Tuning Advisor finishes analyzing the SQL statement, the Completed (check mark) icon appears in the Status column on the SQL Tuning Advisor page.

   Select the row that includes the SQL statement that SQL Tuning Advisor has finished analyzing, and then click View Result to see the recommendations the SQL Tuning Advisor has for this SQL statement. In this example, the first row is selected.

   
   Description of the illustration row6_sqlta_manual_tab.gif

   The Tuning Result for SQL: SQLID page appears, which shows a summary of the tuning recommendations for the selected SQL statement.

9. The Select Recommendation section at the bottom of the page shows the recommendations for tuning the SQL statement.

   If there are multiple recommendations on the page, only one of them should be implemented.

   To implement a recommendation, select it in the table and click Implement. You will be prompted to provide the necessary information to implement the recommendation.

   To help you decide which (if any) of the recommendations to implement, you may want to view more details about each of the recommendations.

   Note:

   Some SQL statements may have a SQL Profile recommendation, which means that SQL Tuning Advisor recommends creating a SQL profile for the statement. When a SQL profile is recommended for a statement, the Select Recommendation section includes a recommendation with a value of SQL Profile in the Type column, as well as a Validate with SPA button. To validate the impact of implementing the SQL profile for a statement, select the SQL Profile recommendation and click Validate with SPA.

   To view more details about a recommendation, select it in the table, then click View Details.

   In this example, the Some alternative execution plans for this statement were found by searching the system's real-time and historical performance data recommendation is selected.

   
   Description of the illustration row6_sqlta_tun_results.gif

   The Recommendation Details page appears.

10. The top section of this page describes the performance recommendation, and the section is named after the type of recommendation. Some possible names for this section are "Alternative Plan(s)," "Stale or Missing Statistics," "Restructure SQL," and "SQL Profile." This section provides an overview of the recommendation.
    
    Description of the illustration row6_sqlta_tun_recs.gif

    The Compare Explain Plans section at the bottom of the page includes one or more tabs that that enable you to view one or more execution plans for the selected statement. The four tabs that can appear are the Original Plan, Original Plan with Adjusted Cost, Plan Using SQL Profile, and Alternative Plan tabs. The Tabular and Graphical button enable you to display an execution plan in tabular or graphical format. In this example, the execution plan is displayed in tabular format.

    For recommendations that do not include a potentially better execution plan, only the Original Plan tab appears, and the operations for the original plan are shown on the Original Plan subpage.

    On the Original Plan with Adjusted Cost subpage, the execution plan steps are the same as the Original Plan steps, but the Original Plan with Adjusted Cost steps have different costs for the steps (as shown in the Operation Cost column).

    On the Plan Using SQL Profile subpage, the steps are different than the Original Plan steps, and the steps have different costs (as shown in the Operation Cost column).

    The Alternative Plan subpage is available when the execution history for the original plan cannot be found. In this case, if you know that the alternative plan suggested by SQL Tuning Advisor is better than the original plan, you can click the Create SQL Plan Baseline button to create a SQL plan baseline for the alternative plan so that the Oracle optimizer will pick the alternative plan for the statement in the future.

    To implement a recommendation, click the Back button in your browser, and implement the recommendation on the Tuning Result page.

This section includes information about memory advisors.

• About the Memory Advisors