Access path Selection

Karthick I

Access path Selection
Dear Experts,

Will Segement size influence the access path selection? . If we alter
the Segement size for the segmented table space will it change the access
path.

Thanks,
Regards,
Karthick.I
----------------------------------------------
AT-LINK & IBM Ops Support
Transco Support Project
Internal No : 715-32817
---------------------------------------------

Unless expressly stated to the contrary, the views expressed in this email
are not necessarily the views of National Grid Transco plc or any of its
subsidiaries or affiliates (Group Companies), and the Group Companies,
their directors, officers and employees make no representation and accept
no liability for its accuracy or completeness.

This e-mail, and any attachments are strictly confidential and intended for
the addressee(s) only. The content may also contain legal, professional or
other privileged information. If you are not the intended recipient, please
notify the sender immediately and then delete the e-mail and any
attachments. You should not disclose, copy or take any action in reliance
on this transmission.

You may report the matter by calling us on + 44(0) 1455 230999

Please ensure you have adequate virus protection before you open or detach
any documents from this transmission. The Group Companies do not accept
any liability for viruses. An e-mail reply to this address may be subject
to monitoring for operational reasons or lawful business practices.



Sushanta K Dash

Re: Access path Selection
(in response to Karthick I)
Hi Karthik, Here I could say what I knew and experienced. Please wait for
others also. I guess your main aim is for altering the SEGSIZE and to
consider its impact at access path. The application uses mostly prefetch
then your altering the SEGSIZE to a big value would definitely improve the
performances. Mostly it would be beneficial if you go for 32 as
segsize. Similarly your space map pages would be at buffer pool and you
would get benefits. You may get advantage in reducing the number of
getpages. If the alter is towards more size then the insert queries would
work better than earlier.


With thanks
Dash S.K.

EDS/India Solution Center
Saturn/AAT Data Base Administration
Hello 28113801 through 28113815 ext-2366
e-mail: [login to unmask email] <mailto:[login to unmask email]>



-----Original Message-----
From: Karthick I [SMTP:[login to unmask email]
Sent: Monday, January 06, 2003 4:40 PM
To: [login to unmask email]
Subject: Access path Selection

Dear Experts,

Will Segement size influence the access path selection? . If
we alter
the Segement size for the segmented table space will it change the
access
path.

Thanks,
Regards,
Karthick.I
----------------------------------------------
AT-LINK & IBM Ops Support
Transco Support Project
Internal No : 715-32817
---------------------------------------------

Unless expressly stated to the contrary, the views expressed in this
email
are not necessarily the views of National Grid Transco plc or any of
its
subsidiaries or affiliates (Group Companies), and the Group
Companies,
their directors, officers and employees make no representation and
accept
no liability for its accuracy or completeness.

This e-mail, and any attachments are strictly confidential and
intended for
the addressee(s) only. The content may also contain legal,
professional or
other privileged information. If you are not the intended recipient,
please
notify the sender immediately and then delete the e-mail and any
attachments. You should not disclose, copy or take any action in
reliance
on this transmission.

You may report the matter by calling us on + 44(0) 1455 230999

Please ensure you have adequate virus protection before you open or
detach
any documents from this transmission. The Group Companies do not
accept
any liability for viruses. An e-mail reply to this address may be
subject
to monitoring for operational reasons or lawful business practices.



visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the
list can



Terry Purcell

Re: Access path Selection
(in response to Sushanta K Dash)
Karthik,

I have not seen any evidence that SEGSIZE will alter access path selection.
It can however impact prefetch performance with multi-table segmented
tablespaces. This is documented in the manuals.

It wouldn't really surprise me if SEGSIZE is not considered for access path
selection, as there are much bigger fish to fry for access path selection.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Dash, Sushanta
Sent: Monday, January 06, 2003 5:50 AM
To: [login to unmask email]
Subject: Re: Access path Selection


Hi Karthik, Here I could say what I knew and experienced. Please wait for
others also. I guess your main aim is for altering the SEGSIZE and to
consider its impact at access path. The application uses mostly prefetch
then your altering the SEGSIZE to a big value would definitely improve the
performances. Mostly it would be beneficial if you go for 32 as
segsize. Similarly your space map pages would be at buffer pool and you
would get benefits. You may get advantage in reducing the number of
getpages. If the alter is towards more size then the insert queries would
work better than earlier.


With thanks
Dash S.K.

EDS/India Solution Center
Saturn/AAT Data Base Administration
Hello 28113801 through 28113815 ext-2366
e-mail: [login to unmask email] <mailto:[login to unmask email]>



-----Original Message-----
From: Karthick I [SMTP:[login to unmask email]
Sent: Monday, January 06, 2003 4:40 PM
To: [login to unmask email]
Subject: Access path Selection

Dear Experts,

Will Segement size influence the access path selection? . If
we alter
the Segement size for the segmented table space will it change the
access
path.

Thanks,
Regards,
Karthick.I
----------------------------------------------
AT-LINK & IBM Ops Support
Transco Support Project
Internal No : 715-32817
---------------------------------------------



John Higgins

Re: Access path Selection
(in response to Terry Purcell)
Terry,

I can't argue that there aren't bigger fish to fry, but SEGSIZE seems to be
low-hanging fruit (to completely scramble the metaphor).

As you said, SEGSIZE can impact prefetch performance. Why wouldn't we want
the optimizer to consider that?

Best regards,
John P. Higgins




-----Original Message-----
From: Terry Purcell [mailto:[login to unmask email]
Sent: Monday, January 06, 2003 5:34 PM
To: [login to unmask email]
Subject: Re: Access path Selection


Karthik,

I have not seen any evidence that SEGSIZE will alter access path selection.
It can however impact prefetch performance with multi-table segmented
tablespaces. This is documented in the manuals.

It wouldn't really surprise me if SEGSIZE is not considered for access path
selection, as there are much bigger fish to fry for access path selection.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Dash, Sushanta
Sent: Monday, January 06, 2003 5:50 AM
To: [login to unmask email]
Subject: Re: Access path Selection


Hi Karthik, Here I could say what I knew and experienced. Please wait for
others also. I guess your main aim is for altering the SEGSIZE and to
consider its impact at access path. The application uses mostly prefetch
then your altering the SEGSIZE to a big value would definitely improve the
performances. Mostly it would be beneficial if you go for 32 as
segsize. Similarly your space map pages would be at buffer pool and you
would get benefits. You may get advantage in reducing the number of
getpages. If the alter is towards more size then the insert queries would
work better than earlier.


With thanks
Dash S.K.

EDS/India Solution Center
Saturn/AAT Data Base Administration
Hello 28113801 through 28113815 ext-2366
e-mail: [login to unmask email] <mailto:[login to unmask email]>



-----Original Message-----
From: Karthick I [SMTP:[login to unmask email]
Sent: Monday, January 06, 2003 4:40 PM
To: [login to unmask email]
Subject: Access path Selection

Dear Experts,

Will Segement size influence the access path selection? . If
we alter
the Segement size for the segmented table space will it change the
access
path.

Thanks,
Regards,
Karthick.I
----------------------------------------------
AT-LINK & IBM Ops Support
Transco Support Project
Internal No : 715-32817
---------------------------------------------








Terry Purcell

Re: Access path Selection
(in response to John Higgins)
John,

I can't disagree entirely with you either. But let me give you my thoughts
(which may or may not be correct since I have really never given this too
much thought).

Firstly, if you only have one table per tablespace then you can stop reading
here.

For multi-table tablespaces, I guess this is more an issue for simple
tablespaces (SEGSIZE 0). As I understand it, the optimizer does consider
index access over a tablespace scan even if the entire table is accessed,
since a single table may only reside on a small number of the tablespace
pages. Otherwise for a scan of a simple tablespace you will read all pages
regardless of whether they belong to the table you want or not.

But the focus of the question is for segmented. The issue is that sequential
prefetch will read 32 (based on BP size) contiguous pages that may contain
segments from other tables within the tablespace.

If you size your segments right, then prefetch performance should not be an
issue at all.

Basically it should not be an issue if:
- It is determined at runtime that less than 4 pages are to be read then
sequential prefetch is disabled.
- Your segsize is equal to or greater than your prefetch quantity
(determined by your bufferpool size).
- Your segsize is less than your prefetch quantity but all rows are
contained within one segment.
- Your segsize is less than your prefetch quantity and all your tables
segments are contiguous.

So if I am not mistaken (which becomes more likely the greater the detail),
then the issue is only when you have more than one table per tablespace,
your segsize is less than your prefetch quantity, you have a single tables
pages that span more than one segment, and your segments are not contiguous.

If that's true.....then it's probably not worth the effort for the optimizer
to consider SEGSIZE for that one situation.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Higgins John
Sent: Monday, January 06, 2003 8:06 PM
To: [login to unmask email]
Subject: Re: Access path Selection


Terry,

I can't argue that there aren't bigger fish to fry, but SEGSIZE seems to be
low-hanging fruit (to completely scramble the metaphor).

As you said, SEGSIZE can impact prefetch performance. Why wouldn't we want
the optimizer to consider that?

Best regards,
John P. Higgins




-----Original Message-----
From: Terry Purcell [mailto:[login to unmask email]
Sent: Monday, January 06, 2003 5:34 PM
To: [login to unmask email]
Subject: Re: Access path Selection


Karthik,

I have not seen any evidence that SEGSIZE will alter access path selection.
It can however impact prefetch performance with multi-table segmented
tablespaces. This is documented in the manuals.

It wouldn't really surprise me if SEGSIZE is not considered for access path
selection, as there are much bigger fish to fry for access path selection.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Dash, Sushanta
Sent: Monday, January 06, 2003 5:50 AM
To: [login to unmask email]
Subject: Re: Access path Selection


Hi Karthik, Here I could say what I knew and experienced. Please wait for
others also. I guess your main aim is for altering the SEGSIZE and to
consider its impact at access path. The application uses mostly prefetch
then your altering the SEGSIZE to a big value would definitely improve the
performances. Mostly it would be beneficial if you go for 32 as
segsize. Similarly your space map pages would be at buffer pool and you
would get benefits. You may get advantage in reducing the number of
getpages. If the alter is towards more size then the insert queries would
work better than earlier.


With thanks
Dash S.K.

EDS/India Solution Center
Saturn/AAT Data Base Administration
Hello 28113801 through 28113815 ext-2366
e-mail: [login to unmask email] <mailto:[login to unmask email]>



-----Original Message-----
From: Karthick I [SMTP:[login to unmask email]
Sent: Monday, January 06, 2003 4:40 PM
To: [login to unmask email]
Subject: Access path Selection

Dear Experts,

Will Segement size influence the access path selection? . If
we alter
the Segement size for the segmented table space will it change the
access
path.

Thanks,
Regards,
Karthick.I
----------------------------------------------
AT-LINK & IBM Ops Support
Transco Support Project
Internal No : 715-32817
---------------------------------------------



Karthick I

Re: Access path Selection
(in response to Terry Purcell)
Dear Experts,

Thanks for the reply. I have created a new table same like existing
old table except SEGSZIE (I inceased the SEGSIZE to 16 from 4). I loaded
50% of the rows from the old table and rebound all the programs to use the
new table. As I loaded with Recluster option all the
non -cluster indexes cluster ratio has decreased considerably. Most of the
Join SQL which uses non-clustering index of the new table changed to
Nested loop join which was originally Hybrid Join. So, it gives very poor
performance. As a part of this change I also changed the Type-1 indexes to
type-2 indexes (We are still in DB2 V 4.1) . Is it true that index type
change or SEGSIZE change wouldn't have influenced to use the Nested loop
join?.

Thanks & Regards,
Karthick.I
----------------------------------------------
AT-LINK & IBM Ops Support
Transco Support Project
Internal No : 715-32817
---------------------------------------------

Unless expressly stated to the contrary, the views expressed in this email
are not necessarily the views of National Grid Transco plc or any of its
subsidiaries or affiliates (Group Companies), and the Group Companies,
their directors, officers and employees make no representation and accept
no liability for its accuracy or completeness.

This e-mail, and any attachments are strictly confidential and intended for
the addressee(s) only. The content may also contain legal, professional or
other privileged information. If you are not the intended recipient, please
notify the sender immediately and then delete the e-mail and any
attachments. You should not disclose, copy or take any action in reliance
on this transmission.

You may report the matter by calling us on + 44(0) 1455 230999

Please ensure you have adequate virus protection before you open or detach
any documents from this transmission. The Group Companies do not accept
any liability for viruses. An e-mail reply to this address may be subject
to monitoring for operational reasons or lawful business practices.



Terry Purcell

Re: Access path Selection
(in response to Karthick I)
Karthick,

You are not really comparing apples to apples.

You mention that the following have changed:
1. Number of rows (50%)
2. Index Clusterratios
3. Type 1 to Type 2 indexes
4. SEGSIZE from 4 to 16

I would expect 1 & 2 to have a very large influence on the access path, and
3 & 4 to have very little. However, if you saw changes in index stats such
as NLEVELS with the change from type 1 to 2, then this could influence the
access path.

There could be many reasons for the access path changes. Cardinality,
clusterratio, parallelism, index screening (not available for hybrid join in
V4), optimizer deficiency (fixed in a later release) etc.......

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Karthick I
Sent: Tuesday, January 07, 2003 5:55 AM
To: [login to unmask email]
Subject: Re: Access path Selection


Dear Experts,

Thanks for the reply. I have created a new table same like existing
old table except SEGSZIE (I inceased the SEGSIZE to 16 from 4). I loaded
50% of the rows from the old table and rebound all the programs to use the
new table. As I loaded with Recluster option all the
non -cluster indexes cluster ratio has decreased considerably. Most of the
Join SQL which uses non-clustering index of the new table changed to
Nested loop join which was originally Hybrid Join. So, it gives very poor
performance. As a part of this change I also changed the Type-1 indexes to
type-2 indexes (We are still in DB2 V 4.1) . Is it true that index type
change or SEGSIZE change wouldn't have influenced to use the Nested loop
join?.

Thanks & Regards,
Karthick.I
----------------------------------------------
AT-LINK & IBM Ops Support
Transco Support Project
Internal No : 715-32817
---------------------------------------------



John Higgins

Re: Access path Selection
(in response to Terry Purcell)
Terry,

OK, I get it now.

Why have the optimizer do the job of the DBA?

Best regards,
John P. Higgins



-----Original Message-----
From: Terry Purcell [mailto:[login to unmask email]
Sent: Monday, January 06, 2003 10:27 PM
To: [login to unmask email]
Subject: Re: Access path Selection


John,

I can't disagree entirely with you either. But let me give you my thoughts
(which may or may not be correct since I have really never given this too
much thought).

Firstly, if you only have one table per tablespace then you can stop reading
here.

For multi-table tablespaces, I guess this is more an issue for simple
tablespaces (SEGSIZE 0). As I understand it, the optimizer does consider
index access over a tablespace scan even if the entire table is accessed,
since a single table may only reside on a small number of the tablespace
pages. Otherwise for a scan of a simple tablespace you will read all pages
regardless of whether they belong to the table you want or not.

But the focus of the question is for segmented. The issue is that sequential
prefetch will read 32 (based on BP size) contiguous pages that may contain
segments from other tables within the tablespace.

If you size your segments right, then prefetch performance should not be an
issue at all.

Basically it should not be an issue if:
- It is determined at runtime that less than 4 pages are to be read then
sequential prefetch is disabled.
- Your segsize is equal to or greater than your prefetch quantity
(determined by your bufferpool size).
- Your segsize is less than your prefetch quantity but all rows are
contained within one segment.
- Your segsize is less than your prefetch quantity and all your tables
segments are contiguous.

So if I am not mistaken (which becomes more likely the greater the detail),
then the issue is only when you have more than one table per tablespace,
your segsize is less than your prefetch quantity, you have a single tables
pages that span more than one segment, and your segments are not contiguous.

If that's true.....then it's probably not worth the effort for the optimizer
to consider SEGSIZE for that one situation.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Higgins John
Sent: Monday, January 06, 2003 8:06 PM
To: [login to unmask email]
Subject: Re: Access path Selection


Terry,

I can't argue that there aren't bigger fish to fry, but SEGSIZE seems to be
low-hanging fruit (to completely scramble the metaphor).

As you said, SEGSIZE can impact prefetch performance. Why wouldn't we want
the optimizer to consider that?

Best regards,
John P. Higgins




-----Original Message-----
From: Terry Purcell [mailto:[login to unmask email]
Sent: Monday, January 06, 2003 5:34 PM
To: [login to unmask email]
Subject: Re: Access path Selection


Karthik,

I have not seen any evidence that SEGSIZE will alter access path selection.
It can however impact prefetch performance with multi-table segmented
tablespaces. This is documented in the manuals.

It wouldn't really surprise me if SEGSIZE is not considered for access path
selection, as there are much bigger fish to fry for access path selection.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Dash, Sushanta
Sent: Monday, January 06, 2003 5:50 AM
To: [login to unmask email]
Subject: Re: Access path Selection


Hi Karthik, Here I could say what I knew and experienced. Please wait for
others also. I guess your main aim is for altering the SEGSIZE and to
consider its impact at access path. The application uses mostly prefetch
then your altering the SEGSIZE to a big value would definitely improve the
performances. Mostly it would be beneficial if you go for 32 as
segsize. Similarly your space map pages would be at buffer pool and you
would get benefits. You may get advantage in reducing the number of
getpages. If the alter is towards more size then the insert queries would
work better than earlier.


With thanks
Dash S.K.

EDS/India Solution Center
Saturn/AAT Data Base Administration
Hello 28113801 through 28113815 ext-2366
e-mail: [login to unmask email] <mailto:[login to unmask email]>



-----Original Message-----
From: Karthick I [SMTP:[login to unmask email]
Sent: Monday, January 06, 2003 4:40 PM
To: [login to unmask email]
Subject: Access path Selection

Dear Experts,

Will Segement size influence the access path selection? . If
we alter
the Segement size for the segmented table space will it change the
access
path.

Thanks,
Regards,
Karthick.I
----------------------------------------------
AT-LINK & IBM Ops Support
Transco Support Project
Internal No : 715-32817
---------------------------------------------