Troubleshooting high db file sequential reads cause a insert do index split
To control the number of blocks scanned per insert,
Example:
_assm_low_gsp_threshold = 2000; — the current default is 10000
_assm_high_gsp_threshold = <_assm_low_gsp_threshold + 2100 --- the currentdefault : 11024
- Between low and high, we collect the block rejection reason for diagnostics purpose.
- high must be gre ater than or equal to low
The low is capped at 1024. If the app requires a value lower than 1024. Set event 450503 in addition to force the code to accept whatever is set for _assm_low_gsp_threshold.
Sample s etting:
450502, level 1 -- to enable fix
_assm_low_gsp_threshold = 1024 -- co llect rejection stats after rejecting 1024 blocks
_assm_low _gsp_threshold = 1100 - If blocks rejected > 1100, extend the segment
The recommendation is to enable bug fix 8735005 with event 450502 level 1.
If the index contention persists in the presence of ‘leaf node splits’
statistic (awr showed 57,072 splits), then we could try to adjust the parameters _assm_low_gsp_threshold and _assm_high_gsp_threshold to favor segment extention more frequently.
.
Please set event 450502 level 1 for the session(s) executing the above INSERTand monitor for index contention.
Event 43822 – took care of reducing sequential reads during root/branch split. After rejecting about 5 blocks, we would extend the segment.
There were customers who complained about seq reads on blocks other than root/branch. To fix, we introduced event 450502. This subsumed event 43822.
.
Overall,
1. Set 450502, level 1 -> to control block rejections for any index block.
2. Set underscores to reduce the limit from 10000 to something lower. If the limit is good enough, do not have to set these parameters.
3. If the limit must be reduced to below 1024 (using the underscore) additionally set event 450503 – it forces the code to accept whatever user sets.
4. Set 43822 – to control rejections for root/branch split.
An example setting could be
1. event 450502, level 1
2. event 43822
3. _assm_{low| high}_gsp_threshold = {512, 512}
4. event 450503, level 1
.
The above setting will have the following impact:
1. When root/branch splits, extend the segment af ter 5 rejections
2. When any other block splits, extend the segment after 512 rejections
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。