因为tempfile争用，而导致的buffer busy wait
1. 同事发来一份AWR报告，反馈当前系统运行非常慢，让帮忙看看具体是什么原因。
2. 下面，直接来看看这份AWR。
| Event | Waits | Total Wait Time (sec) | Wait Avg(ms) | % DB time | Wait Class |
|---|---|---|---|---|---|
| buffer busy waits | 7,078 | 30.9K | 4365 | 75.2 | Concurrency |
| gc buffer busy release | 5,972 | 5157.1 | 864 | 12.6 | Cluster |
| DB CPU | 2612.3 | 6.4 | |||
| enq: SS - contention | 321 | 1159.4 | 3612 | 2.8 | Configuration |
| local write wait | 894 | 675.9 | 756 | 1.6 | User I/O |
| db file sequential read | 887,180 | 213.3 | 0 | .5 | User I/O |
| library cache lock | 618 | 98.8 | 160 | .2 | Concurrency |
| gc current grant 2-way | 457,033 | 65.3 | 0 | .2 | Cluster |
| direct path write | 93,208 | 61.9 | 1 | .2 | User I/O |
| enq: RO - fast object reuse | 13 | 57.8 | 4446 | .1 | Application |
从等待事件来看，buffer busy waits最严重。
3、直接看buffer busy waits相关的对象。
Segments by Buffer Busy Waits
- % of Capture shows % of Buffer Busy Waits for each top segment compared
- with total Buffer Busy Waits for all segments captured by the Snapshot
| Owner | Tablespace Name | Object Name | Subobject Name | Obj. Type | Buffer Busy Waits | % of Capture |
|---|---|---|---|---|---|---|
| OCS_TAE | IDX_OCSTAE | PK_E_OCS_BASE_INFO | INDEX | 132 | 33.08 | |
| SYS | SYSTEM | I_OBJ1 | INDEX | 33 | 8.27 | |
| OCS_TAE | DATA_OCSCALC | PK_ES_RCA_BASE_INFO_MID4 | INDEX | 33 | 8.27 | |
| SYS | SYSTEM | I_OBJ5 | INDEX | 23 | 5.76 | |
| OCS_CALC | IDX_OCSCALC | PK_E_OCS_CONSPRC_TACTIC_SNAP | P20230110_P41402 | INDEX SUBPARTITION | 17 | 4.26 |
这里有个奇怪的现象，那就是在数据对象上的buffer busy waits其实并不高，这说明buffer busy wait的并不是data。
4、继续分析buffer busy waits部分的统计信息
Buffer Wait Statistics
- ordered by wait time desc, waits desc
| Class | Waits | Total Wait Time (s) | Avg Time (ms) |
|---|---|---|---|
| file header block | 39,559 | 30,685 | 776 |
| data block | 1,219 | 1 | 1 |
| undo header | 388 | 0 | 0 |
| undo block | 195 | 0 | 0 |
| segment header | 3 | 0 | 3 |
| bitmap index block | 45 | 0 | 0 |
| 1st level bmb | 14 | 0 | 0 |
| 2nd level bmb | 4 | 0 | 0 |
可以看出file header block部分的平均等待时间和总的等待时间最长，并且这个时间与AWR中的TOP EVENT是相符的，说明buffer busy wait主要在等待file header block。
5、具体buffer busy wait在哪个文件头上等待呢，此时需要分析dba_hist_active_sess_history了。
可以看出，在故障时间段，基本上主要是3条SQL语句在等待buffer busy waits, 进一步分析这3条SQL语句，发现这3条SQL语句都非常简单，例如：INSERT INTO E_OCS_CAL_DATA_TEMP(CONS_ID, BATCH_NO) VALUES (:B1 , :B2 )；
6、查看这3条SQL语句所涉及的对象的表结构情况，发现全部为全局临时表。
7. 查看buffer busy waits等待事件的p1和p2值：
可以看出，buffer busy waits主要等待(4097,2)。 而p1=4097，实际上是temp表空间的第1个tempfile。
8、至此，故障的原因最终理清楚了，全局临时表（global temporary table）上大量的数据插入操作，导致tempfile产生争用，而出现buffer busy wait。从最上面的 TOP EVENT部分的enq: SS - contention也能看出临时段的争用。
9、解决办法。 由于这3张全局临时表属于不同的用户，建议：1、为不同的用户分配不同的临时表空间。2、新创的临时表空间的uniform size 为200MB。
