WORKLOAD REPOSITORY report for
DB Name | DB Id | Instance | Inst num | Startup Time | Release | RAC |
---|---|---|---|---|---|---|
MYPL | 569030708 | mypl | 1 | 03-Dec-13 20:12 | 11.2.0.1.0 | NO |
Host Name | Platform | CPUs | Cores | Sockets | Memory (GB) |
---|---|---|---|---|---|
orale | Linux IA (32-bit) | 1 | 1.39 |
Snap Id | Snap Time | Sessions | Cursors/Session | |
---|---|---|---|---|
Begin Snap: | 64 | 03-Dec-13 20:24:57 | 23 | 1.4 |
End Snap: | 66 | 03-Dec-13 22:00:49 | 27 | 1.7 |
Elapsed: | 95.87 (mins) | |||
DB Time: | 2.92 (mins) |
Report Summary
Cache Sizes
Begin | End | |||
---|---|---|---|---|
Buffer Cache: | 196M | 196M | Std Block Size: | 8K |
Shared Pool Size: | 68M | 68M | Log Buffer: | 5,504K |
Load Profile
Per Second | Per Transaction | Per Exec | Per Call | |
---|---|---|---|---|
DB Time(s): | 0.0 | 0.3 | 0.00 | 0.18 |
DB CPU(s): | 0.0 | 0.1 | 0.00 | 0.07 |
Redo size: | 1,746.6 | 15,697.9 | ||
Logical reads: | 112.2 | 1,008.5 | ||
Block changes: | 10.1 | 90.7 | ||
Physical reads: | 0.7 | 6.2 | ||
Physical writes: | 0.3 | 2.7 | ||
User calls: | 0.2 | 1.5 | ||
Parses: | 5.6 | 50.5 | ||
Hard parses: | 0.8 | 7.4 | ||
W/A MB processed: | 0.0 | 0.2 | ||
Logons: | 0.1 | 0.5 | ||
Executes: | 21.5 | 192.9 | ||
Rollbacks: | 0.0 | 0.0 | ||
Transactions: | 0.1 |
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: | 100.00 | Redo NoWait %: | 100.00 |
Buffer Hit %: | 99.38 | In-memory Sort %: | 100.00 |
Library Hit %: | 90.15 | Soft Parse %: | 85.35 |
Execute to Parse %: | 73.83 | Latch Hit %: | 99.99 |
Parse CPU to Parse Elapsd %: | 62.69 | % Non-Parse CPU: | 83.68 |
Shared Pool Statistics
Begin | End | |
---|---|---|
Memory Usage %: | 92.36 | 93.29 |
% SQL with executions>1: | 78.84 | 82.63 |
% Memory for SQL w/exec>1: | 78.04 | 87.68 |
Top 5 Timed Foreground Events
Event | Waits | Time(s) | Avg wait (ms) | % DB time | Wait Class |
---|---|---|---|---|---|
DB CPU | 67 | 38.47 | |||
db file sequential read | 1,997 | 37 | 19 | 21.36 | User I/O |
resmgr:cpu quantum | 80 | 7 | 88 | 4.04 | Scheduler |
db file scattered read | 194 | 5 | 24 | 2.66 | User I/O |
library cache: mutex X | 2 | 2 | 934 | 1.07 | Concurrency |
Host CPU (CPUs: 1 Cores: Sockets: )
Load Average Begin | Load Average End | %User | %System | %WIO | %Idle |
---|---|---|---|---|---|
0.17 | 1.27 | 0.7 | 1.1 | 1.3 | 96.7 |
%Total CPU | %Busy CPU | %DB time waiting for CPU (Resource Manager) |
---|---|---|
1.5 | 45.7 | 4.0 |
Begin | End | |
---|---|---|
Host Mem (MB): | 1,419.6 | 1,419.6 |
SGA use (MB): | 598.7 | 598.7 |
PGA use (MB): | 45.7 | 61.1 |
% Host Mem used for SGA+PGA: | 45.39 | 46.47 |
Main Report
- Report Summary
- Wait Events Statistics
- SQL Statistics
- Instance Activity Statistics
- IO Stats
- Buffer Pool Statistics
- Advisory Statistics
- Wait Statistics
- Undo Statistics
- Latch Statistics
- Segment Statistics
- Dictionary Cache Statistics
- Library Cache Statistics
- Memory Statistics
- Streams Statistics
- Resource Limit Statistics
- Shared Server Statistics
- init.ora Parameters
Back to Top
Wait Events Statistics
- Time Model Statistics
- Operating System Statistics
- Operating System Statistics - Detail
- Foreground Wait Class
- Foreground Wait Events
- Background Wait Events
- Wait Event Histogram
- Wait Event Histogram Detail (64 msec to 2 sec)
- Wait Event Histogram Detail (4 sec to 2 min)
- Wait Event Histogram Detail (4 min to 1 hr)
- Service Statistics
- Service Wait Class Stats
Time Model Statistics
- Total time in database user-calls (DB Time): 175s
- Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
- Ordered by % or DB time desc, Statistic name
Statistic Name | Time (s) | % of DB Time |
---|---|---|
sql execute elapsed time | 176.32 | 100.74 |
parse time elapsed | 78.54 | 44.87 |
hard parse elapsed time | 74.94 | 42.82 |
DB CPU | 67.32 | 38.47 |
PL/SQL execution elapsed time | 17.87 | 10.21 |
hard parse (sharing criteria) elapsed time | 5.32 | 3.04 |
PL/SQL compilation elapsed time | 2.22 | 1.27 |
hard parse (bind mismatch) elapsed time | 2.14 | 1.22 |
repeated bind elapsed time | 0.49 | 0.28 |
sequence load elapsed time | 0.18 | 0.10 |
connection management call elapsed time | 0.15 | 0.08 |
failed parse elapsed time | 0.08 | 0.05 |
DB time | 175.02 | |
background elapsed time | 75.94 | |
background cpu time | 16.58 |
Back to Wait Events Statistics
Back to Top
Operating System Statistics
- *TIME statistic values are diffed. All others display actual values. End Value is displayed if different
- ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name
Statistic | Value | End Value |
---|---|---|
BUSY_TIME | 18,374 | |
IDLE_TIME | 532,106 | |
IOWAIT_TIME | 7,208 | |
NICE_TIME | 120 | |
SYS_TIME | 5,992 | |
USER_TIME | 3,640 | |
LOAD | 0 | 1 |
RSRC_MGR_CPU_WAIT_TIME | 702 | |
PHYSICAL_MEMORY_BYTES | 1,488,609,280 | |
NUM_CPUS | 1 | |
GLOBAL_RECEIVE_SIZE_MAX | 131,071 | |
GLOBAL_SEND_SIZE_MAX | 131,071 | |
TCP_RECEIVE_SIZE_DEFAULT | 87,380 | |
TCP_RECEIVE_SIZE_MAX | 4,194,304 | |
TCP_RECEIVE_SIZE_MIN | 4,096 | |
TCP_SEND_SIZE_DEFAULT | 16,384 | |
TCP_SEND_SIZE_MAX | 4,194,304 | |
TCP_SEND_SIZE_MIN | 4,096 |
Back to Wait Events Statistics
Back to Top
Operating System Statistics - Detail
Snap Time | Load | %busy | %user | %sys | %idle | %iowait |
---|---|---|---|---|---|---|
03-Dec 20:24:57 | 0.17 | |||||
03-Dec 21:00:16 | 0.00 | 2.82 | 0.29 | 0.96 | 97.18 | 0.28 |
03-Dec 22:00:49 | 1.27 | 3.64 | 0.88 | 1.17 | 96.36 | 1.91 |
Back to Wait Events Statistics
Back to Top
Foreground Wait Class
- s - second, ms - millisecond - 1000th of a second
- ordered by wait time desc, waits desc
- %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
- Captured Time accounts for 69.5% of Total DB time 175.02 (s)
- Total FG Wait Time: 54.29 (s) DB CPU time: 67.32 (s)
Wait Class | Waits | %Time -outs | Total Wait Time (s) | Avg wait (ms) | %DB time |
---|---|---|---|---|---|
DB CPU | 67 | 38.47 | |||
User I/O | 2,369 | 0 | 43 | 18 | 24.46 |
Scheduler | 80 | 0 | 7 | 88 | 4.04 |
Concurrency | 12 | 0 | 2 | 184 | 1.26 |
Commit | 77 | 0 | 1 | 19 | 0.82 |
System I/O | 1,812 | 0 | 0 | 0 | 0.20 |
Other | 184 | 97 | 0 | 1 | 0.12 |
Administrative | 1 | 100 | 0 | 180 | 0.10 |
Application | 2 | 0 | 0 | 0 | 0.00 |
Network | 31 | 0 | 0 | 0 | 0.00 |
Back to Wait Events Statistics
Back to Top
Foreground Wait Events
- s - second, ms - millisecond - 1000th of a second
- Only events with Total Wait Time (s) >= .001 are shown
- ordered by wait time desc, waits desc (idle events last)
- %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
Event | Waits | %Time -outs | Total Wait Time (s) | Avg wait (ms) | Waits /txn | % DB time |
---|---|---|---|---|---|---|
db file sequential read | 1,997 | 0 | 37 | 19 | 3.12 | 21.36 |
resmgr:cpu quantum | 80 | 0 | 7 | 88 | 0.13 | 4.04 |
db file scattered read | 194 | 0 | 5 | 24 | 0.30 | 2.66 |
library cache: mutex X | 2 | 0 | 2 | 934 | 0.00 | 1.07 |
log file sync | 77 | 0 | 1 | 19 | 0.12 | 0.82 |
read by other session | 2 | 0 | 0 | 206 | 0.00 | 0.24 |
control file sequential read | 1,812 | 0 | 0 | 0 | 2.83 | 0.20 |
ADR block file read | 4 | 0 | 0 | 54 | 0.01 | 0.12 |
library cache load lock | 1 | 0 | 0 | 186 | 0.00 | 0.11 |
JS kgl get object wait | 1 | 100 | 0 | 180 | 0.00 | 0.10 |
direct path write temp | 15 | 0 | 0 | 10 | 0.02 | 0.09 |
direct path sync | 2 | 0 | 0 | 72 | 0.00 | 0.08 |
latch: cache buffers chains | 1 | 0 | 0 | 64 | 0.00 | 0.04 |
cursor: pin S wait on X | 2 | 0 | 0 | 27 | 0.00 | 0.03 |
latch: shared pool | 5 | 0 | 0 | 6 | 0.01 | 0.02 |
utl_file I/O | 5 | 0 | 0 | 5 | 0.01 | 0.01 |
Disk file operations I/O | 154 | 0 | 0 | 0 | 0.24 | 0.01 |
asynch descriptor resize | 178 | 100 | 0 | 0 | 0.28 | 0.00 |
jobq slave wait | 11,272 | 100 | 5,893 | 523 | 17.61 | |
SQL*Net message from client | 30 | 0 | 5,520 | 183990 | 0.05 | |
PL/SQL lock timer | 1 | 100 | 2 | 1684 | 0.00 |
Back to Wait Events Statistics
Back to Top
Background Wait Events
- ordered by wait time desc, waits desc (idle events last)
- Only events with Total Wait Time (s) >= .001 are shown
- %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
Event | Waits | %Time -outs | Total Wait Time (s) | Avg wait (ms) | Waits /txn | % bg time |
---|---|---|---|---|---|---|
os thread startup | 209 | 0 | 19 | 89 | 0.33 | 24.43 |
log file parallel write | 2,954 | 0 | 14 | 5 | 4.62 | 18.00 |
db file sequential read | 906 | 0 | 9 | 10 | 1.42 | 12.12 |
control file parallel write | 1,899 | 0 | 5 | 3 | 2.97 | 6.85 |
db file async I/O submit | 1,083 | 0 | 3 | 3 | 1.69 | 3.79 |
control file sequential read | 5,412 | 0 | 0 | 0 | 8.46 | 0.34 |
db file scattered read | 7 | 0 | 0 | 28 | 0.01 | 0.25 |
ADR block file read | 12 | 0 | 0 | 15 | 0.02 | 0.23 |
library cache load lock | 2 | 0 | 0 | 70 | 0.00 | 0.18 |
rdbms ipc reply | 1 | 0 | 0 | 117 | 0.00 | 0.15 |
latch: shared pool | 4 | 0 | 0 | 11 | 0.01 | 0.06 |
ADR block file write | 5 | 0 | 0 | 6 | 0.01 | 0.04 |
direct path sync | 1 | 0 | 0 | 25 | 0.00 | 0.03 |
asynch descriptor resize | 230 | 100 | 0 | 0 | 0.36 | 0.02 |
latch free | 188 | 0 | 0 | 0 | 0.29 | 0.01 |
Disk file operations I/O | 53 | 0 | 0 | 0 | 0.08 | 0.01 |
enq: JS - queue lock | 1 | 0 | 0 | 3 | 0.00 | 0.00 |
LGWR wait for redo copy | 8 | 0 | 0 | 0 | 0.01 | 0.00 |
rdbms ipc message | 24,737 | 94 | 68,829 | 2782 | 38.65 | |
DIAG idle wait | 11,024 | 100 | 11,499 | 1043 | 17.23 | |
pmon timer | 1,980 | 93 | 5,747 | 2903 | 3.09 | |
Space Manager: slave idle wait | 1,107 | 99 | 5,746 | 5191 | 1.73 | |
Streams AQ: qmn slave idle wait | 209 | 0 | 5,745 | 27488 | 0.33 | |
Streams AQ: qmn coordinator idle wait | 236 | 86 | 5,745 | 24343 | 0.37 | |
smon timer | 19 | 100 | 5,704 | 300186 | 0.03 | |
Streams AQ: waiting for time management or cleanup tasks | 5 | 60 | 2,735 | 547083 | 0.01 | |
class slave wait | 20 | 0 | 0 | 2 | 0.03 | |
SQL*Net message from client | 5 | 0 | 0 | 3 | 0.01 |
Back to Wait Events Statistics
Back to Top
Wait Event Histogram
- Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
- % of Waits: value of .0 indicates value was <.05%; value of null is truly 0
- % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
- Ordered by Event (idle events last)
% of Waits | |||||||||
---|---|---|---|---|---|---|---|---|---|
Event | Total Waits | <1ms | <2ms | <4ms | <8ms | <16ms | <32ms | <=1s | >1s |
ADR block file read | 16 | 31.3 | 18.8 | 18.8 | 31.3 | ||||
ADR block file write | 5 | 60.0 | 20.0 | 20.0 | |||||
ADR file lock | 6 | 100.0 | |||||||
Disk file operations I/O | 207 | 99.5 | .5 | ||||||
JS kgl get object wait | 1 | 100.0 | |||||||
LGWR wait for redo copy | 8 | 100.0 | |||||||
SQL*Net break/reset to client | 2 | 100.0 | |||||||
SQL*Net message to client | 34 | 100.0 | |||||||
SQL*Net more data to client | 1 | 100.0 | |||||||
asynch descriptor resize | 408 | 99.3 | .7 | ||||||
control file parallel write | 1899 | 83.0 | 6.6 | 1.9 | 2.2 | 2.1 | 2.4 | 1.8 | |
control file sequential read | 7224 | 99.7 | .1 | .0 | .1 | .0 | .0 | ||
cursor: pin S wait on X | 2 | 50.0 | 50.0 | ||||||
db file async I/O submit | 1083 | 77.3 | 10.1 | 2.6 | 2.7 | 3.4 | 2.4 | 1.6 | |
db file scattered read | 204 | 22.1 | 3.9 | 2.0 | 2.5 | 15.2 | 26.0 | 28.4 | |
db file sequential read | 2741 | 41.9 | 7.8 | 2.0 | 5.0 | 16.6 | 14.6 | 12.0 | .0 |
direct path sync | 3 | 33.3 | 33.3 | 33.3 | |||||
direct path write temp | 16 | 37.5 | 25.0 | 12.5 | 12.5 | 12.5 | |||
enq: JS - queue lock | 1 | 100.0 | |||||||
latch free | 188 | 100.0 | |||||||
latch: cache buffers chains | 1 | 100.0 | |||||||
latch: cache buffers lru chain | 2 | 100.0 | |||||||
latch: redo allocation | 10 | 100.0 | |||||||
latch: shared pool | 10 | 30.0 | 20.0 | 10.0 | 10.0 | 20.0 | 10.0 | ||
library cache load lock | 3 | 100.0 | |||||||
library cache: mutex X | 2 | 100.0 | |||||||
log file parallel write | 2996 | 80.9 | 5.0 | 1.5 | 1.6 | 2.5 | 4.0 | 4.5 | |
log file sync | 79 | 31.6 | 5.1 | 3.8 | 8.9 | 13.9 | 12.7 | 24.1 | |
os thread startup | 209 | 2.4 | .5 | 2.4 | 94.3 | .5 | |||
rdbms ipc reply | 1 | 100.0 | |||||||
read by other session | 2 | 100.0 | |||||||
reliable message | 1 | 100.0 | |||||||
resmgr:cpu quantum | 93 | 11.8 | 3.2 | 2.2 | 3.2 | 11.8 | 11.8 | 55.9 | |
row cache lock | 1 | 100.0 | |||||||
utl_file I/O | 5 | 80.0 | 20.0 | ||||||
DIAG idle wait | 11K | 82.2 | 17.8 | ||||||
PL/SQL lock timer | 1 | 100.0 | |||||||
SQL*Net message from client | 35 | 31.4 | 11.4 | 2.9 | 54.3 | ||||
Space Manager: slave idle wait | 1108 | .1 | 99.9 | ||||||
Streams AQ: qmn coordinator idle wait | 236 | 13.1 | .4 | 86.4 | |||||
Streams AQ: qmn slave idle wait | 209 | 2.4 | 97.6 | ||||||
Streams AQ: waiting for time management or cleanup tasks | 5 | 40.0 | 20.0 | 40.0 | |||||
class slave wait | 20 | 90.0 | 5.0 | 5.0 | |||||
jobq slave wait | 11.3K | 99.9 | .1 | ||||||
pmon timer | 1982 | 6.9 | .2 | .1 | 92.9 | ||||
rdbms ipc message | 24.8K | 2.3 | .2 | .3 | .3 | .3 | .4 | 20.6 | 75.7 |
smon timer | 19 | 100.0 |
Back to Wait Events Statistics
Back to Top
Wait Event Histogram Detail (64 msec to 2 sec)
- Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
- Units for % of Total Waits: ms is milliseconds s is 1024 milliseconds (approximately 1 second)
- % of Total Waits: total waits for all wait classes, including Idle
- % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
- Ordered by Event (only non-idle events are displayed)
% of Total Waits | |||||||||
---|---|---|---|---|---|---|---|---|---|
Event | Waits 64ms to 2s | <32ms | <64ms | <1/8s | <1/4s | <1/2s | <1s | <2s | >=2s |
ADR block file read | 5 | 68.8 | 25.0 | 6.3 | |||||
JS kgl get object wait | 1 | 100.0 | |||||||
control file parallel write | 34 | 98.2 | 1.3 | .4 | .1 | ||||
control file sequential read | 3 | 100.0 | .0 | .0 | |||||
cursor: pin S wait on X | 1 | 50.0 | 50.0 | ||||||
db file async I/O submit | 17 | 98.4 | 1.3 | .2 | .1 | ||||
db file scattered read | 58 | 71.6 | 22.1 | 5.4 | 1.0 | ||||
db file sequential read | 330 | 88.0 | 6.9 | 3.2 | 1.4 | .5 | .0 | .0 | |
direct path sync | 1 | 66.7 | 33.3 | ||||||
direct path write temp | 2 | 87.5 | 12.5 | ||||||
latch: cache buffers chains | 1 | 100.0 | |||||||
latch: shared pool | 1 | 90.0 | 10.0 | ||||||
library cache load lock | 3 | 33.3 | 33.3 | 33.3 | |||||
library cache: mutex X | 2 | 100.0 | |||||||
log file parallel write | 136 | 95.5 | 2.9 | 1.1 | .5 | .1 | |||
log file sync | 19 | 75.9 | 21.5 | 2.5 | |||||
os thread startup | 197 | 5.3 | 46.4 | 43.1 | 3.8 | .5 | .5 | .5 | |
rdbms ipc reply | 1 | 100.0 | |||||||
read by other session | 2 | 100.0 | |||||||
resmgr:cpu quantum | 52 | 44.1 | 19.4 | 16.1 | 15.1 | 5.4 |
Back to Wait Events Statistics
Back to Top
Wait Event Histogram Detail (4 sec to 2 min)
- Units for Total Waits column: K is 1000, M is 1000000, G is 1