Re: ASSM tablespace three level bmp block questions.

lsllcm wrote:
Hi All

I have one question about ASSM tablespace. The ASSM tablespace has
three level BMP blocks to
indicate if block is used. How many blocks does one L1 BMP block
manages? After do one test, I found
the number increases when the data increases. Is there any rule about
it?

How many L1 BMP blocks does one L2 BMP block manages. After do the
test, I found the number 241 when
the blocksize is 2048 bytes. Is that right?

Any answers are appreciated.

Below is test case.

1. drop tablespace and create tablespace
drop tablespace aaa including contents and datafiles;
CREATE TABLESPACE aaa DATAFILE
'a' SIZE 20m AUTOEXTEND ON
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 10k
BLOCKSIZE 2K
SEGMENT SPACE MANAGEMENT AUTO;

2. create table
create table tt ( x number ) tablespace aaa
storage (initial 1000K);
;

3. get first block id
exec show_space ( 'TT','AUTO', 'TABLE' );

4. insert enough data and make sure the table has more than 1 L2 BMP
begin
for i in 1 .. 5500000 loop
insert into tt values(i);
end loop;
commit;
end;
/

exec show_space ( 'TT','AUTO', 'TABLE' );

5. dump the segment header
alter system dump datafile 7 block min 33 block max 35;

6. get three L2 BMP block
frmt: 0x02 chkval: 0x41dd type: 0x23=PAGETABLE SEGMENT HEADER

Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01c00022
DBA 2: 0x01c032a4
DBA 3: 0x01c06b20

7. dump all L2 BMP blocks and get all list of L1 BMP blocks

8. create table test (
seq -- sequence
chex -- hex dba
cdec -- decimal dba
diff -- L1 BMP block DBA range number
)

9, load hex dba of the L1 BMP blocks into table test

10. update decimal dba
update exte set cdec = to_number(chex,'XXXXXXXX');

11. get L1 BMP block DBA range number
update exte AA set diff = cdec - (select cdec from exte BB where
AA.seq = BB.seq+1);

12. get below test result
seq hex DBA dec DBA DBA range number
--------------------------------------------------
1 01c00021 29360161 15
...
36 01c0025b 29360731 60
...
573 01c0804b 29392971 80

13. L2 BMP has max 241 L1 range number

Thanks
Jacky



Here is a good start...

http://www.oracle.com/technology/products/manageability/database/pdf/ow04/1241_minhas_pres.pdf
.