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

Hi Karl,

And what does an F stand for? Be nice. Would it be an overflow on an index leaf?

Marty

________________________________

From: info-ingres-bounces@xxxxxxxxxxxxxxxxxxxxxxxxx on behalf of Betty & Karl Schendel
Sent: Fri 21/09/2007 12:04
To: info-ingres@xxxxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres


Relevant Pages

  • Re: [Info-Ingres] Lock escalation caused by bad page estimate
    ... Martin Bowes wrote: ... I tried a modify table to table_debug with table_option=2 and counted 116 pages 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 data page is associated to that leaf, ... 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. ...
    (comp.databases.ingres)
  • Re: [Info-Ingres] Lock escalation caused by bad page estimate
    ... Martin Bowes wrote: ... I tried a modify table to table_debug with table_option=2 and counted 116 pages 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 data page is associated to that leaf, ... 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. ...
    (comp.databases.ingres)