[20210528]oracle大表空間預分配問題.txt

[20210528]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 SMCO 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.環境:
SCOTT@book> @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

SYS@book> @ 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

SYS@book> @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*:
SYS@book> @ 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;