With OneAgent installed on an application server, Davis®, the Dynatrace AI causation engine, continuously analyzes all database statements within the context of your applications. In this way, Dynatrace can tell you which statements need your attention.
Now, with Oracle database insights, we’re going even deeper, giving you visibility into what’s going on in the database layer. Oracle database insights not only point you to any underperforming statements, it also helps you understand why a statement is slow, which is often the answer to a million-dollar question.
Resolve performance problems rooted in the Oracle database layer
When Dynatrace detects that your application is slow due to a database query, you can start your analysis from the application perspective. Dynatrace helps you identify common problems like:
- N+1 issues – when the same query is executed multiple times
- Chatty database requests – requests that invoke too many database statements
- Slow requests – For example, due to a single lengthy SQL query
- Database access-related problems (for example, connection pool problems)
This is great, but what if a problem can’t be identified from the application perspective, which also makes it impossible to fix at the application layer? This is where database insights come in handy. With additional data from the database server, you’ll be able to resolve performance problems that are rooted deep in the database layer.
Why is your database statement slow?
Davis tells you which statements need to be improved to speed up your application. And now, with Oracle database insights, we track the 100 most time-consuming statements. For each statement, we provide you with 14 metrics. With this data, we give you actionable hints on how to solve problems. Here’s an example:
If you see that Wait time is responsible for the majority of a statement’s Elapsed time, select the Waits tab.
You’ll now get immediate information as to what kind of wait events are slowing down the query.
Most typically, it’s the User IO wait time. On the Most time-consuming Oracle statements page, you’ll be able to find the disk-heavy statements. Simply select the Disk tab—this gives you the list of disk-heavy queries.
If CPU time occupies most of the elapsed time, the problem is related to the query itself. Download the execution plan to analyze it. Sound like too much work? We’re with you—we’ll provide AI-powered automated analysis that you can use in such situations.
Oracle performance metrics
From the hundreds of available Oracle performance metrics, we’ve selected those that matter most. The metrics are self-explanatory, so there’s no need for a detailed discussion of each, but a few metrics are definitely worth mentioning:
- If your Buffer cache hit is low, the database disk is used instead of RAM.
- If there aren’t enough sorts in the memory, CPU and I/O resource consumption will be increased.
- The Oracle server should spend a majority of the time on SQL execution.
- High SQL parse time can indicate that you need to use more prepared statements with bind variables, rather than SQL statements that aren’t pre-compiled.
- Program global area (PGA) and System global area metrics help you control your Oracle memory usage.
All metrics, including those related to queries, can be charted using custom charts. The new custom alert page (available in Dynatrace version 1.176) will give you the ability to set alerts on all Oracle metrics, including those that are associated with statements.
Don’t forget—Oracle database insights works without OneAgent installed on the server. However, we strongly recommend using Dynatrace capabilities to the max and installing OneAgent on your Oracle host.
To free up your Oracle database resources, find the statements that are responsible for the greatest resource consumption. Use the tabs on the Most time-consuming Oracle statements page to select one of the predefined data sets broken down by resource type. If you need more than the predefined data sets, head over to the Custom tab, which is a powerful tool in the hands of an expert user.
Need to know more?
For all the technical details, prerequisites, and instructions for using this feature, see the Oracle database insights help page.
This is the first public release for database insights. Our next updates will focus on making Davis even more helpful and powerful.