Dear Mr Connor,
my oracle database load is particularly high,and I don't connect it ,finally application developer reboot their application server to solve it!
I export an AWR report for failure diagnosis in peroid time,
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst num Startup Time Release RAC
ORCL28 298590189 orcl28 1 05-Jun-17 15:06 11.2.0.4.0 NO
Host Name Platform CPUs Cores Sockets Memory (GB)
orcl28 Linux x86 64-bit 192 96 8 511.90
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 10188 27-Jun-17 15:00:02 3722 .8
End Snap: 10189 27-Jun-17 16:11:13 5461 .4
Elapsed: 71.18 (mins)
DB Time: 82,459.71 (mins)
Report Summary
Load Profile
Per Second Per Transaction Per Exec Per Call
DB Time(s): 1,158.5 39.3 2.86 0.83
DB CPU(s): 33.0 1.1 0.08 0.02
Redo size (bytes): 134,012.0 4,549.7
Logical read (blocks): 82,632.5 2,805.3
Block changes: 735.7 25.0
Physical read (blocks): 21.8 0.7
Physical write (blocks): 52.1 1.8
Read IO requests: 21.8 0.7
Write IO requests: 40.1 1.4
Read IO (MB): 0.2 0.0
Write IO (MB): 0.4 0.0
User calls: 1,392.1 47.3
Parses (SQL): 316.7 10.8
Hard parses (SQL): 0.0 0.0
SQL Work Area (MB): 20.6 0.7
Logons: 5.0 0.2
Executes (SQL): 405.7 13.8
Rollbacks: 0.2 0.0
Transactions: 29.5
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.98 Redo NoWait %: 99.99
Buffer Hit %: 99.97 In-memory Sort %: 100.00
Library Hit %: 99.93 Soft Parse %: 99.99
Execute to Parse %: 21.92 Latch Hit %: 99.96
Parse CPU to Parse Elapsd %: 2.77 % Non-Parse CPU: 57.96
Top 10 Foreground Events by Total Wait Time
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
library cache: mutex X 54,682 3837.3K 70176 77.6 Concurrency
latch: row cache objects 4,029 544.4K 135120 11.0 Concurrency
latch free 6,411 492.8K 76863 10.0 Other
DB CPU 141.1K 2.9
latch: enqueue hash chains 26,169 114.3K 4367 2.3 Other
wait list latch free 1,924 110.4K 57370 2.2 Other
enq: TA - contention 1,191 99.8K 83793 2.0 Other
SQL*Net more data to client 1,255,356 88.4K 70 1.8 Network
latch: shared pool 26,672 77.8K 2917 1.6 Concurrency
log file sync 115,577 37.9K 328 .8 Commit
Wait Classes by Total Wait Time
Wait Class Waits Total Wait Time (sec) Avg Wait (ms) % DB time Avg Active Sessions
Concurrency 143,022 4,554,552 31845 92.1 1066.5
Other 80,764 830,300 10281 16.8 194.4
DB CPU 141,082 2.9 33.0
Network 6,873,781 89,840 13 1.8 21.0
Commit 115,580 37,879 328 .8 8.9
System I/O 291,812 29,329 101 .6 6.9
Configuration 4,151 3,551 855 .1 0.8
User I/O 98,298 2,229 23 .0 0.5
Application 2,438 229 94 .0 0.1
Host CPU
CPUs Cores Sockets Load Average Begin Load Average End %User %System %WIO %Idle
192 96 8 5.08 27.82 1.5 25.9 0.2 72.6
Instance CPU
%Total CPU %Busy CPU %DB time waiting for CPU (Resource Manager)
17.9 65.3 0.0
IO Profile
Read+Write Per Second Read per Second Write Per Second
Total Requests: 95.1 26.0 69.1
Database Requests: 61.9 21.8 40.1
Optimized Requests: 0.0 0.0 0.0
Redo Requests: 26.3 0.2 26.1
Total (MB): 1.5 0.4 1.1
Database (MB): 0.6 0.2 0.4
Optimized Total (MB): 0.0 0.0 0.0
Redo (MB): 0.3 0.2 0.1
Database (blocks): 73.9 21.8 52.1
Via Buffer Cache (blocks): 71.9 21.0 50.9
Direct (blocks): 2.0 0.8 1.2
Memory Statistics
Begin End
Host Mem (MB): 524,181.6 524,181.6
SGA use (MB): 153,600.0 153,600.0
PGA use (MB): 9,455.3 10,238.1
% Host Mem used for SGA+PGA: 31.11 31.26
Cache Sizes
Begin End
Buffer Cache: 124,928M 124,928M Std Block Size: 8K
Shared Pool Size: 17,207M 17,166M Log Buffer: 344,184K
Please help me for analysing my oracle database,and How can I solve this problem?
Thanks!
1400 user calls per second should give you a reasonable idea.... sounds like a busy system.
Add to that - your big overheads are Concurrency, in particular, library cache: mutex X
Parse calls at 300 per seconds sounds like lots of little, non-shared SQL's being thrown at the system.
So first place to look - head down through AWR report and focus on the SQL statement section - look for high executions, high buffer gets, high disk reads, and high parse counts. That is where you should start.