Oracle 11g R2 Performance Tuning : March 2014

Friday, March 14, 2014

The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. Active sessions are sampled every second and are stored in a circular buffer in SGA. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session. This includes any session that was on the CPU at the time of sampling.

Topics

Refer the links below:

Performance Analysis with the Oracle Active Session History

Practical Use of Active Session History

Generating ASH Reports

Through SQL

SQL> define dbid = '';

SQL> define inst_num = '';

SQL> define report_type = 'html';

SQL> define begin_time = '09:00';

SQL> define duration = 480;

SQL> define report_name = '/tmp/sql_ashrpt.txt';

SQL> define slot_width = '';

SQL> define target_session_id = '';

SQL> define target_sql_id = 'abcdefghij123';

SQL> define target_wait_class = '';

SQL> define target_service_hash = '';

SQL> define target_module_name = '';

SQL> define target_action_name = '';

SQL> define target_client_id = '';

SQL> @?/rdbms/admin/ashrpti

ASH Report Activity Over Time

One of the most informative sections of the ASH Report is the Activity Over Time section. In this section, the ASH report time span is divided into 10 time slots. If the time period is too short or the data too sparse, the resulting report has fewer slots. Slots 2 through 9 are defined as an integer number of minutes of all the same size for easy comparison. So, it is best to compare the inner slots to one another.

Using the Activity Over Time section, you can perform skew analysis by looking for spikes in the Event Count column. This would indicate an increase in the number of processes waiting for a particular event. A spike in the Slot Count indicates an increase in active sessions because ASH data is sampled from active sessions only. In the pictured example, note the circled event. The number of active session samples has increased, and the number of sessions associated with the buffer busy waits event has also spiked. This kind of skew in a slot possibly represents the root cause of the problem being investigated.

ASH Report Structure

The ASH Report follows the pattern of the AWR report. Starting from the upper right of the slide, the report sections are as follows:

Top Events: Reports the user events, background events, and the event parameter values

Load Profile: Reports the top service/module, top clients, identifies the type of SQL commands and top phases of execution

Top SQL: Reports top SQL statements associated with the top events, SQL associated with the top rowsources, top SQL using literals, and the SQL text for these SQL statements.

Top PL/SQL Procedures: Lists the PL/SQL procedures that accounted for the highest percentages of sampled session activity

Top Java Workload: Describes the top Java programs in the sampled session activity

Top Sessions: Reports the top sessions found waiting, top blocking sessions, and aggregates for PQ sessions

Top Objects/Files/Latches: Reports the top objects, files, or latches that were involved in a wait

Activity Over Time: Reports the top three wait events for 10 equally sized time periods during the report period

Active Session History Mechanics

It would be very expensive to record all activities of all sessions. The ASH extracts only samples of information from V$SESSION. One sample is extracted every second. This is efficiently done without using SQL.

ASH is designed as a rolling buffer in memory, and previous information is overwritten when needed. The volume of the data in the ASH buffer can be very large, and flushing it all to disk is unacceptable. A more efficient approach is to filter the history data while flushing it to the workload repository.

This is done automatically by the manageability monitor (MMON) process every 60 minutes, and by the manageability monitor light (MMNL) process whenever the buffer is full.

Note: The memory for the ASH comes from the System Global Area (SGA), and it is fixed for the lifetime of the instance. It represents 2 MB of memory per CPU. The ASH cannot exceed a maximum bound of five percent of the shared pool size, or five percent of the SGA_TARGET.

ASH statistics are available through the V$ACTIVE_SESSION_HISTORY fixed view. This view contains one row for each active session per sample. All columns that describe the session in the ASH are present in the V$SESSION view.

Automatic Database Diagnostic Monitor

When problems occur with a system, it is important to perform accurate and timely diagnosis of the problem before making any changes to a system. Oftentimes, a database administrator (DBA) simply looks at the symptoms and immediately starts changing the system to fix those symptoms. However, an accurate diagnosis of the actual problem in the initial stage significantly increases the probability of success in resolving the problem.

With Oracle Database, the statistical data needed for accurate diagnosis of a problem is stored in the Automatic Workload Repository (AWR). The Automatic Database Diagnostic Monitor (ADDM):

Analyzes the AWR data on a regular basis
Diagnoses the root causes of performance problems
Provides recommendations for correcting any problems
Identifies non-problem areas of the system

Because AWR is a repository of historical performance data, ADDM can analyze performance issues after the event, often saving time and resources in reproducing a problem.

In most cases, ADDM output should be the first place that a DBA looks when notified of a performance problem. ADDM provides the following benefits:

Automatic performance diagnostic report every hour by default
Problem diagnosis based on decades of tuning expertise
Time-based quantification of problem impacts and recommendation benefits
Identification of root cause, not symptoms
Recommendations for treating the root causes of problems
Identification of non-problem areas of the system
Minimal overhead to the system during the diagnostic process

It is important to realize that tuning is an iterative process, and fixing one problem can cause the bottleneck to shift to another part of the system. Even with the benefit of ADDM analysis, it can take multiple tuning cycles to reach acceptable system performance. ADDM benefits apply beyond production systems; on development and test systems, ADDM can provide an early warning of performance issues.

Topics

Refer the links below:
Automatic Performance Diagnostics
Diagnosing and Resolving Performance Problems Using ADDM

Thursday, March 13, 2014

ADDM with Oracle Real Application Clusters

If you are using Oracle RAC, you can run ADDM in Database analysis mode to analyze the throughput performance of all instances of the database. In Database mode, ADDM considers DB time as the sum of the database time for all database instances. Using the Database analysis mode enables you to view all findings that are significant to the entire database in a single report, instead of reviewing a separate report for each instance.

The Database mode report includes findings about database resources (such as I/O and interconnect). The report also aggregates findings from the various instances if they are significant to the entire database. For example, if the CPU load on a single instance is high enough to affect the entire database, the finding will appear in the Database mode analysis, which will point to the particular instance responsible for the problem.

Automatic Database Diagnostic Monitor and Oracle RAC Performance

The Automatic Database Diagnostic Monitor (ADDM) is a self-diagnostic engine built into the Oracle Database. ADDM examines and analyzes data captured in the Automatic Workload Repository (AWR) to determine possible performance problems in Oracle Database. ADDM then locates the root causes of the performance problems, provides recommendations for correcting them, and quantifies the expected benefits. ADDM analyzes the AWR data for performance problems at both the database and the instance level.

An ADDM analysis is performed as each AWR snapshot is generated, which is every hour by default. The results are saved in the database and can be viewed by using Enterprise Manager. Any time you have a performance problem, you should first review the results of the ADDM analysis. An ADDM analysis is performed from the top down, first identifying symptoms, then refining the analysis to reach the root causes, and finally providing remedies for the problems.

For the clusterwide analysis, Enterprise Manager reports two types of findings:

Database findings: An issue that concerns a resource that is shared by all instances in the cluster database, or an issue that affects multiple instances. An example of a database finding is I/O contention on the disk system used for shared storage.

Instance findings: An issue that concerns the hardware or software that is available for only one instance, or an issue that typically affects just a single instance. Examples of instance findings are high CPU load or sub-optimal memory allocation.

Description of addm_cluster_findings.gif follows

ADDM reports only the findings that are significant, or findings that take up a significant amount of instance or database time. Instance time is the amount of time spent using a resource due to a performance issue for a single instance and database time is the sum of time spent using a resource due to a performance issue for all instances of the database, excluding any Oracle Automatic Storage Management (Oracle ASM) instances.

An instance finding can be reported as a database finding if it relates to a significant amount of database time. For example, if one instance spends 900 minutes using the CPU, and the sum of all time spent using the CPU for the cluster database is 1040 minutes, then this finding would be reported as a database finding because it takes up a significant amount of database time.

A problem finding can be associated with a list of recommendations for reducing the impact of the performance problem. Each recommendation has a benefit that is an estimate of the portion of database time that can be saved if the recommendation is implemented. A list of recommendations can contain various alternatives for solving the same problem; you do not have to apply the recommendations.

Recommendations are composed of actions and rationales. You must apply all the actions of a recommendation to gain the estimated benefit of that recommendation. The rationales explain why the actions were recommended, and provide additional information to implement the suggested recommendation.

Viewing ADDM for Oracle RAC Findings

Wednesday, March 12, 2014

Automated Maintenance Tasks

With Oracle Database 11g, the Automated Maintenance Tasks feature relies on the Resource Manager being enabled during the maintenance windows.

The resource plan associated with the window is automatically enabled when the window opens.

The goal is to prevent maintenance work from consuming excessive amounts of system resources.Each maintenance window is associated with a resource plan that specifies how the resources will be allocated during the window duration.

In Oracle Database 11g, WEEKNIGHT_WINDOW and WEEKEND_WINDOW (defined in Oracle Database 10g) are replaced with daily maintenance windows.

Automated Maintenance Task Priorities

The Automated Maintenance Tasks feature is implemented by Autotask Background Process (ABP). ABP functions as an intermediary between automated tasks and the Scheduler.
Its main purpose is to translate automated tasks into AUTOTASK jobs for execution by the Scheduler.
Just as important, ABP maintains a history of execution of all tasks. ABP stores its private repository in the SYSAUX tablespace; you view the repository through DBA_AUTOTASK_TASK.
ABP is started by MMON at the start of a maintenance window. There is only one ABP required for all instances. The MMON process monitors ABP and restarts it if necessary.
ABP assigns jobs to various Scheduler job classes. These job classes map the job to a consumer group based on priority.
The Automatic Maintenance Tasks feature determines when—and in what order—tasks are performed. As a DBA, you can control the following:

If the maintenance window turns out to be inadequate for the maintenance workload, adjust the duration and start time of the maintenance window.
Control the resource plan that allocates resources to the automated maintenance tasks during each window.
Enable or disable individual tasks in some or all maintenance windows.
In a RAC environment, shift maintenance work to one or more instances by mapping maintenance work to a service. Enabling the service on a subset of instances shifts maintenance work to these instances.

Enterprise Manager is the preferred way to control Automatic Maintenance Tasks. However, you can also use the DBMS_AUTO_TASK_ADMIN package.

AWR Baseline

AWR Baselines

AWR Baseline contains a set of AWR snapshots for an “interesting or reference” period of time.

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

Create AWR baselines
Enable adaptive thresholds
Create AWR baselines for future time periods

Topics

Refer the links below:

Resolving Performance Degradation Over Time
Using Automatic Workload Repository for Database Tuning: Tips for Expert DBAs

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

Refer the links below:

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