| Oracle® Database 2 Day + Performance Tuning Guide 10g Release 2 (10.2) Part Number B28051-01 |
|
|
View PDF |
| Oracle® Database 2 Day + Performance Tuning Guide 10g Release 2 (10.2) Part Number B28051-01 |
|
|
View PDF |
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:
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".
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.
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:
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
See Also:
"Monitoring User Activity" for information about using the Active Sessions Working page
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.
The Text section contains the SQL text for the selected SQL statement, as shown in Figure 9-4.
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".
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:
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.
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
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.
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.
The Shared Cursors Statistics and Execution Statistics sections provide information about the efficiency of various stages of the SQL execution process.
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.
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:
"Monitoring Top Sessions" for information about monitoring session activity and details
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.
See Also:
Chapter 10, "Tuning SQL Statements" for information about execution plan and the query optimizer
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
See Also:
Chapter 10, "Tuning SQL Statements" for information about the SQL Tuning Advisor and SQL profiles
Chapter 11, "Optimizing Data Access Paths" for information about the SQL Access Advisor
