Insert space serach alogoritm in partitoned vs segmented

venkata rama rajesh mallina

Insert space serach alogoritm in partitoned vs segmented

In below link 7 and 8 slides there are space search steps

http://www.gsebelux.com/sites/default/files/Insert%20Performance%20V9_V10%20outlook.pdf

I am trying understand the differences of INSERT space search process between classic partitioned and segmented.

And I am trying justify myself how segmented is better. But I am getting confused.

Could some one put space search steps for me.

 

Venkata Rama Rajesh

Michael Hannan

RE: Insert space serach alogoritm in partitoned vs segmented
(in response to venkata rama rajesh mallina)

Venkata ...,

The search for freespace for Insert, is a very complex subject. In old days it used to subtly change at almost every release or at least the wording changed in the Diagnosis manual. Was also hard to understand the description there, when DB2 actually checked a page and when it just checked the Spacemap.

John Campbell's (IBM Distinguished Engineer) presentations and papers are the authoritative guide to this.

I will quote just one small part of one of his presentations:

"So, the advantage of segmented table spaces is that four bits of information per data page
in the space map with the classic partitioned and simple linear table spaces, there's only
two bits per data page in the space map. So, these four bits instead of two bits provides
more granular information, and it can reduce the number of situations, where you can get
a false lead. A false lead being when the space map page indicates a space in the data
page, and then when DB2 goes to the respective data page, there's actually insufficient
space to absorb the new row.
But there are pros and cons on this. On one hand, having four bits per data page, means
that there's better chance of avoiding these false leads. The bad news is clearly there's
more updates to be done on the space map pages when you have-high volume insert
processing, or high-volume update processing."

I am not going to try to go through the exhaustive space search algorithm. I would mainly have to be quoting John anyway for fear of getting it a bit wrong.

TRACKMOD NO is important to cut the level of Spacemap update, especially in Data Sharing.

Append and MC00 are measures to prevent long space search.

We also have to bear in mind that ability to Insert to page  requires a conditional lock (for page level locking), or in Data Sharing, also a Page p-Lock (Global member extended time Latch is how I think of it) when row level locking is used.

I recently tuned a very expensive Insert process, not so much from contention, and not even massive Insert rates. Somewhat exhaustive space search was costly and I believe that Conditional locks were taken on pages only to find there was insufficient space in the page (despite Spacemap false leads due to lack of granularity of the spacemap bits).  The table needed a Reorg badly quite clearly and we decided to use Append, with MC just in case. After the change, Lock counts dropped and performance improved dramatically. Note that the Spacemap pages do not get logical locks, only latches, or Page p-locks (the Global latch). Was a very old table I think,so don't remember if was a UTS or not, or whether Segmented (most likely) or partitioned (unlikely). That was not quite the main issue for me at the time. Row length varied a lot, but problem most likely was a for a new frequent extra long row length.

V12 has added the extra subtlety of  Insert Algorithm 2, to make sure concurrent inserters on same DS member are trying to append to different pages instead of conditional lock fighting for pages to Insert to, because MC is only separating the cross member concurrent processes.

In the modern day we UTS, either partitioned by growth or range partitioned, so IBM consider having segments to be better then traditional partitioned spaces, I guess. 

If an old partitioned space is having Insert performance problems, would I convert it to UTS with segments? Not very likely. I think there are other measures first, like tuning freespace, freespace for update, MAXROWS, running Reorg, Append, MC, etc.

venkata rama rajesh mallina

RE: Insert space serach alogoritm in partitoned vs segmented
(in response to Michael Hannan)

Hi Michael

I kept 2 bit and 4 bit free space status info. Could you kindly give one example for possibility of "false lead". And which can avoided in 4 bit process.

2 bit info

B'00' The amount of free space available in the data page is greater
than or equal to the maximum size of the record.

B'01' The amount of free space available in the data page is greater
than or equal to the average size of the record and is less than
the maximum size of the record.
B'10' The amount of free space available in the data page is greater
than or equal to the minimum size of the record and is less than
the average size of the record.
B'11' The page is full almost.

4 bit info

B'0000' The page is empty (not yet formatted by the DM)
B'0001' The page is empty (caused by a mass delete)
B'0010' The page is empty (caused by a normal delete)
B'0011' The amount of free space available in the data page is greater than or equal to the maximum size of the record.
B'xxxx' B'xxxx' = B'0100' through B'1010' = he amount of free space available < maximum size of record and more than minimum size of record for variable length rows
B'1111'  The page is full almost.

Venkata Rama Rajesh

Michael Hannan

RE: Insert space serach alogoritm in partitoned vs segmented
(in response to venkata rama rajesh mallina)

In Reply to venkata rama rajesh mallina:

Hi Michael

I kept 2 bit and 4 bit free space status info. Could you kindly give one example for possibility of "false lead". And which can avoided in 4 bit process.

You are stretching it. Ideally you should address the forum with questions unless they are very specific to me. Anyone interested or very knowledgeable could answer. I can't say I have looked into it in such bitwise immense detail as what you are trying to do. So I am not the World expert on this topic. LOL. I have enough knowledge to tune Inserts showing poor performance and did so recently.

I took John Campbell's and other's word for it that 4 bits gives better granularity and can reduce the chance of a false lead, notice it did not claim to eliminate false leads. I have seen the bad performance with a false lead, possibly when suddenly rows of a different size to those ever Inserted before were attempted. I don't know if max size means max possible length or max length previously found at some point. Max theoretical length might not be useful for VARCHARs where actual max length doesn't come close.

When I wanted to know really seriously in depth stuff, I sent my questions to Jim Teng (Distinguished Eng) in the old days and got some very fine answers that improved my understanding of Data Mgr, and sometimes Akira Shibamiya. These days I still ask Terry Purcell a few things related to the Optimizer. Lucky he reads the DB2 Listserv too.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

venkata rama rajesh mallina

Insert space serach alogoritm in partitoned vs segmented
(in response to Michael Hannan)
Thanks Michael Thanks & Regards
Venkata Rama Rajesh IT Specialist - Mainframe DB2 DBA  IBM Certified Database Administrator IBM Certified Solution DeveloperIBM Certified Database AssociateEmail : [login to unmask email]: +91-7338817414 IBM Services ----- Original message -----
From: Michael Hannan <[login to unmask email]>
To: [login to unmask email]
Cc:
Subject: [DB2-L] - RE: Insert space serach alogoritm in partitoned vs segmented
Date: Thu, Jul 5, 2018 4:07 PM

In Reply to venkata rama rajesh mallina:
Hi Michael
I kept 2 bit and 4 bit free space status info. Could you kindly give one example for possibility of "false lead". And which can avoided in 4 bit process.
You are stretching it. Ideally you should address the forum with questions unless they are very specific to me. Anyone interested or very knowledgeable could answer. I can't say I have looked into it in such bitwise immense detail as what you are trying to do. So I am not the World expert on this topic. LOL. I have enough knowledge to tune Inserts showing poor performance and did so recently.

I took John Campbell's and other's word for it that 4 bits gives better granularity and can reduce the chance of a false lead, notice it did not claim to eliminate false leads. I have seen the bad performance with a false lead, possibly when suddenly rows of a different size to those ever Inserted before were attempted. I don't know if max size means max possible length or max length previously found at some point. Max theoretical length might not be useful for VARCHARs where actual max length doesn't come close.
When I wanted to know really seriously in depth stuff, I sent my questions to Jim Teng (Distinguished Eng) in the old days and got some very fine answers that improved my understanding of Data Mgr, and sometimes Akira Shibamiya. These days I still ask Terry Purcell a few things related to the Optimizer. Lucky he reads the DB2 Listserv too.
Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Faster data refresh is here! The long waits and babysitting of unload/load jobs is over. Contact
ESAi to learn about BCV5 & XDM. Be a hero to users with fast on-demand test/QA data provisioning.See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2