Dear All,
I am facing issue regarding latches , due to multiple latches I am confiused from where I need to start
Top 10 Foreground Events by Total Wait Time
| Event | Waits | Total Wait Time (sec) | Wait Avg(ms) | % DB time | Wait Class |
|---|
| latch free | 98,656 | 854.4K | 8660 | 23.1 | Other |
| DB CPU | | 841.4K | | 22.7 | |
| latch: shared pool | 53,967 | 214.2K | 3970 | 5.8 | Concurrency |
| library cache: mutex X | 96,411 | 81.3K | 843 | 2.2 | Concurrency |
| latch: cache buffers chains | 2,088,031 | 69.3K | 33 | 1.9 | Concurrency |
| latch: row cache objects | 10,164 | 36.4K | 3578 | 1.0 | Concurrency |
| log file sync | 1,110,364 | 35.1K | 32 | .9 | Commit |
| db file sequential read | 14,100,987 | 23.5K | 2 | .6 | User I/O |
| direct path read | 17,884,887 | 10.5K | 1 | .3 | User I/O |
| read by other session | 491,257 | 9177.6 | 19 | .2 | User I/O |
Based on above stats I checked "Parse CPU to Parse Elapse time is less the 1%.
Time Model Statistics
- Total time in database user-calls (DB Time): 3702660.6s
- Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
- Ordered by % or DB time desc, Statistic name
| Statistic Name | Time (s) | % of DB Time |
|---|
| sql execute elapsed time | 2,605,630.07 | 70.37 |
| DB CPU | 841,391.69 | 22.72 |
| parse time elapsed | 241,526.31 | 6.52=> |
| hard parse elapsed time | 67,879.47 | 1.83=> |
| connection management call elapsed time | 50,863.35 | 1.37 |
| RMAN cpu time (backup/restore) | 39,768.96 | 1.07 |
| PL/SQL compilation elapsed time | 32,330.40 | 0.87 |
| PL/SQL execution elapsed time | 24,281.43 | 0.66 |
| hard parse (sharing criteria) elapsed time | 5,872.98 | 0.16 |
| sequence load elapsed time | 897.58 | 0.02 |
| inbound PL/SQL rpc elapsed time | 156.19 | 0.00 |
| hard parse (bind mismatch) elapsed time | 64.34 | 0.00 |
| repeated bind elapsed time | 49.92 | 0.00 |
| failed parse elapsed time | 5.96 | 0.00 |
| DB time | 3,702,660.56 | |
| background elapsed time | 123,822.08 | |
| background cpu time | 47,388.92 |
Latch Sleep Breakdown
| Latch Name | Get Requests | Misses | Sleeps | Spin Gets |
|---|
| cache buffers chains | 176,297,452,218 | 2,496,743,772 | 2,354,985 | 2,494,760,632 |
| row cache objects | 1,508,567,473 | 2,609,603 | 10,742 | 2,599,488 |
| simulator hash latch | 5,862,340,896 | 405,550 | 88,499 | 318,007 |
| shared pool | 160,588,674 | 293,215 | 72,899 | 235,551 |
| mostly latch-free SCN | 9,546,962 | 263,181 | 164 | 263,020 |
| messages | 73,965,956 | 84,377 | 200 | 84,183 |
| redo allocation | 156,542,911 | 76,936 | 188 | 76,748 |
| process allocation | 810,407 | 57,969 | 137,852 | 4,553 |
| call allocation | 1,982,663 | 28,040 | 18,515 | 10,674 |
| object queue header operation | 601,140,032 | 20,557 | 424 | 20,179 |
| cache buffer handles | 14,298,842 | 16,079 | 29 | 16,054 |
| active checkpoint queue latch | 1,676,587 | 15,550 | 20 | 15,530 |
| In memory undo latch | 320,133,035 | 14,393 | 2,069 | 12,368 |
| simulator lru latch | 7,896,657 | 12,821 | 483 | 12,342 |
| parameter table management | 7,552,428 | 12,723 | 9,207 | 4,801 |
I need help in identifying root cause for same problem.'
Regards