Skip Headers
Oracle® Database 2 Day + Performance Tuning Guide
11g Release 1 (11.1)

B28275-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

9 Identifying High-Load SQL Statements

High-load SQL statements may consume a disproportionate amount of system resources. These SQL statements often cause a large impact on database performance and must be tuned to optimize their performance and resource consumption. Even when a database itself is properly tuned, inefficient SQL statements can significantly degrade database performance.

Identifying high-load SQL statements is an important SQL tuning activity that you must perform regularly. Automatic Database Diagnostic Monitor (ADDM) automates this task by proactively identifying potential high-load SQL statements. Additionally, you can use Oracle Enterprise Manager (Enterprise Manager) to identify high-load SQL statements that require further investigation. After you have identified the high-load SQL statements, you can tune them with SQL Tuning Advisor and SQL Access Advisor.

This chapter describes how to identify high-load SQL statements and contains the following sections:

Identification of High-Load SQL Statements Using ADDM Findings

By default, ADDM runs proactively once every hour. It analyzes key statistics gathered by the Automatic Workload Repository (AWR) over the last hour to identify any performance problems, including high-load SQL statements. When the system finds performance problems, it displays them as ADDM findings in the Automatic Database Diagnostic Monitor (ADDM) page.

ADDM provides recommendations with each ADDM finding. When a high-load SQL statement is identified, ADDM gives appropriate recommendations, such as running SQL Tuning Advisor on the SQL statement. You can begin tuning as described in Chapter 10, "Tuning SQL Statements".

Identifying High-Load SQL Statements Using Top SQL

ADDM automatically identifies high-load SQL statements that may be causing systemwide performance degradation. Under normal circumstances, manual identification of high-load SQL statements is not necessary. In some cases, however, you may want to monitor SQL statements at a more granular level. The Top SQL section of the Top Activity page in Enterprise Manager enables you to identify high-load SQL statements for any 5-minute interval.

Figure 9-1 shows an example of the Top Activity page.

Figure 9-1 Top Activity Page

Description of Figure 9-1 follows
Description of "Figure 9-1 Top Activity Page"

To access the Top Activity page:

  1. From the Database Home page, click Performance.

    The Performance page appears.

  2. Under Additional Monitoring Links, click Top Activity.

    The Top Activity page appears.

    This page shows a 1-hour time line of the top activity running on the database. SQL statements that are using the highest percentage of database activity are listed under the Top SQL section, and are displayed in 5-minute intervals.

  3. To move the 5-minute interval, drag and drop the shaded box to the time of interest.

    The information contained in the Top SQL section will be automatically updated to reflect the selected time period. Use this page to identify high-load SQL statements that may be causing performance problems.

  4. To monitor SQL statements for a longer duration than one hour, select Historical from the View Data list.

    In Historical view, you can view the top SQL statements for the duration defined by the AWR retention period.

This section contains the following topics:

Viewing SQL Statements by Wait Class

The SQL statements that appear in the Top SQL section of the Top Activity page are categorized into various wait classes, based on their corresponding color as described in the legend on the Top Activity chart.

To view the SQL statements for a particular wait class, click the block of color on the chart for the wait class, or its corresponding wait class in the legend. The Active Sessions Working page for the selected wait class appears, and the Top SQL section will be automatically updated to show only the SQL statements for that wait class.

The example in Figure 9-2 shows the Active Sessions Working page for the CPU Used wait class. Only SQL statements that are consuming the most CPU time are displayed in the Top Working SQL section.

Figure 9-2 Viewing SQL Statement by Wait Class

Description of Figure 9-2 follows
Description of "Figure 9-2 Viewing SQL Statement by Wait Class"

See Also:

Viewing Details of SQL Statements

The Top SQL section of the Top Activity page displays the SQL statements executed within the selected 5-minute interval in descending order based on their resource consumption. The SQL statement at the top of this table represents the most resource-intensive SQL statement during that time period, followed by the second most resource-intensive SQL statement, and so on.

In the example shown in Figure 9-1, the SQL statement with SQL_ID 05b6pvb81dg8b is consuming 89.7 percent of database activity and should be investigated.

To view details of SQL statements:

  1. From the Database Home page, click Performance.

    The Performance page appears.

  2. Under Additional Monitoring Links, click Top Activity.

    The Top Activity page appears.

  3. In the Top SQL section, click the SQL ID link of the SQL statement.

    The SQL Details page for the selected SQL statement appears.

  4. To view SQL details for a longer time period, select Historical from the View Data list.

    You can now view SQL details in the past, up to the duration defined by the AWR retention period.

  5. Review the SQL text for the SQL statement.

    The Text section contains the SQL text for the selected SQL statement.

    Description of sql_details_sql_text.gif follows
    Description of the illustration sql_details_sql_text.gif

    If only part of the SQL statement is displayed, then a plus sign (+) icon will appear next to the Text heading. To view the SQL text for the entire SQL statement, click the plus sign (+) icon.

  6. If the SQL statement has multiple plans, then select All from the Plan Hash Value list to show SQL details for all plans.

    Alternatively, you can select a particular plan to display SQL details for that plan only.

    Description of sql_details_plan_hash.gif follows
    Description of the illustration sql_details_plan_hash.gif

  7. Access the subpages from the SQL Details page to gather more information about the SQL statement, as described in the following sections:

  8. If the SQL statement is a high-load SQL statement, then tune it as described in Chapter 10, "Tuning SQL Statements".

Viewing SQL Statistics

The Statistics subpage of the SQL Details page displays statistical information about the SQL statement.

To view statistics for the SQL statement:

  1. On the SQL Details page, under Details, click Statistics.

    The Statistics subpage appears.

    Description of sql_details_statistics.gif follows
    Description of the illustration sql_details_statistics.gif

  2. View the statistics for the SQL statement, as described in the following sections:

SQL Statistics Summary

The Summary section displays SQL statistics and activity on a chart.

In the Real Time view, the Active Sessions chart shows the average number of active sessions executing the SQL statement in the last hour. If the SQL statement has multiple plans and All is selected in the Plan Hash Value list, then the chart will display each plan in different colors, enabling you to easily spot if the plan changed and whether this may be the cause of the performance degradation. Alternatively, you can select a particular plan to display that plan only.

In the Historical view, the chart shows execution statistics in different dimensions. To view execution statistics, select the desired dimension from the View list:

  • Elapsed time per execution

  • Executions per hour

  • Disk reads per execution

  • Buffer gets per execution

This view enables you to track the response time of the SQL statement using different dimensions and determine if the performance of the SQL statement has degraded based on the dimension selected.

To view statistics of the SQL statement for a particular time interval, click the snapshot icon below the chart. You can also use the arrows to scroll the chart to locate a desired snapshot.

General SQL Statistics

The General section enables you to identify the origin of the SQL statement by listing the following information:

  • Module, if specified using the DBMS_APPLICATION_INFO package

  • Action, if specified using the DBMS_APPLICATION_INFO package

  • Parsing schema, or the database users account that is used to execute the SQL statement

  • PL/SQL source, or the code line if the SQL statement is part of a PL/SQL program unit

Activity by Wait Statistics and Activity by Time Statistics

The Activity by Wait and Activity by Time sections enable you to identify where the SQL statement spent most of its time. The Activity by Wait section contains a graphical representation of how much elapsed time is consumed by CPU and by remaining waits. The Activity by Time section breaks out the total elapsed time into CPU time and wait time by seconds.

Elapsed Time Breakdown Statistics

The Elapsed Time Breakdown section enables you to identify if the SQL statement itself is consuming a lot of time, or if the total elapsed time is inflated due to the amount of time the originating program or application is spending with the PL/SQL or Java engine. If the PL/SQL time or Java time makes up a significant portion of the elapsed time, then there may be minimal benefit gained by tuning the SQL statement. Instead, you should examine the application to determine how the PL/SQL time or Java time can be reduced.

Shared Cursors Statistics and Execution Statistics

The Shared Cursors Statistics and Execution Statistics sections provide information about the efficiency of various stages of the SQL execution process.

Other SQL Statistics

The Other Statistics section provides additional information about the SQL statement, such as average persistent and run-time memory.

Viewing Session Activity

To view session activity for the SQL statement, in the Details section, click Activity.

The Activity subpage contains a graphical representation of the session activity.

Description of sql_details_activity.gif follows
Description of the illustration sql_details_activity.gif

The Activity subpage displays details of various sessions executing the SQL statement. The Active Sessions chart profiles the average number of active sessions over time. You can drag the shaded box to select a 5-minute interval. The Detail for Selected 5 Minute Interval section lists the sessions that executed the SQL statement during the selected 5-minute interval. The multicolored bar in the Activity % column depicts how the database time is divided for each session while executing the SQL statement. To view more details for a particular session, click the link in the SID column of the session you want to view to display the Session Details page.

See Also:

Viewing the SQL Execution Plan

To view the execution plan for the SQL statement, in the Details section, click Plan. The execution plan for a SQL statement is the sequence of operations Oracle Database performs to run the statement.

The Plan subpage displays the execution plan for the SQL statement in a graph view and a table view.

To view the SQL execution in a graph view, click Graph.

Description of sql_details_plan_graph.gif follows
Description of the illustration sql_details_plan_graph.gif

In the graph view, you can display details about the operations shown in the execution plan by selecting the operation in the graph. Details about the selected operations are displayed under Selection Details. If the selected operation is on a particular database object (such as a table), then you can view further details about the database object by clicking the Object link.

To view the SQL execution in a table view, click Table.

Description of sql_details_plan_table.gif follows
Description of the illustration sql_details_plan_table.gif

Oracle Database compares the cost for the query, with and without query rewrite, and selects the least costly alternative. If a rewrite is necessary, then the query rewrite and its cost benefit are displayed in the Explain Rewrite section.

See Also:

Viewing the SQL Tuning Information

To view the tuning information for the SQL statement, in the Details section, click Tuning Information.

The Tuning Information subpage contains information about the SQL tuning tasks and the SQL profiles recommended by SQL Tuning Advisor for the SQL statement.

The SQL Profiles and Outlines section displays SQL profiles and outlines associated with the SQL statement. A SQL profile contains additional statistics of the SQL statement. An outline contains hints for the SQL statement for the query optimizer. Both are used by the query optimizer to generate a better execution plan for the SQL statement.

The SQL Tuning History section displays a history of SQL Tuning Advisor or SQL Access Advisor tasks.

The ADDM Findings for this SQL During Historic Period section displays the number of occurrences of ADDM findings that are associated with the SQL statement.

Description of sql_details_tuning.gif follows
Description of the illustration sql_details_tuning.gif

See Also: