We are using Oracle 10g on Linux platform. We received ORA-00018: maximum number of sessions exceeded error for about 30 minutes. Below is the AWR report.
As per report below, sessions are not getting released on time due to log file sync wait event which resulted in the ORA-00018 error. The number of commits are same if we compare this report to an old report. They why we are getting so much log file sync wait events. The log file parallel write wait event has an average wait of about 5 ms. I think this means there is no problem with the Disc. Can any body help me to diagnose this? We are using RAC with 3 instances. But the application that generated the ORA-00018 error only use the instance 1 from which this report has been generated.
| DB Name | DB Id | Instance | Inst num | Release | RAC | Host |
|---|
| | 1 | 1 | 10.2.0.4.0 | YES | |
| Snap Id | Snap Time | Sessions | Cursors/Session |
|---|
| Begin Snap: | 58066 | 06-May-15 19:00:11 | 229 | 29.3 |
| End Snap: | 58068 | 06-May-15 21:00:05 | 231 | 24.5 |
| Elapsed: | | 119.90 (mins) | | |
| DB Time: | | 2,520.28 (mins) | | |
Report Summary
Cache Sizes
| Begin | End | | |
|---|
| Buffer Cache: | 6,800M | 6,800M | Std Block Size: | 8K |
| Shared Pool Size: | 1,280M | 1,280M | Log Buffer: | 47,100K |
Load Profile
| Per Second | Per Transaction |
|---|
| Redo size: | 516,815.96 | 20,562.56 |
| Logical reads: | 12,965.27 | 515.85 |
| Block changes: | 2,681.93 | 106.71 |
| Physical reads: | 25.01 | 1.00 |
| Physical writes: | 58.77 | 2.34 |
| User calls: | 175.28 | 6.97 |
| Parses: | 82.86 | 3.30 |
| Hard parses: | 0.72 | 0.03 |
| Sorts: | 6.79 | 0.27 |
| Logons: | 0.46 | 0.02 |
| Executes: | 297.84 | 11.85 |
| Transactions: | 25.13 | |
| % Blocks changed per Read: | 20.69 | Recursive Call %: | 77.07 |
| Rollback per transaction %: | 7.20 | Rows per Sort: | 283.56 |
Instance Efficiency Percentages (Target 100%)
| Buffer Nowait %: | 99.91 | Redo NoWait %: | 100.00 |
| Buffer Hit %: | 99.82 | In-memory Sort %: | 100.00 |
| Library Hit %: | 99.39 | Soft Parse %: | 99.14 |
| Execute to Parse %: | 72.18 | Latch Hit %: | 99.97 |
| Parse CPU to Parse Elapsd %: | 60.76 | % Non-Parse CPU: | 98.45 |
Shared Pool Statistics
| Begin | End |
|---|
| Memory Usage %: | 70.46 | 71.76 |
| % SQL with executions>1: | 86.30 | 90.41 |
| % Memory for SQL w/exec>1: | 87.29 | 89.45 |
Top 5 Timed Events
| Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
|---|
| log file sync | 206,755 | 82,537 | 399 | 54.6 | Commit |
| gc buffer busy | 68,335 | 65,677 | 961 | 43.4 | Cluster |
| enq: CF - contention | 8,117 | 2,410 | 297 | 1.6 | Other |
| gc cr block busy | 1,805 | 2,186 | 1,211 | 1.4 | Cluster |
| CPU time | | 1,434 | | .9 | |
RAC Statistics
| Begin | End |
|---|
| Number of Instances: | 3 | 3 |
Global Cache Load Profile
| Per Second | Per Transaction |
|---|
| Global Cache blocks received: | 2.58 | 0.10 |
| Global Cache blocks served: | 0.90 | 0.04 |
| GCS/GES messages received: | 57.45 | 2.29 |
| GCS/GES messages sent: | 83.63 | 3.33 |
| DBWR Fusion writes: | 0.05 | 0.00 |
| Estd Interconnect traffic (KB) | 55.40 | |
Global Cache Efficiency Percentages (Target local+remote 100%)
| Buffer access - local cache %: | 99.80 |
| Buffer access - remote cache %: | 0.02 |
| Buffer access - disk %: | 0.18 |
Global Cache and Enqueue Services - Workload Characteristics
| Avg global enqueue get time (ms): | 5.6 |
| Avg global cache cr block receive time (ms): | 702.9 |
| Avg global cache current block receive time (ms): | 1.3 |
| Avg global cache cr block build time (ms): | 0.0 |
| Avg global cache cr block send time (ms): | 0.0 |
| Global cache log flushes for cr blocks served %: | 0.4 |
| Avg global cache cr block flush time (ms): | 26,721.4 |
| Avg global cache current block pin time (ms): | 561.0 |
| Avg global cache current block send time (ms): | 0.0 |
| Global cache log flushes for current blocks served %: | 1.5 |
| Avg global cache current block flush time (ms): | 10.0 |
Global Cache and Enqueue Services - Messaging Statistics
| Avg message sent queue time (ms): | 0.1 |
| Avg message sent queue time on ksxp (ms): | 0.4 |
| Avg message received queue time (ms): | 0.2 |
| Avg GCS message process time (ms): | 0.0 |
| Avg GES message process time (ms): | 0.0 |
| % of direct sent messages: | 22.52 |
| % of indirect sent messages: | 24.97 |
| % of flow controlled messages: | 52.51 |