Skip Headers
Oracle® Database 2 Day + Performance Tuning Guide
10g Release 2 (10.2)

Part Number B28051-01
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
View PDF

9 Identifying High-Load SQL Statements

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

Identifying high-load SQL statements is an important SQL tuning activity that must be performed regularly. The Automatic Database Diagnostic Monitor (ADDM) automates this task by proactively identifying potential high-load SQL statements. Additionally, Enterprise Manager can be used to identify high-load SQL statements that require further investigation. Once the high-load SQL statements have been identified, they can be tuned using the SQL Tuning Advisor and SQL Access Advisor.

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

Identifying High-Load SQL Statements Using ADDM Findings

By default, ADDM runs proactively once every hour, and 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 performance problems are identified by ADDM, they are displayed 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 will give appropriate recommendations, such as running the SQL Tuning Advisor on the SQL statement, and tuning can begin 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 system-wide 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, shown in Figure 9-1, enables you to identify high-load SQL statements for any 5-minute interval.

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, on the Database Performance page, click Top Activity.

The Top Activity page shows a 1-hour timeline 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. 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.

To monitor SQL statements for a longer duration than one hour, switch to Historical view by selecting 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 are categorized into various wait classes, based on their corresponding color as described in the legend on the Top Activity graph. On the Top Activity page, shown in Figure 9-1, SQL statements are displayed for CPU usage (shown in green), concurrency (shown in dark red), and system I/O (shown in light blue) wait classes. To view only SQL statements for a particular wait class, click the block of color on the graph 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 is 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 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 forth. In the example shown in Figure 9-1, the SQL statement with SQL_ID 5mxdwvuf9j3vp is using 91 percent of database activity and should be investigated.

To view details about a SQL statement, in the Top SQL section, click the SQL ID link of the SQL statement. This displays the SQL Details page for the selected SQL statement, as shown in Figure 9-3.

Figure 9-3 SQL Details Page

Description of Figure 9-3 follows
Description of "Figure 9-3 SQL Details Page"

The Text section contains the SQL text for the selected SQL statement, as shown in Figure 9-4.

Figure 9-4 Viewing SQL Text

Description of Figure 9-4 follows
Description of "Figure 9-4 Viewing SQL Text"

If only part of the SQL statement is displayed, an + icon will appear next to the Text heading. To view the SQL text for the entire SQL statement, click the + icon.

If the SQL statement has multiple plans, you can display SQL details for all plans by selecting All in the Plan Hash Value list. Alternatively, you can select a particular plan to display SQL details for that plan only.

The Real Time view shows SQL details for the past hour. To view SQL details for a longer time period, switch to Historical view by selecting Historical from the View Data list. In Historical view, you can view SQL details in the past, up to the duration defined by the AWR retention period.

The SQL Details page also contains four subpages that you can use to perform the following tasks:

If the SQL statement is identified to be a high-load SQL statement after reviewing the SQL details, you can proceed to tune the SQL statement, as described in Chapter 10, "Tuning SQL Statements".

Viewing SQL Statistics

To view statistics for the SQL statement, under Details, click Statistics.

The Statistics subpage, as shown in Figure 9-5, displays statistical information about the SQL statement in the following sections:

Figure 9-5 Viewing SQL Statistics

Description of Figure 9-5 follows
Description of "Figure 9-5 Viewing SQL Statistics"

Viewing SQL Statistics Summary

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

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

Viewing 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 line if the SQL statement is part of PL/SQL program unit

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

Viewing 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 whether 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, 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.

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

Viewing Other SQL Statistics

The Other Statistics section provides additional information about the SQL statement, such as average persistent and runtime 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, as shown in Figure 9-6.

Figure 9-6 Viewing Session Activity

Description of Figure 9-6 follows
Description of "Figure 9-6 Viewing Session Activity"

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

See Also:

Viewing SQL Execution Plan

To view the execution plan for the SQL statement, in the Details section, click Plan.

The Plan subpage contains the execution plan for the SQL statement, as shown in Figure 9-7. Oracle Database compares the cost for the query, with and without query rewrite, and selects the least costly alternative. If a rewrite is necessary, the query rewrite and its cost benefit are displayed in the Explain Rewrite section.

Figure 9-7 Viewing SQL Execution Plan

Description of Figure 9-7 follows
Description of "Figure 9-7 Viewing SQL Execution Plan"

See Also:

Viewing SQL Tuning Information

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

As shown in Figure 9-8, the Tuning Information subpage contains information about the SQL tuning tasks and the SQL profiles recommended by the SQL Tuning Advisor for the SQL statement. The SQL Tuning History section displays a history of tuning activities using the SQL Tuning Advisor or SQL Access Advisor.

Figure 9-8 Viewing SQL Tuning Information

Description of Figure 9-8 follows
Description of "Figure 9-8 Viewing SQL Tuning Information"

See Also: