During Quest Forum Digital Event: Database & Technol­ogy Week, Kurt Engeleiter, product manager of database manageability at Oracle, presented on database optimization and how to get the best out of Oracle Enterprise Manager. Accord­ing to Engeleiter, you should follow these five steps to maximize performance of Oracle Enterprise Manager:

  1. Understand database management solutions.
  2. Know why database performance can change.
  3. Determine where your database is spending time.
  4. Tune the database with built-in tools.
  5. Fix performance problems before they happen.

Step 1: Understand database management solutions.

For best-in-class monitoring, management, and control of the Oracle estate, customers can explore three main 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 database and engineered systems

  • A single pane of glass for on-premise and Oracle Cloud
  • One release per year matching Oracle Database’s annual release cadence
  • Management by the customer; deployed on-premise, in Oracle Cloud, or in another IaaS (infrastructure as a service)

EM is best used as your primary console when:

  • You’re an existing customer already managing your entire Oracle Database fleet with EM.
  • You’re a new database customer that requires active database performance and lifecycle management.

Option 2: OMC provides:

  • Intelligent, fleet-wide analytics of Enterprise Manager data
  • Full-stack monitoring and log analytics for Oracle, heterogeneous, and multi-cloud environments
  • Management by Oracle; delivered globally as SaaS from Oracle Cloud

OMC is best used as your primary console when:

  • You’re an existing customer not already using EM for moni­toring database fleet.
  • 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)
  • Database 19.3 for Oracle Management Repository
  • Oracle Linux 7.6 host

Both 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 can change.

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

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

  • Adding or dropping indexes, partitioning tables, adding materialized views
  • Gathering optimized statistics
  • Modifying init.ora parameters
  • Implementing new features such as Compression, Database In-Memory
  • Applying patches—one-offs, patch bundles
  • Upgrading versions or updating releases

Step 3. Determine where your database is spending time.

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

Customers can monitor database time with Perfhub, Auto­matic Workload Repository (AWR) reporting, and Active Session History (ASH) analytics.

ASH analytics enable targeted performance analysis of transient problems. Here’s how ASH works:

  • Samples active sessions every one second into memory
  • Helps answer questions such as “Has this SQL gotten slower over time?”
  • Provides direct access to kernel structures
  • Flushes one of 10 samples to AWR at every snapshot
  • Captures several types of data
  • System Identifier (SID)
  • SQL ID
  • Program, Module, Action
  • Wait event#
  • Object, File, Block
  • Actual wait time (if captured while waiting)

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

Try Automatic SQL Tuning for sub-optimal performance. Perks of using this tool include:

  • Recommendations on various problems found during anal­ysis phase
  • It uses the same cost-based optimizer (CBO) but has more time budget for comprehensive analysis
  • Identification of alternative execution plans using real-time and historical performance data
  • SQL profile recommendations to compensate for incorrect statistics and transparently improve performance

Additionally, SQL Tuning Advisor helps users choose the high­est DB time with a targeted improvement recommendation.

Overview of SQL Tuning Advisor on Exadata

  • In Oracle Database 18 and above, SQL Tuning Advisor detects if SQL is executing on Exadata.
  • SQL Tuning Advisor privately gathers system statistics and does analysis with and without these statistics.
  • If a better execution plan is found with these system statistics, an Exadata-aware SQL Profile will be recommended.
  • Can result in 10x or better performance improvement for SQLs which can benefit from Exadata hardware—e.g., cell smart scans.

In a comparison of performance, tuning without Exadata­aware SQL Profile took 1.9 minutes and turning with Exada­ta-aware SQL Profile finished in only 13 seconds.

Customer Benefits from Real-Time SQL Monitoring

  • Automatically monitors longer running and parallel SQL
  • Out-of-the-box with no performance impact
  • Provides a graphical explain plan
  • Guides tuning efforts
  • Shows monitoring statistics
  • Global execution level
  • Plan operation level
  • Parallel execution level
  • SQL-level metrics
  • CPU, I/O requests, throughput, PGA, temp space

Application Developers Benefits from SQL Monitoring 19.1

  • Application developers can now view a list of all moni­tored SQL executed and a SQL Monitor details report for that SQL
  • Table names for which a user does not have visibility are hid­den from the SQL Monitor report (even if they can access views defined on the tables)
  • DBA users will continue to see all monitored SQL, just as before

Step 5: Fix performance problems before they happen.

Get proactive about performance management with SPA Quick Check.

Overview of Benefits from SPA Quick Check

  • 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 build different SQL trials (experiments) of SQL statements performance by test execution or explain plan
  • Day-to-day use cases integrated 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 opti­mizing use on production systems, controlling with time limits, and keeping context-aware with Optimizer-gathered statistics and Init.ora parameter changes.

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

 

Source: here