Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: LMT MAXEXTENTS for 8K Blocksize

Re: LMT MAXEXTENTS for 8K Blocksize

From: Madhavan Amruthur <mad012000_at_hotmail.com>
Date: Thu, 15 Feb 2001 10:04:08 -0800
Message-ID: <F001.002B578E.20010215093603@fatcity.com> 






Hi,


Below is a mail that I had posted regarding LMTs and how many extents they 
can track. As you can see from the mail, there is a maximum as to the number 
of extents tracked and for 8K blocksize its 380928. I am not sure what 
happens if you add more space after this.



Any thoughts will be appreciated.



Thanks and Regards,


Madhavan






Hi all,


Sorry for the long mail.


I had a question regarding the extent tracking that is done by the bitmap 
structure for a locally managed tablespace.
For a 4K block size database,


the 1st block of the datafile is the datafile header.
the 2nd block is the Bitmapped File Space Header
3rd to 16th block (14 blocks) is the bitmap structure itself.
Total of 16 blocks for a 64K size required for a LMT
I dumped the 3rd block and


Start dump data blocks tsn: 3 file#: 4 minblk 3 maxblk 3
buffer tsn: 3 rdba: 0x01000003 (4/3)


scn: 0x0000.000098d7 seq: 0x01 flg: 0x00 tail: 0x98d71e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:


BitMap Control:


RelFno: 4, BeginBlock: 17, Flag: 0, First: 0, Free: 30720

0000000000000000 0000000000000000 0000000000000000 0000000000000000


.....


The number of extents tracked by this block is 30720. I have pasted only the 
1st line of the bitmap and each bit tracks 4 extents and there are 64 bits 
in one line and so 256 extents. There are 120 lines with 64 bits each in 
this block that does the tracking. Data is stored from block 17 onwards 
indicated by Beginblock 17


Total extents that can be tracked in this datafile is 430080 (30720*14)
For a 8K block size database,


the 1st 2 blocks are same as above and


3rd - 8th block (6 blocks) has the bitmap structure
Total 8 blocks (64K bitmap)


Start dump data blocks tsn: 4 file#: 5 minblk 3 maxblk 3
buffer tsn: 4 rdba: 0x01400003 (5/3)


scn: 0x0000.0000a145 seq: 0x01 flg: 0x00 tail: 0xa1451e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:


BitMap Control:


RelFno: 5, BeginBlock: 9, Flag: 0, First: 0, Free: 63488

0000000000000000 0000000000000000 0000000000000000 0000000000000000


Here the extents are 63488 that are tracked. Data is stored from block 9 
onwards indicated by Beginblock 9


Total extents that can be tracked here is 380928 (63488*8)
For a 16K block size,


The 1st two blocks are the same


3rd and 4th blocks (2 blocks)for bitmap structure
Total 4 blocks for a 64K bitmap structure
Start dump data blocks tsn: 77 file#: 173 minblk 3 maxblk 3
buffer tsn: 77 rdba: 0x2b400003 (173/3)


scn: 0x03ac.cb84e875 seq: 0x01 flg: 0x00 tail: 0xe8751e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:


BitMap Control:


RelFno: 173, BeginBlock: 5, Flag: 0, First: 0, Free: 129024

0000000000000000 0000000000000000 0000000000000000 0000000000000000


Here the number of extents that are tracked is 129024. Data is stored from 
block 5 onwards indicated by Beginblock 5.
Total extents that can be tracked for this data file is 258048 (129024*2)
Question 1


The only similarity between the numbers 30720, 63488 and 129024 is if you 
subtract these numbers from the next higher power of 2 then the difference 
is 2048 for these numbers.


For eg: 32768 - 30720 = 2048


65536 - 63488 = 2048


131072 - 129024 = 2048


Is there a reason for these number of extents(30720 for 4K, 63488 for 8K and 
129024 for 16K) that are being tracked? What is the reasoning behind these 
numbers that are tracked?


Question 2


Though it is probably not possible practically, if we have a 4K block size 
and if I create a datafile that is 13.125G and create a table with initial 
extents of 430080 it should get created OK. What happens if I add space to 
the datafile and allocate one more extent 430081 and how is that extent 
tracked?


Thanks for your help in advance.


Sorry once again for the long mail.



Regards,


Madhavan



>From: paquette stephane Reply-To: ORACLE-L_at_fatcity.com To: Multiple 

>recipients of list ORACLE-L Subject: Re: LMT MAXEXTENTS for 8K Blocksize 

>Date: Thu, 15 Feb 2001 08:06:06 -0800

>

>Mark,

>

>Appology accepted ;-)

>

>Some people flame me because they did not read my post carefully. I said 

>that for optimal performance the number of extents with an 8K blocksize was 

>505. People thought that was a maximum.

>

>2147483645 is a theoritical limit. It's like the maximum Oracle database 

>size : 512 Petabytes .

>

>If everything was simple , they're would be less job for the dba.

>

>By putting a so large limit, you are sure that the system won't stopped. 

>I'd rather have 2001 extents and not having the users complaining that the 

>system stopped.

>

>

>

>--- Mark Leith a écrit : > Hi List, > > I have been reading through the 8i 

>Tuning & > Performance Guide, and > remembered a topic on the list a few 

>weeks back > discussing the maximum > number of extents for a LMT. > > In 

>the Tuning I/O section of this guide there is a > reference to this > 

>stating: > > ---snip--- > Evaluating Unlimited Extents > > Even though an 

>object may have unlimited extents, > this does not mean that > having > a 

>large number of small extents is acceptable. For > optimal performance you 

> > may > decide to reduce the number of extents. > > Extent maps list all 

>extents for a particular > segment. The number of extents > entries > per 

>Oracle block depends on operating system block > size and platform. > 

>Although > an extent is a data structure inside Oracle, the > size of this 

>data structure > depends on > the platform. Accordingly, this affects the 

>number > of extents Oracle can > store in a > single operating system 

>block. Typically, this value > is as follows: > > Block Size (KB) Maximum 

>Number of Extents > 2 121 > 4 255 > 8 504 > 16 1032 > 32 2070 > > > For 

>optimal performance, you should be able to read > the extent map with a > 

>single > I/O. Performance degrades if multiple I/Os are > necessary for a 

>full table > scan to get > the extent map. > > Avoid dynamic extension in 

>dictionary-mapped > tablespaces. For > dictionary-mapped tablespaces, do 

>not let the number > of extents exceed > 1,000. If extent allocation is 

>local, then do not > have more than 2,000 > extents. Having too many 

>extents reduces performance > when dropping or > truncating tables. > 

>---snip--- > > Now, I also remember somebody stating that > MAXEXTENTS 

>should be set to 504 > for an 8K block size, and he was flamed.. I also > 

>added to this, and now > appologise.. > > Now, the question arises : > > I 

>created an LMT with the default storage > parameters, and Oracle in its > 

>infinite wisdom, gave a MAXEXTENTS value of > 2147483645!! > > A few more 

>than 2000, was Larry thinking of his bank > balance when he decided > on 

>this? :) > > You know, it's so great that they put these little > "pearls" 

>of wisdom in to > tuning guides, that you have to dig in to, just to > find 

>a ghost of an > answer, but when it comes to putting them as > standard in 

>to the bloody > product they give stupid values that would grind a > system 

>to a halt if ever > allowed to happen!! Would it not then make sense to > 

>just set this as a > default?? > > Am I being naive? LOL.. > > Can we all 

>join hands and shout "Thank you > LARRY!!!!" > > Mark > > > BEGIN:VCARD > 

>VERSION:2.1 > N:Leith;Mark > FN:Mark Leith > ORG:Cool Tools UK Ltd;Sales > 

>TITLE:Sales & Marketing > TEL;WORK;VOICE:01905 330 281 > 

>TEL;CELL;VOICE:07771 691 122 > TEL;WORK;FAX:0870 127 5283 > 


>ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;9 Turbary > Ave,=0D=0ALyppard 

>Kettleby;Worcester;;WR40PS;England > LABEL;WORK;ENCODING=QUOTED-PRINTABLE:9 

>Turbary > Ave,=0D=0ALyppard Kettleby=0D=0AWorcester > WR40PS=0D=0AEngland > 

>URL: > URL:http://www.cool-tools.co.uk > 

>EMAIL;PREF;INTERNET:mark_at_cool-tools.co.uk > REV:20010118T150512Z > 

>END:VCARD >


>

>

>===== Stephane Paquette DBA Oracle stephane_paquette_at_yahoo.com

>

>__________________________________________________ Do You Yahoo!? Get 

>personalized email addresses from Yahoo! Mail - only $35 a year! 

>http://personal.mail.yahoo.com/ --

>Please see the official ORACLE-L FAQ: http://www.orafaq.com --

>Author: =?iso-8859-1?q?paquette=20stephane?= INET: 

>stephane_paquette_at_yahoo.com

>

>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, 

>California -- Public Internet access / Mailing Lists 

>-------------------------------------------------------------------- To 

>REMOVE yourself from this mailing list, send an E-Mail message to: 

>ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message 

>BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing 

>list you want to be removed from). You may also send the HELP command for 

>other information (like subscribing).





Get your FREE download of MSN Explorer at http://explorer.msn.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Madhavan Amruthur
  INET: mad012000_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Thu Feb 15 2001 - 12:04:08 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US