Defining a Problem

Defining Problems

Problems can arise at any time. A proactive DBA watches for problems and corrects them before they are noticed by users. In the past, the discovery and definition step has been tedious and frequently dependent on listening to user feedback. User feedback is important, but is often subjective and not reproducible. In Oracle Database 11g, many of the information sources can be viewed from the Enterprise Manager interface.

• Monitor the current state of the database instance and compare it to a previous state.
• Examine AWR or Statspack reports and instance files carefully.

Objectives

• Identify performance issues
• Set tuning priorities
• Interpret tuning diagnostics
• Tune for life cycle phase
  
  

Topics

1. Limit the Scope
2. Defining the Problem
3. Setting the Priority
4. Top SQL Reports
   
5. Common Tuning Problems
6. Tuning During the Life Cycle
7. ADDM Tuning Session
8. Performance Versus Business Requirements

Refer the links below:

Performance Tuning Overview
Performance Tuning Exam(1Z0-054)

Performance Versus Business Requirements

Factors that affect performance:

• Frequent checkpointing
• Performing archiving
• Block check sums
• Redundancy
• Frequent backups of data files
• Multiple control files
• Multiple redo log members in a group
• Security
• Auditing
• Encryption
• Virtual Private Database/Fine Grained Access Control

There is always a cost of doing business in certain ways. Often the requirements of the business can impact performance. The impact of downtime and crash recovery, and even the unlikely event of block corruption, must be considered against the overhead of protecting against these events. Redundancy improves availability but requires more I/Os

The question is not whether to use the security features but what is required, and use only those features.

Often the business can only tolerate minimal downtime, so you configure the database for the business, and then tune your database accordingly. The uptime requirement, the mean time to recovery, and the amount of data that could be lost in a disk or system crash are all business issues.

ADDM Tuning Session

ADDM consolidates the manual tuning session to quickly identify the areas that produce the greatest benefit. ADDM runs automatically each time a AWR snapshot is taken. The findings of the latest ADDM report is displayed on the Database Home page of Enterprise Manager. 

Tuning During the Life Cycle

An application’s life cycle can be divided into different phases:

• Application design and development
• Testing: Database configuration
• Deployment: Adding a new application to an existing database
• Production: Troubleshooting and tuning
• Migration, upgrade, and environment changes

Tuning can be divided into two classes:

• Proactive (make it better, so it will not break) 
• Test scenarios. 
• Find the problem areas. 
• Resolve the problem. 
• Reactive (wait until it breaks, then fix it) 
• Monitor active instance. 
• Tune issues as needed.

Tuning during the life cycle involves two courses of action: proactive tuning or reactive. During the design, development, and testing phases tuning is mostly proactive; that is, scenarios and test cases are designed and tested. The results are measured and compared against other configurations. In the deployment and production environments, the tuning is mostly reactive. The need for hypothetical loads is removed as actual users and workloads are created, but the ability to anticipate problems also diminishes. You can monitor the database instance to observe changes and trends in performance metrics. From the information you gather by monitoring, you may be able to mitigate performance issues before they are noticed by users. 

The DBA may be involved in tuning from the earliest stages of design and development. It is less expensive to correct bugs and performance problems early in the life cycle than later. The differences in tuning the later phases of the life cycle are primarily in what is allowed. Many DBAs in a production environment are not allowed to change the SQL or data structures. However, a design change to improve performance may warrant a change request to the application vendor or development team.

Common Tuning Problems

The most common tuning problems:

1. Inefficient or high-load SQL statements
2. Suboptimal use of Oracle Database by the application
3. Undersized memory structures
4. Concurrency issues
5. I/O issues 
6. Database configuration issues
7. Short-lived performance problems
8. Degradation of database performance over time 
9. Unexpected performance regression after environment changes 
10. Locking issues

Tuning inefficient or high-load SQL statements has an wide impact that can reduce memory use, CPU, and IO resources. SQL tuning issues includes poorly written SQL, ineffective use of indexes, access path costs, and sorting. In this course, we assume that the DBA has little or no opportunity to change the SQL statements. 

Problems such as establishing new database connections repeatedly, excessive SQL parsing, and high levels of contention for a small amount of data (also known as application-level block contention) can degrade the application performance significantly. These are all poor use of the database by the application. 

Memory issues are high on the list of instance tuning problems. Proper sizing of the System Global Area (SGA) including the Shared pool and Buffer cache, and the Process Global Area (PGA) reduce contention for memory resources and indirectly reduces IO and CPU. 

A high degree of concurrent activities, multiple processes, or users might result in contention for shared resources that can be manifested in the forms of various types of waits. Many resources can only be accessed by only one process at a time. Several processes attempting to access the same resource creates contention.

In any database, I/O issues, such as database file layout on disk or RAID devices, can be a source of performance problems. In OLTP applications, the amount of redo and undo generated can create bottlenecks in memory or I/O.

Some problems are reported by users but may not be apparent from reports that span intervals of 30 minutes or longer. The Oracle Database has additional tools (Active Session History ASH) that allow the DBA to view statistics and metrics over small segments of time in the recent past. 

Many databases will have gradual changes: the number of user, the amount of data, the number of reports, and modules in use. These changes may lead to a degradation in performance. The proactive DBA will capture and save statistics sets from when the database is performing acceptably, to compare with statistics when the database performance is poor to identify the differences.

The environment of the database is seldom static. Patches, upgrades, new hardware, or changes to the instance parameters can change the performance of the database. Sometimes a change improves performance in one area and causes another area to degrade. 

Locking issues are not common problems, but when you have locking issues they become very important.

Top SQL Reports

The following Top SQL sections sort the SQL statements with the top resource usage in multiple ways, as indicated by their titles:

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Executions
• SQL ordered by Parse Calls
• SQL ordered by Sharable Memory
• SQL ordered by Version Count

These sorts allow you to find the troublesome SQL statement. You can also view the Complete List of SQL Text section to view the entire SQL text. In this example, a single SQL statement is responsible for almost all of the instance activity.

Setting the Priority

Determine which problem to tune first. In the performance reports, you see many statistics; even a well-tuned database shows a set of top wait events. The Oracle server provides a set of wait event statistics for processes that are idle or waiting. The Oracle server also records CPU utilization for processes that are running. To determine the impact of a particular event, it must be compared with the overall time spent. 

Each request to the database server has a response time consisting of a wait time and a service time. The service time is the time spent actively working on the request (CPU time). The wait time is by definition the time waiting for any reason. Both service time and wait time may be tuned. 

To tune the service time something has to change: the processing, the SQL, the access path, or the data storage structure. Wait times can be tuned by reducing contention for the resource where the wait is occurring. 

How to determine the priority 

You can take help from AWR reports.

The top wait events always have some values. In example shown in the slide, the users are complaining of slow response time. The Top 5 Timed Foreground Events only shows that the instance is using the CPU.

The Instance CPU section show that the instance is using 65% of the OS CPU; this information is inconclusive. The instance is supposed to use CPU and not wait. This set of diagnostics may mean that the instance is CPU bound. As pointed out earlier, performance tuning can either reduce wait time or service time. In this case, the service time needs to be reduced. To reduce service time, SQL is the usual area to be examined.

Setting the Priority: Example

The top wait events did not give a clear direction. So you continue with the time model to find which areas are consuming the DB time. You can determine the top-priority tuning tasks by comparing the time spent in various waits and tasks with the overall wait time and service time. Both major tools report the Time Model Statistics to guide your tuning efforts. For example, the AWR report excerpt in the slide shows that the database CPU time (time in user calls) is 474.04 seconds. The time spent in user calls is 44.85% of the total DB time. The “sql execute elapsed time” shows 1050.06 seconds; this time includes wait times. Just from this limited view, the wait times for the SQL execution are significant, and would lead you to examine the wait statistics related to the SQL execution and the SQL reports to identify individual SQL statements for tuning.

The “% of DB Time” values indicate a relative impact tuning this area could have. If the “sql execute elapsed time” could be reduced, then the maximum possible improvement is 1050 seconds or 99%. SQL will always take some time to execute. Therefore, the actual improvement may be much less, depending on the amount of improvement you can get from that area.

Defining the Problem

Problems can arise at any time. A proactive DBA watches for problems and corrects them before they are noticed by users. In the past, the discovery and definition step has been tedious and frequently dependent on listening to user feedback. User feedback is important, but is often subjective and not reproducible. In Oracle Database 11g, many of the following information sources can be viewed from the Enterprise Manager interface:

• Monitor the current state of the database instance and compare it to a previous state.
• Use Statspack or AWR to collect performance metrics regularly. Changes can point to issues before they become noticeable to users. 
• Use OS or EM tools to check for CPU and disk queuing, disk utilization, and memory swapping. These are the signs of an overloaded system. 
• Examine AWR or Statspack reports and instance files carefully.
• Use the available tools, such as Statspack or AWR reports, to identify SQL statements in the applications that are consuming the most resources. Have these changed?
• Check the alert logs, and trace files for error messages that might give a quick clue to the nature of the problem. Do not overlook system- and application-specific logs.
• Ensure that the initialization parameter settings make sense for the system. 
• Collect instance and OS statistics. Statspack reports point to components where the greatest waits and the greatest use of resources occur. ADDM goes further by focusing on those components with the greatest potential benefit

Limit the Scope

Does the performance issue originate in the operating system (OS), the instance, or the application SQL? This question is not always easy to answer. 

• For e.g. Poorly performing SQL can cause excessive physical reads and writes, appearing to be an I/O issue.
• Improperly sized memory components (an instance configuration issue) can lead to excessive swapping in the OS. 
• Poor disk configuration can appear to be an instance configuration problem, causing a large redo file waits or commit waits, and other problems.

Does the performance issue originate in the operating system (OS), the instance, or the application SQL? This question is not always easy to answer. Poorly performing SQL can cause excessive physical reads and writes, appearing to be an I/O issue. Improperly sized memory components (an instance configuration issue) can lead to excessive swapping in the OS. Poor disk configuration can appear to be an instance configuration problem, causing a large redo file waits or commit waits, and other problems.

Eliminate possibilities. When the instance appears to have I/O problems, compare the instance file I/O statistics to OS level statistics. The differences can guide you to the actual problem. For example: A higher than normal average wait time on a particular tablespace, could be due to: 

• Hardware: A file is on a slow drive or an improper RAID configuration.
• OS: The OS is busy with other files on the same drive or partition.
• Instance: The tablespace was created with different properties than other tablespaces have, other busy database files are on the same disk or partition (the database I/O is not balanced across all the drives), or the objects being accessed are mostly in the same tablespace, file, or disk. 
• Application: The application is doing excessive I/O due to poor access path choice by the optimizer due to out of date statistics, inefficient indexes, or other reasons.

Determine the scope of the problem to focus your efforts on the solutions that provide the most benefit.