Re: Larger vs. Small data block
Posted: Jun 16, 2008 1:48 PM
> Good news, I think I can emulate your problem - and
> give you the solution. (That’s assuming my guesses
> about your setup are correct).
Headline results for update:
16KB Block size: 1 hour 36 minutes 45.06 seconds 8KB Block size: 1 minute 1.08 seconds 4KB Block size: 1 minute 28.00 seconds
The tablespaces are locally managed with a uniform extent size of 128KB and using ASSM which, I think, is in accordance with the description given by Steve Karam.
The SQL for creating, populating, and updating the table is given below.
You will note that in my test case the rows are very short, and the updated column starts out null. A typical row starts at 9 bytes (11 if you count the row index entry), and grows to 15 (17) bytes. This means that the default pctfree of 10 is much too small, and a large number of rows will migrate leaving a 9 (11) byte forwarding address. This means that the table needs to be defined with a pctfree of around 35 if it is avoid problems with rows migrating. (In my second test run I used 50 to avoid having to be too exact).
A combination of short rows, mass row extension, poor choice of pctfree, and large blocks seems to cause ASSM some problems identifying a block that will be able to accept a migrated row - and it uses a lot of resources searching for a suitable block.
There was a bug of this nature in early releases of ASSM, but I thought it had been fixed. Possibly the fix had an arithmetical component that was based on an 8KB block size and was not tested in extreme cases against larger block sizes.
execute dbms_random.seed(0); drop table t1; create table t1 ( n1 number, n2 number ) -- pctfree 50 tablespace test_4k_assm ; insert into t1 with generator as ( select --+ materialize rownum id from all_objects where rownum <= 3000 ) select trunc(dbms_random.value(10000000,100000000)) n1, to_number(null) n2 -- trunc(dbms_random.value(10000000,100000000)) n2 from generator v1, generator v2 where rownum <= 830000 ; commit; alter session set events '10046 trace name context forever, level 8'; update t1 set n2 = n1;
As part of my test code, I also took snapshots of v$mystat, v$session_event, and x$kcbsw/x$kcbwh (see: http://www.jlcomp.demon.co.uk/buffer_usage.html for further details on the last one). Here are some of the key statistics:
16KB Block size --------------- pctfree 10 pctfree 50 Time 1:36:45.06 21.07 Wait time 2.94 12.65 db block get 845,084,110 848,345 redo entries 2,161,504 830,503 redo size 491,906,180 186,504,584 Critical buffer get calls ktspfwh10: ktspscan_bmb 144,587,672 0 ktspbwh1: ktspfsrch 696,965,277 0 8KB Block size --------------- pctfree 10 pctfree 50 Time 1:01.08 20.01 Wait time 19.16 11.69 db block get 5,526,488 848,321 redo entries 2,172,130 830,399 redo size 492,560,476 186,542,972 Critical buffer get calls ktspfwh10: ktspscan_bmb 1,320,444 0 ktspbwh1: ktspfsrch 664,235 0 4KB Block size --------------- pctfree 10 pctfree 50 Time 1:28.00 21.04 Wait time 39.47 13.01 db block get 5,547,182 851,170 redo entries 2,183,488 830,458 redo size 493,455,356 186,632,124 Critical buffer get calls ktspfwh10: ktspscan_bmb 1,321,618 0 ktspbwh1: ktspfsrch 668,945 0
Most of the wait time recorded in my tests was due to log buffer space waits.
You will note that my model is obviously not an exact match for the details Steve Karam gave - compared to his figures, the increase I saw in current gets is too large and the increase in redo log may not be large enough to be particularly significant. This suggests that a smaller percentage of rows in his data were subject to migration, and that some of the excess work may have been related to delayed block cleanout.
“The greatest enemy of knowledge is not ignorance,
it is the illusion of knowledge.” (Stephen Hawking)