OCI Ops Insights, (OPSI) service is a cloud native solution that is part of the Oracle Cloud Observability and Management platform. OPSI helps gather and analyze telemetry data from various resources and plan for enterprise growth. It helps perform data-driven capacity planning and performance management, thereby reducing enterprise capital expenditure and improving applications throughput. OPSI continues to be enhanced to enrich the end user experience through a new integration with Oracle Enterprise Manager (EM). With the latest release of EM 13.5, Release Update 23, we deliver a new feature, EM Cloud Extension, which seamlessly broadens EM’s database long-term analytic capabilities.

EM Cloud Extension enhancement helps you take advantage of the OPSI service’s rich features directly in the EM interface. View SQL performance insights, database capacity planning, and forecasting information in the context of a database, right on the EM’s Database Home page. In addition, once upgraded to EM 13.5, RU23 and this feature is enabled, you can continuously benefit from the latest enhancements to these OPSI database functionalities.

EM Cloud Extension use cases 

With EM Cloud Extension feature, an EM administrator or a DBA can enjoy the benefits of complementary OPSI features in EM. Below are the key use cases:

  • Enterprise Manager complementary insights 
  • Seamless cloud capabilities without the need to patch EM instance
  • Offload intensive analytic tasks to cloud for EM scalability

EM Cloud Extension console experience

To access EM Cloud Extension from EM, go to the Targets menu and then click on Databases sub-menu, select the database of your choice. Then, navigate to the database home page. On this page, if the EM managed database that was selected has not been enabled in OCI for OPSI service, you will see the following page as shown in Figure 1. The page helps the existing EM users understand OPSI service and its complementary features such as Capacity Planning and SQL Insights. It also includes the steps required to enable OPSI service via the EM Cloud Bridge feature for the selected EM managed database.

 

Figure 1: EM Cloud Extension landing page
Figure 1: EM Cloud Extension landing page

If the EM managed database that has been selected has been enabled in OCI for OPSI service, you will see the “Capacity and SQL Insights” tile on the left-hand side of the page populated with appropriate information. The right-hand side shows two tabs – Capacity Planning and SQL Insights.

The Capacity Planning tab (as shown in Figure 2) provides the following capabilities:

  1. Analyze and forecast database resource consumption using long-term historical data and machine learning.
  2. Optimize resource usage by identifying and managing over and underutilized servers and moving workloads or adjusting resource allocations to maximize future performance.
  3. Provide out-of-the-box insights pertaining to underlying infrastructure-level capacity such as CPU, Storage, Memory and I/O.
  4. Identifiy outliers across the enabled resources for a given time period and utilize AutoML based forecasting algorithms to predict future demand of complex workloads.
Figure 2: Capacity Planning tab – Database capacity view
Figure 2: Capacity Planning tab – Database capacity view

The SQL Insights tab (Figure 3 provides an overview of the SQL workload executing in the database.  This includes basic information about the database (version, cores, instances) and the SQL (unique and average daily counts) collected periodically from the database. It also includes SQL Activity breakdowns of total time by command and module and the ratio of time spent in SQL or PL/SQL.  Insight tiles at the database level quantify SQL by types of insights (degrading, degraded with plan changes, new, improving SQL, etc.). SQL activity is shown by selected time period broken down by command type to provide insights into changes in workload type over time. Execute to parse ratio and SQL count and invalidations charts reveal important application workload characteristics over time.

Figure 3: SQL Insights tab – SQL performance view by database
Figure 3:  SQL Insights tab – SQL performance view by database

Triaging database performance issues with EM Cloud Extension’s SQL Insights

Let’s say you’re an application developer for web apps used by customers all around the world. Recently, customers have reported slow response time from the sales application as well as issues with data not being persisted. One way to investigate such performance issues is to start with the EM Cloud Extension’s SQL Insights tab in context of the database.

Based on the Top SQL table in the SQL Insights tab, the top culprit can be narrowed down to single or set of SQL IDs that may have caused database degradation. Clicking on one of the SQL_ID, opens the SQL analysis dashboard within OPSI’s SQL Insights application, to show the average latency by plan widget. The same results can be also be validated using a complimentary application such as SQL Explorer where the same data can be visualized.

Note: Use of Capacity Planning and SQL Insights features requires subscription to OCI Ops Insights service. Once the EM managed target is enabled for OCI Ops Insights service and configured through EM Cloud Bridge, the information on the Capacity and SQL Insights tile is populated and information is shown in the details page.

Get Started with ML-enriched Capacity Planning today

Find OPSI under the Observability and Management menu in the Oracle Cloud Console. The service is accessible through the Console, CLI, and REST API. OPSI is available to use with Autonomous Databases, external databases (Oracle Databases deployed on-premises), and Oracle Cloud Databases on bare metal, VMs, and Exadata Database Services on Dedicated Infrastructure and Exadata Cloud at Customer.

To test drive this feature and many other features in OPSI before even signing up for the service, try the demo mode using your tenancy or try demo mode by signing up for Livelabs.

Not yet a customer?  Sign up for an Oracle Cloud Infrastructure trial account!

Resources

For more blogs on OPSI, visit the Observability and Management – OPSI blog space.

OPSI customers can now use the newly launched OCI Generative AI powered Oracle Support Digital Assistant. The Digital Assistant eliminates the need to manually search through the O&M documentation, and provides with quick, accurate answers directly within the OCI console interface. In addition to the answering the query, it also provides references and links to exact sections of our comprehensive product documentation. Learn more about it here.