2 Oracle Database Performance Method

Performance improvement is an iterative process. Removing the first bottleneck might not lead to performance improvement immediately, because another bottleneck might be revealed that has an even greater performance impact on the system. For this reason, the Oracle performance method is iterative. Accurately diagnosing the performance problem is the first step towards ensuring that the changes you make to the system will result in improved performance.

Performance problems generally result from a lack of throughput, unacceptable user or job response time, or both. The problem might be localized to specific application modules, or it might span the entire system. Before looking at any database or operating system statistics, it is crucial to get feedback from the most important components of the system: the users of the system and the people ultimately paying for the application. Getting feedback from users makes determining the performance goal easier, and improved performance can be measured in terms of real business goals, rather than system statistics.

The Oracle performance method can be applied until performance goals are met or deemed impractical. This process is iterative, and it is likely that some investigations will be made that have little impact on the performance of the system. It takes time and experience to accurately pinpoint critical bottlenecks in a timely manner. The Automatic Database Diagnostic Monitor (ADDM) implements the Oracle performance method and analyzes statistics to provide automatic diagnosis of major performance problems. Using ADDM can significantly shorten the time required to improve the performance of a system, and it is the method used in this guide.

This chapter discusses the Oracle Database performance method and contains the following sections:

Gathering Database Statistics Using the Automatic Workload Repository

Database statistics provide information about the type of load on the database and the internal and external resources used by the database. To accurately diagnose performance problems with the database using ADDM, statistics must be available.

Oracle Database generates many types of cumulative statistics for the system, sessions, and individual SQL statements. Oracle Database also tracks cumulative statistics about segments and services. The Automatic Workload Repository (AWR) automates database statistics gathering by collecting, processing, and maintaining performance statistics for database problem detection and self-tuning purposes. By default, this process is repeated every hour, and the result is called an AWR snapshot. The delta values captured by the snapshot represent the changes for each statistic over the time period. Statistics gathered by the AWR are queried from memory. The gathered data can be displayed in both reports and views.

Gathering database statistics using the AWR is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL parameter should be set to TYPICAL or ALL to enable statistics gathering by the AWR. The default setting is TYPICAL. Setting the STATISTICS_LEVEL parameter to BASIC disables many Oracle Database features, including the AWR, and is not recommended. For information about the STATISTICS_LEVEL initialization parameter, see Oracle Database Reference.

The database statistics collected and processed by AWR include:

Time Model Statistics

Time model statistics are statistics that measure the time spent in the database by operation type. The most important time model statistic is database time, or DB time. Database time represents the total time spent in database calls, and is an indicator of the total instance workload. As shown in Figure 2-1, database time makes up a portion of an application's overall user response time.

Figure 2-1 DB Time in Overall User Response Time

Description of Figure 2-1 follows
Description of "Figure 2-1 DB Time in Overall User Response Time"

Database time is calculated by aggregating the CPU time and wait time of all user sessions not waiting for idle wait events (nonidle user sessions). For example, a user session may involve an online transaction made at a bookseller's Web site consisting of the following actions, as shown in Figure 2-2:

Figure 2-2 DB Time in User Transaction

Description of Figure 2-2 follows
Description of "Figure 2-2 DB Time in User Transaction"

  1. Query for novels by author

    The user performs a search for novels by a particular author. This causes the application to perform a database query for novels by the author.

  2. Browse results of query

    The user browses the list of novels by the author that are returned and accesses additional details, such as user reviews and inventory status, about the novels. This causes the application to perform additional database queries.

  3. Add item to cart

    After browsing details on the novels, the user decides to add one of the novels to the shopping cart. This causes the application to make a database call to update the shopping cart.

  4. Checkout

    The user completes the transaction by checking out using the address and payment information previously saved at the bookseller's Web site from a previous purchase. This causes the application to perform various database operations to retrieve the user's information, add a new order, update the inventory, and generate an E-mail confirmation.

For each of these actions, the user makes a request to the database, as represented by the down arrow in Figure 2-2. The CPU time spent by the database processing the request and the wait time spent waiting for the database are considered DB time, as represented by the shaded areas. Once the request is completed, the results are returned to the user, as represented by the up arrow. The space between the up and down arrows represents the total user response time for processing the request, which contains other components besides DB time, as illustrated in Figure 2-1.

The objective of database tuning is to reduce the time that users spend performing actions on the database, or reducing database time. By doing so, the overall response time of user transactions on the application can be improved.

Wait Event Statistics

Wait events are statistics that are incremented by a session to indicate that it had to wait for an event to complete before being able to continue processing. When a session has to wait while processing a user request, the AWR records the wait using one of a set of predefined wait events that are then grouped into wait classes. Wait event data reveals various symptoms of problems that might be impacting performance, such as latch contention, buffer contention, and I/O contention.

Session and System Statistics

A large number of cumulative database statistics are available on a system and session level. Some of these statistics are collected by the AWR.

Active Session History Statistics

The Active Session History (ASH) statistics are samples of session activity in the database. Active sessions are sampled every second, and are stored in a circular buffer in the system global area (SGA). Any session that is connected to the database and using CPU, or is waiting for an event that does not belong to the idle wait class, is considered an active session. By capturing only active sessions, a manageable set of data is represented with the size being directly related to the work being performed, rather than the number of sessions allowed on the system.

Using the DB time example described in "Time Model Statistics", samples of session activity are collected from the online transaction made at the bookseller's Web site, represented as vertical lines below the horizontal arrow in Figure 2-3.

Figure 2-3 Active Session History

Description of Figure 2-3 follows
Description of "Figure 2-3 Active Session History"

The light vertical lines represent samples of inactive session activity that are not captured in the ASH statistics. The bold vertical lines represent samples of active sessions that are captured at:

  • 7:38, while novels by the author are being queried

  • 7:42, while the user is browsing the query results

  • 7:50, when one of the novels is added to the shopping cart

  • 7:52, during the checkout process

Table 2-1 lists the ASH statistics that are collected for the active sessions, along with examples of the session ID, module, SQL ID, session state, and wait events that are sampled.

Table 2-1 Active Session History

Time SID Module SQL ID State Event

7:38

213

Book by author

qa324jffritcf

Waiting

db file sequential read

7:42

213

Get review ID

aferv5desfzs5

CPU

 

7:50

213

Add item to cart

hk32pekfcbdfr

Waiting

buffer busy wait

7:52

213

Checkout

abngldf95f4de

Waiting

log file sync


High-Load SQL Statistics

SQL statements that are consuming the most resources produce the highest load on the system, based on criteria such as elapsed time and CPU time.

Using the Oracle Performance Method

Performance tuning using the Oracle performance method is driven by identifying and eliminating bottlenecks in the database, and by developing efficient SQL statements. Database tuning is performed in two phases: proactively and reactively.

In the proactive tuning phase, you need to perform tuning tasks as part of your daily database maintenance routine, such as reviewing ADDM analysis and findings, monitoring the real-time performance of the database, and responding to alerts.

In the reactive tuning phase, you need to respond to issues reported by the users, such as performance problems that may occur for only a short duration of time, or performance degradation to the database over time.

SQL tuning is an iterative process to identify, tune, and improve the efficiency of high-load SQL statements.

Applying the Oracle performance method involves:

To improve the performance of your database, you will need to apply these principles iteratively.

Preparing the Database for Tuning

This section lists and describes the steps that need to be performed before the database can be properly tuned.

To prepare the database for tuning:

  1. Get feedback from users.

    Determine the scope of the performance project and subsequent performance goals, and determine performance goals for the future. This process is key for future capacity planning.

  2. Sanity-check the operating systems of all systems involved with user performance.

    Check for hardware or operating system resources that are fully utilized. List any overused resources as possible symptoms for later analysis. In addition, ensure that all hardware is functioning properly.

  3. Ensure that the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL to enable the automatic performance tuning features of Oracle Database, including the AWR and ADDM.

    The default setting for this parameter is TYPICAL.

See Also:

Tuning the Database Proactively

This section lists and describes the steps required to keep the database properly tuned on a regular basis. These tuning procedures are considered proactive and should be performed as part of your daily maintenance of Oracle Database.

To tune the database proactively:

  1. Review the ADDM findings, as described in Chapter 3, "Automatic Database Performance Monitoring".

    ADDM automatically detects and reports on performance problems with the database, including most of the "Common Performance Problems Found in Oracle Databases". The results are displayed as ADDM findings on the Database Home page in Enterprise Manager. Reviewing these findings enables you to quickly identify the performance problems that require your attention.

  2. Implement the ADDM recommendations, as described in Chapter 3, "Automatic Database Performance Monitoring".

    ADDM automatically provides a list of recommendations for reducing the impact of the performance problem with each ADDM finding. Implementing a recommendation applies the suggested changes to improve the database performance.

  3. Monitor performance problems with the database in real time, as described in Chapter 4, "Monitoring Real-Time Database Performance".

    The Database Performance page in Oracle Enterprise Manager enables you to identify and respond to real-time performance problems. By drilling down to the appropriate pages, you can identify and resolve performance problems with the database in real time, without having to wait until the next ADDM analysis.

  4. Respond to performance-related alerts, as described in Chapter 5, "Monitoring Performance Alerts".

    The Database Home page in Oracle Enterprise Manager enables you to view performance-related alerts generated by the system. Typically, these alerts reveal performance problems that, once resolved, will improve the performance of your database.

  5. Validate that the changes made have produced the desired effect, and verify if the perception of performance to the users has improved.

  6. Repeat these steps until your performance goals are met or become impossible to achieve due to other constraints.

Tuning the Database Reactively

This section lists and describes the steps required to tune the database based on user feedback. These tuning procedure are considered reactive and should be performed periodically when performance problems are reported by the users.

To tune the database reactively:

  1. Run ADDM manually to diagnose current and historical database performance when performance problems are reported by the users, as described in Chapter 6, "Manual Database Performance Monitoring".

    This is useful if you want to run ADDM before the next ADDM analysis to analyze current database performance, or to analyze historical database performance when you were not proactively monitoring the system.

  2. Resolve transient performance problems, as described in Chapter 7, "Resolving Transient Performance Problems".

    The Active Session History (ASH) reports enable you to analyze transient performance problems with the database that are short-lived and do not appear in the ADDM analysis.

  3. Resolve performance degradation over time, as described in Chapter 8, "Resolving Performance Degradation Over Time".

    The Automatic Workload Repository (AWR) Compare Periods reports enable you to compare database performance between two periods of time, and resolve performance degradation that may happen from one time period to another.

  4. Validate that the changes made have produced the desired effect, and verify if the perception of performance to the users has improved.

  5. Repeat these steps until your performance goals are met or become impossible to achieve due to other constraints.

Tuning SQL Statements

This section lists and describes the steps required to identify, tune, and optimize high-load SQL statements.

To tune SQL statements:

  1. Identify high-load SQL statements, as described in Chapter 9, "Identifying High-Load SQL Statements".

    Use the ADDM findings and the Top SQL to identify high-load SQL statements that are causing the greatest contention.

  2. Tune high-load SQL statements, as described in Chapter 10, "Tuning SQL Statements".

    You can improve the efficiency of high-load SQL statements by tuning them using the SQL Tuning Advisor.

  3. Optimize data access paths, as described in Chapter 11, "Optimizing Data Access Paths".

    You can optimize the performance of data access paths by creating the proper set of materialized views, materialized view logs, and indexes for a given workload by using the SQL Access Advisor.

  4. Repeat these steps until all high-load SQL statements are tuned for greatest efficiency.

Common Performance Problems Found in Oracle Databases

This section lists and describes common performance problems found in Oracle databases. By following the Oracle performance method outlined in this chapter, you should be able to avoid these problems. If you have these problems, then repeat the steps in the Oracle performance method, as described in "Using the Oracle Performance Method", or consult the chapter or section that addresses these problems, as described in this section.

  • CPU bottlenecks

    Is the application performing poorly because the system is CPU bound? Performance problems caused by CPU bottlenecks are diagnosed by ADDM automatically, as described in Chapter 3, "Automatic Database Performance Monitoring". You can also identify CPU bottlenecks by using the Database Performance page in Oracle Enterprise Manager, as described in "Monitoring CPU Utilization".

  • Undersized memory structures

    Are the Oracle memory structures—such as the SGA, PGA, and buffer cache—adequately sized? Performance problems caused by undersized memory structures are diagnosed by ADDM automatically, as described in Chapter 3, "Automatic Database Performance Monitoring". You can also identify memory usage issues by using the Database Performance page in Oracle Enterprise Manager, as described in "Monitoring Memory Utilization".

  • I/O capacity issues

    Is the I/O subsystem performing as expected? Performance problems caused by I/O capacity issues are diagnosed by ADDM automatically, as described in Chapter 3, "Automatic Database Performance Monitoring". You can also identify disk I/O issues by using the Database Performance page in Oracle Enterprise Manager, as described in "Monitoring Disk I/O Utilization".

  • Suboptimal use of Oracle Database by the application

    Is the application making suboptimal use of Oracle Database? Problems such as establishing new database connection repeatedly, excessive SQL parsing, and high level of contention for a small amount of data (also known as application-level block contention) can degrade the application performance significantly. Performance problems caused by suboptimal use of Oracle Database by the application are diagnosed by ADDM automatically, as described in Chapter 3, "Automatic Database Performance Monitoring". You can also monitor top activity in various dimensions—including SQL, session, services, modules, and actions—by using the Database Performance page in Oracle Enterprise Manager, as described in "Monitoring User Activity".

  • Concurrency issues

    Is the database performing suboptimally due to a high degree of concurrent activities in the database? A high degree of concurrent activities might result in contention for shared resources that can manifest in the forms of locks or waits for buffer cache. Performance problems caused by concurrency issues are diagnosed by ADDM automatically, as described in Chapter 3, "Automatic Database Performance Monitoring". You can also identify concurrency issues by using Top Sessions in Oracle Enterprise Manager, as described in "Monitoring Top Sessions".

  • Database configuration issues

    Is the database configured optimally to provide desired performance levels. For example, is there evidence of incorrect sizing of log files, archiving issues, excessive checkpoints, or suboptimal parameter settings? Performance problems caused by database configuration issues are diagnosed by ADDM automatically, as described in Chapter 3, "Automatic Database Performance Monitoring".

  • Short-lived performance problems

    Are your users complaining about short-lived or intermittent performance problems? Depending on the interval between snapshots taken by the AWR, performance problems that have a short duration may not be captured by ADDM. You can identify short-lived performance problems by using the Active Session History report, as described in Chapter 7, "Resolving Transient Performance Problems".

  • Degradation of database performance over time

    Is there evidence that the database performance has degraded over time? For example, are you or your users noticing that the database is not performing as well as it used to 6 months ago? You can generate an Automatic Workload Repository Compare Periods report to compare the period when the performance was poor to a period when the performance is stable to identify configuration settings, workload profile, and statistics that are different between these two time periods. This will help you identify the cause of the performance degradation, as described in Chapter 8, "Resolving Performance Degradation Over Time".

  • Inefficient or high-load SQL statements

    Are there any SQL statements that are using excessive system resources that impact the system? Performance problems caused by high-load SQL statements are diagnosed by ADDM automatically, as described in Chapter 3, "Automatic Database Performance Monitoring" and "Identifying High-Load SQL Statements Using ADDM Findings". You can also identify high-load SQL statements by using Top SQL in Oracle Enterprise Manager, as described in "Identifying High-Load SQL Statements Using Top SQL". After they have been identified, you can tune the high-load SQL statements using the SQL Tuning Advisor, as described in Chapter 10, "Tuning SQL Statements".

  • Data access paths to hot objects

    Are there database objects that are the source of bottlenecks because they are continuously accessed? Performance problems caused by hot objects are diagnosed by ADDM automatically, as described in Chapter 3, "Automatic Database Performance Monitoring". You can also optimize the data access path to these objects using the SQL Access Advisor, as described in Chapter 11, "Optimizing Data Access Paths".