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執行效率提升數十倍。 本篇文章只是藉此機會講述方法，真正解決的方法，還是上一篇走索引。