Hi,
in another thread How can this deadlock occur? , i joyed Jonathan's method to get block_id for table rows. The idea was that block id identifies chunks to be processed by parallel jobs.
mod(dbms_rowid.rowid_block_number(rowid)) = {process identifier}
Then i got reminder, and i have no clue what Jonathan says here.
Remember that it's block within file number, and it's only in very simple tests that an object consists of a consecutive set of blocks in a single file.
This is just a very simple-minded trick that could, if you're a little unlucky, produce a very uneven distribution of work.
(Considering the possibilities, I might suggest that the number of processes used should be a prime number to avoid some of the little quirks of ASSM and space management blocks with small extents.)
Regards
Jonathan Lewis
So i spent hours playing with files, extents and blocks to understand that. No luck!
Question is, how to chunk table rows that it's safe for multiple session processing. Well let's play that i have 10.2, although i have 12.1 and dbms_execute_parallel package.
Here's my poc (at least relearned a lot about forgotten storage stuff)
alter system set
db_create_file_dest = 'c:\app\oracle\oradata\poc\'
scope = memory
;
create tablespace blocktest
datafile size 80k, size 80k
extent management local uniform size 40k -- minimum extent 5 x block size
;
alter user jarkko
quota unlimited on blocktest
;
create table jarkko.blocktest(
text varchar2( 2048 )
)
tablespace blocktest
;
begin
for r in (select object_id from dba_objects)
loop
insert into jarkko.blocktest ( text )
values ( dbms_random.string( 'p', 2048 ) );
end loop;
exception when others then
commit;
end;
/
column segment_name format a20
select segment_name, segment_type, extents, blocks
from dba_segments
where tablespace_name = 'BLOCKTEST'
;
select file_id, extent_id, block_id, blocks, bytes, bytes / blocks block_size
from dba_extents
where tablespace_name = 'BLOCKTEST'
order by file_id, extent_id
;
select dbms_rowid.rowid_block_number( blocktest.rowid ) Block_id, rowid from jarkko.blocktest
;
drop tablespace blocktest including contents
;
System SET altered.
Tablespace BLOCKTEST created.
User JARKKO altered.
Table JARKKO.BLOCKTEST created.
PL/SQL procedure successfully completed.
SEGMENT_NAME SEGMENT_TYPE EXTENTS BLOCKS
-------------------- ------------------ ---------- ----------
BLOCKTEST TABLE 2 10
FILE_ID EXTENT_ID BLOCK_ID BLOCKS BYTES BLOCK_SIZE
---------- ---------- ---------- ---------- ---------- ----------
8 0 4 5 40960 8192
9 1 4 5 40960 8192
BLOCK_ID ROWID
---------- ------------------
7 AAAcs4AAIAAAAAHAAA
7 AAAcs4AAIAAAAAHAAB
7 AAAcs4AAIAAAAAHAAC
8 AAAcs4AAIAAAAAIAAA
8 AAAcs4AAIAAAAAIAAB
8 AAAcs4AAIAAAAAIAAC
4 AAAcs4AAJAAAAAEAAA
4 AAAcs4AAJAAAAAEAAB
4 AAAcs4AAJAAAAAEAAC
5 AAAcs4AAJAAAAAFAAA
5 AAAcs4AAJAAAAAFAAB
5 AAAcs4AAJAAAAAFAAC
6 AAAcs4AAJAAAAAGAAA
6 AAAcs4AAJAAAAAGAAB
6 AAAcs4AAJAAAAAGAAC
7 AAAcs4AAJAAAAAHAAA
7 AAAcs4AAJAAAAAHAAB
7 AAAcs4AAJAAAAAHAAC
8 AAAcs4AAJAAAAAIAAA
8 AAAcs4AAJAAAAAIAAB
8 AAAcs4AAJAAAAAIAAC
21 rows selected
Tablespace BLOCKTEST dropped.