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

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

身份认证购VIP最低享 7 折!

30元优惠券

资源详情

资源推荐

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版学习指南

pt-visual-explain

db2中explain

db2 explain报错

db2 explain

db2中如何查看执行就啊

explain和explain analysis

tf_explain安装

explain analyze和explain的区别

SQL SERVER EXPLAIN用法

MySql数据库explain用法示例_Navicat Explain 指南(示例代码)

Postgresql explain怎么用

oracle explain

navicat 怎么使用 EXPLAIN

navicat中explain使用

如何录入一个explain

oracle explain怎么看

mysql explain filter 作用

db2 sql调优方式

explain关键字的作用

最新资源