Sample AWR Report

posted Aug 4, 2011, 5:19 PM by Sachchida Ojha

WORKLOAD REPOSITORY report for

DB NameDB IdInstanceInst numReleaseRACHost
TESTIBM1448208628TESTIBM110.2.0.2.0NObrepredbwh04


Snap IdSnap TimeSessionsCursors/Session
Begin Snap:35610-Apr-06 10:40:4729 13.3
End Snap:35710-Apr-06 10:50:4929 13.3
Elapsed:  10.05 (mins)  
DB Time:  94.98 (mins)  

Report Summary

Cache Sizes


BeginEnd

Buffer Cache: 712M 712MStd Block Size: 16K
Shared Pool Size: 72M 72MLog Buffer: 6,956K

Load Profile


Per SecondPer Transaction
Redo size: 94,931.31 4,104.60
Logical reads: 3,114.09 134.65
Block changes: 747.30 32.31
Physical reads: 51.58 2.23
Physical writes: 40.22 1.74
User calls: 107.97 4.67
Parses: 85.70 3.71
Hard parses: 1.11 0.05
Sorts: 8.01 0.35
Logons: 0.06 0.00
Executes: 392.03 16.95
Transactions: 23.13 

% Blocks changed per Read: 24.00Recursive Call %: 88.74
Rollback per transaction %: 0.00Rows per Sort: 11.42

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 99.96Redo NoWait %: 100.00
Buffer Hit %: 98.34In-memory Sort %: 100.00
Library Hit %: 99.06Soft Parse %: 98.71
Execute to Parse %: 78.14Latch Hit %: 99.80
Parse CPU to Parse Elapsd %: 21.24% Non-Parse CPU: 95.56

Shared Pool Statistics


BeginEnd
Memory Usage %: 82.94 82.29
% SQL with executions>1: 80.13 83.96
% Memory for SQL w/exec>1: 80.59 77.96

Top 5 Timed Events

EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class
db file sequential read 31,091 3,424 110 60.1User I/O
log file sync 27,881 2,099 75 36.8Commit
enq: TX - row lock contention 1,358 144 106 2.5Application
CPU time  138  2.4 
control file parallel write 274 18 66 .3System I/O

Main Report


Back to Top

Wait Events Statistics

Back to Top

Time Model Statistics

  • Total time in database user-calls (DB Time): 5699s
  • 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 NameTime (s)% of DB Time
sql execute elapsed time 3,588.15 62.96
DB CPU 137.72 2.42
parse time elapsed 20.23 0.35
PL/SQL execution elapsed time 16.09 0.28
hard parse elapsed time 15.90 0.28
PL/SQL compilation elapsed time 3.43 0.06
connection management call elapsed time 1.53 0.03
hard parse (sharing criteria) elapsed time 0.77 0.01
repeated bind elapsed time 0.20 0.00
sequence load elapsed time 0.04 0.00
DB time 5,698.97  
background elapsed time 720.16  
background cpu time 18.17  

Back to Wait Events Statistics
Back to Top

Wait Class

  • s - second
  • cs - centisecond - 100th of a second
  • ms - millisecond - 1000th of a second
  • us - microsecond - 1000000th of a second
  • ordered by wait time desc, waits desc
Wait ClassWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
User I/O 31,205 0.00 3,434 110 2.24
Commit 27,881 0.00 2,099 75 2.00
Application 29,012 0.00 151 5 2.08
System I/O 18,927 0.00 19 1 1.36
Concurrency 744 1.21 7 9 0.05
Other 650 0.00 2 3 0.05
Network 33,810 0.00 0 0 2.43
Configuration 10 0.00 0 1 0.00

Back to Wait Events Statistics
Back to Top

Wait Events

  • s - second
  • cs - centisecond - 100th of a second
  • ms - millisecond - 1000th of a second
  • us - microsecond - 1000000th of a second
  • ordered by wait time desc, waits desc (idle events last)
EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
db file sequential read 31,091 0.00 3,424 110 2.23
log file sync 27,881 0.00 2,099 75 2.00
enq: TX - row lock contention 1,358 0.00 144 106 0.10
control file parallel write 274 0.00 18 66 0.02
read by other session 94 0.00 9 94 0.01
SQL*Net break/reset to client 27,654 0.00 8 0 1.98
buffer busy waits 667 0.00 6 10 0.05
enq: TX - contention 7 0.00 2 242 0.00
db file scattered read 10 0.00 1 131 0.00
log file parallel write 12,681 0.00 0 0 0.91
SQL*Net message to client 33,225 0.00 0 0 2.38
control file sequential read 809 0.00 0 0 0.06
enq: FB - contention 7 0.00 0 19 0.00
cursor: pin S wait on X 9 100.00 0 12 0.00
rdbms ipc reply 235 0.00 0 0 0.02
os thread startup 2 0.00 0 43 0.00
db file parallel write 5,163 0.00 0 0 0.37
latch: enqueue hash chains 23 0.00 0 3 0.00
latch: In memory undo latch 25 0.00 0 3 0.00
latch free 27 0.00 0 2 0.00
latch: library cache 14 0.00 0 4 0.00
LGWR wait for redo copy 146 0.00 0 0 0.01
latch: cache buffers chains 26 0.00 0 1 0.00
cursor: pin S 196 0.00 0 0 0.01
SQL*Net more data to client 570 0.00 0 0 0.04
latch: session allocation 6 0.00 0 1 0.00
enq: HW - contention 10 0.00 0 1 0.00
SQL*Net more data from client 15 0.00 0 0 0.00
latch: shared pool 1 0.00 0 0 0.00
latch: redo allocation 3 0.00 0 0 0.00
direct path write 6 0.00 0 0 0.00
direct path read 4 0.00 0 0 0.00
Streams AQ: waiting for time management or cleanup tasks 3 33.33 1,279 426475 0.00
SQL*Net message from client 33,224 0.00 1,204 36 2.38
Streams AQ: qmn slave idle wait 22 0.00 602 27346 0.00
Streams AQ: qmn coordinator idle wait 44 50.00 602 13673 0.00
Streams AQ: waiting for messages in the queue 119 99.16 578 4856 0.01
class slave wait 2 0.00 0 0 0.00

Back to Wait Events Statistics
Back to Top

Background Wait Events

  • ordered by wait time desc, waits desc (idle events last)
EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
control file parallel write 273 0.00 18 66 0.02
db file sequential read 109 0.00 9 80 0.01
log file parallel write 12,687 0.00 0 0 0.91
events in waitclass Other 384 0.00 0 0 0.03
db file parallel write 5,210 0.00 0 0 0.37
os thread startup 2 0.00 0 43 0.00
log file sync 1 0.00 0 22 0.00
db file scattered read 1 0.00 0 10 0.00
buffer busy waits 3 0.00 0 1 0.00
direct path read 4 0.00 0 0 0.00
direct path write 2 0.00 0 0 0.00
control file sequential read 122 0.00 -0 -2 0.01
rdbms ipc message 12,188 13.82 5,286 434 0.87
Streams AQ: waiting for time management or cleanup tasks 3 33.33 1,279 426475 0.00
Streams AQ: qmn slave idle wait 22 0.00 602 27346 0.00
Streams AQ: qmn coordinator idle wait 44 50.00 602 13673 0.00
pmon timer 212 100.00 580 2736 0.02
smon timer 18 5.56 561 31144 0.00

Back to Wait Events Statistics
Back to Top

Operating System Statistics

StatisticTotal
BUSY_TIME 17,250
IDLE_TIME 219,596
IOWAIT_TIME 144,677
NICE_TIME 0
SYS_TIME 4,815
USER_TIME 11,592
LOAD 7
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 32,588
NUM_CPUS 4
NUM_CPU_SOCKETS 2

Back to Wait Events Statistics
Back to Top

Service Statistics

  • ordered by DB Time
Service NameDB Time (s)DB CPU (s)Physical ReadsLogical Reads
TESTIBM 5,719.40 133.20 30,110 1,803,632
SYS$USERS 22.30 5.60 211 37,857
SYS$BACKGROUND 0.00 0.00 865 40,436

Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

  • Wait Class info for services in the Service Statistics section.
  • Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
  • Time Waited (Wt Time) in centisecond (100th of a second)
Service NameUser I/O Total WtsUser I/O Wt TimeConcurcy Total WtsConcurcy Wt TimeAdmin Total WtsAdmin Wt TimeNetwork Total WtsNetwork Wt Time
TESTIBM 30168 333711 736 663 0 0 32344 25
SYS$USERS 199 1613 0 0 0 0 1366 2
SYS$BACKGROUND 852 8245 6 8 0 0 0 0

Back to Wait Events Statistics
Back to Top

SQL Statistics

Back to Top

SQL ordered by Elapsed Time

  • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
  • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
Elapsed Time (s)CPU Time (s)Executions Elap per Exec (s) % Total DB Time SQL IdSQL ModuleSQL Text
2,707 36 13,420 0.20 47.51 16dhat4ta7xs9 wish8.4@sles10b8 (TNS V1-V3) begin neword(:no_w_id, :no_max...
2,446 11 13,414 0.18 42.92 88fgqncchy6wg wish8.4@sles10b8 (TNS V1-V3) SELECT I_PRICE, I_NAME, I_DA...
499 47 13,601 0.04 8.76 aw9ttz9acxbc3 wish8.4@sles10b8 (TNS V1-V3) BEGIN payment(:p_w_id, :p_d_id...
239 6 8,148 0.03 4.20 4wg725nwpxb1z wish8.4@sles10b8 (TNS V1-V3) SELECT C_FIRST, C_MIDDLE, C_...
216 7 1,343 0.16 3.79 5hyh0360hgx2u wish8.4@sles10b8 (TNS V1-V3) BEGIN slev(:st_w_id, :st_d_id,...
214 6 1,342 0.16 3.76 13dn4hkrzfpdy wish8.4@sles10b8 (TNS V1-V3) SELECT COUNT(DISTINCT (S_I_ID)...
185 3 13,411 0.01 3.24 5mz8u3b34u9gw wish8.4@sles10b8 (TNS V1-V3) SELECT C_DISCOUNT, C_LAST, C...
84 1 5,452 0.02 1.47 2k7k32av19mqz wish8.4@sles10b8 (TNS V1-V3) SELECT C_FIRST, C_MIDDLE, C_...
68 8 1,391 0.05 1.19 gcvvtwcxafqp5 wish8.4@sles10b8 (TNS V1-V3) BEGIN ostat(:os_w_id, :os_d_id...
52 6 13,594 0.00 0.92 g5u7xuchhfu62 wish8.4@sles10b8 (TNS V1-V3) UPDATE WAREHOUSE SET W_YTD = W...

Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

  • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
  • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
CPU Time (s)Elapsed Time (s)Executions CPU per Exec (s)% Total DB Time SQL IdSQL ModuleSQL Text
47 499 13,601 0.00 8.76 aw9ttz9acxbc3 wish8.4@sles10b8 (TNS V1-V3) BEGIN payment(:p_w_id, :p_d_id...
36 2,707 13,420 0.00 47.51 16dhat4ta7xs9 wish8.4@sles10b8 (TNS V1-V3) begin neword(:no_w_id, :no_max...
11 2,446 13,414 0.00 42.92 88fgqncchy6wg wish8.4@sles10b8 (TNS V1-V3) SELECT I_PRICE, I_NAME, I_DA...
8 68 1,391 0.01 1.19 gcvvtwcxafqp5 wish8.4@sles10b8 (TNS V1-V3) BEGIN ostat(:os_w_id, :os_d_id...
7 216 1,343 0.01 3.79 5hyh0360hgx2u wish8.4@sles10b8 (TNS V1-V3) BEGIN slev(:st_w_id, :st_d_id,...
6 214 1,342 0.00 3.76 13dn4hkrzfpdy wish8.4@sles10b8 (TNS V1-V3) SELECT COUNT(DISTINCT (S_I_ID)...
6 9 13,416 0.00 0.16 gwcvcq87cn4sc wish8.4@sles10b8 (TNS V1-V3) INSERT INTO ORDERS (O_ID, O_D...
6 239 8,148 0.00 4.20 4wg725nwpxb1z wish8.4@sles10b8 (TNS V1-V3) SELECT C_FIRST, C_MIDDLE, C_...
6 52 13,594 0.00 0.92 g5u7xuchhfu62 wish8.4@sles10b8 (TNS V1-V3) UPDATE WAREHOUSE SET W_YTD = W...
5 29 1,391 0.00 0.51 csv0xdm9c394t wish8.4@sles10b8 (TNS V1-V3) SELECT O_ID, O_CARRIER_ID, O...
3 185 13,411 0.00 3.24 5mz8u3b34u9gw wish8.4@sles10b8 (TNS V1-V3) SELECT C_DISCOUNT, C_LAST, C...
1 84 5,452 0.00 1.47 2k7k32av19mqz wish8.4@sles10b8 (TNS V1-V3) SELECT C_FIRST, C_MIDDLE, C_...

Back to SQL Statistics
Back to Top

SQL ordered by Gets

  • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
  • Total Buffer Gets: 1,876,963
  • Captured SQL account for 86.4% of Total
Buffer Gets Executions Gets per Exec %TotalCPU Time (s)Elapsed Time (s) SQL IdSQL ModuleSQL Text
814,525 1,343 606.50 43.40 7.37 216.06 5hyh0360hgx2u wish8.4@sles10b8 (TNS V1-V3) BEGIN slev(:st_w_id, :st_d_id,...
811,818 1,342 604.93 43.25 6.32 214.35 13dn4hkrzfpdy wish8.4@sles10b8 (TNS V1-V3) SELECT COUNT(DISTINCT (S_I_ID)...
407,367 13,601 29.95 21.70 47.33 499.06 aw9ttz9acxbc3 wish8.4@sles10b8 (TNS V1-V3) BEGIN payment(:p_w_id, :p_d_id...
310,391 13,420 23.13 16.54 36.07 2707.39 16dhat4ta7xs9 wish8.4@sles10b8 (TNS V1-V3) begin neword(:no_w_id, :no_max...
136,325 8,148 16.73 7.26 5.60 239.38 4wg725nwpxb1z wish8.4@sles10b8 (TNS V1-V3) SELECT C_FIRST, C_MIDDLE, C_...
94,301 13,414 7.03 5.02 10.82 2446.07 88fgqncchy6wg wish8.4@sles10b8 (TNS V1-V3) SELECT I_PRICE, I_NAME, I_DA...
67,793 13,411 5.06 3.61 3.20 184.53 5mz8u3b34u9gw wish8.4@sles10b8 (TNS V1-V3) SELECT C_DISCOUNT, C_LAST, C...
67,174 13,416 5.01 3.58 5.81 8.86 gwcvcq87cn4sc wish8.4@sles10b8 (TNS V1-V3) INSERT INTO ORDERS (O_ID, O_D...
55,521 13,594 4.08 2.96 5.58 52.27 g5u7xuchhfu62 wish8.4@sles10b8 (TNS V1-V3) UPDATE WAREHOUSE SET W_YTD = W...
54,171 13,412 4.04 2.89 4.34 50.24 bswc46zum45tj wish8.4@sles10b8 (TNS V1-V3) UPDATE DISTRICT SET D_NEXT_O_I...
38,302 1,391 27.54 2.04 7.65 67.70 gcvvtwcxafqp5 wish8.4@sles10b8 (TNS V1-V3) BEGIN ostat(:os_w_id, :os_d_id...
28,402 39 728.26 1.51 0.32 0.33 6dbcntpgxsckn Realtime Connection SELECT /*+ NO_MERGE(t) USE_NL(...
27,818 13,590 2.05 1.48 2.20 48.34 69gxtaqr1vk8j wish8.4@sles10b8 (TNS V1-V3) UPDATE DISTRICT SET D_YTD = D_...
27,462 13,594 2.02 1.46 1.67 1.69 4r83hzuhh3yuv wish8.4@sles10b8 (TNS V1-V3) SELECT W_STREET_1, W_STREET_2...
27,256 8,971 3.04 1.45 1.70 22.23 7m5h0wf6stq0q wish8.4@sles10b8 (TNS V1-V3) SELECT COUNT(C_ID) FROM CUSTOM...
27,204 13,598 2.00 1.45 1.27 1.27 g1r3tpjj3b2gh wish8.4@sles10b8 (TNS V1-V3) SELECT D_STREET_1, D_STREET_2...
26,830 13,416 2.00 1.43 1.94 1.95 80ajya2tnyfg2 wish8.4@sles10b8 (TNS V1-V3) INSERT INTO NEW_ORDER (NO_O_ID...
20,502 6,834 3.00 1.09 0.65 0.91 1bn3cfn6n3xv5 wish8.4@sles10b8 (TNS V1-V3) SELECT C_DATA FROM CUSTOMER WH...
20,502 6,833 3.00 1.09 1.25 1.25 c90b8j61ndux8 wish8.4@sles10b8 (TNS V1-V3) UPDATE CUSTOMER SET C_BALANCE ...
20,307 6,769 3.00 1.08 1.16 1.18 95yr4xtfr50hx wish8.4@sles10b8 (TNS V1-V3) UPDATE CUSTOMER SET C_BALANCE ...
19,848 1,391 14.27 1.06 5.15 29.30 csv0xdm9c394t wish8.4@sles10b8 (TNS V1-V3) SELECT O_ID, O_CARRIER_ID, O...

Back to SQL Statistics
Back to Top

SQL ordered by Reads

  • Total Disk Reads: 31,089
  • Captured SQL account for 95.7% of Total
Physical ReadsExecutionsReads per Exec %TotalCPU Time (s)Elapsed Time (s) SQL IdSQL ModuleSQL Text
21,877 13,420 1.63 70.37 36.07 2707.39 16dhat4ta7xs9 wish8.4@sles10b8 (TNS V1-V3) begin neword(:no_w_id, :no_max...
20,516 13,414 1.53 65.99 10.82 2446.07 88fgqncchy6wg wish8.4@sles10b8 (TNS V1-V3) SELECT I_PRICE, I_NAME, I_DA...
3,673 1,342 2.74 11.81 6.32 214.35 13dn4hkrzfpdy wish8.4@sles10b8 (TNS V1-V3) SELECT COUNT(DISTINCT (S_I_ID)...
3,673 1,343 2.73 11.81 7.37 216.06 5hyh0360hgx2u wish8.4@sles10b8 (TNS V1-V3) BEGIN slev(:st_w_id, :st_d_id,...
2,952 13,601 0.22 9.50 47.33 499.06 aw9ttz9acxbc3 wish8.4@sles10b8 (TNS V1-V3) BEGIN payment(:p_w_id, :p_d_id...
2,149 8,148 0.26 6.91 5.60 239.38 4wg725nwpxb1z wish8.4@sles10b8 (TNS V1-V3) SELECT C_FIRST, C_MIDDLE, C_...
1,338 13,411 0.10 4.30 3.20 184.53 5mz8u3b34u9gw wish8.4@sles10b8 (TNS V1-V3) SELECT C_DISCOUNT, C_LAST, C...
809 1,391 0.58 2.60 7.65 67.70 gcvvtwcxafqp5 wish8.4@sles10b8 (TNS V1-V3) BEGIN ostat(:os_w_id, :os_d_id...
580 5,452 0.11 1.87 0.83 83.98 2k7k32av19mqz wish8.4@sles10b8 (TNS V1-V3) SELECT C_FIRST, C_MIDDLE, C_...
496 1,391 0.36 1.60 5.15 29.30 csv0xdm9c394t wish8.4@sles10b8 (TNS V1-V3) SELECT O_ID, O_CARRIER_ID, O...

Back to SQL Statistics
Back to Top

SQL ordered by Executions

  • Total Executions: 236,289
  • Captured SQL account for 94.8% of Total
Executions Rows ProcessedRows per ExecCPU per Exec (s)Elap per Exec (s) SQL IdSQL ModuleSQL Text
13,602 13,603 1.00 0.00 0.00 5xm451a80drbc wish8.4@sles10b8 (TNS V1-V3) INSERT INTO HISTORY (H_C_D_ID,...
13,601 13,603 1.00 0.00 0.04 aw9ttz9acxbc3 wish8.4@sles10b8 (TNS V1-V3) BEGIN payment(:p_w_id, :p_d_id...
13,598 13,602 1.00 0.00 0.00 g1r3tpjj3b2gh wish8.4@sles10b8 (TNS V1-V3) SELECT D_STREET_1, D_STREET_2...
13,594 13,601 1.00 0.00 0.00 4r83hzuhh3yuv wish8.4@sles10b8 (TNS V1-V3) SELECT W_STREET_1, W_STREET_2...
13,594 13,600 1.00 0.00 0.00 g5u7xuchhfu62 wish8.4@sles10b8 (TNS V1-V3) UPDATE WAREHOUSE SET W_YTD = W...
13,590 13,600 1.00 0.00 0.00 69gxtaqr1vk8j wish8.4@sles10b8 (TNS V1-V3) UPDATE DISTRICT SET D_YTD = D_...
13,420 0 0.00 0.00 0.20 16dhat4ta7xs9 wish8.4@sles10b8 (TNS V1-V3) begin neword(:no_w_id, :no_max...
13,416 13,416 1.00 0.00 0.00 80ajya2tnyfg2 wish8.4@sles10b8 (TNS V1-V3) INSERT INTO NEW_ORDER (NO_O_ID...
13,416 13,416 1.00 0.00 0.00 gwcvcq87cn4sc wish8.4@sles10b8 (TNS V1-V3) INSERT INTO ORDERS (O_ID, O_D...
13,414 13,417 1.00 0.00 0.18 88fgqncchy6wg wish8.4@sles10b8 (TNS V1-V3) SELECT I_PRICE, I_NAME, I_DA...
13,412 13,415 1.00 0.00 0.00 bswc46zum45tj wish8.4@sles10b8 (TNS V1-V3) UPDATE DISTRICT SET D_NEXT_O_I...
13,411 13,416 1.00 0.00 0.01 5mz8u3b34u9gw wish8.4@sles10b8 (TNS V1-V3) SELECT C_DISCOUNT, C_LAST, C...
8,971 8,974 1.00 0.00 0.00 7m5h0wf6stq0q wish8.4@sles10b8 (TNS V1-V3) SELECT COUNT(C_ID) FROM CUSTOM...
8,148 59,588 7.31 0.00 0.03 4wg725nwpxb1z wish8.4@sles10b8 (TNS V1-V3) SELECT C_FIRST, C_MIDDLE, C_...
6,834 6,834 1.00 0.00 0.00 1bn3cfn6n3xv5 wish8.4@sles10b8 (TNS V1-V3) SELECT C_DATA FROM CUSTOMER WH...
6,833 6,834 1.00 0.00 0.00 c90b8j61ndux8 wish8.4@sles10b8 (TNS V1-V3) UPDATE CUSTOMER SET C_BALANCE ...
6,769 6,769 1.00 0.00 0.00 95yr4xtfr50hx wish8.4@sles10b8 (TNS V1-V3) UPDATE CUSTOMER SET C_BALANCE ...
5,452 5,453 1.00 0.00 0.02 2k7k32av19mqz wish8.4@sles10b8 (TNS V1-V3) SELECT C_FIRST, C_MIDDLE, C_...
3,099 2,196 0.71 0.00 0.00 96g93hntrzjtr   select /*+ rule */ bucket_cnt,...

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

  • Total Parse Calls: 51,654
  • Captured SQL account for 93.0% of Total
Parse CallsExecutions % Total Parses SQL IdSQL ModuleSQL Text
13,588 13,601 26.31 aw9ttz9acxbc3 wish8.4@sles10b8 (TNS V1-V3) BEGIN payment(:p_w_id, :p_d_id...
13,415 13,414 25.97 88fgqncchy6wg wish8.4@sles10b8 (TNS V1-V3) SELECT I_PRICE, I_NAME, I_DA...
13,408 13,420 25.96 16dhat4ta7xs9 wish8.4@sles10b8 (TNS V1-V3) begin neword(:no_w_id, :no_max...
1,391 1,391 2.69 gcvvtwcxafqp5 wish8.4@sles10b8 (TNS V1-V3) BEGIN ostat(:os_w_id, :os_d_id...
1,381 1,381 2.67 d4ujh5yqt1fph wish8.4@sles10b8 (TNS V1-V3) BEGIN delivery(:d_w_id, :d_o_c...
1,342 1,343 2.60 5hyh0360hgx2u wish8.4@sles10b8 (TNS V1-V3) BEGIN slev(:st_w_id, :st_d_id,...
571 571 1.11 2ym6hhaq30r73   select type#, blocks, extents,...
267 813 0.52 3c1kubcdjnppq   update sys.col_usage$ set eq...
239 239 0.46 asvzxj61dc5vs   select timestamp, flags from ...
163 163 0.32 8swypbbr0m372   select order#, columns, types ...

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

Complete List of SQL Text

SQL IdSQL Text
13dn4hkrzfpdySELECT COUNT(DISTINCT (S_I_ID)) FROM ORDER_LINE, STOCK WHERE OL_W_ID = :B2 AND OL_D_ID = :B4 AND (OL_O_ID < :B3 ) AND OL_O_ID >= (:B3 - 20) AND S_W_ID = :B2 AND S_I_ID = OL_I_ID AND S_QUANTITY < :B1
16dhat4ta7xs9begin neword(:no_w_id, :no_max_w_id, :no_d_id, :no_c_id, :no_o_ol_cnt, :no_c_discount, :no_c_last, :no_c_credit, :no_d_tax, :no_w_tax, :no_d_next_o_id, TO_DATE(:timestamp, 'YYYYMMDDHH24MISS')); END;
1bn3cfn6n3xv5SELECT C_DATA FROM CUSTOMER WHERE C_W_ID = :B3 AND C_D_ID = :B2 AND C_ID = :B1
2k7k32av19mqzSELECT C_FIRST, C_MIDDLE, C_LAST, C_STREET_1, C_STREET_2, C_CITY, C_STATE, C_ZIP, C_PHONE, C_CREDIT, C_CREDIT_LIM, C_DISCOUNT, C_BALANCE, C_SINCE FROM CUSTOMER WHERE C_W_ID = :B3 AND C_D_ID = :B2 AND C_ID = :B1
2ym6hhaq30r73select type#, blocks, extents, minexts, maxexts, extsize, extpct, user#, iniexts, NVL(lists, 65535), NVL(groups, 65535), cachehint, hwmincr, NVL(spare1, 0), NVL(scanhint, 0) from seg$ where ts#=:1 and file#=:2 and block#=:3
3c1kubcdjnppqupdate sys.col_usage$ set equality_preds = equality_preds + decode(bitand(:flag, 1), 0, 0, 1), equijoin_preds = equijoin_preds + decode(bitand(:flag, 2), 0, 0, 1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag, 4), 0, 0, 1), range_preds = range_preds + decode(bitand(:flag, 8), 0, 0, 1), like_preds = like_preds + decode(bitand(:flag, 16), 0, 0, 1), null_preds = null_preds + decode(bitand(:flag, 32), 0, 0, 1), timestamp = :time where obj# = :objn and intcol# = :coln
4r83hzuhh3yuvSELECT W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP, W_NAME FROM WAREHOUSE WHERE W_ID = :B1
4wg725nwpxb1zSELECT C_FIRST, C_MIDDLE, C_ID, C_STREET_1, C_STREET_2, C_CITY, C_STATE, C_ZIP, C_PHONE, C_CREDIT, C_CREDIT_LIM, C_DISCOUNT, C_BALANCE, C_SINCE FROM CUSTOMER WHERE C_W_ID = :B3 AND C_D_ID = :B2 AND C_LAST = :B1 ORDER BY C_FIRST
5hyh0360hgx2uBEGIN slev(:st_w_id, :st_d_id, :threshold); END;
5mz8u3b34u9gwSELECT C_DISCOUNT, C_LAST, C_CREDIT, W_TAX FROM CUSTOMER, WAREHOUSE WHERE WAREHOUSE.W_ID = :B3 AND CUSTOMER.C_W_ID = :B3 AND CUSTOMER.C_D_ID = :B2 AND CUSTOMER.C_ID = :B1
5xm451a80drbcINSERT INTO HISTORY (H_C_D_ID, H_C_W_ID, H_C_ID, H_D_ID, H_W_ID, H_DATE, H_AMOUNT, H_DATA) VALUES (:B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 )
69gxtaqr1vk8jUPDATE DISTRICT SET D_YTD = D_YTD + :B3 WHERE D_W_ID = :B2 AND D_ID = :B1
6dbcntpgxscknSELECT /*+ NO_MERGE(t) USE_NL(t) USE_NL(p) ORDERED */ t.task_id, to_timestamp(p.parameter_value, 'MM-DD-YYYY HH24:MI:SS') FROM (SELECT task_id FROM dba_advisor_tasks WHERE advisor_id = 1 ) t, dba_advisor_parameters_proj p, dba_advisor_parameters_proj p1, dba_advisor_parameters_proj p2 WHERE p.task_id = t.task_id AND p.parameter_name = 'END_TIME' AND decode(p.parameter_value, 'UNUSED', sysdate+1000, to_timestamp(p.parameter_value, 'MM-DD-YYYY HH24:MI:SS') ) >= :1 AND decode(p.parameter_value, 'UNUSED', sysdate+1000, to_timestamp(p.parameter_value, 'MM-DD-YYYY HH24:MI:SS') ) <= :2 AND p1.task_id = t.task_id AND p1.parameter_name = 'INSTANCE' AND p1.parameter_value = to_char(:3) AND p2.task_id = t.task_id AND p2.parameter_name = 'DB_ID' AND p2.parameter_value = to_char(:4)
7m5h0wf6stq0qSELECT COUNT(C_ID) FROM CUSTOMER WHERE C_LAST = :B3 AND C_D_ID = :B2 AND C_W_ID = :B1
80ajya2tnyfg2INSERT INTO NEW_ORDER (NO_O_ID, NO_D_ID, NO_W_ID) VALUES (:B3 , :B2 , :B1 )
88fgqncchy6wgSELECT I_PRICE, I_NAME, I_DATA FROM ITEM WHERE I_ID = :B1
8swypbbr0m372select order#, columns, types from access$ where d_obj#=:1
95yr4xtfr50hxUPDATE CUSTOMER SET C_BALANCE = :B4 WHERE C_W_ID = :B3 AND C_D_ID = :B2 AND C_ID = :B1
96g93hntrzjtrselect /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
asvzxj61dc5vsselect timestamp, flags from fixed_obj$ where obj#=:1
aw9ttz9acxbc3BEGIN payment(:p_w_id, :p_d_id, :p_c_w_id, :p_c_d_id, :p_c_id, :byname, :p_h_amount, :p_c_last, :p_w_street_1, :p_w_street_2, :p_w_city, :p_w_state, :p_w_zip, :p_d_street_1, :p_d_street_2, :p_d_city, :p_d_state, :p_d_zip, :p_c_first, :p_c_middle, :p_c_street_1, :p_c_street_2, :p_c_city, :p_c_state, :p_c_zip, :p_c_phone, :p_c_since, :p_c_credit, :p_c_credit_lim, :p_c_discount, :p_c_balance, :p_c_data, TO_DATE(:timestamp, 'YYYYMMDDHH24MISS')); END;
bswc46zum45tjUPDATE DISTRICT SET D_NEXT_O_ID = D_NEXT_O_ID + 1 WHERE D_ID = :B2 AND D_W_ID = :B1 RETURNING D_NEXT_O_ID, D_TAX INTO :O0 , :O1
c90b8j61ndux8UPDATE CUSTOMER SET C_BALANCE = :B5 , C_DATA = :B4 WHERE C_W_ID = :B3 AND C_D_ID = :B2 AND C_ID = :B1
csv0xdm9c394tSELECT O_ID, O_CARRIER_ID, O_ENTRY_D FROM (SELECT O_ID, O_CARRIER_ID, O_ENTRY_D FROM ORDERS WHERE O_D_ID = :B3 AND O_W_ID = :B2 AND O_C_ID=:B1 ORDER BY O_ID DESC) WHERE ROWNUM = 1
d4ujh5yqt1fphBEGIN delivery(:d_w_id, :d_o_carrier_id, TO_DATE(:timestamp, 'YYYYMMDDHH24MISS')); END;
g1r3tpjj3b2ghSELECT D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP, D_NAME FROM DISTRICT WHERE D_W_ID = :B2 AND D_ID = :B1
g5u7xuchhfu62UPDATE WAREHOUSE SET W_YTD = W_YTD + :B2 WHERE W_ID = :B1
gcvvtwcxafqp5BEGIN ostat(:os_w_id, :os_d_id, :os_c_id, :byname, :os_c_last, :os_c_first, :os_c_middle, :os_c_balance, :os_o_id, :os_entdate, :os_o_carrier_id); END;
gwcvcq87cn4scINSERT INTO ORDERS (O_ID, O_D_ID, O_W_ID, O_C_ID, O_ENTRY_D, O_OL_CNT, O_ALL_LOCAL) VALUES (:B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 )

Back to SQL Statistics
Back to Top

Instance Activity Statistics

Back to Top

Instance Activity Stats

StatisticTotalper Secondper Trans
CPU used by this session 12,679 21.04 0.91
CPU used when call started 12,575 20.86 0.90
CR blocks created 1,647 2.73 0.12
Cached Commit SCN referenced 19,955 33.11 1.43
Commit SCN cached 32 0.05 0.00
DB time 644,278 1,068.93 46.22
DBWR checkpoint buffers written 9,265 15.37 0.66
DBWR checkpoints 0 0.00 0.00
DBWR transaction table writes 40 0.07 0.00
DBWR undo block writes 13,797 22.89 0.99
IMU CR rollbacks 1,645 2.73 0.12
IMU Flushes 15,776 26.17 1.13
IMU Redo allocation size 37,404,828 62,058.81 2,683.27
IMU commits 12,080 20.04 0.87
IMU contention 13,193 21.89 0.95
IMU pool not allocated 0 0.00 0.00
IMU recursive-transaction flush 0 0.00 0.00
IMU undo allocation size 74,673,508 123,891.73 5,356.78
IMU- failed to get a private strand 0 0.00 0.00
SMON posted for undo segment shrink 16 0.03 0.00
SQL*Net roundtrips to/from client 33,013 54.77 2.37
active txn count during cleanout 2,360 3.92 0.17
application wait time 15,115 25.08 1.08
background checkpoints completed 0 0.00 0.00
background checkpoints started 0 0.00 0.00
background timeouts 1,853 3.07 0.13
buffer is not pinned count 998,805 1,657.13 71.65
buffer is pinned count 8,851,449 14,685.55 634.97
bytes received via SQL*Net from client 36,811,312 61,074.10 2,640.70
bytes sent via SQL*Net to client 15,275,278 25,343.40 1,095.79
calls to get snapshot scn: kcmgss 193,778 321.50 13.90
calls to kcmgas 29,902 49.61 2.15
calls to kcmgcs 2,007 3.33 0.14
change write time 371 0.62 0.03
cleanout - number of ktugct calls 2,500 4.15 0.18
cleanouts and rollbacks - consistent read gets 1,042 1.73 0.07
cleanouts only - consistent read gets 107 0.18 0.01
cluster key scan block gets 4,379 7.27 0.31
cluster key scans 3,374 5.60 0.24
commit batch performed 0 0.00 0.00
commit batch requested 0 0.00 0.00
commit batch/immediate performed 13,797 22.89 0.99
commit batch/immediate requested 13,797 22.89 0.99
commit cleanout failures: block lost 0 0.00 0.00
commit cleanout failures: callback failure 7 0.01 0.00
commit cleanout failures: cannot pin 300 0.50 0.02
commit cleanouts 56,313 93.43 4.04
commit cleanouts successfully completed 56,006 92.92 4.02
commit immediate performed 13,797 22.89 0.99
commit immediate requested 13,797 22.89 0.99
commit txn count during cleanout 362 0.60 0.03
concurrency wait time 732 1.21 0.05
consistent changes 2,316 3.84 0.17
consistent gets 1,447,328 2,401.28 103.83
consistent gets - examination 1,188,174 1,971.31 85.23
consistent gets direct 0 0.00 0.00
consistent gets from cache 1,447,328 2,401.28 103.83
cursor authentications 167 0.28 0.01
data blocks consistent reads - undo records applied 1,690 2.80 0.12
db block changes 450,419 747.30 32.31
db block gets 429,635 712.81 30.82
db block gets direct 9 0.01 0.00
db block gets from cache 429,626 712.80 30.82
deferred (CURRENT) block cleanout applications 32,922 54.62 2.36
dirty buffers inspected 9,187 15.24 0.66
enqueue conversions 152 0.25 0.01
enqueue releases 133,763 221.93 9.60
enqueue requests 133,766 221.93 9.60
enqueue waits 1,364 2.26 0.10
execute count 236,289 392.03 16.95
frame signature mismatch 0 0.00 0.00
free buffer inspected 47,882 79.44 3.43
free buffer requested 46,747 77.56 3.35
heap block compress 1,410 2.34 0.10
hot buffers moved to head of LRU 35,313 58.59 2.53
immediate (CR) block cleanout applications 1,149 1.91 0.08
immediate (CURRENT) block cleanout applications 14 0.02 0.00
index fetch by key 424,534 704.35 30.45
index scans kdiixs1 43,548 72.25 3.12
leaf node 90-10 splits 2 0.00 0.00
leaf node splits 2 0.00 0.00
lob reads 317 0.53 0.02
lob writes 121 0.20 0.01
lob writes unaligned 121 0.20 0.01
logons cumulative 36 0.06 0.00
messages received 17,995 29.86 1.29
messages sent 17,995 29.86 1.29
no buffer to keep pinned count 0 0.00 0.00
no work - consistent read gets 213,002 353.39 15.28
opened cursors cumulative 44,680 74.13 3.21
parse count (hard) 667 1.11 0.05
parse count (total) 51,654 85.70 3.71
parse time cpu 612 1.02 0.04
parse time elapsed 2,882 4.78 0.21
physical read IO requests 31,058 51.53 2.23
physical read bytes 509,362,176 845,088.99 36,539.61
physical read total IO requests 31,864 52.87 2.29
physical read total bytes 522,567,680 866,998.40 37,486.92
physical read total multi block requests 10 0.02 0.00
physical reads 31,089 51.58 2.23
physical reads cache 31,089 51.58 2.23
physical reads cache prefetch 31 0.05 0.00
physical reads direct 0 0.00 0.00
physical reads direct (lob) 0 0.00 0.00
physical reads direct temporary tablespace 0 0.00 0.00
physical reads prefetch warmup 0 0.00 0.00
physical write IO requests 10,764 17.86 0.77
physical write bytes 397,180,928 658,967.71 28,492.18
physical write total IO requests 22,857 37.92 1.64
physical write total bytes 472,509,440 783,946.17 33,895.94
physical write total multi block requests 17,212 28.56 1.23
physical writes 24,242 40.22 1.74
physical writes direct 9 0.01 0.00
physical writes from cache 24,233 40.21 1.74
physical writes non checkpoint 16,697 27.70 1.20
pinned buffers inspected 0 0.00 0.00
prefetch clients - default 2 0.00 0.00
prefetch warmup blocks aged out before use 0 0.00 0.00
prefetched blocks aged out before use 0 0.00 0.00
process last non-idle time 586 0.97 0.04
recursive calls 512,815 850.82 36.79
recursive cpu usage 8,174 13.56 0.59
redo blocks written 121,499 201.58 8.72
redo buffer allocation retries 0 0.00 0.00
redo entries 126,420 209.74 9.07
redo log space requests 0 0.00 0.00
redo log space wait time 0 0.00 0.00
redo ordering marks 89 0.15 0.01
redo size 57,218,140 94,931.31 4,104.60
redo synch time 215,211 357.06 15.44
redo synch writes 33,454 55.50 2.40
redo wastage 2,958,752 4,908.90 212.25
redo write time 50,488 83.77 3.62
redo writer latching time 6 0.01 0.00
redo writes 12,592 20.89 0.90
rollback changes - undo records applied 68,985 114.45 4.95
rollbacks only - consistent read gets 602 1.00 0.04
rows fetched via callback 350,378 581.32 25.13
session connect time 0 0.00 0.00
session cursor cache hits 41,220 68.39 2.96
session logical reads 1,876,963 3,114.09 134.65
session pga memory 65,536 108.73 4.70
session pga memory max 393,216 652.39 28.21
session uga memory 1,045,472 1,734.56 75.00
session uga memory max 19,836,940 32,911.71 1,423.02
shared hash latch upgrades - no wait 49,565 82.23 3.56
shared hash latch upgrades - wait 8 0.01 0.00
sorts (memory) 4,825 8.01 0.35
sorts (rows) 55,099 91.42 3.95
sql area evicted 246 0.41 0.02
sql area purged 0 0.00 0.00
summed dirty queue length 43,319 71.87 3.11
switch current to new buffer 0 0.00 0.00
table fetch by rowid 4,740,174 7,864.48 340.04
table fetch continued row 195 0.32 0.01
table scan blocks gotten 6,162 10.22 0.44
table scan rows gotten 31,702 52.60 2.27
table scans (short tables) 226 0.37 0.02
total number of times SMON posted 17 0.03 0.00
transaction rollbacks 13,797 22.89 0.99
undo change vector size 15,866,644 26,324.54 1,138.21
user I/O wait time 341,954 567.34 24.53
user calls 65,076 107.97 4.67
user commits 13,940 23.13 1.00
user rollbacks 0 0.00 0.00
workarea executions - optimal 3,516 5.83 0.25
write clones created in foreground 22 0.04 0.00

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

  • Statistics with absolute values (should not be diffed)
StatisticBegin ValueEnd Value
session cursor cache count 8,812 9,267
opened cursors current 385 387
logons current 29 29

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

  • Statistics identified by '(derived)' come from sources other than SYSSTAT
StatisticTotalper Hour
log switches (derived) 0 0.00

Back to Instance Activity Statistics
Back to Top

IO Stats

Back to Top

Tablespace IO Stats

  • ordered by IOs (Reads + Writes) desc
TablespaceReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)
USERS 30,182 50 111.06 1.00 5,995 10 745 21.01
UNDOTBS1 2 0 155.00 1.00 4,703 8 15 0.00
SYSTEM 642 1 83.30 1.01 9 0 0 0.00
SYSAUX 432 1 116.60 1.06 164 0 0 0.00
TEMP 1 0 400.00 1.00 2 0 0 0.00

Back to IO Stats
Back to Top

File IO Stats

  • ordered by Tablespace, File
TablespaceFilenameReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)
SYSAUX /u02/app/oracle/oradata/TESTIBM/sysaux01.dbf 432 1 116.60 1.06 164 0 0 0.00
SYSTEM /u02/app/oracle/oradata/TESTIBM/system01.dbf 642 1 83.30 1.01 9 0 0 0.00
TEMP /u02/app/oracle/oradata/TESTIBM/temp01.dbf 1 0 400.00 1.00 2 0 0  
UNDOTBS1 /u02/app/oracle/oradata/TESTIBM/undotbs01.dbf 2 0 155.00 1.00 4,703 8 15 0.00
USERS /u02/app/oracle/oradata/TESTIBM/users01.dbf 30,182 50 111.06 1.00 5,995 10 745 21.01

Back to IO Stats
Back to Top

Buffer Pool Statistics

  • Standard block size Pools D: default, K: keep, R: recycle
  • Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
PNumber of BuffersPool Hit%Buffer GetsPhysical ReadsPhysical WritesFree Buff WaitWrit Comp WaitBuffer Busy Waits
D 44,812 98 1,878,409 31,077 24,296 0 0 760


Back to Top

Advisory Statistics

Back to Top

Instance Recovery Stats

  • B: Begin snapshot, E: End snapshot

Targt MTTR (s) Estd MTTR (s)Recovery Estd IOsActual Redo BlksTarget Redo BlksLog File Size Redo BlksLog Ckpt Timeout Redo BlksLog Ckpt Interval Redo Blks
B 0 17 6376 65433 1094425 1105920 1094425  
E 0 17 7012 64833 817068 1105920 817068  

Back to Advisory Statistics
Back to Top

Buffer Pool Advisory

  • Only rows with estimated physical reads >0 are displayed
  • ordered by Block Size, Buffers For Estimate
PSize for Est (M)Size FactorBuffers for EstimateEst Phys Read FactorEstimated Physical Reads
D 68 0.10 4,284 5.34 3,287,009
D 136 0.19 8,568 2.69 1,657,555
D 204 0.28 12,852 2.15 1,326,591
D 272 0.38 17,136 1.82 1,120,813
D 340 0.47 21,420 1.60 982,692
D 408 0.57 25,704 1.43 881,118
D 476 0.66 29,988 1.30 802,298
D 544 0.76 34,272 1.20 736,910
D 612 0.85 38,556 1.11 683,493
D 680 0.95 42,840 1.04 637,849
D 716 1.00 45,108 1.00 616,098
D 748 1.04 47,124 0.97 597,883
D 816 1.14 51,408 0.91 562,243
D 884 1.23 55,692 0.86 531,794
D 952 1.33 59,976 0.82 506,936
D 1,020 1.42 64,260 0.79 485,682
D 1,088 1.52 68,544 0.76 467,516
D 1,156 1.61 72,828 0.73 452,428
D 1,224 1.71 77,112 0.71 439,220
D 1,292 1.80 81,396 0.70 428,164
D 1,360 1.90 85,680 0.68 418,356

Back to Advisory Statistics
Back to Top

PGA Aggr Summary

  • PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
PGA Cache Hit %W/A MB ProcessedExtra W/A MB Read/Written
100.00 120 0

Back to Advisory Statistics
Back to Top

PGA Aggr Target Stats

  • B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
  • Auto PGA Target - actual workarea memory target
  • W/A PGA Used - amount of memory used for all Workareas (manual + auto)
  • %PGA W/A Mem - percentage of PGA memory allocated to workareas
  • %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
  • %Man W/A Mem - percentage of workarea memory under manual control

PGA Aggr Target(M)Auto PGA Target(M)PGA Mem Alloc(M) W/A PGA Used(M) %PGA W/A Mem%Auto W/A Mem%Man W/A MemGlobal Mem Bound(K)
B 103 66 68.23 0.00 0.00 0.00 0.00 21,094
E 103 66 68.35 0.00 0.00 0.00 0.00 21,094

Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

  • Optimal Executions are purely in-memory operations
Low Optimal High OptimalTotal ExecsOptimal Execs1-Pass ExecsM-Pass Execs
2K 4K 3,321 3,321 0 0
64K 128K 3 3 0 0
256K 512K 2 2 0 0
512K 1024K 190 190 0 0

Back to Advisory Statistics
Back to Top

PGA Memory Advisory

  • When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0
PGA Target Est (MB)Size FactrW/A MB ProcessedEstd Extra W/A MB Read/ Written to Disk Estd PGA Cache Hit %Estd PGA Overalloc Count
13 0.13 1,228.87 76.82 94.00 29
26 0.25 1,228.87 76.82 94.00 28
52 0.50 1,228.87 0.00 100.00 0
77 0.75 1,228.87 0.00 100.00 0
103 1.00 1,228.87 0.00 100.00 0
124 1.20 1,228.87 0.00 100.00 0
144 1.40 1,228.87 0.00 100.00 0
165 1.60 1,228.87 0.00 100.00 0
185 1.80 1,228.87 0.00 100.00 0
206 2.00 1,228.87 0.00 100.00 0
309 3.00 1,228.87 0.00 100.00 0
412 4.00 1,228.87 0.00 100.00 0
618 6.00 1,228.87 0.00 100.00 0
824 8.00 1,228.87 0.00 100.00 0

Back to Advisory Statistics
Back to Top

Shared Pool Advisory

  • SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
  • Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.
Shared Pool Size(M)SP Size FactrEst LC Size (M)Est LC Mem ObjEst LC Time Saved (s)Est LC Time Saved FactrEst LC Load Time (s)Est LC Load Time FactrEst LC Mem Obj Hits
56 0.78 8 938 131,861 0.90 15,783 39.07 2,934,251
64 0.89 15 1,623 143,724 0.98 3,920 9.70 2,939,129
72 1.00 22 2,293 147,240 1.00 404 1.00 2,942,798
80 1.11 29 2,761 147,347 1.00 297 0.74 2,944,841
88 1.22 37 3,295 150,156 1.02 1 0.00 2,945,826
96 1.33 44 3,622 150,195 1.02 1 0.00 2,946,260
104 1.44 52 3,980 150,205 1.02 1 0.00 2,946,415
112 1.56 59 4,484 150,207 1.02 1 0.00 2,946,449
120 1.67 64 5,149 150,207 1.02 1 0.00 2,946,453
128 1.78 64 5,149 150,207 1.02 1 0.00 2,946,453
136 1.89 64 5,149 150,207 1.02 1 0.00 2,946,453
144 2.00 64 5,149 150,207 1.02 1 0.00 2,946,453

Back to Advisory Statistics
Back to Top

SGA Target Advisory

SGA Target Size (M)SGA Size FactorEst DB Time (s)Est Physical Reads
200 0.25 132,052 1,707,575
400 0.50 77,875 1,012,314
600 0.75 62,568 759,128
800 1.00 50,292 615,875
1,000 1.25 44,545 522,200
1,200 1.50 41,150 466,094
1,400 1.75 38,735 430,989
1,600 2.00 38,743 430,989

Back to Advisory Statistics
Back to Top

Streams Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

Java Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

Wait Statistics

Back to Top

Buffer Wait Statistics

  • ordered by wait time desc, waits desc
ClassWaitsTotal Wait Time (s)Avg Time (ms)
data block 744 16 22
undo header 15 0 0
2nd level bmb 1 0 0
segment header 1 0 0

Back to Wait Statistics
Back to Top

Enqueue Activity

  • only enqueues with waits are shown
  • Enqueue stats gathered prior to 10g should not be compared with 10g data
  • ordered by Wait Time desc, Waits desc
Enqueue Type (Request Reason)RequestsSucc GetsFailed GetsWaitsWt Time (s)Av Wt Time(ms)
TX-Transaction (row lock contention) 1,348 1,348 0 1,348 147 108.69
TX-Transaction 28,269 28,190 0 7 2 252.86
FB-Format Block 18 18 0 7 0 20.00
HW-Segment High Water Mark 108 108 0 8 0 0.00

Back to Wait Statistics
Back to Top

Undo Statistics

Back to Top

Undo Segment Summary

  • Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
  • STO - Snapshot Too Old count, OOS - Out of Space count
  • Undo segment block stats:
  • uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
  • eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo TS#Num Undo Blocks (K)Number of TransactionsMax Qry Len (s)Max Tx ConcurcyMin/Max TR (mins)STO/ OOS uS/uR/uU/ eS/eR/eU
1 34.93 70,414 37 12 29.6/36.1166666666666666666666666666666666667 0/0 0/0/0/0/0/0

Back to Undo Statistics
Back to Top

Undo Segment Stats

  • Most recent 35 Undostat rows, ordered by Time desc
End TimeNum Undo BlocksNumber of TransactionsMax Qry Len (s)Max Tx ConcyTun Ret (mins)STO/ OOS uS/uR/uU/ eS/eR/eU
10-Apr 10:57 20,906 42,006 0 11 36 0/0 0/0/0/0/0/0
10-Apr 10:47 14,021 28,408 37 12 30 0/0 0/0/0/0/0/0

Back to Undo Statistics
Back to Top

Latch Statistics

Back to Top

Latch Activity

  • "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests
  • "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
  • "Pct Misses" for both should be very close to 0.0
Latch NameGet RequestsPct Get MissAvg Slps /MissWait Time (s)NoWait RequestsPct NoWait Miss
AWR Alerted Metric Element list 2,160 0.00   0 0  
Consistent RBA 12,674 0.00   0 0  
FOB s.o list latch 157 0.00   0 0  
In memory undo latch 278,895 0.40 0.02 0 28,091 0.00
JS queue state obj latch 3,540 0.00   0 0  
KMG MMAN ready and startup request latch 198 0.00   0 0  
KTF sga latch 1 0.00   0 199 0.00
KWQMN job cache list latch 9 0.00   0 0  
MQL Tracking Latch 0     0 12 0.00
Memory Management Latch 0     0 198 0.00
OS process 120 0.00   0 0  
OS process allocation 278 0.00   0 0  
OS process: request allocation 72 0.00   0 0  
PL/SQL warning settings 32,168 1.27 0.00 0 0  
SGA IO buffer pool latch 825 0.00   0 825 0.00
SQL memory manager latch 1 0.00   0 191 0.00
SQL memory manager workarea list latch 14,130 0.00   0 0  
Shared B-Tree 22 0.00   0 0  
active checkpoint queue latch 5,364 0.00   0 0  
active service list 1,766 0.00   0 212 0.00
begin backup scn array 1 0.00   0 0  
cache buffer handles 184 0.00   0 0  
cache buffers chains 3,957,186 0.12 0.01 0 49,972 0.03
cache buffers lru chain 73,643 0.00 0.00 0 48,148 0.45
cache table scan latch 0     0 9 0.00
channel handle pool latch 74 0.00   0 0  
channel operations parent latch 3,877 0.00   0 0  
checkpoint queue latch 63,091 0.00   0 22,648 0.02
client/application info 187 0.00   0 0  
commit callback allocation 4 0.00   0 0  
compile environment latch 33,924 0.17 0.00 0 0  
dml lock allocation 197,803 0.96 0.01 0 0  
dummy allocation 72 0.00   0 0  
enqueue hash chains 269,997 0.61 0.01 0 1,883 0.00
enqueues 47,996 0.39 0.00 0 0  
event group latch 36 0.00   0 0  
file cache latch 270 0.00   0 0  
global KZLD latch for mem in SGA 34 0.00   0 0  
hash table column usage latch 314 0.00   0 15,201 0.00
hash table modification latch 3 0.00   0 0  
job_queue_processes parameter latch 10 0.00   0 0  
kks stats 1,811 0.00   0 0  
ksuosstats global area 42 0.00   0 0  
ktm global data 50 0.00   0 0  
kwqbsn:qsga 22 0.00   0 0  
lgwr LWN SCN 12,672 0.00   0 0  
library cache 295,862 0.51 0.01 0 3,558 0.00
library cache load lock 3,016 0.00   0 7 0.00
library cache lock 81,083 0.40 0.00 0 0  
library cache lock allocation 904 0.00   0 0  
library cache pin 136,154 0.57 0.00 0 0  
library cache pin allocation 308 0.00   0 0  
list of block allocation 75 0.00   0 0  
loader state object freelist 14 0.00   0 0  
message pool operations parent latch 2 0.00   0 0  
messages 54,292 0.05 0.00 0 0  
mostly latch-free SCN 12,696 0.05 0.00 0 0  
multiblock read objects 18 0.00   0 0  
ncodef allocation latch 10 0.00   0 0  
object queue header heap 11,071 0.00   0 254 0.00
object queue header operation 174,962 0.03 0.00 0 0  
object stats modification 1 0.00   0 0  
parallel query alloc buffer 76 0.00   0 0  
parameter list 129 0.00   0 0  
parameter table allocation management 36 0.00   0 0  
post/wait queue 31,776 0.00   0 27,848 0.15
process allocation 72 0.00   0 36 0.00
process group creation 72 0.00   0 0  
qmn task queue latch 84 0.00   0 0  
redo allocation 94,567 0.05 0.06 0 126,707 0.20
redo copy 0     0 126,821 0.18
redo writing 44,053 0.00 0.00 0 0  
resmgr group change latch 37 0.00   0 0  
resmgr:actses active list 69 0.00   0 0  
resmgr:actses change group 34 0.00   0 0  
resmgr:free threads list 68 0.00   0 0  
resmgr:schema config 1 0.00   0 0  
row cache objects 295,191 0.02 0.00 0 2,425 0.08
rules engine aggregate statistics 1 0.00   0 0  
rules engine rule set statistics 2 0.00   0 0  
sequence cache 490 0.00   0 0  
session allocation 353,834 0.23 0.01 0 0  
session idle bit 164,566 0.06 0.02 0 0  
session state list latch 70 0.00   0 0  
session switching 10 0.00   0 0  
session timer 212 0.00   0 0  
shared pool 64,905 0.07 0.02 0 0  
shared pool sim alloc 2 0.00   0 0  
shared pool simulator 72,504 0.00   0 0  
simulator hash latch 116,163 0.00 0.00 0 0  
simulator lru latch 109,128 0.08 0.06 0 2,855 0.00
slave class 2 0.00   0 0  
slave class create 8 0.00   0 0  
sort extent pool 37 0.00   0 0  
state object free list 2 0.00   0 0  
statistics aggregation 112 0.00   0 0  
temp lob duration state obj allocation 2 0.00   0 0  
threshold alerts latch 35 0.00   0 0  
transaction allocation 73 0.00   0 0  
transaction branch allocation 10 0.00   0 0  
undo global data 175,064 0.22 0.00 0 0  
user lock 136 0.00   0 0  

Back to Latch Statistics
Back to Top

Latch Sleep Breakdown

  • ordered by misses desc
Latch NameGet RequestsMissesSleepsSpin GetsSleep1Sleep2Sleep3
cache buffers chains 3,957,186 4,926 26 4,900 0 0 0
dml lock allocation 197,803 1,908 20 1,888 0 0 0
enqueue hash chains 269,997 1,644 23 1,621 0 0 0
library cache 295,862 1,516 14 1,502 0 0 0
In memory undo latch 278,895 1,127 23 1,104 0 0 0
session allocation 353,834 822 6 816 0 0 0
session idle bit 164,566 91 2 89 0 0 0
simulator lru latch 109,128 87 5 82 0 0 0
redo allocation 94,567 51 3 48 0 0 0
shared pool 64,905 48 1 47 0 0 0

Back to Latch Statistics
Back to Top

Latch Miss Sources

  • only latches with sleeps are shown
  • ordered by name, sleeps desc
Latch NameWhereNoWait Misses SleepsWaiter Sleeps
In memory undo latch ktiFlush: child 0 12 19
In memory undo latch ktichg: child 0 4 1
In memory undo latch kticmt: child 0 4 0
In memory undo latch ktiTxnPoolFree 0 3 0
cache buffers chains kcbgtcr: fast path 0 27 23
cache buffers chains kcbgcur: kslbegin 0 7 6
cache buffers chains kcbzgb: scan from tail. nowait 0 3 0
cache buffers chains kcbchg: kslbegin: bufs not pinned 0 2 11
cache buffers chains kcbgtcr: kslbegin excl 0 2 3
cache buffers chains kcbgtcr: kslbegin shared 0 2 1
cache buffers chains kcbnew: new latch again 0 2 0
cache buffers chains kcb_post_apply: kcbhq62 0 1 0
cache buffers chains kcbget: exchange rls 0 1 0
dml lock allocation ktaiam 0 15 20
dml lock allocation ktaidm 0 5 0
enqueue hash chains ksqgtl3 0 16 21
enqueue hash chains ksqrcl 0 7 2
library cache kglpnp: child 0 6 13
library cache kglpndl: child: after processing 0 3 0
library cache kglLockCursor 0 2 0
library cache kglati 0 1 0
library cache kglpin 0 1 0
library cache lock kgllkdl: child: no lock handle 0 1 0
redo allocation kcrfw_redo_gen: redo allocation 1 0 3 0
session allocation ksuprc 0 3 0
session allocation ksuxds: not user session 0 2 0
session allocation ksucri 0 1 6
session idle bit ksupuc: set busy 0 2 0
shared pool kghalo 0 1 1
simulator lru latch kcbs_simulate: simulate set 0 5 5

Back to Latch Statistics
Back to Top

Parent Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

Child Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

Segment Statistics

Back to Top

Segments by Logical Reads

  • Total Logical Reads: 1,876,963
  • Captured Segments account for 84.6% of Total
OwnerTablespace NameObject NameSubobject NameObj. TypeLogical Reads%Total
TPCC USERS STOCK_I1   INDEX 557,104 29.68
TPCC USERS STOCK   TABLE 277,584 14.79
TPCC USERS CUSTOMER_I2   INDEX 114,800 6.12
TPCC USERS CUSTOMER   TABLE 108,800 5.80
TPCC USERS CUSTOMER_I1   INDEX 82,064 4.37

Back to Segment Statistics
Back to Top

Segments by Physical Reads

  • Total Physical Reads: 31,089
  • Captured Segments account for 97.8% of Total
OwnerTablespace NameObject NameSubobject NameObj. TypePhysical Reads%Total
TPCC USERS ITEM_ID   INDEX 13,551 43.59
TPCC USERS ITEM   TABLE 7,718 24.83
TPCC USERS CUSTOMER   TABLE 4,403 14.16
TPCC USERS STOCK   TABLE 3,692 11.88
TPCC USERS ORDERS   TABLE 352 1.13

Back to Segment Statistics
Back to Top

Segments by Row Lock Waits

  • % of Capture shows % of row lock waits for each top segment compared
  • with total row lock waits for all segments captured by the Snapshot
OwnerTablespace NameObject NameSubobject NameObj. TypeRow Lock Waits% of Capture
TPCC USERS DISTRICT   TABLE 1,410 51.42
TPCC USERS WAREHOUSE   TABLE 1,332 48.58

Back to Segment Statistics
Back to Top

Segments by ITL Waits

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by Buffer Busy Waits

  • % of Capture shows % of Buffer Busy Waits for each top segment compared
  • with total Buffer Busy Waits for all segments captured by the Snapshot
OwnerTablespace NameObject NameSubobject NameObj. TypeBuffer Busy Waits% of Capture
TPCC USERS ORDERS   TABLE 500 76.69
TPCC USERS WAREHOUSE   TABLE 77 11.81
TPCC USERS HISTORY   TABLE 52 7.98
TPCC USERS DISTRICT   TABLE 22 3.37
TPCC USERS ORDERS_I1   INDEX 1 0.15

Back to Segment Statistics
Back to Top

Dictionary Cache Stats

  • "Pct Misses" should be very low (< 2% in most cases)
  • "Final Usage" is the number of cache entries being used
CacheGet RequestsPct MissScan ReqsPct MissMod ReqsFinal Usage
dc_awr_control 14 14.29 0   2 1
dc_global_oids 72 31.94 0   0 2
dc_histogram_data 4,011 34.60 0   0 371
dc_histogram_defs 7,939 39.84 0   0 499
dc_object_grants 54 25.93 0   0 3
dc_object_ids 5,050 9.35 0   0 172
dc_objects 2,499 19.93 0   0 166
dc_profiles 34 8.82 0   0 1
dc_rollback_segments 141 0.00 0   0 23
dc_segments 2,032 28.94 0   5 119
dc_sequences 17 35.29 0   17 0
dc_tablespace_quotas 1 100.00 0   0 0
dc_tablespaces 73,539 0.00 0   0 5
dc_usernames 228 3.51 0   0 4
dc_users 4,136 0.60 0   0 15
outstanding_alerts 13 76.92 0   1 0


Back to Top

Library Cache Activity

  • "Pct Misses" should be very low
NamespaceGet RequestsPct MissPin RequestsPct MissReloadsInvali- dations
BODY 8 37.50 15,326 0.07 7 0
CLUSTER 39 0.00 93 4.30 4 0
INDEX 40 70.00 111 27.93 3 0
SQL AREA 603 85.07 273,676 0.63 145 0
TABLE/PROCEDURE 1,296 25.62 53,001 2.71 401 0


Back to Top

Memory Statistics

Back to Top

Process Memory Summary

  • B: Begin snap E: End snap
  • All rows below contain absolute values (i.e. not diffed over the interval)
  • Max Alloc is Maximum PGA Allocation size at snapshot time
  • Hist Max Alloc is the Historical Max Allocation for still-connected processes
  • ordered by Begin/End snapshot, Alloc (MB) desc

CategoryAlloc (MB)Used (MB)Avg Alloc (MB)Std Dev Alloc (MB)Max Alloc (MB)Hist Max Alloc (MB)Num ProcNum Alloc
B Other 52.60   1.81 4.01 22 22 29 29
  Freeable 12.69 0.00 1.15 0.93 4   11 11
  SQL 1.55 0.67 0.07 0.03 0 5 23 23
  PL/SQL 1.40 0.52 0.05 0.05 0 0 29 29
E Other 52.66   1.82 4.01 22 22 29 29
  Freeable 12.75 0.00 1.16 0.91 4   11 11
  SQL 1.56 0.66 0.07 0.03 0 5 23 23
  PL/SQL 1.40 0.49 0.05 0.05 0 0 29 29

Back to Memory Statistics
Back to Top

SGA Memory Summary

SGA regionsBegin Size (Bytes)End Size (Bytes) (if different)
Database Buffers 746,586,112  
Fixed Size 1,263,572  
Redo Buffers 7,122,944  
Variable Size 83,888,172  

Back to Memory Statistics
Back to Top

SGA breakdown difference

  • ordered by Pool, Name
  • N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot
PoolNameBegin MBEnd MB% Diff
java free memory 4.00 4.00 0.00
large PX msg pool 1.02 1.02 0.00
large free memory 2.98 2.98 0.00
shared ASH buffers 8.00 8.00 0.00
shared CCursor 0.81   -100.00
shared FileOpenBlock 1.58 1.58 0.00
shared KCB Table Scan Buffer 3.80 3.80 0.00
shared KGH: NO ACCESS 3.31 3.31 0.00
shared KSFD SGA I/O b 3.79 3.79 0.00
shared KSXR pending messages que 0.80 0.80 0.00
shared KSXR receive buffers 0.99 0.99 0.00
shared KTI-UNDO 2.36 2.36 0.00
shared db_block_hash_buckets 1.13 1.13 0.00
shared dbwriter coalesce buffer 1.00 1.00 0.00
shared event statistics per sess 2.56 2.56 0.00
shared free memory 12.28 12.75 3.84
shared kglsim hash table bkts 2.00 2.00 0.00
shared kks stbkt 0.88 0.88 0.00
shared library cache 2.79 2.79 -0.04
shared private strands 2.29 2.29 0.00
shared row cache 3.57 3.57 0.00
shared sessions 1.54 1.54 0.00
shared sql area 2.30 2.13 -7.45
  buffer_cache 712.00 712.00 0.00
  fixed_sga 1.21 1.21 0.00
  log_buffer 6.79 6.79 0.00

Back to Memory Statistics
Back to Top

Streams Statistics

Back to Top

Streams CPU/IO Usage

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Streams Capture

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Streams Apply

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Buffered Queues

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Buffered Subscribers

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Rule Set

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Resource Limit Stats

No data exists for this section of the report.


Back to Top

init.ora Parameters

Parameter NameBegin valueEnd value (if different)
audit_file_dest /opt/oracle/admin/TESTIBM/adump   
background_dump_dest /opt/oracle/admin/TESTIBM/bdump   
compatible 10.2.0.2.0   
control_files /u02/app/oracle/oradata/TESTIBM/control01.ctl, /u02/app/oracle/oradata/TESTIBM/control02.ctl, /u03/app/oracle/oradata/TESTIBM/control03.ctl   
core_dump_dest /opt/oracle/admin/TESTIBM/cdump   
db_block_size 16384   
db_domain      
db_file_multiblock_read_count 16   
db_name TESTIBM   
filesystemio_options ASYNCH   
job_queue_processes 10   
open_cursors 300   
pga_aggregate_target 108003328   
processes 300   
remote_login_passwordfile EXCLUSIVE   
sessions 335   
sga_target 838860800   
undo_management AUTO   
undo_tablespace UNDOTBS1   
user_dump_dest /opt/oracle/admin/TESTIBM/udump   
Comments