Learn About Identifying Inefficient Databases

After you create and launch a database on Oracle Cloud Infrastructure, as a best practice you should monitor the performance of your database. Slow or erratic performance can affect your organization’s productivity, and it can be difficult to pinpoint without the necessary tools. You can use the analytics and dashboard features in Oracle Management Cloud to proactively identify inefficiencies, analyze the underlying causes, and initiate corrective action in minutes instead of days.

Inefficient databases are the databases that don't take full advantage of compute resources and are determined by active sessions for CPU, I/O, and inefficient wait time. Database performance problems can wreak havoc with your company’s productivity. Traditionally, database performance issues are identified when there’s a noticeable reduction in performance. At which point, the database administrator (DBA) begins troubleshooting possible causes, such as system resource allocation, processes with long execution times, and blocking issues. Manually collecting and reviewing logs and script output to identify the culprit is often complex and time consuming.

What if you can view all of your databases in a single console and use the power of artificial intelligence to view the most inefficient databases in minutes, not days? Oracle Management Cloud is designed to help you proactively manage your systems to identify inefficiencies and make corrections before there’s a noticeable impact to productivity. Dashboards display an overview of the status of your monitored databases and provide a summary of database inefficiency and performance degradation at a glance. You can quickly drill down to see details for a specific database. Use Database Performance Analytics to quickly locate the most inefficient databases at a glance and identify databases that are increasing in inefficiency for a given time frame.

The following are some of the Oracle Management Cloud terms you’ll come across:

Agents: collect data and logs from monitored entities and make the data and logs available in the Oracle Management Cloud console.
Entities: monitored technical assets such as databases, host servers, compute resources, or application servers.
License Editions: Pre-defined categories of Oracle Management Cloud offerings.

Before You Begin

Before you begin to identify inefficiencies in the performance of your databases that are in Oracle Cloud Infrastructure, you need one or more databases running in Oracle Cloud Infrastructure and an instance of Oracle Management Cloud.

Learn about related solutions:

Architecture

When you have Oracle Database systems running in Oracle Cloud Infrastructure, you can deploy an agent on the host where the database resides (where database is running) and use the Oracle Management Cloud console to provide real-time database performance information and trend analysis.

Oracle Management Cloud is a suite of autonomous management services for monitoring, managing, and securing applications and infrastructure. A console provides a single user interface for all managed entities. Oracle IT Analytics provides insight into performance, availability, and capacity of applications and infrastructure. Database Performance Analytics enables you to perform a historical, comparative analysis to find any bottlenecks affecting enterprise-wide database performance. You can also determine the root cause of recurring systemic problems, identify the performance profile and activity types for CPU or I/O intensive database activities across multiple databases to help understand trends.

The following architecture diagram illustrates how Agents in Oracle Cloud Infrastructure send database metrics through a gateway and virtual network to Oracle Management Cloud. End users log into the Oracle Management Cloud console to access the performance metrics and graphs in Database Performance Analytics.

Description of the illustration dbpa-arch.eps

After configuring and enabling Oracle Management Cloud - Enterprise Edition, the workflow to monitor database performance is:

Log into the Oracle Management Cloud console.
Select Database Performance Analytics from the Oracle IT Analytics menu.
View the Summary to identify an overview of database performance degradation and inefficiencies for a specified time period.
Select a database to view greater details, graphs, and findings.

About Required Services and Roles for Identifying Inefficient Database Performance

You should be familiar with Oracle Cloud Infrastructure and Oracle Management Cloud services. Database Performance Analytics uses the following services and roles:

A subscription to Oracle Cloud Infrastructure (this gives you the ability to create instances of various services).
An instance of Oracle Management Cloud - Enterprise Edition. Licensing is per entity.
One or more Oracle Database, Enterprise Edition systems that you created and launched in Oracle Cloud Infrastructure.
Enable the entities for Oracle Management Cloud - Enterprise Edition.

To enable an entity, install an agent on the Oracle Cloud Infrastructure system where your database is installed, discover your database, then enable Oracle Management Cloud - Enterprise Edition on both the Host (Oracle Cloud Infrastructure system) and the database.

These are the roles and privileges needed for each service.

Service Name: Role	Required to...
Oracle Management Cloud: `OMCEXTERNAL_ENTITLEMENT_ADMINISTRATOR`	Create Oracle Management Cloud instances. Assign Oracle Management Cloud instance roles to other users.
Oracle Management Cloud instance role privilege: `OMC <instance name> Administrator`	Add and enable Oracle Database entities for Oracle Management Cloud monitoring and Oracle IT Analytics. Set up monitoring and analysis. Manage Cloud Agents. Configure alert rules. Delete entities. Disable notifications on alerts (during maintenance periods). View and monitor infrastructure status and performance. Receive alert notifications and view alerts. Create and administer new log sources. Create and administer new log parsers.
Oracle Management Cloud instance role privilege: `OMC <instance name> User`	View and monitor infrastructure status and performance. Receive alert notifications and view alerts. Select targets, groups, or systems to explore. Search and analyze logs. Save and share log searches. Build custom dashboards.
Oracle Cloud Infrastructure: Service administrator role	Manage a specific Oracle Cloud service instance.

See Learn how to get Oracle Cloud services for Oracle Solutions to get the cloud services you need.