Explore performance using SQL Explorer in Oracle Cloud Infrastructure Operations Insights

July 6, 2022 | 4 minute read
Murtaza Husain
Director, Product Management - Management Cloud
Text Size 100%:

SQL Explorer is a new reporting enhancement now generally available in the Oracle Cloud Infrastructure (OCI) Operations Insights service complementing the SQL Warehouse capability. SQL Explorer provides an easy-to-use interface that lets you interactively explore and visualize detailed performance statistics stored in Operations Insights SQL Warehouse.

The OCI Operations Insights (OPSI) service, part of the Oracle Cloud Infrastructure Observability and Management platform, enables proactive performance and capacity management of hybrid cloud databases, Oracle Autonomous Databases and Exadata, and host infrastructure. OPSI enables you to easily analyze CPU and storage resources, forecast capacity issues, and proactively identify SQL performance issues. OPSI extends operational observability using telemetry from within and beyond the cloud, across many targets, and provides historical and long-term data analysis and machine learning in support of AIOps.

 

Graphical user interface, applicationDescription automatically generated
Figure 1: OPSI SQL Explorer user interface

OPSI service’s SQL Explorer offers interactive data exploration and visualization for deep exploration of application SQL performance statistics. SQL Explorer enables users to interactively query and visualizes detailed SQL performance statistics at the SQL_ID level over larger and more complex workloads and for longer periods than what was previously available. The user interface is designed to simplify and streamline query development, with built-in integration and user experience mechanisms to help the user do more work and perform it faster.

SQL Explorer uses SQL query syntax to guide users with interactive exploration of SQL Warehouse data objects.  Data objects encapsulate and combine raw SQL performance telemetry with entity configuration data for consumption by SQL Explorer.

OCI Operations Insights SQL Explorer Example Use Cases

DevOps, application, and database administrators can perform common use cases by querying SQL Warehouse data objects:

Top SQL – Identify top SQL by performance and resource consumption measures over a day, week, or month. Compare top SQL from different days to investigate workload changes over time.

Application footprint – Aggregate total resource demands overall SQL by the module to compare different application components to each other. Break these down further by SQL command type to determine which statements dominate within the module.

SQL performance history – Track performance of specific SQL over time to observe degradation or identify unusual variances.

These are just a few examples of how SQL Explorer can help you gain more insights into the scale and nature of a workload as well as any SQL it contains.

SQL Explorer Functionality for OCI OPSI

SQL Explorer accesses data objects with an easy-to-use user interface (UI) for authoring and visualizing queries for those objects. The UI is designed to simplify and expedite the formulation of analytic-type queries over SQL Warehouse data objects.  It includes mechanisms to assist users including:

  • Click dimension and measure columns into query syntax
  • Code completion and quick column search
  • Create computed columns and pin columns for repeated selection
  • Easy wipe clean and start over

SQL Explorer's easy-to-use interface reduces keystroke input and enables rapid and iterated exploration of SQL performance data.

Query output can be displayed in tabular form or using graphical data visualizations to reveal greater “insight”.

Queries can be saved, shared, and re-executed.

SQL Statistics Data Object Daily Summary

This release includes SQL Statistics Daily, a data object exposing a daily summary of SQL performance statistics at the SQL_ID level for an entire fleet of databases.

This data object also joins in important dimension attributes like entity configuration information and time components that are key to the aggregation and filtering of data.

Dashboard Widgets

Saved queries can be linked to data visualizations and combined into widgets, which can be added to dashboards with similar widgets from other OCI Observability and Management services.

Dashboards offer comprehensive and customized enterprise-level observability.

Summary

SQL Explorer is a powerful new capability added to the OPSI service that enables deep investigation of SQL performance history, at both the individual statement level and in aggregate.

Try Operations Insights today!

You can find Operations Insights under the Observability and Management menu in the Oracle Cloud Console. The service is accessible through the Console, CLI, and REST API and is available today in all commercial regions and coming soon to Government regions.

OPSI is available to use with external databases (Oracle Databases deployed on-premises), Oracle Cloud Databases on OCI Bare Metal, VMs, Exadata Cloud service. Support for Exadata Insights for Exadata Cloud@Customer is available and for Exadata Cloud Service is coming soon.

If you're not already using the Oracle Cloud, sign up for an Oracle Cloud Infrastructure trial account! Follow the links below for more information about OPSI and SQL Explorer.

Resources

Murtaza Husain

Director, Product Management - Management Cloud


Previous Post

Govern your OCI organization with governance rules: Part 2 of 3

Tim Tam | 6 min read

Next Post


How to address identity sprawl with a unified identity and access management platform

Rachel Nizinski | 4 min read