| Oracle® Database 2 Day + Performance Tuning Guide 11g Release 1 (11.1) Part Number B28275-01 |
|
|
View PDF |
| Oracle® Database 2 Day + Performance Tuning Guide 11g Release 1 (11.1) Part Number B28275-01 |
|
|
View PDF |
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:
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".
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.
To access the Top Activity page:
From the Database Home page, click Performance.
The Performance page appears.
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.
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, 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:
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
See Also:
"Monitoring User Activity" for information about using the Active Sessions Working page
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:
From the Database Home page, click Performance.
The Performance page appears.
Under Additional Monitoring Links, click Top Activity.
In the Top SQL section, click the SQL ID link of the SQL statement.
The SQL Details page for the selected SQL statement appears.
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.
Review the SQL text for the SQL statement.
The Text section contains the SQL text for the selected SQL statement.
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.
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.
Access the subpages from the SQL Details page to gather more information about the SQL statement, as described in the following sections:
If the SQL statement is a high-load SQL statement, then tune it as described in Chapter 10, "Tuning SQL Statements".
The Statistics subpage of the SQL Details page displays statistical information about the SQL statement.
To view statistics for the SQL statement:
On the SQL Details page, under Details, click Statistics.
The Statistics subpage appears.
View the statistics for the SQL statement, as described in the following sections:
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 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.
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
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 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.
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.
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:
"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 execution plan for a SQL statement is the sequence of operations Oracle 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.
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.
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:
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.
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.
See Also:
Chapter 10, "Tuning SQL Statements" for information about SQL Tuning Advisor and SQL profiles
Chapter 11, "Optimizing Data Access Paths" for information about SQL Access Advisor
