| Snap Id | Snap Time | Sessions | Cursors/Session | |
|---|---|---|---|---|
| Begin Snap: | 4512 | 17-Jul-13 10:00:04 | 24 | 2.0 |
| End Snap: | 4513 | 17-Jul-13 11:00:06 | 41 | 19.5 |
| Elapsed: | 60.03 (mins) | |||
| DB Time: | 70.09 (mins) |
Load Profile
| Per Second | Per Transaction | |
|---|---|---|
| Redo size: | 2,061,966.68 | 840,400.17 |
| Logical reads: | 28,242.96 | 11,511.05 |
| Block changes: | 4,749.98 | 1,935.96 |
| Physical reads: | 1,990.22 | 811.16 |
| Physical writes: | 244.15 | 99.51 |
| User calls: | 303.93 | 123.87 |
| Parses: | 80.30 | 32.73 |
| Hard parses: | 0.02 | 0.01 |
| Sorts: | 91.59 | 37.33 |
| Logons: | 0.04 | 0.02 |
| Executes: | 132.32 | 53.93 |
| Transactions: | 2.45 |
Instance Efficiency Percentages (Target 100%)
| Buffer Nowait %: | 99.67 | Redo NoWait %: | 99.95 |
| Buffer Hit %: | 96.06 | In-memory Sort %: | 100.00 |
| Library Hit %: | 99.98 | Soft Parse %: | 99.97 |
| Execute to Parse %: | 39.31 | Latch Hit %: | 97.11 |
| Parse CPU to Parse Elapsd %: | 2.80 | % Non-Parse CPU: | 99.69 |
soft parsing of SQL statements was consuming significant database time.
| Statistic |
Begin Value |
End Value |
| session pga memory max |
15,053,354,632 |
15,249,597,664 |
| session cursor cache count |
383,435 |
386,192 |
| session uga memory |
4.4E+13 |
4.5E+13 |
| opened cursors current |
48 |
800 |
| logons current |
24 |
41 |
| session uga memory max |
44,030,880,616 |
44,314,080,728 |
| session pga memory |
13,478,131,336 |
13,655,531,424 |
给出的建议如下：
ACTION: Consider increase parameter open_cursors more then 300.
increase parameter session_cached_cursors to 150
now parameter details: open_cursors=300 session_cached_cursors=100
IMPACT: 3.9% impact (163 seconds)
此外还发现了一些top sql,但是没有明显的调优方向，我没有马上动手，我觉得这些需要以上的系统级配置生效以后需要做持续性的调整。
lob的调优还需要多补补。在后续章节分享。
