About extent map block

By cui hua on September 22, 2009 4:09 PM | 1 Comment

Kostas Hairopoulos continued to ask me three questions：

1、What's the meaning of an extent map purpose however of an extent map since we have BMB blocks which already cover many extents as you mentioned?

2、Do we have always an extent map for every table or only if we allocate more extents except the initial allocation during table creation?

3、I checked the buffer touch counts for the staging table in my application and I noticed that extent map is not loaded in the buffer cache. I am wondering why

 

The answers are:

1、Extent map blocks are used where the extent map can no longer fit within the segment header. Extent map blocks are extent maps that overflow when the extent map area in the segment header block is not large enough to describe all extents that are used by a segment. Extent maps are achieved by a linked list of blocks containing extent map portions.

 

Let's see an example:

adcbth and uplpdt are two tables both reside in ASSM tablespace.

uplpdt have four extent map blocks. However, adcbth desn't have any.

 

SQL> select count(*) from armshistemp.adcbth;

 

  COUNT(*)

----------

       367

 

SQL> select count(*) from armshistemp.uplpdt;

 

  COUNT(*)

----------

   6962716

 

SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARMSHISTEMP' and segment_name='ADCBTH';

 

HEADER_FILE HEADER_BLOCK

----------- ------------

        112          908

 

SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARMSHISTEMP' and segment_name='UPLPDT';

 

HEADER_FILE HEADER_BLOCK

----------- ------------

        111           12

 

BBED> set dba 112,908

        DBA             0x1c00038c (469762956 112,908)

 

BBED> dump

 File: /dras14/oradata/astca/armshistemptbs_02.dbf (112)

 Block: 908              Offsets:    0 to  511           Dba:0x1c00038c

------------------------------------------------------------------------

 23020000 1c00038c 968702fd 00020104 c6020000 00000000 00000000 00000000

 00000000 00000001 00000080 0a9c0000 00000000 00000040 00000080 1c0003c9

 00000000 00000000 00000000 0000003c 00000000 00000000 00000000 00000000

 00000004 00000080 1c00038d 00000000 00000000 00000000 00000000 1c000389

 1c000389 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00010000 00002000 00000000 00001434

 00000000 1c00038b 00000001 1c00038a 1c00038b 00000000 00000000 00000000

 00000000 00000000 00000001 00000000 00014f15 20000000 1c000389 00000080

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 

 <32 bytes per line>

 

BBED> set dba 111,12

        DBA             0x1bc0000c (465567756 111,12)

 

BBED> dump

 File: /dras14/oradata/astca/armshistemptbs_01.dbf (111)

 Block: 12               Offsets:    0 to  511           Dba:0x1bc0000c

------------------------------------------------------------------------

 23020000 1bc0000c aa2aee16 00080104 65b90000 00000000 00000000 1ec3ef0a

 00000004 00000906 00048300 0a9c0000 00000905 00000080 00000080 1e443009

 1ec3ef0a 000001de 00000000 00047ffb 00000000 00000000 00000000 00000905

 00000080 00000080 1e443009 1ec3ef0a 000001de 00000000 00048300 1ec42a09

 1ec42a09 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00010000 00002000 00000000 00001434

 00000000 1bc0000b 00000001 1ec42a09 1bc0000b 00000000 00000000 00000000

 00000000 00000000 00000133 1ec3260a 00014f3c 20000000 1bc00009 00000080

 1e430009 00000080 1e82fd89 00000080 1ec2fc89 00000080 1f02fb89 00000080

 1e430089 00000080 1e82fe09 00000080 1ec2fd09 00000080 1f02fc09 00000080

 1e430109 00000080 1e82fe89 00000080 1ec2fd89 00000080 1f02fc89 00000080

 1e430189 00000080 1e82ff09 00000080 1ec2fe09 00000080 1f02fd09 00000080

 1e430209 00000080 1e82ff89 00000080 1ec2fe89 00000080 1f02fd89 00000080

 1e430289 00000080 1e830009 00000080 1ec2ff09 00000080 1f02fe09 00000080

 1e430309 00000080 1e830089 00000080 1ec2ff89 00000080 1f02ff09 00000080

 

 <32 bytes per line>

 

So, for table adcbth, there is no Extent Map Block！because the last EMB's dba and the next EMB's dba are both 0x00000000.

 

But for table uplpdt，the last EMB's dba is 0x1ec3ef0a and the next EMB's dba is 0x1ec3260a.

 

Let's see those two EMBs plus another two EMBs：

BBED> set dba 0x1ec3ef0a

        DBA             0x1ec3ef0a (516157194 123,257802)

 

BBED> dump

 File: /dras21/astca/armshistemptbs_05.dbf (123)

 Block: 257802           Offsets:    0 to  511           Dba:0x1ec3ef0a

------------------------------------------------------------------------

 24020000 1ec3ef0a 968db1d2 00020104 c7300000 000001df 00000000 00014f3c

 40000000 1ec3ef09 00000080 1f03ee89 00000080 1e43f409 00000080 1e83f109

 00000080 1ec3ef89 00000080 1f03ef09 00000080 1e43f489 00000080 1e83f189

 00000080 1ec3f009 00000080 1f03ef89 00000080 1e43f509 00000080 1e83f209

 00000080 1ec3f089 00000080 1f03f009 00000080 1e43f589 00000080 1e83f289

 00000080 1ec3f109 00000080 1f03f089 00000080 1e43f609 00000080 1e83f309

 00000080 1ec3f189 00000080 1f03f109 00000080 1e43f689 00000080 1e83f389

 00000080 1ec3f209 00000080 1f03f189 00000080 1e43f709 00000080 1e83f409

 00000080 1ec3f289 00000080 1f03f209 00000080 1e43f789 00000080 1e83f489

 00000080 1ec3f309 00000080 1f03f289 00000080 1e43f809 00000080 1e83f509

 00000080 1ec3f389 00000080 1f03f309 00000080 1e43f889 00000080 1e83f589

 00000080 1ec3f409 00000080 1f03f389 00000080 1e43f909 00000080 1e83f609

 00000080 1ec3f489 00000080 1f03f409 00000080 1e43f989 00000080 1e83f689

 00000080 1ec3f509 00000080 1f03f489 00000080 1e43fa09 00000080 1e83f709

 00000080 1ec3f589 00000080 1f03f509 00000080 1e43fa89 00000080 1e83f789

 00000080 1ec3f609 00000080 1f03f589 00000080 1e43fb09 00000080 1e83f809

 

 <32 bytes per line>

 

BBED> set dba  0x1ec3260a

        DBA             0x1ec3260a (516105738 123,206346)

 

BBED> dump

 File: /dras21/astca/armshistemptbs_05.dbf (123)

 Block: 206346           Offsets:    0 to  511           Dba:0x1ec3260a

------------------------------------------------------------------------

 24020000 1ec3260a 968c90a8 00020104 cf490000 000001fc 1ec3700a 00014f3c

 40000000 1ec32609 00000080 1f032589 00000080 1e432b09 00000080 1e832809

 00000080 1ec32709 00000080 1f032689 00000080 1e432b89 00000080 1e832889

 00000080 1ec32789 00000080 1f032709 00000080 1e432c09 00000080 1e832989

 00000080 1ec32809 00000080 1f032789 00000080 1e432c89 00000080 1e832a09

 00000080 1ec32889 00000080 1f032809 00000080 1e432d09 00000080 1e832a89

 00000080 1ec32909 00000080 1f032889 00000080 1e432d89 00000080 1e832b09

 00000080 1ec32989 00000080 1f032909 00000080 1e432e89 00000080 1e832b89

 00000080 1ec32a09 00000080 1f032989 00000080 1e432f09 00000080 1e832c09

 00000080 1ec32a89 00000080 1f032a09 00000080 1e432f89 00000080 1e832d09

 00000080 1ec32b89 00000080 1f032b09 00000080 1e433009 00000080 1e832d89

 00000080 1ec32c09 00000080 1f032b89 00000080 1e433109 00000080 1e832e09

 00000080 1ec32c89 00000080 1f032c09 00000080 1e433189 00000080 1e832e89

 00000080 1ec32d09 00000080 1f032c89 00000080 1e433209 00000080 1e832f09

 00000080 1ec32d89 00000080 1f032d09 00000080 1e433289 00000080 1e833009

 00000080 1ec32e09 00000080 1f032d89 00000080 1e433309 00000080 1e833089

 

 <32 bytes per line>

 

BBED> set dba 0x1ec3700a

        DBA             0x1ec3700a (516124682 123,225290)

 

BBED> dump

 File: /dras21/astca/armshistemptbs_05.dbf (123)

 Block: 225290           Offsets:    0 to  511           Dba:0x1ec3700a

------------------------------------------------------------------------

 24020000 1ec3700a 968cf558 00020104 64b90000 000001fc 1ec3af89 00014f3c

 40000000 1ec37009 00000080 1f036f89 00000080 1e437509 00000080 1e837209

 00000080 1ec37089 00000080 1f037009 00000080 1e437589 00000080 1e837289

 00000080 1ec37109 00000080 1f037089 00000080 1e437609 00000080 1e837309

 00000080 1ec37189 00000080 1f037109 00000080 1e437689 00000080 1e837389

 00000080 1ec37209 00000080 1f037189 00000080 1e437709 00000080 1e837409

 00000080 1ec37289 00000080 1f037209 00000080 1e437789 00000080 1e837489

 00000080 1ec37309 00000080 1f037289 00000080 1e437809 00000080 1e837509

 00000080 1ec37389 00000080 1f037309 00000080 1e437889 00000080 1e837589

 00000080 1ec37409 00000080 1f037389 00000080 1e437909 00000080 1e837609

 00000080 1ec37489 00000080 1f037409 00000080 1e437989 00000080 1e837689

 00000080 1ec37509 00000080 1f037489 00000080 1e437a09 00000080 1e837709

 00000080 1ec37589 00000080 1f037509 00000080 1e437a89 00000080 1e837789

 00000080 1ec37609 00000080 1f037589 00000080 1e437b09 00000080 1e837809

 00000080 1ec37689 00000080 1f037609 00000080 1e437b89 00000080 1e837889

 00000080 1ec37709 00000080 1f037689 00000080 1e437c09 00000080 1e837909

 

 <32 bytes per line>

 

BBED> set dba 0x1ec3af89

        DBA             0x1ec3af89 (516140937 123,241545)

 

BBED> dump

 File: /dras21/astca/armshistemptbs_05.dbf (123)

 Block: 241545           Offsets:    0 to  511           Dba:0x1ec3af89

------------------------------------------------------------------------

 24020000 1ec3af89 968d5057 00020104 a6220000 000001fc 1ec3ef0a 00014f3c

 40000000 1ec3af89 00000080 1f03af09 00000080 1e43b489 00000080 1e83b189

 00000080 1ec3b009 00000080 1f03af89 00000080 1e43b509 00000080 1e83b209

 00000080 1ec3b089 00000080 1f03b009 00000080 1e43b589 00000080 1e83b289

 00000080 1ec3b109 00000080 1f03b089 00000080 1e43b609 00000080 1e83b309

 00000080 1ec3b189 00000080 1f03b109 00000080 1e43b689 00000080 1e83b389

 00000080 1ec3b209 00000080 1f03b189 00000080 1e43b709 00000080 1e83b409

 00000080 1ec3b289 00000080 1f03b209 00000080 1e43b789 00000080 1e83b489

 00000080 1ec3b309 00000080 1f03b289 00000080 1e43b809 00000080 1e83b509

 00000080 1ec3b389 00000080 1f03b309 00000080 1e43b889 00000080 1e83b589

 00000080 1ec3b409 00000080 1f03b389 00000080 1e43b909 00000080 1e83b609

 00000080 1ec3b489 00000080 1f03b409 00000080 1e43b989 00000080 1e83b689

 00000080 1ec3b509 00000080 1f03b489 00000080 1e43ba09 00000080 1e83b709

 00000080 1ec3b589 00000080 1f03b509 00000080 1e43ba89 00000080 1e83b789

 00000080 1ec3b609 00000080 1f03b589 00000080 1e43bb09 00000080 1e83b809

 00000080 1ec3b689 00000080 1f03b609 00000080 1e43bb89 00000080 1e83b889

 

 <32 bytes per line>

 

The first byte of those blocks are 0x24, means Pagetable extent map block.

From the dump contents of above four blocks, we can clearly see that those EMB are linked together.

 

If adcbth and uplpdt are reside in MSSM tablespace, then the first byte of those EMB blocks are 0x12，and you can track those EMB in MSSM by using ktemh struct in segment header block.

 

2、No. we don't always have EMB for every table. Extent map blocks are created where the extent map can no longer fit within the segment header.

 

3、You are right，EMB blocks are not loaded into the buffer cache nor dictionary cache, but I don't know why.

Categories:

1 Comment

Kostas Hairopoulos | March 9, 2010 4:22 PM | Reply

Your response was really excellent.
I really appreciated it. I still cannot figure out how did you know the exact location for extent map DBA inside the header block.

Thank you for sharing your knowledge with the oracle community


Warm Regards
kostas

Leave a comment