關於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/，如需轉載，請註明出處，否則將追究法律責任。