Tag Archives: row chaining

Intra-block row chaining optimization in 12.2

Posted on by Posted in 12c, oracle, trace, undocumented 2,137 Page views 4 Comments

I’ve wrote in previous post

4. Next row piece can be stored in the same block only with inserts. When you run update, oracle will place new row piece into another block.

But it’s not valid anymore 🙂 Since 12.2 Oracle optimizes updates too.
You can check it on 12.2 and previous version using example 4 from previous post:

Test 4

[sourcecode language=”sql”]drop table test purge;
set serverout on
alter session set tracefile_identifier=’test4′;

declare
cols varchar2(32000):=’c_1 char(3)’;

procedure print_and_exec(c varchar2) as
begin
dbms_output.put_line(c);
execute immediate c;
end;
begin
for i in 2..355 loop
cols:=cols||’,c_’||i||’ char(3)’;
end loop;
print_and_exec (‘create table test(‘||cols||’)’);
print_and_exec (‘insert into test(c_1) values(null)’);
commit;
for i in 256..355 loop
execute immediate ‘update test set c_’||i||’=’||i;
end loop;
commit;
execute immediate ‘alter system flush buffer_cache’;
for r in (select ‘alter system dump datafile ‘||file#||’ block ‘||block# cmd
from (
select distinct file#,block#
from v$bh
where class#=1
and objd in (select o.data_object_id from user_objects o where object_name=’TEST’)
order by 1,2
)
)
loop
execute immediate r.cmd;
end loop;
end;
/
disc;[/sourcecode]

[collapse]

On 12.2 you will see that it creates just 5 blocks 🙂

PS. My presentation about Intra-block row chaining from RuOUG seminar: Intra-block row chaining(RuOUG)

Why you dont want to set _partition_large_extents FALSE

Posted on by Posted in oracle, SmartScan, undocumented 2,134 Page views Leave a comment

I’ve seen some blogs recommending that _partition_large_extents be set to FALSE for a variety of space conserving reasons without the authors thinking about the negative impact this is going to have on Smart Scan. Large Extents cause an INITIAL allocation of 8 MB and a NEXT allocation of 1 MB and they have been the default for table spaces on Exadata since 11.2.0.2. You can verify that large extents are in use by a given table or partition by:

Select segment_flags 
From sys_dba_segs 
where segment_name = <table_name> 
and owner = <schema_name>;

The segment flag bit for large extents is 0x40000000.

This pattern of allocation is design to work optimally with Smart Scan because Smart Scan intrinsically works in 1 MB chunks.  Reads of ASM allocation units are split into maximum 1 MB chunks to be passed to the filter processing library to have their blocks sliced and diced to create the synthetic blocks that contain only the rows and columns of interest to return to the table scan driver. When less than 1 MB gets allocated at a time to a segment and then the next contiguous blocks gets allocated to a different  segment, each separate run of blocks will be read by a different MBR. Each run will be passed separately to Smart Scan and we get sub-optimal chunks to work on increasing both the overhead of processing and the number of round trips needed to process the table scan. The design of Smart Scan is predicated on scooping up contiguous runs of data from disk for efficient processing.

This matters particularly for HCC data and for chained rows.

Continue reading→

row pieces, 255 columns, intra-block row chaining in details

Posted on by Posted in oracle, trace, undocumented 2,892 Page views 11 Comments

You might be familiar with intra-block row chaining, which can occur when a table has more than 255 columns. However, did you know that intra-block chaining only works with inserts, not updates? (Upd: This is not valid since version 12.2).

Documentation says:

When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row’s pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.

A bit more details:
1. A single row piece can store up to 255 columns.
2. Oracle splits fields into row pieces in reverse order.
3. Oracle doesn’t store trailing NULLs in a row (not in a row piece)
4. The next row piece can be stored in the same block only with inserts. When you run an update, oracle will place the new row piece into a different block. (not valid since 12.2)

I’ll show in examples with dumps:

Continue reading→