Locally Managed Tablespaces

Locally Managed Tablespaces Unless you have been living on another planet (one that does not run Oracle!) you will probably have heard of Locally Managed Tablespaces. For those of you that are not familiar with the term, here is a quick recap on the way tablespaces have evolved in Oracle. Tablespaces are the mechanism by which segments (tables, indexes, etc.) consume space in the database, and they are the logical representation of one or more datafiles on disk. Obviously as segments are created, dropped or grow in the database, we must track what parts of the tablespace (or more accurately the underlying datafiles) are in use and which parts are free. For example, a tablespace which starts off empty and then has five segments created can be represented by the following diagram: Table:EMP Table:ORDERS, extent 1 Table:DEPT Table:ORDERS, extent 2 Table:CUSTOMERS Never used space When we drop one of the segments (say the ORDERS table in this example), the tablespace can now be represented as: Table:EMP Free space chunk Table:DEPT Free space chunk Table:CUSTOMERS Never used space Clearly Oracle needs some mechanism of tracking the allocation and de-allocation of space within each file of each tablespace, so that free space (either never used or previously used) can be (re)used as required. For each chunk of space in a file within a tablespace we need to track: 1. where it starts in the file(s) for the tablespace; 2. the size of the chunk; and, 3. if it is in use, we need to know what segment is occupying that space Until Oracle 8.1 all tablespaces were known as "dictionary managed" tablespaces. Since Oracle is a relational database, it makes sense to store the information above in a database table. Hence, Oracle stored the three required elements of information above in 2 tables: UET$ (Used Extent Table) and FET$ (Free Extent Table). By logging on to SQL Plus as SYS, we can see that the structure of the tables reflects the tracking information. SQL> desc UET$ Name Null? Type ----------------- ----------- ----------- SEGFILE# NOT NULL NUMBER SEGBLOCK# NOT NULL NUMBER | The segment that uses this space EXT# NOT NULL NUMBER TS# NOT NULL NUMBER | The tablespace ID and the file FILE# NOT NULL NUMBER | ID for that tablespace BLOCK# NOT NULL NUMBER LENGTH NOT NULL NUMBER | The location and size of the chunk SQL> desc FET$ Name Null? Type ----------------- ----------- ----------- TS# NOT NULL NUMBER | The tablespace ID and the file FILE# NOT NULL NUMBER | ID for that tablespace BLOCK# NOT NULL NUMBER LENGTH NOT NULL NUMBER | The location and size of the chunk We have a row for each chunk of space (used or free) in each tablespace and file. This approach works fine as long as there is not a lot of concurrent space management required in a database. For example, if a segment is dropped, then we need to remove the rows from the UET$ table (since they are no longer in use) and add new entries to the FET$ (since we now have some more free space). Consider what would happen if two people were trying to manipulate these two critical tables at the same time. If this were allowed, we could easily introduce a data corruption, so Oracle serialises all operations on these two tables. If multiple Oracle sessions need to manipulate the UET$/FET$ concurrently, then all except one will be forced to queue for it. This is observed as a ST (space transaction) lock and clearly it can cause significant problems with concurrency in environments where a lot of space allocation requests are made. For those sites using the standard (contents=permanent) tablespaces for sorting data, this can cripple performance. To solve this problem Oracle introduced the locally managed tablespace in 8.1. Rather than store the space usage information in two tables, Oracle have implemented a mechanism to store this information in the tablespaces themselves. By not relying on the two global tables, space allocation operations on one tablespace have no bearing on another. Let’s have a look under the covers at how Oracle does this. We create a simple locally managed tablespace, and then create a table within it: SQL> create tablespace demo 2 datafile '/ora01/oem/oemdemo01.dbf' size 10m 3 extent management local uniform size 64k; Tablespace created. The first thing we notice is that we are telling Oracle that any object in this tablespace will allocate space in 64k pieces. So what happens if we specify a much larger size when we try to create segments in this tablespace? SQL> create table demotab ( x number ) tablespace demo 2 storage ( initial 1m ); Table created. Oracle simply assumes that you want to allocate enough 64k chunks to satisfy 1M, which would mean 16extents (each of 64k). We can verify this by looking at USER_EXTENTS: SQL> select count(*) from user_extents where segment_name = 'DEMOTAB'; COUNT(*) ---------- 16 Before we proceed, first we see what file number the database has assigned to our new file: SQL> col name format a30 trunc SQL> select file#, name from v$datafile; File# NAME ----- -------------------- 1 /oras1/oem/oemsystem01.dbf 2 /oras3/oem/oemundo01.dbf 3 /ora01/oem/oemoem_repository01 4 /ora01/oem/oemrcat01.dbf 5 /ora01/oem/oemdemo01.dbf For conventional tablespace this would be managed via entries in UET$ and FET$: SQL> select count(*) 2 from uet$ 3 where file# = 5; COUNT(*) ---------- 0 SQL> select count(*) 2 from fet$ 3 where file# = 5; COUNT(*) ---------- 0 We can see that Oracle has not put any entries in these tables anymore. So where IS the information being stored? Let’s look at what's going on in detail. We will dump the third block of the file. The first block of a file is called the file header, the second block is the file space bitmap header, but the information we are interested in is block 3, which is the first bitmap block: SQL> alter system dump datafile 5 block 3; System altered. We then take a look at the trace file that is created: Start dump data blocks tsn: 5 file#: 5 minblk 3 maxblk 3 buffer tsn: 5 rdba: 0x01400003 (5/3) scn: 0x0000.202f7a6f seq: 0x01 flg: 0x00 tail: 0x7a6f1e01 frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 5, BeginBlock: 9, Flag: 0, First: 16, Free: 63472 FFFF000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 ..... What is this telling us? Looking at the bold section, FFFF00, we see a hexadecimal representation that we will convert to binary. In hexidecimal, F = decimal 15 which equals 1111 in binary. Once converted to binary, FFFF00 becomes: 1111,1111,1111,1111,0000,0000 Notice that there are 16 1's. Each of these 1's represents a 64k chunk of space (i.e. an extent) in our table, and this is how Oracle is mapping the space. Our table, which is 16 extents, is being represented with a bitmap in the tablespace file itself. All of the subsequent 0's indicate space that is free for use. What happens if we add three more extents to the table? SQL> alter table demotab allocate extent; Table altered. SQL> alter table demotab allocate extent; Table altered. SQL> alter table demotab allocate extent; Table altered. Now we have 19 extents - what does our bitmap look like now? Start dump data blocks tsn: 5 file#: 5 minblk 3 maxblk 3 buffer tsn: 5 rdba: 0x01400003 (5/3) scn: 0x0000.202f7c64 seq: 0x01 flg: 0x00 tail: 0x7c641e01 frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap File Space Bitmap Block: BitMap Control: RelFno: 5, BeginBlock: 9, Flag: 0, First: 19, Free: 63469 FFFF070000000000 0000000000000000 0000000000000000 0000000000000000 We have FFFF as before, but now we have 07. How do we decipher that? First we convert it to binary which is 0000,0111. But one thing that was not apparent from the first example we presented is that Oracle byte-swaps the information. So 0000,0111 becomes: 1110,000. We did not notice this in the first case, because F (=1111) does not change when we byte-swap. FFFF07 converted to binary is 1111,1111,1111,1111,0000,0111. We then swap each 8 bits, which gives: 1111,1111,1111,1111,1110,0000 which maps exactly to our 19 extents. This bitmap occupies 8 blocks, namely, a 64K chunk of space in the file, which should be taken into consideration when creating tablespaces. Because these are bitwise operations Oracle can process them extremely quickly, and we have conducted tests that show that even tables (in locally managed tablespaces) with thousands of extents can be created and dropped in just a few seconds. Compare this to the UET$ and FET$ where manipulating large numbers of extents could take minutes, hours or even days! You may be asking yourself, “if a single bit represents an extent, then surely all extents in the tablespace are then forced to be the same size?”. This is true - as in the example above, Oracle allows you to specify any size you like, but it will always be reduced to a number of bits in the bitmap, where each bit represent a space chunk. In any event, it is best practice for all extents in a tablespace to be the same size since this eliminates space lost due to fragmentation. In 8.1.x of Oracle, there are some caveats with having your rollback segments in a locally managed tablespace (you should ensure that you have at least 1 rollback segment in a standard dictionary tablespace). There are virtually no negatives to using locally managed tablespaces, and in fact they are the default in Oracle 9. Similarly, in Oracle 9i.2, the SYSTEM tablespace defaults to a locally managed tablespace, after which it is no longer possible to create a dictionary-managed tablespace. Many sites do not want to migrate to locally managed tablespaces because they are under the impression that they will need to unload and reload all of their database, but this is not the case. Version 8.1.6 and above gives two built-in functions to allow you to switch back and forth between dictionary and locally managed tablespaces without unloading/reloading the data. It was 8.1.5 that introduced the dbms_space_admin package which is used 'behind the scenes' to probe the tablespace bitmaps in locally managed tablespace, mainly for use in the everyday views DBA_EXTENTS and DBA_SEGMENTS. In 8.1.6 two new procedures were added to the package: DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL There are a few important things to note if you wish to convert to locally managed tablespaces. Firstly, it is not sufficient to have compatible set to 8.1, it must be 8.1.6 or higher. When converting from dictionary management to locally management, there must be some free space in the tablespace holding the bitmap that will be created (at least 64k), otherwise migration will fail. When converting from dictionary management to locally management, a "full conversion" does not actually take place - simply an appropriate bitmap is built to map the existing extents, along with some rounding of the space used. Thus you end up with an interesting hybrid, where if you query DBA_TABLESPACES, you will see that management policy is LOCAL but the allocation policy is USER (not uniform or automatic). In short, you will have definitely solved one problem, that of stress on UET$ and FET$, but you are not guaranteed to resolve the fragmentation issue. Obviously a pre-existing dictionary managed tablespace could have a proliferation of many different extent sizes, so how does Oracle convert this to a management system where each bit corresponds to a common extent size? To do this, Oracle scans each file for the tablespace, and tries to come with up with an extent size that every existing extent will divide evenly into. In fact, Oracle must find a multiple of the block size that divides evenly into: all of the existing extents; all of the extents that have been used in the past that are now free; and, the MINIMUM EXTENT clause, if this has been specified at the tablespace level. During conversion, it will try to find the largest suitable extent size, that is, the greatest common divisor of the three categories above. In the worst case, this would be reduced to a single block (for example, if there were two tables, one of 7 blocks and one of 8 blocks in the tablespace). There is another very critical factor that is not documented. If a MINIMUM EXTENT clause has NOT been specified, then rather than this clause being ignored, Oracle assumes it to be the smallest possible size of an extent (whether it exists or not). In an 8k block size database, this is 40k (Oracle rounds extents bar the initial one up to 5 blocks), so no matter what distribution of extents you have, the bitmap will always be chosen as 1bit per 40k, unless you specify the MINIMUM EXTENT clause. Consider the following example. We create a dictionary managed tablespace that we will attempt to convert to a locally managed one: SQL> create tablespace blah 2 datafile 'G:ORA9IORADATADB9BLAH.DBF' size 10m reuse 3 extent management dictionary; Tablespace altered. SQL> col bytes format 999,999,999 SQL> select * from dba_free_space where tablespace_name = 'BLAH'; TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCK RELATIVE_FNO --------------- -------- ----------- ------------ ------- ---------------- BLAH 8 2 10,477,568 1279 8 Then we create three tables that have a common extent multiple of 400k: SQL> create table t1 ( x number ) storage ( initial 400k) tablespace blah; Table created. SQL> create table t2 ( x number ) storage ( initial 800k) tablespace blah; Table created. SQL> create table t3 ( x number ) storage ( initial 1200k) tablespace blah; Table created. SQL> select * from dba_free_space where tablespace_name = 'BLAH'; TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCK RELATIVE_FNO --------------- -------- ----------- ----------- ------- ---------------- BLAH 8 302 8,019,968 979 8 SQL> select bytes from dba_extents where tablespace_name = 'BLAH'; BYTES ---------- 409,600 819,200 1,228,800 At this stage, we have three chunks of used space, and since all of the extents are multiples of 400k (that is, 50 blocks) we should be able to convert to a locally managed tablespace with a extent size of 50 blocks, but if we try... SQL> exec dbms_space_admin.TABLESPACE_MIGRATE_TO_LOCAL('BLAH',50); BEGIN dbms_space_admin.TABLESPACE_MIGRATE_TO_LOCAL('BLAH',50); END; * ERROR at line 1: ORA-03241: Invalid unit size ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0 ORA-06512: at line 1 If we set the minimum extent clause on the tablespace to 400K as well: SQL> alter tablespace blah minimum extent 400k; Tablespace altered. SQL> exec dbms_space_admin.TABLESPACE_MIGRATE_TO_LOCAL('BLAH',50); PL/SQL procedure successfully completed. Conversion goes through with no problems. As noted, converted locally managed tablespaces are not as effective at managing fragmentation as a fully uniform extent sized locally managed tablespace. It is for this reason that I recommend that anyone wishing to undertake a conversion should make the additional effort to convert uniform extent sizing (via appropriate use of 'alter table move' and 'alter index rebuild'), and not just use DBMS_SPACE_ADMIN

With an auto-allocate tablespace, the extent sizes consist of the units 64k, 1m, 8m, 64m and possibly 256m. The greatest common divisor of these values is 64k, so each bit in the bitmap will represent a 64k chunk of an extent. For example,

So we have 50 extents of 1M, but the bitmap consists of 800 bits set, that is, 600 x 64K chunks. (Thanks to Richard Foote for this example).

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/412/viewspace-900277/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/412/viewspace-900277/