ORACLE通過BIND_AWARE+SQL PATCH解決SQL繫結變數中資料傾斜的問題

selectshen發表於2017-01-07
OracleSQL變數
       繫結變數在OLTP系統中是解決硬解析問題的有利手斷，但如果某SQL使用的索引中過濾欄位存在資料傾斜（資料分佈不均勻）的問題，就可能造成資料庫效能異常。從Oracle 9i引入的BIND PEEK不能解決這個問題，因為bind peek只是發生在第一次硬解析;從11G引入了ACS(adaptive cursor sharing)可以解決這個問題，但Bug多多，在最佳實踐中會將其關閉。當然我們可以通過HINT:BIND_AWARE加上SQL PATCH在不修改SQL的情況下解決ACS關閉時的這個問題。
以下測試：
DB Version:Oracle 11.2.0.4
OS:centos 6.6
測試工具：pl/sql developer
 
1.建立測試表和資料
--建表
create table scott.tb_sql_patch as select * from dba_objects;

--建索引
create index scott.idx_tb_sql_patch_01 on scott.tb_sql_patch(object_id);

--更新資料,使用資料分佈不均勻
update scott.tb_sql_patch set object_id=10 where object_id>10;
commit;

select object_id,count(1) from scott.tb_sql_patch group by object_id;
/*
OBJECT_ID    COUNT(1)
            3
6            1
2            1
5            1
4            1
8            1
3            1
7            1
10            86869
9            1
*/

--收集統計資訊
begin
  dbms_stats.gather_table_stats('scott','tb_sql_patch', method_opt => 'for columns object_id size auto',cascade=>true);
end;

--檢視直方圖資訊
select table_name,column_name,histogram from dba_tab_col_statistics
where table_name='TB_SQL_PATCH' and column_name='OBJECT_ID';
/*
TABLE_NAME    COLUMN_NAME    HISTOGRAM
TB_SQL_PATCH    OBJECT_ID    FREQUENCY
*/

select * from dba_tab_histograms
where table_name='TB_SQL_PATCH' and column_name='OBJECT_ID';
/*
OWNER    TABLE_NAME    COLUMN_NAME    ENDPOINT_NUMBER    ENDPOINT_VALUE    ENDPOINT_ACTUAL_VALUE
SCOTT    TB_SQL_PATCH    OBJECT_ID    1    2    
SCOTT    TB_SQL_PATCH    OBJECT_ID    2    8    
SCOTT    TB_SQL_PATCH    OBJECT_ID    5559    10    
*/

2.檢視ACS(adaptive cursor sharing)和bind peek相關引數
--從下面查詢結果可以看到ACS已關閉，BIND PEEK是開啟的。如果BIND PEEK關閉，ACS會自動關閉。
select name, value
  from v$parameter
 where name in ('_optimizer_adaptive_cursor_sharing',
                '_optimizer_extended_cursor_sharing_rel',
                '_optimizer_extended_cursor_sharing',
                '_optim_peek_user_binds');
                
/*
NAME    VALUE
_optimizer_extended_cursor_sharing    NONE
_optimizer_extended_cursor_sharing_rel    NONE
_optimizer_adaptive_cursor_sharing    FALSE
_optim_peek_user_binds    TRUE
*/

3.測試
3.1先看看使用非繫結變數的表現
select * from scott.tb_sql_patch where object_id=1;

select * from scott.tb_sql_patch where object_id=10;

--從下面的查詢結果可以看出，因為索引欄位上存在直方圖，SQL根據非繫結變數的實際值走了不同的執行計劃。這也是一種解決繫結變數中資料傾斜的方法，但使用非繫結變數，硬解析會加大。可以考慮在程式中先判斷變數的值來決定走繫結變數方式還是非繫結變數方式。
select sql_id,plan_hash_value,a.sql_text from v$sql a
where sql_text like 'select * from scott.tb_sql_patch where object_id%';
/*
SQL_ID    PLAN_HASH_VALUE    SQL_TEXT
atdt8tn0pgn5a    815055989    select * from scott.tb_sql_patch where object_id=10
dt85j6g88ztmj    57853615    select * from scott.tb_sql_patch where object_id=1
*/

3.2再來看看使用繫結變數的表現
--清空共享池
alter system flush shared_pool;

--使用繫結變數
--執行下面兩個pl/sql,兩個繫結變數的資料分佈不同
DECLARE
  V_SQL VARCHAR2(3000);
BEGIN
  V_SQL := 'select * from scott.tb_sql_patch where object_id=:1';
  EXECUTE IMMEDIATE V_SQL
    USING 1;
END;


DECLARE
  V_SQL VARCHAR2(3000);
BEGIN
  V_SQL := 'select * from scott.tb_sql_patch where object_id=:1';
  EXECUTE IMMEDIATE V_SQL
    USING 10;
END;

--從下面的查詢結果可以看出，兩個繫結變數的資料分佈不同，但SQL只生成了一個執行計劃
select sql_id,plan_hash_value,a.sql_text from v$sql a
where sql_text like 'select * from scott.tb_sql_patch where object_id=:1';
/*
SQL_ID    PLAN_HASH_VALUE    SQL_TEXT    IS_BIND_SENSITIVE    IS_BIND_AWARE
djzfp4cy24dx3    815055989    select * from scott.tb_sql_patch where object_id=:1    N    N    
*/

3.3最後看看使用繫結變數+HINT:BIND_AWARE+SQL PATCH的表現
--執行以下pl/sql,為SQL:djzfp4cy24dx3增加HINT，需要SYS使用者去執行
DECLARE
  V_SQL CLOB;
begin
  --取出原SQL的文字
  SELECT SQL_FULLTEXT INTO V_SQL FROM V$SQL WHERE SQL_ID = 'djzfp4cy24dx3' AND ROWNUM = 1;
  --增加HINT
  sys.dbms_sqldiag_internal.i_create_patch(sql_text  => V_SQL,
                                           hint_text => 'BIND_AWARE',
                                           name      => 'sql_djzfp4cy24dx3');
end;
--執行成功後，可在dba_sql_patches檢視中檢視相關資訊

--dbms_sqldiag_internal.i_create_patch在Oracle中是加密的，解密後內容如下：
PACKAGE dbms_sqldiag_internal
  PROCEDURE I_CREATE_PATCH(
           SQL_TEXT      IN CLOB,
           HINT_TEXT     IN VARCHAR2,
           NAME          IN VARCHAR2 := NULL,
           DESCRIPTION   IN VARCHAR2 := NULL,
           CATEGORY      IN VARCHAR2 := 'DEFAULT',
                   VALIDATE      IN BOOLEAN  := TRUE)
  IS
    RET_NAME  VARCHAR2(30);
    HS        SYS.SQLPROF_ATTR;
  BEGIN
    COMMIT;
    DBMS_SMB.CHECK_SMB_PRIV;
    HS := SYS.SQLPROF_ATTR(HINT_TEXT);
    RET_NAME := DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE(
      SQL_TEXT => SQL_TEXT,
      PROFILE_XML => DBMS_SMB_INTERNAL.VARR_TO_HINTS_XML(HS),
      NAME => NAME,
      DESCRIPTION => DESCRIPTION,
      CATEGORY => CATEGORY,
      CREATOR => SYS_CONTEXT('USERENV', 'SESSION_USER'),
      VALIDATE => VALIDATE,
      TYPE => 'PATCH',
      IS_PATCH => TRUE);
  END;

 
--清空共享池  
alter system flush shared_pool;
--新開會話視窗

--使用繫結變數
--執行下面兩個pl/sql，兩個繫結變數的資料分佈不同
DECLARE
  V_SQL VARCHAR2(3000);
BEGIN
  V_SQL := 'select * from scott.tb_sql_patch where object_id=:1';
  EXECUTE IMMEDIATE V_SQL
    USING 1;
END;


DECLARE
  V_SQL VARCHAR2(3000);
BEGIN
  V_SQL := 'select * from scott.tb_sql_patch where object_id=:1';
  EXECUTE IMMEDIATE V_SQL
    USING 10;
END;

--從下面可以看到，兩個繫結變數的資料分佈不同，SQL生成了兩個不同執行計劃，並且使用了上面新增的SQL PATCH
select sql_id,plan_hash_value,a.sql_text,is_bind_sensitive,is_shareable from v$sql a
where sql_text like 'select * from scott.tb_sql_patch where object_id=:1';
/*
SQL_ID    PLAN_HASH_VALUE    SQL_TEXT    IS_BIND_SENSITIVE    IS_BIND_AWARE    SQL_PATCH
djzfp4cy24dx3    815055989    select * from scott.tb_sql_patch where object_id=:1    Y    Y    sql_djzfp4cy24dx3
djzfp4cy24dx3    57853615    select * from scott.tb_sql_patch where object_id=:1    Y    Y    sql_djzfp4cy24dx3
*/

備註：
1.據說在11.2.0.3上使用此方法由於user_bind_peek_mismatch，執行計劃不能被共享。我的測試資料庫是Oracle 11.2.0.4，沒有出現這個問題。
2.上面的測試中，_optim_peek_user_binds=TRUE，如果_optim_peek_user_binds=FALSE，將dbms_sqldiag_internal.i_create_patch中的hint_text值改為 'OPT_PARAM(''_optim_peek_user_binds'' ''true'') BIND_AWARE'即可。
3.如果不再需要SQL PATCH，可通過dbms_sqldiag.drop_sql_patch刪除。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-2132051/，如需轉載，請註明出處，否則將追究法律責任。

相關文章