Resource Analyzer Reports

There are three basic Usage Analysis categories:

• Users. Provides a variety of information about the users at your site, such as how many requests each user submits, the dates and resource consumption of those requests, the data sources certain users are accessing the most, and whether they are issuing ad hoc or cataloged requests, summarized or detailed requests, and so on.
• Procedures. Gives a variety of information about the remote procedures used to submit requests, including the actual syntax of the requests.
• Data Sources. Shows how your data sources are being accessed, what kind of activity patterns are occurring at your site, and the earliest and latest data access dates. These reports show more detailed information, such as aggregate functions used against columns, and column use in relations, sorting, and grouping.

Usage Analysis reports can measure three types of usage:

• Frequency of Use. Focuses on how frequently particular data sources are accessed, users make requests, or procedures are executed.
• Resources Used. Focuses on the resources consumed by requests to particular data sources, by particular users, or with particular procedures.
• Historical Use. Shows data source, user, and procedure information over time.
• Top n. Shows usage data for the top number of users, procedures, or data sources specified for the Top n report. The available options for top number are 5, 10, 15, 20, and 25. The default value is 10.

One of the best techniques for improving system performance is to educate users about request preparation. Education involves familiarizing users with what data is available and what constitutes the designs of different data sources. If users understand what is required of them in executing requests, their expectations about system response will be more realistic. After educating users, you can then organize elements of the data according to user needs, taking into account efficient grouping and clustering of this data. Some questions to consider about users are:

• Are users familiar with the cardinality of the data sources and the impact of multiple joins?
• Have the profiles of the users been identified?
• Who are the novices, who are the experienced users, and what are their habits and performance expectations?
• Do users visit data very often or sporadically?
• Are user profiles predictable or do they operate in binary mode, where most of the time they impose no activity on the system but sometimes they consume every computer resource possible?

The Users reports help you analyze the activity of users at your site and determine such information as: whether users are novice or experienced, which data sources they are accessing most often, when they are using the most resources, and so on.

The Procedures reports supply a variety of information about the procedures with which users submit requests at your site. This information helps you compare the relative resource usage of cataloged versus ad hoc requests, find out which procedures are the most popular and frequently executed, and thereby determine the truly mission-critical applications run at your site.

In order to improve system performance at your site, it is helpful to determine which data sources are most mission-critical, most popular among your users, and most resource-intensive. The Data Sources reports show you how your data sources are being accessed, as well as illustrate various activity patterns at your site and the earliest and latest dates that data was accessed.

The Errors and Messages report provides an overview of error messages that Resource Management has monitored.

The following image shows the Errors and Messages report.

This report has one hyperlink that allows you to drill down to other reports, as described in the following table.

If you click the hyperlink in that report, you generate a final report which shows the error message in red. Click the red hyperlink to open the All Messages report, which shows all of the messages generated by the selected procedure.

The Procedure with Errors report provides information on error situations by Procedure Name.

The following image shows the Procedure with Errors report.

This report has one hyperlink that allows you to drill down to another report, as described in the following table.

If you click the hyperlink in the Requests Executed with Errors report, the Error Message report opens. This report shows all of the error messages generated.

The summary version of the report displays the domain name, the number of reports from the domain that have been monitored, the number of users that have run domain reports that have been monitored, the average execution time of all of the monitored reports in the domain, and the average CPU time used by all of the monitored reports in the domain. The following image shows the Domain Usage report.

This report has two hyperlinks that allow you to drill down to additional reports summarized in the following table.

It is very useful to determine in advance which users and applications will be most affected by impending data source schema changes. The Change Impact for Column reports determine how data-source modifications affect users and their applications.

Once you select the report, select the column for which you want to examine information under the Column name drop-down menu and click View Report. Any use of a column with that name in any monitored table is reported.

The report window opens, as shown in the following image. This report summarizes the procedures used to access the specified column.

This report has two hyperlinks that allow you to drill down to additional reports summarized in the following table.

Benchmarking is a process by which you determine whether your site's service level agreements (SLAs) are being met. Use the Procedure Benchmarks reports to identify response times for requests and other information for analyzing site performance.

From the Procedure Name drop-down menu, select the name of the procedure for which you want to examine information and click View Report.

The following image shows the Procedure Benchmarks report appears, as shown in the following image. This report shows information about executions of the specified procedure, including execution date and time, user, CPU and elapsed time, usage type, and client type. Click any of the hyperlinked column headings to sort the report by that column.

Some of the most helpful information Resource Analyzer can provide is about which requests are using the most resources. This kind of information helps you identify causes of network and machine-use bottlenecks and enables you to decide what data needs to be indexed, what requests need to be scheduled, or which users need to be educated. The Excessive Resources reports help you with this investigation.

For a request to be included in the report, it need only exceed one of the resource values specified, not all. Specify criteria to limit your report to information on requests that consume resource usage values above a certain average, and click View Report.

The following image shows the Excessive Resources report. This report lists the most resource-intensive procedures: those whose average resource usage values, including CPU seconds, elapsed time, I/Os, and rows returned, exceed at least one of the limits you specified. The report also lists the average resource usage values for each procedure. Click any of the hyperlinked column headings to sort the report by that column.

This report has one hyperlink that allows you to drill down to another report, as described in the following table.

Procedures with long running requests use excessive resources, a particular problem when dealing with operational systems. These procedures should be targeted for optimization.

The following image shows the Long-running Requests report. By default, the report is sorted by Average Elapsed Seconds and Average CPU Seconds.

This report has one hyperlink that allows you to drill down to another report, as described in the following table.

Large volume requests are requests that return more data than might be necessary. These requests can be targeted for optimization. You can identify these reports with the Large Volume Requests report.

The following image shows the Large Volume Requests report. By default the report is sorted by the highest Average Rows.

This report has one hyperlink that allows you to drill down to another report, as described in the following table.

Dormant data, or data that is very infrequently accessed, can slow response times on your site. Use the Dormant Data reports to identify which data sources and columns have been less recently or never queried and can be deleted or archived to improve performance.

The following image shows the Dormant Data report. This report shows a variety of information about each data source, such as number of requests, users, resource usage, and access dates. The Days Since Used column indicates the number of days that have passed since the data source was last accessed. By default, the report is sorted by access dates, with those data sources that have no access dates (and are therefore dormant) listed first, and includes all data sources in the application path, including those that aren't being monitored.

This report has one hyperlink that allows you to drill down to another report, as described in the following table.

Joining too many tables can be slow and costly. Denormalization can be an effective method for cutting CPU time and improving response times. The Excessive Joins reports help you determine which data is most frequently joined by user requests and help you decide how to plan your de-normalizing strategies.

The following image shows the Excessive Joins report. This report lists the most frequently joined data sources along with the procedures used to join them.

This report has one hyperlink that allows you to drill down to another report, as described in the following table.

Efficiencies can be created by calculating column values once and storing the results in summary tables for repeated use. This saves the cost of calculating these values every time a report is run.

The following image shows the Repeated Aggregations report. This report shows how many requests used different functions on certain columns of data sources, along with the dates of access. Click any of the hyperlinked column headings to sort the report by that column.

This report has one hyperlink that allows you to drill down to another report, as described in the following table.

Indexing columns that are frequently used in selection criteria for reports can improve efficiency. This report identifies columns that should be indexed.

This report shows how often columns were used as selection criteria in requests, as shown in the following image. Click any of the hyperlinked column headings to sort the report by that column.

For WebFOCUS language reports, for example, TABLE FILE, that are run against a relational data source, the drill-down options in this report lead users to a report that will show the original code, which is translated SQL with a drill-down link to any aggregation messages.

This report has one hyperlink that allows you to drill down to another report, as described in the following table.

If you drill down on the Command Count hyperlink, a report opens that lists the Command Names. If that column has an active drilldown, you can click that link and open the Original/Translated/Messages report, as shown in the following image.

The following columns are available:

If you click the drilldown in the Messages column, the report shows that the reason the request was not optimized is because optimization was disabled by the user, as shown in the following image.

The Bandwidth by Procedure report shows the number of bytes retrieved to the web server for requests. The report analyzes the bandwidth by database type for each year/month combination.

The following image shows the Bandwidth by Procedure report.

Resources by Connection report shows the resources consumed by the different IP connections.

The following image shows the Resources by Connection report.

This report has one hyperlink that allows you to drill down to another report, described in the following table.

These graph reports allow you to determine transaction-bottleneck periods of request activity or resource usage by different time periods.

You can filter a report by data source name and/or user name by selecting a valid data source name and/or user name from the drop-down menu, or by selecting ALL, to indicate all data sources and all users. The default is ALL. After you select your data sources or accept the default, click View Report.

Un-tuned queries frequently return more data than necessary. Identify the procedures running these requests and target them for optimization.

This report compares the number of requests to one of four factors of resource usage: CPU seconds, elapsed execution time in seconds, rows returned, and I/Os (all mapped on the vertical axis). This information is shown for each hour of the day, with the hours mapped along the horizontal axis. The values on the vertical axis are cumulative for the time period of the dates you select for Usage Monitoring data for reports. For more information on setting report date ranges, see How to Set Selection Criteria Dates for Reports.

By default, the first graph that appears shows requests graphed against CPU Time.

The following image shows the Query Volume vs. Resource Utilization Report.