HASH_VALUE CHILD
---------- ----------
outline/plan_hash_value
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Ex DISK_READS bg bg/exec rows LOAD_TIME
---------- ---------- --------------- ------------ ---------- -----------
2162723433 1
2854600501
3635 295 4046953316 1113329.66 880 06-14/10:25
TOTAL
3635 295 4046953316 1113023.46 880
执行了3635次，返回880行，平均返回0.24行。平均每次执行逻辑读1113023.46（每个逻辑读是访问oracle内存块一次）
SQL没有group by聚合操作，基本说明了有很大优化空间
SQL如下，删掉select子句部分
from gpcs_placepoint f,
ZX_GZYB_drugcodg_v b,
bms_lot_def c,
bms_st_io_dtl d,
bms_st_io_doc a,
ngpcs_all_price e
where a.goodsid = b.goodsid
and c.lotid = d.lotid
and a.inoutid = d.inoutid
and a.storageid = f.storageid
and a.comefrom = 77
and f.placepointid = e.placepointid
and a.goodsid = e.goodsid
and e.priceid = 2
and f.placepointid= :1
and a.keepdate between to_date(:2 , 'yyyy-MM-dd') and to_date(:3 , 'yyyy-MM-dd')
碰到多表JOIN的时候一般分析思路：
0 表基本大小信息，sql实现功能，确认表上关联条件有索引（1对多的关联，多的表的关联字段一定要有索引）
1 确认有效的过滤条件
如上SQL有效的过滤条件可能是：
3 确认最先的驱动表
在这里，F表的过滤条件强，是等值查询，F作为驱动表。
4 确认过滤条件尽早能用到，尽早被关联，特别是过滤性强的条件
如f表、a表，其次是e表；其它基本没过滤条件
5 检查当前执行计划
最先驱动表问题，是gpcs_placepoint ，唯一索引扫描，最多返回一条记录，很好。
然后关联bms_st_io_doc ，选择这个表是很正确的选择，因为有一个较强的过滤条件在这个表上。
但问题出在这14步，是按时间字段取数据，实际上并没有利用到驱动表的storageid这个条件，这个条件是在13步中作为filter出现的
为什么会这样呢？
看下bms_st_io_doc 的索引信息
有一个索引（keepdate，goodid）和（storageid）
Oracle在评估这2个索引的选择中选择了索引（keepdate，goodid），通过keepdate来access数据。
如果选择（storageid）索引，单storageid大约记录数是390075135/9276=42052,远比执行计划中的通过keepdate获取的记录多。
执行计划中通过keepdate索引获取的记录是36行，这个肯定是有问题的。实际返回行数肯定比这个高很多。
问什么会这样呢？
主要是Oracle会采用绑定变量窥视功能，加上统计信息旧导致的（统计信息是6月10号收集的）
5 到此怎么优化呢？
加一个bms_st_io_doc （storageid，keepdate）的复合索引，对这条SQL的优化就OK了。
这个例子中有storageid的单列索引；加了（storageid，keepdate），基本可以把这个单列索引删除。
这个表很大，建索引有一定风险。 ！！！这么大的表，不能简单通过提DMS工单执行，需要评估对系统影响，由DBA操作处理。
非紧急情况下，建索引需要空闲时间段完成；需要考虑建索引对主库、从库的影响及可能的执行计划变化。
建索引后观察系统性能变化你SQL执行计划变化，确认无误后，设置单列索引为invisible（优化器不可见），观察几天没问题后再删除。
6 另外对于统计信息旧的问题，特别有时间字段索引的表（或伪时间字段），建议每天收集；分区表采用增量收集模式，非分区表，表太大的可以指定采样比例，如5000万记录，采样2%可以满足要求（分析1百万记录）。
基本结论：
Oracle数据库sharepool竞争导致新建连接堵塞，最终导致lsnrctl crash；lsnrctl crash后由于没有新连接进来，从而sharedpool竞争缓解从而数据库恢复（但监听进程没了）；
sharedpool竞争主要有一下几个方面因素：
sharedpool过大，大小发生过动态调整
系统free内存少，需要分配内存时慢，容易竞争
因没有绑定变量，使用cursor_sharing=force,这个容易引发问题
有些些很大的in-list SQL，消耗大量内存
POS从库开启了AWR，AWR是每秒对系统相关视图做快照，动过dblink保存在主库，容易引发竞争（基本上出现竞争时，都出现AWR快照任务）
处理办法：
0 修改oracle参数配置，使用directio或setall（directio+async），减少大量pagecache的换进换出
1 减少sharedpool大小，sharedpool过大造成管理成本高，在大量存储分配情况下出现卡顿 ，固定sharedpool大小。 以上修改方法见链接：POS从库优化调优变更
2 listener打开日志，这个增加开销不大，但可以记录各个应用logon的情况；按需使用crontab定期清理日志即可
3 系统数据库负载均衡，建议分配2台物理机，安装LVS+keeplived；或商业负载均衡
3 应用检查连接配置，在数据库故障情况下确认能自动重连。连接池最大最小连接一样，不要动态扩容收缩。这次出现需要应用重启的情况。考虑部署应用守护脚本，必要时自动重启应用。
4 关闭pos从库的awr
5 绑定变量没减少hardparse。hardparse严重影响系统性能和稳定性，不建议在重要系统采用cursor_sharing=force
6 巨大的inlist SQL需要调整，一条SQL消耗数百M内存，严重影响系统稳定性，调整办法：SQL in-list绑定变量，避免硬拼接
故障描述:
03-11月-22 06.30开始大量library cache pin等待，当时主要问题包是：CMX_POS2MOM04_SQL.POS2MOM04
这个包依赖CMX_FILTER_LOV_VALIDATE_SQL-》继续依赖SQL_LIB
SQL_LIB依赖DYNAMIC_HIER_CODE_SQL，而DYNAMIC_HIER_CODE_SQL有依赖SQL_LIB，从而出现互相包死锁堵塞情况
CMX_FILTER_LOV_VALIDATE_SQL发包编译时间是17:43-44，但包头最后DDL时间是：18:30；包体是19:07.肯定存在再次失效和编译过程。这个过程中系统发生堵塞。
最终ORACLE也检测到了包死锁情况发生。
批量kill堵塞连接，CMX_FILTER_LOV_VALIDATE_SQL、SQL_LIB等包valid后系统恢复正常。最终恢复时间是19:16
备注：开发18:30分开发执行过alter table RMS.code_detail modify CODE_DESC varchar2(150)。直接导致问题发生。
整改措施：
0 严禁不按规则不走流程执行DML、DDL
1 包头没有改动，严禁发布包头。
oracle package包头发布，会失效依赖它的对象；引起大量对象失效，oracle在使用这些对象时会自动编译（ADG库执行不会编译）
而只发布包体，如果发布的包体是valid，不会失效依赖它的对象
2 发布包体的时候，一定要开发确认有效正确，如果包体invalid，依赖它的对象会全部失效。由此带来问题应由开发负责。
3 发布包的时候要检查数据库'library cache%'等待，有问题要及时处理；要检查oracle running进程即可得知。
4 禁止包循环依赖
5 发布包体的时候，如果执行卡顿，说明此包被使用，务必及时终止发布
6 发布包头包体的时候，首先确认是否需要发布包头。要检查包的依赖关系，依赖的包是否在运行等。检查确认变更先后失效对象信息。
7 MOM库是一个非常繁忙的库，要有敬畏之心，处理前后都要检查系统是否有异常。繁忙的时候减少发关键的运行频繁的依赖多的包。
1 现状描述：
MOM数据库2023年3月14号中午1点发生row cache lock堵塞，导致rms用户登陆异常；已登陆的rms用户操作表的时候，也出现row cache lock等待
通过sqlplus登陆数据库，查到blocker进程是3639，在执行grant命令，也被row cache lock堵住，但没显示blocking session信息。
SAMPLE_ID : 156231324
SAMPLE_TIME : 14-MAR-23 01.00.04.865 PM
SAMPLE_TIME_UTC : 14-MAR-23 05.00.04.000 AM
USECS_PER_ROW : 1019634
IS_AWR_SAMPLE : N
SESSION_ID : 3639
SESSION_SERIAL# : 64743
SESSION_TYPE : FOREGROUND
FLAGS : 16
USER_ID : 2573
SQL_ID : cbd3ysw9c5tsa
IS_SQLID_CURRENT : Y
SQL_CHILD_NUMBER : 0
SQL_OPCODE : 17
SQL_OPNAME : GRANT OBJECT
FORCE_MATCHING_SIGNATURE : 0
TOP_LEVEL_SQL_ID : 7zy99xcu4rn4a
TOP_LEVEL_SQL_OPCODE : 47
SQL_ADAPTIVE_PLAN_RESOLVED : 1
SQL_FULL_PLAN_HASH_VALUE : 0
SQL_PLAN_HASH_VALUE : 0
SQL_PLAN_LINE_ID :
SQL_PLAN_OPERATION :
SQL_PLAN_OPTIONS :
SQL_EXEC_ID : 16777216
SQL_EXEC_START : 14-mar-2023 13:00:04
PLSQL_ENTRY_OBJECT_ID : 7585627
PLSQL_ENTRY_SUBPROGRAM_ID : 1
PLSQL_OBJECT_ID :
PLSQL_SUBPROGRAM_ID :
QC_INSTANCE_ID :
QC_SESSION_ID :
QC_SESSION_SERIAL# :
PX_FLAGS :
EVENT : row cache lock
EVENT_ID : 1714089451
EVENT# : 354
SEQ# : 411
P1TEXT : cache id
P1 : 10
P2TEXT : mode
P2 : 0
P3TEXT : request
P3 : 5
WAIT_CLASS : Concurrency
WAIT_CLASS_ID : 3875070507
WAIT_TIME : 0
SESSION_STATE : WAITING
TIME_WAITED : 0
BLOCKING_SESSION_STATUS : GLOBAL
BLOCKING_SESSION :
BLOCKING_SESSION_SERIAL# :
BLOCKING_INST_ID :
BLOCKING_HANGCHAIN_INFO :
CURRENT_OBJ# : 7782616
CURRENT_FILE# : 8
CURRENT_BLOCK# : 26536473
CURRENT_ROW# : 0
TOP_LEVEL_CALL# : 59
TOP_LEVEL_CALL_NAME : VERSION2
CONSUMER_GROUP_ID : 12890
XID : 2B0010007AA71200
REMOTE_INSTANCE# :
TIME_MODEL : 1024
IN_CONNECTION_MGMT : N
IN_PARSE : N
IN_HARD_PARSE : N
IN_SQL_EXECUTION : Y
IN_PLSQL_EXECUTION : N
IN_PLSQL_RPC : N
IN_PLSQL_COMPILATION : N
IN_JAVA_EXECUTION : N
IN_BIND : N
IN_CURSOR_CLOSE : N
IN_SEQUENCE_LOAD : N
IN_INMEMORY_QUERY : N
IN_INMEMORY_POPULATE : N
IN_INMEMORY_PREPOPULATE : N
IN_INMEMORY_REPOPULATE : N
IN_INMEMORY_TREPOPULATE : N
IN_TABLESPACE_ENCRYPTION : N
CAPTURE_OVERHEAD : N
REPLAY_OVERHEAD : N
IS_CAPTURED : N
IS_REPLAYED : N
IS_REPLAY_SYNC_TOKEN_HOLDER : N
SERVICE_HASH : 1635281341
PROGRAM : sqlplus@rmsap1 (TNS V1-V3)
MODULE : SQL*Plus
ACTION :
CLIENT_ID :
MACHINE : rmsap1
PORT : 34170
ECID :
DBREPLAY_FILE_ID : 0
DBREPLAY_CALL_COUNTER : 0
TM_DELTA_TIME : 41655
TM_DELTA_CPU_TIME : 65185
TM_DELTA_DB_TIME : 71366
DELTA_TIME : 1054239
DELTA_READ_IO_REQUESTS : 20
DELTA_WRITE_IO_REQUESTS : 0
DELTA_READ_IO_BYTES : 335872
DELTA_WRITE_IO_BYTES : 0
DELTA_INTERCONNECT_IO_BYTES : 335872
DELTA_READ_MEM_BYTES : 367968256
PGA_ALLOCATED : 4975616
TEMP_SPACE_ALLOCATED : 0
CON_DBID : 1849138624
CON_ID : 0
DBOP_NAME :
DBOP_EXEC_ID : 0
-----------------
2 处理过程
查到top_level_sql_id,确认在存储过程中执行grant 。。。 to rms操作。
SQL> select sql_text from gv$sql where sql_id='7zy99xcu4rn4a';
DECLARE L_str_error_tst VARCHAR2(1) := NULL; FUNCTION_ERROR EXCEPTION; L_return_code varchar2(10); BEGIN CMX_CREATE_USER_SQL; EXCEPTION when FUNCTION_ERROR then ROLLBACK; :GV_return_code := 255; when OTHERS then ROLLBACK; :G
V_script_error := SQLERRM; :GV_return_code := 255; END;
Finding Blockers:
Holders and requesters can be seen in view X$KQRFP for parent objects, and X$KQRFS for subordinates.
eg: The following select will show all holders of parent row cache objects so can be used to help find the blocking session.
SELECT * FROM x$kqrfp WHERE kqrfpmod!=0;
(KQRFPSES is the address of the holding session V$SESSION.SADDR)
确认row cache lock的类型是dc_users
SQL> select parameter,gets,getmisses,MODIFICATIONS from v$rowcache where cache#=10;
PARAMETER GETS GETMISSES MODIFICATIONS
-------------------------------- ---------- ---------- -------------
dc_users 2672132800 20260 1739
当时在实例1上查这个进程的SPID，发现挂死不能执行。改在实例2上查找：
select spid from gv$process where addr in (select paddr from gv$session where sid=3639 and serial#=64743)
再在实例上kill这个进程，系统恢复正常。
3 事后分析：
堵塞的存储过程：
PROCEDURE CMX_CREATE_USER_SQL AUTHID CURRENT_USER IS
G_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'CMX_CREATE_USER_SQL';
V_SQL VARCHAR2(2000);
ERROR VARCHAR2(1000);
V_BREAKPOINT INTEGER;
V_MSG VARCHAR2(255);
CURSOR C_USER IS
SELECT CU.*,
CU.ROWID ROW_ID
FROM CMX_USER_ATTR CU
WHERE NVL(CU.IS_FINISH, 'N') = 'N';
BEGIN
--L_SQL:= 'dbms_session.set_role(' || L_ROLE || ')';
--EXECUTE IMMEDIATE L_SQL;
FOR C_REC IN C_USER LOOP
DELETE FROM CMX_ROLE_TO_RMS_TMP;
INSERT INTO CMX_ROLE_TO_RMS_TMP
(GRANTED_ROLE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
SELECT DISTINCT T.GRANTED_ROLE ROLE_ID,
SYSDATE,
USER,
SYSDATE,
USER
FROM DBA_ROLE_PRIVS T
WHERE T.GRANTEE = C_REC.USER_ID
UNION
SELECT DISTINCT ROLE_ID,
SYSDATE,
USER,
SYSDATE,
USER
FROM CMX_ROLE_DESC
WHERE TRIM(STATION) = TRIM(C_REC.STATION)
UNION
SELECT DISTINCT T.GRANTED_ROLE,
SYSDATE,
USER,
SYSDATE,
USER
FROM DBA_ROLE_PRIVS T,
CMX_USER_ATTR UT
WHERE T.GRANTEE = UT.USER_ID
AND UT.USER_ID = C_REC.SAME_USER;
FOR REC_ROLE IN (SELECT CR.GRANTED_ROLE
FROM CMX_ROLE_TO_RMS_TMP CR) LOOP
V_SQL := 'GRANT ' || REC_ROLE.GRANTED_ROLE || ' TO RMS with admin option';
EXECUTE IMMEDIATE V_SQL;
END LOOP;
--CMX_GRANT_ROLE_RMS;
CMX_USER_ADD(C_REC.USER_ID, C_REC.PWD, C_REC.USER_NAME, C_REC.STATION, C_REC.IS_CG, C_REC.SAME_USER, NULL);
UPDATE CMX_USER_ATTR CU
SET CU.IS_FINISH = 'Y'
WHERE CU.ROWID = C_REC.ROW_ID;
FOR REC_ROLE IN (SELECT CR.GRANTED_ROLE
FROM CMX_ROLE_TO_RMS_TMP CR) LOOP
V_SQL := 'REVOKE ' || REC_ROLE.GRANTED_ROLE || ' FROM RMS';
EXECUTE IMMEDIATE V_SQL;
END LOOP;
--CMX_REVOKE_ROLE_RMS;
END LOOP;
--RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
V_MSG := (SQLCODE || '---' || SQLERRM);
--RAISE_APPLICATION_ERROR(-20001, TO_CHAR(V_BREAKPOINT) || '-' || V_MSG || '-' || ERROR);
END CMX_CREATE_USER_SQL;
其中有动态建用户，动态grant，动态revoke SQL。
其中动态grant导致了锁堵塞，引发了死锁。
4 根因分析：
在grant role to rms时候的时候，有用户登陆操作，发生死锁，但oracle没有检测出来死锁。
解决办法：终止挂死操作。
APPLIES TO:
Oracle Database - Standard Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
The deadlock for dc_users is note detected automatically on RAC environment.
By such a symptom, DDL operations which require dc_users will be hunged.
Example)
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <temporary tablespace name>;
When the issue is occured, a session will be waitng as "waiting for 'row cache lock'" of dc_users for long periods.
Also, a blocker is waiting for a waited session. However, the deadlock is not detected automatically and errors like ORA-60 is not happend.
CHANGES
CAUSE
Bug 32369734 has invetigated the symptom, but not fixed yet.
SOLUTION
The deadlock can be occured based on its timinng.
If the issue is occured, please cancel a hanged operation manually and retry it.
REFERENCES
BUG:32369734 - THE DEADLOCK FOR DC_USERS IS NOT DETECTED ON RAC ENVIRONMENT
5 后期处理：
1 修改这个存储过程，去掉grant、revoke操作
2 规范开发，不要在繁忙时段对关键账户做grant或revoke操作
3 部署锁堵塞处理脚本，减少处理时间
4 监控加强，监控library cache lock，library cache pin, row cache lock等待情况，有进程堵塞超过30秒告警
tips：查blocker信息：
set serveroutput on
exec print_table('SELECT * FROM x$kqrfp WHERE kqrfpmod!=0')
POS从库发生library cache lock，这个问题是遗留下来的老问题。根本原因是系统有大量需要bind variables的SQL及巨长in-list导致hardparse时间很长的SQL导致。
注：< oracle ADG从库有一个DB instance的锁，整个实例只有一个，oracle在做parse的时候需要以S模式获取，oracle的LGWR不定时需要以X模式获取。
oracle的LGWR不定时需要以X模式获取，这个操作一般很快很快完成即可释放锁，但由于hardparse相对很慢（100ms-100s不等），出现一个慢的hardparse会堵住LGWR的情况，而LGWR又堵住其它所有需要S模式锁的连接，从而导致并发告警产生。
根本上解决这个问题，从这几个方面调整：一是使用绑定变量，二是使用cursor_sharing=force，三是消除巨长in-list SQL。
使用绑定变量和巨长in-list问题已多次向开发提出，未得到解决。cursor_sharing=force会导致SQL执行计划变坏，引起更多其它问题，放弃。>
针对这个问题，oracle有一个隐含参数，来缓解这个问题：_adg_parselock_timeout
这个参数的意思是：LGWR在获取锁时，等待 _adg_parselock_timeout时间后仍然未获取到，将放弃申请，sleep一段时间后再次申请。放弃申请后被堵住的连接获取锁从而得到运行，从而缓解锁竞争。
_adg_parselock_timeout单位是分秒
针对目前238的情况。调整这个参数，从550调整到250，从而减少竞争情况：
ALTER SYSTEM SET _adg_parselock_timeout=250 SCOPE=BOTH;
注:<_adg_parselock_timeout不易设置太小，可能出现LGWR饿死的情况，从而影响归档应用和其它影响。
走变更调整后，lirbary cache lock竞争明显降低，从而提升性能。
