本文主要是介绍latch: CDC 与buffer busy waits，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
latch: CDC 等待的是latch，引发的原因是大量同一块（一块内包含很多行）的读
buffer busy waits以及 free buffer 一般是DML导致的，修改块内容
Securefiles DMLs cause high 'buffer busy waits' & 'enq: TX - contention' wait events leading to whole database performance degradation.
Top 5 Timed Foreground Events
| Event | Waits | Time(s) | Avg wait (ms) | % DB time | Wait Class |
|---|---|---|---|---|---|
| buffer busy waits | 887,096 | 92,283 | 104 | 66.65 | Concurrency |
| enq: TX - contention | 2,245,624 | 41,244 | 18 | 29.79 | Other |
| DB CPU | 2,030 | 1.47 | |||
| db file sequential read | 854,861 | 1,524 | 2 | 1.10 | User I/O |
| latch: cache buffers chains | 375,672 | 634 | 2 | 0.46 | Concurrency |
$ sqlplus / as sysdba
col Parameter for a30
col "Session Value" for a20
col "Instance Value" for a20
SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND lower(a.ksppinm) like '_securefiles_concurrency_estimate';
Parameter Session Value Instance Value
---------------------------------------- -------------------- --------------------
_securefiles_concurrency_estimate 12 12
CAUSE
Bug 16375331 was filed seeking development team's feedback. It was closed as duplicate of Bug 13593640 which was also closed as "Not a Bug".
There is no indication for of any abnormal behavior. There is a high contention on GBSASP.SYS_LOB0000017961C00006$$ when the default setting of the hidden parameter "_securefiles_concurrency_estimate" can not afford such high concurrency.
System state dumps show high 'enq: TX - contention' with no single blocker.
AWR report shows frequently executed DMLs against table TBPINSTANCES:
SQL ordered by Elapsed Time
| Elapsed Time (s) | Executions | Elapsed Time per Exec (s) | %Total | %CPU | %IO | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|---|
| 37,917.03 | 0 | 27.38 | 0.63 | 0.72 | 3bfqvwrqqpgmr | SeeBIS6 AS 20138 | UPDATE TBPINSTANCES SET cProce... | |
| 33,449.86 | 0 | 24.16 | 0.62 | 0.71 | 0u0vxfm0k4yba | SeeBIS6 AS 20138 | INSERT INTO TBPINSTANCES (cID,... | |
| 21,192.09 | 0 | 15.30 | 0.62 | 0.70 | 4xfhztsqxh1nq | SeeBIS6 AS 20138 | UPDATE TBPINSTANCES SET cProce... | |
| 11,153.72 | 0 | 8.06 | 0.61 | 0.73 | f4jzxjbazkyr1 | SeeBIS6 AS 20138 | UPDATE TBPINSTANCES SET cProce... | |
| 11,151.98 | 0 | 8.05 | 0.63 | 0.70 | 391f6569xdbr3 | SeeBIS6 AS 20138 | UPDATE TBPINSTANCES SET cState... | |
| 7,803.23 | 0 | 5.64 | 0.62 | 0.74 | 8a1urkdd3y048 | SeeBIS6 AS 20138 | UPDATE TBPINSTANCES SET cState... | |
| 5,573.08 | 0 | 4.02 | 0.62 | 0.73 | 20a291w9y9ruy | SeeBIS6 AS 20138 | UPDATE TBPINSTANCES SET cProce... | |
| 3,343.87 | 0 | 2.41 | 0.62 | 0.72 | 899f7bqcg1rg7 | SeeBIS6 AS 20138 | UPDATE TBPINSTANCES SET cProce... | |
| 1,114.66 | 0 | 0.80 | 0.62 | 0.62 | 53b97nu5k9948 | SeeBIS6 AS 20138 | UPDATE TBPINSTANCES SET cProce... | |
| 1,113.91 | 0 | 0.80 | 0.65 | 0.74 | 82zk467usk116 | SeeBIS6 AS 20138 | UPDATE TBPINSTANCES SET cProce... |
Segments by Buffer Busy Waits
| Owner | Tablespace Name | Object Name | Subobject Name | Obj. Type | Buffer Busy Waits | % of Capture |
|---|---|---|---|---|---|---|
| GBSASP | GBSLOB | SYS_LOB0000017961C00006$$ | LOB | 1,005,665 | 99.96 | |
| GBSASP | GBSDTA | TJMSMESSAGES | TABLE | 124 | 0.01 | |
| GBSASP | GBSIDX | PK_TJMSMESSAGES | INDEX | 94 | 0.01 | |
| GBSASP | GBSIDX | IDX_JMSMESSAGES_TXOP_TXID | INDEX | 51 | 0.01 | |
| GBSASP | GBSIDX | PK_TJMSTRANSACTIONS | INDEX | 37 | 0.00 |
>>>> The contention seems to be on GBSASP.SYS_LOB0000017961C00006$$
LOBs are mainly designed for "once inserted + many times queried". They have not been designed for such great number of UPDATEs.
SOLUTION
1) Please increase the securefiles concurrency estimate as follows:
# sqlplus / as sysdba
alter system set "_securefiles_concurrency_estimate"=50 scope=spfile;
>>> then restart the instance for the new value to take effect.
By increasing this parameter, we expect less concurrency contention on the securefiles. This Parameter can only hold values only between 1 and 50 (both included).
2) Then, kindly recreate any table that contains securefiles especially the contended table.
Recreating the contended table is necessary as the hidden parameter "_securefiles_concurrency_estimate" is picked up for securefiles setting only during creation.
这篇关于latch: CDC 与buffer busy waits的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
