oracle 大表空间预调配问题.txt
–// 应用 oracle 大表空间带来的益处就是一个表空间仅仅一个数据文件, 保护治理简略, 但也带来一些问题, 就是预调配问题.
–// 缺省 oracle 有一个后盾过程监测 SMCO (Space Management Coordinator). 看文档每个小时唤醒监测 1 次.
–// 当表空间有余时扩大数据文件. 这样能够带来一个益处, 比方用户大量导入数据, 而数据文件须要扩张时, 整个业务要略微进展.
–// 如果当时数据文件扩大了, 就能够缩小这方面的进展.
–// 受两个隐含参数的_enable_space_preallocation,_kttext_warning.
–// 设想一下如果数据文件 1T, 减少 5%, 也就是减少 50G, 假如写入 400m/s, 也大略须要 50000/400 = 125 秒实现.
–// 补充常识：
MOS 如下文档具体阐明了这个过程的作用：
SMCO (Space Management Coordinator) For Autoextend On Datafiles And How To Disable/Enable (文档 ID 743773.1)
What is the function of SMCO background process
SMCO coordinates the following space management tasks. It performs proactive space allocation and space reclamation. It
dynamically spawns slave processes (Wnnn) to implement the task.
SMCO 协调以下空间治理工作。它执行被动空间调配和空间回收。游戏代理的动静生成隶属过程 (Wnnn) 来实现工作。
表空间级别的空间预调配 - Tablespace-level space (Extent) pre-allocation.
Pre-allocation here refers to datafile extention, Datafile extension happens when a space request (extent
allocation) operation generally triggered via Insert / loading to a segment does not find contiguous space in the
tablespace, the session will extend the file by the next increment set for the datafile and will continue with the
space request or Extent allocation.
这里的预调配是指数据文件扩大，当通常通过插入 / 加载到段时触发的空间申请 (扩大区调配) 操作在表空间中找不到间断空间时会发
生数据文件扩大，会话将依照 increment 设置扩大文件，同时将持续应用空间申请或 Extent 调配。For SMCO to autoextend a datafile, the AUTOEXTEND should be set to ON for the datafile. SMCO decides to expand the
tablespace based on history, extension is split evenly across all datafiles in the tablespace which have not reached
their maxsize and are still limited to 10% of the full tablespace size in one hourly SMCO wakeup.
(Full tablespace size = Sum of datafile sizes at any given instance of time.)
要使 SMCO 主动扩大数据文件，应将的数据文件的 AUTOEXTEND 设置为 ON。SMCO 决定依据历史记录扩大表空间，扩大在表空间中的所有数
据文件中平均调配，这些数据文件尚未达到其 maxsize，并且在每小时 SMCO 唤醒时，扩大空间限度为残缺表空间大小的 10%。(残缺表
空间大小 = 任何给定时刻的数据文件大小总和。)
Apart from the above mentioned task, the SMCO process is also responsible for performing the following tasks.
updating block and extents counts in SEG$ for locally managed tablespaces after adding an extent (from unpublished
Bug 12940620)
Securefile lob segment pre-extension.
Securefile lob segment in-memory dispenser space pre-allocation.
Securefile lob segment space reclamation (moving free chunks from uncommitted free space area to committed free space area).
Temporary segment space reclamation.
The main advantage is that sessions need not wait for reactive space allocation/deallocation operations as this is
proactively done, and hence a gain in performance.
How to disable and enable the SMCO background process
The feature provided by the SMCOwww.walajiao.com process is called “Tablespace-level space (Extent) pre-allocation.”
It can be turned off by setting “_enable_space_preallocation”=0 as below:
ALTER SYSTEM SET “_ENABLE_SPACE_PREALLOCATION” = 0;
The feature can be turned on again any time by setting “_enable_space_preallocation”=3 which is the default value:
ALTER SYSTEM SET “_ENABLE_SPACE_PREALLOCATION” = 3;
The parameter is dynamic and the scope is ALTER SYSTEM.
NOTE:
Please use the double quotes as indicated to prevent an ORA-911 because of the leading ‘_’
Following is other possible settings:
- 0 to turn off the tbs pre-extension feature.
- 1 To enable tablespace extension.
- 2 To enable segment growth.
- 4 To enable chunk allocation.
以上摘自 MOS 内容，仅供参考。
1. 环境:
SCOTTbook> ver1
PORT_STRING VERSION BANNER
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
SYSbook> hide _enable_space_preallocation
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
_enable_space_preallocation enable space pre-allocation TRUE 3 3 FALSE IMMEDIATE
SYSbook> hide _kttext_warning
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
_kttext_warning tablespace pre-extension warning threshold in percentage TRUE 5 5 FALSE IMMEDIATE
–// 还有相干参数_ktslj*:
SYSbook> hide _ktslj
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
_ktslj_segext_max_mb segment pre-extension max size in MB (0: unlimited) TRUE 0 0 FALSE IMMEDIATE
_ktslj_segext_retry segment pre-extension retry TRUE 5 5 FALSE IMMEDIATE
_ktslj_segext_warning segment pre-extension warning threshold in percentage TRUE 10 10 FALSE IMMEDIATE
_ktslj_segext_warning_mb segment pre-extension warning threshold in MB TRUE 0 0 FALSE IMMEDIATE
2. 解决办法：
alter system set “_enable_space_preallocation”=0;
–// 留神监测表空间的应用定期减少表空间大小.
或者限度增长率:
alter system set “_kttext_warning”=1;