Database Performance Management

Automatic SQL Tuning

Automatic SQL Tuning identifies and automatically tunes suboptimally performing SQL statements. SQL Tuning Advisor allows DBAs to look into problem SQLs and get comprehensive SQL Tuning recommendations identified during the diagnosis phase. It helps DBAs identify alternate execution plans using real-time and historical performance data.

Analyze performance over long periods

ADDM Spotlight aggregates the findings and recommendations from individual ADDM task runs over a specific time period for performance analysis. It provides recommendations for correcting the problems and helps users to do the following:

• Improve and standardize database performance management using Oracle ADDM findings and recommendations
• Proactively fix performance degradation from ADDM reports on custom windows for root cause analysis investigation
• Compare application code performance before and after upgrades
• Be informed of the application's high-load executions

Real-Time SQL Monitoring

Real-Time SQL Monitoring provides detailed information about the execution of SQL statements within the Oracle Database. No extra steps are needed and there are no performance overheads. Additionally, it helps DBAs, app developers, and DevOps engineers with the following use cases:

• Automatically monitor long-running and resource-intensive SQL (such as queries, DML, and DDL)
• Monitor statistics at global execution, plan operation, and parallel execution level
• Supports real-time, historical, and offline analysis via Active Reports
• Guides tuning efforts

Upgrade, migrate, and validate your database

When upgrading and migrating a database, you can encounter performance regression or slowness, which can extend the testing phase of such activities. Real Application Testing can streamline the complete process, including capacity planning, migration, and performance evaluation, with minimal effort. Start with Migration Planner to identify resources required, then proceed with Migration Workbench to identify the best migration solution followed by capturing workloads and cloning the database.

Next, it is time to start testing.

• Use the clone feature to capture test data
• Run SQL Performance Analyzer (SPA) before upgrading to get a baseline
• Upgrade your database
• Run SPA to identify SQL regression
• Tune regressed SQL statements with Tuning Advisor or use SQL plan baselines for plan stability
• When all regression is fixed, run Database Replay for throughput testing to identify regression

This approach will minimize the risk of surprises caused by regression from hardware or software and will reduce firefighting after the upgrade.

Identify root cause of performance regression in production

Consider the scenario of showing up for work on Monday morning and immediately running into unhappy users. No changes happened over the weekend, but the application has performance issues, nevertheless. Workload Analysis can help you to identify the root cause. Start by creating a SQL Tuning Set from your workload when the database works as expected. This will be your baseline workload. After encountering performance issues, you should create a new SQL Tuning Set to get started with the analysis. Workload Analysis can now compare the baseline with the current workload.

You can learn details on statements executed in the database such as:

• Statements with new execution plan and the total impact on the workload
• New statements not seen in the baseline and their impact
• Missing SQL statements only seen in the baseline

The next step is to invoke SQL Tuning Advisor to tune new and regressed statements. This process can be fully automated on a regular basis with the results and displayed on Your Enterprise Manager Database homepage.

Physical or logical database migration

The Migration Workbench offers physical/logical and offline workflow options and allows for inline version upgrades. Workflows are managed with a graphical user experience, command line interface, and REST APIs. The Cloud Premigration Advisor is embedded into the workflow.

Migration Workbench offers two migration options to migrate Oracle Databases:

1. With transportable tablespace (TTS) using logical or physical migration method
2. With the Data Pump logical migration method

The benefits of using Migration Workbench to migrate databases are:

• One tool for all replatform use cases, including cross-platform environment
• Best data movement technology based on the source and target choices
• Non-multitenant to multitenant migrations
• Near zero downtime for applications during replatform
• No or little changes for applications
• Improved application performance