ASSM tablespace three level bmp block questions.
lsllcm
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
Michael Austin
lsllcm
> Here is a good start...
>
>
> - Show quoted text -
Hi Michael,
Thanks for the document, but I still cannot find the answer from the
document
Thanks
Jacky
Michael Austin
The last thing I have seen on this level of information is: "Oracle does
not publish the internals of space management" - This was one of the
"features" Oracle obtained when they purchased the DEC/Rdb product. IIRC
there was a particular patent that is used to make all of this work. If
you can find some of the old DEC/Rdb internals manuals, you may get a
better idea of how this works. In Rdb, you could actually set the SPAM
interval. There are Area Inventory pages (AIP), "Area" bitmaps (aka
tablespace bitmaps aka ABM) and SPAce Management bitmaps) and Data
pages. A data page was 1-to-n multiple of the disk block size. So, in
VMS all data on disks were stored in 512byte blocks. A "page size"
(configurable) of 1024 was 2 disk blocks. The SPAM interval (in Rdb) was
the number of data_pages per SPAM page. On disk if the DATA:SPAM was
100, you would have a SPAM page followed contiguously as possible by 100
data pages of size 1024bytes. The ability to size these very small was
to reduce high volume insert contention. In the Oracle/RDBMS
implementation a data page is know as an extent (they didn't want to
confuse the masses with new terminology.) They also seemed have changed
the terminology of the SPAM "threshold" (fullness value) and when it
should "consider" adding more data to this page (pctfree/used concept).
One other note, there are some additional differences and possibly new
concepts (as stated, none of this is actually published out side of
Oracle) in the current ASSM implementatin but this is what happens when
the best minds in the database engine arena get together.
Randolf Geist
> Thanks for the document, but I still cannot find the answer from the
> document
Jacky,
Tanel Poder has done a quite detailed explanation and comparison of
ASSM and freelist management, although it's already a bit outdated I
think the key aspects are still applicable to the latest Oracle
versions, and it might cover some of your detailed questions:
<a href="http://perfvision.com/docs/
Poder_Freelists_vs_ASSM.ppt">Freelists vs. ASSM presentation</a>
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
lsllcm
> lsllcmwrote:
> >> Here is a good start...
>
Thanks, I need some time to understand it
lsllcm
I also read this document, in the document, it states that Level 1 BMP
block has 64 - 1024 DBA range.
Thanks
Jacky
Michael Austin
I believe it also stated that this was the observed behavior and not
actually from any valid Oracle documentation on the subject.
> Thanks
> Jacky
Jonathan Lewis
"lsllcm" <lsl...@gmail.com> wrote in message
news:88e531d4-55df-4e53...@d25g2000prn.googlegroups.com...
> 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.
>
I don't think there is any public documentation of such things, and
given the apparent flexibility of the algorithms I don't think you can
expect a solid answer.
Your 241 L1's per L2 in a 2KB block looks about right since an L1
reference seems to be 8 bytes when you dump an L2,
The number of blocks referenced by an L1 BMB depends on the extent
sizes and the block sizes - larger extents lead to more blocks more L1 BMB,
for example I have a 64MB extent size with an 8KB block that gives 256
blocks
per L1 BMP. An entry in an L1 block needs only 4 bits - so an single L1
block could reference far more that the 1024 data blocks that Tanel quotes
in his presentation , but I'd guess that the actual number is restricted to
avoid
excessive content on L1 blocks.
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
lsllcm
> >> On Apr 1, 1:10 pm,lsllcm<lsl...@gmail.com> wrote:
>
> >>> Thanks for the document, but I still cannot find the answer from the
> >>> document
> >> Jacky,
>
> >> Tanel Poder has done a quite detailed explanation and comparison of
> >> ASSM and freelist management, although it's already a bit outdated I
> >> think the key aspects are still applicable to the latest Oracle
> >> versions, and it might cover some of your detailed questions:
>
> >> <a href="http://perfvision.com/docs/
> >> Poder_Freelists_vs_ASSM.ppt">Freelists vs. ASSM presentation</a>
>
> >> Regards,
> >> Randolf
>
> >> Oracle related stuff blog:http://oracle-randolf.blogspot.com/
>
>
> > I also read this document, in the document, it states that Level 1 BMP
> > block has 64 - 1024 DBA range.
>
> I believe it also stated that this was the observed behavior and not
> actually from any valid Oracle documentation on the subject.
>
>
>
> > Thanks
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
Yes, the document does not state from any valid oracle document
lsllcm
wrote:
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Hi Jonathan,
Thanks for your answer.