Basic Performance and Tuning
7 min readAs a DBA, you are responsible for the performance of your Oracle database. Typically, performance problems result from unacceptable response time, which is the time to complete a specified workload, or throughput, which is the amount of work that can be completed in a specified time.
Typical problems include: • CPU bottlenecks • Undersized memory structures • I/O capacity issues • Inefficient or high-load SQL statements • Unexpected performance regression after tuning SQL statements • Concurrency and contention issues • Database configuration issues
The general goal of tuning is usually to improve response time, increase throughput, or both. A specific and measurable goal might be “Reduce the response time of the specified SELECT statement to under 5 seconds.” Whether this goal is achievable depends on factors that may or may not be under the control of the DBA.
In general, tuning is the effort to achieve specific, measurable, and achievable tuning goals by using database resources in the most efficient way possible.
The Oracle performance method is based on identifying and eliminating bottlenecks in the database, and developing efficient SQL statements. Applying the Oracle performance method involves the following tasks: • Performing pre-tuning preparations • Tuning the database proactively on a regular basis • Tuning the database reactively when users report performance problems • Identifying, tuning, and optimizing high-load SQL statements
Database Self-Monitoring
Self-monitoring take place as the database performs its regular operation, ensuring that the database is aware of problems as they arise. Oracle Database can send a server-generated alert to notify you of an impending problem.
Alerts are automatically generated when a problem occurs or when data does not match expected values for metrics such as physical reads per second or SQL response time. A metric is the rate of change in a cumulative statistic. Servergenerated alerts can be based on user-specified threshold levels or because an event has occurred.
Server-generated alerts not only identify the problem, but sometimes recommend how the reported problem can be resolved. An example is an alert that the fast recovery area is running out of space with the recommendation that obsolete backups should be deleted or additional disk space added.
Automatic Workload Repository (AWR)
Automatic Workload Repository (AWR) is a repository of historical performance data that includes cumulative statistics for the system, sessions, individual SQL statements, segments, and services.
AWR statistics are the foundation of performance tuning. By automating the gathering of database statistics for problem detection and tuning, AWR serves as the foundation for database self-management.
The database stores recent AWR statistics in the SGA. By default, the MMON process gathers statistics every hour and creates an AWR snapshot. An AWR snapshot is a set of performance statistics captured at a specific time. The database writes snapshots to the SYSAUX tablespace. AWR manages snapshot space, purging older snapshots according to a configurable snapshot retention policy.
An AWR baseline is a collection of statistic rates usually taken over a period when the system is performing well at peak load. You can specify a pair or range of AWR snapshots as a baseline. By using an AWR report to compare statistics captured during a period of bad performance to a baseline, you can diagnose problems.
An automated maintenance infrastructure known as AutoTask illustrates how Oracle. Database uses AWR for self-management. By analyzing AWR data, AutoTask can determine the need for maintenance tasks and schedule them to run in Oracle Scheduler maintenance windows. Examples of tasks include gathering statistics for the optimizer and running the Automatic Segment Advisor.
Automatic Database Monitor (ADDM)
Using statistics captured in AWR (Automatic Workload Repository), ADDM automatically and proactively diagnoses database performance and determines how identified problems can be resolved. You can also run ADDM manually.
Automatic Database Diagnostic Monitor (ADDM) is a self- advisor built into Oracle Database.
ADDM takes a holistic approach to system performance, using time as a common currency between components. ADDM identifies areas of Oracle Database consuming the most time.
For example, the database may be spending an excessive amount of time waiting for free database buffers. ADDM drills down to identify the root cause of problems, rather than just the symptoms, and reports the effect of the problem on Oracle Database overall. Minimal overhead occurs during the process.
In many cases, ADDM recommends solutions and quantifies expected performance benefits. For example, ADDM may recommend changes to hardware, database configuration, database schema, or applications. If a recommendation is made, then ADDM reports the time benefit.
The use of time as a measure enables comparisons of problems or recommendations.
Besides reporting potential performance issues, ADDM documents areas of the database that are not problems. Subcomponents such as I/O and memory that are not significantly impacting database performance are pruned from the classification tree at an early stage. ADDM lists these subcomponents so that you can quickly see that there is little benefit to performing actions in those areas.
Active Session History (ASH)
Active Session History (ASH) samples active database sessions each second, writing the data to memory and persistent storage. ASH is an integral part of the database self-management framework and is useful for diagnosing performance problems.
Unlike instance-level statistics gathered by Automatic Workload Repository (AWR), the database gathers ASH statistics at the session level. An active session is a session that is using CPU and is not waiting for an event in the idle wait class.
You can use Enterprise Manager or SQL scripts to generate ASH reports that gather session statistics gathered over a specified duration. You can use ASH reports for:
• Analysis of short-lived performance problems not identified by Automatic Database Diagnostic Monitor (ADDM)
• Scoped or targeted performance analysis by various dimensions or their combinations, such as time, session, module, action, or SQL ID
For example, a user notifies you that the database was slow between 10:00 p.m. and 10:02 p.m. However, the 2-minute performance degradation represents a small portion of the AWR snapshot interval from 10:00 p.m. and 11:00 p.m. and does not appear in ADDM findings. ASH reports can help identify the source of the transient problem.
Application and SQL Tuning
Oracle Database completely automates the SQL tuning process.
ADDM identifies SQL statements consuming unusually high system resources and therefore causing performance problems. In addition, AWR automatically captures the top SQL statements in terms of CPU and shared memory consumption. The identification of high-load SQL statements happens automatically and requires no intervention.
EXPLAIN PLAN Statement
Tools such as the EXPLAIN PLAN statement enable you to view execution plans chosen by the optimizer.
EXPLAIN PLAN shows the query plan for the specified SQL query if it were executed now in the current session. Other tools are Oracle Enterprise Manager and the SQL*Plus AUTOTRACE command.
Optimizer Statistics Advisor
Optimizer Statistics Advisor is diagnostic software that analyzes how you are currently gathering statistics, the effectiveness of existing statistics gathering jobs, and the quality of the gathered statistics. Optimizer Statistics Advisor uses the same advisor framework as Automatic Database Diagnostic Monitor (ADDM), SQL Performance Analyzer, and other advisors.
Optimizer Statistics Advisor provides the following advantages over the traditional approach, which relies on best practices: • Provides easy-to-understand reports • Supplies scripts to implement necessary fixes without requiring changes to application code • Runs a predefined task named AUTO_STATS_ADVISOR_TASK once per day in the maintenance window • Provides an API in the DBMS_STATS package that enables you to create and run tasks manually, store findings and recommendations in data dictionary views, generate reports for the tasks, and implement corrections when necessary • Integrates with existing tools such as Oracle Enterprise Manager (Enterprise Manager)
Optimizer Statistics Advisor maintains rules, which are Oracle-supplied standards by which the advisor performs its checks. The rules embody Oracle best practices based on the current feature set. If the best practices change from release to release, then the Optimizer Statistics Advisor rules also change. In this way, the advisor always provides the most up-to-date recommendations.
Optimizer Statistics Advisor inspects the statistics gathering process, and then generates a report of findings, which are violations of the rules. If the advisor makes recommendations, and if it proposes actions based on these recommendations, then you can either implement the actions automatically or generate an editable, executable PL/SQL script.
The advisor task runs automatically in the maintenance window, but you can also run it on demand.
SQL Tuning Advisor
The interface for automatic SQL tuning is SQL Tuning Advisor, which runs automatically during system maintenance windows as a maintenance task.
During each automatic run, the advisor selects high-load SQL queries in the database and generates recommendations for tuning these queries.
SQL Tuning Advisor recommendations fall into the following categories: • Statistics analysis • SQL profiling • Access path analysis • SQL structure analysis
A SQL profile contains additional statistics specific to a SQL statement and enables the optimizer to generate a better execution plan. Essentially, a SQL profile is a method for analyzing a query. Both access path and SQL structure analysis are useful for tuning an application under development or a homegrown production application.
A principal benefit of SQL Tuning Advisor is that solutions come from the optimizer rather than external tools. Thus, tuning is performed by the database component that is responsible for the execution plans and SQL performance. The tuning process can consider past execution statistics of a SQL statement and customizes the optimizer settings for this statement.
