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
?
?
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:
?
| 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... |
| 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.
?
?
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.