Oracle SYSAUX表空间中SQL_MANAGEMENT_BASE组件过大处理方式

SYSAUX表空间中SQL_MANAGEMENT_BASE组件过大处理方式

SQL PLAN BASELINES:

中文名SQL执行计划基线（简称基线），基本上它的主要作用可以归纳为如下：
1.稳定给定SQL语句的执行计划，防止执行环境或对象统计信息等等因子的改变对SQL语句的执行计划产生影响。
2.减少数据库中出现SQL语句性能退化的概率，理论上不允许一条语句切换到一个比已经执行过的执行计划慢很多的新的执行计划上。

在11204环境下,默认情况下SQL PLAN BASELINES捕获功能默认为关闭:

SQL> show parameter sql
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

如果不开启optimizer_capture_sql_plan_baselines参数
那么就不会自动捕获执行过的SQL语句,并且只有执行两次的SQL语句才会被捕获(可以测试)。

如果不开启optimizer_capture_sql_plan_baselines参数
那么SQL_MANAGEMTNT_BASE（SMB）组件占用空间也不会增大。

开启捕获:
alter system set optimizer_capture_sql_plan_baselines=true scope=both;

查询SQL PLAN BASELINE条目信息:
SQL> select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines;
SQL_HANDLE                     PLAN_NAME                      ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --------------
SQL_15fd249fe8d13767           SQL_PLAN_1bz94mznd2dv7391601ca YES NO               1
SQL_17d5afbcf9738173           SQL_PLAN_1gpdgrmwr70bm0538c56d YES NO             338
SQL_6720b1ddecdbc6c1           SQL_PLAN_6f85jvrqdrjq1d583a8e3 YES NO              31
SQL_aea38aa7dc216335           SQL_PLAN_ax8wanzf22stp6397e3b7 YES NO               0
SQL_b999cdcccb07a248           SQL_PLAN_bm6fdtm5hg8k87347ab53 YES NO               2

SQL_PLAN_MANAGEMTNT:
将SQL_PLAN_BASELINE信息存放在一个数据字典中,这个数据字典就是SQL_MANAGEMTNT_BASE（SMB），SMB被存放在sysaux表空间中。
所以SQL PLAN BASELINE信息多少直接关系到SQL_MANAGEMTNT_BASE(SMB)的大小。

查询SQL_MANAGEMTNT_BASE（SMB）占用SYSAUX空间占比:
select OCCUPANT_NAME,SCHEMA_NAME,SPACE_USAGE_KBYTES,MOVE_PROCEDURE from V$SYSAUX_OCCUPANTS where OCCUPANT_NAME='SQL_MANAGEMTNT_BASE'
OCCUPANT_NAME             SCHEMA_NAME          SPACE_USAGE_KBYTES MOVE_PROCEDURE
------------------------- -------------------- ------------------ --------------------
SQL_MANAGEMENT_BASE       SYS                                1728

可以看出MOVE_PROCEDURE列为null,因此对于SMB组件,Oracle是不支持迁移到其他表空间中的。

由于其不可迁移,那么当SQL_MANAGEMTNT_BASE（SMB）空间占用SYSAUX表空间较大时该如何解决呢

1.配置SMB使用空间占比以及自动删除策略。

SQL> select parameter_name,parameter_value from dba_sql_management_config;
PARAMETER_NAME         PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT             10
PLAN_RETENTION_WEEKS             53

参数space_budget_percent表示SMB可以占用sysaux表空间的比例。默认是10%
参数PLAN_RETENTION_WEEKS 表示SMB自动删除时间。默认是10%（只会删除未被使用过的SQL Plan）

SQL> exec dbms_spm.configure('space_budget_percent',40);  占用比例
SQL> exec dbms_spm.configure ('plan_retention_weeks', 105); 删除策略

2.手动删除
我们可以选择删除部分SQL PLAN BASELINE或者删除全部的SQL PLAN BASELINE，
但是注意全部删除这样会影响到执行计划的稳定性，极有可能会造成数据库性能问题。

SQL PLAN BASELINE单个清除方式如下:

SQL> select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines;

declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_32814757f4b84f6b',plan_name=>null);
END;
/

SQL PLAN BASELINE全部清除方式如下：

connect / as sysdba
select count(*) from dba_sql_plan_baselines;

declare
pgn number;
sqlhdl varchar2(30);
cursor hdl_cur is
select distinct sql_handle from dba_sql_plan_baselines;
begin
open hdl_cur;
loop
fetch hdl_cur into sqlhdl;
exit when hdl_cur%NOTFOUND;
pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>sqlhdl);
end loop;
close hdl_cur;
commit;
end;
/

select count(*) from dba_sql_plan_baselines;

在我们清理完SQL PLAN BASELINE后，SQL MANAGEMTNT BASE(SMB)在SYSAUX表空间占用的空间不会被释放掉，我们需要执行如下命令，让其释放空间。

alter table "SYS"."SQL$" enable row movement;
alter table "SYS"."SQL$TEXT" enable row movement;
alter table "SYS"."SQLOBJ$AUXDATA" enable row movement;
alter table "SYS"."SQL$" shrink space cascade;
alter table "SYS"."SQL$TEXT" shrink space cascade;
alter table "SYS"."SQLOBJ$AUXDATA" shrink space cascade;
alter table "SYS"."SQL$" disable row movement;
alter table "SYS"."SQL$TEXT" disable row movement;
alter table "SYS"."SQLOBJ$AUXDATA" disable row movement;
alter table "SYS"."SQLOBJ$" shrink space cascade;
alter table "SYS"."SQLOBJ$DATA" shrink space cascade;
alter table "SYS"."SQL$" modify lob ("SPARE2") (shrink space cascade);
alter table "SYS"."SQL$TEXT" modify lob ("SPARE2") (shrink space cascade);
alter table "SYS"."SQL$TEXT" modify lob ("SQL_TEXT") (shrink space cascade);
alter table "SYS"."SQLOBJ$" modify lob ("SPARE2") (shrink space cascade);
alter table "SYS"."SQLOBJ$DATA" modify lob ("COMP_DATA") (shrink space cascade);
alter table "SYS"."SQLOBJ$DATA" modify lob ("SPARE2") (shrink space cascade);
alter table "SYS"."SQLOBJ$AUXDATA" modify lob ("SPARE2") (shrink space cascade);

3.Oracle BUG原因导致SMB组件占用过大空间
Bug 9910484 - SQL Plan Management Capture uses excessive space in SYSAUX (Doc ID 9910484.8)
官方建议关闭SQL捕获功能

参考以及相关文档:

How to Drop Plans from the SQL Plan Management (SPM) Repository (Doc ID 790039.1) 
Is it Possible to Move SQL Plan Management (SPM) Data Outside of SYSAUX (Doc ID 2065088.1)
[Note 1499542.1]Reducing the Space Usage of the SQL Management Base in the SYSAUX Tablespace
[Note 1905305.1]Using SQL Plan Management (SPM)
How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes (Doc ID 1563921.1)
[Document 287679.1]Space Management In Sysaux Tablespace with AWR in Use
[Document 329984.1]Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER