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: 7480 02-Apr-07 20:28:31 27 11.9
End Snap: 7481 02-Apr-07 20:31:18 28 12.7
Elapsed:   2.79 (mins)    
DB Time:   2.60 (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: 23,001.54 1,881.48
Logical reads: 11,379.23 930.80
Block changes: 101.53 8.30
Physical reads: 11.93 0.98
Physical writes: 1.17 0.10
User calls: 853.64 69.83
Parses: 341.70 27.95
Hard parses: 159.62 13.06
Sorts: 27.53 2.25
Logons: 0.08 0.01
Executes: 386.86 31.64
Transactions: 12.23  

 

% Blocks changed per Read: 0.89 Recursive Call %: 62.19
Rollback per transaction %: 0.10 Rows per Sort: 12.16

Instance Efficiency Percentages (Target 100%)

 

Buffer Nowait %: 100.00 Redo NoWait %: 99.98
Buffer Hit %: 99.90 In-memory Sort %: 100.00
Library Hit %: 60.28 Soft Parse %: 53.29
Execute to Parse %: 11.68 Latch Hit %: 98.98
Parse CPU to Parse Elapsd %: 94.59 % Non-Parse CPU: 55.31

Shared Pool Statistics

 

Begin End
Memory Usage %: 65.96 69.79
% SQL with executions>1: 72.51 23.09
% Memory for SQL w/exec>1: 54.90 19.43

Top 5 Timed Events

 

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time   150   95.9  
log file parallel write 2,064 10 5 6.4 System I/O
log file sync 2,024 10 5 6.2 Commit
control file parallel write 89 3 38 2.2 System I/O
latch: shared pool 518 1 3 .9 Concurrency

Main Report


Back to Top

Wait Events Statistics

Back to Top

Time Model Statistics

  • Total time in database user-calls (DB Time): 156.3s
  • 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
DB CPU 149.82 95.86
parse time elapsed 88.15 56.40
hard parse elapsed time 72.83 46.60
sql execute elapsed time 69.20 44.28
PL/SQL compilation elapsed time 16.84 10.77
PL/SQL execution elapsed time 0.77 0.49
connection management call elapsed time 0.48 0.31
hard parse (sharing criteria) elapsed time 0.45 0.29
repeated bind elapsed time 0.09 0.06
sequence load elapsed time 0.02 0.02
hard parse (bind mismatch) elapsed time 0.02 0.01
DB time 156.29  
background elapsed time 14.73  
background cpu time 1.12  

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
System I/O 3,157 0.00 13 4 1.55
Commit 2,024 0.00 10 5 0.99
Concurrency 609 0.16 2 4 0.30
Network 90,565 0.00 0 0 44.33
Application 16 0.00 0 18 0.01
User I/O 1,547 0.00 0 0 0.76
Configuration 2 0.00 0 101 0.00
Other 7 0.00 0 5 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)
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
log file parallel write 2,064 0.00 10 5 1.01
log file sync 2,024 0.00 10 5 0.99
control file parallel write 89 0.00 3 38 0.04
latch: shared pool 518 0.00 1 3 0.25
latch: library cache 86 0.00 1 7 0.04
SQL*Net message to client 90,564 0.00 0 0 44.33
SQL*Net break/reset to client 16 0.00 0 18 0.01
db file sequential read 1,499 0.00 0 0 0.73
log file switch completion 2 0.00 0 101 0.00
os thread startup 1 0.00 0 61 0.00
latch free 6 0.00 0 5 0.00
control file sequential read 1,000 0.00 0 0 0.49
log file single write 2 0.00 0 6 0.00
cursor: pin S wait on X 1 100.00 0 10 0.00
db file scattered read 44 0.00 0 0 0.02
latch: enqueue hash chains 1 0.00 0 9 0.00
latch: row cache objects 3 0.00 0 2 0.00
SQL*Net more data to client 1 0.00 0 0 0.00
direct path write 4 0.00 0 0 0.00
log file sequential read 2 0.00 0 0 0.00
SQL*Net message from client 90,563 0.00 1,293 14 44.33
Streams AQ: waiting for messages in the queue 34 100.00 166 4884 0.02
Streams AQ: qmn slave idle wait 6 0.00 164 27346 0.00
Streams AQ: qmn coordinator idle wait 12 50.00 164 13673 0.01
jobq slave wait 56 96.43 163 2918 0.03
wait for unread message on broadcast channel 167 100.00 163 978 0.08

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
log file parallel write 2,064 0.00 10 5 1.01
control file parallel write 89 0.00 3 38 0.04
os thread startup 1 0.00 0 61 0.00
log file single write 2 0.00 0 6 0.00
control file sequential read 49 0.00 0 0 0.02
db file sequential read 7 0.00 0 0 0.00
latch: shared pool 1 0.00 0 0 0.00
log file sequential read 2 0.00 0 0 0.00
rdbms ipc message 2,606 20.26 1,292 496 1.28
smon timer 1 100.00 293 292973 0.00
pmon timer 58 100.00 164 2830 0.03
Streams AQ: qmn slave idle wait 6 0.00 164 27346 0.00
Streams AQ: qmn coordinator idle wait 12 50.00 164 13673 0.01

Back to Wait Events Statistics
Back to Top

Operating System Statistics

Statistic Total
BUSY_TIME 39,074
IDLE_TIME 27,832
IOWAIT_TIME 1,227
NICE_TIME 204
SYS_TIME 13,101
USER_TIME 25,724
LOAD 2
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 36,936
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 153.70 147.40 1,858 1,886,166
o102ee1 2.50 2.40 206 13,674
SYS$BACKGROUND 0.00 0.00 7 1,503

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 1334 21 597 209 0 0 90241 44
o102ee1 206 7 10 1 0 0 310 0
SYS$BACKGROUND 7 0 2 6 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
25 25 1,000 0.03 16.22 8zt0p6avg76yz SQL*Plus SELECT COUNT(ename) FROM emp3 ...
3 3 10,000 0.00 2.21 90p005ah4wm47 SQL*Plus select distinct emp1.deptno fr...
1 1 10,000 0.00 0.92 5u3yjw57ppbsx SQL*Plus SELECT ENAME FROM EMP2 WHERE E...
1 1 9 0.15 0.84 b058ymxj1rvkg Admin Connection SELECT sql_id, sql_text from v...
1 1 3 0.36 0.70 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
1 1 22 0.04 0.56 257rmrxgvaj4z Realtime Connection select begin_time, wait_class...
0 0 2,202 0.00 0.24 db78fxqxwxt7r   select /*+ rule */ bucket, en...
0 0 265 0.00 0.22 7ng34ruy5awxq   select i.obj#, i.ts#, i.file#,...
0 0 1 0.34 0.22 bunssq950snhf   insert into wrh$_sga_target_ad...
0 0 11 0.03 0.21 b8b5jdj7khuaw Realtime Connection SELECT event#, sql_id, sql_p...

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
25 25 1,000 0.03 16.22 8zt0p6avg76yz SQL*Plus SELECT COUNT(ename) FROM emp3 ...
3 3 10,000 0.00 2.21 90p005ah4wm47 SQL*Plus select distinct emp1.deptno fr...
1 1 10,000 0.00 0.92 5u3yjw57ppbsx SQL*Plus SELECT ENAME FROM EMP2 WHERE E...
1 1 9 0.13 0.84 b058ymxj1rvkg Admin Connection SELECT sql_id, sql_text from v...
1 1 3 0.36 0.70 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
1 1 22 0.04 0.56 257rmrxgvaj4z Realtime Connection select begin_time, wait_class...
0 0 2,202 0.00 0.24 db78fxqxwxt7r   select /*+ rule */ bucket, en...
0 0 265 0.00 0.22 7ng34ruy5awxq   select i.obj#, i.ts#, i.file#,...
0 0 1 0.34 0.22 bunssq950snhf   insert into wrh$_sga_target_ad...
0 0 11 0.03 0.21 b8b5jdj7khuaw Realtime Connection SELECT event#, sql_id, sql_p...

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,901,617
  • Captured SQL account for 96.0% of Total
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
1,557,049 1,000 1,557.05 81.88 25.34 25.35 8zt0p6avg76yz SQL*Plus SELECT COUNT(ename) FROM emp3 ...
140,047 10,000 14.00 7.36 3.42 3.46 90p005ah4wm47 SQL*Plus select distinct emp1.deptno fr...
40,049 10,000 4.00 2.11 1.44 1.44 5u3yjw57ppbsx SQL*Plus SELECT ENAME FROM EMP2 WHERE E...
8,979 3,257 2.76 0.47 0.25 0.25 96g93hntrzjtr   select /*+ rule */ bucket_cnt,...
6,865 2,202 3.12 0.36 0.38 0.38 db78fxqxwxt7r   select /*+ rule */ bucket, en...
6,593 3 2,197.67 0.35 1.08 1.09 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
5,180 1,000 5.18 0.27 0.25 0.25 d9qr16p9z7xy2 SQL*Plus update emp4 set ename = 'aaa' ...
5,071 1,000 5.07 0.27 0.27 0.27 6kpzvzuv00t58 SQL*Plus update EMP5 set ename = 'aaa' ...
3,140 656 4.79 0.17 0.06 0.06 53saa2zkr6wc3   select intcol#, nvl(pos#, 0), ...
3,000 1,000 3.00 0.16 0.23 0.23 5uq8u6hqrr21g SQL*Plus SELECT COUNT(ENAME) FROM EMP3 ...

Back to SQL Statistics
Back to Top

SQL ordered by Reads

  • Total Disk Reads: 1,994
  • Captured SQL account for 62.4% of Total
Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
598 1,000 0.60 29.99 25.34 25.35 8zt0p6avg76yz SQL*Plus SELECT COUNT(ename) FROM emp3 ...
241 2,202 0.11 12.09 0.38 0.38 db78fxqxwxt7r   select /*+ rule */ bucket, en...
77 3 25.67 3.86 1.08 1.09 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
56 107 0.52 2.81 0.06 0.06 cvn54b7yz0s8u   select /*+ index(idl_ub1$ i_id...
21 117 0.18 1.05 0.06 0.06 39m4sx9k63ba2   select /*+ index(idl_ub2$ i_id...
20 249 0.08 1.00 0.09 0.10 cqgv56fmuj63x   select owner#, name, namespace...
19 249 0.08 0.95 0.06 0.06 8swypbbr0m372   select order#, columns, types ...
16 1 16.00 0.80 0.10 0.10 4qju99hqmn81x   INSERT INTO WRH$_ACTIVE_SESSIO...
15 10,000 0.00 0.75 1.44 1.44 5u3yjw57ppbsx SQL*Plus SELECT ENAME FROM EMP2 WHERE E...
14 117 0.12 0.70 0.05 0.05 ga9j9xk5cy9s0   select /*+ index(idl_sb4$ i_id...

Back to SQL Statistics
Back to Top

SQL ordered by Executions

  • Total Executions: 64,650
  • Captured SQL account for 59.9% 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...
3,257 2,305 0.71 0.00 0.00 96g93hntrzjtr   select /*+ rule */ bucket_cnt,...
2,202 34,432 15.64 0.00 0.00 db78fxqxwxt7r   select /*+ rule */ bucket, en...
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.03 0.03 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' ...
734 734 1.00 0.00 0.00 2ym6hhaq30r73   select type#, blocks, extents,...
656 899 1.37 0.00 0.00 53saa2zkr6wc3   select intcol#, nvl(pos#, 0), ...

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

  • Total Parse Calls: 57,102
  • Captured SQL account for 51.8% of Total
Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
10,000 10,000 17.51 5u3yjw57ppbsx SQL*Plus SELECT ENAME FROM EMP2 WHERE E...
10,000 10,000 17.51 90p005ah4wm47 SQL*Plus select distinct emp1.deptno fr...
1,000 1,000 1.75 5uq8u6hqrr21g SQL*Plus SELECT COUNT(ENAME) FROM EMP3 ...
1,000 1,000 1.75 6kpzvzuv00t58 SQL*Plus update EMP5 set ename = 'aaa' ...
1,000 1,000 1.75 8zt0p6avg76yz SQL*Plus SELECT COUNT(ename) FROM emp3 ...
1,000 1,000 1.75 d9qr16p9z7xy2 SQL*Plus update emp4 set ename = 'aaa' ...
734 734 1.29 2ym6hhaq30r73   select type#, blocks, extents,...
255 255 0.45 asvzxj61dc5vs   select timestamp, flags from ...
249 249 0.44 8swypbbr0m372   select order#, columns, types ...
249 249 0.44 cqgv56fmuj63x   select owner#, name, namespace...

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
101 10 18naypzfmabd6 OEM.BoundedPool INSERT INTO MGMT_SYSTEM_PERFOR...

Back to SQL Statistics
Back to Top

 

Complete List of SQL Text

 

SQL Id SQL Text
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))
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
39m4sx9k63ba2 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#, length, piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#
4qju99hqmn81x INSERT INTO WRH$_ACTIVE_SESSION_HISTORY ( snap_id, dbid, instance_number, sample_id, sample_time, session_id, session_serial#, user_id, sql_id, sql_child_number, sql_plan_hash_value, force_matching_signature, service_hash, session_type, sql_opcode, plsql_entry_object_id, plsql_entry_subprogram_id, plsql_object_id, plsql_subprogram_id, blocking_session, blocking_session_serial#, qc_session_id, qc_instance_id, xid, current_obj#, current_file#, current_block#, event_id, seq#, p1, p2, p3, wait_time, time_waited, program, module, action, client_id ) (SELECT :snap_id, :dbid, :instance_number, a.sample_id, a.sample_time, a.session_id, a.session_serial#, a.user_id, a.sql_id, a.sql_child_number, a.sql_plan_hash_value, a.force_matching_signature, a.service_hash, a.session_type, a.sql_opcode, a.plsql_entry_object_id, a.plsql_entry_subprogram_id, a.plsql_object_id, a.plsql_subprogram_id, a.blocking_session, a.blocking_session_serial#, a.qc_session_id, a.qc_instance_id, a.xid, a.current_obj#, a.current_file#, a.current_block#, a.event_id, a.seq#, a.p1, a.p2, a.p3, a.wait_time, a.time_waited, substrb(a.program, 1, 64), a.module, a.action, a.client_id FROM x$ash a, (SELECT h.sample_addr, h.sample_id FROM x$kewash h WHERE ( (h.sample_id >= :begin_flushing) and (h.sample_id < :latest_sample_id) ) and (MOD(h.sample_id, :disk_filter_ratio) = 0) ) shdr WHERE shdr.sample_addr = a.sample_addr and shdr.sample_id = a.sample_id)
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
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
7ng34ruy5awxq select i.obj#, i.ts#, i.file#, i.block#, i.intcols, i.type#, i.flags, i.property, i.pctfree$, i.initrans, i.maxtrans, i.blevel, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey, i.clufac, i.cols, i.analyzetime, i.samplesize, i.dataobj#, nvl(i.degree, 1), nvl(i.instances, 1), i.rowcnt, mod(i.pctthres$, 256), i.indmethod#, i.trunccnt, nvl(c.unicols, 0), nvl(c.deferrable#+c.valid#, 0), nvl(i.spare1, i.intcols), i.spare4, i.spare2, i.spare6, decode(i.pctthres$, null, null, mod(trunc(i.pctthres$/256), 256)), ist.cachedblk, ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols, min(to_number(bitand(defer, 1))) deferrable#, min(to_number(bitand(defer, 4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
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
asvzxj61dc5vs select timestamp, flags from fixed_obj$ where obj#=:1
b058ymxj1rvkg SELECT sql_id, sql_text from v$sql WHERE sql_id in (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
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
bunssq950snhf insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbid, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS from v$sga_target_advice
cqgv56fmuj63x select owner#, name, namespace, remoteowner, linkname, p_timestamp, p_obj#, nvl(property, 0), subname, d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
cvn54b7yz0s8u select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#, length, piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#
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
ga9j9xk5cy9s0 select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#, length, piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#

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 14,816 88.66 7.25
CPU used when call started 14,641 87.61 7.17
CR blocks created 55 0.33 0.03
Cached Commit SCN referenced 1,487,000 8,898.17 727.85
Commit SCN cached 1 0.01 0.00
DB time 69,674 416.93 34.10
DBWR checkpoint buffers written 121 0.72 0.06
DBWR checkpoints 1 0.01 0.00
DBWR transaction table writes 4 0.02 0.00
DBWR undo block writes 26 0.16 0.01
IMU CR rollbacks 0 0.00 0.00
IMU Flushes 3 0.02 0.00
IMU Redo allocation size 0 0.00 0.00
IMU commits 110 0.66 0.05
IMU contention 1 0.01 0.00
IMU ktichg flush 0 0.00 0.00
IMU pool not allocated 2,007 12.01 0.98
IMU recursive-transaction flush 0 0.00 0.00
IMU undo allocation size 98,716 590.71 48.32
IMU- failed to get a private strand 2,007 12.01 0.98
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 90,550 541.85 44.32
active txn count during cleanout 104 0.62 0.05
application wait time 29 0.17 0.01
background checkpoints completed 0 0.00 0.00
background checkpoints started 1 0.01 0.00
background timeouts 532 3.18 0.26
branch node splits 0 0.00 0.00
buffer is not pinned count 122,779 734.71 60.10
buffer is pinned count 10,488 62.76 5.13
bytes received via SQL*Net from client 12,455,183 74,531.50 6,096.52
bytes sent via SQL*Net to client 18,959,665 113,454.16 9,280.31
calls to get snapshot scn: kcmgss 65,111 389.62 31.87
calls to kcmgas 2,382 14.25 1.17
calls to kcmgcs 70 0.42 0.03
change write time 23 0.14 0.01
cleanout - number of ktugct calls 126 0.75 0.06
cleanouts and rollbacks - consistent read gets 0 0.00 0.00
cleanouts only - consistent read gets 4 0.02 0.00
cluster key scan block gets 12,723 76.13 6.23
cluster key scans 7,306 43.72 3.58
commit batch performed 0 0.00 0.00
commit batch requested 0 0.00 0.00
commit batch/immediate performed 2 0.01 0.00
commit batch/immediate requested 2 0.01 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 13 0.08 0.01
commit cleanout failures: cannot pin 0 0.00 0.00
commit cleanouts 3,068 18.36 1.50
commit cleanouts successfully completed 3,055 18.28 1.50
commit immediate performed 2 0.01 0.00
commit immediate requested 2 0.01 0.00
commit txn count during cleanout 64 0.38 0.03
concurrency wait time 215 1.29 0.11
consistent changes 55 0.33 0.03
consistent gets 1,887,280 11,293.44 923.78
consistent gets - examination 127,889 765.28 62.60
consistent gets direct 0 0.00 0.00
consistent gets from cache 1,887,280 11,293.44 923.78
cursor authentications 151 0.90 0.07
data blocks consistent reads - undo records applied 55 0.33 0.03
db block changes 16,967 101.53 8.30
db block gets 14,337 85.79 7.02
db block gets direct 4 0.02 0.00
db block gets from cache 14,333 85.77 7.02
deferred (CURRENT) block cleanout applications 2,767 16.56 1.35
dirty buffers inspected 36 0.22 0.02
enqueue conversions 84 0.50 0.04
enqueue releases 32,573 194.92 15.94
enqueue requests 32,573 194.92 15.94
enqueue timeouts 0 0.00 0.00
enqueue waits 0 0.00 0.00
execute count 64,650 386.86 31.64
failed probes on index block reclamation 0 0.00 0.00
frame signature mismatch 0 0.00 0.00
free buffer inspected 1,955 11.70 0.96
free buffer requested 2,345 14.03 1.15
heap block compress 5 0.03 0.00
hot buffers moved to head of LRU 719 4.30 0.35
immediate (CR) block cleanout applications 4 0.02 0.00
immediate (CURRENT) block cleanout applications 131 0.78 0.06
index crx upgrade (positioned) 1,288 7.71 0.63
index fast full scans (full) 4 0.02 0.00
index fetch by key 38,614 231.07 18.90
index scans kdiixs1 36,094 215.99 17.67
leaf node 90-10 splits 11 0.07 0.01
leaf node splits 45 0.27 0.02
lob reads 0 0.00 0.00
lob writes 18 0.11 0.01
lob writes unaligned 18 0.11 0.01
logons cumulative 14 0.08 0.01
messages received 2,154 12.89 1.05
messages sent 2,154 12.89 1.05
no buffer to keep pinned count 0 0.00 0.00
no work - consistent read gets 1,752,471 10,486.74 857.79
opened cursors cumulative 65,334 390.96 31.98
parse count (failures) 0 0.00 0.00
parse count (hard) 26,674 159.62 13.06
parse count (total) 57,102 341.70 27.95
parse time cpu 6,695 40.06 3.28
parse time elapsed 7,078 42.35 3.46
physical read IO requests 1,461 8.74 0.72
physical read bytes 16,334,848 97,747.32 7,995.52
physical read total IO requests 2,471 14.79 1.21
physical read total bytes 32,785,408 196,187.06 16,047.68
physical read total multi block requests 44 0.26 0.02
physical reads 1,994 11.93 0.98
physical reads cache 1,994 11.93 0.98
physical reads cache prefetch 533 3.19 0.26
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 104 0.62 0.05
physical write bytes 1,605,632 9,608.06 785.92
physical write total IO requests 2,440 14.60 1.19
physical write total bytes 10,788,352 64,557.23 5,280.64
physical write total multi block requests 2,087 12.49 1.02
physical writes 196 1.17 0.10
physical writes direct 4 0.02 0.00
physical writes direct (lob) 4 0.02 0.00
physical writes direct temporary tablespace 0 0.00 0.00
physical writes from cache 192 1.15 0.09
physical writes non checkpoint 97 0.58 0.05
pinned buffers inspected 0 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 453 2.71 0.22
recursive calls 234,632 1,404.03 114.85
recursive cpu usage 7,942 47.52 3.89
redo blocks written 9,389 56.18 4.60
redo buffer allocation retries 2 0.01 0.00
redo entries 8,575 51.31 4.20
redo log space requests 2 0.01 0.00
redo log space wait time 20 0.12 0.01
redo ordering marks 204 1.22 0.10
redo size 3,843,856 23,001.54 1,881.48
redo subscn max counts 370 2.21 0.18
redo synch time 1,006 6.02 0.49
redo synch writes 2,180 13.05 1.07
redo wastage 882,104 5,278.49 431.77
redo write time 1,022 6.12 0.50
redo writer latching time 0 0.00 0.00
redo writes 2,064 12.35 1.01
rollback changes - undo records applied 2 0.01 0.00
rollbacks only - consistent read gets 55 0.33 0.03
rows fetched via callback 16,500 98.74 8.08
session connect time 0 0.00 0.00
session cursor cache hits 36,685 219.52 17.96
session logical reads 1,901,617 11,379.23 930.80
session pga memory 7,518,544 44,990.78 3,680.15
session pga memory max 12,237,136 73,226.71 5,989.79
session uga memory 12,886,771,104 77,114,115.02 6,307,768.53
session uga memory max 14,514,732 86,855.79 7,104.62
shared hash latch upgrades - no wait 1,830 10.95 0.90
sorts (disk) 0 0.00 0.00
sorts (memory) 4,601 27.53 2.25
sorts (rows) 55,967 334.91 27.39
sql area evicted 391 2.34 0.19
sql area purged 0 0.00 0.00
summed dirty queue length 37 0.22 0.02
switch current to new buffer 10 0.06 0.00
table fetch by rowid 45,352 271.39 22.20
table fetch continued row 373 2.23 0.18
table scan blocks gotten 1,552,500 9,290.12 759.91
table scan rows gotten 262,154,185 1,568,724.07 128,318.25
table scans (long tables) 1 0.01 0.00
table scans (short tables) 1,148 6.87 0.56
total number of times SMON posted 0 0.00 0.00
transaction rollbacks 2 0.01 0.00
undo change vector size 1,223,524 7,321.54 598.89
user I/O wait time 30 0.18 0.01
user calls 142,654 853.64 69.83
user commits 2,041 12.21 1.00
user rollbacks 2 0.01 0.00
workarea executions - onepass 0 0.00 0.00
workarea executions - optimal 2,574 15.40 1.26
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 324,175 324,442
opened cursors current 320 356
workarea memory allocated 676 676
logons current 27 28

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) 1 21.54

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)
SYSAUX 939 6 0.27 1.00 55 0 0 0.00
SYSTEM 505 3 0.02 1.00 26 0 0 0.00
USERS 101 1 0.10 6.28 13 0 0 0.00
UNDOTBS1 0 0 0.00 0.00 10 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 939 6 0.27 1.00 55 0 0 0.00
SYSTEM /opt/app/oracle/oradata/o102ee1/system01.dbf 505 3 0.02 1.00 26 0 0 0.00
UNDOTBS1 /opt/app/oracle/oradata/o102ee1/undotbs01.dbf 0 0     10 0 0 0.00
USERS /opt/app/oracle/oradata/o102ee1/users01.dbf 101 1 0.10 6.28 13 0 0 0.00

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,463 100 1,901,141 2,038 192 0 0 0


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 9 330 10870 36671 184320 36671  
E 0 9 1355 9620 40152 184320 40152  

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,914,016
D 16 0.17 1,996 1.00 34,808,473
D 24 0.26 2,994 1.00 34,795,620
D 32 0.35 3,992 1.00 34,782,399
D 40 0.43 4,990 1.00 34,761,105
D 48 0.52 5,988 1.00 34,750,759
D 56 0.61 6,986 1.00 34,746,512
D 64 0.70 7,984 1.00 34,743,013
D 72 0.78 8,982 1.00 34,740,738
D 80 0.87 9,980 1.00 34,738,926
D 88 0.96 10,978 1.00 34,737,304
D 92 1.00 11,477 1.00 34,736,613
D 96 1.04 11,976 1.00 34,735,940
D 104 1.13 12,974 1.00 34,734,616
D 112 1.22 13,972 1.00 34,733,593
D 120 1.30 14,970 1.00 34,732,599
D 128 1.39 15,968 1.00 34,731,587
D 136 1.48 16,966 1.00 34,730,769
D 144 1.57 17,964 1.00 34,729,797
D 152 1.65 18,962 1.00 34,728,513
D 160 1.74 19,960 0.90 31,383,190

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 35 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 89.44 0.66 0.74 100.00 0.00 3,276
E 16 4 92.85 0.66 0.71 100.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,527 2,527 0 0
64K 128K 4 4 0 0
512K 1024K 40 40 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,725.09 719.79 95.00 144
16 1.00 12,725.09 28.91 100.00 144
19 1.20 12,725.09 28.91 100.00 144
22 1.40 12,725.09 28.91 100.00 144
26 1.60 12,725.09 28.91 100.00 143
29 1.80 12,725.09 28.91 100.00 138
32 2.00 12,725.09 27.47 100.00 114
48 3.00 12,725.09 1.61 100.00 29
64 4.00 12,725.09 0.00 100.00 4
96 6.00 12,725.09 0.00 100.00 0
128 8.00 12,725.09 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 2,816 183,536 0.88 26,821 9.14 10,497,299
100 0.68 32 4,459 190,169 0.92 20,188 6.88 10,511,046
116 0.78 47 6,031 196,511 0.95 13,846 4.72 10,523,578
132 0.89 62 7,640 202,319 0.98 8,038 2.74 10,534,651
148 1.00 77 9,242 207,423 1.00 2,934 1.00 10,544,803
164 1.11 92 10,849 211,726 1.02 1 0.00 10,553,865
180 1.22 107 12,506 215,206 1.04 1 0.00 10,561,590
196 1.32 122 14,146 217,907 1.05 1 0.00 10,568,458
212 1.43 137 15,775 219,925 1.06 1 0.00 10,575,467
228 1.54 152 17,925 221,376 1.07 1 0.00 10,583,027
244 1.65 167 19,934 222,381 1.07 1 0.00 10,590,940
260 1.76 182 21,989 223,053 1.08 1 0.00 10,599,067
276 1.86 197 24,071 223,489 1.08 1 0.00 10,608,781
292 1.97 212 26,096 223,772 1.08 1 0.00 10,621,493
308 2.08 227 28,190 223,957 1.08 1 0.00 10,636,684

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,848 35,910,638
192 0.75 32,671 35,910,638
256 1.00 32,437 34,736,543
320 1.25 32,434 34,726,122
384 1.50 31,769 31,380,993
448 1.75 31,769 31,380,993
512 2.00 31,769 31,380,993

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

No data exists for this section of the report.

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

No data exists for this section of the report.

Back to Undo Statistics
Back to Top

 

Undo Segment Stats

No data exists for this section of the report.

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 110 0.00   0 0  
AWR Alerted Metric Element list 652 0.00   0 0  
Consistent RBA 2,065 0.00   0 0  
FOB s.o list latch 49 0.00   0 0  
In memory undo latch 453 0.00   0 2,119 0.00
JS queue state obj latch 990 0.00   0 0  
JS slv state obj latch 3 0.00   0 0  
KMG MMAN ready and startup request latch 55 0.00   0 0  
KTF sga latch 1 0.00   0 51 0.00
KWQP Prop Status 1 0.00   0 0  
MQL Tracking Latch 0     0 3 0.00
Memory Management Latch 0     0 55 0.00
OS process 36 0.00   0 0  
OS process allocation 80 0.00   0 0  
OS process: request allocation 21 0.00   0 0  
PL/SQL warning settings 10,072 0.00   0 0  
SQL memory manager latch 357 0.00   0 55 0.00
SQL memory manager workarea list latch 28,322 0.00   0 0  
Shared B-Tree 9 0.00   0 0  
active checkpoint queue latch 133 0.00   0 0  
active service list 377 0.00   0 58 0.00
archive control 2 0.00   0 0  
begin backup scn array 4 0.00   0 0  
cache buffer handles 94 0.00   0 0  
cache buffers chains 3,709,197 0.03 0.00 0 5,010 0.00
cache buffers lru chain 476 0.00   0 6,102 0.00
cache table scan latch 44 0.00   0 44 0.00
channel handle pool latch 21 0.00   0 0  
channel operations parent latch 1,015 0.00   0 0  
checkpoint queue latch 2,861 0.00   0 1,222 0.00
client/application info 167 0.00   0 0  
compile environment latch 2,282 0.00   0 0  
dml lock allocation 5,012 0.00   0 0  
dummy allocation 27 0.00   0 0  
enqueue hash chains 65,230 0.59 0.00 0 0  
enqueues 58,181 1.79 0.00 0 0  
event group latch 11 0.00   0 0  
file cache latch 8 0.00   0 0  
global KZLD latch for mem in SGA 9 0.00   0 0  
hash table column usage latch 0     0 46,830 1.02
hash table modification latch 5 0.00   0 0  
job workq parent latch 0     0 6 0.00
job_queue_processes parameter latch 6 0.00   0 0  
kks stats 174,078 0.35 0.01 0 0  
ksuosstats global area 16 0.00   0 0  
ktm global data 1 0.00   0 0  
kwqbsn:qsga 9 0.00   0 0  
lgwr LWN SCN 2,118 0.00   0 0  
library cache 749,083 1.58 0.01 1 116,181 1.02
library cache load lock 3,990 0.00   0 0  
library cache lock 218,571 0.47 0.00 0 0  
library cache lock allocation 598 0.00   0 0  
library cache pin 206,204 0.35 0.00 0 0  
library cache pin allocation 324 0.00   0 0  
list of block allocation 94 0.00   0 0  
loader state object freelist 8 0.00   0 0  
messages 7,481 0.00   0 0  
mostly latch-free SCN 2,134 0.14 0.00 0 0  
multiblock read objects 138 0.00   0 0  
ncodef allocation latch 2 0.00   0 0  
object queue header heap 322 0.00   0 14 0.00
object queue header operation 6,633 0.00   0 0  
object stats modification 693 0.00   0 0  
parallel query alloc buffer 20 0.00   0 0  
parameter list 10 0.00   0 0  
parameter table allocation management 13 0.00   0 0  
post/wait queue 5,062 0.00   0 2,026 0.00
process allocation 21 0.00   0 11 0.00
process group creation 21 0.00   0 0  
qmn task queue latch 24 0.00   0 0  
redo allocation 8,751 0.00   0 8,559 0.00
redo copy 0     0 8,559 0.00
redo writing 6,450 0.00   0 0  
resmgr group change latch 38 0.00   0 0  
resmgr:actses active list 29 0.00   0 0  
resmgr:actses change group 13 0.00   0 0  
resmgr:free threads list 27 0.00   0 0  
resmgr:schema config 2 0.00   0 0  
row cache objects 699,017 5.14 0.00 0 4,465 0.20
rules engine rule set statistics 100 0.00   0 0  
sequence cache 49 0.00   0 0  
session allocation 364,363 0.09 0.00 0 0  
session idle bit 287,574 0.19 0.00 0 0  
session state list latch 27 0.00   0 0  
session switching 2 0.00   0 0  
session timer 58 0.00   0 0  
shared pool 988,664 3.98 0.01 1 0  
shared pool simulator 1,267,371 0.01 0.00 0 0  
simulator hash latch 114,734 0.00   0 0  
simulator lru latch 114,371 0.00   0 159 0.00
sort extent pool 6 0.00   0 0  
state object free list 2 0.00   0 0  
statistics aggregation 112 0.00   0 0  
threshold alerts latch 3 0.00   0 0  
transaction allocation 24 0.00   0 0  
transaction branch allocation 2 0.00   0 0  
undo global data 6,919 0.00   0 0  
user lock 40 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
shared pool 988,664 39,391 518 38,896 0 0 0
row cache objects 699,017 35,945 3 35,942 0 0 0
library cache 749,083 11,817 86 11,734 0 0 0
enqueues 58,181 1,044 1 1,043 0 0 0
kks stats 174,078 606 5 602 0 0 0
enqueue hash chains 65,230 384 1 383 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
enqueue hash chains ksqrcl 0 1 1
enqueues ksqdel 0 1 1
kks stats kks stats alloc/free 0 5 5
library cache kglpndl: child: after processing 0 12 0
library cache kglukp: child 0 9 5
library cache kglobpn: child: 0 8 16
library cache kglpin 0 6 3
library cache kgldti: 2child 0 5 4
library cache kglic 0 2 2
library cache kglpndl: child: before processing 0 2 3
library cache kglhdgn: child: 0 1 16
library cache lock kgllkdl: child: no lock handle 0 28 24
row cache objects kqreqd: reget 0 2 0
row cache objects kqrpre: find obj 0 1 3
shared pool kghalo 0 310 214
shared pool kghupr1 0 131 140
shared pool kghfrunp: alloc: wait 0 43 0
shared pool kghalp 0 18 105
shared pool kghfrunp: clatch: nowait 0 10 0
shared pool kghasp 0 9 39
shared pool kghfre 0 7 20
shared pool kghfrunp: clatch: wait 0 3 12

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,901,617
  • Captured Segments account for 98.1% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Logical Reads %Total
SCOTT USERS EMP3   TABLE 1,557,840 81.92
SCOTT USERS IND_EMP1_DEPTNO   INDEX 136,128 7.16
SCOTT USERS IND_EMP1_EMP1NO   INDEX 49,504 2.60
SCOTT USERS PK_EMP2   INDEX 25,712 1.35
SCOTT USERS EMP1   TABLE 14,416 0.76

Back to Segment Statistics
Back to Top

Segments by Physical Reads

  • Total Physical Reads: 1,994
  • Captured Segments account for 82.6% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
SCOTT USERS EMP3   TABLE 595 29.84
SYS SYSTEM HISTGRM$   TABLE 300 15.05
SYS SYSAUX WRH$_SQL_PLAN   TABLE 251 12.59
SYS SYSAUX WRH$_SQL_PLAN_PK   INDEX 128 6.42
SYS SYSAUX WRH$_SQLTEXT   TABLE 94 4.71

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 7 57.14 0   2 1
dc_global_oids 355 11.83 0   0 15
dc_histogram_data 4,387 46.96 0   0 649
dc_histogram_defs 37,804 8.35 0   0 949
dc_object_grants 89 59.55 0   0 14
dc_object_ids 99,688 0.44 0   0 355
dc_objects 18,440 3.56 0   0 322
dc_profiles 12 0.00 0   0 1
dc_rollback_segments 21 0.00 0   0 12
dc_segments 49,448 1.41 0   0 327
dc_sequences 5 100.00 0   5 0
dc_tablespaces 3,128 0.13 0   0 4
dc_usernames 737 0.95 0   0 6
dc_users 19,940 0.12 0   0 14


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 68 11.76 368 6.52 16 0
CLUSTER 45 2.22 95 3.16 2 0
INDEX 20 65.00 22 77.27 4 0
SQL AREA 27,147 98.57 122,187 65.42 352 62
TABLE/PROCEDURE 2,172 14.69 83,432 2.29 678 0
TRIGGER 6 33.33 37 16.22 4 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 76.49   2.83 6.16 26 26 27 27
  Freeable 9.44 0.00 0.73 0.39 1   13 13
  SQL 2.74 1.42 0.13 0.19 1 8 21 18
  PL/SQL 0.79 0.28 0.03 0.04 0 0 27 27
E Other 78.47   2.80 6.05 26 26 28 28
  Freeable 10.69 0.00 0.67 0.29 1   16 16
  SQL 2.87 1.47 0.13 0.18 1 8 22 19
  PL/SQL 0.84 0.30 0.03 0.04 0 0 28 28

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 3.16 6.62 109.49
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.83 15.89 -33.31
shared KGLS heap 2.99   -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 2.12 4.63 118.69
shared PL/SQL MPCODE 4.05   -100.00
shared event statistics per sess 3.85 3.85 0.00
shared free memory 50.38 44.71 -11.24
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 5.38 6.23 15.64
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 12.86 3.44 -73.26
shared sql area:PLSQL   15.66  
  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