OGG複製程式延遲高，優化方法二(存在索引),SQL選擇不好的索引

綠茶有點甜發表於2020-08-28

優化索引SQL

https://www.cnblogs.com/lvcha001/p/13469500.html

接前序，本次場景中有索引，但是OGG複製程式使用了低效率的索引？類似SQL使用低效索引，如何讓Oracle使用好的索引，從而加快複製程式的效率呢？

疑問？ Oracle為什麼有好的索引，但是還是選擇不好的索引，從而造成SQL效率低下，OGG複製程式緩慢呢？

本次DB版本11g,都是CBO，基於成本進行計算。

1.重新收集統計資訊，讓Oracle自動選擇好的索引，走好的執行計劃，從而讓OGG複製程式同步速度加快；

2.可以使用繫結執行計劃，因為生產環境中大表經常收集統計資訊不靠譜，因此繫結執行計劃的選擇性更好。

根據基於Oracle的SQL優化一書中，繫結執行計劃有三種情況：

1.使用SQL Profile手工進行繫結執行計劃；

2.使用Oracle 11g後推出的SQL分析工具後，根據提示繫結執行計劃；

3.使用Oracle 11g後推出的SQM 手工繫結執行計劃。

一、收集統計資訊，從而讓SQL使用好的執行計劃

1.1 OGG程式延遲，追蹤定位慢SQL ,或者說定位OGG複製程式慢在什麼地方。

查詢程式延遲
ogg>info all
REPLICAT    RUNNING     R064    00:14:38      35:23:35    

$ ps -ef|grep R064
oracle   131684  28611  1 Jul31 ?        05:40:59 /ogg/replicat PARAMFILE /ogg/dirprm/r064.prm REPORTFILE /ogg/dirrpt/R064.rpt PROCESSID R064
 USESUBDIRS
$ ps -ef|grep 131684
oracle   131684  28611  1 Jul31 ?        05:40:59 /ogg/replicat PARAMFILE /ogg/dirprm/r064.prm REPORTFILE /ogg/dirrpt/R064.rpt PROCESSID R064
 USESUBDIRS
oracle   131704 131684  9 Jul31 ?        1-22:52:46 oracxxx2 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

select s.sid,s.serial#,sql_id,p.program from v$process p,v$session s where p.addr=s.paddr and p.spid=131704;
       SID    SERIAL# 
---------- ---------- 
      2521        7 
檢查是否存在異常event,阻塞等異常情況,null,基本說明是SQL執行效率問題。

select sql_id,SQL_PLAN_HASH_VALUE,event,BLOCKING_SESSION,CURRENT_OBJ#,count(*) from v$active_session_history where SAMPLE_TIME>sysdate-1 and SESSION_ID=2521 and SESSION_SERIAL#=7
group by sql_id,SQL_PLAN_HASH_VALUE,event,BLOCKING_SESSION,CURRENT_OBJ# order by 6,5;
2mgbv3kv27j1u 122754776 -1 1016
9gwf6964729pb 122754776 -1 1565
f5wzbp6ukpgyb 122754776 -1 19809
35a1j6rvxxq25 122754776 -1 24694
7hqzr0xnw1dzn 122754776 -1 32829

select sql_id,SQL_PLAN_HASH_VALUE,count(*) from v$active_session_history where SAMPLE_TIME>sysdate-1 and SESSION_ID=2521 and SESSION_SERIAL#=7
group by sql_id,SQL_PLAN_HASH_VALUE order by 3,1,2;

SQL_ID SQL_PLAN_HASH_VALUE COUNT(*)
------------- ------------------- ----------
9r3gsjgu643vc 1485047805 1
dhhn34zjdswwm 1485047805 2
51rz0rnxn6h63 1485047805 10
dmddfppkdjm3j 122754776 30
9qmjbmy368dt6 122754776 32
gxt936qxcskus 122754776 80
13cyznw9s3k22 122754776 82
7yf333kq4tsug 122754776 188
8ag1chwapa6g5 1485047805 197
cc22d5nz8us4n 1485047805 281
4ryk8q58djv4k 122754776 812
2mgbv3kv27j1u 122754776 1068
9gwf6964729pb 122754776 1640
f5wzbp6ukpgyb 122754776 20649
35a1j6rvxxq25 122754776 26210
7hqzr0xnw1dzn 122754776 34301 基本上就是這三個SQL導致這個OGG複製程式延遲很高，緩慢的問題。

檢查SQL對應的SQL文字物件，及執行計劃

select * from table(dbms_xplan.display_cursor('7hqzr0xnw1dzn'));
Plan hash value: 122754776
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 4 (100)| | | |
| 1 | DELETE | S_OTHER | | | | | | |
|* 2 | COUNT STOPKEY | | | | | | | |
| 3 | PARTITION RANGE SINGLE | | 1 | 150 | 4 (0)| 00:00:01 | KEY | KEY |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| S_OTHER | 1 | 150 | 4 (0)| 00:00:01 | KEY | KEY |
|* 5 | INDEX RANGE SCAN | IDX_S_OTHER_DATE | 1 | | 3 (0)| 00:00:01 | KEY | KEY |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
4 - filter(("TIME" IS NULL AND "TIME1"=:B1 AND "DATA1"=:B38 and ······
5 - access("STAT_DATE"=:B0 AND "STATTYPE"=:B5)

三條SQL涉及的物件一致，只是update or delete 少量差異，基本上都是一樣的。可以發現SQL已經走了索引

1.2 檢查SQL統計資訊，物件涉及的索引及索引列的選擇性。

--最消耗時間的執行計劃步驟
select 
inst_id,sql_plan_hash_value,sql_plan_line_id,
sql_plan_operation,sql_plan_options,event,
count(*) cnt
 from gv$active_session_history 
where sql_id='7hqzr0xnw1dzn' and 
sample_time >sysdate-2/24
  group by 
inst_id,sql_plan_hash_value,sql_plan_line_id,
sql_plan_operation,sql_plan_options,event
 order by count(*) ;
   INST_ID SQL_PLAN_HASH_VALUE SQL_PLAN_LINE_ID SQL_PLAN_OPERATION           SQL_PLAN_OPTIONS           EVENT              CNT
---------- ------------------- ---------------- ------------------------------ ------------------------------ -------------------- ----------
     2         122754776              5 INDEX                                    RANGE SCAN                         4013
     2         122754776              4 TABLE ACCESS                      BY LOCAL INDEX ROWID                    10194
慢在回表，說明SQL通過索引獲取到非常多的ROWID,但是我們都知道SQL 文字最後有ROWNUM=1，因此可以說明SQL實際涉及一行記錄，但是SQL通過索引訪問獲取到N條>>1條記錄，索引選擇性很差。

select owner,object_name,object_type from dba_objects where object_name='S_OTHER';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------------------
A S_OTHER TABLE PARTITION

select index_owner,index_name,table_name,column_name,column_position from dba_ind_columns where table_name='S_OTHER' order by index_name,column_position;

INDEX_OWNER INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ----------- -------
REP IDX_S_OTHER_DATE A_DATE 1
REP IDX_S_OTHER_DATE STYPE 2
REP IDX_S_OTHER_DATE S_DATE 1
REP IDX_S_OTHER_DATE STYPE1 2

可以發現，SQL選擇的執行計劃索引，對應有4個列

另一個索引是全列索引！！！ 25個列,雖然有點。。。。不靠譜，但是根據rowunum=1的方式，我們可以認為這個索引等同於一個IOT表。因此實際的訪問效率肯定是> 上面的慢索引。

select OWNER,TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,NUM_NULLS,to_char(LAST_ANALYZED,'yyyy-mm-dd hh24') as "date" from dba_tab_col_statistics where table_name='S_ST_BUSI_OTHER' order by column_name;

可以發現統計資訊都是19年2月份，慢索引的四個列，NUM_DISTINCT分別為 1200,700,2,3 因此組合索引效率並不高！！！

select segment_name,sum(bytes)/1024/1024/1024 from dba_segments where owner='REP' and segment_name='S_OTHER' group by segment_name;
SEGMENT_NAME SUM(BYTES)/1024/1024/1024
--------------------------------------------------------------------------------- -------------------------
S_OTHER 15.5256958

1.3 收集統計資訊，觀察執行計劃

exec dbms_stats.gather_table_stats(ownname=>'R',tabname=>'S_OTHER',cascade=>true,degree=>6,estimate_percent=>60);
檢查執行計劃是否改變！！！   這裡有個小細節，如果SQL執行效率很高，非常可能無法在cursor觀察到。

select * from table(dbms_xplan.display_cursor('35a1j6rvxxq25'));

本次未看到執行計劃改變！！！？？？？沒效果？？？

通過ASH檢視查詢 SQL_ID 的執行計劃。

--最消耗時間的執行計劃步驟
select
inst_id,sql_plan_hash_value,sql_plan_line_id,
sql_plan_operation,sql_plan_options,event,
count(*) cnt
from gv$active_session_history
where sql_id='7hqzr0xnw1dzn' and
sample_time >sysdate-2/24
group by
inst_id,sql_plan_hash_value,sql_plan_line_id,
sql_plan_operation,sql_plan_options,event
order by count(*) ;

INST_ID SQL_PLAN_HASH_VALUE SQL_PLAN_LINE_ID SQL_PLAN_OPERATION SQL_PLAN_OPTIONS EVENT CNT
---------- ------------------- ---------------- ------------------------------ ------------------------------ -------------------- ----------
2 122754776 5 INDEX RANGE SCAN 4013
2 122754776 4 TABLE ACCESS BY LOCAL INDEX ROWID 10194

收集統計資訊後

INST_ID SQL_PLAN_HASH_VALUE SQL_PLAN_LINE_ID SQL_PLAN_OPERATION SQL_PLAN_OPTIONS EVENT CNT
---------- ------------------- ---------------- ------------------------------ -------------

2 122754776 1 DELETE log file sync 4
2 122754776 0 DELETE STATEMENT 5
2 11252613 1 DELETE 6 執行計劃發生改變，並且cnt數量很低，說明效率很OK
2 122754776 1 DELETE 14
2 122754776 5 INDEX RANGE SCAN 3148
2 122754776 4 TABLE ACCESS BY LOCAL INDEX ROWID 8293

使用SQL指令碼對比SQL執行效率，提升了4倍的訪問效率。

二、繫結執行計劃，從而讓SQL使用好的執行計劃

2.1 定位問題SQL，與上面套路一樣

select sql_id,SQL_PLAN_HASH_VALUE,event,BLOCKING_SESSION,CURRENT_OBJ#,count(*) from v$active_session_history where SAMPLE_TIME>sysdate-1 
and SESSION_ID=2072  and SESSION_SERIAL#=19023
 group by sql_id,SQL_PLAN_HASH_VALUE,event,BLOCKING_SESSION,CURRENT_OBJ# order by 6,5;
1wcqwzmn1mw6b           3794392338
觀察執行計劃

走  TABLE ACCESS BY LOCAL INDEX ROWID| S_DAY 索引。

與上面的例子類似，唯一不同的就是換了個使用者，換了一個表名稱。

2.2 繫結執行計劃

1.獲得好的執行計劃
由於SQL只有一個執行計劃，並沒有我們希望的走全列索引的執行計劃。
因此第一步驟需要製造一個好的執行計劃，可以使用explan 
EXPLAIN PLAN FOR SELECT /* test_sql_20200828 */ * FROM T_USER where id= status=  ......選擇了4個好的列，及distinct較多的列，作為where條件  and rownum=1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
可以得到希望得到的執行計劃。

2.查詢好的執行計劃 sql_id, hash_plan
可以通過v$sql

SQL_ID SQL_TEXT PLAN_HASH_VALUE  定位獲得需要的資訊。


這裡有一個疑問？？？
假設我們explain plan for 的SQL條件是WHERE 4個列？
那麼繫結執行計劃後，走全列索引20個列，那麼繫結後的執行計劃中access 是4個列？filter 是其它條件20個列？    還是 access 是索引20個列，filter 是表中不包含索引列的資訊。

這個疑問代表如果假設Oracle繫結後，access 選擇4個列，說明Oracle是一個錯誤的執行計劃，那樣filter 需要排除16個列，甚至20個列,SQL效率低下；
如果假設access 選擇4個列，filter rownum=1 沒有其它條件，嚴格與繫結的模板或者說好的執行計劃步驟走，甚至得到的結果都可以理解為錯誤的？？？ 條件變少了！！！
有興趣的朋友可以測試下，不在囉嗦。  測試結果為access索引全列+ filter rownum=1，因此oracle 還是很聰明的。

2.3 固定執行計劃

--繫結執行計劃

declare
  m_clob clob;
begin
  select sql_fullteXt
    into m_clob
    from v$sql
   where sql_id = '6up8w69qu5y98'      --慢SQL需要優化的SQL_id
     and child_number = 0;
  dbms_output.put_line(m_clob);
  dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id          => '6up8w69qu5666',   --選擇好的執行計劃對應的SQL_ID,可以不一樣
                                                             plan_hash_value => 2061801194,        --選擇好的執行計劃對應的plan_value
                                                             sql_text        => m_clob,
                                                             fixed           => 'YES',
                                                             enabled         => 'YES'));

end;
/

==如下可以查詢繫結執行計劃後的資訊，如果有問題可以參考如下刪除。
SELECT SQL_HANDLE,PLAN_NAME,ORIGIN FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%WF_H_WorkItem%';
SQL_HANDLE              PLAN_NAME                            ORIGIN
------------------------------------------------------------ 
SQL_6e591e5940320852    SQL_PLAN_6wq8yb503422k0e58574a       MANUAL-LOAD
VAR TEMP NUMBER;
BEGIN 
 :TEMP:=DBMS_SPM.DROP_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_6e591e5940320852',PLAN_NAME=>NULL);
 END;
 /
SQL> SELECT SQL_HANDLE,PLAN_NAME,ORIGIN FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%WF_H_WorkItem%';
no rows selected

2.4 如何讓SQL重新硬解析我們經常可以發現，繫結執行計劃或者收集統計資訊後。 SQL執行計劃不變？？？

1.Oracle預設收集統計資訊，是oracle自行判斷，什麼時候讓SQL原有的執行計劃失效，從而後續涉及的物件下一次SQL執行硬解析;
2.那我們如何手工讓SQL 硬解析呢？？？

前提條件，執行慢的SQL需要暫停執行，我們能改變的是新的SQL，而非現有正在執行慢的SQL；
OGG需要stop 涉及的程式。

1.對物件進行DDL操作
舉例說明
alter table a modify status varchar2(13); 表欄位長度更新（慎重）
表別名，列別名修改。 可以使用plsql修改建議

-- 設定表別名
COMMENT ON TABLE EMPLOYEE is '僱員';
--設定欄位別名
COMMENT ON COLUMN PRODUCT.PRODUCT_CLASS_ID IS '產品分類程式碼';
參考http://blog.itpub.net/106943/viewspace-1005783/

別名修改，比較好用，plsql改下就行，業務高峰期不要操作。

2.清空sql涉及的共享池

參考
https://zm.sm-tc.cn/?src=l4uLj4zF0NCIiIjRk5aRioeWm5zRnJCS0LOWkYqH0M3PzsjSz8zQzsvOysbH0ZeLkg%3D%3D&uid=06b807ecd1b8ae1f4a368a691fabd3eb&hid=
305572c2dd05de2fc54a7211940a4021&pos=1&cid=9&time=1597929982090&from=click&restype=1&pagetype=0000804000000402&bu=ss_doc&query=Oracle%E6%B8%85%E7
%A9%BAsql+%E5%AF%B9%E5%BA%94%E7%9A%84shared+pool.%E5%9C%B0%E5%9D%80&mode=&v=1&province=%E5%A4%A9%E6%B4%A5%E5%B8%82&city=%E5%A4%A9%E6%B4%A5%E5%B8%8
2&uc_param_str=dnntnwvepffrgibijbprsvdsdichei

 zx@MYDB>select sql_text,sql_id,version_count,executions,OBJECT_STATUS,address,hash_value from v$sqlarea where sql_text like 'select object_name 
from s%';
      SQL_TEXT                            SQL_ID                    VERSION_COUNT EXECUTIONS OBJECT_STATUS  ADDRESS        HASH_VALUE
      ------------------------------------------------------------ --------------------------------------- ------------- ---------- ---------------

      select object_name from s1 where object_id=20          1s45nwjtws2tj                  1    1 VALID          00000000B4F85A18 1942752049
      select object_name from s1 where object_id=30          1hdyqyxhtavqs                  1    1 VALID          00000000BE7E56C8 1637183192

      現在要刪除object_id=20對應的SQL快取的執行計劃和解析樹。

      zx@MYDB>exec sys.dbms_shared_pool.purge('00000000B4F85A18,1942752049','C');
      PL/SQL procedure successfully completed.
      zx@MYDB>select sql_text,sql_id,version_count,executions,OBJECT_STATUS,address,hash_value from v$sqlarea where sql_text 
like 'select object_name from s%';
      SQL_TEXT                            SQL_ID                    VERSION_COUNT EXECUTIONS OBJECT_STATUS  ADDRESS        HASH_VALUE
      ------------------------------------------------------------ --------------------------------------- ------------- ---------- ------
      select object_name from s1 where object_id=30          1hdyqyxhtavqs                  1    1 VALID          00000000BE7E56C8 1637183192

      從輸出可以看出object_id=20對應的SQL快取的執行計劃和解析樹被刪除了，而object_id=30對應的SQL的執行計劃沒有受影響。

      需要注意的是，如果在10.2.0.4中使用dbms_shared_pool.purge，則在使用之前必須特工設定event 5614566(alter session set events '5614566 trace 
name context forever')，否則dbms_shared_pool.purge將不起作用，這個限制在10.2.0.4以上的版本中已經不存在了。
如果預設沒有安裝dbms_shared_pool包的可以執行@?/rdbms/admin/dbmspool.sql

3.收集統計資訊的時候使用引數
exec dbms_stats.gather_index_stats(ownname=>'R',indname=>'IDX_DATE',degree=>6,estimate_percent=>60,no_invalidate=>false);
由於收集表及索引統計資訊時間太長，因此可以選擇收集你想要的的索引單個統計資訊。使用引數也是可以的。

如果只是為了加快時間，可以考慮，estimate_percent 取樣比例使用0.1 之類很小的值，應該是秒級別。   目的可以快速讓物件涉及的SQL都硬解析，但是不好的地方在於，
oracle收集統計資訊後會更新物件統計資訊值，使用過低的比例，值不準確，可能影響其他的SQL。  但是如果SQL基本都是繫結執行計劃可以忽略。

最後：可能比較厲害或者比較會玩的朋友會想到，為什麼全列索引呢？？？有點傻。

確實我們也想到了，在上述一堆的操作之後，我們去彙總，檢查發現SQL執行效率才提升4倍左右，但是SQL執行效率還是不是非常高。

這個表都是15g左右，沒有主鍵列。並且！！！沒有選擇性非常好的列。因此我們在評估 4個選擇性不好列的索引，全列選擇性好但是體量太大的索引的情況下。

進行了均衡發展，我們選擇了6個選擇性distinct最高的值，建立了一個符合索引。刪除了全列索引，SQL執行效率提升數十倍。本篇文章只是藉此機會講述方法，真正解決的方法，還是上一篇走索引。

OGG複製程式延遲高，優化方法一(使用索引)
2020-08-10
優化索引
SQL優化（二）（聯合索引的使用）
2012-12-18
SQL優化索引
SQL優化-索引
2013-11-15
SQL優化索引
SQL Server索引優化系列之二：索引效能考慮 (轉)
2010-09-29
SQLServer索引優化
SQL優化案例-正確的使用索引（二）
2018-06-15
SQL優化索引
SUM優化（複合索引）
2014-08-25
優化索引
PostgreSQL中的複製延遲
2023-04-20
SQL
增加複合索引優化SQL的簡單過程
2008-04-29
索引優化SQL
聊聊索引和SQL優化
2011-02-03
索引SQL優化
SQL優化--函式索引
2012-01-19
SQL優化函式索引
MySQL Slave延遲很大優化方法
2016-12-23
MySql優化
MySQL學習之影響優化器選擇索引因素
2021-03-10
MySql優化索引
MySQL 8 複製（三）——延遲複製與部分複製
2020-02-12
MySql
mysql主從延遲複製
2019-01-29
MySql
MySQL 5.7 延遲複製配置
2016-05-04
MySql
複合索引與函式索引優化一例
2014-08-25
索引函式優化
sql優化之多列索引的使用
2011-09-04
SQL優化索引
理解索引：索引優化
2018-06-04
索引優化
又一個複合索引的SQL調優
2008-05-05
索引SQL
一個複合索引的優化案例
2016-04-15
索引優化
MySQL字首索引和索引選擇性
2017-09-07
MySql索引
SQL優化之利用索引排序
2019-07-10
SQL優化索引排序
索引的選擇原則
2016-08-19
索引
SQL優化案例一則--複合索引沒有被使用
2014-02-11
SQL優化索引
SQL優化案例-分割槽索引之無字首索引（六）
2018-08-21
SQL優化索引
SQLT 最佳化SQL 用複合索引代替單列索引的案例
2014-06-06
SQL索引
MySQL查詢索引的選擇性、索引欄位、註釋等基本資訊的SQL
2016-05-24
MySql索引
MySQL 查詢索引的選擇性、索引欄位、註釋等基本資訊的SQL
2015-10-30
MySql索引
MySQL SQL 優化之覆蓋索引
2017-11-29
MySql優化索引
高吞吐低延遲Java應用的垃圾回收優化
2018-10-25
Java優化
ORACLE 9i資料庫優化案例(2) --- 單列索引變複合索引的優化
2010-11-30
Oracle資料庫優化索引
MySQL進階【五】—— MySQL查詢優化器是如何選擇索引的
2020-09-27
MySql優化索引
分割槽表全域性索引與本地索引的選擇
2016-05-04
索引
Mysql索引優化之索引的分類
2019-07-04
MySql索引優化
Elasticsearch 中為什麼選擇倒排索引而不選擇 B 樹索引
2021-10-26
Elasticsearch索引
[zt] 基於索引的SQL語句優化
2009-05-15
索引SQL優化
從效能的角度談SQL Server聚集索引鍵的選擇
2015-12-21
SQLServer索引
Sql Server實時監控釋出訂閱複製Replication、subscription有多少延遲的方法
2022-01-06
SQLServer