http://forums.oracle.com/forums/message.jspa?messageID=2592642#2592642
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.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

“The greatest enemy of knowledge is not ignorance,
it is the illusion of knowledge.” (Stephen Hawking)