WORKLOAD REPOSITORY report for

 

DB Name DB Id Instance Inst num Release RAC Host
O102EE1 2384218455 o102ee1 1 10.2.0.3.0 NO hpap0601

 

  Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 7478 02-Apr-07 20:01:05 24 11.5
End Snap: 7479 02-Apr-07 20:09:17 24 11.8
Elapsed:   8.21 (mins)    
DB Time:   31.18 (mins)    

 

Report Summary

Cache Sizes

 

  Begin End    
Buffer Cache: 92M 92M Std Block Size: 8K
Shared Pool Size: 148M 148M Log Buffer: 6,960K

Load Profile

 

  Per Second Per Transaction
Redo size: 9,225.19 2,188.74
Logical reads: 109,152.58 25,897.25
Block changes: 41.65 9.88
Physical reads: 6,637.45 1,574.78
Physical writes: 4.19 0.99
User calls: 291.04 69.05
Parses: 117.25 27.82
Hard parses: 53.76 12.76
Sorts: 8.37 1.99
Logons: 0.04 0.01
Executes: 131.21 31.13
Transactions: 4.21  

 

% Blocks changed per Read: 0.04 Recursive Call %: 58.40
Rollback per transaction %: 0.10 Rows per Sort: 11.47

Instance Efficiency Percentages (Target 100%)

 

Buffer Nowait %: 99.01 Redo NoWait %: 100.00
Buffer Hit %: 93.92 In-memory Sort %: 100.00
Library Hit %: 60.84 Soft Parse %: 54.15
Execute to Parse %: 10.64 Latch Hit %: 98.31
Parse CPU to Parse Elapsd %: 89.82 % Non-Parse CPU: 96.11

Shared Pool Statistics

 

  Begin End
Memory Usage %: 70.15 73.54
% SQL with executions>1: 28.38 17.46
% Memory for SQL w/exec>1: 29.51 19.63

Top 5 Timed Events

 

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time   1,522   81.3  
read by other session 532,940 194 0 10.4 User I/O
db file scattered read 463,215 56 0 3.0 User I/O
db file sequential read 557,900 20 0 1.1 User I/O
latch: cache buffers chains 3,042 13 4 .7 Concurrency

Main Report


Back to Top

Wait Events Statistics

Back to Top

Time Model Statistics

  • Total time in database user-calls (DB Time): 1870.8s
  • 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 1,789.04 95.63
DB CPU 1,521.67 81.34
parse time elapsed 92.41 4.94
hard parse elapsed time 75.54 4.04
PL/SQL compilation elapsed time 27.88 1.49
PL/SQL execution elapsed time 1.16 0.06
hard parse (sharing criteria) elapsed time 0.15 0.01
connection management call elapsed time 0.14 0.01
hard parse (bind mismatch) elapsed time 0.07 0.00
repeated bind elapsed time 0.06 0.00
sequence load elapsed time 0.00 0.00
DB time 1,870.76  
background elapsed time 25.61  
background cpu time 2.68  

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 Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
User I/O 1,554,055 0.00 271 0 748.94
System I/O 3,379 0.00 16 5 1.63
Concurrency 3,444 0.26 14 4 1.66
Other 2,214 0.00 10 5 1.07
Commit 2,039 0.00 6 3 0.98
Network 91,201 0.00 1 0 43.95
Application 36 0.00 0 2 0.02

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)
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
read by other session 532,940 0.00 194 0 256.84
db file scattered read 463,215 0.00 56 0 223.24
db file sequential read 557,900 0.00 20 0 268.87
latch: cache buffers chains 3,042 0.00 13 4 1.47
latch free 2,100 0.00 10 5 1.01
control file parallel write 169 0.00 8 49 0.08
log file parallel write 2,246 0.00 8 3 1.08
log file sync 2,039 0.00 6 3 0.98
latch: shared pool 307 0.00 1 2 0.15
SQL*Net message to client 91,168 0.00 1 0 43.94
latch: library cache 72 0.00 0 3 0.03
library cache load lock 1 0.00 0 133 0.00
cursor: pin S wait on X 9 100.00 0 14 0.00
latch: cache buffers lru chain 42 0.00 0 3 0.02
buffer exterminate 1 0.00 0 99 0.00
SQL*Net break/reset to client 36 0.00 0 2 0.02
LGWR wait for redo copy 67 0.00 0 1 0.03
latch: row cache objects 8 0.00 0 5 0.00
SQL*Net more data to client 31 0.00 0 1 0.01
wait list latch free 2 0.00 0 13 0.00
control file sequential read 964 0.00 0 0 0.46
latch: library cache pin 1 0.00 0 10 0.00
latch: library cache lock 4 0.00 0 1 0.00
latch: object queue header operation 2 0.00 0 2 0.00
SQL*Net more data from client 2 0.00 0 0 0.00
SQL*Net message from client 91,168 0.00 2,717 30 43.94
Streams AQ: qmn slave idle wait 18 0.00 498 27680 0.01
Streams AQ: qmn coordinator idle wait 39 53.85 498 12775 0.02
Streams AQ: waiting for messages in the queue 99 100.00 484 4885 0.05
wait for unread message on broadcast channel 491 100.00 481 980 0.24
jobq slave wait 164 95.12 479 2923 0.08
SGA: MMAN sleep for component shrink 16 75.00 0 11 0.01

Back to Wait Events Statistics
Back to Top

Background Wait Events

  • ordered by wait time desc, waits desc (idle events last)
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
control file parallel write 169 0.00 8 49 0.08
log file parallel write 2,246 0.00 8 3 1.08
events in waitclass Other 89 0.00 0 1 0.04
db file sequential read 20 0.00 0 0 0.01
control file sequential read 12 0.00 0 0 0.01
rdbms ipc message 3,660 42.73 3,827 1046 1.76
smon timer 2 100.00 586 292979 0.00
Streams AQ: qmn slave idle wait 18 0.00 498 27680 0.01
Streams AQ: qmn coordinator idle wait 39 53.85 498 12775 0.02
pmon timer 165 100.00 481 2916 0.08
SGA: MMAN sleep for component shrink 16 75.00 0 11 0.01

Back to Wait Events Statistics
Back to Top

Operating System Statistics

Statistic Total
BUSY_TIME 185,753
IDLE_TIME 11,123
IOWAIT_TIME 478
NICE_TIME 689
SYS_TIME 23,995
USER_TIME 160,956
LOAD 4
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 48,632
NUM_CPUS 4
NUM_CPU_SOCKETS 2

Back to Wait Events Statistics
Back to Top

Service Statistics

  • ordered by DB Time
Service Name DB Time (s) DB CPU (s) Physical Reads Logical Reads
SYS$USERS 1,868.70 1,519.90 3,259,798 53,700,828
o102ee1 1.50 1.20 11 6,351
SYS$BACKGROUND 0.00 0.00 20 5,324

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 Name User I/O Total Wts User I/O Wt Time Concurcy Total Wts Concurcy Wt Time Admin Total Wts Admin Wt Time Network Total Wts Network Wt Time
SYS$USERS 1553951 27078 3443 1406 0 0 90463 58
o102ee1 11 0 0 0 0 0 719 0
SYS$BACKGROUND 20 0 0 0 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 Id SQL Module SQL Text
78 57 1,000 0.08 4.17 8zt0p6avg76yz SQL*Plus SELECT COUNT(ename) FROM emp3 ...
4 4 10,000 0.00 0.22 90p005ah4wm47 SQL*Plus select distinct emp1.deptno fr...
4 3 21 0.19 0.21 b058ymxj1rvkg Admin Connection SELECT sql_id, sql_text from v...
2 2 1 2.37 0.13 0cg8busnwuyh9 sqlplus@hpap0601 (TNS V1-V3) BEGIN DBMS_WORKLOAD_REPOSITO...
2 2 64 0.04 0.12 257rmrxgvaj4z Realtime Connection select begin_time, wait_class...
2 2 1 2.33 0.12 6cm3sr71q92uz SQL*Plus BEGIN statspack.snap(7); END; ...
2 2 10,000 0.00 0.12 5u3yjw57ppbsx SQL*Plus SELECT ENAME FROM EMP2 WHERE E...
1 1 8 0.13 0.06 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
1 1 32 0.03 0.06 b8b5jdj7khuaw Realtime Connection SELECT event#, sql_id, sql_p...
1 1 32 0.03 0.05 cakg0hdjjw2wf Realtime Connection select value from v$sysmetric ...

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 Id SQL Module SQL Text
57 78 1,000 0.06 4.17 8zt0p6avg76yz SQL*Plus SELECT COUNT(ename) FROM emp3 ...
4 4 10,000 0.00 0.22 90p005ah4wm47 SQL*Plus select distinct emp1.deptno fr...
3 4 21 0.16 0.21 b058ymxj1rvkg Admin Connection SELECT sql_id, sql_text from v...
2 2 1 2.37 0.13 0cg8busnwuyh9 sqlplus@hpap0601 (TNS V1-V3) BEGIN DBMS_WORKLOAD_REPOSITO...
2 2 1 2.33 0.12 6cm3sr71q92uz SQL*Plus BEGIN statspack.snap(7); END; ...
2 2 10,000 0.00 0.12 5u3yjw57ppbsx SQL*Plus SELECT ENAME FROM EMP2 WHERE E...
2 2 64 0.03 0.12 257rmrxgvaj4z Realtime Connection select begin_time, wait_class...
1 1 32 0.03 0.06 b8b5jdj7khuaw Realtime Connection SELECT event#, sql_id, sql_p...
1 1 8 0.12 0.06 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
1 1 32 0.03 0.05 cakg0hdjjw2wf Realtime Connection select value from v$sysmetric ...

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: 53,736,798
  • Captured SQL account for 9.9% of Total
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
1,557,001 1,000 1,557.00 2.90 56.72 77.97 8zt0p6avg76yz SQL*Plus SELECT COUNT(ename) FROM emp3 ...
140,000 10,000 14.00 0.26 3.90 4.07 90p005ah4wm47 SQL*Plus select distinct emp1.deptno fr...
39,999 10,000 4.00 0.07 2.15 2.15 5u3yjw57ppbsx SQL*Plus SELECT ENAME FROM EMP2 WHERE E...
35,407 1 35,407.00 0.07 2.37 2.37 0cg8busnwuyh9 sqlplus@hpap0601 (TNS V1-V3) BEGIN DBMS_WORKLOAD_REPOSITO...
27,336 1 27,336.00 0.05 2.33 2.33 6cm3sr71q92uz SQL*Plus BEGIN statspack.snap(7); END; ...
6,270 2,358 2.66 0.01 0.15 0.15 96g93hntrzjtr   select /*+ rule */ bucket_cnt,...
6,000 1,000 6.00 0.01 0.37 0.37 6kpzvzuv00t58 SQL*Plus update EMP5 set ename = 'aaa' ...
6,000 1,000 6.00 0.01 0.38 0.48 d9qr16p9z7xy2 SQL*Plus update emp4 set ename = 'aaa' ...
5,200 8 650.00 0.01 0.95 1.04 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
5,090 1,017 5.00 0.01 0.06 0.06 53saa2zkr6wc3   select intcol#, nvl(pos#, 0), ...

Back to SQL Statistics
Back to Top

SQL ordered by Reads

  • Total Disk Reads: 3,267,675
  • Captured SQL account for 9.8% of Total
Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
1,245 1 1,245.00 0.04 0.07 0.07 b7twbdp012nbw SQL*Plus select emp1no, ename from emp1...
1,244 1 1,244.00 0.04 0.07 0.07 4xqb04qv3vtg7 SQL*Plus select emp1no, ename from emp1...
1,235 1 1,235.00 0.04 0.08 0.08 ahnqq4n2849u8 SQL*Plus select emp1no, ename from emp1...
1,231 1 1,231.00 0.04 0.07 0.07 7dtyn9bahqv8n SQL*Plus select emp1no, ename from emp1...
1,217 1 1,217.00 0.04 0.07 0.07 7d8q61r65cnhs SQL*Plus select emp1no, ename from emp1...
1,216 1 1,216.00 0.04 0.07 0.07 36mswpf5cfhuc SQL*Plus select emp1no, ename from emp1...
1,196 1 1,196.00 0.04 0.08 0.08 19wddcpnyw0bm SQL*Plus select emp1no, ename from emp1...
1,175 1 1,175.00 0.04 0.07 0.07 42m7brc22hpwk SQL*Plus select emp1no, ename from emp1...
1,173 1 1,173.00 0.04 0.07 0.07 ag1rxfdgkw116 SQL*Plus select emp1no, ename from emp1...
1,165 1 1,165.00 0.04 0.08 0.08 cdp18jk8u8mny SQL*Plus select emp1no, ename from emp1...

Back to SQL Statistics
Back to Top

SQL ordered by Executions

  • Total Executions: 64,597
  • Captured SQL account for 61.1% of Total
Executions Rows Processed Rows per Exec CPU per Exec (s) Elap per Exec (s) SQL Id SQL Module SQL Text
10,000 9,999 1.00 0.00 0.00 5u3yjw57ppbsx SQL*Plus SELECT ENAME FROM EMP2 WHERE E...
10,000 10,000 1.00 0.00 0.00 90p005ah4wm47 SQL*Plus select distinct emp1.deptno fr...
2,358 1,444 0.61 0.00 0.00 96g93hntrzjtr   select /*+ rule */ bucket_cnt,...
1,300 20,342 15.65 0.00 0.00 db78fxqxwxt7r   select /*+ rule */ bucket, en...
1,017 1,528 1.50 0.00 0.00 53saa2zkr6wc3   select intcol#, nvl(pos#, 0), ...
1,000 1,000 1.00 0.00 0.00 5uq8u6hqrr21g SQL*Plus SELECT COUNT(ENAME) FROM EMP3 ...
1,000 1,000 1.00 0.00 0.00 6kpzvzuv00t58 SQL*Plus update EMP5 set ename = 'aaa' ...
1,000 1,000 1.00 0.06 0.08 8zt0p6avg76yz SQL*Plus SELECT COUNT(ename) FROM emp3 ...
1,000 1,000 1.00 0.00 0.00 d9qr16p9z7xy2 SQL*Plus update emp4 set ename = 'aaa' ...
883 883 1.00 0.00 0.00 3c1kubcdjnppq   update sys.col_usage$ set eq...
651 651 1.00 0.00 0.00 2ym6hhaq30r73   select type#, blocks, extents,...

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

  • Total Parse Calls: 57,722
  • Captured SQL account for 53.1% of Total
Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
10,000 10,000 17.32 5u3yjw57ppbsx SQL*Plus SELECT ENAME FROM EMP2 WHERE E...
10,000 10,000 17.32 90p005ah4wm47 SQL*Plus select distinct emp1.deptno fr...
1,000 1,000 1.73 5uq8u6hqrr21g SQL*Plus SELECT COUNT(ENAME) FROM EMP3 ...
1,000 1,000 1.73 6kpzvzuv00t58 SQL*Plus update EMP5 set ename = 'aaa' ...
1,000 1,000 1.73 8zt0p6avg76yz SQL*Plus SELECT COUNT(ename) FROM emp3 ...
1,000 1,000 1.73 d9qr16p9z7xy2 SQL*Plus update emp4 set ename = 'aaa' ...
651 651 1.13 2ym6hhaq30r73   select type#, blocks, extents,...
317 883 0.55 3c1kubcdjnppq   update sys.col_usage$ set eq...
317 317 0.55 b2gnxm5z6r51n   lock table sys.col_usage$ in e...
188 188 0.33 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

  • Only Statements with Version Count greater than 20 are displayed
Version Count Executions SQL Id SQL Module SQL Text
100 16 18naypzfmabd6 OEM.BoundedPool INSERT INTO MGMT_SYSTEM_PERFOR...

Back to SQL Statistics
Back to Top

 

Complete List of SQL Text

 

SQL Id SQL Text
0cg8busnwuyh9 BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); END;
18naypzfmabd6 INSERT INTO MGMT_SYSTEM_PERFORMANCE_LOG (JOB_NAME, TIME, DURATION, MODULE, ACTION, IS_TOTAL, NAME, VALUE, CLIENT_DATA, HOST_URL) VALUES (:B9 , SYSDATE, :B8 , SUBSTR(:B7 , 1, 512), SUBSTR(:B6 , 1, 32), :B5 , SUBSTR(:B4 , 1, 128), SUBSTR(:B3 , 1, 128), SUBSTR(:B2 , 1, 128), SUBSTR(:B1 , 1, 256))
19wddcpnyw0bm select emp1no, ename from emp1 where emp1no = 19914
257rmrxgvaj4z select begin_time, wait_class#, (time_waited)/(intsize_csec/100) from v$waitclassmetric union all select begin_time, -1, value from v$sysmetric where metric_name = 'CPU Usage Per Sec' and group_id = 2 order by begin_time, wait_class#
2ym6hhaq30r73 select 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
36mswpf5cfhuc select emp1no, ename from emp1 where emp1no = 15984
3c1kubcdjnppq update 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
42m7brc22hpwk select emp1no, ename from emp1 where emp1no = 15989
4xqb04qv3vtg7 select emp1no, ename from emp1 where emp1no = 15993
53saa2zkr6wc3 select intcol#, nvl(pos#, 0), col#, nvl(spare1, 0) from ccol$ where con#=:1
5u3yjw57ppbsx SELECT ENAME FROM EMP2 WHERE EMP2NO = :num
5uq8u6hqrr21g SELECT COUNT(ENAME) FROM EMP3 WHERE EMP3NO = 5
6cm3sr71q92uz BEGIN statspack.snap(7); END;
6gvch1xu9ca3g DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
6kpzvzuv00t58 update EMP5 set ename = 'aaa' where EMP5no = 100
7d8q61r65cnhs select emp1no, ename from emp1 where emp1no = 15987
7dtyn9bahqv8n select emp1no, ename from emp1 where emp1no = 15999
8swypbbr0m372 select order#, columns, types from access$ where d_obj#=:1
8zt0p6avg76yz SELECT COUNT(ename) FROM emp3 WHERE emp3no = 1
90p005ah4wm47 select distinct emp1.deptno from emp1, dept where emp1.deptno = dept.deptno and dept.deptno = 10
96g93hntrzjtr select /*+ 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
ag1rxfdgkw116 select emp1no, ename from emp1 where emp1no = 15983
ahnqq4n2849u8 select emp1no, ename from emp1 where emp1no = 15990
b058ymxj1rvkg SELECT sql_id, sql_text from v$sql WHERE sql_id in (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
b2gnxm5z6r51n lock table sys.col_usage$ in exclusive mode nowait
b7twbdp012nbw select emp1no, ename from emp1 where emp1no = 15996
b8b5jdj7khuaw SELECT event#, sql_id, sql_plan_hash_value, sql_opcode, session_id, session_serial#, module, action, client_id, DECODE(wait_time, 0, 'W', 'C'), 1, time_waited, service_hash, user_id, program, sample_time, p1, p2, p3, current_file#, current_obj#, current_block#, qc_session_id, qc_instance_id FROM v$active_session_history WHERE sample_time > :1 AND sample_time <= :2
cakg0hdjjw2wf select value from v$sysmetric where group_id = 2 and metric_id = :1
cdp18jk8u8mny select emp1no, ename from emp1 where emp1no = 15992
d9qr16p9z7xy2 update emp4 set ename = 'aaa' where emp4no = 1000000
db78fxqxwxt7r select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket

Back to SQL Statistics
Back to Top

 

Instance Activity Statistics

Back to Top

Instance Activity Stats

Statistic Total per Second per Trans
CPU used by this session 154,183 313.18 74.31
CPU used when call started 153,883 312.57 74.16
CR blocks created 159 0.32 0.08
Cached Commit SCN referenced 1,488,000 3,022.49 717.11
Commit SCN cached 1 0.00 0.00
DB time 337,693 685.94 162.74
DBWR checkpoint buffers written 1,502 3.05 0.72
DBWR checkpoints 0 0.00 0.00
DBWR transaction table writes 13 0.03 0.01
DBWR undo block writes 359 0.73 0.17
IMU CR rollbacks 56 0.11 0.03
IMU Flushes 123 0.25 0.06
IMU Redo allocation size 49,976 101.51 24.08
IMU commits 2,067 4.20 1.00
IMU contention 0 0.00 0.00
IMU ktichg flush 0 0.00 0.00
IMU pool not allocated 0 0.00 0.00
IMU recursive-transaction flush 0 0.00 0.00
IMU undo allocation size 1,980,276 4,022.42 954.35
IMU- failed to get a private strand 0 0.00 0.00
Misses for writing mapping 0 0.00 0.00
SMON posted for undo segment shrink 0 0.00 0.00
SQL*Net roundtrips to/from client 91,164 185.18 43.93
active txn count during cleanout 130 0.26 0.06
application wait time 6 0.01 0.00
background checkpoints completed 0 0.00 0.00
background checkpoints started 0 0.00 0.00
background timeouts 1,568 3.18 0.76
branch node splits 0 0.00 0.00
buffer is not pinned count 69,146 140.45 33.32
buffer is pinned count 10,559 21.45 5.09
bytes received via SQL*Net from client 12,511,630 25,414.18 6,029.70
bytes sent via SQL*Net to client 19,116,850 38,831.00 9,212.94
calls to get snapshot scn: kcmgss 177,631 360.81 85.61
calls to kcmgas 2,990 6.07 1.44
calls to kcmgcs 77 0.16 0.04
change write time 62 0.13 0.03
cleanout - number of ktugct calls 155 0.31 0.07
cleanouts and rollbacks - consistent read gets 0 0.00 0.00
cleanouts only - consistent read gets 2 0.00 0.00
cluster key scan block gets 11,478 23.31 5.53
cluster key scans 6,324 12.85 3.05
commit batch performed 0 0.00 0.00
commit batch requested 0 0.00 0.00
commit batch/immediate performed 2 0.00 0.00
commit batch/immediate requested 2 0.00 0.00
commit cleanout failures: block lost 0 0.00 0.00
commit cleanout failures: buffer being written 0 0.00 0.00
commit cleanout failures: callback failure 14 0.03 0.01
commit cleanout failures: cannot pin 0 0.00 0.00
commit cleanouts 3,675 7.46 1.77
commit cleanouts successfully completed 3,661 7.44 1.76
commit immediate performed 2 0.00 0.00
commit immediate requested 2 0.00 0.00
commit txn count during cleanout 74 0.15 0.04
concurrency wait time 1,407 2.86 0.68
consistent changes 159 0.32 0.08
consistent gets 53,716,484 109,111.32 25,887.46
consistent gets - examination 112,512 228.54 54.22
consistent gets direct 0 0.00 0.00
consistent gets from cache 53,716,484 109,111.32 25,887.46
cursor authentications 51 0.10 0.02
data blocks consistent reads - undo records applied 159 0.32 0.08
db block changes 20,507 41.65 9.88
db block gets 20,314 41.26 9.79
db block gets direct 0 0.00 0.00
db block gets from cache 20,314 41.26 9.79
deferred (CURRENT) block cleanout applications 3,047 6.19 1.47
dirty buffers inspected 280 0.57 0.13
enqueue conversions 147 0.30 0.07
enqueue releases 36,147 73.42 17.42
enqueue requests 36,147 73.42 17.42
enqueue timeouts 0 0.00 0.00
enqueue waits 0 0.00 0.00
execute count 64,597 131.21 31.13
failed probes on index block reclamation 0 0.00 0.00
frame signature mismatch 0 0.00 0.00
free buffer inspected 4,201,761 8,534.80 2,024.95
free buffer requested 3,268,269 6,638.65 1,575.07
heap block compress 4 0.01 0.00
hot buffers moved to head of LRU 2,562 5.20 1.23
immediate (CR) block cleanout applications 2 0.00 0.00
immediate (CURRENT) block cleanout applications 245 0.50 0.12
index crx upgrade (positioned) 2,023 4.11 0.97
index fast full scans (full) 4 0.01 0.00
index fetch by key 41,245 83.78 19.88
index scans kdiixs1 19,646 39.91 9.47
leaf node 90-10 splits 12 0.02 0.01
leaf node splits 50 0.10 0.02
lob reads 0 0.00 0.00
lob writes 0 0.00 0.00
lob writes unaligned 0 0.00 0.00
logons cumulative 20 0.04 0.01
messages received 2,941 5.97 1.42
messages sent 2,941 5.97 1.42
no buffer to keep pinned count 0 0.00 0.00
no work - consistent read gets 53,483,923 108,638.93 25,775.38
opened cursors cumulative 64,322 130.65 31.00
parse count (failures) 0 0.00 0.00
parse count (hard) 26,468 53.76 12.76
parse count (total) 57,722 117.25 27.82
parse time cpu 5,919 12.02 2.85
parse time elapsed 6,590 13.39 3.18
physical read IO requests 1,022,372 2,076.69 492.71
physical read bytes 26,768,793,600 54,373,967.57 12,900,623.42
physical read total IO requests 1,023,340 2,078.65 493.18
physical read total bytes 26,784,620,544 54,406,115.96 12,908,250.86
physical read total multi block requests 464,021 942.54 223.62
physical reads 3,267,675 6,637.45 1,574.78
physical reads cache 3,267,675 6,637.45 1,574.78
physical reads cache prefetch 2,245,303 4,560.76 1,082.07
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 912 1.85 0.44
physical write bytes 16,891,904 34,311.59 8,140.68
physical write total IO requests 3,662 7.44 1.76
physical write total bytes 30,827,520 62,618.23 14,856.64
physical write total multi block requests 2,485 5.05 1.20
physical writes 2,062 4.19 0.99
physical writes direct 0 0.00 0.00
physical writes direct (lob) 0 0.00 0.00
physical writes direct temporary tablespace 0 0.00 0.00
physical writes from cache 2,062 4.19 0.99
physical writes non checkpoint 1,296 2.63 0.62
pinned buffers inspected 15,285 31.05 7.37
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 0 0.00 0.00
recursive calls 201,158 408.60 96.94
recursive cpu usage 8,239 16.74 3.97
redo blocks written 10,994 22.33 5.30
redo buffer allocation retries 0 0.00 0.00
redo entries 8,895 18.07 4.29
redo log space requests 0 0.00 0.00
redo log space wait time 0 0.00 0.00
redo ordering marks 209 0.42 0.10
redo size 4,541,644 9,225.19 2,188.74
redo subscn max counts 523 1.06 0.25
redo synch time 649 1.32 0.31
redo synch writes 2,473 5.02 1.19
redo wastage 980,504 1,991.64 472.53
redo write time 784 1.59 0.38
redo writer latching time 5 0.01 0.00
redo writes 2,246 4.56 1.08
rollback changes - undo records applied 2 0.00 0.00
rollbacks only - consistent read gets 159 0.32 0.08
rows fetched via callback 16,960 34.45 8.17
session connect time 0 0.00 0.00
session cursor cache hits 34,884 70.86 16.81
session logical reads 53,736,798 109,152.58 25,897.25
session pga memory 11,498,144 23,355.54 5,541.27
session pga memory max 38,826,656 78,866.44 18,711.64
session uga memory 34,360,181,400 69,793,933.08 16,559,123.57
session uga memory max 18,476,416 37,530.12 8,904.30
shared hash latch upgrades - no wait 2,275 4.62 1.10
sorts (disk) 0 0.00 0.00
sorts (memory) 4,119 8.37 1.99
sorts (rows) 47,244 95.96 22.77
sql area evicted 1,089 2.21 0.52
sql area purged 0 0.00 0.00
summed dirty queue length 382 0.78 0.18
switch current to new buffer 20 0.04 0.01
table fetch by rowid 26,892 54.62 12.96
table fetch continued row 228 0.46 0.11
table scan blocks gotten 53,331,826 108,329.98 25,702.08
table scan rows gotten 8,653,895,624 17,578,178.79 4,170,552.11
table scans (long tables) 16,002 32.50 7.71
table scans (short tables) 1,275 2.59 0.61
total number of times SMON posted 0 0.00 0.00
transaction rollbacks 2 0.00 0.00
undo change vector size 1,462,984 2,971.68 705.05
user I/O wait time 27,089 55.02 13.05
user calls 143,283 291.04 69.05
user commits 2,073 4.21 1.00
user rollbacks 2 0.00 0.00
workarea executions - onepass 0 0.00 0.00
workarea executions - optimal 2,340 4.75 1.13
write clones created in foreground 0 0.00 0.00

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

  • Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
session cursor cache count 323,059 323,448
opened cursors current 277 282
logons current 24 24

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

  • Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per 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
Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
USERS 1,019,913 2,072 0.07 3.19 3 0 532,785 0.38
SYSAUX 353 1 0.17 1.00 833 2 0 0.00
SYSTEM 326 1 0.03 1.00 21 0 0 0.00
UNDOTBS1 11 0 0.00 1.00 64 0 0 0.00

Back to IO Stats
Back to Top

File IO Stats

  • ordered by Tablespace, File
Tablespace Filename Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
SYSAUX /opt/app/oracle/oradata/o102ee1/sysaux01.dbf 353 1 0.17 1.00 833 2 0 0.00
SYSTEM /opt/app/oracle/oradata/o102ee1/system01.dbf 326 1 0.03 1.00 21 0 0 0.00
UNDOTBS1 /opt/app/oracle/oradata/o102ee1/undotbs01.dbf 11 0 0.00 1.00 64 0 0 0.00
USERS /opt/app/oracle/oradata/o102ee1/users01.dbf 1,019,913 2,072 0.07 3.19 3 0 532,785 0.38

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
P Number of Buffers Pool Hit% Buffer Gets Physical Reads Physical Writes Free Buff Wait Writ Comp Wait Buffer Busy Waits
D 11,454 94 53,651,906 3,265,093 2,157 0 0 532,936


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 IOs Actual Redo Blks Target Redo Blks Log File Size Redo Blks Log Ckpt Timeout Redo Blks Log Ckpt Interval Redo Blks
B 0 10 1454 14167 31282 184320 31282  
E 0 8 340 5837 31186 184320 31186  

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
P Size for Est (M) Size Factor Buffers for Estimate Est Phys Read Factor Estimated Physical Reads
D 8 0.09 998 1.03 35,821,637
D 16 0.17 1,996 1.00 34,788,021
D 24 0.26 2,994 1.00 34,775,353
D 32 0.35 3,992 1.00 34,762,212
D 40 0.43 4,990 1.00 34,741,003
D 48 0.52 5,988 1.00 34,730,740
D 56 0.61 6,986 1.00 34,726,568
D 64 0.70 7,984 1.00 34,723,257
D 72 0.78 8,982 1.00 34,721,018
D 80 0.87 9,980 1.00 34,719,236
D 88 0.96 10,978 1.00 34,717,662
D 92 1.00 11,477 1.00 34,716,993
D 96 1.04 11,976 1.00 34,716,324
D 104 1.13 12,974 1.00 34,715,004
D 112 1.22 13,972 1.00 34,713,990
D 120 1.30 14,970 1.00 34,713,013
D 128 1.39 15,968 1.00 34,712,009
D 136 1.48 16,966 1.00 34,711,257
D 144 1.57 17,964 1.00 34,710,335
D 152 1.65 18,962 1.00 34,709,104
D 160 1.74 19,960 0.90 31,365,579

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 Processed Extra W/A MB Read/Written
100.00 30 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 Mem Global Mem Bound(K)
B 16 4 85.36 0.00 0.00 0.00 0.00 3,276
E 16 4 86.04 0.00 0.00 0.00 0.00 3,276

Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

  • Optimal Executions are purely in-memory operations
Low Optimal High Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
2K 4K 2,302 2,302 0 0
64K 128K 3 3 0 0
128K 256K 1 1 0 0
512K 1024K 34 34 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 Factr W/A MB Processed Estd Extra W/A MB Read/ Written to Disk Estd PGA Cache Hit % Estd PGA Overalloc Count
12 0.75 12,519.56 662.39 95.00 134
16 1.00 12,519.56 27.30 100.00 134
19 1.20 12,519.56 27.30 100.00 134
22 1.40 12,519.56 27.30 100.00 134
26 1.60 12,519.56 27.30 100.00 133
29 1.80 12,519.56 27.30 100.00 128
32 2.00 12,519.56 25.86 100.00 104
48 3.00 12,519.56 0.00 100.00 19
64 4.00 12,519.56 0.00 100.00 1
96 6.00 12,519.56 0.00 100.00 0
128 8.00 12,519.56 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 Factr Est LC Size (M) Est LC Mem Obj Est LC Time Saved (s) Est LC Time Saved Factr Est LC Load Time (s) Est LC Load Time Factr Est LC Mem Obj Hits
84 0.57 17 3,289 182,229 0.88 26,704 9.43 10,412,937
100 0.68 32 5,062 188,854 0.92 20,079 7.09 10,425,649
116 0.78 47 6,824 195,190 0.95 13,743 4.85 10,437,445
132 0.89 62 8,563 200,997 0.98 7,936 2.80 10,448,384
148 1.00 77 10,329 206,100 1.00 2,833 1.00 10,458,487
164 1.11 92 12,128 210,402 1.02 1 0.00 10,467,477
180 1.22 107 14,244 213,881 1.04 1 0.00 10,475,174
196 1.32 122 16,244 216,582 1.05 1 0.00 10,482,020
212 1.43 137 18,252 218,599 1.06 1 0.00 10,489,001
228 1.54 152 20,234 220,049 1.07 1 0.00 10,496,538
244 1.65 167 22,511 221,054 1.07 1 0.00 10,504,418
260 1.76 182 25,156 221,726 1.08 1 0.00 10,512,520
276 1.86 197 28,020 222,162 1.08 1 0.00 10,521,630
292 1.97 212 31,045 222,442 1.08 1 0.00 10,532,816
308 2.08 227 34,151 222,625 1.08 1 0.00 10,547,536

Back to Advisory Statistics
Back to Top

SGA Target Advisory

SGA Target Size (M) SGA Size Factor Est DB Time (s) Est Physical Reads
128 0.50 54,772 34,466,860
192 0.75 32,228 34,466,860
256 1.00 32,215 34,398,064
320 1.25 32,212 34,387,745
384 1.50 31,555 31,085,530
448 1.75 31,555 31,085,530
512 2.00 31,555 31,085,530

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
Class Waits Total Wait Time (s) Avg Time (ms)
data block 532,796 202 0

Back to Wait Statistics
Back to Top

 

Enqueue Activity

No data exists for this section of the report.

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 Transactions Max Qry Len (s) Max Tx Concurcy Min/Max TR (mins) STO/ OOS uS/uR/uU/ eS/eR/eU
1 0.48 2,300 0 3 15/15 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 Time Num Undo Blocks Number of Transactions Max Qry Len (s) Max Tx Concy Tun Ret (mins) STO/ OOS uS/uR/uU/ eS/eR/eU
02-Apr 20:01 481 2,300 0 3 15 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 Name Get Requests Pct Get Miss Avg Slps /Miss Wait Time (s) NoWait Requests Pct NoWait Miss
ASM db client latch 322 0.00   0 0  
AWR Alerted Metric Element list 1,753 0.00   0 0  
Consistent RBA 2,246 0.00   0 0  
FOB s.o list latch 45 0.00   0 0  
In memory undo latch 9,423 0.00   0 2,310 0.00
JS queue state obj latch 2,940 0.00   0 0  
KMG MMAN ready and startup request latch 180 0.00   0 0  
KMG resize request state object freelist 7 0.00   0 0  
KTF sga latch 2 0.00   0 150 0.00
KWQP Prop Status 1 0.00   0 0  
MQL Tracking Latch 0     0 10 0.00
Memory Management Latch 96 0.00   0 180 0.00
OS process 33 0.00   0 0  
OS process allocation 187 0.00   0 0  
OS process: request allocation 23 0.00   0 0  
PL/SQL warning settings 10,096 0.00   0 0  
SGA IO buffer pool latch 15 0.00   0 15 0.00
SQL memory manager latch 325 0.00   0 161 0.00
SQL memory manager workarea list latch 33,216 0.00   0 0  
Shared B-Tree 22 0.00   0 0  
active checkpoint queue latch 865 0.00   0 0  
active service list 990 0.00   0 164 0.61
archive control 5 0.00   0 0  
buffer pool 19 0.00   0 0  
cache buffer handles 242 0.00   0 0  
cache buffers chains 111,191,175 1.77 0.00 13 42,741,445 0.01
cache buffers lru chain 7,403 11.67 0.05 0 88,521,524 2.94
cache table scan latch 463,254 0.06 0.00 0 463,254 0.06
channel handle pool latch 23 0.00   0 0  
channel operations parent latch 2,900 0.00   0 0  
checkpoint queue latch 12,784 0.00   0 1,403 0.00
client/application info 295 0.00   0 0  
compile environment latch 2,484 0.00   0 0  
dml lock allocation 7,062 0.00   0 0  
dummy allocation 39 0.00   0 0  
enqueue hash chains 72,409 0.47 0.00 0 0  
enqueues 62,482 1.60 0.00 0 0  
event group latch 12 0.00   0 0  
file cache latch 20 0.00   0 0  
global KZLD latch for mem in SGA 10 0.00   0 0  
hash table column usage latch 376 0.00   0 29,664 1.04
job workq parent latch 0     0 16 0.00
job_queue_processes parameter latch 16 0.00   0 0  
kks stats 170,836 0.42 0.01 0 0  
ksuosstats global area 37 0.00   0 0  
ktm global data 2 0.00   0 0  
kwqbsn:qsga 22 0.00   0 0  
lgwr LWN SCN 2,356 0.00   0 0  
library cache 849,627 1.31 0.01 0 122,031 0.54
library cache load lock 3,366 0.00   0 0  
library cache lock 222,573 0.40 0.00 0 0  
library cache lock allocation 674 0.00   0 0  
library cache pin 210,084 0.31 0.00 0 0  
library cache pin allocation 400 0.00   0 0  
list of block allocation 99 0.00   0 0  
messages 11,145 0.01 0.00 0 0  
mostly latch-free SCN 2,366 0.04 0.00 0 0  
multiblock read objects 2,015,384 0.27 0.01 0 0  
ncodef allocation latch 8 0.00   0 0  
object queue header heap 1,745 0.00   0 47 0.00
object queue header operation 6,534,010 0.00 0.05 0 468 0.00
object stats modification 685 0.00   0 0  
parallel query alloc buffer 64 0.00   0 0  
parameter table allocation management 27 0.00   0 0  
post/wait queue 5,073 0.00   0 2,039 0.00
process allocation 23 0.00   0 12 0.00
process group creation 23 0.00   0 0  
qmn task queue latch 72 0.00   0 0  
redo allocation 11,574 0.00   0 8,836 0.00
redo copy 0     0 8,836 0.76
redo writing 7,944 0.00   0 0  
resmgr group change latch 71 0.00   0 0  
resmgr:actses active list 41 0.00   0 0  
resmgr:actses change group 20 0.00   0 0  
resmgr:free threads list 39 0.00   0 0  
resmgr:schema config 2 0.00   0 0  
row cache objects 407,529 5.06 0.00 0 2,141 0.05
rules engine rule set statistics 100 0.00   0 0  
sequence cache 45 0.00   0 0  
session allocation 293,555 0.00   0 0  
session idle bit 288,999 0.12 0.00 0 0  
session state list latch 36 0.00   0 0  
session switching 8 0.00   0 0  
session timer 165 0.00   0 0  
shared pool 977,411 3.70 0.01 1 0  
shared pool simulator 1,290,805 0.01 0.00 0 0  
simulator hash latch 12,310,453 0.01 0.01 0 0  
simulator lru latch 6,592,701 5.83 0.01 10 206,414 0.00
sort extent pool 16 0.00   0 0  
state object free list 2 0.00   0 0  
statistics aggregation 112 0.00   0 0  
threshold alerts latch 34 0.00   0 0  
transaction allocation 29 0.00   0 0  
transaction branch allocation 8 0.00   0 0  
undo global data 12,637 0.00   0 0  
user lock 54 0.00   0 0  

Back to Latch Statistics
Back to Top

Latch Sleep Breakdown

  • ordered by misses desc
Latch Name Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3
cache buffers chains 111,191,175 1,967,362 3,042 1,965,138 0 0 0
simulator lru latch 6,592,701 384,605 2,056 382,938 0 0 0
shared pool 977,411 36,195 307 35,937 0 0 0
row cache objects 407,529 20,635 8 20,631 0 0 0
library cache 849,627 11,093 72 11,038 0 0 0
multiblock read objects 2,015,384 5,524 32 5,502 0 0 0
simulator hash latch 12,310,453 963 7 958 0 0 0
library cache lock 222,573 884 4 882 0 0 0
cache buffers lru chain 7,403 864 42 830 0 0 0
kks stats 170,836 716 5 713 0 0 0
library cache pin 210,084 654 1 653 0 0 0
object queue header operation 6,534,010 44 2 42 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 Name Where NoWait Misses Sleeps Waiter Sleeps
cache buffers chains kcbgtcr: kslbegin excl 0 3,341 4,425
cache buffers chains kcbrls: kslbegin 0 1,252 228
cache buffers chains kcbzib: multi-block read: nowait 0 103 0
cache buffers chains kcbzib: finish free bufs 0 89 24
cache buffers chains kcbzwb 0 50 186
cache buffers chains kcbzgb: scan from tail. nowait 0 18 0
cache buffers chains kcbbic1 0 1 0
cache buffers chains kcbbic2 0 1 0
cache buffers lru chain kcbzar: KSLNBEGIN 0 35 0
cache buffers lru chain kcbzgws 0 6 33
cache buffers lru chain kcbzgm 0 1 0
kks stats kks stats alloc/free 0 5 5
library cache kglic 0 11 1
library cache kgldti: 2child 0 7 4
library cache kglobpn: child: 0 5 12
library cache kglpin 0 5 2
library cache kglpndl: child: after processing 0 5 1
library cache kglukp: child 0 5 8
library cache kglhdgc: child: 0 2 0
library cache kglpndl: child: before processing 0 1 3
library cache lock kgllkdl: child: no lock handle 0 21 24
library cache lock kgllkal: child: multiinstance 0 2 4
library cache lock kgllkdl: child: cleanup 0 2 0
library cache pin kglpndl 0 1 0
multiblock read objects kcbzib: normal mbr free 0 24 6
multiblock read objects kcbzib: mbr get 0 8 26
object queue header operation kcbw_link_q 0 1 0
object queue header operation kcbw_unlink_q 0 1 0
row cache objects kqreqd: reget 0 4 0
row cache objects kqrpre: find obj 0 4 8
shared pool kghalo 0 141 106
shared pool kghupr1 0 90 109
shared pool kghfrunp: alloc: wait 0 43 1
shared pool kghalp 0 22 63
shared pool kghfrunp: clatch: nowait 0 7 0
shared pool kgh: sim resz update 0 4 0
shared pool kghfre 0 4 4
shared pool kghasp 0 3 24
shared pool kghfrunp: clatch: wait 0 3 9
simulator hash latch kcbsacc: lookup dba 0 5 3
simulator hash latch kcbsacc: insert dba 0 2 0
simulator lru latch kcbs_simulate: simulate set 0 2,055 2,045
simulator lru latch kcbs_simbufseq 0 1 11

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: 53,736,798
  • Captured Segments account for 99.8% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Logical Reads %Total
SCOTT USERS EMP1   TABLE 51,813,408 96.42
SCOTT USERS EMP3   TABLE 1,558,448 2.90
SCOTT USERS IND_EMP1_DEPTNO   INDEX 132,416 0.25
SCOTT USERS PK_EMP2   INDEX 29,264 0.05
SCOTT USERS PK_DEPT   INDEX 9,696 0.02

Back to Segment Statistics
Back to Top

Segments by Physical Reads

  • Total Physical Reads: 3,267,675
  • Captured Segments account for 99.6% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
SCOTT USERS EMP1   TABLE 3,254,786 99.61
SYS SYSTEM HISTGRM$   TABLE 189 0.01
SYS SYSAUX WRH$_SQL_PLAN_PK   INDEX 57 0.00
SYS SYSAUX WRH$_SQLTEXT   TABLE 46 0.00
SCOTT USERS EMP2   TABLE 23 0.00

Back to Segment Statistics
Back to Top

 

Segments by Row Lock Waits

No data exists for this section of the report.

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

No data exists for this section of the report.

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
Cache Get Requests Pct Miss Scan Reqs Pct Miss Mod Reqs Final Usage
dc_awr_control 12 0.00 0   2 1
dc_global_oids 769 3.64 0   0 19
dc_histogram_data 2,911 47.72 0   0 1,031
dc_histogram_defs 37,023 6.44 0   0 1,254
dc_object_grants 58 29.31 0   0 17
dc_object_ids 35,408 0.86 0   0 431
dc_objects 17,783 2.82 0   0 400
dc_profiles 18 0.00 0   0 1
dc_rollback_segments 54 0.00 0   0 12
dc_segments 33,277 2.00 0   2 401
dc_sequences 3 100.00 0   3 0
dc_tablespaces 3,844 0.10 0   0 5
dc_usernames 447 0.22 0   0 6
dc_users 4,855 0.25 0   0 21
outstanding_alerts 12 83.33 0   0 0


Back to Top

 

Library Cache Activity

  • "Pct Misses" should be very low
Namespace Get Requests Pct Miss Pin Requests Pct Miss Reloads Invali- dations
BODY 93 3.23 828 2.17 15 0
CLUSTER 20 0.00 39 5.13 2 0
INDEX 20 95.00 20 95.00 0 0
SQL AREA 27,249 96.22 121,913 65.11 205 87
TABLE/PROCEDURE 1,479 7.91 84,139 1.93 823 0
TRIGGER 6 0.00 36 5.56 2 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
  Category Alloc (MB) Used (MB) Avg Alloc (MB) Std Dev Alloc (MB) Max Alloc (MB) Hist Max Alloc (MB) Num Proc Num Alloc
B Other 73.38   3.06 6.52 26 26 24 24
  Freeable 9.69 0.00 0.69 0.39 1   14 14
  SQL 1.64 0.61 0.09 0.10 0 8 18 15
  PL/SQL 0.67 0.22 0.03 0.04 0 0 24 24
E Other 73.35   3.06 6.52 26 26 24 24
  Freeable 9.75 0.00 0.75 0.38 1   13 13
  SQL 1.67 0.62 0.09 0.10 0 8 18 15
  PL/SQL 0.67 0.22 0.03 0.04 0 0 24 24

Back to Memory Statistics
Back to Top

SGA Memory Summary

SGA regions Begin Size (Bytes) End Size (Bytes) (if different)
Database Buffers 96,468,992  
Fixed Size 1,261,248  
Redo Buffers 7,127,040  
Variable Size 163,578,176  

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
Pool Name Begin MB End 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 5.12 5.12 0.00
shared CCursor 6.57 9.50 44.69
shared Cursor Stats 1.83 1.83 0.00
shared KCB Table Scan Buffer 3.80 3.80 0.00
shared KGH: NO ACCESS 23.82 23.82 0.00
shared KGLS heap 1.75   -100.00
shared KSFD SGA I/O b 3.79 3.79 0.00
shared KTI-UNDO 3.60 3.60 0.00
shared PCursor 4.87 6.51 33.56
shared event statistics per sess 3.85 3.85 0.00
shared free memory 44.17 39.15 -11.36
shared kglsim hash table bkts 2.00 2.00 0.00
shared kglsim heap 3.17 3.17 0.00
shared kglsim object batch 4.49 4.49 0.00
shared library cache 6.68 6.82 2.20
shared private strands 3.49 3.49 0.00
shared row cache 3.58 3.58 0.00
shared sessions 2.30 2.30 0.00
shared sql area 5.53 8.51 54.04
shared sql area:PLSQL 12.63 10.46 -17.19
  buffer_cache 92.00 92.00 0.00
  fixed_sga 1.20 1.20 0.00
  log_buffer 6.80 6.80 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 Name Begin value End value (if different)
audit_file_dest /opt/app/oracle/admin/o102ee1/adump   
background_dump_dest /opt/app/oracle/admin/o102ee1/bdump   
compatible 10.2.0.3.0   
control_files /opt/app/oracle/oradata/o102ee1/control01.ctl, /opt/app/oracle/oradata/o102ee1/control02.ctl, /opt/app/oracle/oradata/o102ee1/control03.ctl   
db_block_size 8192   
db_domain      
db_name o102ee1   
db_recovery_file_dest /opt/app/oracle/flash_recovery_area   
db_recovery_file_dest_size 2147483648   
java_pool_size 0   
job_queue_processes 10   
nls_language JAPANESE   
nls_territory JAPAN   
open_cursors 1024   
pga_aggregate_target 16777216   
processes 150   
sessions 500   
sga_target 268435456   
undo_management AUTO   
undo_tablespace UNDOTBS1   
workarea_size_policy AUTO   


Back to Top

 

 

 

 

 

End of Report