Oracle資料傾斜導致的問題-有繫結變數
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"，共同學習，共同成長！！！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2676879/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle資料傾斜導致的問題-無繫結變數Oracle變數
- Oracle面對“資料傾斜列使用繫結變數”場景的解決方案Oracle變數
- 【Spark篇】---Spark解決資料傾斜問題Spark
- 如何解決 Redis 資料傾斜、熱點等問題Redis
- 實戰 | Hive 資料傾斜問題定位排查及解決Hive
- 如何解決Hive中經常出現的資料傾斜問題Hive
- 【ORACLE】Oracle繫結變數知識梳理Oracle變數
- Spark學習——資料傾斜Spark
- 資料傾斜解決辦法
- IoT資料傾斜如何解決
- AdornerDecorator的CacheMode繫結和windows鎖屏導致TableControl鎖死問題Windows
- PostgreSQL DBA(193) - 資料傾斜下的HashJoinSQL
- Redis 切片叢集的資料傾斜分析Redis
- hive優化-資料傾斜優化Hive優化
- Spark 資料傾斜及其解決方案Spark
- hadoop 透過cachefile來避免資料傾斜Hadoop
- 【Hive】資料傾斜優化 shuffle, join, group byHive優化
- Hive千億級資料傾斜解決方案Hive
- 有問題的mybatis的sql導致對資料庫進行了批量的修改MyBatisSQL資料庫
- 傾斜攝影三維模型的立體裁剪的問題分析模型
- oracle 序列值導致的主鍵衝突問題Oracle
- 數倉效能最佳化：傾斜最佳化-表示式計算傾斜的hint最佳化
- jmeter引數化導致反斜槓（\）被轉義JMeter
- 【epoll問題】EPOLLRDHUP使用導致無法接受資料
- 大資料SQL優化之資料傾斜解決案例全集大資料SQL優化
- 一種自平衡解決資料傾斜的分表方法
- 關於 iconv 轉碼導致資料丟失的問題
- 使用資料庫處理併發可能導致的問題資料庫
- Laravel 專案一次釋出導致的 BUG（環境變數問題）Laravel變數
- Oracle中如何查詢未使用繫結變數的SQL語句？Oracle變數SQL
- 硬碟問題導致Gbase資料庫叢集SQL任務執行效率變慢硬碟資料庫SQL
- Spark效能最佳化篇三：資料傾斜調優Spark
- Chrome89針對sessionStorage的更新導致資料共享問題ChromeSession
- 【CURSOR】Oracle繫結變數、執行計劃對遊標的影響Oracle變數
- 總結導致oracle資料庫主機CPU sys%高的一些原因Oracle資料庫
- ANALYZE導致的阻塞問題分析
- 五款傾斜攝影與三維資料處理工具介紹：GISBox、Cesiumlab、OSGBLab、靈易智模、傾斜伴侶
- 什麼是資料洩露?哪些問題可導致資料洩露