关于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即可

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23850820/viewspace-1097685/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23850820/viewspace-1097685/