海龙仓报上架慢，登陆数据库，发现一条sql多个并发运行，运行时间零点几秒
看其执行计划，发现有多个执行计划，有个执行计划有问题
SQL> !ora sql 2707273556
SQL_TEXT
----------------------------------------------------------------
SELECT a.Status, a.Qty, a.serialkey FROM LotxLocxId a WHERE a.lo
t = :1 AND a.loc = :2 AND a.id = :3
------------------------------ Formatted SQL ------------------------------
/db/home/oracle/oracle11/product/11.2.0/db_2/bin/ora[5503]: /export/home/oracle/bin/prettySQL.pl: not found [没有那个文件或目录]
HASH_VALUE CHILD outline/plan_hash_value Ex DISK_READS bg bg/exec rows LOAD_TIME
---------- ---------- ---------------------------------------------------------------- ---------- ---------- --------------- ------------ ---------- -----------
2707273556 3 825317404 15349 0 76749 5.00 15349 07-26/00:13
2707273556 4 825317404 39055 0 195294 5.00 39058 07-26/00:13
2707273556 5 825317404 110692 0 553469 5.00 110692 07-26/00:13
2707273556 6 825317404 4 0 24 6.00 4 07-26/00:13
2707273556 7 825317404 13 0 69 5.31 13 07-26/00:13
2707273556 8 1219680500 72988 6 3972097371 54421.24 71144 07-26/00:13
2707273556 9 825317404 9796 0 48984 5.00 9796 07-26/00:13
2707273556 10 825317404 14718 0 73594 5.00 14718 07-26/00:13
2707273556 11 1219680500 16310 0 2902041069 177930.17 15890 07-26/00:13
TOTAL 278925 6 6875086623 24647.72 276664
10 rows selected.
PL/SQL procedure successfully completed.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
SQL_ID bk6mucqhpvdun, child number 3
-------------------------------------
SELECT a.Status, a.Qty, a.serialkey FROM LotxLocxId a WHERE a.lot = :1
AND a.loc = :2 AND a.id = :3
Plan hash value: 825317404
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| LOTXLOCXID | 1 | 72 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_SYS_C004687 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."LOT"=SYS_OP_C2C(:1) AND "A"."LOC"=SYS_OP_C2C(:2) AND
"A"."ID"=SYS_OP_C2C(:3))
SQL_ID bk6mucqhpvdun, child number 4
-------------------------------------
SELECT a.Status, a.Qty, a.serialkey FROM LotxLocxId a WHERE a.lot = :1
AND a.loc = :2 AND a.id = :3
Plan hash value: 825317404
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| LOTXLOCXID | 1 | 72 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_SYS_C004687 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."LOT"=SYS_OP_C2C(:1) AND "A"."LOC"=SYS_OP_C2C(:2) AND
"A"."ID"=SYS_OP_C2C(:3))
SQL_ID bk6mucqhpvdun, child number 5
-------------------------------------
SELECT a.Status, a.Qty, a.serialkey FROM LotxLocxId a WHERE a.lot = :1
AND a.loc = :2 AND a.id = :3
Plan hash value: 825317404
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| LOTXLOCXID | 1 | 72 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_SYS_C004687 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."LOT"=SYS_OP_C2C(:1) AND "A"."LOC"=SYS_OP_C2C(:2) AND
"A"."ID"=SYS_OP_C2C(:3))
SQL_ID bk6mucqhpvdun, child number 6
-------------------------------------
SELECT a.Status, a.Qty, a.serialkey FROM LotxLocxId a WHERE a.lot = :1
AND a.loc = :2 AND a.id = :3
Plan hash value: 825317404
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| LOTXLOCXID | 1 | 72 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_SYS_C004687 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."LOT"=SYS_OP_C2C(:1) AND "A"."LOC"=SYS_OP_C2C(:2) AND
"A"."ID"=SYS_OP_C2C(:3))
SQL_ID bk6mucqhpvdun, child number 7
-------------------------------------
SELECT a.Status, a.Qty, a.serialkey FROM LotxLocxId a WHERE a.lot = :1
AND a.loc = :2 AND a.id = :3
Plan hash value: 825317404
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| LOTXLOCXID | 1 | 72 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_SYS_C004687 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."LOT"=SYS_OP_C2C(:1) AND "A"."LOC"=SYS_OP_C2C(:2) AND
"A"."ID"=SYS_OP_C2C(:3))
SQL_ID bk6mucqhpvdun, child number 8
-------------------------------------
SELECT a.Status, a.Qty, a.serialkey FROM LotxLocxId a WHERE a.lot = :1
AND a.loc = :2 AND a.id = :3
Plan hash value: 1219680500
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| LOTXLOCXID | 1 | 72 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_LLI_ISQ | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("A"."LOT"=SYS_OP_C2C(:1) AND "A"."LOC"=SYS_OP_C2C(:2)))
2 - access("A"."ID"=SYS_OP_C2C(:3))
SQL_ID bk6mucqhpvdun, child number 9
-------------------------------------
SELECT a.Status, a.Qty, a.serialkey FROM LotxLocxId a WHERE a.lot = :1
AND a.loc = :2 AND a.id = :3
Plan hash value: 825317404
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| LOTXLOCXID | 1 | 72 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_SYS_C004687 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."LOT"=SYS_OP_C2C(:1) AND "A"."LOC"=SYS_OP_C2C(:2) AND
"A"."ID"=SYS_OP_C2C(:3))
SQL_ID bk6mucqhpvdun, child number 10
--------------------------------------
SELECT a.Status, a.Qty, a.serialkey FROM LotxLocxId a WHERE a.lot = :1
AND a.loc = :2 AND a.id = :3
Plan hash value: 825317404
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| LOTXLOCXID | 1 | 72 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_SYS_C004687 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."LOT"=SYS_OP_C2C(:1) AND "A"."LOC"=SYS_OP_C2C(:2) AND
"A"."ID"=SYS_OP_C2C(:3))
SQL_ID bk6mucqhpvdun, child number 11
--------------------------------------
SELECT a.Status, a.Qty, a.serialkey FROM LotxLocxId a WHERE a.lot = :1
AND a.loc = :2 AND a.id = :3
Plan hash value: 1219680500
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| LOTXLOCXID | 1 | 72 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_LLI_ISQ | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("A"."LOT"=SYS_OP_C2C(:1) AND "A"."LOC"=SYS_OP_C2C(:2)))
2 - access("A"."ID"=SYS_OP_C2C(:3))
绑定SQL执行计划：
DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id=>'bk6mucqhpvdun',
plan_hash_value=>'825317404',
fixed=>'YES',
enabled=>'YES');
end;
/
绑定后SQL执行计划正常
这个SQL很快执行完：
上面那个SQL已不再active sql中。
至此优化完成。
结论：关键表禁止在繁忙业务时段进行DDL，会失效大量依赖这个表的视图、存储过程等对象，造成系统卡顿。
CRM siebel库：
9y1sz472yx87x SELECT T2.CONFLICT_ID, T2.LAST_UPD, T2.CREATED, T2.LAST_UPD_BY, T2.CREATED_BY, T2.MODIFICATION_NUM, T2.ROW_ID, T1.SUB_TYPE, T1.CODE, T2.ACTIVE_FLG, T2.STATUS, T2.END_TIME, T2.BEGIN_TIME, T2.VOUCHER_CODE, T2.MEMBER_ID, T2.SEND_TYPE, T2.SOURCE, T2.RULE_CODE, T2.CURCY_AMOUNT, T2.CURCY_CD, T2.CONSUMED_TXN_ID, T2.CURCY_EXCH_DT, T2.MERGE_MEM_ID, T2.PROD_ID, T2.QTY, T2.ORIG_VCHR_ID, T2.VCHR_EFF_START_DT, T2.GENERAL_DATE, T1.VOUCHER_ID, T1.ACTIVE_FLG FROM SIEBEL.CX_VOUCHER_RULE T1, SIEBEL.CX_VOUCHER T2 WHERE T2.RULE_CODE = T1.ROW_ID (+) AND (T2.RULE_CODE = :1 AND T2.STATUS = :2)
sql执行计划无问题，但SQL性能有问题。跟踪后发现大量递归SQL发生。
建议升级到12.2/19c
临时解决办法：
_optimizer_dsdir_usage_control
126
TRUE
改成0
：alter system set "_optimizer_dsdir_usage_control" = 0 scope=both ;
改回alter system set "_optimizer_dsdir_usage_control" = 126 scope=both
这几个参数几点说明：
0 session参数不一致
sessions参数一般不需要设置，oracle会自动通过processes参数计算出来，这个40176是什么原因设置
1 proceses参数设置
processes参数应该和开发沟通，估计会有多少连接，加上一个保险系数。例如现在POS主库平均连接是900，设置大了主要存在的风险是当因开发某个bug导致连接泄漏，或数据库出现堵塞时，连接数会急剧上升，内存大量消耗，加剧数据库的风险。
这个参数起到了某种程度的流控，保护了数据库；设置过大，实际上通过连接数来保护数据库这种机制失效。
2 open_cursors
这个参数是每个oracle 连接最大能打开的游标数量，设置10000过大了，
主库平均290，最大游标,880
SQL> select max(count(*)) from v$open_cursor group by sid;
MAX(COUNT(*))
-------------
880
这个参数设置过大实际上没意义，应用出现游标泄漏后还会影响数据库，在游标数量监控不到位时也发现不了应用游标泄漏的问题。
设置过大增大server process的内存消耗及管理成本，总之这个参数太大无意义，减弱了这个参数对数据库的保护机制。
3 session_cached_cursors
这个参数是每个连接缓存的游标数量，当用户close cursor后，满足一定条件会缓存这个这个游标。缓存的游标通过LRU算法管理，设置过大会增大server process的内存消耗及管理成本，总之这个参数设置太大无意义。
ORacle这个参数缺省是20.
很多参数实际上有一定限流保护机制，在正常情况下，这些机制不起作用，但在异常情况下存在保护数据库的可能性，设置过大只能浪费资源，削弱保护机制，加剧风险。参数设置一定要合理设置，有依据。
特别是需要重启的参数，设置成非缺省值要有依据，一定要慎重。
对于DBA来说，保证数据库的稳定运行是第一位的。
POS目前主库主要问题有2个：
1大量SQL没使用绑定变量，导致设置cursor_sharing=force。一般来说这个参数不适合关键业务系统，潜在有各种各样的bug
2 巨长的拼接SQL，如大量union all select .... from dual;还有巨大的in-list
POS从库主要问题有：
1 standby query scn advance等待问题
2 library cache lock问题
3 cache buffers chains问题，主要是UNDO block竞争
4 巨大的in-list导致硬解析问题
5 从库延迟
6 执行计划有时变化问题，难以绑定固化执行计划
7 异常block change问题
从库上级几个问题直接有联系，例如从库延迟主要是因为是library cache lock问题，而library cache lock问题主要来自巨大的in-list导致硬解析问题
standby query scn advance等待问题又和从库延迟有一定关系。
近期调整了一个参数：
alter system set "_log_committime_block_cleanout" = TRUE;
这个参数作用是：主库在commit的时候，会清理ITL事务槽，缺省这个清理不会记录redo；从而从库的ITL事务槽没有清理。当从库读取这个block时，会取一个freeblock，做commit cleanout，但由于从库是只读，这个清理并不会持久化到数据文件。
这个参数改成true后，可以把commit cleanout产生的redo传到从库，从而使ITL事务槽得到更新，避免大量重复的commit cleanout。
这个参数有一下几个好处：
1 减少大量无效commit cleanout操作。同时数据库 blcok change大大减少
2 减少大量free内存page申请。因为每次从库block commit cleanout，会申请一个新的内存page
3 减少逻辑读次数。block每次访问的时候，做block commit cleanout情况下，逻辑读会从1次增加到2次
4 减少cache buffer chains等待，修改前这个等待每天有大量cache buffer chains事件等待，修改后很少发生：
10.0.102.238上查云一天的这个事件被捕捉的情况：
SQL> select count(*) from v$active_session_history where event='latch: cache buffers chains';
COUNT(*)
----------
10
关于latch：cache buffers chains等待事件说明：
1 oracle通过对buffer cache 分桶，（桶的数量大致是block的2倍，平均一个桶只有0.5个block），一批桶使用一个buffers chain latch来管理。buffers chain latch数量和桶个数、CPU数量有关，POS上这个拉太长大约有2M个。
已800G buffer cache计算，大约每个latch管理50个block
2 buffers chain latch有2中模式，S模式和X模式，S模式互相兼容，S和X、X和X不兼容
3 CBC latch何时共享持有:
1.读非唯一索引的根块+分支块:读非唯一索引的叶子块是排他.
2.通过唯一索引访问时,索引的根块+分支块+叶子块+表块
原因:因为根块和分支块的访问频度高，而且很少改动。
CBC latch何时独占持有:
1.所有涉及改的操作。
2.以及上面列出的共享持有以外的所有读操作.
3 4月21日POS环境竞争的block时UNDO block，访问block时，CBC模式是独占，会互相竞争
4 POS从库cache bufferchains等待的SQL主要有2类：FORCE_MATCHING_SIGNATURE=2087400549852082274 or FORCE_MATCHING_SIGNATURE=3447245080524081308
SQL：
查30天内数据
查180天内数据
这2条SQL都会查最新数据。由于Oracle的查询SQL最查询的时候，会确定QUERY SCN，是查询QUERY SCN时间点的数据。
如果这个时间点数据被修改，会访问UNDO，构建block在QUERY SCN时间点的快照。
SQL越慢，需要通过UNDO构建的block就会越多，对CBC latch的竞争却激烈
ConCng是因为一致读导致的block change，数量巨大，导致CBClatch竞争激烈。
5 另外这2个查询是打开界面上显示的，用户会在慢的时候反复刷新，导致后台同样SQL大量运行。进一步加剧了堵塞
6 当时处理办法是kill这2个SQL的连接，需要快速kill，通过 alter system kill session速度达不到要求。
kill大量堵塞的查询，结果系统恢复正常。
注：这里kill有效，是系统在正常负载下可以支撑正常流量。kill后用户会继刷新，但由于先前堵塞的查询已被KILL，新来的查询能以较快的速度执行完，从而堵塞解除。
select '!kill -9 '||spid
from v$process
where addr in (select paddr from v$session where status='ACTIVE'
and sql_id in (select sql_id from v$sql where FORCE_MATCHING_SIGNATURE=2087400549852082274 or FORCE_MATCHING_SIGNATURE=3447245080524081308));
spool off
!sh kill.txt
调整后ConCng急剧减少
优化总结：
1 not in 改写成left join
2 提前group by
3 去掉无效的distinct
4 确认执行计划和构思的一致。按照正确次序走hash-join
登陆服务器，发现free内存还有很多，但available不足，结果触发告警：
[root@pm4db ~]# free -m
total used free shared buff/cache available
Mem: 31826 9870 13762 5597 8193 2133
Swap: 16383 5877 10506
free是13762，但available只有2133，和正常available>free相反。
进一步检查，发现vm.min_free_kbytes值不对，设成了vm.min_free_kbytes = 10589460
这个值会影响zone（即CPU scoket）的设置(/proc/zoneinfo)：
min约等于vm.min_free_kbytes/4
low约等于min*1.25
high约等于min*1.5
这个会影响available计算，available需要扣减min这部分内存，从而出现available>free的情况。
available = free + buffer/cache - 不可被回收内存(共享内存段、tmpfs、ramfs等) - sum(zone.min)
Linux内核回收内存是这样处理的：
当zone free内存小于low的时候，kswapd开始工作，回收到当free达到high时，停止回收。（kswapd线程参与回收）
当zong free内存小于min的时候，会发生direct path reclaim回收（用户线程分配内存时陷入到内存回收程序中），当线程多的时候，由于底层内存回收是串行，这样严重影响系统性能，应用表现卡顿，SYSTEM CPU变高，但进程上下文切换不是很高。
vm.min_free_kbytes的作用是什么呢？
vm.min_free_kbytes用来保留内存用于内核内存分配，避免内核分配内存时不足从而OOM。一般来说内核内存分配是较为缓慢（相比应用，内核每次申请的量不大），当系统free减少的时候，kswapd会开始工作回收内存。
所以这个值不要设置过大，一般来说缺省即可（缺省值是根据物理内存自动计算的），必要时可以增大，一般来说64M足够使用（特别大物理内存的系统可以增大，但也不要超过512M）。
vm.min_free_kbytes这部分内存基本上可以当做"浪费"的内存,只是紧急为内核内存分配使用
后把这台机的vm.min_free_kbytes从sysctl.conf去掉，保留缺省值即可。内存告警也立即恢复了。
available内存也恢复正常：
total used free shared buff/cache available
Mem: 31826 8562 13457 6261 9806 16417
POS主库不时出现性能波动，主要症状是log file sync显著变慢，正常情况下log file sync是0.5ms（存储使用了联想纯SSD FC存储），但出现波动时log file sync会达到几十ms。
这时候一些正常很快完成的操作会由于log file sync而放大。
log file sync等待是事务提交时，redo log落盘到redofile的操作，主要时间消耗在redo写IO上（LGWR忙的话，CPU也有些消耗）,那什么原因导致写IO变慢呢？
通过上图，发现有log file switch completion操作（这个是写redofile到归档路径），还有direct path read、db file sequentialread，说明有大量读IO，且读IO的延迟相对于正常情况，变慢很多。
那是存储性能不行吗？
通过iostat命令和sar命令，发现IO queue time明显变大，servicetime还好
servicetime是访问IO的时间。awaittime=servicetime+queuetime（排队时间）
servicetime情况还好，说明IO压力虽大，但没有到存储瓶颈，主要时间花在排队上。
那排队原因是什么？
分析缓存页情况：
通过sar，知道有大量的pgpgin发生（读IO），有大量文件系统内存回收发生（回收内存，为写IO使用）。操作系统的文件系统page换进换出出现堵塞，从而导致读写都变慢。
在看系统free内存情况：
freememory很少，文件系统缓存很大。
在看数据库的参数配置：
filesystemio_options
string
none
说明oracle数据库所有IO都先通过文件系统缓存，文件系统缓存换进换出堵塞了IO，导致IO变慢。
综上，整合问题根因就清楚了：由于filesystemio_options=none，导致系统free内存很少，导致所有IO都要通过文件系统缓存；当出现redologfile切换时，需要写一个大文件（redofile是6144MB），这时又有大量读IO的时候，文件系统缓存需要做大量内存回收、分配、换进换出的操作，就会造成IO延时性能急剧下降。这时候所有读写操作都明显变慢，从而出现性能波动。
上面图中的enq: SQ - contention、row cache lock就是因为写IO变慢，从而取sequence的操作平时很正常的这时出现明显等待。这个可以也可以通过设大sequence cache缓解。
解决办法：
设置filesystemio_options=SETALL，使用direct+async IO，所有IO操作不经过文件系统缓存层处理，从而避免了性能波动。
这个需要重启数据库生效
