Oracle 資料傾斜導致的問題 - 有繫結變數

參考整理---<< 恩墨年貨 -SQL 與效能最佳化 >>

場景二：

在上一節實驗可以知道，沒有 繫結變數 時，資料傾斜問題在特定場景下可以用直方圖解決，那麼在有繫結變數情況下，資料傾斜問題單憑直方圖可以解決嗎？

顯然是不能的， Oracle 繫結變數 技術解決了SQL 語句硬解析過多的問題，降低了資源的爭用。但是繫結變數在引入 cursor sharing ，增加了軟解析的同時， 由於SQL 文字相同，經常生成相同的執行計劃，在資料分佈不均勻，資料傾斜嚴重時，有時會出現效能問題。

在oracle 9i 版本，引入了 繫結變數窺探Bind Peeking 技術，在首次硬解析時，會去探測繫結變數的真實值，從而生成更準確的執行計劃，但是從第二次軟解析開始，一直會沿用之前的執行計劃，而一個執行計劃並不會適用所有的繫結值，在過濾列資料分佈嚴重傾斜時，可能會生成低效的執行計劃。

為了彌補繫結變數窺探Bind Peeking 技術的缺陷， 11g 引入了 自適應遊標共享技術(Adaptive Cursor Sharing) ，透過自適應遊標共享，可以僅針對使用繫結變數的語句智慧地共享遊標。

一：繫結變數窺探Bind Peeking 對執行計劃的影響

二： 自適應遊標共享技術(Adaptive Cursor Sharing)

一：繫結變數窺探Bind Peeking 對執行計劃的影響

1 檢視 Bind Peeking 和 Adaptive Cursor Sharing 引數

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 建立測試資料

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;

建立索引：

SQL> create index idx_t1_01 on t1(object_id);

增加資料:

SQL> insert into t1 select * from t1;

/

SQL> update t1 set object_id=rownum;

更新資料, 使用資料分佈不均勻：

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 繫結變數窺探對執行計劃的影響

硬解析時繫結變數窺探特性可以根據繫結變數真實值生成高效的執行計劃。

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'));

第二次執行軟解析，繫結變數值換成了1 ，結果集只有 1 條，但是沿用了之前的執行計劃，走全表掃描，顯然是不合理的。

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%';

二： 自適應遊標共享技術(Adaptive Cursor Sharing)

在多次執行繫結變數等於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;

在多次執行繫結變數等於10 的語句。

SQL> execute :xxx := 1 0 ;

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

.......

遊標自適應生效了

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

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%' ;

檢視生成的執行計劃

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';

注意：

遊標自適應有時會導致大量SQL執行計劃不穩定，在11.2.0.1版本，繫結變數窺探特性可能會導致ORA-03137:TTC protocol internal error:[12333] 問題，有時我們會根據情況選擇關閉這些特性。

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' );

--均為動態引數

--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 ;

資料庫級別遊標自適應關閉後，可以手動開啟語句級別遊標自適應，方法如下：

---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 及以後版本

# 建立 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 hh24:mi:ss') as created,

status,

force_matching,

description,

substr(sql_text, 1, 50) as sql_text

from dba_sql_patches

order by created;

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

Oracle資料傾斜導致的問題-有繫結變數

Oracle資料傾斜導致的問題-有繫結變數

相關文章