105 lines
9.5 KiB
HTML
105 lines
9.5 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="concept" />
|
||
|
|
<meta name="DC.Title" content="Optimizing query performance using query optimization tools" />
|
||
|
|
<meta name="abstract" content="Query optimization is an iterative process. You can gather performance information about your queries and control the processing of your queries." />
|
||
|
|
<meta name="description" content="Query optimization is an iterative process. You can gather performance information about your queries and control the processing of your queries." />
|
||
|
|
<meta name="DC.subject" content="OPNQRYF (Open Query File) command, performance, OPNQRYF, monitoring, optimizing, tools" />
|
||
|
|
<meta name="keywords" content="OPNQRYF (Open Query File) command, performance, OPNQRYF, monitoring, optimizing, tools" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqkickoff.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="qoptcmds.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="qodm.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="psi.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqdisplayplancache.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="mbdma.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqnavsummon.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="dms.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqnavmon.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="idxadvisor.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="visexpl.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="cqa.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="statsmanager.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqhealthcenter.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="compare.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="queryopt" />
|
||
|
|
<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>Optimizing query performance using query optimization tools</title>
|
||
|
|
</head>
|
||
|
|
<body id="queryopt"><a name="queryopt"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Optimizing query performance using query optimization tools</h1>
|
||
|
|
<div><p>Query optimization is an iterative process. You can gather performance
|
||
|
|
information about your queries and control the processing of your queries.</p>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<ul class="ullinks">
|
||
|
|
<li class="ulchildlink"><strong><a href="qoptcmds.htm">Verify the performance of SQL applications</a></strong><br />
|
||
|
|
You can verify the performance of an SQL application by using commands.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="qodm.htm">Examine query optimizer debug messages in the job log</a></strong><br />
|
||
|
|
Query optimizer debug messages issue informational messages to the job log about the implementation of a query. These messages explain what happened during the query optimization process.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="psi.htm">Gather information about embedded SQL statements with the PRTSQLINF command</a></strong><br />
|
||
|
|
The <span class="cmdname">Print SQL Information (PRTSQLINF)</span> command
|
||
|
|
returns information about the embedded SQL statements in a program, SQL package
|
||
|
|
(the object normally used to store the access plan for a remote query), or
|
||
|
|
service program. This information is then stored in a spooled file.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rzajqdisplayplancache.htm">Viewing the plan cache with iSeries Navigator</a></strong><br />
|
||
|
|
The Plan Cache contains a wealth of information about the SQE queries being run through the database. Its contents are viewable through the iSeries Navigator GUI interface.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="mbdma.htm">Monitoring your queries using memory-resident database monitor</a></strong><br />
|
||
|
|
The Memory-Resident Database Monitor is a tool that provides another method for monitoring database performance. This tool is only intended for SQL performance monitoring and is useful for programmers and performance analysts. The monitor, with the help of a set of APIs, takes database monitoring information and manages them for the user in memory. This memory-based monitor reduces CPU overhead as well as resulting table sizes.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rzajqnavsummon.htm">Using iSeries Navigator with summary monitors</a></strong><br />
|
||
|
|
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.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="dms.htm">Monitoring your queries using Start Database Monitor (STRDBMON)</a></strong><br />
|
||
|
|
<span class="cmdname">Start Database Monitor (STRDBMON)</span> command gathers
|
||
|
|
information about a query in real time and stores this information in an output
|
||
|
|
table. This information can help you determine whether your system and your
|
||
|
|
queries are performing as they should, or whether they need fine tuning. Database
|
||
|
|
monitors can generate significant CPU and disk storage overhead when in use.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rzajqnavmon.htm">Using iSeries Navigator with detailed monitors</a></strong><br />
|
||
|
|
You can work with detailed monitors from the iSeries™ Navigator
|
||
|
|
interface. The detailed SQL performance monitor is the iSeries™ Navigator version of the STRDBMON
|
||
|
|
database monitor, found on the native interface.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="idxadvisor.htm">Query optimizer index advisor</a></strong><br />
|
||
|
|
The query optimizer analyzes the row selection in the query and determines, based on default values, if creation of a permanent index improves performance. If the optimizer determines that a permanent index might be beneficial, it returns the key columns necessary to create the suggested index.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="visexpl.htm">View the implementation of your queries with Visual Explain</a></strong><br />
|
||
|
|
You can use the <strong>Visual Explain</strong> tool with iSeries™ Navigator
|
||
|
|
to create a query graph that graphically displays the implementation of an
|
||
|
|
SQL statement. You can use this tool to see information about both static
|
||
|
|
and dynamic SQL statements. Visual Explain supports the following types of
|
||
|
|
SQL statements: SELECT, INSERT, UPDATE, and DELETE.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="cqa.htm">Change the attributes of your queries with the Change Query Attributes (CHGQRYA) command</a></strong><br />
|
||
|
|
You can modify different types of attributes of the queries that
|
||
|
|
you will execute during a certain job with the <span class="cmdname">Change Query Attributes
|
||
|
|
(CHGQRYA)</span> CL command, or by using the iSeries™ Navigator Change Query Attributes
|
||
|
|
interface.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="statsmanager.htm">Collecting statistics with the Statistics Manager</a></strong><br />
|
||
|
|
As stated earlier, the collection of statistics is handled by a separate component called the Statistics Manager. Statistical information can be used by the query optimizer to determine the best access plan for a query. Since the query optimizer bases its choice of access plan on the statistical information found in the table, it is important that this information be current.</li>
|
||
|
|
<li class="ulchildlink"><img src="./delta.gif" alt="Start of change" /><strong><a href="rzajqhealthcenter.htm">Display information with Database Health Center</a></strong><img src="./deltaend.gif" alt="End of change" /><br />
|
||
|
|
Use the Database Health Center to capture information about your database. You can view the total number of objects, the size limits of selected objects in your database, and the design limits of selected objects.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="compare.htm">Query optimization tools: Comparison table</a></strong><br />
|
||
|
|
Use this table to learn what information each tool can yield about your query, when in the process a specific tool can analyze your query, and the tasks that each tool can perform to improve your query.</li>
|
||
|
|
</ul>
|
||
|
|
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzajqkickoff.htm" title="The goal of database performance tuning is to minimize the response time of your queries and to make the best use of your server's resources by minimizing network traffic, disk I/O, and CPU time. This goal can only be achieved by understanding the logical and physical structure of your data, understanding the applications used on your server, and understanding how the many conflicting uses of your database may impact database performance.">Performance and query optimization</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|