Database Performance Management Features

Features topics

Find issues using performance diagnostics

Automatic Workload Repository (AWR)

A repository of historical performance data that includes the following:

  • Cumulative statistics for the system, sessions, individual SQL statements, segments, and services
  • High-load SQL statements
  • Database time at the database level as well as at the session level
  • Object statistics for segments such as tables, indexes, and other database objects
  • Statistics that are the foundation of performance tuning
  • Data is captured every hour and retained for eight days, by default

ADDM Spotlight

Provides performance recommendations and impact analysis over long periods.

  • Aggregates expert tactical advice into strategic performance optimization insights
  • Findings ordered by overall impact based on database time, recommendations by overall benefit
  • Recommendations grouped by implementation domain: database parameters, SQL, schema objects
  • Database parameter provides flexible opt-in filtering: high impact and non-default
Learn more about ADDM Spotlight

Top Activity Lite

Single screen to enable easy visualization of issues for real-time performance diagnostics.

  • Uses compact UI layout for NOC-like screen support
  • Efficiently displays last-hour activity, even under heavy load
  • Simplified version of Performance Hub—contains only a subset of relevant information
Learn more about Top Activity Lite

Performance Hub

Allows you to view all the performance data available for a specified time period.

  • Showcases a summary of average active session waits by category, load profile per second, active session activity.
Learn more about Performance Hub

Fix issues using Performance Tuning

SQL Tuning Advisor

Provides advice on various problems identified during the analysis phase.

  • Uses the same CBO but has more time budget to perform a comprehensive analysis
  • Identifies alternate execution plans using real-time and historical performance data
  • Recommends parallel profile or indexes if it will improve SQL performance significantly (2X or more)

Real-Time SQL Monitoring

In-depth application SQL performance analysis.

  • Identifies poorly written and designed SQL statements
  • Identifies and guides optimization of application calls in the data tier
  • Captures fine-grained SQL statistics at each step of the execution plan
  • Provides interactive visualization
  • Analyze current and historical SQL statements
Learn more about Real-Time SQL Monitoring

Automatic SQL Tuning

Automates end-to-end tuning of high-load SQL statements.

  • Automatically captures the high-load SQL by various criteria
  • Runs SQL Tuning Advisor on captured SQLs in a maintenance window
  • Provides comprehensive recommendations to improve response time: Statistics Refresh/Gather, SQL Profile, Alternate SQL, Create Indexes, Restructure SQL, and more
  • View the report or automatically implement SQL profiles

Validate with Real Application Testing

SPA Quick Check

SPA Quick Check proactively identifies the impact of routine DBA tasks on database performance. You can use SPA Quick Check to validate what the impact to your database workload will be for the following changes:

  • Changing the value of an initialization parameter
  • Gathering pending optimizer statistics
  • Implementing key SQL profiles

SQL Performance Analyzer

SQL Performance Analyzer provides proactive SQL response time (unit) testing on upgrades and migrated databases. It compares the performance of SQL statements before and after the change and produces a report identifying any changes in execution plans or performance of the SQL statements. Designed for identifying and tuning regressed SQLs, it helps to safeguard production systems against SQL performance regressions due to various database changes such as:

  • Upgrades, migrations, patch sets
  • Init.ora configuration modifications
  • Schema changes—access structures (indexes, MV, partition)
  • Minor application upgrades
  • Routine maintenance tasks—statistics refresh, and more

Workload Analysis

Workload Analysis helps you identify, quantify, and eliminate the reason for regression or improvements. It performs an analysis of top queries in the database from two different time points expected to be the same or similar. Regressed statements can then be tuned by using SQL Tuning Advisor or SQL plan baselines. The Workload Analysis:

  • Helps to analyze performance data at the application level
  • Compares two SQL tuning sets from different time points in a production database
  • Provides near real-time analysis of top SQL statements to identify changed performance and reason for changed performance using historical execution statistics.

Database Replay

Database Replay enables you to replay a full production workload on a test system to assess the overall impact of system changes. You can use Database Replay to:

  • Capture a workload on the production system
  • Replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload
  • Test the effects of a system change without affecting the production system

Database Migration Planner

Database Migration Planner provides a comprehensive, end-to-end solution for managing database consolidation. It enables you to match managed sources you want to consolidate with new or existing destinations. Database Migration Planner supports the following combinations.

  • Consolidate source databases (single instance or RAC) to fewer destination databases, using the database-to-database (D2D) consolidation type (consolidation to multi-tenant). Destinations can be existing databases (both non-CDB and CDB) or new databases on new servers, which can be Oracle Exadata Database Machines, Oracle Compute Cloud shapes, or generic servers.
  • Consolidate source databases (single instance or RAC) to fewer servers where the number of databases stays the same, using the database-to-server (D2S) consolidation type. Destinations can be existing servers or new servers, which can be Oracle Exadata Database Machines, Oracle Compute Cloud shapes, or generic servers.
  • Migrate source databases to another server with more resources. You can perform consolidation at a later time.

Migration Workbench

Migration Workbench is a solution that helps migrate on-premises databases to different Oracle Database destinations, such as non-multitenant to multitenant or on-premises to Autonomous Database. It provides the ability to migrate databases from legacy to new generation Exadata Database Machines. It has built-in capabilities for automatic pre- and post-migration analysis. With SQL Performance Analyzer, each migration activity will go through a comprehensive performance analysis with deep insights into performant SQL statements on the migrated database.

  • One tool for all types of migrations
  • Near zero downtime for application during migration
  • Accuracy, predictability, and repetitiveness
Learn more about Migration Workbench