Hi,
I'm facing lots of SQL*Net more data from dblink waits while refreshing materialized view from a remote database using dblink.
Report Summary
Top ADDM Findings by Average Active Sessions
| Finding Name | Avg active sessions of the task | Percent active sessions of finding | Task Name | Begin Snap Time | End Snap Time |
|---|
| Top SQL Statements | 2.89 | 33.28 | ADDM:1123398265_1_5143 | 21-Dec-15 23:30 | 22-Dec-15 00:30 |
| Log File Switches | 2.89 | 16.59 | ADDM:1123398265_1_5143 | 21-Dec-15 23:30 | 22-Dec-15 00:30 |
| Unusual "Network" Wait Event | 2.89 | 12.37 | ADDM:1123398265_1_5143 | 21-Dec-15 23:30 | 22-Dec-15 00:30 |
| "User I/O" wait Class | 2.89 | 9.65 | ADDM:1123398265_1_5143 | 21-Dec-15 23:30 | 22-Dec-15 00:30 |
| PL/SQL Execution | 2.89 | 7.07 | ADDM:1123398265_1_5143 | 21-Dec-15 23:30 | 22-Dec-15 00:30 |
Top 10 Foreground Events by Total Wait Time
| Event | Waits | Total Wait Time (sec) | Wait Avg(ms) | % DB time | Wait Class |
|---|
| DB CPU | | 1801.4 | | 17.3 | |
| log file switch (checkpoint incomplete) | 66 | 1728.9 | 26195.62 | 16.6 | Configuration |
| SQL*Net message from dblink | 4,675 | 1289 | 275.73 | 12.4 | Network |
| db file sequential read | 125,853 | 839 | 6.67 | 8.1 | User I/O |
| SQL*Net more data from dblink | 352,254 | 369.4 | 1.05 | 3.5 | Network |
| free buffer waits | 116 | 335.7 | 2894.35 | 3.2 | Configuration |
| log buffer space | 1,150 | 183.3 | 159.40 | 1.8 | Configuration |
| db file scattered read | 6,310 | 166.2 | 26.34 | 1.6 | User I/O |
| latch: shared pool | 1,007 | 12.2 | 12.12 | .1 | Concurrency |
| log file sync | 155 | 5.5 | 35.16 | .1 | Commit |
Wait Classes by Total Wait Time
| Wait Class | Waits | Total Wait Time (sec) | Avg Wait (ms) | % DB time | Avg Active Sessions |
|---|
| Configuration | 1,380 | 2,657 | 1925.35 | 25.5 | 0.7 |
| DB CPU | | 1,801 | | 17.3 | 0.5 |
| Network | 362,358 | 1,658 | 4.58 | 15.9 | 0.5 |
| System I/O | 108,662 | 1,655 | 15.23 | 15.9 | 0.5 |
| User I/O | 134,635 | 1,014 | 7.53 | 9.7 | 0.3 |
| Other | 25,626 | 838 | 32.70 | 8.0 | 0.2 |
| Application | 40 | 220 | 5499.96 | 2.1 | 0.1 |
| Concurrency | 1,556 | 73 | 46.70 | .7 | 0.0 |
| Commit | 173 | 6 | 36.09 | .1 | 0.0 |
| Scheduler | 2 | 0 | 0.03 | .0 | 0.0 |
At times while refreshing one Mview takes more than 2 hours and other times it completes in 2 minutes.
Kindly suggest the solution.
DB version : 12.1
OS: AIX 6.1
Regards,
Gaurav Dutt