In this presentation, we're going to walk through the database dashboards and explain how to view the overall health of the database instance, as well as solve all the database mysteries that usually concern DBAs. Let's talk about the most common database performance mysteries and the typical questions that DBAs have when performing their daily tasks.
So what's important to a DBA? Is it the SQL statements that consume the highest database time? Can a DBA investigate using historical time-based analysis? Or how to understand the correlation between database changes and the database workload if an index was dropped or a parameter was changed? Or how to analyze execution plans, which can be quite complicated to understand? And how to compare between a good and a bad plan?
DBAs are generally concerned about performance tuning. What causes databases to be slow? Is it CPU time or non-ideal rate events or both? It's possible that the total database time is consumed by active sessions. But what is the overall goal for the DBA? I mean, the DBA is looking to reduce database time and maintain good database health.
Before we step into solving problems, you should be aware that Foglight for SQL Server and Oracle comes with SQL Performance Investigator. What is SQL PI? The PI tool provides unique multi-dimensional diagnostic capabilities. It has advanced analytics around the change tracking, compare, and explain plan features. The agent configuration is quite simple to enable. The agent can collect data remotely and it uses embedded repository post graphs to store the SQL performance data. PI supports all Oracle and SQL Server versions and configurations.
And now a brief overview of the Foglight for cross-platform databases dashboard. Now this dashboard gives you a visual representation of all databases in a single pane. From this dashboard, you can configure database's agents. It covers the entire monitoring process starting with the discovery of database instances and the connection with these instances plus the global administration of database agents.
With SQL PI configured, you'll have the ability to performing more in-depth analysis and investigation of an instance activity. For example, if I click on one of the Oracle instances and go to the SQL PI, the performance tree provides access to any of the key dimensions associated with the database activity. This is based on polar multi-dimensional models and an instance view of an instance activity. In this video, I'll show you specifics on Oracle database activity and how to diagnose issues in the database.
So let's talk about how a DBA can solve these mysteries. The database performance problems. So can a DBA resolve performance degradation due to blocking lock issues? Well, first of all, one should have an overview of database locks. Why locks? Because database needs to support multiple user applications used to ensure database consistency and integrity. These affect the interaction of readers and writers. Every database management system has its own implementation of a locking mechanism.
To identify blocking locks issues, select Locked Objects. The analysis displays all the locks that took place within the selected time range. In this case, we have two locks within the time range of eight hours. You can further drill down to the specifics of the locked objects for-- well, for example, in this case, I can drill down to the loaded object which is Quest from PPC parameters. It displays the locked trees, including the details for the blocker and the block session.
You can select the Blocking History tab. The Blocking History tab provides the details with the event start date, the instance name, session ID, serial number, the status, whether it's in a blocking or block state, and the associated SQL statement for the locked object.
Now that we have information related to a program, SQL text, and the client machine, the DBA can investigate the problem from this perspective. So let's take a look at how a DBA can identify performance degradation due to excessive I/O activity. To investigate and analyze I/O-related rate events and statistics, click the object I/O tree. This view gives details on objects that have high physical or logical reads.
Block changes give you the total number of changes that were part of an update or delete operation that were made to all the blocks in the SGA. So how can DBAs correlate between database performance degradation and changes in the environment? With SQL PI's advanced analytics, DBAs we'll be able to track changes and correlate them with the database workload.
So if I select the SQL statement, for instance, and go to the Advanced Analytics tab, the dots on the resource consumption graph shows multiple database changes within the specified time range. In this case, there are multiple changes around the Oracle configuration. There are 423 changes around the Oracle schema. And the below table gives the details with the time specified, the description of the changes, and the category that it's in.
Foglight clearly gives a deep dive perspective on SQL performance metrics. But how can a DBA easily interpret execution plans and identify if a SQL statement has been very costly and has been causing performance issues? And to analyze an execution plan, select the SQL statements. Now take a look at the SQL Statements table on the right. This gives you all the top SQL statements in the specified time range, which statements have been most active and the average SQL response time.
To investigate further, you can select View SQL Text to see the complete SQL script. To understand the execution plan, select Analyze Plan. You can view the total CPU cost of the SQL statement in three different perspectives-- Plan Details, Operation Analysis, and Object Analysis.
The Plan Details and Operation Analysis tabs gives costs associated with the operation and the object name. The Object Analysis tab gives further details on the object name, metrics on the blocks, number of rows that has accessed, and the size of the data the query has accessed. Let's take it one step further.
How can a DBA understand performance degradation due to the execution plan changes? DBAs will be able to compare execution plans for different time ranges by selecting Compare Plans. It gives the DBA a plan from a different time range perspective. They can further analyze if the SQL statement has been costly or performing better, or if the statement needs to be improved.
Foglight also comes with a great feature called Compare, where DBAs can compare performance between production and test environments. Compare between two instances or compare between two time slices.
To compare instances of dimensions, first select the SQL Statements tree. Use the Compare option here. In this example, I'm going to compare results based on workload and compare to the same time range starting on a specific date. Click Compare. The comparisons statistics are displayed on the right-hand side. This gives you a good perspective on the instance performance between two different dates.
Similarly, other dimensions can be compared. For example, the comparison results can be based on workload, CPU, user I/O, application, and more. Another common example is to compare the configuration between two instances or between a production and a UAP environment. Finally, the overall goal of a DBA is to tune database performance.
Foglight comes with multiple advisories. In this view, you can see the overview has been selected. Click on the Advisories to go to the Advisory section. For this specific instance, there is one action plan.
So when I select the advisory, I get a description of the problem, analysis on the data collected, and perspective to help investigate the problems. And further, Foglight provides recommendations and actionable steps in order to improve performance.
To summarize, Foglight for cross-platform database provides a single pane view for database monitoring and management. It also provides deep drill-downs and multi-dimensional diagnostic capabilities.
In this video, we've covered how to perform a database health check, including how to investigate blocking lock issues, SQL statements causing excessive I/O activity, understanding and correlating performance degradations in database changes, understanding execution plans, identifying performance issues due to execution plans and changes, comparing performance between production and test environments and between different instances, and steps to tune the database performance using Foglight advisories.
Thank you for watching this video.
10:02