Dear all,
I'm assessing performance in a 12.1.0.2 database and the pattern that I'm seeing is not very clear for me. As you can see in top 10 wait times there are high waits for concurrency, mainly library cache lock: mutex X.
Top 10 Foreground Events by Total Wait Time
| Event | Waits | Total Wait Time (sec) | Wait Avg(ms) | % DB time | Wait Class |
|---|
| DB CPU | | 6361.6 | | 31.8 | |
| library cache: mutex X | 108,488 | 1122.9 | 10.35 | 5.6 | Concurrency |
| log file sync | 109,964 | 594.7 | 5.41 | 3.0 | Commit |
| latch: row cache objects | 53,204 | 586 | 11.01 | 2.9 | Concurrency |
| cursor: mutex X | 251,559 | 499.7 | 1.99 | 2.5 | Concurrency |
| cursor: mutex S | 86,777 | 384 | 4.42 | 1.9 | Concurrency |
| buffer busy waits | 32,125 | 234.9 | 7.31 | 1.2 | Concurrency |
| enq: SQ - contention | 9,520 | 188.6 | 19.81 | .9 | Configuration |
| latch: call allocation | 22,652 | 168.3 | 7.43 | .8 | Other |
| cursor: pin S | 33,070 | 111.7 | 3.38 | .6 | Concurrency |
However, the SQL that is promoting library cache: mutex X is just a
SELECT UNIT FROM SECTION WHERE CODE = :B1
No reloads or invalidations, the high version count of this query is too low. Find below the ASH from that period:
| SQL ID | Plan Hash | Executions | % Activity | Event | % Event | Top Row Source | % Row Source | SQL Text | Container Name |
|---|
| 6pbbbumdmt35w | | 0 | 13.05 | CPU + Wait for CPU | 10.32 | SELECT STATEMENT | 6.58 | SELECT UNIT FROM SECTION WHERE.. | PRD |
6pbbbumdmt35w | | 0 | 13.05 | library cache: mutex X | 1.62 | SELECT STATEMENT | 1.28 | SELECT UNIT FROM SECTION WHERE... | PRD |
| dzsk004vfprp1 | 3769162526 | 142 | 7.92 | CPU + Wait for CPU | 7.92 | UNION-ALL | 1.39 | | PRD |
| f8ry92xjrqm3x | 2914122226 | 134 | 7.47 | CPU + Wait for CPU | 7.47 | HASH - GROUP BY | 3.07 | | PRD |
| bpmzta1v09vb7 | 1972600620 | 117 | 6.58 | CPU + Wait for CPU | 6.47 | SELECT STATEMENT | 5.02 | | PRD |
| 3c7fapy0fk5a5 | 1388734953 | 54 | 3.23 | CPU + Wait for CPU | 3.23 | SELECT STATEMENT | 3.18 | | PRD |
Any idea why so high library cache mutex X? Any bug?
Why the sum of all wait classes %DB time is only ~50% ? Where is database spending the other 50%?
Wait Classes by Total Wait Time
| Wait Class | Waits | Total Wait Time (sec) | Avg Wait (ms) | % DB time | Avg Active Sessions |
|---|
| DB CPU | | 6,362 | | 31.8 | 1.2 |
| Concurrency | 582,938 | 3,073 | 5.27 | 15.3 | 0.6 |
| Other | 198,001 | 792 | 4.00 | 4.0 | 0.1 |
| Commit | 110,000 | 595 | 5.41 | 3.0 | 0.1 |
| System I/O | 176,942 | 373 | 2.11 | 1.9 | 0.1 |
| Configuration | 9,684 | 190 | 19.63 | .9 | 0.0 |
| User I/O | 46,449 | 8 | 0.18 | .0 | 0.0 |
| Network | 870,987 | 1 | 0.00 | .0 | 0.0 |
| Application | 1,155 | 1 | 1.08 | .0 | 0.0 |
Thanks in advance