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

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

 

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

資料傾斜即表中某個欄位值不均勻，那麼什麼叫欄位值不均勻呢？

如下t1 表的 object_id 欄位值就是嚴重的欄位值不均勻， t1 表有 290020 條資料，其中 object_id 值 1 到 9 每個值只有一條記錄， object_id=10 的值有 290011 條資料。

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

在這種情況下，當以object_id 欄位為過濾條件時，在某些場景下可能會出現效能問題。

場景一：未使用繫結變數  

1 建立測試資料

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;

當資料分佈不均勻的欄位做為過濾條件或連線條件時，如果據分佈不均勻的欄位沒有收集直方圖可能會有問題，在沒有收集直方圖的情況下，這個欄位的過濾性 DENSITY 都是等於 1/NUM_DISTINCT;

2 對測試表 t1 進行統計資訊收集

收集時指定不收集欄位object_id 的直方圖：

begin

  dbms_stats.gather_table_stats ( 'CJC' ,

                                 'T1' ,

                                method_opt =>   'for columns object_id size 1' ,

                                 cascade      =>   true );

end ;

3 檢視 T1 表上 Object_id 列沒有收集直方圖資訊

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

4 以 object_id 列為過濾條件，對比結果集相差懸殊的兩次查詢操作的執行計劃

(1) 檢視結果集少的執行計劃

object_id=1 時結果集只有1 條資料

SQL> set autotrace traceonly

SQL> set linesize 200

SQL> set timing on

SQL> select * from t1 where object_id=1;

(2) 檢視結果集多的執行計劃

object_id=1 0 時結果集有 290011 條資料

SQL> select * from t1 where object_id=10;

290011 rows selected.

從上圖可以看出，兩條 SQL 的 PLAN_HASH_VALUE 是一樣的，走了相同的執行計劃。

select sql_text, sql_id, plan_hash_value

  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  PLAN_HASH_VALUE =   '964845277' ;

顯然在 object_id=1 0 時，結果集有 290011 條資料，佔比總表99.99% 的資料量，是不適合走索引範圍掃描，全表掃描會更高效些。

5  收集 OBJECT_ID 列直方圖資訊

在Oracle 中直方圖是一種對資料分佈質量情況進行描述的工具。

它會按照某一列不同值出現數量多少，以及出現的頻率高低來繪製資料的分佈情況，以便能夠指導優化器根據資料的分佈做出正確的選擇。正確的直方圖，將會對優化器做出正確的選擇發揮巨大的作用，使得SQL 語句執行成本最低，從而提升效能。

-- 下面收集欄位 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';

6 重新執行 SQL ，檢視執行計劃

(1) 結果集少的執行計劃

SQL> select * from t1 where object_id=1;

(2) 結果集多的執行計劃

SQL> select * from t1 where object_id=10;

檢視結果集多的SQL 執行計劃已經發生了變化，執行了更高效的全表掃描。

select sql_text, sql_id, plan_hash_value, address, hash_value

  from v$sql

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

注意：

有幾種情況，在收集直方圖後，執行計劃不會馬上變化

一： SQL 的 CURSOR 沒有失效，不會重新生成執行計劃，可以通過如下幾種方法讓 SQL 的 CURSOR 失效。

(1) 在收集統計資訊時，指定引數 no_invalidate => false , 使這兩條 SQL 的 CURSOR 失效，進行重新解析。

我們通過以下儲存過程將這兩個 CURSOR 清除，這樣再執行就會重新解析了。

--填寫ADDRESS和HASH_VALUE值

BEGIN

  DBMS_SHARED_POOL.PURGE('000000006EBF2F78,589030732', 'C');

  DBMS_SHARED_POOL.PURGE('000000006F2B3660,2332556305', 'C');

END;

(2) 在收集統計時，加 no_invalidate => false 引數

begin

  dbms_stats.gather_table_stats('CJC',

                                'T1',

                                method_opt    => 'for columns object_id size 1',

                                cascade       => true,

                                 no_invalidate => false );

end;

(3) 重新整理整個 share pool( 生產環境謹慎使用 )

alter system flush shared_pool;

(4) 對這個表做 ddl 操作或授權或新增改變註釋等。

例如：

comment on column C JC . T1 . OBJECT _ID is  'PK_T 1 _ OBJECT_ID ';

comment on column C JC . T1 . OBJECT _ID is  '';

二：資料庫 cursor_sharing 引數的值是否為 exact ，如果引數的值為 force ，相當於使用繫結變數，收集直方圖後，執行計劃可能沒有變化，解決辦法請參考下一節 Oracle 資料傾斜導致的問題 - 有繫結變數

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

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