Oracle段收縮功能

Oracle段收縮功能
1. 瞭解段收縮

應用情境：如果對一張表頻繁執行插入、更新和刪除操作，時間長了可能會出現大量片段，Oracle針對這種情境推出段收縮功能，以便減少片段。
Oracle的段收縮執行兩項不同的任務：(1)壓縮資料行，(2)移動高水位線（HWM）。
因為重新置放HWM可能阻塞使用者的DML操作，而存粹的收縮操作則不會產生這種影響。
操作期間會維護表上的所有索引。

2. 自動執行Segment Advisor

 

2.1 在OEM查看並設定

伺服器 -> Oracle Scheduler -> 自動維護任務 -> 配置，來啟用或禁用“段指導”任務，同時這裡還可以設定“最佳化程式統計資訊搜集”，“自動 SQL 最佳化”

2.2 使用SQL命令設定

--禁用自動執行Segment AdvisorBEGINdbms_auto_task_admin.disable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);END;/--啟用自動執行Segment AdvisorBEGINdbms_auto_task_admin.enable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);END;/

3. 收縮段

收縮段核心步驟：

--啟用行移動alter table T3 enable row movement;--與大多數Oracle SQL命令不同，添加COMPACT關鍵字後，實際執行的操作不多反少，即HWM不移動alter table T3 shrink space compact;--去掉COMPACT關鍵字後，執行HWM移動alter table T3 shrink space;--禁用行移動alter table T3 disable row movement;

實驗：執行段分析和收縮操作

3.1 建立表T3

--建立表T3create table T3 (c1 varchar2(4000));

3.2 往T3插入1000行（使用大值），然後使用小值更新這些行

--往T3插入1000行（使用大值）begin  for i in 1..1000 loop    insert into T3 select rpad(i,3900,'*') from dual;  end loop;  commit; end;/--T3大小select segment_name, BYTES/1024/1024 "MB" from user_segments where segment_name = 'T3';SEGMENT_NAME                           MB------------------------------ ----------T3                                      8--使用小值更新這些行update t3 set c1='1';commit;--此時重新查詢表T3大小是不變的

3.3 設定啟動Segment Advisor作業

variable task_id numberdeclare  name varchar2(100);  descr varchar2(500);  obj_id number;begin  name := ''; --unique name  descr := 'Check T3 table';  dbms_advisor.create_task('Segment Advisor', :task_id, name, descr, null);  dbms_advisor.create_object(name, 'TABLE', 'JINGYU', 'T3', null, null, obj_id);  dbms_advisor.set_task_parameter(name, 'RECOMMEND_ALL', 'TRUE');  dbms_advisor.execute_task(name);end;/

執行過程如下：

SQL> conn jingyu/jingyuConnected.SQL> variable task_id numberSQL> declare  2    name varchar2(100);  3    descr varchar2(500);  4    obj_id number;  5  begin  6    name := ''; --unique name  7    descr := 'Check T3 table';  8    dbms_advisor.create_task('Segment Advisor', :task_id, name, descr, null);  9    dbms_advisor.create_object(name, 'TABLE', 'JINGYU', 'T3', null, null, obj_id); 10    dbms_advisor.set_task_parameter(name, 'RECOMMEND_ALL', 'TRUE'); 11    dbms_advisor.execute_task(name); 12  end; 13  /PL/SQL procedure successfully completed.Elapsed: 00:00:02.01SQL> SQL> SQL> print task_id   TASK_ID----------      1358

根據上面的TASK_ID查詢DBA_ADVISOR_FINDINGS

col owner for a20col task_name for a20col type for a20select owner, task_id, task_name, type, message, more_info from dba_advisor_findings where task_id=1358;OWNER                   TASK_ID TASK_NAME            TYPE-------------------- ---------- -------------------- --------------------MESSAGE------------------------------------------------------------------------------------------------------------------------MORE_INFO------------------------------------------------------------------------------------------------------------------------JINGYU                     1358 TASK_1358            PROBLEMThe free space in the object is less than 10MB.Allocated Space:8388608: Used Space:954000: Reclaimable Space :7434608:

3.4 執行段收縮

alter table T3 enable row movement;alter table T3 shrink space compact;alter table T3 shrink space;alter table T3 disable row movement;--T3大小select segment_name, BYTES/1024/1024 "MB" from user_segments where segment_name = 'T3';SEGMENT_NAME                           MB------------------------------ ----------T3                                  .0625

可以看到，T3表由8M大小成功收縮成0.0625M大小。至此，完成T3表的shrink操作。