[Info-Ingres] Lock escalation caused by bad page estimate

[Karl & Betty Schendel]

At 2:19 PM +0100 9/21/07, Martin Bowes wrote:
>Hi Karl,
> 
>And what does an F stand for? Be nice. Would it be an overflow on an index leaf?

F is a free page (as opposed to U = never used)

H is  the FHDR, free-space management header.
M is one of the FMAP free-space management map pages.

r is the btree root.
s is a sprig page, ie a bottom page of a btree index.
i is a between-root-and-sprig index page.
L is a btree leaf.
O is a btree overflow leaf (2k page only)
o is an overflow data page
U is an unused page
d is an empty data page
e is an empty overflow page
(the last two might only appear on the "hash structure" section
of a hash table dump, haven't bothered to look closely!)

$ING_SRC/back/dmf/dmp/dm1p.c, look for dm1p_dump

Karl



>
>From: info-ingres-bounces at kettleriverconsulting.com on behalf of Betty & Karl Schendel
>Sent: Fri 21/09/2007 12:04
>To: info-ingres at kettleriverconsulting.com
>Subject: Re: [Info-Ingres] Lock escalation caused by bad page estimate
>
>At 11:52 AM +0100 9/21/07, Martin Bowes wrote:
>>...
>>I tried a modify table to table_debug with table_option=2 and
>>counted 116 pages (ie. a lot more than 40) flagged with either a D
>>or an A. I also examined the tids in the table to check for page
>>number and came up with 116 distinct values. The sequence of page
>>numbers and D's and A's matches as well.
>>
>>I don't know what an 'A' page is - they clearly have data on them - any ideas?
>>
>
>A is an Associated data page, ie the data page currently being used
>for row inserts from a leaf.  The data page is associated to that leaf,
>hence the name.
>
>>But of course the optimiser is a *LOT* smarter than I am. It
>>realised that the query didn't rely on data just on keys, so it only
>>need to scan the L pages holding the Key details to do the outer
>>join. And guess what - theres 40 of them, and those are the pages
>>that the lock trace show are being opened.
>
>Nice catch, I was wondering where the 40 came from.  Didn't
>think of leaf-only scans.
>
>Karl
>_______________________________________________
>Info-Ingres mailing list
>Info-Ingres at kettleriverconsulting.com
><http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres>http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres