Re: ASSM tablespace three level bmp block questions.

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Tue, 31 Mar 2009 02:06:55 -0500
Message-ID: <ljjAl.21990$YU2.4015_at_nlpi066.nbdc.sbc.com>

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 Received on Tue Mar 31 2009 - 02:06:55 CDT