Oracle 11gR1
OL5
Hi ALL,
We are running loading (insert) programs via batch background job.
The first run with 300,000 rows was completed successfully in 1+ hrs,
The 2nd run with 200,000 rows hung and was terminated after 7 hrs.
How do I find the cause of the problem for the 2nd batch job?
Below are comparative AWR statistics for the two batch jobs.
Please help how to find the issue.
Thanks a lot.
Below are the two similar loading programs which have different performance behavior.
The one with more rows succeeded/ completed in 1+ hrs , but the second run hung and was terminated after 7 hrs.
Could there be locking issue?
1) Data insert loading 1
DATE: 23-JUL-19 -- successful process with 300k+ records
Start Time: 03:18 AM
End Time : 04:45 AM
2.) Data insert loading 2
DATE: 24-JUL-19 --- issue encountered with 200k+ records
Start Time: 01:50 AM -- terminated after 7 hrs
AWR of #1
==========
WORKLOAD REPOSITORY report for
| PROD | 157292119 | PROD | 1 | 24-Jun-19 05:06 | 11.1.0.7.0 | NO |
| prod.oracle.com | Linux x86 64-bit | 8 | 8 | 2 | 38.38 |
| Begin Snap: | 73119 | 23-Jul-19 03:00:31 | 298 | 25.3 |
| End Snap: | 73120 | 23-Jul-19 04:00:34 | 298 | 25.5 |
| Elapsed: | | 60.04 (mins) | | |
| DB Time: | | 40.97 (mins) | | |
Report Summary
Cache Sizes
| Buffer Cache: | 11,904M | 11,904M | Std Block Size: | 8K |
| Shared Pool Size: | 3,072M | 3,072M | Log Buffer: | 58,728K |
Load Profile
| DB Time(s): | 0.7 | 0.1 | 0.01 | 0.02 |
| DB CPU(s): | 0.7 | 0.1 | 0.01 | 0.02 |
| Redo size: | 350,876.9 | 59,102.6 | | |
| Logical reads: | 88,980.8 | 14,988.2 | | |
| Block changes: | 2,598.9 | 437.8 | | |
| Physical reads: | 4.9 | 0.8 | | |
| Physical writes: | 55.6 | 9.4 | | |
| User calls: | 28.4 | 4.8 | | |
| Parses: | 6.6 | 1.1 | | |
| Hard parses: | 0.1 | 0.0 | | |
| W/A MB processed: | 609,524.5 | 102,669.8 | | |
| Logons: | 0.1 | 0.0 | | |
| Executes: | 82.7 | 13.9 | | |
| Rollbacks: | 1.5 | 0.3 | | |
| Transactions: | 5.9 | | | |
Instance Efficiency Percentages (Target 100%)
| Buffer Nowait %: | 100.00 | Redo NoWait %: | 100.00 |
| Buffer Hit %: | 99.99 | In-memory Sort %: | 100.00 |
| Library Hit %: | 97.65 | Soft Parse %: | 98.69 |
| Execute to Parse %: | 91.99 | Latch Hit %: | 100.00 |
| Parse CPU to Parse Elapsd %: | 0.01 | % Non-Parse CPU: | 99.97 |
Shared Pool Statistics
| Memory Usage %: | 42.94 | 43.04 |
| % SQL with executions>1: | 93.85 | 96.38 |
| % Memory for SQL w/exec>1: | 91.99 | 96.47 |
Top 5 Timed Foreground Events
| DB CPU | | 2,426 | | 98.71 | |
| db file sequential read | 16,420 | 13 | 1 | 0.52 | User I/O |
| log file sync | 14,848 | 11 | 1 | 0.43 | Commit |
| SQL*Net more data from client | 52,628 | 1 | 0 | 0.05 | Network |
| local write wait | 1,182 | 1 | 1 | 0.05 | User I/O |
Host CPU (CPUs: 8 Cores: 8 Sockets: 2)
| 0.70 | 1.04 | 8.7 | 0.1 | 0.2 | 91.2 |
Instance CPU
Memory Statistics
| Host Mem (MB): | 39,299.7 | 39,299.7 |
| SGA use (MB): | 15,360.0 | 15,360.0 |
| PGA use (MB): | 2,047.3 | 2,042.4 |
| % Host Mem used for SGA+PGA: | 44.29 | 44.29 |
Main Report
================================ AWR of #2========
WORKLOAD REPOSITORY report for
| PROD | 157292119 | PROD | 1 | 24-Jun-19 05:06 | 11.1.0.7.0 | NO |
| prod.oracle.com | Linux x86 64-bit | 8 | 8 | 2 | 38.38 |
| Begin Snap: | 73141 | 24-Jul-19 01:00:20 | 305 | 23.9 |
| End Snap: | 73142 | 24-Jul-19 02:00:22 | 303 | 24.5 |
| Elapsed: | | 60.04 (mins) | | |
| DB Time: | | 9.88 (mins) | | |
Report Summary
Cache Sizes
| Buffer Cache: | 11,776M | 11,776M | Std Block Size: | 8K |
| Shared Pool Size: | 3,200M | 3,200M | Log Buffer: | 58,728K |
Load Profile
| DB Time(s): | 0.2 | 0.1 | 0.00 | 0.01 |
| DB CPU(s): | 0.2 | 0.1 | 0.00 | 0.01 |
| Redo size: | 14,286.5 | 5,654.5 | | |
| Logical reads: | 18,178.0 | 7,194.7 | | |
| Block changes: | 100.3 | 39.7 | | |
| Physical reads: | 2.3 | 0.9 | | |
| Physical writes: | 2.3 | 0.9 | | |
| User calls: | 22.6 | 9.0 | | |
| Parses: | 6.9 | 2.7 | | |
| Hard parses: | 0.1 | 0.0 | | |
| W/A MB processed: | 554,938.5 | 219,641.2 | | |
| Logons: | 0.1 | 0.0 | | |
| Executes: | 42.2 | 16.7 | | |
| Rollbacks: | 1.5 | 0.6 | | |
| Transactions: | 2.5 | | | |
Instance Efficiency Percentages (Target 100%)
| Buffer Nowait %: | 100.00 | Redo NoWait %: | 100.00 |
| Buffer Hit %: | 99.99 | In-memory Sort %: | 100.00 |
| Library Hit %: | 97.10 | Soft Parse %: | 99.11 |
| Execute to Parse %: | 83.66 | Latch Hit %: | 99.99 |
| Parse CPU to Parse Elapsd %: | 0.01 | % Non-Parse CPU: | 99.86 |
Shared Pool Statistics
| Memory Usage %: | 43.41 | 43.36 |
| % SQL with executions>1: | 94.87 | 96.81 |
| % Memory for SQL w/exec>1: | 95.18 | 97.49 |
Top 5 Timed Foreground Events
| DB CPU | | 583 | | 98.41 | |
| db file sequential read | 7,331 | 6 | 1 | 1.08 | User I/O |
| log file sync | 2,853 | 2 | 1 | 0.37 | Commit |
| local write wait | 594 | 1 | 1 | 0.10 | User I/O |
| control file sequential read | 1,063 | 0 | 0 | 0.07 | System I/O |
Host CPU (CPUs: 8 Cores: 8 Sockets: 2)
Instance CPU
Memory Statistics
| Host Mem (MB): | 39,299.7 | 39,299.7 |
| SGA use (MB): | 15,360.0 | 15,360.0 |
| PGA use (MB): | 1,906.2 | 1,906.6 |
| % Host Mem used for SGA+PGA: | 43.93 | 43.93 |
Main Report
Please help how to analyze between these two good and bad performance.
How do I find the cuase of the issue for job run#2
Kind regards,