184 lines
12 KiB
HTML
184 lines
12 KiB
HTML
|
<?xml version="1.0" encoding="UTF-8"?>
|
||
|
|
<!DOCTYPE html
|
||
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow" />
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="DC.Type" content="reference" />
|
||
|
|
<meta name="DC.Title" content="Analyzing summary monitor information" />
|
||
|
|
<meta name="abstract" content="Once data has been collected in the monitor, it can be analyzed." />
|
||
|
|
<meta name="description" content="Once data has been collected in the monitor, it can be analyzed." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqnavsummon.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="rzajqanalyzesum" />
|
||
|
|
<meta name="DC.Language" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>Analyzing summary monitor information</title>
|
||
|
|
</head>
|
||
|
|
<body id="rzajqanalyzesum"><a name="rzajqanalyzesum"><!-- --></a>
|
||
|
|
<img src="./delta.gif" alt="Start of change" /><!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Analyzing summary monitor information</h1>
|
||
|
|
<div><p>Once data has been collected in the monitor, it can be analyzed.</p>
|
||
|
|
<div class="section">You can analyze information in a summary monitory by right-clicking
|
||
|
|
the summary monitor in the right pane and selecting <span class="uicontrol">Analyze</span>.
|
||
|
|
A summary monitor must be ended or paused in order to analyze the data.</div>
|
||
|
|
<div class="section"><p>The following is an overview of the information that you can obtain
|
||
|
|
from the predefined reports.</p>
|
||
|
|
<dl><dt class="dlterm">General Summary</dt>
|
||
|
|
<dd>Contains information that summarizes all SQL activity. This information
|
||
|
|
provides the user with a high level indication of the nature of the SQL statements
|
||
|
|
used. For example, how much SQL is used in the application? Are the SQL statements
|
||
|
|
mainly short-running or long running? Is the number of results returned small
|
||
|
|
or large?</dd>
|
||
|
|
<dt class="dlterm">Job Summary</dt>
|
||
|
|
<dd>Contains a row of information for each job. Each row summarizes all SQL
|
||
|
|
activity for that job. This information can be used to tell which jobs on
|
||
|
|
the system are the heaviest users of SQL, and hence which ones are perhaps
|
||
|
|
candidates for performance tuning. The user may then want to start a separate
|
||
|
|
detailed performance monitor on an individual job to get more detailed information
|
||
|
|
without having to monitor the entire system.</dd>
|
||
|
|
<dt class="dlterm">Operation Summary</dt>
|
||
|
|
<dd>Contains a row of summary information for each type of SQL operation.
|
||
|
|
Each row summarizes all SQL activity for that type of SQL operation. This
|
||
|
|
information provides the user with a high level indication of the type of
|
||
|
|
SQL statements used. For example, are the applications mainly read-only, or
|
||
|
|
is there a large amount of update, delete, or insert activity. This information
|
||
|
|
can then be used to try specific performance tuning techniques. For example,
|
||
|
|
if a large amount of INSERT activity is occurring, perhaps using an <span class="cmdname">OVRDBF</span> command
|
||
|
|
to increase the blocking factor or perhaps use of the <span class="apiname">QDBENCWT</span> API
|
||
|
|
is appropriate.</dd>
|
||
|
|
<dt class="dlterm">Program Summary</dt>
|
||
|
|
<dd>Contains a row of information for each program that performed SQL operations.
|
||
|
|
Each row summarizes all SQL activity for that program. This information can
|
||
|
|
be used to identify which programs use the most or most expensive SQL statements.
|
||
|
|
Those programs are then potential candidates for performance tuning. Note
|
||
|
|
that a program name is only available if the SQL statements are embedded inside
|
||
|
|
a compiled program. SQL statements that are issued through ODBC, JDBC, or
|
||
|
|
OLE DB have a blank program name unless they result from a procedure, function,
|
||
|
|
or trigger.</dd>
|
||
|
|
</dl>
|
||
|
|
<p>Additionally, you can select more Detailed Results:</p>
|
||
|
|
<dl><dt class="dlterm">Basic statement information</dt>
|
||
|
|
<dd>This information provides the user with basic information about each SQL
|
||
|
|
statement. The most expensive SQL statements are presented first in the list
|
||
|
|
so at a glance the user can see which statements (if any) were long running.</dd>
|
||
|
|
<dt class="dlterm">Access plan rebuild information</dt>
|
||
|
|
<dd>Contains a row of information for each SQL statement that required the
|
||
|
|
access plan to be rebuilt. Reoptimization will occasionally be necessary for
|
||
|
|
one of several reasons such as a new index being created or dropped, the apply
|
||
|
|
of a PTF, and so on. However, excessive access plan rebuilds may indicate
|
||
|
|
a problem.</dd>
|
||
|
|
<dt class="dlterm">Optimizer information</dt>
|
||
|
|
<dd>Contains a row of optimization information for each subselect in an SQL
|
||
|
|
statement. This information provides the user with basic optimizer information
|
||
|
|
about those SQL statements that involve data manipulation (Selects, opens,
|
||
|
|
updates, and so on) The most expensive SQL statements are presented first
|
||
|
|
in the list.</dd>
|
||
|
|
<dt class="dlterm">Index create information</dt>
|
||
|
|
<dd>Contains a row of information for each SQL statement that required an
|
||
|
|
index to be created. Temporary indexes may need to be created for several
|
||
|
|
reasons such as to perform a join, to support scrollable cursors, to implement
|
||
|
|
ORDER BY or GROUP BY, and so on. The created indexes may only contain keys
|
||
|
|
for rows that satisfy the query (such indexes are known as sparse indexes).
|
||
|
|
In many cases, the index create may be perfectly normal and the most efficient
|
||
|
|
way to perform the query. However, if the number of rows is large, or if the
|
||
|
|
same index is repeatedly created, you may be able to create a permanent index
|
||
|
|
to improve performance of this query. This may be true whether an index was
|
||
|
|
advised.</dd>
|
||
|
|
<dt class="dlterm">Index used information</dt>
|
||
|
|
<dd>Contains a row of information for each permanent index that an SQL statement
|
||
|
|
used. This can be used to quickly tell if any of the permanent indexes were
|
||
|
|
used to improve the performance of a query. Permanent indexes are typically
|
||
|
|
necessary to achieve optimal query performance. This information can be used
|
||
|
|
to determine how often a permanent index was used by in the statements that
|
||
|
|
were monitored. Indexes that are never (or very rarely) used should probably
|
||
|
|
be dropped to improve the performance of inserts updates and deletes to a
|
||
|
|
table. Before dropping the index you may also want to look at the last used
|
||
|
|
date in the Description information for the index.</dd>
|
||
|
|
<dt class="dlterm">Open information</dt>
|
||
|
|
<dd>Contains a row of information for each open activity for each SQL statement.
|
||
|
|
The first time (or times) a open occurs for a specific statement in a job
|
||
|
|
is a full open. A full open creates an Open Data Path (ODP) that will be then
|
||
|
|
be used to fetch, update, delete, or insert rows. Since there will typically
|
||
|
|
be many fetch, update, delete, or insert operations for an ODP, as much processing
|
||
|
|
of the SQL statement as possible is done during the ODP creation so that same
|
||
|
|
processing does not need to be done on each subsequent I/O operation. An ODP
|
||
|
|
may be cached at close time so that if the SQL statement is run again during
|
||
|
|
the job, the ODP will be reused. Such an open is called a pseudo open and
|
||
|
|
is much less expensive than a full open. You can control the number of ODPs
|
||
|
|
that are cached in the job and then number of times the same ODP for a statement
|
||
|
|
should be created before caching it.</dd>
|
||
|
|
<dt class="dlterm">Table scan</dt>
|
||
|
|
<dd>Contains a row of information for each subselect that required records
|
||
|
|
to be processed in arrival sequence order. Table scans of large tables can
|
||
|
|
be time-consuming. If the SQL statement is long running, it may indicate that
|
||
|
|
an index might be necessary to improve performance.</dd>
|
||
|
|
<dt class="dlterm">Sort information</dt>
|
||
|
|
<dd>Contains a row of information for each sort that an SQL statement performed.
|
||
|
|
Sorts of large result sets in an SQL statement may be a time consuming operation.
|
||
|
|
In some cases, an index can be created that will eliminate the need for a
|
||
|
|
sort.</dd>
|
||
|
|
<dt class="dlterm">Temporary file information</dt>
|
||
|
|
<dd>Contains a row of information for each SQL statement that required a temporary
|
||
|
|
result. Temporary results are sometimes necessary based on the SQL statement.
|
||
|
|
If the result set inserted into a temporary result is large, you may want
|
||
|
|
to investigate why the temporary result is necessary. In some cases, the
|
||
|
|
SQL statement can be modified to eliminate the need for the temporary result.
|
||
|
|
For example, if a cursor has an attribute of INSENSITIVE, a temporary result
|
||
|
|
will be created. Eliminating the keyword INSENSITIVE will typically remove
|
||
|
|
the need for the temporary result, but your application will then see changes
|
||
|
|
as they are occur in the database tables.</dd>
|
||
|
|
<dt class="dlterm">Data conversion information</dt>
|
||
|
|
<dd>Contains a row of information for each SQL statement that required data
|
||
|
|
conversion. For example, if a result column has an attribute of INTEGER, but
|
||
|
|
the variable the result is being returned to is DECIMAL, the data must be
|
||
|
|
converted from integer to decimal. A single data conversion operation is very
|
||
|
|
inexpensive, but repeated thousands or millions of times can add up. In some
|
||
|
|
cases, it is a simple task to change one of the attributes so a faster direct
|
||
|
|
map can be performed. In other cases, the conversion is necessary because
|
||
|
|
there is no exact matching data type available.</dd>
|
||
|
|
<dt class="dlterm">Subquery information</dt>
|
||
|
|
<dd>Contains a row of subquery information. This information can indicate
|
||
|
|
which subquery in a complex SQL statement is the most expensive.</dd>
|
||
|
|
</dl>
|
||
|
|
<p>Finally, you can select the Composite view.</p>
|
||
|
|
<dl><dt class="dlterm">Summary data </dt>
|
||
|
|
<dd>Contains resource and other general information about monitored jobs. </dd>
|
||
|
|
<dt class="dlterm">Statement text </dt>
|
||
|
|
<dd>Contains the SQL text that monitored jobs call. </dd>
|
||
|
|
<dt class="dlterm">Table scan </dt>
|
||
|
|
<dd>Contains the table scan data for the monitored jobs. </dd>
|
||
|
|
<dt class="dlterm">Data sorts </dt>
|
||
|
|
<dd>Contains details of data sorts that monitored jobs perform.</dd>
|
||
|
|
<dt class="dlterm">Host variable use </dt>
|
||
|
|
<dd>Contains the values of host variables that monitored jobs use.</dd>
|
||
|
|
<dt class="dlterm">Optimizer time out/access paths considered</dt>
|
||
|
|
<dd>Contains details of any occurrences of time outs of monitored jobs.</dd>
|
||
|
|
<dt class="dlterm">Indexes used</dt>
|
||
|
|
<dd>Contains details of how indexes are used by monitored jobs. </dd>
|
||
|
|
<dt class="dlterm">Index creation</dt>
|
||
|
|
<dd>Contains details of the creation of indexes by monitored jobs.</dd>
|
||
|
|
<dt class="dlterm">Subselect processing</dt>
|
||
|
|
<dd>Contains information about each subselect in an SQL statement.</dd>
|
||
|
|
<dt class="dlterm">Temporary file use </dt>
|
||
|
|
<dd>Contains details of temporary files that monitored jobs created. </dd>
|
||
|
|
</dl>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzajqnavsummon.htm" title="You can work with summary monitors from the iSeries Navigator interface. A summary monitor creates a Memory-Resident Database monitor (DBMon), found on the native interface.">Using iSeries Navigator with summary monitors</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<img src="./deltaend.gif" alt="End of change" /></body>
|
||
|
|
</html>
|