DB2 Version 9 Visual Explain教程：Linux与Windows版学习指南

需积分: 8 101 浏览量更新于2024-08-02 收藏 1.11MB PDF 举报

本教程是针对DB2 Version 9的Visual Explain详解指南，主要适用于Linux和Windows操作系统。DB2 Version 9是一款强大的关系型数据库管理系统，其Visual Explain工具是理解查询性能的关键组件。该教程旨在帮助用户深入了解如何利用Visual Explain来优化查询计划，以便提高数据库操作的效率和响应速度。 Visual Explain是DB2中的一个功能，它允许用户在执行SQL语句之前预览其执行计划，这有助于识别可能存在的性能瓶颈和潜在的优化机会。通过这个工具，开发人员可以观察到查询的各个阶段，包括表扫描、索引使用、排序、连接操作等，从而更好地理解和调整查询逻辑，确保查询的高效执行。在使用本教程前，请务必阅读附带的“注意事项”部分，其中包括版权声明和许可协议，以及关于产品保修和声明的澄清。教程内容可能不包含产品保修，并且任何手册中的陈述都不能视为保修承诺。用户可以通过IBM官方网站（www.ibm.com/shop/publications/）在线订购IBM出版物，或通过全球IBM联系人目录（www.ibm.com/planetwide）找到本地代表进行购买。对于美国和加拿大的用户，可以直接拨打1-800-IBM-4YOU (426-4968)与DB2营销和销售团队联系订购相关的DB2文档。本教程提供了一个深入学习和实践Visual Explain技巧的基础，无论是初次接触DB2的开发人员，还是希望提升查询性能优化技能的专业人士，都将从中受益匪浅。通过DB2 Version 9的Visual Explain教程，读者将学会如何有效地利用该工具分析SQL语句，理解其背后的执行逻辑，以及如何据此进行优化，以实现更高效的数据库管理。这是一项必备的技能，对于任何处理大量数据并追求性能优化的IT专业人员来说，都是一份不可或缺的学习资料。

To view details for multiple operators, select each one by clicking on it (it is

highlighted); then select Node–>Show Details. A Statistics window opens

for each of the selected objects. (The windows might be stacked and some

dragging and dropping could be required in order to access them all.)

Getting statistics for functions:

To view catalog statistics for built-in functions and user-defined functions,

select Statement–>Show Statistics–>Functions;. Select one or more entries

from the list displayed on the Functions window and click OK. A Function

Statistics window opens for each of the selected functions.

Getting statistics for tables spaces:

To view catalog statistics for table spaces, select Statement–>Show

Statistics–>Table Spaces. Select one or more entries from the list displayed

on the Table Spaces window and click on OK. A Table Space Statistics

window opens for each of the selected table spaces.

Getting statistics for columns in an SQL or XQuery statement:

To view statistics for the columns referenced in an SQL or XQuery

statement, double-click a table in the access plan graph. The Table Statistics

window opens. Click the Referenced Columns push button. The

Referenced Columns window opens, listing the columns in the table. Select

one or more columns from the list, and click OK. A Referenced Column

Statistics window opens for each of the columns selected.

Getting information about configuration parameters and bind options:

To view information about configuration parameters and bind options

(optimization parameters), select Statement–>Show Optimization

parameters from the Access Plan Graph window. The Optimization

Parameters window opens, displaying information about the parameter

values that were in effect at the time the snapshot was created, as well as

the current values.

Related concepts:

v “Access plan graph node” on page 49

v “Predicate” on page 55

Changing the appearance of a graph

To change various characteristics of how a graph appears:

1. From the Access Plan Graph window, select View–>Settings. The the Access

Plan Graph Settings notebook opens.

2. To change the background color, choose the Graph tab.

3. To change the color of various operators, use the Basic, Extend, Update, and

Miscellaneous tabs.

4. To change the color of table, index, or table function nodes, select the Operand

tab.

5. To specify which type of information is shown in operator nodes (type of cost

or cardinality, which is the estimated number of rows returned so far), choose

the Operator tab.

6. To specify whether schema names or user IDs are shown in table nodes, select

the Operand tab.

10 Visual Explain Tutorial

Lesson 3. Improving an access plan in a single-partition

database environment

In this lesson, you will learn how the access plan and related windows for the

basic query change when you perform various tuning activities. Using a series of

examples, accompanied by illustrations, you will learn how the estimated total cost

for the access plan of even a simple query can be improved by using the runstats

command and adding appropriate indexes. By using the four sample explain

snapshots as examples, you will learn how tuning is an important part of database

performance.

As you gain experience with Visual Explain, you will discover other ways to tune

queries.

Queries associated with the explain snapshots in a single-partition

database environment

The queries associated with the explain snapshots are numbered 1 – 4. Each query

uses the same SQL or XQuery statement (described in Lesson 1):

SELECT S.ID,S.NAME,O.DEPTNAME,SALARY+COMM

FROM ORG O, STAFF S

WHERE

O.DEPTNUMB = S.DEPT AND

S.JOB <> ’Mgr’ AND

S.SALARY+S.COMM > ALL( SELECT ST.SALARY*.9

FROM STAFF ST

WHERE ST.JOB=’Mgr’ )

ORDER BY S.NAME

But each iteration of the query uses more tuning techniques than the previous

execution. For example, Query 1 has had no performance tuning, while Query 4

has had the most. The differences in the queries are described below:

Query 1

Running a query with no indexes and no statistics

Query 2

Collecting current statistics for the tables and indexes in a query

Query 3

Creating indexes on columns used to join tables in a query

Query 4

Creating additional indexes on table columns

Related tasks:

v “Running a query with no indexes and no statistics in a single-partition database

environment” on page 14

v “Collecting current statistics for the tables and indexes using runstats in a

single-partition database environment” on page 17

v “Creating indexes on columns used to join tables in a query in a single-partition

database environment” on page 21

v “Creating additional indexes on table columns in a single-partition database

environment” on page 26

剩余97页未读，继续阅读

johncheung2004

粉丝: 0
资源: 1

DB2 Version 9 Visual Explain教程：Linux与Windows版学习指南

DB2 Universal Database Visual Explain Tutoria.pdf

DB29.7Visual Explain教程

DB2数据库全套资料(简体中文共5部分60M第1部分)

pt-visual-explain

db2中explain

db2 explain报错

db2 explain

db2中如何查看执行就啊

explain和explain analysis

tf_explain安装

最新资源