Database Optimization: Getting the Most Out of Oracle Enterprise Manager

At the Quest Forum: Database & Technology Week digital event, Kurt Engeleiter, product manager of database manageability at Oracle, discussed database optimization and how to get the most out of Oracle Enterprise Manager. According to Engeleiter, you should follow these five steps to optimize the performance of Oracle Enterprise Manager:

  1. Understand database management solutions.
  2. Know why database performance might change.
  3. Determine where your database spends time.
  4. Optimize the database with built-in tools.
  5. Fix performance issues before they happen.

Step 1: Understand database management solutions.

For best-in-class monitoring, management, and control of Oracle’s fleet, customers can explore three primary options:

  1. Oracle Enterprise Manager (EM)
  2. Oracle Management Cloud (OMC)
  3. A combination of both

Option 1: Oracle Enterprise Manager provides:

Integrated management of the entire Oracle stack, especially databases and engineering systems

  • A single screen for on-premises and Oracle Cloud
  • One release per year matching Oracle Database’s annual release cadence
  • Management by the client; deployed on-premises, in Oracle Cloud, or another IaaS (Infrastructure as a Service)

EM is best used as the primary console when:

  • You are an existing customer and already manage your entire Oracle database fleet with EM.
  • You are a new database customer who requires active database lifecycle and performance management.

Option 2: OMC provides:

  • Intelligent fleet-wide analysis of Enterprise Manager data
  • Full stack monitoring and log analysis for Oracle, heterogeneous, and multicloud environments
  • Management by Oracle; delivered worldwide as SaaS from Oracle Cloud

OMC is best used as the primary console when:

  • You are an existing customer who does not yet use EM to monitor the database estate.
  • You use monitoring, advanced log search, and IT analytics capabilities to monitor applications on-premises and across multiple clouds.

Option 3: New! Oracle Enterprise Manager in Oracle Cloud Marketplace provides:

  • Enterprise Manager 13.3PG BP3 (July 2019 release)
  • 19.3 Database for Oracle Management Repository
  • Oracle Linux 7.6 Host

EM and OMC are recommended when:

  • EM customers use OMC for log analysis and advanced analytics against EM data for Oracle Database and Exadata targets

Step 2: Know why database performance might change.

Data growth, user population growth, database consolidation, and infrastructure changes (server upgrades, disk storage changes, migration to new platforms) are environmental changes that affect performance.

In addition to environmental variables, several DBA actions impact performance. These actions include:

  • Add or remove indexes, partition tables, add materialized views
  • Optimized statistics collection
  • Changing init.ora settings
  • Implemented new features such as compression, in-memory database
  • Patching: single parts, patch sets
  • Upgrading Versions or Updating Versions

Step 3. Determine where your database spends time.

Database time is the total time of database calls by foreground sessions. This includes CPU time, I/O time, and active wait time. This number is a fundamental unit for analyzing Oracle’s performance.

Customers can monitor database time with Perfhub, Automatic Workload Repository (AWR) reports and Active Session History (ASH) analysis.

ASH analysis allows targeted performance analysis of transient issues. Here’s how ASH works:

  • Samples active sessions every second into memory
  • Help answer questions like “Has this SQL gotten slower over time?”
  • Provides direct access to kernel structures
  • Dumps one of 10 samples to AWR every snapshot
  • Captures multiple types of data
  • System identifier (SID)
  • SQL ID
  • Program, Module, Action
  • Wait for event#
  • Object, file, block
  • Actual waiting time (if captured while waiting)

Step 4: Optimize the database with built-in tools.

Try automatic SQL tuning for suboptimal performance. Benefits of using this tool include:

  • Recommendations on various problems encountered during the analysis phase
  • It uses the same cost-based optimizer (CBO) but has a larger time budget for full analysis
  • Identify alternative execution plans using historical and real-time performance data
  • SQL profile recommendations to compensate for incorrect statistics and improve performance seamlessly

Additionally, SQL Tuning Advisor helps users choose the highest database time with targeted improvement recommendation.

Introducing SQL Tuning Advisor on Exadata

  • In Oracle Database 18 and later, SQL Tuning Advisor detects if SQL is running on Exadata.
  • SQL Tuning Advisor privately collects system statistics and performs analysis with and without these statistics.
  • If a better execution plan is found with these system statistics, an Exadata compatible SQL profile will be recommended.
  • May result in 10x or better performance improvement for SQLs that can benefit from Exadata hardware, e.g. smart cell analytics.

In a performance comparison, tuning without the Exadata-compatible SQL profile took 1.9 minutes and tuning with the Exadata-compatible SQL profile was completed in just 13 seconds.

Real-Time SQL Monitoring Customer Benefits

  • Automatically monitors longer and parallel SQLs
  • Ready to use with no impact on performance
  • Provides a graphical explanation plan
  • Guides adjustment efforts
  • Displays monitoring statistics
  • Global run level
  • Plan the level of operation
  • Parallel Execution Level
  • SQL level metrics
  • CPU, I/O requests, throughput, PGA, temporary space

Application developers benefit from SQL Monitoring 19.1

  • Application developers can now view a list of all monitored SQLs executed and a detailed SQL Monitor report for that SQL
  • Table names for which a user does not have visibility are hidden in the SQL Monitor report (even if he can access the views defined on the tables)
  • DBA users will continue to see all monitored SQLs, as before

Step 5: Fix performance issues before they happen.

Be proactive about performance management with SPA Quick Check.

Overview of SPA Quick Check benefits

  • Helps users predict the impact of routine system changes on production SQL workloads
  • Low-overhead capture of SQL workload to SQL Tuning Sets (STS)
  • Equips users to create different SQL tests (experiments) of performance of SQL statements by running tests or explanation plan
  • Integrated daily use cases with SPA Quick Check, SQL Plan Baselines and SQL Tuning Advisor to form an end-to-end solution

Proactive performance management can predict the impact of routine system changes on SQL workload response time by optimizing utilization on production systems, controlling with time limits and taking context into account through statistics collected by Optimizer and changes to Init.ora settings.

By focusing on these five steps, you can get the most out of Oracle Enterprise Manager.

For more information, check out Engeleiter’s Quest Forum digital event: Database and Technology Week Overview at https://questoraclecommunity.org/learn/videos/database-optimization-to-the-max-getting-the-best-out-of-enterprise-manager.