121 lines
8.1 KiB
HTML
121 lines
8.1 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="Database monitor performance analysis example 1" />
|
||
|
|
<meta name="abstract" content="Determine which queries in your SQL application are implemented with table scans. The complete information can be obtained by joining two views: QQQ1000, which contains information about the SQL statements, and QQQ3000, which contains data about queries performing table scans." />
|
||
|
|
<meta name="description" content="Determine which queries in your SQL application are implemented with table scans. The complete information can be obtained by joining two views: QQQ1000, which contains information about the SQL statements, and QQQ3000, which contains data about queries performing table scans." />
|
||
|
|
<meta name="DC.subject" content="performance analysis, example 1, examples, output, SQL queries that performed table scans, table scans, output for SQL queries" />
|
||
|
|
<meta name="keywords" content="performance analysis, example 1, examples, output, SQL queries that performed table scans, table scans, output for SQL queries" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="dbmonexamples.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="rzajqmon1" />
|
||
|
|
<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>Database monitor performance analysis example 1</title>
|
||
|
|
</head>
|
||
|
|
<body id="rzajqmon1"><a name="rzajqmon1"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Database monitor performance analysis example 1</h1>
|
||
|
|
<div><p>Determine which queries in your SQL application are implemented
|
||
|
|
with table scans. The complete information can be obtained by joining two
|
||
|
|
views: QQQ1000, which contains information about the SQL statements, and QQQ3000,
|
||
|
|
which contains data about queries performing table scans.</p>
|
||
|
|
<div class="section"><p>The following SQL query can be used: </p>
|
||
|
|
<pre><strong>SELECT</strong> A.System_Table_Schema, A.System_Table_Name, A.Table_Total_Rows, A.Index_Advised,
|
||
|
|
C.Number_Rows_Returned, (B.End_Timestamp - B.Start_Timestamp)
|
||
|
|
<strong>AS</strong> TOT_TIME, B.Statement_Text_Long
|
||
|
|
<strong>FROM</strong> LIB/QQQ3000 A, LIB/QQQ1000 B, LIB/QQQ3019 C
|
||
|
|
<strong>WHERE</strong> A.Join_Column = B.Join_Column
|
||
|
|
<strong>AND</strong> A.Unique_Count = B.Unique_Count
|
||
|
|
<strong>AND</strong> A.Join_Column = C.Join_Column
|
||
|
|
<strong>AND</strong> A.Unique_Count = C.Unique_Count </pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Sample output of this query is shown in the table below. Key to
|
||
|
|
this example are the join criteria: </p>
|
||
|
|
<pre> <strong>WHERE</strong> A.Join_Column = B.Join_Column
|
||
|
|
<strong>AND</strong> A.Join_Column = C.Join_Column</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>A lot of data about many queries is contained in multiple rows
|
||
|
|
in table LIB/PERFDATA. It is not uncommon for data about a single query to
|
||
|
|
be contained in 10 or more rows within the table. The combination of defining
|
||
|
|
the logical views and then joining the views together allows you to piece
|
||
|
|
together all the data for a query or set of queries. Column QQJFLD uniquely
|
||
|
|
identifies all queries within a job; column QQUCNT is unique at the query
|
||
|
|
level. The combination of the two, when referenced in the context of the logical
|
||
|
|
views, connects the query implementation to the query statement information.</p>
|
||
|
|
</div>
|
||
|
|
|
||
|
|
<table cellpadding="4" cellspacing="0" border="1" class="tableborder"><tr><td>
|
||
|
|
<a name="rzajqmon1__tblscn"><!-- --></a><table cellpadding="4" cellspacing="0" summary="" id="rzajqmon1__tblscn" width="100%" border="0"><caption>Table 1. Output for
|
||
|
|
SQL Queries that Performed Table Scans</caption><thead align="left"><tr><th align="left" valign="bottom" width="10%" id="d0e81">Lib Name</th>
|
||
|
|
<th align="left" valign="bottom" width="10%" id="d0e83">Table Name</th>
|
||
|
|
<th align="left" valign="bottom" width="10%" id="d0e85">Total Rows</th>
|
||
|
|
<th align="left" valign="bottom" width="10%" id="d0e87">Index Advised</th>
|
||
|
|
<th align="left" valign="bottom" width="10%" id="d0e89">Rows Returned</th>
|
||
|
|
<th align="left" valign="bottom" width="10%" id="d0e91">TOT_ TIME</th>
|
||
|
|
<th align="left" valign="bottom" width="40%" id="d0e93">Statement Text</th>
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<tbody><tr><td align="left" valign="top" width="10%" headers="d0e81 "><strong>LIB1</strong></td>
|
||
|
|
<td align="left" valign="top" width="10%" headers="d0e83 "><strong>TBL1</strong></td>
|
||
|
|
<td align="left" valign="top" width="10%" headers="d0e85 "><strong>20000</strong></td>
|
||
|
|
<td align="left" valign="top" width="10%" headers="d0e87 "><strong>Y</strong></td>
|
||
|
|
<td align="left" valign="top" width="10%" headers="d0e89 "><strong>10</strong></td>
|
||
|
|
<td align="left" valign="top" width="10%" headers="d0e91 "><strong>6.2</strong></td>
|
||
|
|
<td align="left" valign="top" width="40%" headers="d0e93 "><pre>SELECT * FROM LIB1/TBL1
|
||
|
|
WHERE FLD1 = 'A'</pre>
|
||
|
|
</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td align="left" valign="top" width="10%" headers="d0e81 "><strong>LIB1</strong></td>
|
||
|
|
<td align="left" valign="top" width="10%" headers="d0e83 "><strong>TBL2</strong></td>
|
||
|
|
<td align="left" valign="top" width="10%" headers="d0e85 "><strong>100</strong></td>
|
||
|
|
<td align="left" valign="top" width="10%" headers="d0e87 "><strong>N</strong></td>
|
||
|
|
<td align="left" valign="top" width="10%" headers="d0e89 "><strong>100</strong></td>
|
||
|
|
<td align="left" valign="top" width="10%" headers="d0e91 "><strong>0.9</strong></td>
|
||
|
|
<td align="left" valign="top" width="40%" headers="d0e93 "><pre>SELECT * FROM LIB1/TBL2</pre>
|
||
|
|
</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td align="left" valign="top" width="10%" headers="d0e81 "><strong>LIB1</strong></td>
|
||
|
|
<td align="left" valign="top" width="10%" headers="d0e83 "><strong>TBL1</strong></td>
|
||
|
|
<td align="left" valign="top" width="10%" headers="d0e85 "><strong>20000</strong></td>
|
||
|
|
<td align="left" valign="top" width="10%" headers="d0e87 "><strong>Y</strong></td>
|
||
|
|
<td align="left" valign="top" width="10%" headers="d0e89 "><strong>32</strong></td>
|
||
|
|
<td align="left" valign="top" width="10%" headers="d0e91 "><strong>7.1</strong></td>
|
||
|
|
<td align="left" valign="top" width="40%" headers="d0e93 "><pre>SELECT * FROM LIB1/TBL1
|
||
|
|
WHERE FLD1 = 'B' AND
|
||
|
|
FLD2 > 9000</pre>
|
||
|
|
</td>
|
||
|
|
</tr>
|
||
|
|
</tbody>
|
||
|
|
</table>
|
||
|
|
</td></tr></table>
|
||
|
|
<div class="section"><p>If the query does not use SQL, the SQL information row (QQQ1000)
|
||
|
|
is not created. This makes it more difficult to determine which rows in LIB/PERFDATA
|
||
|
|
pertain to which query. When using SQL, row QQQ1000 contains the actual SQL
|
||
|
|
statement text that matches the monitor rows to the corresponding query. Only
|
||
|
|
through SQL is the statement text captured. For queries executed using the
|
||
|
|
OPNQRYF command, the OPNID parameter is captured and can be used to tie the
|
||
|
|
rows to the query. The OPNID is contained in column Open_Id of row QQQ3014.</p>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="dbmonexamples.htm" title="The iSeries navigator interface provides a powerful tool for gathering and analyzing performance monitor data using database monitor. However, you may want to do your own analysis of the database monitor files.">Database monitor examples</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|