Chained Rows - Inter Block Chaining
Date: Thu, 8 Jan 2015 21:18:15 -0500
Message-Id: <AC054E78-E70C-430D-B66E-EA26179541CF_at_gmail.com>
Sorry if this is a duplicate post, got an error back on my 1st attempt.
I can’t believe I’ve put this off since April, although not a coincidence that my company was acquired in May.
I was finally able to test the hidden parameter and it works ( mostly ). I used imp, trying to keep it simple, to load 100k rows that were plagued with populated column ID’s > 255. With the parameter set to 0, simulating pre-parameter, the data loaded with 3,420 chained rows. After setting the parameter to 8000, with a 8k block size database, I reloaded the data and although I expected the chained rows to be 0 it left 29. I have yet to explain the 29 since they are not too large to fit in a block, average row size of 852, and were less than the 8000 I set the parameter to. After a CTAS of the previously loaded table the new resulting table with the same 100k rows returned 0 chained rows. I ran the load process a number of times consistently with the same results.
Nonetheless, although not perfect it seems to make a significant dent in the number of inter block chained rows and after some additional testing we will be excited to get this into production. If I can find some time to dig deeper in this I’ll try to dump the block and see what is going on.
In case you were wondering, here is the parameter. _rowlen_for_chaining_threshold
Thanks again Gaja for pointing out the bug ( way back in April ).
Thanks,
Kenny
> On Apr 29, 2014, at 8:39 AM, Kenny Payton <K3nnyp_at_gmail.com <mailto:K3nnyp_at_gmail.com>> wrote:
>
>
> Bug #9373758 seems like a silver bullet for what I’ve been looking for. Thanks. We will be testing this soon and hopefully roll it into production in the upcoming weeks.
>
> Kenny
>
>
>
> On Apr 27, 2014, at 6:29 PM, Kenny Payton <k3nnyp_at_gmail.com <mailto:k3nnyp_at_gmail.com> <mailto:k3nnyp_at_gmail.com <mailto:k3nnyp_at_gmail.com>>> wrote:
>
>> Thanks for the note. I will dig into it tomorrow. In my mind, splitting the table into two doesn't help when you need the 2nd half of the row. You still end up with multiple block reads unless they are clustered and also have to deal with another index. >> >>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 09 2015 - 03:18:15 CET