I'm looking at a production system today (64 bit Linux 11.2.0.3 running in VMWare ESX server) and I'm puzzled by the AWR report for today.
Top 5 Timed Foreground Events
| Event | Waits | Time(s) | Avg wait (ms) | % DB time | Wait Class |
|---|
| read by other session | 31,441,112 | 257,807 | 8 | 46.80 | User I/O |
| db file sequential read | 47,854,402 | 234,627 | 5 | 42.59 | User I/O |
| enq: TX - row lock contention | 143,551 | 41,435 | 289 | 7.52 | Application |
| DB CPU | | 19,726 | | 3.58 | |
| log file sync | 504,636 | 812 | 2 | 0.15 | Commit |
Looks to me like this is all disk I/O related. Less than 4% CPU.
Top queries are mostly I/O:
| Elapsed Time (s) | Executions | Elapsed Time per Exec (s) | %Total | %CPU | %IO | SQL Id | SQL Module | SQL Text |
|---|
| 299,751.89 | 91 | 3,293.98 | 54.41 | 0.81 | 99.32 | 1grysv9w7zdyu | LBRPRD-00001d | select kvisummaryintid, seq_nu... |
| 64,741.57 | 1,193 | 54.27 | 11.75 | 2.03 | 98.61 | b1hdcrcy02d0q | LBRPRD-000018 | select * from ( select k.* fro... |
| 42,836.93 | 0 | | 7.78 | 1.95 | 98.90 | 2ypct9gq7n9cc | LBRPRD-000019 | SELECT u.repgrp, '' as highlvl... |
| 36,194.38 | 232 | 156.01 | 6.57 | 0.00 | 0.00 | 0nn82pybvg9ma | LBRPRD-000001 | update kvi_summary set last_up... |
| 33,377.76 | 1 | 33,377.76 | 6.06 | 1.71 | 99.04 | 4xf9205gbdcc7 | LBRPRD-00001e | SELECT u.repgrp, '' as highlvl... |
| 28,920.34 | 0 | | 5.25 | 1.75 | 99.05 | b44xz4mkkpt5y | LBRPRD-000017 | SELECT u.repgrp, '' as highlvl... |
| 12,012.59 | 11,719 | 1.03 | 2.18 | 11.85 | 88.59 | c2sq2a5577quf | LBRPRD-000011 | select report_date from ( sel... |
| 2,601.18 | 160,891 | 0.02 | 0.47 | 2.42 | 96.63 | 9ynhj7wq3htwk | LBRPRD-000400 | insert into discrete (worktype... |
| 2,101.32 | 90,929 | 0.02 | 0.38 | 2.01 | 16.55 | 3d53vyphc1uu2 | LBRPRD-000012 | delete from discrete where kvi... |
| 1,784.89 | 38 | 46.97 | 0.32 | 1.37 | 99.31 | 6cvukgj13hzxt | LBRPRD-000013 | select * from ( Select Kvi_sum... |
But buffer cache hit ratio high:
Instance Efficiency Percentages (Target 100%)
| Buffer Nowait %: | 94.40 | Redo NoWait %: | 100.00 |
| Buffer Hit %: | 96.40 | In-memory Sort %: | 100.00 |
| Library Hit %: | 99.81 | Soft Parse %: | 97.59 |
| Execute to Parse %: | 96.21 | Latch Hit %: | 99.43 |
| Parse CPU to Parse Elapsd %: | 30.10 | % Non-Parse CPU: | 98.95 |
Weird. I figure we might want to add memory or of course tune the queries. But, I'm puzzled why the hit cache number is high.
Here is the SGA advisor on the memory:
SGA Target Advisory
| SGA Target Size (M) | SGA Size Factor | Est DB Time (s) | Est Physical Reads |
|---|
| 4,096 | 0.25 | 377,544,975 | 37,577,469,651 |
| 6,144 | 0.38 | 175,540,881 | 23,924,145,887 |
| 8,192 | 0.50 | 30,245,694 | 3,777,580,176 |
| 10,240 | 0.63 | 22,799,892 | 2,745,179,833 |
| 12,288 | 0.75 | 15,676,179 | 1,757,422,511 |
| 14,336 | 0.88 | 12,839,908 | 1,364,051,634 |
| 16,384 | 1.00 | 11,798,133 | 1,219,754,658 |
| 18,432 | 1.13 | 10,238,420 | 1,003,492,157 |
| 20,480 | 1.25 | 9,071,584 | 841,630,714 |
| 22,528 | 1.38 | 8,602,019 | 776,495,815 |
| 24,576 | 1.50 | 7,830,421 | 669,645,307 |
| 26,624 | 1.63 | 7,231,076 | 586,336,064 |
| 28,672 | 1.75 | 6,978,596 | 551,451,081 |
| 30,720 | 1.88 | 6,977,416 | 551,451,081 |
| 32,768 | 2.00 | 6,977,416 | 551,451,081 |
I guess bump the SGA up to 28 gigabytes would help, but it seems like something is wrong with the numbers if the buffer cache hit ratio is 94% but everything else points to I/O.
Also, it isn't doing direct path reads that I can tell - seems to be db file sequential reads so it isn't bypassing the buffer cache.
I keep digging into it but I thought I would throw this out there if anyone has ideas.
- Bobby