Analyze Database Resources

Operations Insights Capacity Planning functionality provides insight into resource and usage of your databases, thus allowing you to meet both peak and long-term database capacity for your organization.

Topics:

Typical Goals

As a database fleet administrator or capacity planner, the Capacity Planning application lets you perform the following use case goals at the compartment level for both CPU and storage resources.

What you want to do: How to get it done:
  • Obtain a birds-eye view of aggregate database CPU allocation, utilization and usage trends
  • Gain proactive insights into current CPU or Storage utilization issues or forecast to occur near-term
  • View fleet-wide distribution of allocated database CPU resources and their utilization levels
  • Trend and forecast aggregate CPU utilization for all or specific groups of databases
  • Trend and forecast aggregate Storage utilization for all or specific groups of databases
Compare CPU Utilization Between Databases
Trend and forecast CPU utilization for specific databases of interest CPU Details - Insights Table
Trend and forecast Storage utilization for specific databases of interest Storage Details - Insights Table
  • Obtain a birds-eye view of aggregate database Storage allocation, utilization and usage trends
  • View fleet-wide distribution of allocated database Storage resources and their utilization levels
Compare Storage Utilization Between Databases
  • Estimate the opportunity cost of resource allocation for highly variable CPU demand
  • Identify candidates for Oracle Autonomous DB auto-scaling of CPU
CPU Details - Trend & Forecast

Database Capacity Planning

Use the Capacity Planning/Database menu item to access a summary of database CPU, Storage, Memory, and I/O utilization across the selected compartment.

From the Database Capacity Planning Summary page, you can view current database resource utilization by CPU, storage, memory, and I/O . You can also compare and contrast resource usage between databases, and identify which databases use the most resources, and are expanding rapidly.


Image shows the Capacity Planning Summary page.

This page directly supports the goals of providing a birds-eye view of database resource usage and trends, and providing proactive insights into specific utilization issues either current or forecast for the near-term.

From this page you can perform the following tasks in support of the Capacity Planning use case goals:

  • View total allocation and utilization of CPU, Storage, Memory, and I/O resources for all (enabled) databases in the compartment
  • Identify top-5 databases of CPU, Storage, and Memory by absolute usage or utilization percentage
  • Identify top-5 databases by CPU, Storage, and Memory growth over the time period
  • See aggregated historical usage trends for CPU, Storage, and Memory over the time period
Note

Capacity Planning color scheme associates green with CPU resources and blue with Storage resources.

The Inventory section displays the total number of databases enabled for Operations Insights along with the database types. In addition, the CPU, Storage, Memory, and I/O usage charts display overall resource consumption (Top Consumers and Usage Trend) by these database targets.

The section shows the current utilization of databases and the databases that are reaching the server capacity headroom.

CPU Insights, Storage Insights, and Memory Insights.

These sections show the number of databases running with low (0–25%) and high (75–100%) utilization of resources. For CPU utilization, it is the utilization percentage for the 90th percentile value of the daily average CPU usage over the selected time period. That is, if the 90th percentile value of CPU usage is associated with utilization above the 75% threshold, then the database is considered high CPU utilization.

For Storage and Memory utilization, it is the databases forecast to reach high utilization (>75%) or maintain low utilization (<25%) within the 0-30 day time period. Forecast training data are based on the selected historical data time period.

You can drill down to view the CPU, Storage or Memory breakdown for either individual databases or for an aggregated view of all databases.

Analyze Database CPU Usage

You can analyze your CPU utilization, using the Database Resource Analytics application.

Analyze Available CPU Resources

For a complete view of CPU usage across all Operations Insights enabled databases, click CPU under Resources in the left navigation menu. This page has a master-detail design with three primary components:

  • Insights – table of databases flagged for CPU utilization insights
  • Aggregate – treemap of CPU utilization over all databases in the compartment
  • Trend & Forecast – time series charts of CPU usage trends and forecasts for individual or groups of databases

By default, you view comprehensive CPU usage for each database. Viewing the resource utilization for each database, lets you compare resource utilization between databases and identify servers with underused or overused resources.

Note

To view or use this CPU usage information outside of this service, Operations Insights allows you to download a CSV version.
Note

For specific details about the selected database, click the external link icon next to the database name in the Trend & Forecast chart. You'll be taken to the details page for that database.

Insights Table

The Insights table and Aggregate treemap are either-or choices for the top half of the page, which is used to drive the Trend & Forecast drill-down chart in the bottom half.


Graphic shows the insights table.

  • Database Types
    • ADW-D: Autonomous Data Warehouse Dedicated
    • ADW-S: Autonomous Data Warehouse Shared
    • ATP-D: Autonomous Transaction Processing Dedicated
    • ATP-S: Autonomous Transaction Processing Shared
    • External-NonCDB: External database that is not a container database
    • External PDB: External Pluggable Database
  • Utilization (%):Utilization percentage for the 90th percentile value of the daily average storage usage over the selected time period
  • Usage Change (%): Percentage change in the linear trend of storage usage over the selected time period
  • Usage (TB): 90th percentile value of the daily average storage usage over the selected time period
  • Allocation (TB): Allocation for the 90th percentile value of the daily average storage usage over the selected time period
  • Database Version: Version of the Oracle database.
  • Telemetry: The source platform of the database: Cloud Infrastructure, Enterprise Manager, or Agent Service
  • Instances: The number of instances. Click on the instance number to display a list of the instance and host names.

The table facilitates a number of tasks in support of Capacity Planning use cases:

  • Isolates databases with current or forecasted high (or low) utilization
  • Quickly compare these databases to each other by size or usage to prioritize analysis
  • Identify databases with fastest growth (shrinkage) in CPU demand over the time period

Selecting a database row in the Insights Table will display historical trends and future forecasts for CPU usage for that database in the lower half of the page, described next.

Trend and Forecast Chart

The Trend and Forecast chart displays several historical time series plots related to CPU allocation and usage for the selected database or group, as well as linear and machine learning trends and forecasts over these data. Trends are computed over the time period selected in the global context area.

The chart facilitates numerous tasks in support of Capacity Planning use cases:

  • Forecast future maximum and average demand for CPU resources
  • Compare current usage to allocation to detect over-provisioning
  • Compare maximum to average usage and trends to assess demand volatility
  • Forecast difference between maximum and average daily CPU usage to estimate potential savings from workload smoothing
  • Detect and forecast daily or weekly seasonality in CPU demand
  • See confidence channels around forecasts to contextualize variability

The following CPU data series can be selected or de-selected for display from the legend at right end of the chart:

  • Max Allocation - maximum allocation of CPU for the database (or group)
  • Max Usage - maximum value of daily (hourly) CPU usage data for database (or group)
  • Max Usage Forecast - forecast of Max Usage data using selected forecast model
  • Avg Usage - average value of daily (hourly) CPU usage data
  • Avg Usage Forecast - forecast of Avg Usage data using selected forecast model
  • Max Confidence Channel - confidence channel for Max Usage Forecast
  • Avg Confidence Channel - confidence channel for Avg Usage Forecast

Averages and maximums are over either daily raw data or hourly raw data depending on the global time context as follows: hourly aggregates are used when time period is within the last seven days, otherwise daily aggregates are used.

Forecasts periods are set to ½ the length of the trending time period, e.g. 30-day trends are projected 15 days forward, and 90-day trends 45 days forward.


Graphic shows trend and forecast chart.

The above chart shows daily average and maximum CPU usage over 30-days for the HR-Dev database. We see a consistent and considerable gap between the Avg Usage and Max Usage data series, indicating a high level of variability in CPU demand during the day.

The Max Usage trend and forecast are both very close or touching the Max Allocation for the database, indicating a risk of capacity exhaustion should demand increase or if new workload is introduced.

The difference here between forecast Max Usage (~29 Avg Active CPU) and forecast Avg Usage (~15 Avg Active CPU) is the difference between how much CPU is required to run all work safely and how much CPU is actually required to do all work. When CPU resources are “hard allocated” to databases, this difference represents the amount of resource being paid for minus the amount of resource being used, and is thus an “opportunity cost” induced by having highly variable CPU demand.

One potential way to mitigate this imputed cost of variability is by sharing CPU resources among several databases and allowing dynamic resource allocation among them in response to changing demand.

Oracle Autonomous Database with Auto-scale enabled offers precisely such a solution.

Seasonality Detection

The following chart shows 90-day trend and forecast for the SALES-WT database using the machine learning model:


Graphic illustrates seasonality detection.

We see that the machine learning model has detected strong weekly seasonality in both the Avg Usage and Max Usage time series, resulting in the “wavy” forecast lines. There is also a high level of variability in the data, as evidenced by the very wide confidence channels around the forecast lines.

By incorporating the undulating weekly seasonality of the data in the forecast, users are better able to anticipate peak demand requirements that can be smoothed away using linear forecasts.

Compare CPU Utilization Between Databases

You can compare CPU usage across multiple databases by clicking the Aggregate tab. Viewing the summary of resource utilization across all databases lets you identify points in time where there may have been unexpected spikes in resource utilization.

Aggregate Treemap

When the Aggregate tab is selected in the CPU Details page, the top half of the page displays a treemap of all databases enabled for Operations Insights in the compartment. By default, the chart shows you the CPU breakdown by individual database, which lets you compare how your different, individual databases are using their resources. To compare CPU resource usage across multiple databases, you can group the databases by either Database Type or Utilization Level.


Graphic shows the aggregate view for DB CPU usage

The treemap visualization shows the total compartment database fleet as a collection of rectangles dividing the entire visual canvas and sized in proportion to a sizing metric. Each database is a rectangle and relative sizes of rectangles are easily compared. The entire treemap represents the compartment total in the sizing metric, so the decomposition of fleet totals by database is the fundamental basis of this visualization. For example, the CPU utilization and the percentage change are represented visually, through the size and color of the cells, respectively. Cells that are larger in size utilize more CPU resource than smaller cells. The largest cell would be that of the databases using the most CPU resources. The color of the cells is determined by the percentage change in the CPU resources used by the databases.

Selecting a specific group amalgamates data for all members of the group to generate the Trend & Forecast chart, which allows you to view forecasted utilization for the particular resource along with the current utilization trend. You can also click on a single member of a group to display trending and forecasting for a single database.

The CPU treemap supports Capacity Planning use cases by enabling the following tasks:

  • Analyze the distribution of CPU allocation across databases
  • Analyze the distribution of CPU usage across databases
  • Analyze CPU usage and allocation distributions by utilization levels
  • Analyze CPU usage and allocation distributions by database type
  • Forecast average and maximum CPU usage grouped by utilization levels
  • Forecast average and maximum CPU usage grouped by database type
  • Identify databases using most CPU and whether usage is growing or shrinking
  • Identify the allocation footprint of under-utilized database CPU capacity
  • Rapidly scan through selected databases looking for anomalous trends/forecasts
  • Rapidly scan through selected databases for strong seasonality trends
  • Analyze the distribution of growing vs. shrinking database CPU usage by allocation

CPU Treemap Sizing

Two sizing metrics are offered for the Treemap:

  • Usage (Avg Active CPUs) – Larger database cells are bigger consumers of CPU. This sizing is especially useful in CPU-metered cloud settings as it is effectively the relative “burn rate” of those databases.
  • Max Allocation – Database cells are sized by CPU allocation. For “hard allocation” scenarios this is the relative fixed CPU cost of databases. For Autonomous auto-scaled databases, this is the maximum amount of CPU available (but not guaranteed) to the database by OCI.

These two options permit users of both Autonomous as well as on-premises and dedicated databases to assess the relative costs vs. utilization levels of their database fleet, as the differing cost models are both covered.

CPU Treemap Grouping

By default the CPU treemap shows All Databases as the group, meaning there is only the group composed of all databases in the compartment, and each database cell area sized proportionally to the sizing metric.

The treemap can be organized as a two-level hierarchy by selecting a Grouping property. Here, the treemap is divided first into groups and then into individual cells within the groups. This allows group size comparisons in addition to cell size.

The following options are available for the Grouping property:

  • Database Type
  • Utilization Level

Grouping by database type allows usage and allocation footprints to be compared by database type, while grouping by utilization level allows rapid evaluation of over-and-under-utilized allocation footprints.

CPU Treemap Coloring

The CPU treemap can be colored using the following two options:

  • Utilization (%) - Avg Usage as a percentage of Max Allocation over the time interval
  • Usage Change (%) - Percentage change in linear trend of Avg Usage over the time interval

The colors for Utilization (%) are shades of green (CPU branding) from light to dark in increasing quartiles: 0-25%, 25-50%, 50-75%, >75%. These can be seen in the graphic above.

The colors for Usage Change (%) use two color palettes as seen below. Positive changes in usage are colored in shades of green (CPU), light to dark with increasing positive values. Negative changes in usage are colored in shades of amber, light to dark with increasing negative values. This scheme is a bit more complex but captures the crucial distinction of positive from negative as well as the degree of change in either direction.


Graphic illustrates use of treemap coloring.

In the CPU Details Aggregate page pictured above, Treemap sizing is by Max Allocation and coloring is by Usage Change (%). The database cells are grouped by Database Type, and the ADW-D group (Autonomous Data Warehouse - Dedicated) is selected.

We see that the trend and forecasts for ADW-D database Max and Avg CPU usage are pretty flat, and with relatively low variance. However, within the group there are four databases, and two are growing in usage (green shades) and two are shrinking in usage (amber shades.) In addition, we can easily see that ADW-D databases account for approximately ¼ of the total database CPU allocation across the compartment. We can also drill down individually on the growing and shrinking databases within the group to analyze their specific trends and forecasts.

Analyze Database Storage Usage

Operations Insights lets you view current and predicted storage usage for both individual databases as across multiple databases.

Analyze Available Storage Resources

For a complete view of storage usage across all Operations Insights enabled databases, click Storage under Resources in the left navigation menu.

By default, you view comprehensive storage usage for each database. Viewing the storage utilization for each database lets you compare resource utilization between databases and identify servers with underused or overused resources. For each database, you view the following:

  • Database Types
    • ADW-D: Autonomous Data Warehouse Dedicated
    • ADW-S: Autonomous Data Warehouse Shared
    • ATP-D: Autonomous Transaction Processing Dedicated
    • ATP-S: Autonomous Transaction Processing Shared
    • External-NonCDB: External database that is not a container database
    • External PDB: External Pluggable Database
  • Utilization (%):Utilization percentage for the 90th percentile value of the daily average storage usage over the selected time period
  • Usage Change (%): Percentage change in the linear trend of storage usage over the selected time period
  • Usage (TB): 90th percentile value of the daily average storage usage over the selected time period
  • Allocation (TB): Allocation for the 90th percentile value of the daily average storage usage over the selected time period
  • Database Version: Version of the Oracle database.
  • Telemetry: The source platform of the database: Cloud Infrastructure, Enterprise Manager, or Agent Service
  • Instances: The number of instances. Click on the instance number to display a list of the instance and host names.

Graphic shows the storage insights for single databases

To view or use this storage usage information outside of this service, Operations Insights allows you to download a CSV version.

Analyze Trend and Forecast Storage Usage

Selecting a database from the storage usage table, you can view storage, the trend and usage forecast for that database. By default, trending and forecasting are calculated using linear regression. For more advanced analysis, you can have Operations Insights use machine learning to perform the trending and forecasting.

Storage charts analysis and usage are similar to those used for Capacity Planning. For an in-depth discussion about Capacity Planning charts, see:

Compare Storage Utilization Between Databases

Alternatively, click Storage from the Resources list in the navigation pane to gain insight into individual databases or click Aggregate to view storage utilization across multiple databases. When selecting a single database, you can also break down the storage by tablespaces (applies to External Databases only, this option not available for Autonomous Databases).


Graphic shows the aggregate storage usage for multiple databases.

The volume of storage resources used and the percentage change are represented visually, through the size and color of the cells, respectively. Cells that are larger in size use more storage resources than smaller cells. The largest cell would be that of the databases using the most storage resources. The color of the cells is determined by the percentage change in the storage resources used by the databases.

To analyze storage utilization across a subset of databases, use the Group menu to group databases according to Database Type or Utilization Level. Click on a specific group to view the trend and forecast for space utilization for all databases within the group.

For Operations Insights to forecast your resource utilization for a year, there must be at least 13 months of stored data. The forecast value is more accurate when there’s more data. You can use the current and forecast storage utilization to plan your database capacity.

Analyze Database Memory Usage

Operations Insights lets you view current and predicted memory usage for both individual databases and across multiple databases.

Analyze Available Memory Resources

For a complete view of memory usage across all Operations Insights enabled databases, click Memory under Resources in the left navigation menu.
Note

Memory is only applicable for external databases.

By default, you view comprehensive memory usage for each database. Viewing the memory utilization for each database lets you compare resource utilization between databases and identify servers with underused or overused resources. For each database, you view the following:

  • Database Types
    • ADW-D: Autonomous Data Warehouse Dedicated
    • ADW-S: Autonomous Data Warehouse Shared
    • ATP-D: Autonomous Transaction Processing Dedicated
    • ATP-S: Autonomous Transaction Processing Shared
    • External-NonCDB: External database that is not a container database
    • External PDB: External Pluggable Database
  • Utilization (%):Utilization percentage for the 90th percentile value of the daily average memory usage over the selected time period
  • Usage Change (%): Percentage change in the linear trend of memory usage over the selected time period
  • Usage (TB): 90th percentile value of the daily average memory usage over the selected time period
  • Allocation (TB): Allocation for the 90th percentile value of the daily average memory usage over the selected time period
  • Database Version: Version of the Oracle database.
  • Telemetry: The source platform of the database: Cloud Infrastructure, Enterprise Manager, or Agent Service
  • Instances: The number of instances. Click on the instance number to display a list of the instance and host names.

Analyze Trend and Forecast Memory Usage

Selecting a database from the memory usage table, you can view memory consumption as well as the trend and usage forecast for that database. By default, trending and forecasting are calculated using linear regression. For more advanced analysis, you can have Operations Insights use machine learning to perform the trending and forecasting.

Memory charts analysis and usage are similar to those used elsewhere in the Capacity Planning application. For an in-depth discussion about Capacity Planning charts, see:

Compare Memory Utilization between Databases

Click Memory from the Resources list in the navigation pane to gain insight into individual databases or click Aggregate to view storage utilization across multiple databases.

The amount of memory resources and the percentage change in the usage of memory resources are represented visually, through the size and color of the cells, respectively. The cells that are larger in size use more memory resources than the smaller cells. The largest cell would be that of the database using the most memory resources. The color of the cells is determined by the percentage change in the memory resources used by the databases.

To analyze memory utilization across a subset of databases, use the Group menu to group databases according to Database Type or Utilization Level. Click on a specific group to view the trend and forecast for memory utilization for all databases within the group.

For Operations Insights to forecast your resource utilization for a year, there must be at least 13 months of stored data. The forecast value is more accurate when there’s more data. You can use the current and forecast memory utilization to plan your database capacity.

Analyze Database I/O Activity

Operations Insights lets you view current and predicted Input/Output (I/O) activity for both individual databases and across multiple databases.

Analyze I/O Trend Over Time

To view I/O activity across all Operations Insights enabled databases, click I/O under Resources in the left navigation menu.

Note

I/O is only applicable for external databases.

By default, you view comprehensive I/O activity for each database. Viewing the I/O activity for each database lets you compare resource utilization between databases and identify servers with underused or overused resources. For each database, you view the following:

  • Database Types
    • ADW-D: Autonomous Data Warehouse Dedicated
    • ADW-S: Autonomous Data Warehouse Shared
    • ATP-D: Autonomous Transaction Processing Dedicated
    • ATP-S: Autonomous Transaction Processing Shared
    • External-NonCDB: External database that is not a container database
    • External PDB: External Pluggable Database
  • Usage Change (%): Percentage change in the linear trend of I/O usage over the selected time period
  • Usage (TB): 90th percentile value of the daily average I/O usage over the selected time period
  • Allocation (TB): Allocation for the 90th percentile value of the daily average storage usage over the selected time period
  • Database Version: Version of the Oracle database.
  • Telemetry: The source platform of the database: Cloud Infrastructure, Enterprise Manager, or Agent Service
  • Instances: The number of instances. Click on the instance number to display a list of the instance and host names.

I/O charts analysis and usage are similar to those used elsewhere in the Capacity Planning application. For an in-depth discussion about Capacity Planning charts, see:

Compare I/O Activity Between Databases

Click I/O from the Resources list in the navigation pane to gain insight into individual databases or click Aggregate to view I/O activity across multiple databases.

The amount of I/O activity and the percentage change in that activity are represented visually, through the size and color of the cells, respectively. The cells that are larger in size indicate more I/O activity than the smaller cells. The largest cell would be that of the database that presents the greatest amount of I/O activity. The color of the cells is determined by the percentage change in the I/O resources used by the databases.

To analyze I/O activity across a subset of databases, use the Group menu to group databases according to Database Type. Click on a specific group to view the trend and forecast for memory utilization for all databases within the group.

The volume and percentage changes in the I/O activity are represented visually through the size and color of the cells, respectively. Cells that are larger have greater I/O activity than smaller cells. The largest cell would be that of the database with the highest I/O activity. The color of the cells is determined by the percentage change in the I/O activity during the specified time period.