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后推出的SPM 手工绑定执行计划。
一、收集统计信息，从而让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;
2mgbv3kv27j1u122754776 -1 1016
9gwf6964729pb122754776 -1 1565
f5wzbp6ukpgyb122754776 -1 19809
35a1j6rvxxq25122754776 -1 24694
7hqzr0xnw1dzn122754776 -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
dmddfppkdjm3j122754776 30
9qmjbmy368dt6122754776 32
gxt936qxcskus122754776 80
13cyznw9s3k22122754776 82
7yf333kq4tsug122754776 188
8ag1chwapa6g5 1485047805 197
cc22d5nz8us4n 1485047805 281
4ryk8q58djv4k122754776 812
2mgbv3kv27j1u1227547761068
9gwf6964729pb1227547761640
f5wzbp6ukpgyb122754776 20649
35a1j6rvxxq25122754776 26210
7hqzr0xnw1dzn122754776 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_NAMEOBJECT_TYPE
------------------------------ ------------------------------------------
A S_OTHERTABLE 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 ROWID10194
收集统计信息后
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执行效率提升数十倍。 本篇文章只是借此机会讲述方法，真正解决的方法，还是上一篇走索引。