雲貝教育 | 【技術文章】優化代碼助手查詢資料字典的執行計劃

作者：劉曉峰

原文連結：http://www.tdpub.cn/Home/Blog/detail/action/preview/id/1328.html

這麼多步驟，也能寫自動化腳本，感興趣的可以自己嘗試一下

1.确認一定要開啟共享遊标

begin    
    EXECUTE IMMEDIATE ' alter session set "_optimizer_extended_cursor_sharing_rel"=none ';
    EXECUTE IMMEDIATE ' alter session set "_optimizer_extended_cursor_sharing"=none ';
    EXECUTE IMMEDIATE ' alter session set "_optimizer_adaptive_cursor_sharing"=false';
    EXECUTE IMMEDIATE 'alter session set cursor_sharing=FORCE ';
 end;

2.flush shared_pool的替代方式,友善找到代碼助手産生的執行計劃

DECLARE
  CURSOR a_cur IS
    SELECT DISTINCT s.address || ',' || s.hash_value addr
      FROM v$sql s
     WHERE upper(s.sql_text) LIKE upper('%all_tab_columns%')
       AND s.sql_text LIKE '%:%'
       AND s.sql_text NOT LIKE '%v$sql%';

BEGIN
  FOR a_rec IN a_cur LOOP
    EXECUTE IMMEDIATE 'begin  sys.dbms_shared_pool.purge(:1, ''c''); end;'
      USING a_rec.addr;
  END LOOP;
END;

3.使用代碼助手确認執行計劃

fnd_lookup_values_vl.attribute2
v$session.user#
select * from v$instance k where k.blocked

4.找到sql_id

select s.sql_id ,s.plan_hash_value,s.child_number from v$sql s where upper(s.sql_text) like upper('%all_tab_columns%') and s.sql_text like '%:%' and s.sql_text not like '%v$sql%'
   	SQL_ID PLAN_HASH_VALUE	CHILD_NUMBER
SQL_ID	PLAN_HASH_VALUE	 CHILD_NUMBER
8fqtv1mu1y2fv 1708561845 0

請自行替換後續的8fqtv1mu1y2fv 和1708561845為你環境的值

5.檢查執行計劃

select * from table(dbms_xplan.display_cursor('8fqtv1mu1y2fv','0','advanced'));
SQL_ID  8fqtv1mu1y2fv, child number 0
-------------------------------------
select column_name, nullable, data_type, data_type_mod, 
data_type_owner, data_length, data_precision, data_scale, char_used, 
char_length from sys.all_tab_columns where owner = :"SYS_B_0" and 
table_name = :"SYS_B_1" order by column_id
	 
Plan hash value: 1708561845
	 
-----------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |       |       |  1635 (100)|          |

6.開始使用SPM優化助手優化

DECLARE
  tuning_task_name VARCHAR2(240);
BEGIN

  tuning_task_name := dbms_sqltune.create_tuning_task(sql_id          => '8fqtv1mu1y2fv',
                                                      plan_hash_value => '1708561845',
                                                      scope           => 'COMPREHENSIVE',
                                                      time_limit      => 300, --優化時間上線，機關秒
                                                      task_name       => 'SQLTURNING_CODE_ASSISTANT',
                                                      description     => 'optimize sql',
                                                      con_name        => NULL);
END;

7.執行任務

begin
  dbms_sqltune.execute_tuning_task(task_name=>'SQLTURNING_CODE_ASSISTANT');
end;

8.查詢任務

select * from user_advisor_log u where u.task_name='SQLTURNING_CODE_ASSISTANT';

9.列印結果

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

10.找到關鍵詞“SQL Profile Finding”，

比如我的長這樣

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 77.34%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'SQLTURNING_CODE_ASSISTANT', task_owner => 'APPS', replace =>
            TRUE);

你能看到更多資訊

舊成本Plan hash value: 1062139556

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                   |     2 |   714 |  7435   (1)| 00:00:01 |

優化後的新成本Plan hash value: 4146289287

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |     1 |   357 |   292   (1)| 00:00:04 |

11.執行sql配置更改

begin
dbms_sqltune.accept_sql_profile(task_name =>
            'SQLTURNING_CODE_ASSISTANT', task_owner => 'APPS', replace =>
            TRUE);
end;

12.檢查執行計劃

select* from v$sql s where s.sql_id='8fqtv1mu1y2fv';--子遊标1性能更好
select * from table(dbms_xplan.display_cursor('8fqtv1mu1y2fv','0','advanced'));
select * from table(dbms_xplan.display_cursor('8fqtv1mu1y2fv','1','advanced'));

13.計劃基線捕獲

declare 
l_plans_loaded pls_integer;
begin
 l_plans_loaded:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'8fqtv1mu1y2fv'); --這裡有參數可以直接固定，我為了示範沒有使用fixed=>'YES'
 dbms_output.put_line(l_plans_loaded);
end;

14.檢查計劃基線

SELECT sql_handle,plan_name,enabled
      , --    訓示計劃基準是已啟用（YES）還是已禁用（NO）
       accepted
      , --   表示計劃基線是否被接受（YES）否（NO）
       fixed
      , --    訓示計劃基準是否固定（YES）（NO）  
       substr(sql_text,1,100)
  FROM dba_sql_plan_baselines s
 WHERE  upper(s.sql_text) like upper('%all_tab_columns%') and s.sql_text like '%:%' and s.sql_text not like '%v$sql%'
 SQL_HANDLE  PLAN_NAME ENABLED ACCEPTED  FIXED
SQL_a66bfc0020f65c85  SQL_PLAN_acuzw00hgcr453ebe2368  YES YES NO
SQL_a66bfc0020f65c85  SQL_PLAN_acuzw00hgcr458b0d60a7  YES YES NO

15.檢查并找到性能較好的執行計劃對應的基線

select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(plan_name => 'SQL_PLAN_acuzw00hgcr453ebe2368',sql_handle =>'SQL_a66bfc0020f65c85' ) ); 
select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(plan_name => 'SQL_PLAN_acuzw00hgcr458b0d60a7',sql_handle =>'SQL_a66bfc0020f65c85' ) );--此成本較低，性能好

可以看到執行計劃從跳躍掃描變成了索引順序掃描，同時驅動順序變化（正常的執行計劃應該是順序掃描才對，因為索引的先導列就是name,不應該執行跳躍掃描，正常情況下優化我直接寫hint完事了）

16.固定計劃基線

declare 
l_plans_altered pls_integer;
begin
l_plans_altered:=dbms_spm.alter_sql_plan_baseline(sql_handle      =>'SQL_a66bfc0020f65c85' ,
                                                  plan_name       =>'SQL_PLAN_acuzw00hgcr458b0d60a7' ,
                                                  attribute_name  =>'fixed' ,
                                                  attribute_value =>'YES' );

  
end;

17.重複步驟2,3，然後檢查新産生的執行計劃

select  s.sql_id ,s.plan_hash_value,s.child_number from v$sql s where upper(s.sql_text) like upper('%all_tab_columns%') and s.sql_text like '%:%' and s.sql_text not like '%v$sql%'
select * from table(dbms_xplan.display_cursor('8fqtv1mu1y2fv','0','advanced'));

優化完成

SQL_ID  8fqtv1mu1y2fv, child number 0
 -------------------------------------
 select column_name, nullable, data_type, data_type_mod, 
 data_type_owner, data_length, data_precision, data_scale, char_used, 
 char_length from sys.all_tab_columns where owner = :"SYS_B_0" and 
 table_name = :"SYS_B_1" order by column_id
  
 Plan hash value: 4146289287
  
  -----------------------------------------------------------------------------------------------------------
  | Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                      |                   |       |       |   292 (100)|          |

18.可以自行嘗試修改代碼助手的delay，由預設的500毫秒改成100毫秒甚至更低，

現在我的開發環境，代碼助手彈出column直接起飛,完全不卡，以前是5-10秒，非常卡頓，優化效果非常好（如果我能拿到trace就更有說服力）

如果還是存在卡頓，請按前面的教程，跑trace然後自行分析

另外package的代碼助手優化原理一模一樣，這裡不再贅述

select from fnd_lookup_types_vl flv where flv.lookup_type

雲貝教育 | 【技術文章】優化代碼助手查詢資料字典的執行計劃

繼續閱讀

CVS開設一個新項目

SQL慢？吐血整理MySQL中Explain分析工具

一文帶你了解雲原生微服務DevOps持續傳遞容器化

鍛煉身體是實作目标的基礎

sql執行計劃 explain 以及結合索引優化sql

yum [Errno 14] HTTP Error 404 - Not Found

VC 資料庫程式設計--ADO接口詳解

在FC4下編譯安裝chmsee

無聊時寫的模拟HTTP請求類(模拟POST或GET)

懶人創造的财富

iOS 從證書申請到上架App Store流程( xcode 5)詳細解析

推技術聊天室的實作(下)

java性能、負載、壓力測試工具

HTML的簡述

視覺傳感器在無人駕駛領域取得技術突破

vs2010 MFC 點選對話框按鈕無法建立響應函數