Oracle數(shù)據(jù)傾斜導致的問題-有綁定變量

Oracle 數(shù)據(jù)傾斜導致的問題 - 有綁定變量

參考整理---<< 恩墨年貨   -SQL 與性能優(yōu)化   >>

場景二：

在上一節(jié)實驗可以知道，沒有 綁定變量 時，數(shù)據(jù)傾斜問題在特定場景下可以用直方圖解決，那么在有綁定變量情況下，數(shù)據(jù)傾斜問題單憑直方圖可以解決嗎？

顯然是不能的， Oracle 綁定變量 技術(shù)解決了SQL 語句硬解析過多的問題，降低了資源的爭用。但是綁定變量在引入 cursor sharing ，增加了軟解析的同時， 由于SQL 文本相同，經(jīng)常生成相同的執(zhí)行計劃，在數(shù)據(jù)分布不均勻，數(shù)據(jù)傾斜嚴重時，有時會出現(xiàn)性能問題。

在oracle 9i 版本，引入了 綁定變量窺探Bind Peeking 技術(shù)，在首次硬解析時，會去探測綁定變量的真實值，從而生成更準確的執(zhí)行計劃，但是從第二次軟解析開始，一直會沿用之前的執(zhí)行計劃，而一個執(zhí)行計劃并不會適用所有的綁定值，在過濾列數(shù)據(jù)分布嚴重傾斜時，可能會生成低效的執(zhí)行計劃。

為了彌補綁定變量窺探Bind Peeking 技術(shù)的缺陷， 11g 引入了 自適應(yīng)游標共享技術(shù)(Adaptive Cursor Sharing) ，通過自適應(yīng)游標共享，可以僅針對使用綁定變量的語句智能地共享游標。

一：綁定變量窺探Bind Peeking 對執(zhí)行計劃的影響

二： 自適應(yīng)游標共享技術(shù)(Adaptive Cursor Sharing)

一：綁定變量窺探Bind Peeking 對執(zhí)行計劃的影響

1 查看 Bind Peeking 和 Adaptive Cursor Sharing 參數(shù)

select   name ,   value

   from   ( select  nam.ksppinm   name ,

               val.KSPPSTVL value ,

                --nam.ksppdesc description,

               val.ksppstdf isdefault

           from  sys.x$ksppi nam ,  sys.x$ksppcv val

          where  nam.inst_id =  val.inst_id

            and  nam.indx =  val.indx )

  where   name   in   ( '_optimizer_adaptive_cursor_sharing' ,

                 '_optimizer_extended_cursor_sharing_rel' ,

                 '_optimizer_extended_cursor_sharing' ,

                 '_optim_peek_user_binds' );

2 創(chuàng)建測試數(shù)據(jù)

SQL> select banner_full from v$version;

BANNER_FULL

--------------------------------------------------------------------------------

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME     OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

 2 PDB$SEED     READ ONLY  NO

 3 CJCPDB     READ WRITE NO

SQL> conn cjc/cjc@cjcpdb

Connected

新建測試表 t1 ：

SQL> create table t1 as select * from dba_objects;

創(chuàng)建索引：

SQL> create index idx_t1_01 on t1(object_id);

增加數(shù)據(jù):

SQL> insert into t1 select * from t1;

/

SQL> update t1 set object_id=rownum;

更新數(shù)據(jù), 使用數(shù)據(jù)分布不均勻：

SQL> update t1 set object_id=10 where object_id>10;

290010 rows updated.

SQL> commit;

Commit complete.

SQL> select object_id,count(1) from t1 group by object_id order by 1;

-- 下面收集字段 OBJECT_ID 的直方圖：

SQL>

begin

  dbms_stats.gather_table_stats('CJC',

                        'T1',

                        method_opt => 'for columns object_id size auto',

                        cascade => true);

end;

查看 直方圖 信息 ：

select table_name,

       column_name,

       histogram,

       num_distinct,

       density,

       last_analyzed

  from user_tab_col_statistics

 where table_name = 'T1'

   and column_name = 'OBJECT_ID';

select   *

   from  user_tab_histograms

  where  table_name =   'T1'

    and  column_name =   'OBJECT_ID'

  order   by   5 ;

3 綁定變量窺探對執(zhí)行計劃的影響

硬解析時綁定變量窺探特性可以根據(jù)綁定變量真實值生成高效的執(zhí)行計劃。

SQL> alter system flush shared_pool;

System altered.

SQL> set autotrace traceonly

SQL> set linesize 200

SQL> set timing on

SQL> variable xxx varchar2(100)

SQL> execute :xxx := 1 0 ;

SQL> select * from t1 where object_id=:xxx;

SQL>

select sql_id,

       child_number,

       executions,

       loads,

       buffer_gets,

       is_bind_sensitive as "bind_sensi",

       is_bind_aware     as "bind_aware",

       is_shareable      as "bind_share"

  from v$sql

 where sql_text like 'select * from t1 where object_id%';

select * from table(dbms_xplan.display_cursor('2gr2tazfbjvsa',format => 'advanced'));

第二次執(zhí)行軟解析，綁定變量值換成了1 ，結(jié)果集只有 1 條，但是沿用了之前的執(zhí)行計劃，走全表掃描，顯然是不合理的。

SQL> execute :xxx := 1;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

SQL> select * from t1 where object_id=:xxx;

Elapsed: 00:00:00.05

select sql_id,

       child_number,

       executions,

       loads,

       buffer_gets,

       is_bind_sensitive as "bind_sensi",

       is_bind_aware     as "bind_aware",

       is_shareable      as "bind_share"

  from v$sql

 where sql_text like 'select * from t1 where object_id%';

二： 自適應(yīng)游標共享技術(shù)(Adaptive Cursor Sharing)

在多次執(zhí)行綁定變量等于1 的語句。

SQL> execute :xxx := 1;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

SQL> select * from t1 where object_id=:xxx;

SQL> execute :xxx := 1;

SQL> select * from t1 where object_id=:xxx;

在多次執(zhí)行綁定變量等于10 的語句。

SQL> execute :xxx := 1 0 ;

SQL> select * from t1 where object_id=:xxx;

.......

游標自適應(yīng)生效了

Sql_id 相同，但是 plan_hash_value 不同，表示生成了不同的執(zhí)行計劃

select  sql_id ,

       plan_hash_value ,

       child_number ,

       executions ,

       loads ,

       buffer_gets ,

       is_bind_sensitive as  "bind_sensi" ,

       is_bind_aware      as  "bind_aware" ,

       is_shareable       as  "bind_share"

   from  v$sql

  where  sql_text like   'select * from t1 where object_id%' ;

查看生成的執(zhí)行計劃

SELECT  SQL_ID ,

       PLAN_HASH_VALUE ,

        LPAD ( ' ' ,   4   *   DEPTH )   ||  OPERATION ||  OPTIONS OPERATION ,

       OBJECT_NAME ,

        CARDINALITY ,

       BYTES ,

        COST ,

        TIME

   FROM  V$SQL_PLAN

  where  SQL_ID =   '2gr2tazfbjvsa' ;

select * from v$sql_cs_histogram where sql_id='2gr2tazfbjvsa';

注意：

游標自適應(yīng)有時會導致大量SQL執(zhí)行計劃不穩(wěn)定，在11.2.0.1版本，綁定變量窺探特性可能會導致ORA-03137:TTC protocol internal error:[12333] 問題，有時我們會根據(jù)情況選擇關(guān)閉這些特性。

select   name ,   value ,   description

   from   ( select  nam.ksppinm   name ,

               val.KSPPSTVL value ,

               nam.ksppdesc description ,

               val.ksppstdf isdefault

           from  sys.x$ksppi nam ,  sys.x$ksppcv val

          where  nam.inst_id =  val.inst_id

            and  nam.indx =  val.indx )

  where   name   in   ( '_optimizer_adaptive_cursor_sharing' ,

                 '_optimizer_extended_cursor_sharing_rel' ,

                 '_optimizer_extended_cursor_sharing' ,

                 '_optim_peek_user_binds' );

--均為動態(tài)參數(shù)

--bind peeking（綁定變量窺探

--- alter   system   set  "_optim_peek_user_binds" = false ;

--acs(adaptive cursor sharing)

alter   system   set  "_optimizer_extended_cursor_sharing_rel" = NONE ;

alter   system   set  "_optimizer_extended_cursor_sharing" = NONE ;

alter   system   set  "_optimizer_adaptive_cursor_sharing" = false ;

數(shù)據(jù)庫級別游標自適應(yīng)關(guān)閉后，可以手動開啟語句級別游標自適應(yīng)，方法如下：

---19C測試失敗了，還沒找到具體原因。

# 12.2 之前版本

DECLARE

  V_SQL CLOB;

begin

  --取出原 SQL的文本

  SELECT SQL_FULLTEXT

    INTO V_SQL

    FROM V$SQL

   WHERE SQL_ID = '2gr2tazfbjvsa'

     AND ROWNUM = 1;

  --增加 HINT

  sys.dbms_sqldiag_internal.i_create_patch(sql_text  => V_SQL,

                              hint_text => 'BIND_AWARE',

                              name    => 'sql_2gr2tazfbjvsa');

end;

# 12.2 及以后版本

# 創(chuàng)建 sql patch

declare

  patch_name varchar2(30);

begin

  patch_name := dbms_sqldiag.create_sql_patch(sql_id => '2gr2tazfbjvsa',

                                 hint_text => 'select * from t1 where object_id=:xxx');

end;

/

SQL>

select name,

       to_char(created, 'yyyy-mm-dd hh34:mi:ss') as created,

       status,

       force_matching,

       description,

       substr(sql_text, 1, 50) as sql_text

  from dba_sql_patches

 order by created;

歡迎關(guān)注我的微信公眾號"IT小Chen"，共同學習，共同成長！?。?/span>