oracle sqltune,关于Oracle Baseline和DBMS_SQLTUNE工具

前段时间生产系统出现过一个问题。一个报表突然出现运行失败的情况，检查发现报表在运行的时候不停的消耗TEMP表空间，导致TEMP表空间不足然后SQL失败报错。这个SQL平时都是在20分

钟之内就跑完的，而且也基本不会占用TEMP表空间，单独拿出来给相应的变量赋值运行也正常。另外，其他的SQL和数据库都正常

说明这个SQL的执行计划发生了变化，导致SQL结果跑不出来。

原因且不查，先考虑怎么解决。

数据库环境：Oracle 11.2.0.2

系统环境：AIX 6100-06-04

第一步，尝试进行表分析

结果：做过表分析后，还是运行不成功。而现象则相比原来更差。之前是每秒钟消耗20M临时表空间直至临时表空间不足导致退出。现在是每分钟消耗4M 临时表空间，运行大半个小

时还是运行不出结果

结论：还是SQL的执行计划有问题

第二步，使用oracle提供的baseline工具    baseline是oracle 11g中提供的一个不错的功能，可以将sql语句的执行计划进行调整，使用好的执行计划替换掉不好的执行计划。

但是目前的情况是，找不到一个好的执行计划，因此使用baseline在这个场景并不合适。

而在尝试的过程中，我们将optimizer_capture_sql_plan_baselines参数设置为true,导致后面出现一点麻烦，后面再讲。

alter system set optimizer_capture_sql_plan_baselines=true;

第三步，使用DBMS_SQLTUNE工具包。这个工具包可以对SQL的执行计划进行调整

操作步骤如下：

1. 创建并执行优化任务：

DECLARE

my_task_name VARCHAR2(30);

BEGIN

my_task_name :=   DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_id => 'gn0zff46rbhak',

scope => 'COMPREHENSIVE',

time_limit => 3600,

task_name => 'test_falist_tuning_task1',

description => 'Task to tune a query');

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_falist_tuning_task1');

END;

/

2. 检查系统建议的计划，如果不能接受，则再次执行步骤1进行优化

select dbms_sqltune.report_tuning_task('test_falist_tuning_task1') from dual;

3.  使建议的执行计划生效

execute dbms_sqltune.accept_sql_profile(task_name => 'test_falist_tuning_task1', task_owner => 'APPS', replace =>TRUE);

4. 测试报表，进行验证

最终，报表语句的执行效率正常。

本以为问题就这样解决了，突然发现数据库的SYSAUX表空间快速增长。检查发现是sys.SQLOBJ$DATA表的数据量一直在增加，表中LOB字段COMP_DATA SYS_LOB0000968567C00005$$占用很大空间

。主要原因是optimizer_capture_sql_plan_baselines=true参数的设置

(参考Bug 9910484  SQL Plan Management Capture uses excessive space in SYSAUX)

此BUG出现会影响到下列oracle版本

11.2.0.2

11.2.0.1

11.1.0.7

如果不是必须，把这个参数设置为false即可