During Quest Forum Digital Event: Database & Technology 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. According to Engeleiter, you should follow these five steps to maximize performance of Oracle Enterprise Manager:
- Understand database management solutions.
- Know why database performance can change.
- Determine where your database is spending time.
- Tune the database with built-in tools.
- 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:
- Oracle Enterprise Manager (EM)
- Oracle Management Cloud (OMC)
- 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 monitoring 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 storage, 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, Automatic 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 analysis 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 highest 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 Exadataaware SQL Profile took 1.9 minutes and turning with Exadata-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 monitored SQL executed and a SQL Monitor details report for that SQL
- Table names for which a user does not have visibility are hidden 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 optimizing 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.
For more information, check out Engeleiter’s Quest Forum Digital Event: Database & Technology Week presentation at https://questoraclecommunity.org/learn/videos/database-optimization-to-the-max-getting-the-best-out-of-enterprise-manager.