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: 7882 18-Apr-07 14:09:36 24 10.3
End Snap: 7883 18-Apr-07 14:17:43 24 10.1
Elapsed:   8.12 (mins)    
DB Time:   7.80 (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: 2,873.51 15,907.41
Logical reads: 66,622.36 368,813.08
Block changes: 11.04 61.11
Physical reads: 14,389.36 79,657.72
Physical writes: 1.76 9.74
User calls: 63.72 352.74
Parses: 23.26 128.76
Hard parses: 20.63 114.18
Sorts: 1.86 10.28
Logons: 0.03 0.16
Executes: 27.09 149.95
Transactions: 0.18  

 

% Blocks changed per Read: 0.02 Recursive Call %: 46.05
Rollback per transaction %: 4.55 Rows per Sort: 12.10

Instance Efficiency Percentages (Target 100%)

 

Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 78.40 In-memory Sort %: 100.00
Library Hit %: 54.63 Soft Parse %: 11.32
Execute to Parse %: 14.13 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 98.80 % Non-Parse CPU: 96.96

Shared Pool Statistics

 

  Begin End
Memory Usage %: 78.72 82.39
% SQL with executions>1: 92.37 12.32
% Memory for SQL w/exec>1: 82.10 16.83

Top 5 Timed Events

 

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time   460   98.3  
db file scattered read 420,223 92 0 19.6 User I/O
control file parallel write 160 7 43 1.5 System I/O
db file sequential read 170,518 4 0 .8 User I/O
log file parallel write 120 1 8 .2 System I/O

Main Report


Back to Top

Wait Events Statistics

Back to Top

Time Model Statistics

  • Total time in database user-calls (DB Time): 467.9s
  • 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 459.93 98.30
sql execute elapsed time 452.13 96.63
parse time elapsed 14.37 3.07
hard parse elapsed time 11.10 2.37
PL/SQL execution elapsed time 0.48 0.10
PL/SQL compilation elapsed time 0.10 0.02
hard parse (sharing criteria) elapsed time 0.04 0.01
connection management call elapsed time 0.04 0.01
repeated bind elapsed time 0.00 0.00
hard parse (bind mismatch) elapsed time 0.00 0.00
DB time 467.90  
background elapsed time 12.42  
background cpu time 1.22  

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 590,741 0.00 95 0 6,712.97
System I/O 1,054 0.00 8 7 11.98
Commit 45 0.00 0 9 0.51
Network 20,970 0.00 0 0 238.30
Application 42 0.00 0 0 0.48
Concurrency 3 0.00 0 1 0.03
Other 4 0.00 0 0 0.05

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
db file scattered read 420,223 0.00 92 0 4,775.26
control file parallel write 160 0.00 7 43 1.82
db file sequential read 170,518 0.00 4 0 1,937.70
log file parallel write 120 0.00 1 8 1.36
log file sync 45 0.00 0 9 0.51
SQL*Net message to client 20,969 0.00 0 0 238.28
SQL*Net break/reset to client 42 0.00 0 0 0.48
control file sequential read 774 0.00 0 0 8.80
latch: shared pool 3 0.00 0 1 0.03
LGWR wait for redo copy 4 0.00 0 0 0.05
SQL*Net more data from client 1 0.00 0 0 0.01
SQL*Net message from client 20,969 0.00 1,262 60 238.28
Streams AQ: qmn slave idle wait 18 0.00 485 26957 0.20
Streams AQ: qmn coordinator idle wait 37 51.35 485 13114 0.42
wait for unread message on broadcast channel 487 100.00 476 977 5.53
jobq slave wait 162 95.06 472 2911 1.84
Streams AQ: waiting for messages in the queue 97 100.00 469 4836 1.10
SGA: MMAN sleep for component shrink 2 50.00 0 10 0.02

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 160 0.00 7 43 1.82
log file parallel write 119 0.00 1 8 1.35
events in waitclass Other 4 0.00 0 0 0.05
db file sequential read 1 0.00 0 0 0.01
rdbms ipc message 1,638 94.93 3,786 2311 18.61
Streams AQ: qmn slave idle wait 18 0.00 485 26957 0.20
Streams AQ: qmn coordinator idle wait 37 51.35 485 13114 0.42
pmon timer 162 100.00 472 2916 1.84
smon timer 2 100.00 335 167629 0.02
SGA: MMAN sleep for component shrink 2 50.00 0 10 0.02

Back to Wait Events Statistics
Back to Top

Operating System Statistics

Statistic Total
BUSY_TIME 53,882
IDLE_TIME 140,881
IOWAIT_TIME 1,331
NICE_TIME 308
SYS_TIME 12,748
USER_TIME 40,779
LOAD 1
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 25,232
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 466.60 458.90 7,009,877 32,448,629
o102ee1 1.30 1.00 11 5,600
SYS$BACKGROUND 0.00 0.00 1 1,373

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 590731 9547 3 0 0 0 20214 8
o102ee1 11 0 0 0 0 0 744 0
SYS$BACKGROUND 1 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
1 1 1 0.71 0.15 0cg8busnwuyh9 sqlplus@hpap0601 (TNS V1-V3) BEGIN DBMS_WORKLOAD_REPOSITO...
0 0 8 0.05 0.08 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
0 0 294 0.00 0.08 cb75rw3w1tt0s OEM.SystemPool begin MGMT_JOB_ENGINE.get_sche...
0 0 1 0.30 0.06 bunssq950snhf   insert into wrh$_sga_target_ad...
0 0 1 0.19 0.04 0hbv80w9ypy0n emagent@hpap0601 (TNS V1-V3) /* OracleOEM */ SELECT end_...
0 0 14 0.01 0.04 8hk7xvhua40va OEM.Loader INSERT INTO MGMT_METRICS_RAW(C...
0 0 7 0.02 0.02 abtp0uqvdb1d3   CALL MGMT_ADMIN_DATA.EVALUATE_...
0 0 1 0.11 0.02 a17nj4wsb5gcm SQL*Plus select emp1no, ename from emp1...
0 0 1 0.11 0.02 cwx13g8ysb2ub SQL*Plus select emp1no, ename from emp1...
0 0 1 0.11 0.02 g0180fvp36gmw SQL*Plus select emp1no, ename from emp1...

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
1 1 1 0.71 0.15 0cg8busnwuyh9 sqlplus@hpap0601 (TNS V1-V3) BEGIN DBMS_WORKLOAD_REPOSITO...
0 0 294 0.00 0.08 cb75rw3w1tt0s OEM.SystemPool begin MGMT_JOB_ENGINE.get_sche...
0 0 8 0.04 0.08 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
0 0 1 0.30 0.06 bunssq950snhf   insert into wrh$_sga_target_ad...
0 0 1 0.19 0.04 0hbv80w9ypy0n emagent@hpap0601 (TNS V1-V3) /* OracleOEM */ SELECT end_...
0 0 14 0.01 0.04 8hk7xvhua40va OEM.Loader INSERT INTO MGMT_METRICS_RAW(C...
0 0 7 0.02 0.02 abtp0uqvdb1d3   CALL MGMT_ADMIN_DATA.EVALUATE_...
0 0 1 0.11 0.02 cwx13g8ysb2ub SQL*Plus select emp1no, ename from emp1...
0 0 1 0.11 0.02 a17nj4wsb5gcm SQL*Plus select emp1no, ename from emp1...
0 0 1 0.11 0.02 g0180fvp36gmw SQL*Plus select emp1no, ename from emp1...

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: 32,455,551
  • Captured SQL account for 15.9% of Total
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
8,123 1 8,123.00 0.03 0.71 0.71 0cg8busnwuyh9 sqlplus@hpap0601 (TNS V1-V3) BEGIN DBMS_WORKLOAD_REPOSITO...
3,243 1 3,243.00 0.01 0.08 0.08 004kcfrmktkj2 SQL*Plus select emp1no, ename from emp1...
3,243 1 3,243.00 0.01 0.02 0.02 01vkf625s61b9 SQL*Plus select emp1no, ename from emp1...
3,243 1 3,243.00 0.01 0.07 0.07 02st69bsctjyh SQL*Plus select emp1no, ename from emp1...
3,243 1 3,243.00 0.01 0.07 0.07 02tkznbyqf5su SQL*Plus select emp1no, ename from emp1...
3,243 1 3,243.00 0.01 0.02 0.02 02wgnvau4y6fz SQL*Plus select emp1no, ename from emp1...
3,243 1 3,243.00 0.01 0.02 0.02 03336c2b57677 SQL*Plus select emp1no, ename from emp1...
3,243 1 3,243.00 0.01 0.07 0.07 03464ty2n13zm SQL*Plus select emp1no, ename from emp1...
3,243 1 3,243.00 0.01 0.08 0.08 03u4yqx1rc5h6 SQL*Plus select emp1no, ename from emp1...
3,243 1 3,243.00 0.01 0.07 0.07 043k6frc9jhaa SQL*Plus select emp1no, ename from emp1...

Back to SQL Statistics
Back to Top

SQL ordered by Reads

  • Total Disk Reads: 7,009,879
  • Captured SQL account for 23.4% of Total
Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
1,782 1 1,782.00 0.03 0.08 0.08 d97drcnr50s17 SQL*Plus select emp1no, ename from emp1...
1,772 1 1,772.00 0.03 0.08 0.08 ccbc7uwnta3xd SQL*Plus select emp1no, ename from emp1...
1,769 1 1,769.00 0.03 0.08 0.08 gk4ph2gu4d55u SQL*Plus select emp1no, ename from emp1...
1,766 1 1,766.00 0.03 0.08 0.08 922v4j6cjat33 SQL*Plus select emp1no, ename from emp1...
1,763 1 1,763.00 0.03 0.09 0.09 9uxf7690gxwgf SQL*Plus select emp1no, ename from emp1...
1,762 1 1,762.00 0.03 0.08 0.08 c4cm2gbpjfbhg SQL*Plus select emp1no, ename from emp1...
1,761 1 1,761.00 0.03 0.08 0.08 c629u0x2bmm0g SQL*Plus select emp1no, ename from emp1...
1,756 1 1,756.00 0.03 0.09 0.09 04yxjbx9k8679 SQL*Plus select emp1no, ename from emp1...
1,756 1 1,756.00 0.03 0.08 0.08 4pu4m428cvj2q SQL*Plus select emp1no, ename from emp1...
1,754 1 1,754.00 0.03 0.08 0.08 5dvpduy7zztyb SQL*Plus select emp1no, ename from emp1...

Back to SQL Statistics
Back to Top

SQL ordered by Executions

  • Total Executions: 13,196
  • Captured SQL account for 35.9% of Total
Executions Rows Processed Rows per Exec CPU per Exec (s) Elap per Exec (s) SQL Id SQL Module SQL Text
302 302 1.00 0.00 0.00 089dbukv1aanh OEM.SystemPool SELECT SYS_EXTRACT_UTC(SYSTIME...
294 0 0.00 0.00 0.00 6gh8gj9n09vr7 OEM.SystemPool SELECT JOB_ID, EXECUTION_ID, ...
294 294 1.00 0.00 0.00 cb75rw3w1tt0s OEM.SystemPool begin MGMT_JOB_ENGINE.get_sche...
211 211 1.00 0.00 0.00 91h2x42zqagcm   UPDATE MGMT_CURRENT_METRICS SE...
158 240 1.52 0.00 0.00 0h6b2sajwb74n DBMS_SCHEDULER select privilege#, level from ...
98 8 0.08 0.00 0.00 6ssrk2dqj7jbx   select job, nvl2(last_date, ...
90 90 1.00 0.00 0.00 g2wr3u7s1gtf3   select count(*) from sys.job$ ...
77 77 1.00 0.00 0.00 3c1kubcdjnppq   update sys.col_usage$ set eq...
71 0 0.00 0.00 0.00 350f5yrnnmshs   lock table sys.mon_mods$ in ex...
71 71 1.00 0.00 0.00 g00cj285jmgsw   update sys.mon_mods$ set inser...

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

  • Total Parse Calls: 11,331
  • Captured SQL account for 24.5% of Total
Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
158 158 1.39 0h6b2sajwb74n DBMS_SCHEDULER select privilege#, level from ...
71 71 0.63 350f5yrnnmshs   lock table sys.mon_mods$ in ex...
71 71 0.63 g00cj285jmgsw   update sys.mon_mods$ set inser...
35 77 0.31 3c1kubcdjnppq   update sys.col_usage$ set eq...
35 0 0.31 53btfq0dt9bs9   insert into sys.col_usage$ val...
35 35 0.31 b2gnxm5z6r51n   lock table sys.col_usage$ in e...
18 18 0.16 39m4sx9k63ba2   select /*+ index(idl_ub2$ i_id...
18 18 0.16 c6awqs517jpj0   select /*+ index(idl_char$ i_i...
18 18 0.16 cvn54b7yz0s8u   select /*+ index(idl_ub1$ i_id...
18 18 0.16 ga9j9xk5cy9s0   select /*+ index(idl_sb4$ i_id...

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

Back to SQL Statistics
Back to Top

 

Complete List of SQL Text

 

SQL Id SQL Text
004kcfrmktkj2 select emp1no, ename from emp1 where emp1no = 9789
01vkf625s61b9 select emp1no, ename from emp1 where emp1no = 8213
02st69bsctjyh select emp1no, ename from emp1 where emp1no = 9292
02tkznbyqf5su select emp1no, ename from emp1 where emp1no = 9427
02wgnvau4y6fz select emp1no, ename from emp1 where emp1no = 7029
03336c2b57677 select emp1no, ename from emp1 where emp1no = 8689
03464ty2n13zm select emp1no, ename from emp1 where emp1no = 8951
03u4yqx1rc5h6 select emp1no, ename from emp1 where emp1no = 9028
043k6frc9jhaa select emp1no, ename from emp1 where emp1no = 9517
04yxjbx9k8679 select emp1no, ename from emp1 where emp1no = 8824
089dbukv1aanh SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL
0cg8busnwuyh9 BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); END;
0h6b2sajwb74n select privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
0hbv80w9ypy0n /* OracleOEM */ SELECT end_time, status, session_key, session_recid, session_stamp, command_id, start_time, time_taken_display, input_type, output_device_type, input_bytes_display, output_bytes_display, output_bytes_per_sec_display FROM v$rman_backup_job_details WHERE end_time = (select max(end_time) from v$rman_backup_job_details)
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))
350f5yrnnmshs lock table sys.mon_mods$ in exclusive mode nowait
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#
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
4pu4m428cvj2q select emp1no, ename from emp1 where emp1no = 8794
53btfq0dt9bs9 insert into sys.col_usage$ values ( :objn, :coln, decode(bitand(:flag, 1), 0, 0, 1), decode(bitand(:flag, 2), 0, 0, 1), decode(bitand(:flag, 4), 0, 0, 1), decode(bitand(:flag, 8), 0, 0, 1), decode(bitand(:flag, 16), 0, 0, 1), decode(bitand(:flag, 32), 0, 0, 1), :time)
5dvpduy7zztyb select emp1no, ename from emp1 where emp1no = 8818
6gh8gj9n09vr7 SELECT JOB_ID, EXECUTION_ID, STEP_ID, STEP_NAME, STEP_TYPE, ITERATE_PARAM, ITERATE_PARAM_INDEX, COMMAND_TYPE, TIMEZONE_REGION FROM MGMT_JOB_EXECUTION J WHERE STEP_TYPE IN (:B7 , :B6 , :B5 ) AND STEP_STATUS = :B4 AND COMMAND_TYPE = :B3 AND STEP_NAME LIKE :B2 AND START_TIME <= SYS_EXTRACT_UTC(SYSTIMESTAMP) AND ROWNUM <= :B1
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;
6ssrk2dqj7jbx select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (next_date <= :2)) or ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)) and (this_date is null) order by next_date, job
8hk7xvhua40va INSERT INTO MGMT_METRICS_RAW(COLLECTION_TIMESTAMP, KEY_VALUE, METRIC_GUID, STRING_VALUE, TARGET_GUID, VALUE) VALUES ( :1, NVL(:2, ' '), :3, :4, :5, :6)
91h2x42zqagcm UPDATE MGMT_CURRENT_METRICS SET COLLECTION_TIMESTAMP = :B3 , VALUE = :B2 , STRING_VALUE = :B1 WHERE TARGET_GUID = :B6 AND METRIC_GUID = :B5 AND KEY_VALUE = :B4 AND COLLECTION_TIMESTAMP < :B3
922v4j6cjat33 select emp1no, ename from emp1 where emp1no = 8822
9uxf7690gxwgf select emp1no, ename from emp1 where emp1no = 8798
a17nj4wsb5gcm select emp1no, ename from emp1 where emp1no = 9765
abtp0uqvdb1d3 CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mguid, :result)
b2gnxm5z6r51n lock table sys.col_usage$ in exclusive mode nowait
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
c4cm2gbpjfbhg select emp1no, ename from emp1 where emp1no = 8800
c629u0x2bmm0g select emp1no, ename from emp1 where emp1no = 8808
c6awqs517jpj0 select /*+ index(idl_char$ i_idl_char1) +*/ piece#, length, piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece#
cb75rw3w1tt0s begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;
ccbc7uwnta3xd select emp1no, ename from emp1 where emp1no = 8802
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#
cwx13g8ysb2ub select emp1no, ename from emp1 where emp1no = 9070
d97drcnr50s17 select emp1no, ename from emp1 where emp1no = 8796
g00cj285jmgsw update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where obj# = :objn
g0180fvp36gmw select emp1no, ename from emp1 where emp1no = 8838
g2wr3u7s1gtf3 select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysdate+5/86400))
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#
gk4ph2gu4d55u select emp1no, ename from emp1 where emp1no = 8810

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 47,850 98.22 543.75
CPU used when call started 47,772 98.06 542.86
CR blocks created 99 0.20 1.13
Cached Commit SCN referenced 0 0.00 0.00
Commit SCN cached 0 0.00 0.00
DB time 192,400 394.94 2,186.36
DBWR checkpoint buffers written 851 1.75 9.67
DBWR checkpoints 0 0.00 0.00
DBWR revisited being-written buffer 0 0.00 0.00
DBWR transaction table writes 14 0.03 0.16
DBWR undo block writes 110 0.23 1.25
IMU CR rollbacks 27 0.06 0.31
IMU Flushes 121 0.25 1.38
IMU Redo allocation size 102,332 210.06 1,162.86
IMU commits 80 0.16 0.91
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 275,288 565.09 3,128.27
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 20,957 43.02 238.15
active txn count during cleanout 49 0.10 0.56
application wait time 1 0.00 0.01
background checkpoints completed 0 0.00 0.00
background checkpoints started 0 0.00 0.00
background timeouts 1,555 3.19 17.67
branch node splits 0 0.00 0.00
buffer is not pinned count 8,533 17.52 96.97
buffer is pinned count 2,331 4.78 26.49
bytes received via SQL*Net from client 2,353,810 4,831.73 26,747.84
bytes sent via SQL*Net to client 4,810,373 9,874.38 54,663.33
calls to get snapshot scn: kcmgss 84,662 173.79 962.07
calls to kcmgas 431 0.88 4.90
calls to kcmgcs 41 0.08 0.47
change write time 5 0.01 0.06
cleanout - number of ktugct calls 67 0.14 0.76
cleanouts and rollbacks - consistent read gets 0 0.00 0.00
cleanouts only - consistent read gets 3 0.01 0.03
cluster key scan block gets 556 1.14 6.32
cluster key scans 155 0.32 1.76
commit batch performed 0 0.00 0.00
commit batch requested 0 0.00 0.00
commit batch/immediate performed 4 0.01 0.05
commit batch/immediate requested 4 0.01 0.05
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 6 0.01 0.07
commit cleanout failures: cannot pin 0 0.00 0.00
commit cleanouts 928 1.90 10.55
commit cleanouts successfully completed 922 1.89 10.48
commit immediate performed 4 0.01 0.05
commit immediate requested 4 0.01 0.05
commit txn count during cleanout 37 0.08 0.42
concurrency wait time 0 0.00 0.00
consistent changes 99 0.20 1.13
consistent gets 32,450,820 66,612.65 368,759.32
consistent gets - examination 7,574 15.55 86.07
consistent gets direct 0 0.00 0.00
consistent gets from cache 32,450,820 66,612.65 368,759.32
cursor authentications 44 0.09 0.50
data blocks consistent reads - undo records applied 99 0.20 1.13
db block changes 5,378 11.04 61.11
db block gets 4,731 9.71 53.76
db block gets direct 0 0.00 0.00
db block gets from cache 4,731 9.71 53.76
deferred (CURRENT) block cleanout applications 656 1.35 7.45
dirty buffers inspected 1 0.00 0.01
enqueue conversions 100 0.21 1.14
enqueue releases 14,875 30.53 169.03
enqueue requests 14,875 30.53 169.03
enqueue timeouts 0 0.00 0.00
enqueue waits 0 0.00 0.00
execute count 13,196 27.09 149.95
failed probes on index block reclamation 0 0.00 0.00
frame signature mismatch 0 0.00 0.00
free buffer inspected 7,442,586 15,277.59 84,574.84
free buffer requested 7,010,134 14,389.89 79,660.61
heap block compress 4 0.01 0.05
hot buffers moved to head of LRU 263 0.54 2.99
immediate (CR) block cleanout applications 3 0.01 0.03
immediate (CURRENT) block cleanout applications 102 0.21 1.16
index crx upgrade (positioned) 1,294 2.66 14.70
index fast full scans (full) 4 0.01 0.05
index fetch by key 3,656 7.50 41.55
index scans kdiixs1 2,460 5.05 27.95
leaf node 90-10 splits 7 0.01 0.08
leaf node splits 18 0.04 0.20
lob reads 0 0.00 0.00
lob writes 0 0.00 0.00
lob writes unaligned 0 0.00 0.00
logons cumulative 14 0.03 0.16
messages received 501 1.03 5.69
messages sent 501 1.03 5.69
no buffer to keep pinned count 0 0.00 0.00
no work - consistent read gets 32,371,173 66,449.16 367,854.24
opened cursors cumulative 11,472 23.55 130.36
parse count (failures) 0 0.00 0.00
parse count (hard) 10,048 20.63 114.18
parse count (total) 11,331 23.26 128.76
parse time cpu 1,400 2.87 15.91
parse time elapsed 1,417 2.91 16.10
physical read IO requests 590,724 1,212.59 6,712.77
physical read bytes 57,424,928,768 117,877,663.19 652,556,008.73
physical read total IO requests 591,506 1,214.20 6,721.66
physical read total bytes 57,437,675,520 117,903,828.79 652,700,858.18
physical read total multi block requests 420,223 862.60 4,775.26
physical reads 7,009,879 14,389.36 79,657.72
physical reads cache 7,009,879 14,389.36 79,657.72
physical reads cache prefetch 6,419,155 13,176.77 72,944.94
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 400 0.82 4.55
physical write bytes 7,020,544 14,411.26 79,778.91
physical write total IO requests 1,000 2.05 11.36
physical write total bytes 16,418,816 33,703.34 186,577.45
physical write total multi block requests 239 0.49 2.72
physical writes 857 1.76 9.74
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 857 1.76 9.74
physical writes non checkpoint 210 0.43 2.39
pinned buffers inspected 8 0.02 0.09
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 26,496 54.39 301.09
recursive cpu usage 1,296 2.66 14.73
redo blocks written 2,996 6.15 34.05
redo buffer allocation retries 0 0.00 0.00
redo entries 2,690 5.52 30.57
redo log space requests 0 0.00 0.00
redo log space wait time 0 0.00 0.00
redo ordering marks 81 0.17 0.92
redo size 1,399,852 2,873.51 15,907.41
redo subscn max counts 217 0.45 2.47
redo synch time 41 0.08 0.47
redo synch writes 478 0.98 5.43
redo wastage 34,148 70.10 388.05
redo write time 96 0.20 1.09
redo writer latching time 0 0.00 0.00
redo writes 119 0.24 1.35
rollback changes - undo records applied 4 0.01 0.05
rollbacks only - consistent read gets 99 0.20 1.13
rows fetched via callback 2,272 4.66 25.82
session connect time 0 0.00 0.00
session cursor cache hits 647 1.33 7.35
session logical reads 32,455,551 66,622.36 368,813.08
session pga memory 11,825,824 24,275.18 134,384.36
session pga memory max 32,076,448 65,844.17 364,505.09
session uga memory 34,360,225,176 70,532,138.87 390,457,104.27
session uga memory max 11,349,388 23,297.19 128,970.32
shared hash latch upgrades - no wait 1,341 2.75 15.24
sorts (disk) 0 0.00 0.00
sorts (memory) 905 1.86 10.28
sorts (rows) 10,947 22.47 124.40
sql area evicted 1,085 2.23 12.33
sql area purged 0 0.00 0.00
summed dirty queue length 1 0.00 0.01
switch current to new buffer 18 0.04 0.20
table fetch by rowid 3,622 7.43 41.16
table fetch continued row 79 0.16 0.90
table scan blocks gotten 32,360,510 66,427.27 367,733.07
table scan rows gotten 5,244,535,534 10,765,596.17 59,596,994.70
table scans (cache partitions) 0 0.00 0.00
table scans (long tables) 10,000 20.53 113.64
table scans (short tables) 249 0.51 2.83
total number of times SMON posted 0 0.00 0.00
transaction rollbacks 4 0.01 0.05
undo change vector size 463,716 951.88 5,269.50
user I/O wait time 9,545 19.59 108.47
user calls 31,041 63.72 352.74
user commits 84 0.17 0.95
user rollbacks 4 0.01 0.05
workarea executions - multipass 0 0.00 0.00
workarea executions - onepass 0 0.00 0.00
workarea executions - optimal 495 1.02 5.63
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 775,991 776,259
opened cursors current 247 242
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 590,600 1,212 0.16 11.87 0 0 0 0.00
SYSAUX 99 0 0.20 1.08 346 1 0 0.00
SYSTEM 35 0 0.29 1.00 22 0 0 0.00
UNDOTBS1 0 0 0.00 0.00 33 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 99 0 0.20 1.08 346 1 0 0.00
SYSTEM /opt/app/oracle/oradata/o102ee1/system01.dbf 35 0 0.29 1.00 22 0 0 0.00
UNDOTBS1 /opt/app/oracle/oradata/o102ee1/undotbs01.dbf 0 0     33 0 0 0.00
USERS /opt/app/oracle/oradata/o102ee1/users01.dbf 590,600 1,212 0.16 11.87 0 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,445 78 32,455,562 7,009,883 858 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 8 328 701 26907 184320 26907  
E 0 8 238 582 6971 184320 6971  

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 93,490,172
D 16 0.17 1,996 1.00 91,267,130
D 24 0.26 2,994 1.00 91,212,759
D 32 0.35 3,992 1.00 91,152,983
D 40 0.43 4,990 1.00 91,061,835
D 48 0.52 5,988 1.00 91,011,384
D 56 0.61 6,986 1.00 90,993,062
D 64 0.70 7,984 1.00 90,981,620
D 72 0.78 8,982 1.00 90,973,988
D 80 0.87 9,980 1.00 90,967,050
D 88 0.96 10,978 1.00 90,960,909
D 92 1.00 11,477 1.00 90,958,152
D 96 1.04 11,976 1.00 90,955,394
D 104 1.13 12,974 1.00 90,950,577
D 112 1.22 13,972 1.00 90,946,217
D 120 1.30 14,970 1.00 90,942,473
D 128 1.39 15,968 1.00 90,938,652
D 136 1.48 16,966 1.00 90,935,367
D 144 1.57 17,964 1.00 90,931,569
D 152 1.65 18,962 1.00 90,927,210
D 160 1.74 19,960 0.93 84,145,109

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 13 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 88.67 0.00 0.00 0.00 0.00 3,276
E 16 4 88.86 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 478 478 0 0
64K 128K 1 1 0 0
512K 1024K 16 16 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 27,697.01 1,448.07 95.00 301
16 1.00 27,697.01 47.84 100.00 301
19 1.20 27,697.01 47.84 100.00 301
22 1.40 27,697.01 47.84 100.00 301
26 1.60 27,697.01 47.84 100.00 300
29 1.80 27,697.01 47.84 100.00 295
32 2.00 27,697.01 46.39 100.00 271
48 3.00 27,697.01 8.37 100.00 103
64 4.00 27,697.01 0.00 100.00 4
96 6.00 27,697.01 0.00 100.00 0
128 8.00 27,697.01 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
100 0.68 16 2,914 467,721 0.86 81,204 22.74 22,270,071
116 0.78 31 6,151 495,346 0.91 53,579 15.00 22,301,648
132 0.89 46 9,035 521,612 0.96 27,313 7.65 22,333,461
148 1.00 61 11,736 545,354 1.00 3,571 1.00 22,366,818
164 1.11 76 14,457 566,159 1.04 1 0.00 22,402,955
180 1.22 91 17,303 584,191 1.07 1 0.00 22,438,883
196 1.32 106 20,020 599,706 1.10 1 0.00 22,466,200
212 1.43 121 22,735 612,853 1.12 1 0.00 22,488,542
228 1.54 136 25,311 623,696 1.14 1 0.00 22,509,081
244 1.65 151 28,195 632,347 1.16 1 0.00 22,525,918
260 1.76 166 30,769 639,054 1.17 1 0.00 22,541,665
276 1.86 182 32,794 644,124 1.18 1 0.00 22,556,576
292 1.97 198 34,428 647,796 1.19 1 0.00 22,571,765
308 2.08 213 35,564 650,238 1.19 1 0.00 22,588,234

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 131,497 93,397,837
192 0.75 42,867 93,397,837
256 1.00 42,586 90,880,449
320 1.25 42,582 90,835,009
384 1.50 41,819 84,055,327
448 1.75 41,819 84,055,327
512 2.00 41,819 84,055,327

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

  • 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.25 409 132 3 15/17.2 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
18-Apr 14:21 153 151 132 3 17 0/0 0/0/0/0/0/0
18-Apr 14:11 95 258 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 320 0.00   0 0  
AWR Alerted Metric Element list 1,727 0.00   0 0  
Consistent RBA 119 0.00   0 0  
FOB s.o list latch 24 0.00   0 0  
In memory undo latch 1,570 0.00   0 320 0.00
JS queue state obj latch 2,940 0.00   0 0  
KMG MMAN ready and startup request latch 164 0.00   0 0  
KMG resize request state object freelist 8 0.00   0 0  
KTF sga latch 1 0.00   0 142 0.00
MQL Tracking Latch 0     0 10 0.00
Memory Management Latch 82 0.00   0 164 0.00
OS process 18 0.00   0 0  
OS process allocation 174 0.00   0 0  
OS process: request allocation 12 0.00   0 0  
PL/SQL warning settings 65 0.00   0 0  
SQL memory manager latch 142 0.00   0 160 0.00
SQL memory manager workarea list latch 20,518 0.00   0 0  
Shared B-Tree 19 0.00   0 0  
active checkpoint queue latch 565 0.00   0 0  
active service list 951 0.00   0 162 0.00
archive control 3 0.00   0 0  
buffer pool 8 0.00   0 0  
cache buffer handles 58 0.00   0 0  
cache buffers chains 71,928,645 0.00 0.00 0 27,675,168 0.00
cache buffers lru chain 2,766 1.52 0.00 0 53,608,885 0.00
cache table scan latch 420,223 0.00   0 420,223 0.00
channel handle pool latch 12 0.00   0 0  
channel operations parent latch 2,859 0.00   0 0  
checkpoint queue latch 9,582 0.00   0 1,398 0.00
client/application info 240 0.00   0 0  
compile environment latch 434 0.00   0 0  
dml lock allocation 2,296 0.00   0 0  
dummy allocation 28 0.00   0 0  
enqueue hash chains 29,842 0.00   0 0  
enqueues 27,400 0.00 0.00 0 0  
event group latch 6 0.00   0 0  
file cache latch 40 0.00   0 0  
global KZLD latch for mem in SGA 4 0.00   0 0  
hash table column usage latch 247 0.00   0 11,123 0.00
hash table modification latch 2 0.00   0 0  
job workq parent latch 0     0 16 0.00
job_queue_processes parameter latch 16 0.00   0 0  
kks stats 56,760 0.00   0 0  
ksuosstats global area 34 0.00   0 0  
ktm global data 2 0.00   0 0  
kwqbsn:qsga 19 0.00   0 0  
lgwr LWN SCN 231 0.00   0 0  
library cache 254,413 0.01 0.00 0 40,619 0.01
library cache load lock 242 0.00   0 0  
library cache lock 68,240 0.00   0 0  
library cache lock allocation 314 0.00   0 0  
library cache pin 67,859 0.00   0 0  
library cache pin allocation 86 0.00   0 0  
list of block allocation 70 0.00   0 0  
messages 4,221 0.00   0 0  
mostly latch-free SCN 231 0.00   0 0  
multiblock read objects 1,173,574 0.00   0 0  
ncodef allocation latch 8 0.00   0 0  
object queue header heap 1,132 0.00   0 35 0.00
object queue header operation 14,023,014 0.00 0.00 0 676 0.00
object stats modification 1 0.00   0 0  
parallel query alloc buffer 64 0.00   0 0  
parameter table allocation management 22 0.00   0 0  
post/wait queue 126 0.00   0 45 0.00
process allocation 12 0.00   0 6 0.00
process group creation 12 0.00   0 0  
qmn task queue latch 72 0.00   0 0  
redo allocation 1,219 0.08 0.00 0 2,680 0.00
redo copy 0     0 2,680 0.15
redo writing 1,261 0.00   0 0  
resmgr group change latch 60 0.00   0 0  
resmgr:actses active list 29 0.00   0 0  
resmgr:actses change group 14 0.00   0 0  
resmgr:free threads list 28 0.00   0 0  
resmgr:schema config 1 0.00   0 0  
row cache objects 223,465 0.00   0 1,117 0.00
sequence cache 18 0.00   0 0  
session allocation 9,967 0.00   0 0  
session idle bit 62,502 0.00   0 0  
session state list latch 28 0.00   0 0  
session switching 8 0.00   0 0  
session timer 162 0.00   0 0  
shared pool 342,373 0.01 0.07 0 0  
shared pool simulator 434,797 0.00   0 0  
simulator hash latch 8,628,841 0.00   0 0  
simulator lru latch 4,102,836 0.00 0.00 0 444,191 0.00
sort extent pool 13 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 28 0.00   0 0  
transaction branch allocation 8 0.00   0 0  
undo global data 1,279 0.00   0 0  
user lock 32 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 342,373 43 3 40 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
shared pool kghupr1 0 2 0
shared pool kghalo 0 1 0

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: 32,455,551
  • Captured Segments account for 100.0% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Logical Reads %Total
SCOTT USERS EMP1   TABLE 32,429,568 99.92
SYS SYSAUX SYS_IOT_TOP_8813   INDEX 7,200 0.02
SYS SYSAUX WRH$_SQL_PLAN_PK   INDEX 1,184 0.00
SYS SYSAUX WRH$_SQLSTAT_INDEX 18455_7846 INDEX PARTITION 1,088 0.00
SYS SYSTEM SYS_C00648   INDEX 880 0.00

Back to Segment Statistics
Back to Top

Segments by Physical Reads

  • Total Physical Reads: 7,009,879
  • Captured Segments account for 100.0% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
SCOTT USERS EMP1   TABLE 7,009,747 100.00
SYS SYSAUX WRH$_SQL_PLAN   TABLE 27 0.00
SYS SYSAUX WRH$_SQLTEXT   TABLE 14 0.00
SYS SYSTEM IDL_UB2$   TABLE 9 0.00
SYS SYSAUX WRH$_ACTIVE_SESSION_HISTORY_PK 18455_7846 INDEX PARTITION 4 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 705 0.00 0   0 25
dc_histogram_data 190 5.26 0   0 719
dc_histogram_defs 20,386 0.19 0   0 845
dc_object_grants 14 35.71 0   0 56
dc_object_ids 21,167 0.08 0   0 508
dc_objects 10,226 0.42 0   0 428
dc_profiles 12 0.00 0   0 1
dc_rollback_segments 54 0.00 0   0 12
dc_segments 20,074 0.04 0   3 181
dc_tablespaces 424 0.00 0   0 5
dc_usernames 75 0.00 0   0 6
dc_users 1,276 0.00 0   0 25
outstanding_alerts 12 0.00 0   0 10


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 0.00 845 0.47 4 0
CLUSTER 6 0.00 10 0.00 0 0
SQL AREA 10,443 95.88 34,015 88.63 38 9
TABLE/PROCEDURE 106 10.38 31,796 0.35 88 0
TRIGGER 10 0.00 52 3.85 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 77.84   3.24 6.77 26 26 24 24
  Freeable 9.25 0.00 0.77 0.37 1   12 12
  SQL 1.07 0.44 0.06 0.05 0 8 18 15
  PL/SQL 0.53 0.22 0.02 0.03 0 0 24 24
E Other 78.36   3.26 6.79 26 26 24 24
  Freeable 8.88 0.00 0.74 0.36 1   12 12
  SQL 1.12 0.46 0.06 0.05 0 8 18 15
  PL/SQL 0.53 0.22 0.02 0.03 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 9.93 14.55 46.56
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.75 23.75 0.00
shared KGLS heap 3.62   -100.00
shared KQR M PO 1.77   -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 10.16 12.54 23.39
shared PL/SQL MPCODE 2.13   -100.00
shared event statistics per sess 3.85 3.85 0.00
shared free memory 31.50 26.07 -17.25
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 8.68 9.63 10.90
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 14.62 18.43 26.02
  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