ITPub博客

首页 > 数据库 > Oracle > Oracle 表空间管理(tablespace managment)

Oracle 表空间管理(tablespace managment)

Oracle 作者:zr2095 时间:2015-10-13 21:55:51 0 删除 编辑
一. 官网说明

Logical Storage Structures 

其中有关ASSM 的内容如下:

Logical Space Management

Oracle Database must use logical space management to track and allocate the extents in a tablespace. When a database object requires an extent, the database must have a method of finding and providing it. Similarly, when an object no longer requires an extent, the database must have a method of making the free extent available.

Oracle Database manages space within a tablespace based on the type that you create. You can create either of the following types of tablespaces:

·         Locally managed tablespaces (default)

The database uses bitmaps in the tablespaces themselves to manage extents. Thus, locally managed tablespaces have a part of the tablespace set aside for a bitmap. Within a tablespace, the database can manage segments with automatic segment space management (ASSM) or manual segment space management (MSSM).

·         Dictionary-managed tablespaces

The database uses the data dictionary to manage extents (see "Overview of the Data Dictionary").

Figure 12-3 shows the alternatives for logical space management in a tablespace.

Figure 12-3 Logical Space Management


Description of "Figure 12-3 Logical Space Management"

Locally Managed Tablespaces

A locally managed tablespace maintains a bitmap in the data file header to track free and used space in the data file body. Each bit corresponds to a group of blocks. When space is allocated or freed, Oracle Database changes the bitmap values to reflect the new status of the blocks.

The following graphic is a conceptual representation of bitmap-managed storage. A 1 in the header refers to used space, whereas a 0 refers to free space.

A locally managed tablespace has the following advantages:

·         Avoids using the data dictionary to manage extents

Recursive operations can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a data dictionary table or undo segment.

·         Tracks adjacent free space automatically

In this way, the database eliminates the need to coalesce free extents.

·         Determines the size of locally managed extents automatically

Alternatively, all extents can have the same size in a locally managed tablespace and override object storage options.

Note:

Oracle strongly recommends the use of locally managed tablespaces with Automatic Segment Space Management.

Segment space management is an attribute inherited from the tablespace that contains the segment. Within a locally managed tablespace, the database can manage segments automatically or manually. For example, segments in tablespace users can be managed automatically while segments in tablespace tools are managed manually.

Automatic Segment Space Management

The ASSM method uses bitmaps to manage space. Bitmaps provide the following advantages:

·         Simplified administration

ASSM avoids the need to manually determine correct settings for many storage parameters. Only one crucial SQL parameter controls space allocation: PCTFREE. This parameter specifies the percentage of space to be reserved in a block for future updates (see "Percentage of Free Space in Data Blocks").

ASSM 管理,只需要一个参数:PCTFREE

·         Increased concurrency

Multiple transactions can search separate lists of free data blocks, thereby reducing contention and waits. For many standard workloads, application performance with ASSM is better than the performance of a well-tuned application that uses MSSM.

·         Dynamic affinity of space to instances in an Oracle Real Application Clusters (Oracle RAC) environment

ASSM is more efficient and is the default for permanent, locally managed tablespaces.

Note:

This chapter assumes the use of ASSM in all of its discussions of logical storage space.

 

Manual Segment Space Management

The legacy MSSM method uses a linked list called a free list to manage free space in the segment. For a database object that has free space, a free list keeps track of blocks under the high water mark (HWM), which is the dividing line between segment space that is used and not yet used. As blocks are used, the database puts blocks on or removes blocks from the free list as needed.

In addition to PCTFREE, MSSM requires you to control space allocation with SQL parameters such as PCTUSED, FREELISTS, and FREELIST GROUPS.PCTUSED sets the percentage of free space that must exist in a currently used block for the database to put it on the free list. For example, if you setPCTUSED to 40 in a CREATE TABLE statement, then you cannot insert rows into a block in the segment until less than 40% of the block space is used.

As an illustration, suppose you insert a row into a table. The database checks a free list of the table for the first available block. If the row cannot fit in the block, and if the used space in the block is greater than or equal to PCTUSED, then the database takes the block off the list and searches for another block. If you delete rows from the block, then the database checks whether used space in the block is now less than PCTUSED. If so, then the database places the block at the beginning of the free list.

An object may have multiple free lists. In this way, multiple sessions performing DML on a table can use different lists, which can reduce contention. Each database session uses only one free list for the duration of its session.

As shown in Figure 12-4, you can also create an object with one or more free list groups, which are collections of free lists. Each group has a master free list that manages the individual process free lists in the group. Space overhead for free lists, especially for free list groups, can be significant.

Figure 12-4 Free List Groups


Description of "Figure 12-4 Free List Groups"

Managing segment space manually can be complex. You must adjust PCTFREE and PCTUSED to reduce row migration (see "Chained and Migrated Rows") and avoid wasting space. For example, if every used block in a segment is half full, and if PCTUSED is 40, then the database does not permit inserts into any of these blocks. Because of the difficulty of fine-tuning space allocation parameters, Oracle strongly recommends ASSM. In ASSM, PCTFREE determines whether a new row can be inserted into a block, but it does not use free lists and ignores PCTUSED.

-- ASSM 会忽略PCTUSED 参数

Dictionary-Managed Tablespaces

A dictionary-managed tablespace uses the data dictionary to manage its extents. Oracle Database updates tables in the data dictionary whenever an extent is allocated or freed for reuse. For example, when a table needs an extent, the database queries the data dictionary tables, and searches for free extents. If the database finds space, then it modifies one data dictionary table and inserts a row into another. In this way, the database manages space by modifying and moving data.

The SQL that the database executes in the background to obtain space for database objects is recursive SQL. Frequent use of recursive SQL can have a negative impact on performance because updates to the data dictionary must be serialized. Locally managed tablespaces, which are the default, avoid this performance problem.


.  ASSM 说明

    在Orale 9i以前,表的剩余空间的管理与分配都是由链接列表freelist来完成的,因为freelist存在串行的问题因此容易引起往往容易引起段头的争用与空间的浪费(其实这一点并不明显),最主要的还是因为需要DBA 花费大量的精力去管理这些争用并监控表的空间利用。  
    自动段空间管理(ASSM),它首次出现在Oracle 920里。有了ASSM,链接列表freelist位图所取代,它是一个二进制的数组,能够迅速有效地管理存储扩展和剩余区块(free block),因此能够改善分段存储本质,ASSM表空间上创建的段还有另外一个称呼叫Bitmap Managed SegmentsBMB 段)。  


让我们看看位图freelist是如何实现的。从使用区段空间管理自动参数创建tablespace开始:  
create tablespace demo  
datafile '/ora01/oem/demo01.dbf '  
size 5m  
EXTENT MANAGEMENT LOCAL -- Turn on LMT  
SEGMENT SPACE MANAGEMENT AUTO -- Turn on ASSM;  

    一旦你定义好了tablespace,那么表和索引就能够使用各种方法很容易地被移动到新的tablespace里,带有ASSM的本地管理tablespace会略掉任何为PCTUSEDNEXTFREELISTS所指定的值  

    当表格或者索引被分配到这个tablespace以后,用于独立对象的PCTUSED的值会被忽略,而Oracle9i会使用位图数组来自动地管理tablespace里表格和索引的freelist。对于在LMTtablespace内部创建的表格和索引而言,这个NEXT扩展子句是过时的,因为由本地管理的tablespace会管理它们。但是,INITIAL参数仍然是需要的,因为Oracle不可能提前知道初始表格加载的大小。对于ASSM而言,INITIAL最小的值是三个块  


    新的管理机制用位图来跟踪或管理每个分配到对象的块,每个块有多少剩余空间根据位图的状态来确定,如>75%,50%-75%,25%-50%<25%,也就是说位图其实采用了四个状态位来代替以前的pctused,什么时候该利用该数据块则由设定的pctfree来确定。  


    使用ASSM的一个巨大优势是,位图freelist肯定能够减轻缓冲区忙等待buffer busy wait)的负担,这个问题在Oracle9i以前的版本里曾是一个严重的问题  


    在没有多个freelist的时候,每个Oracle表格和索引在表格的头部都曾有一个数据块,用来管理对象所使用的剩余区块,并为任何SQL插入声明所创建的新数据行提供数据块。当数据缓冲内的数据块由于被另一个DML事务处理锁定而无法使用的时候,缓冲区忙等待就会发生。当你需要将多个任务插入到同一个表格里的时候,这些任务就被强制等待,而同时Oracle会在同时分派剩余的区块,一次一个。  


    有了ASSM之后,Oracle宣称显著地提高了DML并发操作的性能因为(同一个)位图的不同部分可以被同时使用,这样就消除了寻找剩余空间的串行化。根据Oracle的测试结果,使用位图freelist会消除所有分段头部(对资源)的争夺,还能获得超快的并发插入操作 


    尽管ASSM显示出了令人激动的特性并能够简化Oracle DBA的工作,但是Oracle9i的位图分段管理还是有一些局限性的:  
            1. 
一旦DBA被分配之后,它就无法控制tablespace内部的独立表格和索引的存储行为。
  
            2. 
大型对象不能够使用ASSM,而且必须为包含有LOB数据类型的表格创建分离的tablespace
  
            3. 
你不能够使用ASSM创建临时的tablespace。这是由排序时临时分段的短暂特性所决定的。
  
            4. 
只有本地管理的tablespace才能够使用位图分段管理。
  
            5· 
使用超高容量的DML(例如INSERTUPDATEDELETE等)的时候可能会出现性能上的问题。


三、表空间的更改

    有时候我们创建了我们想要的表空间,但是计划是赶不上变化的,比如你的数据数据越来越多,原来的表空间容纳不下了,怎么办?或者你想把你的表空间变为只读,或者你想让你的有空间自己扩展大小,变手工为自动,等等,这些我们都可以利用ORACLE 的ALTER TABLESPACE 来完成。
    最先看本地管理表空间(locally managed tablespace)

   关于表空间,不能更改的内容:
        1.       不能把永久表空间变为临时表空间;
        2.       不能改变表空间的segment管理方式。
   可以更改的内容:
        1.   增加数据文件,DATAFILE
        2.   改变表空间的可用性,online与offline
            a) ALTER TABLESPACE lmtemp TEMPFILE OFFLINE;
            b) ALTER TABLESPACE lmtemp TEMPFILE ONLINE;
        3.       改变表空间的属性,read/write only
        4.       更改数据文件的名字,启动或禁止数据文件的自动扩展功能。
 
    大表空间(bigfile tablespace)
        1. resize:ALTER TABLESPACE XXX RESIZE 80G
        2. autoextend:  ALTER TABLESPACE XXX QUTOEXEND ON NEXT 20G

    本地临时表空间
    1.增加临时文件
      ALTER TABLESPACE lmtemp
        ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE;
    2.改变可用性,不可以让一个临时表空间离线,但可以让临时文件离线
     ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
     ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;
    3.改变临时文件的大小
       ALTER DATABASE TEMPFILE  '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;
    4.干掉临时数据文件
       ALTER DATABASE TEMPFILE  '/u02/oracle/data/lmtemp02.dbf' DROP  INCLUDING DATAFILES;
    5.压缩临时表空间
      先说说操作
      ALTER TABLESPACE lmtemp1 SHRINK SPACE KEEP 20M;
      ALTER TABLESPACE lmtemp2 SHRINK TEMPFILE   '/u02/oracle/data/lmtemp02.dbf';
      KEEP 保持指定大小,如果没有KEEP语句,则尽可能的减小。还是指定一个好。      
      为什么要执行这个操作?因为数据库在执行大的排序操作时,会占用很大的临时表空间,它会让临时表空间增大的,然而它也不很完全负责任,当它执行完命令后,他不会把增加的那部分空间归还给操作系统,它只把它标记为FREE可以重用。这样就会造成磁盘空间乱费,很可能以后那部分FREE空间一直FREE。
 
上面有针对性的说了一些各种类型表空间的更改方法,下面说一些共性的东西吧:
   1.改名字用RENAME TO
      ALTER TABLESPACE user RENAME TO newuser;
   2.删除表空间
      DROP TABLESPACE users INCLUDING CONTENTS;
      DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

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

上一篇: oracle 11g手工建库
请登录后发表评论 登录
全部评论

注册时间:2015-04-15

  • 博文量
    163
  • 访问量
    421118