Oracle 段空间管理方式与PCTFREE和PCTUSED的概念

ALLSTARS_ORACLE

Oracle 段空间管理方式与PCTFREE和PCTUSED的概念

SQL> create table test11 (ID number(10),score number(10,1),name varchar2(20),pc number(10)) tablespace test;
SQL> set timing on
SQL> set time on
SQL> DECLARE i integer;
begin
i:=1;
while i<=1000000 loop
insert into test11(id) values (i);
i:=i+1;
end loop;
commit;
end;
SQL> set timing on
SQL> set time on
SQL> set autotrace traceonly;
SQL> alter session set events '10046 trace name context forever ,level 12';
SQL> alter session set events '10053 trace name context forever ,level 1';
SQL> explain plan for update test11 set score=99 ;
SQL> select * from table (dbms_xplan.display);
SQL> update test11 set score=99;

1000000 rows updated.

Elapsed: 01:00:47.75  -   update 时长一个小时

Execution Plan
----------------------------------------------------------
Plan hash value: 1672778901

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |        |  1000K|  3909K|   705   (3)| 00:00:09 |
|   1 |  UPDATE            | TEST11 |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST11 |  1000K|  3909K|   705   (3)| 00:00:09 |
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
        714  recursive calls
  494090309  db block gets
    1318480  consistent gets
       3329  physical reads
  467634216  redo size
        824  bytes sent via SQL*Net to client
        719  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
1000000  rows processed
测试2：
create table test11 (ID number(10),score number(10,1),name varchar2(20),pc number(10)) tablespace users pctfree 50;

conn user/password
@?/rdbms/admin/utlchain.sql
analyze table test11 list chained rows into chained_rows;
select count(*) from chained_rows where table_name='TEST11';

To avoid it, create table test11 with a high value set for pctfree，Repeat the test once again and you will see the update operation completing faster。

2.        段空间管理方式

段管理主要有两种方式：
自动管理方式AUTO,采用位图管理段的存储空间,使用位图来管理段中已用数据块和空闲数据块.
手工管理方式MANUAL, 采用FREELIST管理段的存储空间,使用FREELIST来管理段中间的空闲数据块.

数据块的管理方法分为：
1、自动管理方式如创建表空间时设置为本地管理方式，并且将段的存储空间方式设置为AUTO，该表空间的所有块均采用自动管理方式。系统默认值，

2、手工管理方式是传统的管理方式，主要通过PCTFREE和PCTUSED两个存储参数控制可用存储区的大小，避免行迁移现象的发生。这两个参数可在创建表空间时设置，
   也可在数据库的模式对象（表，索引）中设置。模式对象中设置的优先级比表空间的要高。如表和索引中没有设置，则按表空间的设置，如表空间也没设置，
   则按自动管理方式管理块。
      
每个表空间中，可以为创建的对象指定缺省的存储参数。创建对象时指定的存储参数将覆盖缺省值。如果在创建对象时没有指定存储参数，那么系统将使用缺省值。
系统表空间使用ASSM，ASSM使用位图而不是传统的FreeList来管理段内的free db block，大大提升了空间管理的性能,
同时显著的减少segment header类型的buffer busy wait等待事件,减少热快和碎片发生。



3.        PCTFREE和PCTUSED的概念：

PCTFREE存储参数告诉ORACLE什么时候应该将数据块从对象的空闲列表中移出。ORACLE的默认参数是 PCTFREE=10；
也就是说，一旦一个INSERT操作使得数据块的90%被使用，这个数据块就从空闲列表(free list)中移出。
PCTUSED存储参数告诉ORACLE什么时候将以前满的数据块加到空闲列表中。当记录从数据表中删除时，数据库的数据块就有空间接受新的记录，
但只有当填充的空间降到PCTUSED值以下时，该数据块才被连接到空闲列表中，才可以往其中插入数据。PCTUSED的默认值是PCTUSED=40。
（1）PCTUSED较高意味着相对较满的数据块会被放置到空闲列表中，从而有效的重复使用数据块的空间，但会导致I/O消耗。
     PCTUSED低意味着在一个数据块快空的时候才被放置到空闲列表中，数据块一次能接受很多的记录，因此可以减少I/O消耗，提高性能。
（2）PCTFREE的值较大意味着数据块没有被利用多少就从空闲列表中断开连接，不利于数据块的充分使用。
     PCTFREE过小的结果是，在更新时可能会出现数据记录迁移(Migration)的情况。
     (注：数据记录迁移(Migration)是指记录在是UPDATE操作扩展了一个列后,
      PCTFREE参数所指定的空间不够扩展，从而记录被ORACLE强制迁移到新的数据块，发生这种情况将较严重的影响ORACLE的性能，出现更新缓慢)。
     
PCTFREE的使用
　　在Oracle中表的每一行数据由唯一的ROWID标记；而Oracle支持的数据类型中有一些长度是可变的，如VARCHAR，当对这些数据进行UPDATE时，
    如果块中的可用空间不能容纳UPDATE后的数据行时，Oracle将会把此行移到其它数据块，同时保留此数据行的 ROWID不变，
    并在原有块中建一指针指向行迁移后的位置。
    在这种情况下读取一行数据将需要访问2个数据块，从而导致性能下降。PCTFREE保留的空间 就是为确保更改后的数据行可以仍存放于原有数据块中,
    避免行迁移的情况发生。

PCTUSED的使用
     当块的使用的空间下降到PCTUSED后，此块被重新放回空闲链表（Freelist） 中，作为后续Insert的候选块。同样，
     设置PCTUSED需要视数据行的特性和Insert、Update、Delete的模式而定，但必须遵守的原 则是：db_block_size * (100 - PCTFREE - PCTUSED)必须比行的长度大。
     对于数据行长度变化较大的情况，应使用最大行长度来计算PCTUSED，并且应使用较低的PCTUSED值。
     因为在执行Insert时，如果数据块的可用空间不能装下一行数据，当块的使用的空间是在PCTUSED之上，Oracle将把此块从Freelist中移走；
     当块的使用的空间是在PCTUSED之下，Oracle将会扩展段空间。因此，PCTUSED如果设得过高，将导致段的不断扩展。 当数据行长度不大时，
     使用缺省的PCTUSED（40）是比较合适的；对于行长度较大的情况，最长的行有可能会占用半个以上的块空间，此时可设置 PCTUSED为10。
     较小的PCTUSED仅在表中的数据以随机方式被删除，而且仍有一些行长时间保留在块中时，才会造成空间使用上的问题，
     因为这些块 可能需要较长的时间才能或永远不能重新被用于存放新数据。在这种应用中，如果空间利用率一直处于较低水平，
     则需对PCTUSED进行分析和调整。

4.         建议：
   
由此，在建表时，如果PCTFREE设置不足时可能产生行迁移；而另一方面如果PCTFREE设置过高，将会造成空间浪费。
    因此正确设置PCTFREE需要对表中数据的使用进行分析。对于数据长度不会变化或极少更新的情况，可以采用较小的PCTFREE;
    对于其它大多数情况应采用稍大的 PCTFREE(PCTFREE的缺省值是10，如果不好估计需预留的空间，可以使用15－25的范围),
    不要为节约块中的空间而使用较小的 PCTFREE值。