DBspeed User Guide

First of all, if your need to read AWR & ASH reports of an Oracle database to check how database performance looks like, DBspeed is the right tool for you. DBspeed lays out more convenient steps to create AWR & ASH reports. To take one step further, it can provide the same time-serials analysis against AWR & ASH data without creating a bunch of reports.

Secondly, if you struggle with capacity planning questions like "what is the IOPS or MBPS at database level", or "what is the interconnect traffic for a RAC database", DBspeed is the right tool for you. DBspeed is designed to provide instant answers based on the AWR & ASH data.

Writing SQL statements to query AWR & ASH data is time-consuming, and sometimes, frustrating. DBspeed makes it easier and faster! It offers hundreds of data mining queries in a graphic user interface and allows you simply click to run.

DBspeed is NOT JUST for DBA. It is designed for all Oracle professionals who want to improve their productivity.

A read-only database account is sufficient to run data mining tool of DBspeed. You can grant SELECT_CATALOG_ROLE to this read-only database account which will cover all views accessed by DBspeed queries.

If you don't have this role available to the database account, you can grant permissions on individual views:
- The majority of data mining queries are ‘SELECT’ statements against DBA_HIST_* Views and please grant ‘SELECT’ permission on them.

You can use the following query to retrieve the view name and compile your 'grant' command:
select view_name from dba_views where view_name like 'DBA_HIST_%' order by 1;

- Some data mining queries offer options which require 'SELECT' permission granted on the following views:
DBA_USERS
DBA_SEGMENTS
DBA_DATA_FILES
DBA_OBJECTS
DBA_TAB_COLS

In order to create AWR and ASH reports, please grant ‘EXECUTE’ permission on package SYS.DBMS_WORKLOAD_REPOSITORY.

DBspeed works in a traditional client-server model. DBspeed is the client-side tool and usually runs on your laptop or workstation. The database which holds the AWR & ASH data to be analyzed is the at the server side. It is usually the production database which you would like to check. It could sit in the remote data center, or could be in the Cloud. DBspeed uses Oracle 11gR2 JDBC thin driver to make connection to the database.

Some companies replicate the AWR & ASH data from production database to a separate repository database to store for a longer period of time. DBspeed works with repository database as well. It automatically detects all the existing DBIDs in the AWR & ASH data and will prompt you to select which DBID you would like to perform analysis.

As shown in the following diagram, DBspeed is used to analyze performance data for target database A and B.

DBspeed runs on Windows 2000/XP/Vista/7 (release on Mac OS X is coming soon).

In order for DBspeed to run, Java Run Time environment 1.6 or higher is required in your environment. It is free and you can download from Internet.

DBspeed is a 'read-only' tool for the production database. All data mining queries are 'SELECT' SQL statements which run against a certain number of DBA_HIST views. You can always review the SQL statement before you run.

DBspeed is designed to have minimum performance impact on the production database. All the data mining queries have been fine-tuned by professional DBAs. Its footprint on the database is similar to creating AWR & ASH reports. Of cause, creating an AWR & ASH report across 10 snapshots or across 100 snapshots will incur different performance overhead to the database. Therefore, DBspeed users still need to be cautious in a production database environment.

When DBspeed connects to the database, its database session information will be displayed on the title bar. In case there is any concern, you can use that information to clean up the session.

DBspeed is designed to provide a much more flexible and efficient method to examine the AWR & ASH data. It offers data mining queries as the enhancement to Oracle AWR & ASH reports for the purpose of providing:

-   time-series analysis

One big limitation of AWR & ASH report is that one report can analyze performance data in one-time aggregate. In order to analyze the performance data over a series of time intervals, multiple reports have to be generated. It is not easy to search a bunch of reports for a specific piece of information, or try to make a summary out of them. With DBspeed, you have the option to run data mining queries either in a one-time aggregate or in a series of continuous time intervals such as day, hour, or AWR snapshot interval, etc. It is just one-click.

-   dimensional analysis

Another big challenge of reading AWR & ASH reports is how to make connections between the information provided by these two different reports. For example, ASH report shows a SQL statement in the 'top SQL' section while AWR report doesn't include it. How to interpret that? AWR or ASH report each has its own internally defined 'top' criteria to filter out performance data and it is difficult to keep track of the particular interested data from one report to the other. DBspeed is designed to line up AWR & ASH data by providing a dimension analytical model. The data mining queries sort both AWR and ASH data out according to dimensions like SQL, SQL plan, database object, etc. Those dimensions not only act as entry points to check database performance symptoms, but also serve as bridges to easily drill down from AWR data to ASH data, or vice versa. DBspeed allows user-defined search or filtering criteria to be added to data mining queries dynamically.

Oracle Enterprise Manager (OEM) is a comprehensive management tool set for Oracle database, database servers, etc. It offers real-time database performance monitoring features but lacks the capability of providing in-depth analysis of AWR & ASH data. DBspeed focuses on time-series analysis and dimensional analysis of AWR & ASH data in DBA_HIST views (usually called historical performance data).

The historical performance data is a subset of the real-time performance data (in V$ views) saved from database memory to disk. It offers an opportunity to detect and analyze database performance related issues under the context of longer period of time. It also serves for other purposes such as workload management, capacity planning, etc. With the automatic AWR snapshot process, database's real-time performance data can be saved as historical data periodically or on-demand.

DBspeed II provides more than 600 data mining queries for Oracle RAC databases while DBspeed I provides more than 500 for Oracle standalone databases. There is a small difference in the number between Oracle 10gR2 version and 11g version. DBspeed automatically detects the database version and provides the corresponding query set.

Most data mining queries DBspeed provides are 'base' queries, which means you can further customize them through the graphic interface. You can view the SQL statements for all the queries and copy/paste them to use elsewhere.

Oracle database has two different categories of performance data: cumulative statistics and sampled data.

Good examples of cumulative statistics are those available in DBA_HIST_SYSSTAT view. Cumulative statistics are information summed up at database instance level. Their value keeps growing as database activities go on and will be reset when database instance restarts. When cumulative statistics are examined, the delta value between two points of time is calculated and the amount of change is measured. Metrics are derived from cumulative statistics by calculating the rate of their changes against a time unit, or other units like transaction, database call etc.

Good examples of sampled data are those available in DBA_HIST_ACTIVE_SESS_HISTORY view. Oracle uses ASH framework to collect session level information by sampling active sessions on database. When sampled data is examined, their percentage within each sampling set and their frequency across multiple sampling sets are calculated. The significance threshold is therefore derived.

Because Oracle uses AWR as framework to save all performance data into DBA_HIST views, there is a tendency to treat ASH as a component of AWR. However, Oracle provides AWR report and ASH report as two separate tools to analyze performance data, which marks the clear differences between these two categories: cumulative statistics as AWR data and sampled data as ASH data.

Both categories of performance data are complimentary to each other in revealing the true image of database workload. DBspeed provides a dimension analytical model to line up AWR data and ASH data for time-series analysis. It organizes hundreds of data mining queries around dimensions so that it is easy to compare as well as contrast AWR & ASH data.

DBspeed doesn't provide graphic chart functions because the data mining queries are designed to display result data in a table format. The value in a table cell can be understood better in the context of 'intra-column' relationship as well as 'inter-column' relationship. For example, when troubleshooting the performance issue for a SQL statement during a particular period of problem-time, its 'response time' should be examined before the problem-time, during the problem-time, and after the problem-time. That is the 'intra-column' relationship in a time-serial analysis. In addition, other variables like 'the number of executions' and 'buffer gets' should also be reviewed because they could play a role in changing the 'response time' footprint of this SQL statement. That is the 'inter-column' relationship.

DBspeed promotes multi-variable and multi-dimension study when analyzing AWR & ASH data. It is not suitable to put different variables or dimensions in the same chart because their values could be very different. If you do want to have graphic charts, DBspeed can save the query report in a tab-delaminated plain text file which you can open directly from MS Excel spreadsheet to create chart.

Using data mining queries against AWR & ASH data to troubleshoot database performance issue has become a widely-adopted practice nowadays. There are many case studies published on the Internet. One of the most popular articles is from IOUG (International Oracle User Group).

"CPU Spikes and Top CPU SQL", SELECT Journal Q2 2011 of IOUG, Chicago, USA, p. 5-11

This article describes two case studies of how to use data mining queries against AWR & ASH to solve CPU spike issues on database servers. It advocates the time-serial analysis and dimension-based analysis of AWR & ASH data. DBspeed is built on the same theoretical foundation.

The uniqueness of DBspeed lies in its user-friendly graphic interface which organizes and presents data mining queries.

DBspeed can be purchased online with major credit cards, or through purchase orders. If you have any questions about the purchase or license information, please feel free to contact sales@dbspeed.com

You can try DBspeed for free for 30 days. The trial version is a fully functional version of the DBspeed product. When the trial period is approaching to the end, you will be prompted to make purchase decision.

DBspeed offers single-user, one-year subscription licenses. The licenses will be managed by license service vendor SoftwareKey. Customers can manage and renew their licenses from their accounts through customer portal.

Free software update is available for all customers with current licenses.

DBspeed offers 30 days of free trial. When the trial period expires, re-download the software will not extend the trial period. If you want to extend the trial period, please contact sales@dbspeed.com.

A login ID and a password will be provided in the invoice. They can be used to activate the software online.