Oracle Segment Advisor

本篇大部分内容来源于官方文档Adminstrator's Guide.Automatic Segment Advisor 成为ASA，Segment Advisor 成为SA。

Segment Advisor可以发现：

• 高水位(high water mark)下有大量未使用空间的表。
• 可以使用OLTP压缩的表（仅限于ASA）。
• 有大量chained rows的表

同时，SA也可以给出意见。

SA通过使用AWR中的数据并对每个segment取样来的出自己的结论。默认的，SA会在数据库的maintenance window自动运行，称之为ASA。

ASA

ASA不会分析数据库中每个segment，他仅仅分析：

• 空间使用已经超过阈值的表空间
• 最活跃的segment
• 空间增长最快的segment

手工执行SA

VARIABLE id NUMBER;
BEGIN
  declare
    v_name   varchar2(100);
    v_descr  varchar2(500);
    v_obj_id NUMBER;
  BEGIN
    v_name :='Manual_Employees';
    v_descr:='Segment Advisor Example';
    
    dbms_advisor.create_task(advisor_name => 'Segment Advisor', task_id => :id, task_name => v_name, task_desc => v_descr);
    dbms_advisor.create_object ( task_name => v_name, object_type => 'TABLE', attr1 => 'SCOTT', attr2 => 'DEPT', attr3 => NULL, attr4 => NULL, attr5 => NULL, object_id => v_obj_id);
    dbms_advisor.set_task_parameter( task_name => v_name, parameter => 'recommend_all', value => 'TRUE');
    dbms_advisor.execute_task(v_name);
  END;
END;

SA可以对tablespace, table, index, partition, subpartition做分析。假如表时分区，create_object时object_type指定'TABLE'，SA会分析分区，子分区。

各个函数/存储过程的具体参数请参考官方文档。

查看SA结果

DBA_Advisor_xxx 视图保存了结果。具体请查看官方文档的Reference这一本。

dba_advisor_tasks可以查看task的状态，确定其是否完成。

dba_advisor_findings

一个更好的办法是使用DBMS_SPACE.ASA_RECOMMENDATIONS

select tablespace_name, segment_name, segment_type, partition_name,recommendations, c1 
from table(dbms_space.asa_recommendations('FALSE', 'TRUE', 'FALSE'));

第一个参数 all_runs：只适用于ASA。true显示所有的结果，false显示上一次的结果。 

第二个参数  show_manual：true 显示手工运行的。false:显示自动运行的。

第三个参数 show_findings：true只显示发现而不给出建议。

Shrinking objects:

该操作可以回收空闲空间，不管是HWM之上的或者HWM之下的。执行table shrink后，一行的rowid会改变， 表的HWM也会下降。几点注意的：

• Shrinking table会，索引会失效。
• IOT， 压缩表，不能执行table shrink.
• 执行前，需要alter table xxx enable row movement.
• 禁用rowid based triggers
• rowid based mv不能做table shrink

ALTER TABLE employees SHRINK SPACE CASCADE;
ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE;

Deallocating Unused Space

这个操作是回收HWM之上的空间，使得oracle有更多空间分配给其他segment。

ALTER TABLE table DEALLOCATE UNUSED KEEP integer;

Keep integer表示HWM之上保留多少空间。

dbms_space.space_usage

给出HWM之下空间利用情况。

variable unf number;
variable unfb number;
variable fs1 number;
variable fs1b number;
variable fs2 number;
variable fs2b number;
variable fs3 number;
variable fs3b number;
variable fs4 number;
variable fs4b number;
variable full number;
variable fullb number;
begin
dbms_space.space_usage('SCOTT','DEPT','TABLE',:unf, :unfb,:fs1, :fs1b,:fs2, :fs2b,:fs3, :fs3b,:fs4, :fs4b,:full, :fullb);
end;
/
print unf ;
print unfb ;
print fs4 ;
print fs4b;
print fs3 ;
print fs3b;
print fs2 ;
print fs2b;
print fs1 ;
print fs1b;
print full;
print fullb;

Unformatted_blocks: HWM之下，但是还未格式化的block个数。假如在ASSM tablespace中，要插入一条数据，而HWM之下已经没有空余空间，Oracle将可能新分配10 block，新的HWM将在这10 block之后，而low HWM是原来的HWM。这是对于这10 block，Oracle只需要格式化一个，剩下9个不格式化。在全表扫描时，Low HWM下的数据将被读取，Low HWM和HWM之间的block需要Oracle访问bitmap 来获知是否已经被格式化，因为读取9个未格式化的block是没有任何意义也不安全的。