在高压力、大并发的情况下，Oracle的种种Bug此起彼伏的跳出来，开始用的10g的版本10.2.0.4进行测试，后来遇到了一个10g中不修正的Bug，只好将数据库升级到Oracle 11gR2上来。
在这个测试中经历了非常多的异常情况，包括对于SAP系统的Debug跟踪等。
以前不常见的种种Latch竞争纷纷呈现。
简要摘录一些测试过程中遇到的问题与大家分享。
以下是10.2.0.4测试的数据库，Buffer Cache 配置200G，Shared Pool配置8G：
| DB Name | DB Id | Instance | Inst num | Release | RAC | Host |
|---|---|---|---|---|---|---|
| E00 | 3694296179 | SAP | 1 | 10.2.0.4.0 | NO | hpdb4 |
| Snap Id | Snap Time | Sessions | Cursors/Session | |
|---|---|---|---|---|
| Begin Snap: | 886 | 25-Oct-10 19:19:14 | 9019 | 38.6 |
| End Snap: | 887 | 25-Oct-10 19:44:06 | 9022 | 12.0 |
| Elapsed: | 24.86 (mins) | |||
| DB Time: | 194.88 (mins) |
Report Summary
Cache Sizes
| Begin | End | |||
|---|---|---|---|---|
| Buffer Cache: | 200,000M | 200,000M | Std Block Size: | 8K |
| Shared Pool Size: | 8,192M | 8,192M | Log Buffer: | 62,988K |
此时的负载概要如下，事务数大约是6580个/秒，每秒Redo大约7M：
| Per Second | Per Transaction | |
|---|---|---|
| Redo size: | 7,490,377.18 | 1,138.27 |
| Logical reads: | 270,762.69 | 41.15 |
| Block changes: | 29,554.77 | 4.49 |
| Physical reads: | 1.95 | 0.00 |
| Physical writes: | 1,563.19 | 0.24 |
| User calls: | 69,075.67 | 10.50 |
| Parses: | 30.44 | 0.00 |
| Hard parses: | 0.09 | 0.00 |
| Sorts: | 8.68 | 0.00 |
| Logons: | 1.31 | 0.00 |
| Executes: | 62,484.20 | 9.50 |
| Transactions: | 6,580.48 |
此时数据库的主要竞争体现在：
Top 5 Timed Events
| Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
|---|---|---|---|---|---|
| latch free | 2,011,998 | 767,164 | 381 | 6,561.1 | Other |
| CPU time | 8,500 | 72.7 | |||
| latch: session allocation | 57,723 | 2,350 | 41 | 20.1 | Other |
| latch: enqueue hash chains | 1,657 | 10 | 6 | .1 | Other |
| latch: cache buffers chains | 10,160 | 5 | 1 | .0 | Concurrency |
这里的latch: session allocation最终被证实是一个Bug，10g中未修正，始终无法解决。
Latch的使用情况如下：
| Latch Name | Get Requests | Misses | Sleeps | Spin Gets | Sleep1 | Sleep2 | Sleep3 |
|---|---|---|---|---|---|---|---|
| dml lock allocation | 31,707,289 | 3,083,158 | 11,321 | 3,072,356 | 0 | 0 | 0 |
| resmgr:active threads | 3,751,592 | 2,022,796 | 2,022,666 | 169 | 0 | 0 | 0 |
| cache buffers chains | 698,231,997 | 1,394,909 | 10,173 | 1,385,399 | 0 | 0 | 0 |
| session allocation | 19,521,250 | 685,814 | 57,723 | 629,338 | 0 | 0 | 0 |
| enqueue hash chains | 51,233,672 | 184,793 | 1,657 | 183,205 | 0 | 0 | 0 |
| redo allocation | 30,701,879 | 73,881 | 91 | 73,799 | 0 | 0 | 0 |
| mostly latch-free SCN | 1,254,255 | 71,716 | 113 | 71,604 | 0 | 0 | 0 |
| library cache | 10,439,897 | 52,171 | 283 | 51,899 | 0 | 0 | 0 |
| undo global data | 49,290,255 | 41,078 | 283 | 40,814 | 0 | 0 | 0 |
| session idle bit | 214,418,726 | 28,335 | 262 | 28,083 | 0 | 0 | 0 |
| enqueues | 9,852,464 | 27,037 | 841 | 26,230 | 0 | 0 | 0 |
| messages | 3,844,660 | 25,985 | 57 | 25,928 | 0 | 0 | 0 |
| redo writing | 4,632,969 | 7,221 | 79 | 7,145 | 0 | 0 | 0 |
| lgwr LWN SCN | 1,184,768 | 7,165 | 1 | 7,164 | 0 | 0 | 0 |
| simulator lru latch | 2,292,839 | 5,484 | 19 | 5,465 | 0 | 0 | 0 |
| resmgr:free threads list | 3,041 | 2,774 | 2,793 | 1 | 0 | 0 | 0 |
| object queue header operation | 10,713,623 | 2,633 | 30 | 2,603 | 0 | 0 | 0 |
| In memory undo latch | 40,626,363 | 1,821 | 1,646 | 263 | 0 | 0 | 0 |
| cache buffers lru chain | 4,578,067 | 1,453 | 61 | 1,392 | 0 | 0 | 0 |
| checkpoint queue latch | 8,894,121 | 957 | 3 | 954 | 0 | 0 | 0 |
| simulator hash latch | 22,594,380 | 707 | 1 | 706 | 0 | 0 | 0 |
| Consistent RBA | 1,177,333 | 233 | 4 | 229 | 0 | 0 | 0 |
| parameter table allocation management | 1,849 | 141 | 107 | 38 | 0 | 0 | 0 |
| session state list latch | 10,218 | 62 | 55 | 11 | 0 | 0 | 0 |
| shared pool | 71,942 | 52 | 1 | 51 | 0 | 0 | 0 |
| process allocation | 2,499 | 27 | 27 | 0 | 0 | 0 | 0 |
| active service list | 8,582 | 5 | 1 | 4 | 0 | 0 | 0 |
这其中另外一个问题是 resmgr:active threads 资源管理的竞争极高，虽然数据库中没有显示的设置任何资源计划。
后来这个问题通过设置隐含参数禁用资源计划得以解决。