Data Object Explorers

Operations Insights data object explorers provide an easy-to-use interface that lets you interactively explore and visualize detailed performance statistics stored in Operations Insights SQL Warehouse.

A data object explorer allows you to explore performance statistics via a SQL query to extract the data with which to create an intuitive visualization. The query is run against data objects, which are essentially curated views on top of the collected Operations Insights metrics. Operations Insights provides different explorers, each defined by the data object(s) they can access:

Data Object Explorer	Data Object
SQL Explorer	SQL Statistics Daily
Database Explorer	Database Load Daily Database Memory Daily Database Storage Daily Database Tablespace Daily Database Summary Daily Database Load Hourly Database Memory Hourly Database Storage Hourly Database Tablespace Hourly Database Summary Hourly
Host Explorer	Host CPU and Memory Daily Host CPU and Memory Hourly Host Processes
Exadata Explorer Note For Exadata Database Service on Dedicated Infrastructure targets, only Exadata Disk Hourly and Exadata Disk Daily data objects are supported.	Exadata Disk Hourly Exadata Disk Daily Exadata Cell Metric Daily Exadata Cell Metric Hourly Exadata IORM by DB on Flash Disks Daily Exadata IORM by DB on Flash Disks Hourly Exadata IORM by DB on Hard Disks Daily Exadata IORM by DB on Hard Disks Hourly Exadata IORM by PDB on Flash Disks Daily Exadata IORM by PDB on Flash Disks Hourly Exadata IORM by PDB on Hard Disks Daily Exadata IORM by PDB on Hard Disks Hourly
Data Object Explorer	All data objects listed above.

A data object explorer UI simplifies constructing a SQL SELECT statement by allowing you to select columns from an attributes list. Alternatively, you can enter SQL manually. The type-ahead feature lets you enter SQL for the query and SQL Explorer automatically provides inline drop-down list of attribute columns to complete the statement. Run the SQL query to extract raw data. You view the raw data returned by the constructed SQL SELECT query and then use this data to create an intuitive visualization that’s easy to consume and draw inferences from.

Accessing Data Object Explorers

Each data object explorer is accessed from within the context of its Operations Insights functional area as follows.

SQL Explorer

SQL Explorer only shows you SQL-related data objects.

Open the navigation menu, click Observability & Management. Under Operations Insights, click Oracle SQL Warehouse. You’ll be taken to the Operations Insights SQL Warehouse page.
From the left menu, click SQL Explorer.

Data Object Explorer

Data Object Explorer shows you all available data objects.

Open the navigation menu, click Observability & Management. Under Operations Insights, click Dashboards. You’ll be taken to the Operations Insights Dashboards page.
From the left menu, click Data Object Explorer.

Exadata Explorer

Exadata Explorer only shows you Exadata-related data objects.

Open the navigation menu, click Observability & Management. Under Operations Insights, click Exadata Insights. You’ll be taken to the Exadata Insights page.
From the left menu, click Exadata Explorer.

Using Data Object Explorers

From the a data object explorer page, you can begin exploring performance information about your monitored resources. The following graphic illustrates the functional regions of the UI.

Region	Description
1	Data Object, Compartment, and Time Range selectors: You set the scope of your session.
2	Attributes List: Select the attributes you're interested in exploring and add them to the query.
3	Query Text Entry (region 3): Manually enter the query to extract data you're interested in. The type-ahead feature helps you construct the query by providing in-context values.
4	Query Results/Raw Data (region 4): All data returned by the query in tabular format.
5	Visualization Center: Manipulate the raw data into an intuitive visualization.
6	Action menu: Save/Save as/Open/Create New/Delete a SQL query.

The following illustration shows the SQL Explorer UI.

Graphic illustrates the UI workflow for a data object explorer.

The following topics walk you through a typical data object explorer session. The step numbers correspond to the functional regions highlighted in the above graphic:

Step 1: Define the Scope

Steps 2 and 3: Construct a Data Object Explorer Query

Step 4: View the Raw Data Returned by the Data Object Query

Steps 5: Create an Intuitive Visualization

Step 6: Save and Share SQL Queries

Define the Scope

Using the drop-down menus shown in region 1 of the graphic, you define the context for the data object explorer session. This includes:

Data Object: The data object provides a curated view of the data stored in Oracle SQL Warehouse against which your SQL query will be run. For more information about data objects and availability, see Data Object Reference
Compartment: The OCI compartment containing the resources you’re interested in. To include data from all child compartments within the currently selected compartment, select the Include child compartments option.
Resource (Display Name): This filter allows you to choose one or more resources from the selected compartment on which to focus your query. The name of this filter changes according to the selected data object. For example, when the SQL Stats Daily data object is selected, the filter is titled Database (Display Name).
Tag filters: This filter allows you to select one or more tags to narrow your query to only those resources with specific tag resource keys and values.
Note

When you select one or more values from either the Resource (Display Name) or Tag filters, or make any updates to the filters, the selected filters will not be applied until you run the query.

Compartment resource filter and tag filters are not persisted into a saved search: Save and Save As operations will not save the filter selections to the saved query. These filters are intended to help you with quick, real-time data exploration.
Time Period: The period of time during which data has been collected

You can collapse/expand the scope region by clicking on the scope icon to the right of the Data Object Explorer title.

Construct a Data Object Explorer Query

Once you’ve defined the scope of your exploration session, you’re ready to construct the data object query (SQL SELECT) used to extract relevant data. By default, all information from the data object is extracted (SELECT *). The data object explorers simplify building your SELECT statement by allowing you to:

Select column query options from the Attributes menu.
Enter query text directly and take advantage of the UI type-ahead functionality

You progressively build queries against data objects by selecting attributes from the selection panel and mapping them to syntactical components of the query: SELECT list, WHERE clause filter, GROUP BY, and ORDER BY.

The SELECT statement you construct is a limited function query optimized to run against the selected data object and extract the most useful data with minimal complexity. The full range of SQL SELECT statement operations will not be compatible with the data object explorers and will generate an error when run. The UI guides you through SELECT statement creation.

Select Attribute Columns from the Attributes List

The data object you select determines what selectable columns appear in the attributes list.

This list is subdivided into the following categories:

Pinned: Frequently used attributes can be “pinned” into the top section of the attribute panel for ease of re-selection to avoid repeated scrolling through long attribute lists
Computed: Measures from a data object can be aggregated using common SQL aggregates like SUM, MAX, MIN, and AVG or combined mathematically using formulas to derive meaningful computed measures. These can be named and re-used which is especially useful for complex or verbose computations.
Measures: Attribute columns that provide numerical data you want to aggregate.
Dimensions: Attribute columns (such as key columns) that characterize the data. These are typically used to group data (GROUP BY operations).

Add an attribute from the list to the SQL query.

To add a column from the attributes list to your SQL query, click on the desired attribute column name (under Measures or Dimensions) in the attributes list. Click on the vertical ellipses to the right of the list attribute to display the Actions menu. Select Add to query and then where to add the column. For Dimension, you can add the attribute column to SELECT, WHERE, GROUPBY, or ORDERBY. For Measures, you can add the attribute column to SELECT or ORDER BY.

When adding a dimension to the WHERE clause, the Add to Where dialog queries the dimensions and then displays a list of values in the dialog. You can conveniently select the desired values to add them to the WHERE clause.

You can use the attribute list Search field to find specific columns.

Pin frequently used attribute columns.

As discussed earlier, you can pin frequently used attributes to the top of the attributes list for easy access. To pin an attribute, left-click on an attribute column in the list to display the Actions menu and select Pin. The attribute appears under the Pinned category at the top of the attributes list. You can select Unpin from the Actions menu to remove the attribute column from the Pinned category.

Create Computed Measures.

You can aggregate data using common SQL aggregates such as SUM, MAX, MIN, and AVG or combine mathematically using formulas to derive meaningful computed measures. To create a computed measure, click on the vertical ellipses of an attribute column from the Measures category to display the Actions menu. Select Add computation to display the Add computation dialog.

Graphic shows the Add computation dialog box.

Enter the required computation details. Setting substitution variables simplifies defining the Computation. Enter a unique Computed data name and click Add. The Computed data name appears under the Computed category in the attributes list.

Once a computed measure has been added to the attributes list, you can use its Actions menu to perform Delete, Add to query (SELECT or ORDER BY), or Pin/Unpin operations.

Enter query text directly and take advantage of a data object explorer’s type-ahead functionality.

You can also enter SQL directly into the query text entry fields. The following SQL Explorer example illustrates how the type-ahead function works when entering a SQL query.

Graphic shows the SQL query text entry region

The type-ahead feature lets you enter SQL while simultaneously providing an in-context list of attribute columns from which to select. You can also use the hot key (CTRL+SPACE) to display the list of attribute columns. You can collapse the text entry area into a read-only single line statement to free up screen real estate.

Note

SQL sub queries are not allowed. For example, embedding a SELECT statement within a WHERE clause or JOIN or UNIION operator.

You can additionally specify limit and amount of pages per query in the Data Explorer, use the Limit and Pages fields located at the far right, beneath the Run and Clear buttons. Limit will allow you to restrict the maximum (1000) number of results per page. Pages will display queried results into one or maximum of 5 pages, the pages number will be the maximum number of pages returned.

Note

Select * query will only return one page regardless of the Pages setting.

View the Raw Data Returned by the Data Object Query

Once you’ve created your data object query SELECT statement, click Run to test the query. Data will be extracted and displayed in tabular format.

Graphic shows raw data extracted using the SQL query.

Modify and re-Run the SELECT statement, if necessary, until the information you’re interested in exploring is extracted.

If you need to start over and create a new SELECT statement from scratch, click Clear.

Create an Intuitive Visualization

Once your SQL query returns the columns you’re interested in, you can now choose how you want the data displayed by selecting a visualization chart type.

There are four visualization chart types from which to choose:

Line Chart
Area Chart
Bar Chart
Pie Chart
Table

To render visualization output,

Select a Chart Type from the drop-down menu in the Visualization region. The Table chart type is selected by default.
Set the visualization parameters:
For Line, Bar, or Area Chart Type

Choose the desired metrics you want to use for the X and Y-axis. For additional clarity when plotting multiple databases, you can use the Color By menu to increase visual differentiation. The Color By option also acts as a grouping (splits into individual series) based on the field selected. You can also choose to include a chart Legend and Y-axis Title.

For a Table Chart Type
1. Choose the columns you want to include. Click in the Columns region to display a drop-down list of columns returned by your SQL query and select a column. When the first column is added, the visualization is rendered.
2. Continue adding columns until the rendered visualization displays the precise information you’re interested in. If desired, you can select the All Columns option to include all columns returned by your query. To remove columns from the rendered visualization, click the “x” to the right of a column name in the Columns region.

Note

To increase screen real estate for your visualization, click Hide raw data.

Save and Share SQL Queries

You can save a SQL query at any point and continue working on it at a later time. You may also want to save a completed query and share the query as a search widget for future use by you or other users.

If you’ve created the widget based on a fixed time range, then every time that you open the widget, it will show the results for the time range that you specified in the search. By default, if you’ve created the widget for a relative time range (say the last 7 days), then every time that you open the widget, it will show the up-to-date results as per the time selector (Last 7 days). When the widget is referenced on a dashboard, it will reflect the page time selector setting.

Prerequisites:

For information regarding the Management Dashboard policies that assign the permissions required to work with dashboards, see Details for Management Dashboard.
For Management Dashboard API documentation, see Management Dashboard API.

The SQL Explorer Action menu allows you to save and share your SQL searches with others.

Save a Completed Search and Add It to a Dashboard

To save a SQL query and add it to a dashboard:

From the Actions menu, select Save. The Save Search dialog displays.
Select a compartment in which to save the search.
Note

There is no association between the selected compartment of the saved search and the underlying data being queried.
Enter a Name and Description for the widget.
Optionally, you can save the search widget and add it to a dashboard.
Click the Add to dashboard option.
Chose whether you want to add the widget to an existing dashboard (select the Dashboard Compartment and specific Dashboard) or create a new dashboard and supply the required information.
Note

Operations Insights provides the following out-of-box dashboards for Exadata:
- Exadata Cell Dashboard - Hourly
  Hourly granularity dashboard for cell disk metrics.
- Exadata IORM Dashboard - Hourly
  Hourly granularity dashboard for Exadata IORM-related metrics.
Click Save.

Open a Previously Saved or Predefined Search

From the Actions menu, select Open. The Open Search dialog displays.
Choose a previously saved search or one of the predefined search widgets that comes with SQL Explorer.
Click Open.

Delete a Saved Search

From the Actions menu, select Delete. The Delete Search dialog displays.
Choose a previously saved search.
Click Delete.

Important: If you delete a saved search widget that is currently referenced by an existing dashboard, be aware that it's not automatically deleted from the dashboard. This will result in an error when accessing the dashboard.

Note

You cannot delete a predefined out-of-box search.

Create a Saved Search Query from an Existing One

You can create a customized search from an existing one using the Save As option.

From the Actions menu, select Open. The Open Search dialog displays.
Choose a previously saved or predefined search from the list.
Click Open. The search criteria is displayed in SQL Explorer.
Update the search criteria as needed.
From the Actions menu, select Save As.
Enter a new name for the updated search.
Click Save.

Update the Name and Description of an Existing Search

While developing a SQL query, you may want to change the name and associated descriptive information of the search widget You can use the Edit option to make these changes.

From the Actions menu, select Open if you are updating an existing search widget. The Open Search dialog displays.
Select a compartment where the search widget resides.
Note

There is no association between the selected compartment of the saved search and the underlying data being queried.
Select a search widget and click Open to display the search in SQL Explorer.
From the Actions menu, select Edit. The Edit Search dialog displays.
Enter a New Search Name and/or New Search Description.
Click Edit to save the changes.

Create a New Search

To clear existing search criteria and start a new search, select Create New from the Actions menu.