Databases

Unlock the Black-Box of Database Performance with i3 for Databases

Databases are very good about capturing detailed performance information about themselves. But they are not very forgiving when it comes to making that information readily available. In fact, to get to this information requires elite-level DBA skills. Even then, it is not always apparent which performance metrics contain the key to the problem.
Therefore, database performance is usually considered a black-box.

i3 for Databases provides unparalleled analysis of database performance metrics, buried deep in the internals of the DBMS system, and presents the information in an intuitive format that makes the metrics usable, obvious and actionable. The product will lead you directly to the root cause of a performance problem that exists in the database tier. Even more importantly is that the product will help you to make the appropriate determination when the problem is NOT in the database. i3 provides a consistent look-and-feel regardless of the platform, but also exploits each of the platforms unique characteristics.

The following represents examples of the common components that make up the standard i3 model.

Dashboard View - Oracle example
The Dashboard is the starting point for your indepth analysis since it displays an overview of all the instances available in the selected environment and information that is also available in some of the other workspaces.

The Dashboard workspace displays availability, statistics over time, and resource consumption of all database instances for the selected time frame. This workspace can be used to monitor your system's current state by viewing all the available instances, a list of all active sessions, and the resource consumption for the last minute (Current tab). In addition, you can view statistics over time, resource consumption over time, and top resource consumers within the selected time frame (on the Overview, Statistics, Application, and Activities tabs).

Activity Workspace - SQL Server example
The Activity workspace allows you to analyze performance behavior over time. The information displayed in the Activity workspace is derived from historical data and enables you to access information for different time intervals at varying levels of detail. You can analyze what happened at a specific time, pinpoint problematic time periods, and drill down to analyze a bottleneck that caused a performance problem.

The Activity workspace was designed to answer questions such as, “What happened yesterday, as well as a month ago and a year ago?” and “What has been the performance level of my applications over time?”

Data in the Activity workspace is available up to the last 15 minutes (information on the last minute is available in the Current workspace). The Activity workspace is usually the place to identify and investigate tuning problems.

Objects Workspace - Oracle example
It is important to understand the relationship between database objects and statements in order to make sound and well-founded tuning decisions.

The Objects workspace is a comprehensive browser that lets you understand the relationships and associations between database schema objects. It also provides further understanding into the relationships between data dictionary components and the statements that access them. This information lets you identify the statements that will be affected by changes to a database object, and the schema relationship impacts that are associated with an object change.

The Objects workspace also lets you observe storage utilization, over time and at varying levels, such as tablespace, table, index, and Oracle file. While the Activity workspace focuses on SQL tuning, the Object workspace focuses on database object tuning. In many cases, you may want to tune a database object rather than a specific query or program. This is especially true if you did not personally write the application in question, as for example, in ERP and CRM applications, and cannot therefore change the text of any statement.

Statistics Workspace - Sybase example
The Statistics workspace displays statistical information on all Sybase instances. The workspace can be used to monitor your system's historical statistical information.

The Statistics workspace lets you provide answers to the following types of questions: “Does Sybase have too many or too few Adaptive Server engines?” or “There are a lot of task context switches in the system that affect performance. What is the main reason for those context switches?”

The Statistics workspace provides hundreds of raw performance counters. It is possible to view status information in 15 minute intervals or historical information roll-up at a higher summary level. Examining the ready-to-use set of graphs that display several related counters is more important than examining a single counter. This is now possible using the Statistics workspace.

Use the Statistics workspace to periodically monitor the health of your system—for example, view the statistics on CPU Utilization or Hit Ratios. Alternatively, you can use the Statistics workspace to fully analyze a performance problem reported by the Collector agent.

SQL Workspace - DB2 example
The SQL workspace enables you to evaluate SQL statements and manage a database warehouse of your application's SQL statements together with their respective explain plans.

The process of explaining statements is a prerequisite for tuning. The explain process is designed to clarify the access path chosen for a statement and translate it into a visual medium.

After a statement is explained, explain results are stored in Performance Warehouse. This information includes the objects referenced by the statement and the operations performed on these objects. The statements are automatically explained every day. In addition, the top 30 resource-consuming statements, and the real execution plans, are automatically explained every 15 minutes.

Understanding the execution plan chosen by the database optimizer is extremely important when tuning your application. You can ensure optimal system performance by ensuring that the best plans are used for your queries.

i3 for Databases provides you with a special workspace just for this purpose—the SQL workspace. It provides several views of the same plan, making the process of analyzing long and complicated queries much easier to do.