Access Paths

Anand Jeyapaul

Access Paths
New Year Greetings.

Can anyone tell me when should we optimize access paths in sql and how do we do
this.

with regards

Anand



Deepak Kumar (CTS) Thakral

Re: Access Paths
(in response to Anand Jeyapaul)
Anand,

When do you need to optimize access paths :
======================

1. Usually, the optimizer in-built in DB2 takes care of determining the
optimum Access path. Sometimes however, the optimizer is not always accurate
and the application analyst understands the nature of the data better than
DB2. Then you can influence the optimzer into choosing an access path that
you think is a better one but which the optimizer thinks is worse.

HOW TO OPTIMIZE ACCESS PATHS
========

1. You need to run RUNSTATS to determine accurate statistics about your SQL
statements. The following characterstics about DB2 objects are indicated by
RUNSTATS :

1)The Organization
2)The cardinality of tablespaces, tables,columns and indexes.
3)The column range.

All these factors are considered by the optimizer when it choosed what it
deems to be the optimal access path for a given SQL statement.


2. Also DB2 EXPLAIN statement places information about the access paths in a
PLAN_TABLE , which can be inspected by a technical analyst.

3. The optimizer takes the size of the bufferpools into account when
determining access paths. As the size of the bufferpool increases, DB2
assumes that read effeciency increases also. Watch out for that.

4. The optimizer also takes into account the type of CPU being used during
access path selection.

There are four ways to influence the optimizer 's access path decisions :

1) Standard DB2-based methods such as using RUNSTATS utility or BIND/REBIND
commands.
2) Tweaking the SQL statement.
3) Optimize for n-rows.
4)Changing DB2 catalog statistics.(usually done only by SYSADM authority)

5. Last but not the least, make sure you have a plan for analysing all
components of DB2 programs, that is both static and Dynamic SQL not just the
Static. Also make sure that you follow every SQL statement by a check of the
SQLCODE or SQLSTATE.

If you really want to go into details about all these, I guess you could
look into Craig Mullins' DB2 Developers Guide, Ch.21 from where most of my
knowledge of RUnstats and access path optimization is based.


Hope this helps .


Thanks,

Deepak.









-----Original Message-----
From: Anand Jeyapaul [mailto:[login to unmask email]
Sent: Thursday, January 06, 2000 12:02 PM
To: [login to unmask email]
Subject: Access Paths


New Year Greetings.

Can anyone tell me when should we optimize access paths in sql and how do we
do
this.

with regards

Anand





Claudio Meluzzi Mendes

Re: Access Paths
(in response to Deepak Kumar (CTS) Thakral)
>Can anyone tell me when should we optimize access paths in sql and how do
we do
>this.

How - I would suggest you the same as Deepak.

When - Whenever a program is ready to enter the production subsystem you
should execute Explain for it. In our instalaltion tables have catalog
information updated by Runstats once a week, on Sundays. All plans/packages
are rebound after the weekly Runstats execution. In addition, I would
suggest you to extract some trace data - you can use DB2 PM or another
performance monitor - and generate a set of average values for your
applications, such as class 7 and class 8 times (or classes 2 and 3 if you
do not have packages). Then use these average values to calculate the
deviation a certain program has from the others. For instance, we have a
standard execution time for CICS applications that is 1 second. If any
CICS/DB2 program has an execution time greater than that the exception is
recorded and the program and related tables are analysed and optimized with
Explain, Runstats, Rebind, etc. For batch environment the execution time
(class 7) is not a good parameter for comparison, so you have to choose
another one like number of getpages, I/O, etc. This approach is suitable for
our particular needs and the use of Explain/Runstats/Bind and daily
Performance Analysis has been providing good execution times for our
applications.

Just my two cents,

Claudio
*********************************************
Claudio Meluzzi Mendes
[login to unmask email]
[login to unmask email]
BEG - Banco do Estado de Goias
Goiania - GO - BRASIL
*********************************************



Craig Mullins

Re: Access Paths
(in response to Claudio Meluzzi Mendes)
Anand and Deepak:

One other thing to add: if you are using DB2 V6, IBM has added optimizer
"hints." Using this method of changing the access path, you point DB2 at an
old access path in a PLAN_TABLE. So, if an access path changes to one that
is less desirable than a previous access path, simply rebind and point to
the old PLAN_TABLE rows to tell the optimizer the access path to use (using
the OPTHINT parameter).

Of course, you could also code your own PLAN_TABLE entries to specify a new
access path and specify that to DB2 using the OPTHINT capability. But you
need to be careful that the access path you manually code is actually
possible and actually performs better...

Cheers,
Craig S. Mullins
Director, DB2 Technology Planning
BMC Software
http://www.bmc.com
[login to unmask email]
http://www.craigsmullins.com
[login to unmask email]


>-----Original Message-----
>Date: Thu, 6 Jan 2000 12:45:51 +0530
>From: "Thakral, Deepak Kumar (CTS)" <[login to unmask email]>
>Subject: Re: Access Paths
>
>This message is in MIME format. Since your mail reader does not understand
>this format, some or all of this message may not be legible.
>
>------_=_NextPart_001_01BF5815.EC7F19E0
>Content-Type: text/plain;
> charset="iso-8859-1"
>
>Anand,
>
>When do you need to optimize access paths :
>======================
>
>1. Usually, the optimizer in-built in DB2 takes care of determining the
>optimum Access path. Sometimes however, the optimizer is not always
accurate
>and the application analyst understands the nature of the data better than
>DB2. Then you can influence the optimzer into choosing an access path that
>you think is a better one but which the optimizer thinks is worse.
>
>HOW TO OPTIMIZE ACCESS PATHS
>========
>
>1. You need to run RUNSTATS to determine accurate statistics about your SQL
>statements. The following characterstics about DB2 objects are indicated by
>RUNSTATS :
>
>1)The Organization
>2)The cardinality of tablespaces, tables,columns and indexes.
>3)The column range.
>
>All these factors are considered by the optimizer when it choosed what it
>deems to be the optimal access path for a given SQL statement.
>
>2. Also DB2 EXPLAIN statement places information about the access paths in
a
>PLAN_TABLE , which can be inspected by a technical analyst.
>
>3. The optimizer takes the size of the bufferpools into account when
>determining access paths. As the size of the bufferpool increases, DB2
>assumes that read effeciency increases also. Watch out for that.
>
>4. The optimizer also takes into account the type of CPU being used during
>access path selection.
>
>There are four ways to influence the optimizer 's access path decisions :
>
>1) Standard DB2-based methods such as using RUNSTATS utility or BIND/REBIND
>commands.
>2) Tweaking the SQL statement.
>3) Optimize for n-rows.
>4)Changing DB2 catalog statistics.(usually done only by SYSADM authority)
>5. Last but not the least, make sure you have a plan for analysing all
>components of DB2 programs, that is both static and Dynamic SQL not just
the
>Static. Also make sure that you follow every SQL statement by a check of
the
>SQLCODE or SQLSTATE.
>
>If you really want to go into details about all these, I guess you could
>look into Craig Mullins' DB2 Developers Guide, Ch.21 from where most of my
>knowledge of RUnstats and access path optimization is based.
>
>
>Hope this helps .
>
>
>Thanks,
>
>Deepak.
>
>-----Original Message-----From: Anand Jeyapaul
[mailto:[login to unmask email]
>Sent: Thursday, January 06, 2000 12:02 PM
>To: [login to unmask email]
>Subject: Access Paths
>
>
>New Year Greetings.
>
>Can anyone tell me when should we optimize access paths in sql and how do
we
>do
>this.
>
>with regards
>
>Anand
>
>