Hello Experts,
Could you help to identify and evaluate performance bottlenecks from the below statistics.
Below is AWR report excerpt of RAC instance,
>> Does the row lock waits on indexes seem to be the factor ? if so, could you suggest improvement actions.
>> could we reduce the buffer busy waits by further tuning of IX1_PCWAUDITLOG, IX1_PCWREQRESP & LOBSEG_NEW (image) , can we reduce the row lock waits on indexes.
================================================================================================================================================
| DB Name | DB Id | Instance | Inst num | Startup Time | Release | RAC |
|---|
| | | 1 | 25-Nov-18 19:11 | 11.2.0.4.0 | YES |
| | Platform | CPUs | Cores | Sockets | Memory (GB) |
|---|
| AIX-Based Systems (64-bit) | 192 | 48 | | 1058.00 |
Report Summary
Load Profile
| Per Second | Per Transaction | Per Exec | Per Call |
|---|
| DB Time(s): | 18.7 | 0.0 | 0.00 | 0.00 |
| DB CPU(s): | 3.6 | 0.0 | 0.00 | 0.00 |
| Redo size (bytes): | 4,016,393.3 | 4,675.4 | | |
| Logical read (blocks): | 63,810.6 | 74.3 | | |
| Block changes: | 12,951.4 | 15.1 | | |
| Physical read (blocks): | 46.4 | 0.1 | | |
| Physical write (blocks): | 686.0 | 0.8 | | |
| Read IO requests: | 46.4 | 0.1 | | |
| Write IO requests: | 412.2 | 0.5 | | |
| Read IO (MB): | 0.4 | 0.0 | | |
| Write IO (MB): | 5.4 | 0.0 | | |
| Global Cache blocks received: | 2,725.4 | 3.2 | | |
| Global Cache blocks served: | 2,762.4 | 3.2 | | |
| User calls: | 23,692.6 | 27.6 | | |
| Parses (SQL): | 6,746.4 | 7.9 | | |
| Hard parses (SQL): | 0.0 | 0.0 | | |
| SQL Work Area (MB): | 0.2 | 0.0 | | |
| Logons: | 0.1 | 0.0 | | |
| Executes (SQL): | 9,634.6 | 11.2 | | |
| Rollbacks: | 253.4 | 0.3 | | |
| Transactions: |
Instance Efficiency Percentages (Target 100%)
| Buffer Nowait %: | 98.37 | Redo NoWait %: | 100.00 |
| Buffer Hit %: | 99.93 | In-memory Sort %: | 100.00 |
| Library Hit %: | 99.89 | Soft Parse %: | 100.00 |
| Execute to Parse %: | 29.98 | Latch Hit %: | 99.66 |
| Parse CPU to Parse Elapsd %: | 43.81 | % Non-Parse CPU: | 93.20 |
Top 10 Foreground Events by Total Wait Time
| Event | Waits | Total Wait Time (sec) | Wait Avg(ms) | % DB time | Wait Class |
|---|
| DB CPU | | 6533.5 | | 19.3 | |
| reliable message | 2,177,163 | 5135.6 | 2 | 15.2 | Other |
| gc buffer busy release | 419,492 | 3654 | 9 | 10.8 | Cluster |
| gc buffer busy acquire | 1,086,379 | 3252.1 | 3 | 9.6 | Cluster |
| gc current block 2-way | 2,093,433 | 2066.9 | 1 | 6.1 | Cluster |
| gc current block busy | 564,702 | 1934.7 | 3 | 5.7 | Cluster |
| gc cr block 2-way | 1,814,301 | 1888.8 | 1 | 5.6 | Cluster |
| buffer busy waits | 479,459 | 1328.4 | 3 | 3.9 | Concurrency |
| gc cr block busy | 427,998 | 1302.7 | 3 | 3.9 | Cluster |
| enq: TX - index contention | 154,438 | 1103.9 | 7 | 3.3 | Concurrency |
Wait Classes by Total Wait Time
| Wait Class | Waits | Total Wait Time (sec) | Avg Wait (ms) | % DB time | Avg Active Sessions |
|---|
| Cluster | 6,956,644 | 14,734 | 2 | 43.6 | 8.2 |
| DB CPU | | 6,533 | | 19.3 | 3.6 |
| Other | 15,371,149 | 6,123 | 0 | 18.1 | 3.4 |
| Concurrency | 4,777,750 | 2,567 | 1 | 7.6 | 1.4 |
| System I/O | 1,086,346 | 686 | 1 | 2.0 | 0.4 |
| Commit | 213,151 | 534 | 3 | 1.6 | 0.3 |
| Network | 30,755,577 | 139 | 0 | .4 | 0.1 |
| Configuration | 68,066 | 134 | 2 | .4 | 0.1 |
| User I/O | 89,424 | 98 | 1 | .3 | 0.1 |
| Application | 2,681 | 3 | 1 | .0 | 0.0 |
| Scheduler | 1,751 | 0 | 0 | .0 | 0.0 |
Time Model Statistics
- Total time in database user-calls (DB Time): 33767.2s
| Statistic Name | Time (s) | % of DB Time |
|---|
| sql execute elapsed time | 22,997.67 | 68.11 |
| DB CPU | 6,533.46 | 19.35 |
| parse time elapsed | 1,061.19 | 3.14 |
| sequence load elapsed time | 26.75 | 0.08 |
| repeated bind elapsed time | 0.37 | 0.00 |
| hard parse (sharing criteria) elapsed time | 0.10 | 0.00 |
| hard parse elapsed time | 0.10 | 0.00 |
| hard parse (bind mismatch) elapsed time | 0.05 | 0.00 |
| connection management call elapsed time | 0.04 | 0.00 |
| DB time | 33,767.24 | |
| background elapsed time | 3,831.12 | |
| background cpu time | 1,692.09 | |
Operating System Statistics
| Statistic | Value | End Value |
|---|
| AVG_BUSY_TIME | 44,420 | |
| AVG_IDLE_TIME | 135,934 | |
| AVG_IOWAIT_TIME | 1,258 | |
| AVG_SYS_TIME | 12,988 | |
| AVG_USER_TIME | 31,336 | |
| BUSY_TIME | 8,548,155 | |
| IDLE_TIME | 26,120,804 | |
| IOWAIT_TIME | 258,649 | |
| SYS_TIME | 2,512,500 | |
| USER_TIME | 6,035,655 | |
| LOAD | 52 | 62 |
| OS_CPU_WAIT_TIME | 10,053,900 | |
| RSRC_MGR_CPU_WAIT_TIME | 204 | |
| VM_IN_BYTES | 0 | |
| VM_OUT_BYTES | 0 | |
| PHYSICAL_MEMORY_BYTES | 1,136,018,849,792 | |
| NUM_CPUS | 192 | |
| NUM_CPU_CORES | 48 | |
| NUM_LCPUS | 192 | |
| NUM_VCPUS | 48 | |
| GLOBAL_RECEIVE_SIZE_MAX | 4,194,304 | |
| GLOBAL_SEND_SIZE_MAX | 4,194,304 | |
| TCP_RECEIVE_SIZE_DEFAULT | 16,384 | |
| TCP_RECEIVE_SIZE_MAX | 9,223,372,036,854,775,807 | |
| TCP_RECEIVE_SIZE_MIN | 4,096 | |
| TCP_SEND_SIZE_DEFAULT | 16,384 | |
| TCP_SEND_SIZE_MAX | 9,223,372,036,854,775,807 | |
| TCP_SEND_SIZE_MIN | 4,096 | |
Foreground Wait Class
- Captured Time accounts for 89.0% of Total DB time 33,767.24 (s)
- Total FG Wait Time: 23,509.19 (s) DB CPU time: 6,533.46 (s)
| Wait Class | Waits | %Time -outs | Total Wait Time (s) | Avg wait (ms) | %DB time |
|---|
| Cluster | 6,954,105 | 0 | 14,732 | 2 | 43.63 |
| DB CPU | | | 6,533 | | 19.35 |
| Other | 2,741,166 | 14 | 5,309 | 2 | 15.72 |
| Concurrency | 1,011,613 | 0 | 2,561 | 3 | 7.58 |
| Commit | 213,147 | 0 | 534 | 3 | 1.58 |
| Network | 30,755,546 | 0 | 139 | 0 | 0.41 |
| Configuration | 68,052 | 38 | 134 | 2 | 0.40 |
| User I/O | 88,677 | 0 | 98 | 1 | 0.29 |
| Application | 2,679 | 0 | 3 | 1 | 0.01 |
| Scheduler | 1,751 | 0 | 0 | 0 | 0.00 |
| System I/O | 4 | 0 | 0 | 1 | 0.00 |
Foreground Wait Events
| Event | Waits | %Time -outs | Total Wait Time (s) | Avg wait (ms) | Waits /txn |
|
|---|