【Oracle原理】pctfree与pctused

PCTFREE 指数据块中保留的空间以便更新现有的行。所以对于update造成的行变长而言更大的PCTFREE是更亲和的,更少造成migrated rows的。这个参数是一个百分比,代表块大小的百分比。

 

在块中剩余空间达到PCTFREE指定的百分比之前,这些空闲空间可以被插入行数据和block header的增长、以及UPDATE造成的行变长等等操作所用。  换而言之当剩余空间等于或者小于PCTFREE时,这些空闲空间不会再为插入所用了,而只是给block header的增长、以及UPDATE造成的行变长等等操作所用。注意在ASSM下 当剩余空间等于或者小于PCTFREE时  数据块就被 bitmap block标记为 FULL了,而不是真的”FULL”了。

PCTUSED参数(ASSM Automatic Segment Space Management下被废弃,仅仅MSSM下具有效果) 决定数据块在达到PCTFREE而被标记为FULL后,当数据块中被使用的比例下降到比PCTUSED(其实也是百分比)更小时,这个数据块被认为又可用来插入新的行数据了,这是PCTUSED的作用。

 

由于当数据被插入到数据块中时,行数据时从块的底部往上存储的。 而块头block header所占的空间又是浮动的,这是由于interested transaction lists (ITLs)也是要占用空间的,而一个块中ITL的数量是变化的,这种空间使用时从块的头部往下扩展的。  设置一个较大的INITRANS初始ITL数据将为块头保留更多的空间,这是因为一开始就会创建空的ITL(对于已有的块要MOVE才生效),但空的ITL已经占了地方了,所以别人就占不走了, 但是这样也会造成每个块中可用的空间减少。  DBA需要自行在 并发需要和可用空间上权衡利弊。

From Askmaclean.com

需要注意 , 这2个参数在MSSM和ASSM 数据段管理方式下的不同。

在MSSM freelist-managed模式下,当PCTFREE指定的值被达到后,由于该块会从Freelist上被移除所以不会有新的数据被插入到该块中。 但余下来的所有空间均只能用来更新现有的行。 DELETE删除显然会释放块上的空间,当DELETE和UPDATE(指让行缩小的UPDATE)营造出更多的空闲空间,以致于被使用的空间已经小于PCTUSED了,那么此时这个数据块又会被放到FREELIST上,这之后用户又可以插入数据到该块中。

还需要注意,PCTUSED在ASSM段上是不生效的。

 

 

 

 

在表空间有足够free space的情况下出现ORA-1652

版本10.2.0.5之前存在这样的问题,当打开recyclebin回收站功能的情况下, Tablespace 上有足够的Free Space空闲空间,但是因为这些Free Space属于回收站中的对象,在并行INSERT数据 或者并行CTAS的情况下 PARALLEL启用的情况下可能遇到ORA-1652错误:

 

 

oracle@localhost:~$ oerr ora 1652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause:  Failed to allocate an extent of the required number of blocks for
//          a temporary segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//          files to the tablespace indicated.

例如ORA-01652: unable to extend temp segment by 320 in tablespace MAC_TS

 

这一般是由于BUG 6977045 – ORA-1652 LMT SPACE NOT REALLOCATED CORRECTLY AFTER DROP TABLE, 该BUG 确认在版本 11.2中修复。

 

该BUG的原理是当CTAS with nologging是使用直接路径加载direct path load,Oracle一开始在针对的永久表空间上创建一个临时段继以加载数据。一旦这些操作完成,则临时段会被重命名并成为表的一部分。 当在该永久表空间上drop一张表,当打开回收站的情况下 该表被置入回收站recyclebin中,该段之前分配的空间由于本BUG的原因造成CTAS + PARALLEL + NOLOGGING时不计算为free space。  这导致了ORA-1652错误的触发。

 

目前针对该BUG 6977045 的off patch有10.2.0.4 、11.1.0.7和11.1.0.7.9 的版本:

 

Patch 6977045 ORA-1652 LMT SPACE NOT REALLOCATED CORRECTLY AFTER DROP TABLE

 

 

如果不想打补丁, 那么Workaround 可以是:

 

1. 关闭 回收站功能 recyclebin=off

2. 在CTAS PARALLEL NOLOGGING 前 purge recyclebin清理回收站

了解你所不知道的SMON功能(十二):Shrink UNDO(rollback) SEGMENT

SMON对于Undo(Rollback)segment的日常管理还不止于OFFLINE UNDO SEGMENT ,在AUM(automatic undo management或称SMU)模式下SMON还定期地收缩Shrink Rollback/undo segment。

 

触发场景

 

这种AUM下rollback/undo segment的undo extents被shrink的现象可能被多种条件触发:

  • 当另一个回滚段的transaction table急需undo空间时
  • 当SMON定期执行undo/rollback管理时(每12个小时一次):
    • SMON会从空闲的undo segment中回收undo space,以便保证其他tranaction table需要空间时可用。另一个好处是undo datafile的身材不会急速膨胀导致用户要去resize
    • 当处于undo space空间压力时,特别是在发生UNDO STEAL的条件下; SGA中会记录前台进程因为undo space压力而做的undo steal的次数(v$undostat UNXPSTEALCNT EXPSTEALCNT);若这种UNDO STEAL的次数超过特定的阀值,则SMON会尝试shrink transaction table

若smon shrink rollback/undo真的发生时,会这样处理:

计算平均的undo retention大小,按照下列公式:

retention size=(undo_retention * undo_rate)/(#online_transaction_table_segment 在线回滚段的个数)

对于每一个undo segment

  • 若是offline的undo segment,则回收其所有的已过期expired undo extents,保持最小2个extents的空间
  • 若是online的undo segment,则回收其所有的已过期expired undo extents,但是保持其segment所占空间不小于平均retention对应的大小。

 

注意SMON的定期Shrink,每12个小时才发生一次,具体发生时可以参考SMON进程的TRACE。

 

若系统中存在大事务,则rollback/undo segment可能扩展到很大的尺寸;视乎事务的大小,则undo tablespace上的undo/rollback segment会呈现出不规则的空间占用分布。

SMON的定期清理undo/rollback segment就是要像一个大锤敲击钢铁那样,把这些大小不规则的online segment清理成大小统一的回滚段,以便今后使用。

当然这种定期的shrink也可能造成一些阻碍,毕竟在shrink过程中会将undo segment header锁住,则事务极低概率可能遇到ORA-1551错误:

 

 

[oracle@vmac1 ~]$ oerr ora 1551
01551, 00000, "extended rollback segment, pinned blocks released"
// *Cause: Doing recursive extent of rollback segment, trapped internally
//        by the system
// *Action: None

 

如何禁止SMON SHRINK UNDO SEGMENT?

 

可以通过设置诊断事件event=’10512 trace name context forever, level 1’来禁用SMON OFFLINE UNDO SEGS;

 

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com

SQL> alter system set events '10512 trace name context forever,level 1';

System altered.

 

 

相关BUG

这些BUG主要集中在9.2.0.8之前,10.2.0.3以后几乎绝迹了:

 

Bug 1955307 – SMON may self-deadlock (ORA-60) shrinking a rollback segment in SMU mode [ID 1955307.8]
Bug 3476871 : SMON ORA-60 ORA-474 ORA-601 AND DATABASE CRASHED
Bug 5902053 : SMON WAITING ON ‘UNDO SEGMENT TX SLOT’ HANGS DATABASE
Bug 6084112 : INSTANCE SLOW SHOW SEVERAL LONGTIME RUNNING WAIT EVENTS

dbms_space_admin.drop_empty_segments

create or replace
package dbms_space_admin is
————
— OVERVIEW

— This package provides tablespace/segment space administration
— not available through the standard sql.

— SECURITY

— run with SYS privileges. thus any user who has privilege to execute the
— the package gets to manipulate the bitmapes.

— CONSTANTS to be used as OPTIONS for various procedures
— refer comments with procedure(s) for more detail

SEGMENT_VERIFY_EXTENTS constant positive := 1;
— used to verify that the space owned by segment is appropriately reflected
— in the bitmap as used
SEGMENT_VERIFY_EXTENTS_GLOBAL constant positive := 2;
— used to verify that the space owned bu segment is appropriately reflected
— in the bitmap as used and that no other segment claims any of this space
— to be used by it
SEGMENT_MARK_CORRUPT constant positive := 3;
— used to mark a temp segment as corrupt whereby facilitating its
— elimination from the dictionary (without space reclaim)
SEGMENT_MARK_VALID constant positive := 4;
— used to mark a corrupt temp segment as valid. Useful when the corruption
— in the segment extent map or elsewhere has been resolved and the segment
— can be dropped normally
SEGMENT_DUMP_EXTENT_MAP constant positive := 5;
— dump the extent map for a given segment
TABLESPACE_VERIFY_BITMAP constant positive := 6;
— verifies the bitmap of the tablespace with extent maps of the segments
— in that tablespace to make sure everything is consistent
TABLESPACE_EXTENT_MAKE_FREE constant positive := 7;
— makes this range (extent) of space free in the bitmaps
TABLESPACE_EXTENT_MAKE_USED constant positive := 8;
— makes this range (extent) of space used in the bitmaps

SEGMENT_VERIFY_BASIC constant positive := 9;
SEGMENT_VERIFY_DEEP constant positive := 10;
SEGMENT_VERIFY_SPECIFIC constant positive := 11;
HWM_CHECK constant positive := 12;
BMB_CHECK constant positive := 13;
SEG_DICT_CHECK constant positive := 14;
EXTENT_TS_BITMAP_CHECK constant positive := 15;
DB_BACKPOINTER_CHECK constant positive := 16;
EXTENT_SEGMENT_BITMAP_CHECK constant positive := 17;
BITMAPS_CHECK constant positive := 18;
TS_VERIFY_BITMAPS constant positive := 19;
TS_VERIFY_DEEP constant positive := 20;
TS_VERIFY_SEGMENTS constant positive := 21;

SEGMENT_DUMP_BITMAP_SUMMARY constant positive := 27;

NGLOB_HBB_CHECK constant positive := 12;
NGLOB_FSB_CHECK constant positive := 13;
NGLOB_PUA_CHECK constant positive := 14;
NGLOB_CFS_CHECK constant positive := 15;

 

—————————-
— PROCEDURES AND FUNCTIONS

procedure segment_verify(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive ,
verify_option in positive DEFAULT SEGMENT_VERIFY_EXTENTS
);

— Verifies the consistency of the extent map of the segment
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header
— verify_option – SEGMENT_VERIFY_EXTENTS or
— SEGMENT_VERIFY_EXTENTS_GLOBAL

procedure segment_corrupt(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive ,
corrupt_option in positive DEFAULT SEGMENT_MARK_CORRUPT
);

— Marks the segment corrupt/valid so that appropriate error recovery
— can be done/skipped. Only for segments already temporary.
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header
— corrupt_option – SEGMENT_MARK_CORRUPT or
— SEGMENT_MARK_VALID

procedure segment_drop_corrupt(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive
);

— Drops a segment currently marked corrupt (without reclaiming space)
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header

procedure segment_dump(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive ,
dump_option in positive DEFAULT SEGMENT_DUMP_EXTENT_MAP
);


— Dumps the extent map of a given segment
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header
— dump_option – SEGMENT_DUMP_EXTENT_MAP

procedure tablespace_verify(
tablespace_name in varchar2 ,
verify_option in positive DEFAULT TABLESPACE_VERIFY_BITMAP
);

— Verifies that the extent maps and the bitmaps are in sync.
— Input arguments:
— tablespace_name – name of tablespace
— verify_option – TABLESPACE_VERIFY_BITMAP

procedure tablespace_fix_bitmaps(
tablespace_name in varchar2 ,
dbarange_relative_file in positive ,
dbarange_begin_block in positive ,
dbarange_end_block in positive ,
fix_option in positive
);

— Marks the appropriate dba range (extent) as free/used in bitmap
— Input arguments:
— tablespace_name – name of tablespace
— dbarange_relative_file – relative fileno of dba range (extent)
— dbarange_begin_block – block number of beginning of extent
— dbarange_end_block – block number (inclusive) of end of extent
— fix_option – TABLESPACE_EXTENT_MAKE_FREE or
— TABLESPACE_EXTENT_MAKE_USED

procedure tablespace_rebuild_bitmaps(
tablespace_name in varchar2 ,
bitmap_relative_file in positive DEFAULT NULL,
bitmap_block in positive DEFAULT NULL
);

— Rebuilds the appropriate bitmap(s). If no bitmap block dba specified then
— rebuilds all bitmaps for the given tablespace
— Input arguments:
— tablespace_name – name of tablespace
— bitmap_relative_file – relative file number of bitmap block to rebuild
— bitmap_block – block number of bitmap block to rebuild


procedure tablespace_rebuild_quotas(
tablespace_name in varchar2
);

— Rebuilds quotas for given tablespace
— Input arguments:
— tablespace_name – name of tablespace


procedure tablespace_migrate_from_local(
tablespace_name in varchar2
);

— Migrates a locally-managed tablespace to dictionary managed
— Input arguments:
— tablespace_name – name of tablespace


procedure tablespace_migrate_to_local(
tablespace_name in varchar2,
unit_size in positive DEFAULT NULL,
rfno in positive DEFAULT NULL
);

— Migrates a dictionary-managed tablespace to locally managed
— Input arguments:
— tablespace_name – name of tablespace
— unit_size – bitmap unit size for the tablespace


procedure tablespace_relocate_bitmaps(
tablespace_name in varchar2,
filno in positive,
blkno in positive);


— Tablespace relocate bitmaps to a different location
— Input arguments:
— tablespace_name – name of tablespace


procedure tablespace_fix_segment_states(
tablespace_name in varchar2);


— Tablespace fix segment states. During migration of tablespaces
— the segments are put in a transition state. If migration fails,
— the state of the segments can be corrected by calling this
— procedure. This procedure should be called if migration failed
— and the user tries to run in an incompatible mode.


procedure tablespace_fix_segment_extblks(
tablespace_name in varchar2);


— Tablespace fix segment extents and blocks based on segment
— header entries


procedure tablespace_dump_bitmaps(
tablespace_name in varchar2);


— Tablespace space header of files

function segment_number_extents(
header_tablespace_id in natural ,
header_relative_file in positive ,
header_block in positive ,
segment_type in positive ,
buffer_pool_id in natural ,
dictionary_flags in natural ,
data_object_id in number,
dictionary_extents in number
) return pls_integer;
pragma RESTRICT_REFERENCES(segment_number_extents,WNDS,WNPS,RNPS);

— Returns the number of extents which belong to the segment. Will return
— NULL if segment has disappeared. IS NOT to be used for any other
— purposes but by the views which need it and are sure that there info
— is correct. Else internal errors will abound

function segment_number_blocks(
header_tablespace_id in natural ,
header_relative_file in positive ,
header_block in positive ,
segment_type in positive ,
buffer_pool_id in natural ,
dictionary_flags in natural ,
data_object_id in number,
dictionary_blocks in number
) return pls_integer;
pragma RESTRICT_REFERENCES(segment_number_blocks,WNDS,WNPS,RNPS);

— Returns the number of blocks which belong to the segment. Will return
— NULL if segment has disappeared. IS NOT to be used for any other
— purposes but by the views which need it and are sure that there info
— is correct. Else internal errors will abound

procedure segment_moveblocks(
segment_owner in varchar2,
segment_name in varchar2,
partition_name in varchar2,
segment_type in varchar2,
group_id in natural,
minimum_size in positive,
move_count in positive,
pause_time in natural,
iterations in positive
);

— Moves blocks from the segment header to the process freelist
— It only moves blocks if the list is shorter than the minimum
— size for the move_count blocks
— segment_owner – name of the object’s owner
— segment_name – name of the object
— partition_name – name of the partition (NULL if not partitioned)
— segment_type – object type (TABLE, INDEX, etc – see DBMS_SPACE)
— group_id – freelist group (0 for the segment header)
— minimum_size – do not move if process free list is longer
— move_count – move up to this number of blocks
— pause_time – pause between loop iterations
— iterations – number of iterations (infinite if NULL)

 

procedure assm_segment_verify(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2,
verify_option in positive default SEGMENT_VERIFY_BASIC ,
attrib in positive default NULL);

— Verifies the consistency of the segment
— Input arguments:
— segment_owner – owner
— segment_name – name of the segment
— segment_type – type of segment
— partition_name – name of partition default NULL
— verify_option – one of SEGMENT_VERIFY_BASIC, DEEP , SPECIFIC
— attrib – used when option SEGMENT_VERIFY_SPECIFIC
procedure nglob_segment_verify(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2,
verify_option in positive default SEGMENT_VERIFY_BASIC ,
attrib in positive default NULL);

— Verifies the consistency of the segment
— Input arguments:
— segment_owner – owner
— segment_name – name of the segment
— segment_type – type of segment
— partition_name – name of partition default NULL
— verify_option – one of SEGMENT_VERIFY_BASIC, DEEP , SPECIFIC
— attrib – used when option SEGMENT_VERIFY_SPECIFIC

procedure assm_tablespace_verify(
tablespace_name in varchar2,
ts_option in positive,
segment_option in positive default NULL);


— Verifies that the tablespace consistency.
— Input arguments:
— tablespace_name – name of tablespace
— ts_option – TS_VERIFY_BITMAPS, TS_VERIFY_DEEP, TS_VERIFY_SEGMENTS
— segment option – used when TS_VERIFY_SEGMENTS, one of SEGMENT_VERIFY_DEEP, SPECIFIC

function assm_segment_synchwm(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2 default NULL,
check_only in number default 1
) return pls_integer;


— Synchronize HWMs of the ASSM segment
— Input arguments:
— segment_owner – owner
— segment_name – name of the segment
— segment_type – type of segment
— partition_name – name of partition default NULL
— check_only – whether it is check only default YES
— Output:
— Return TRUE if the segment requires HWM synchronization
— Return FALSE otherwise

procedure flush_lobsegment_stats;

procedure purge_lobsegment_stats;
————————————————————————-
— PROCEDURE securefile_segment_repair
————————————————————————-

— segment repair option: repair metadata blocks
SEGMENT_REPAIR_METADATA constant positive := 1;

procedure segment_repair(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2 default NULL,
repair_option in number default SEGMENT_REPAIR_METADATA
);


— Description:
— Repair SecureFile segment
— Input parameters:
— segment_owner – owner
— segment_name – name of the LOB segment
— segment_type – type of segment
— values: ‘LOB’, ‘LOB PARTITION’, ‘LOB SUBPARTITION’
— partition_name – name of the LON partition segment
— default: NULL
— repair_option – segment repair option,
— values: see SEGMENT_REPAIR_*** definitions
— default: SEGMENT_REPAIR_METADATA
— Note:
— This function is only used internally and does not require
— documentation.
————————————————————————-
— PROCEDURE segment_extend
————————————————————————-

procedure segment_extend(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2 default NULL,
target_size in number default 1
);


— Description:
— SecureFile segment extend in background
— Input parameters:
— segment_owner – owner
— segment_name – name of the LOB segment
— segment_type – type of segment
— values: ‘LOB’, ‘LOB PARTITION’, ‘LOB SUBPARTITION’
— partition_name – name of the LOB partition segment
— default: NULL
— target_size – segment target size in GB
— Note:
— This function is only used internally and does not require
— documentation.
————————————————————————-
— PROCEDURE drop_empty_segments
————————————————————————-

procedure drop_empty_segments(
schema_name in varchar2 default NULL,
table_name in varchar2 default NULL,
partition_name in varchar2 default NULL
);


— Description:
— Drop segments from empty table(s)/table fragments and dependent
— objects.
— Input parameters:
— schema_name – schema name, default: NULL
— table_name – table name, default: NULL
— partition_name – partition name, default: NULL
— Note:
— Given a schema name, this procedure scans all tables in the schema
— For each table, if the table or any of its fragments are found to be
— empty, and the table satisfies certain criteria [restrictions being
— the same as those imposed by segment creation on demand], the empty
— fragments and associated index segments are dropped. A subsequent
— insert will create segments with the same properties.
— Optionally,
— a. no schema name may be specified in which case we would scan
— tables belonging to all schemas
— b. both schema_name and table_name may be specified to do this
— operation on one particular table
— c. all three arguments may be supplied, in which case we will
— restrict this operation to that partition and its dependent
— objects.
————————————————————————-
— PROCEDURE materialize_deferred_segments
————————————————————————-

procedure materialize_deferred_segments(
schema_name in varchar2 default NULL,
table_name in varchar2 default NULL,
partition_name in varchar2 default NULL
);


— Description:
— Materialize segments for tables/table fragments with deferred
— segment creation (and their dependent objects)
— Input parameters:
— schema_name – schema name, default: NULL
— table_name – table name, default: NULL
— partition_name – partition name, default: NULL
— Note:
— Given a schema name, this procedure scans all tables in the schema.
— For each table, if the deferred/delayed segment property is set for
— the table or any of its fragments, a new segment is created for
— those fragments and their dependent objects.
— Optionally,
— a. no schema name may be specified in which case we would scan tables
— belonging to all schemas
— b. both schema_name and table_name may be specified to do this
— operation on one particular table
— c. all three arguments may be supplied, in which case we will
— restrict this operation to that partition and its dependent
— objects
————————————————————————-
— PROCEDURE materialize_deferred_with_opt
————————————————————————-

procedure materialize_deferred_with_opt(
schema_name in varchar2 default NULL,
table_name in varchar2 default NULL,
partition_name in varchar2 default NULL,
partitioned_only in boolean default FALSE
);


— Description:
— Materialize segments for tables/table fragments with deferred
— segment creation (and their dependent objects), with an additional
— option.
— Input parameters:
— schema_name – schema name, default: NULL
— table_name – table name, default: NULL
— partition_name – partition name, default: NULL
— partitioned_only – apply materialize procedure on partitioned
— tables only, default: FALSE
— Note:
— The materialize_deferred_segments procedure is a wrapper around
— this. This procedure is required for downgrading (from 11.2.0.2)
— to materialize segments for partitioned tables only. The
— partitioned_only argument supports this limited behavior.
————————————————————————-
— PROCEDURE tablespace_fix_affinity
————————————————————————-
procedure tablespace_fix_affinity(
tablespace_name in varchar2,
block_num in binary_integer,
instance_id in binary_integer DEFAULT NULL
);


— Description:
— This procedure sets instance affinity of a File Bitmap Block
— in bigfile tablespace. The block can be either a Level 1 bitmap
— block (FFB) or a Level 2 bitmap block (FSB).
— The report will be generated in the corresponding trace file of
— the session that runs this procedure.

— Input parameters:
— tablespace_name – name of tablespace
— block_num – block number
— instance_id – id of instance to be affined, by default
— it is the current instance

— Note:
— (1) If the block is Second-level File Bitmap block (FSB), and if
— there is another FSB that has already been affined to the instance
— specified, this FSB will be freed.
— (2) This function is only used internally and does not require
— documentation.
end;

沪ICP备14014813号

沪公网安备 31010802001379号