RE: No Row Chaining "Bug" ?
Date: Thu, 21 May 2015 06:48:16 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D92828EC3B_at_EXMBX01.thus.corp>
Looking at the bug and the referenced doc about the hidden parameter, I'd guess someone did a misleading test and came to the wrong conclusion.
A row with 999 columns with a numeric 999th column set to zero and all the rest null would have a row length (on some counts) of 1,000 so falls into that grey area where Oracle might or might not insert it into a different block.
We also have the problem of the way Oracle has introduced some changes in the way it counts continued rows when handling intra-block chaining, and the last time I checked there were still some inconsistencies in the counting.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Chitale, Hemant K [Hemant-K.Chitale_at_sc.com] Sent: 21 May 2015 02:56
To: Kenny Payton
Cc: ORACLE-L
Subject: RE: No Row Chaining "Bug" ?
Yes, I came across this earlier :
Bug 9373758 - Optimization to handle insert of >255 columns (Doc ID 9373758.8)
Salem Ghassan also said that 20901134 was closed as Not a Bug and referred me back to 9373758.
Hemant K Chitale
From: Kenny Payton [mailto:k3nnyp_at_gmail.com]
Sent: Wednesday, May 20, 2015 8:13 PM
To: Chitale, Hemant K
Cc: ORACLE-L
Subject: Re: No Row Chaining "Bug" ?
Not for sure where the 999 comes from but take a look at bug #9373758 and the following parameter added in 11.2.0.4.4 and 12???? This appears to work for inserts, although subsequent updates seem to still become multiple row pieces.
"_rowlen_for_chaining_threshold"
I posted the below to this list in Jan.
"
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 )
"
On May 20, 2015, at 4:57 AM, Chitale, Hemant K <Hemant-K.Chitale_at_sc.com<mailto:Hemant-K.Chitale_at_sc.com>> wrote:
Anyone know what this Bug is :
Bug 20901134 : A ROW WITH 999 COLUMNS WILL NOT CAUSE 'ROW CHAINED' IN 12C
There’s no text in the bug.
Hemant K Chitale
Senior Product Specialist
Finance&Risk Product Engineering
Standard Chartered Bank
7 Changi Business Park Crescent
Singapore 486028
Phone : +65-6596 3127
Email : Hemant-K.Chitale_at_sc.com<mailto:Hemant-K.Chitale_at_sc.com>
Fonenet : 652-63127
This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html
This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 21 2015 - 08:48:16 CEST