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: 7884 18-Apr-07 14:26:52 24 10.1
End Snap: 7888 18-Apr-07 14:35:24 24 10.1
Elapsed:   8.54 (mins)    
DB Time:   8.26 (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: 17,292.09 84,360.38
Logical reads: 63,487.14 309,725.33
Block changes: 64.14 312.90
Physical reads: 15,282.45 74,556.25
Physical writes: 3.23 15.77
User calls: 60.66 295.94
Parses: 23.02 112.30
Hard parses: 19.57 95.50
Sorts: 1.97 9.62
Logons: 0.04 0.20
Executes: 26.98 131.61
Transactions: 0.20  

 

% Blocks changed per Read: 0.10 Recursive Call %: 50.39
Rollback per transaction %: 1.90 Rows per Sort: 30.55

Instance Efficiency Percentages (Target 100%)

 

Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 75.93 In-memory Sort %: 100.00
Library Hit %: 55.33 Soft Parse %: 14.97
Execute to Parse %: 14.67 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 97.32 % Non-Parse CPU: 97.09

Shared Pool Statistics

 

Begin End
Memory Usage %: 78.71 76.89
% SQL with executions>1: 94.45 15.30
% Memory for SQL w/exec>1: 78.77 23.68

Top 5 Timed Events

 

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time   486   98.0  
db file scattered read 502,214 102 0 20.5 User I/O
control file parallel write 216 8 35 1.5 System I/O
db file sequential read 220,044 5 0 1.0 User I/O
log file parallel write 171 2 10 .3 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): 495.8s
  • Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
  • Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
DB CPU 485.83 97.98
sql execute elapsed time 480.10 96.83
parse time elapsed 13.91 2.81
hard parse elapsed time 10.60 2.14
PL/SQL execution elapsed time 1.11 0.22
connection management call elapsed time 0.16 0.03
PL/SQL compilation elapsed time 0.05 0.01
repeated bind elapsed time 0.00 0.00
sequence load elapsed time 0.00 0.00
DB time 495.82  
background elapsed time 16.66  
background cpu time 1.88  

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 722,258 0.00 107 0 6,878.65
System I/O 3,186 0.00 9 3 30.34
Commit 45 0.00 0 8 0.43
Network 20,997 0.00 0 0 199.97
Application 38 0.00 0 0 0.36
Concurrency 2 0.00 0 3 0.02
Other 12 0.00 0 0 0.11

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 502,214 0.00 102 0 4,782.99
control file parallel write 216 0.00 8 35 2.06
db file sequential read 220,044 0.00 5 0 2,095.66
log file parallel write 171 0.00 2 10 1.63
log file sync 45 0.00 0 8 0.43
SQL*Net message to client 20,996 0.00 0 0 199.96
control file sequential read 2,799 0.00 0 0 26.66
SQL*Net break/reset to client 38 0.00 0 0 0.36
latch: shared pool 2 0.00 0 3 0.02
LGWR wait for redo copy 10 0.00 0 0 0.10
rdbms ipc reply 2 0.00 0 0 0.02
SQL*Net more data from client 1 0.00 0 0 0.01
SQL*Net message from client 20,996 0.00 1,561 74 199.96
Streams AQ: qmn slave idle wait 19 0.00 520 27345 0.18
Streams AQ: qmn coordinator idle wait 38 50.00 520 13673 0.36
jobq slave wait 171 94.74 501 2928 1.63
wait for unread message on broadcast channel 511 100.00 499 977 4.87
Streams AQ: waiting for messages in the queue 102 100.00 498 4883 0.97
SGA: MMAN sleep for component shrink 1 0.00 0 0 0.01

Back to Wait Events Statistics
Back to Top

Background Wait Events

  • ordered by wait time desc, waits desc (idle events last)
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
control file parallel write 216 0.00 8 35 2.06
log file parallel write 172 0.00 2 10 1.64
db file scattered read 52 0.00 0 0 0.50
control file sequential read 111 0.00 0 0 1.06
db file sequential read 45 0.00 0 0 0.43
events in waitclass Other 12 0.00 0 0 0.11
rdbms ipc message 1,750 93.37 3,986 2278 16.67
Streams AQ: qmn slave idle wait 19 0.00 520 27345 0.18
Streams AQ: qmn coordinator idle wait 38 50.00 520 13673 0.36
smon timer 2 50.00 513 256551 0.02
pmon timer 172 100.00 501 2913 1.64
SGA: MMAN sleep for component shrink 1 0.00 0 0 0.01

Back to Wait Events Statistics
Back to Top

Operating System Statistics

Statistic Total
BUSY_TIME 57,038
IDLE_TIME 147,751
IOWAIT_TIME 1,700
NICE_TIME 358
SYS_TIME 13,909
USER_TIME 42,720
LOAD 1
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 23,328
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 494.70 484.90 7,827,786 32,512,979
o102ee1 1.10 0.90 71 4,538
SYS$BACKGROUND 0.00 0.00 562 3,712

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 722090 10673 2 1 0 0 20237 8
o102ee1 71 5 0 0 0 0 742 0
SYS$BACKGROUND 97 1 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
5 4 4 1.14 0.92 0cg8busnwuyh9 sqlplus@hpap0601 (TNS V1-V3) BEGIN DBMS_WORKLOAD_REPOSITO...
1 1 4 0.31 0.25 bunssq950snhf   insert into wrh$_sga_target_ad...
1 0 309 0.00 0.11 cb75rw3w1tt0s OEM.SystemPool begin MGMT_JOB_ENGINE.get_sche...
0 0 4 0.07 0.06 d92h3rjp0y217   begin prvt_hdm.auto_execute( :...
0 0 9 0.03 0.06 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
0 0 1 0.13 0.03 9bymqgyftpgwu SQL*Plus select emp1no, ename from emp1...
0 0 1 0.13 0.03 9whvap5tun47x SQL*Plus select emp1no, ename from emp1...
0 0 1 0.12 0.02 ds09ud7x09avh SQL*Plus select emp1no, ename from emp1...
0 0 1 0.12 0.02 amwpy3yxtzh4f SQL*Plus select emp1no, ename from emp1...
0 0 1 0.12 0.02 asg4rqz2wyz54 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
Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
5 4 4 1.14 0.92 0cg8busnwuyh9 sqlplus@hpap0601 (TNS V1-V3) BEGIN DBMS_WORKLOAD_REPOSITO...
1 1 4 0.31 0.25 bunssq950snhf   insert into wrh$_sga_target_ad...
1 0 309 0.00 0.11 cb75rw3w1tt0s OEM.SystemPool begin MGMT_JOB_ENGINE.get_sche...
0 0 4 0.07 0.06 d92h3rjp0y217   begin prvt_hdm.auto_execute( :...
0 0 9 0.03 0.06 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
0 0 1 0.13 0.03 9bymqgyftpgwu SQL*Plus select emp1no, ename from emp1...
0 0 1 0.13 0.03 9whvap5tun47x SQL*Plus select emp1no, ename from emp1...
0 0 1 0.12 0.02 ds09ud7x09avh SQL*Plus select emp1no, ename from emp1...
0 0 1 0.12 0.02 amwpy3yxtzh4f SQL*Plus select emp1no, ename from emp1...
0 0 1 0.12 0.02 asg4rqz2wyz54 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,521,160
  • Captured SQL account for 56.5% of Total
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
73,221 4 18,305.25 0.23 4.49 4.55 0cg8busnwuyh9 sqlplus@hpap0601 (TNS V1-V3) BEGIN DBMS_WORKLOAD_REPOSITO...
4,529 4 1,132.25 0.01 0.26 0.29 d92h3rjp0y217   begin prvt_hdm.auto_execute( :...
3,264 1 3,264.00 0.01 0.04 0.05 cm6k4tzqr38f7 SQL*Plus select emp1no, ename from emp1...
3,243 1 3,243.00 0.01 0.02 0.02 0013pqzx8kbfg SQL*Plus select emp1no, ename from emp1...
3,243 1 3,243.00 0.01 0.02 0.02 004kcfrmktkj2 SQL*Plus select emp1no, ename from emp1...
3,243 1 3,243.00 0.01 0.08 0.08 004ykw9jpsh8g SQL*Plus select emp1no, ename from emp1...
3,243 1 3,243.00 0.01 0.02 0.02 00dww3mzgs927 SQL*Plus select emp1no, ename from emp1...
3,243 1 3,243.00 0.01 0.02 0.02 00uyrt10qq3ks SQL*Plus select emp1no, ename from emp1...
3,243 1 3,243.00 0.01 0.07 0.07 011tnwdfnd6rg SQL*Plus select emp1no, ename from emp1...
3,243 1 3,243.00 0.01 0.02 0.02 0128jdhc4ngnq SQL*Plus select emp1no, ename from emp1...

Back to SQL Statistics
Back to Top

SQL ordered by Reads

  • Total Disk Reads: 7,828,406
  • Captured SQL account for 59.2% of Total
Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
2,322 1 2,322.00 0.03 0.10 0.10 92z27ctrgc1rt SQL*Plus select emp1no, ename from emp1...
2,313 1 2,313.00 0.03 0.10 0.10 8bm3jtsppgfzw SQL*Plus select emp1no, ename from emp1...
2,291 1 2,291.00 0.03 0.10 0.10 7xwd34tdu52f1 SQL*Plus select emp1no, ename from emp1...
2,290 1 2,290.00 0.03 0.10 0.10 gfr8zr790m35j SQL*Plus select emp1no, ename from emp1...
2,279 1 2,279.00 0.03 0.11 0.11 csuc5prcz0trv SQL*Plus select emp1no, ename from emp1...
2,277 1 2,277.00 0.03 0.10 0.10 9mbf11kr6nsrf SQL*Plus select emp1no, ename from emp1...
2,268 1 2,268.00 0.03 0.10 0.10 akfj0252pyg54 SQL*Plus select emp1no, ename from emp1...
2,261 1 2,261.00 0.03 0.10 0.10 asnsx6h0pqgg5 SQL*Plus select emp1no, ename from emp1...
2,254 1 2,254.00 0.03 0.10 0.10 8upvyyhz5fj4u SQL*Plus select emp1no, ename from emp1...
2,246 1 2,246.00 0.03 0.10 0.10 0u4a7zybtrq08 SQL*Plus select emp1no, ename from emp1...

Back to SQL Statistics
Back to Top

SQL ordered by Executions

  • Total Executions: 13,819
  • Captured SQL account for 65.4% of Total
Executions Rows Processed Rows per Exec CPU per Exec (s) Elap per Exec (s) SQL Id SQL Module SQL Text
315 315 1.00 0.00 0.00 089dbukv1aanh OEM.SystemPool SELECT SYS_EXTRACT_UTC(SYSTIME...
309 0 0.00 0.00 0.00 6gh8gj9n09vr7 OEM.SystemPool SELECT JOB_ID, EXECUTION_ID, ...
309 309 1.00 0.00 0.00 cb75rw3w1tt0s OEM.SystemPool begin MGMT_JOB_ENGINE.get_sche...
212 212 1.00 0.00 0.00 5h7w8ykwtb2xt   INSERT INTO SYS.WRI$_ADV_PARAM...
177 270 1.53 0.00 0.00 0h6b2sajwb74n DBMS_SCHEDULER select privilege#, level from ...
116 116 1.00 0.00 0.00 91h2x42zqagcm   UPDATE MGMT_CURRENT_METRICS SE...
103 9 0.09 0.00 0.00 6ssrk2dqj7jbx   select job, nvl2(last_date, ...
94 94 1.00 0.00 0.00 g2wr3u7s1gtf3   select count(*) from sys.job$ ...
75 75 1.00 0.00 0.00 3c1kubcdjnppq   update sys.col_usage$ set eq...
50 49 0.98 0.00 0.00 04xtrk7uyhknh   select obj#, type#, ctime, mti...

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

  • Total Parse Calls: 11,792
  • Captured SQL account for 59.2% of Total
Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
177 177 1.50 0h6b2sajwb74n DBMS_SCHEDULER select privilege#, level from ...
29 75 0.25 3c1kubcdjnppq   update sys.col_usage$ set eq...
29 0 0.25 53btfq0dt9bs9   insert into sys.col_usage$ val...
29 29 0.25 b2gnxm5z6r51n   lock table sys.col_usage$ in e...
24 24 0.20 350f5yrnnmshs   lock table sys.mon_mods$ in ex...
24 24 0.20 g00cj285jmgsw   update sys.mon_mods$ set inser...
18 27 0.15 24dkx03u3rj6k OEM.SystemPool SELECT COUNT(*) FROM MGMT_PARA...
17 17 0.14 2b064ybzkwf1y OEM.SystemPool BEGIN EMD_NOTIFICATION.QUEUE_R...
17 17 0.14 6129566gyvx21 OEM.SystemPool SELECT INSTANTIABLE, supertyp...
16 16 0.14 39m4sx9k63ba2   select /*+ index(idl_ub2$ 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 19 18naypzfmabd6 OEM.BoundedPool INSERT INTO MGMT_SYSTEM_PERFOR...

Back to SQL Statistics
Back to Top

 

Complete List of SQL Text

 

SQL Id SQL Text
0013pqzx8kbfg select emp1no, ename from emp1 where emp1no = 3929
004kcfrmktkj2 select emp1no, ename from emp1 where emp1no = 9789
004ykw9jpsh8g select emp1no, ename from emp1 where emp1no = 5851
00dww3mzgs927 select emp1no, ename from emp1 where emp1no = 4346
00uyrt10qq3ks select emp1no, ename from emp1 where emp1no = 4354
011tnwdfnd6rg select emp1no, ename from emp1 where emp1no = 1147
0128jdhc4ngnq select emp1no, ename from emp1 where emp1no = 7736
04xtrk7uyhknh select obj#, type#, ctime, mtime, stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
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
0u4a7zybtrq08 select emp1no, ename from emp1 where emp1no = 9965
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))
24dkx03u3rj6k SELECT COUNT(*) FROM MGMT_PARAMETERS WHERE PARAMETER_NAME=:B1 AND UPPER(PARAMETER_VALUE)='TRUE'
2b064ybzkwf1y BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
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
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)
5h7w8ykwtb2xt INSERT INTO SYS.WRI$_ADV_PARAMETERS (TASK_ID, NAME, DATATYPE, VALUE, FLAGS, DESCRIPTION) VALUES (:B6 , :B5 , :B4 , :B3 , :B2 , :B1 )
6129566gyvx21 SELECT INSTANTIABLE, supertype_owner, supertype_name, LOCAL_ATTRIBUTES FROM all_types WHERE type_name = :1 AND owner = :2
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
7xwd34tdu52f1 select emp1no, ename from emp1 where emp1no = 9865
8bm3jtsppgfzw select emp1no, ename from emp1 where emp1no = 9867
8upvyyhz5fj4u select emp1no, ename from emp1 where emp1no = 9875
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
92z27ctrgc1rt select emp1no, ename from emp1 where emp1no = 9863
9bymqgyftpgwu select emp1no, ename from emp1 where emp1no = 0
9mbf11kr6nsrf select emp1no, ename from emp1 where emp1no = 9871
9whvap5tun47x select emp1no, ename from emp1 where emp1no = 9951
akfj0252pyg54 select emp1no, ename from emp1 where emp1no = 9879
amwpy3yxtzh4f select emp1no, ename from emp1 where emp1no = 4666
asg4rqz2wyz54 select emp1no, ename from emp1 where emp1no = 5207
asnsx6h0pqgg5 select emp1no, ename from emp1 where emp1no = 9877
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
cb75rw3w1tt0s begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;
cm6k4tzqr38f7 select emp1no, ename from emp1 where emp1no = 4230
csuc5prcz0trv select emp1no, ename from emp1 where emp1no = 9873
d92h3rjp0y217 begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end;
ds09ud7x09avh select emp1no, ename from emp1 where emp1no = 9845
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
g2wr3u7s1gtf3 select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysdate+5/86400))
gfr8zr790m35j select emp1no, ename from emp1 where emp1no = 9869

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 50,739 99.05 483.23
CPU used when call started 50,616 98.81 482.06
CR blocks created 71 0.14 0.68
Cached Commit SCN referenced 0 0.00 0.00
Commit SCN cached 0 0.00 0.00
DB time 207,370 404.82 1,974.95
DBWR checkpoint buffers written 1,474 2.88 14.04
DBWR checkpoints 0 0.00 0.00
DBWR revisited being-written buffer 0 0.00 0.00
DBWR transaction table writes 15 0.03 0.14
DBWR undo block writes 252 0.49 2.40
IMU CR rollbacks 29 0.06 0.28
IMU Flushes 131 0.26 1.25
IMU Redo allocation size 49,608 96.84 472.46
IMU commits 93 0.18 0.89
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 274,812 536.48 2,617.26
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 1 0.00 0.01
SQL*Net roundtrips to/from client 20,978 40.95 199.79
active txn count during cleanout 420 0.82 4.00
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,636 3.19 15.58
branch node splits 1 0.00 0.01
buffer is not pinned count 34,330 67.02 326.95
buffer is pinned count 12,115 23.65 115.38
bytes received via SQL*Net from client 2,342,891 4,573.74 22,313.25
bytes sent via SQL*Net to client 4,816,026 9,401.75 45,866.91
calls to get snapshot scn: kcmgss 87,497 170.81 833.30
calls to kcmgas 1,019 1.99 9.70
calls to kcmgcs 293 0.57 2.79
change write time 28 0.05 0.27
cleanout - number of ktugct calls 467 0.91 4.45
cleanouts and rollbacks - consistent read gets 0 0.00 0.00
cleanouts only - consistent read gets 13 0.03 0.12
cluster key scan block gets 782 1.53 7.45
cluster key scans 122 0.24 1.16
commit batch performed 0 0.00 0.00
commit batch requested 0 0.00 0.00
commit batch/immediate performed 2 0.00 0.02
commit batch/immediate requested 2 0.00 0.02
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 15 0.03 0.14
commit cleanout failures: cannot pin 0 0.00 0.00
commit cleanouts 3,244 6.33 30.90
commit cleanouts successfully completed 3,229 6.30 30.75
commit immediate performed 2 0.00 0.02
commit immediate requested 2 0.00 0.02
commit txn count during cleanout 161 0.31 1.53
concurrency wait time 0 0.00 0.00
consistent changes 71 0.14 0.68
consistent gets 32,488,654 63,423.68 309,415.75
consistent gets - examination 37,870 73.93 360.67
consistent gets direct 0 0.00 0.00
consistent gets from cache 32,488,654 63,423.68 309,415.75
cursor authentications 7 0.01 0.07
data blocks consistent reads - undo records applied 71 0.14 0.68
db block changes 32,855 64.14 312.90
db block gets 32,506 63.46 309.58
db block gets direct 0 0.00 0.00
db block gets from cache 32,506 63.46 309.58
deferred (CURRENT) block cleanout applications 2,294 4.48 21.85
dirty buffers inspected 55 0.11 0.52
enqueue conversions 112 0.22 1.07
enqueue releases 15,744 30.74 149.94
enqueue requests 15,744 30.74 149.94
enqueue timeouts 0 0.00 0.00
enqueue waits 0 0.00 0.00
execute count 13,819 26.98 131.61
failed probes on index block reclamation 0 0.00 0.00
frame signature mismatch 0 0.00 0.00
free buffer inspected 8,421,158 16,439.61 80,201.50
free buffer requested 7,829,230 15,284.06 74,564.10
heap block compress 15 0.03 0.14
hot buffers moved to head of LRU 1,883 3.68 17.93
immediate (CR) block cleanout applications 13 0.03 0.12
immediate (CURRENT) block cleanout applications 375 0.73 3.57
index crx upgrade (positioned) 1,337 2.61 12.73
index fast full scans (full) 2 0.00 0.02
index fetch by key 18,480 36.08 176.00
index scans kdiixs1 2,525 4.93 24.05
leaf node 90-10 splits 27 0.05 0.26
leaf node splits 117 0.23 1.11
lob reads 0 0.00 0.00
lob writes 749 1.46 7.13
lob writes unaligned 749 1.46 7.13
logons cumulative 21 0.04 0.20
messages received 777 1.52 7.40
messages sent 777 1.52 7.40
no buffer to keep pinned count 0 0.00 0.00
no work - consistent read gets 32,378,160 63,207.98 308,363.43
opened cursors cumulative 11,885 23.20 113.19
parse count (failures) 0 0.00 0.00
parse count (hard) 10,027 19.57 95.50
parse count (total) 11,792 23.02 112.30
parse time cpu 1,415 2.76 13.48
parse time elapsed 1,454 2.84 13.85
physical read IO requests 722,224 1,409.91 6,878.32
physical read bytes 64,130,301,952 125,193,855.23 610,764,780.50
physical read total IO requests 725,043 1,415.41 6,905.17
physical read total bytes 64,176,324,608 125,283,699.71 611,203,091.50
physical read total multi block requests 502,214 980.41 4,782.99
physical reads 7,828,406 15,282.45 74,556.25
physical reads cache 7,828,406 15,282.45 74,556.25
physical reads cache prefetch 7,106,182 13,872.54 67,677.92
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 656 1.28 6.25
physical write bytes 13,565,952 26,483.17 129,199.54
physical write total IO requests 1,498 2.92 14.27
physical write total bytes 33,446,400 65,293.37 318,537.14
physical write total multi block requests 454 0.89 4.32
physical writes 1,656 3.23 15.77
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 1,656 3.23 15.77
physical writes non checkpoint 616 1.20 5.87
pinned buffers inspected 162 0.32 1.54
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 31,560 61.61 300.57
recursive cpu usage 1,678 3.28 15.98
redo blocks written 18,093 35.32 172.31
redo buffer allocation retries 0 0.00 0.00
redo entries 17,181 33.54 163.63
redo log space requests 0 0.00 0.00
redo log space wait time 0 0.00 0.00
redo ordering marks 477 0.93 4.54
redo size 8,857,840 17,292.09 84,360.38
redo subscn max counts 880 1.72 8.38
redo synch time 41 0.08 0.39
redo synch writes 497 0.97 4.73
redo wastage 51,788 101.10 493.22
redo write time 180 0.35 1.71
redo writer latching time 0 0.00 0.00
redo writes 172 0.34 1.64
rollback changes - undo records applied 2 0.00 0.02
rollbacks only - consistent read gets 71 0.14 0.68
rows fetched via callback 12,070 23.56 114.95
session connect time 0 0.00 0.00
session cursor cache hits 744 1.45 7.09
session logical reads 32,521,160 63,487.14 309,725.33
session pga memory 10,838,260 21,158.23 103,221.52
session pga memory max 14,704,884 28,706.57 140,046.51
session uga memory 38,655,043,092 75,461,579.34 368,143,267.54
session uga memory max 18,173,036 35,477.03 173,076.53
shared hash latch upgrades - no wait 2,165 4.23 20.62
sorts (disk) 0 0.00 0.00
sorts (memory) 1,010 1.97 9.62
sorts (rows) 30,852 60.23 293.83
sql area evicted 1,856 3.62 17.68
sql area purged 0 0.00 0.00
summed dirty queue length 55 0.11 0.52
switch current to new buffer 22 0.04 0.21
table fetch by rowid 13,748 26.84 130.93
table fetch continued row 127 0.25 1.21
table scan blocks gotten 32,361,396 63,175.25 308,203.77
table scan rows gotten 5,244,540,626 10,238,284.24 49,948,005.96
table scans (cache partitions) 0 0.00 0.00
table scans (long tables) 10,001 19.52 95.25
table scans (short tables) 295 0.58 2.81
total number of times SMON posted 1 0.00 0.01
transaction rollbacks 2 0.00 0.02
undo change vector size 2,723,508 5,316.78 25,938.17
user I/O wait time 10,682 20.85 101.73
user calls 31,074 60.66 295.94
user commits 103 0.20 0.98
user rollbacks 2 0.00 0.02
workarea executions - multipass 0 0.00 0.00
workarea executions - onepass 0 0.00 0.00
workarea executions - optimal 537 1.05 5.11
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 776,479 776,785
opened cursors current 242 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 720,999 1,408 0.15 10.86 0 0 0 0.00
SYSAUX 1,069 2 0.12 1.00 598 1 0 0.00
SYSTEM 168 0 0.65 3.77 10 0 0 0.00
UNDOTBS1 1 0 0.00 1.00 48 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 1,069 2 0.12 1.00 598 1 0 0.00
SYSTEM /opt/app/oracle/oradata/o102ee1/system01.dbf 168 0 0.65 3.77 10 0 0 0.00
UNDOTBS1 /opt/app/oracle/oradata/o102ee1/undotbs01.dbf 1 0 0.00 1.00 48 0 0 0.00
USERS /opt/app/oracle/oradata/o102ee1/users01.dbf 720,999 1,408 0.15 10.86 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 76 32,521,180 7,828,418 1,656 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 250 586 11371 184320 11371  
E 0 9 1145 10818 27452 184320 27452  

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 101,451,398
D 16 0.17 1,996 1.00 99,113,180
D 24 0.26 2,994 1.00 99,055,735
D 32 0.35 3,992 1.00 98,992,339
D 40 0.43 4,990 1.00 98,896,295
D 48 0.52 5,988 1.00 98,843,155
D 56 0.61 6,986 1.00 98,823,845
D 64 0.70 7,984 1.00 98,811,765
D 72 0.78 8,982 1.00 98,803,672
D 80 0.87 9,980 1.00 98,796,319
D 88 0.96 10,978 1.00 98,789,802
D 92 1.00 11,477 1.00 98,786,898
D 96 1.04 11,976 1.00 98,783,991
D 104 1.13 12,974 1.00 98,778,910
D 112 1.22 13,972 1.00 98,774,319
D 120 1.30 14,970 1.00 98,770,380
D 128 1.39 15,968 1.00 98,766,360
D 136 1.48 16,966 1.00 98,762,900
D 144 1.57 17,964 1.00 98,758,904
D 152 1.65 18,962 1.00 98,754,315
D 160 1.74 19,960 0.93 91,608,685

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 47 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.92 0.00 0.00 0.00 0.00 3,276
E 16 4 88.92 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 475 475 0 0
64K 128K 4 4 0 0
512K 1024K 58 58 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,761.02 1,448.07 95.00 301
16 1.00 27,761.02 47.84 100.00 301
19 1.20 27,761.02 47.84 100.00 301
22 1.40 27,761.02 47.84 100.00 301
26 1.60 27,761.02 47.84 100.00 300
29 1.80 27,761.02 47.84 100.00 295
32 2.00 27,761.02 46.39 100.00 271
48 3.00 27,761.02 8.37 100.00 103
64 4.00 27,761.02 0.00 100.00 4
96 6.00 27,761.02 0.00 100.00 0
128 8.00 27,761.02 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 16 3,011 467,932 0.83 100,999 28.16 22,291,086
100 0.68 31 6,200 495,060 0.88 73,871 20.60 22,323,193
116 0.78 46 8,649 520,912 0.92 48,019 13.39 22,356,007
132 0.89 61 11,811 544,494 0.96 24,437 6.81 22,390,958
148 1.00 76 14,706 565,345 1.00 3,586 1.00 22,428,054
164 1.11 91 17,225 583,470 1.03 1 0.00 22,464,773
180 1.22 106 20,390 599,042 1.06 1 0.00 22,494,035
196 1.32 121 23,143 612,218 1.08 1 0.00 22,518,728
212 1.43 136 25,807 623,107 1.10 1 0.00 22,540,942
228 1.54 151 27,956 631,849 1.12 1 0.00 22,560,256
244 1.65 167 30,545 638,669 1.13 1 0.00 22,577,902
260 1.76 182 32,568 643,839 1.14 1 0.00 22,593,553
276 1.86 197 34,131 647,598 1.15 1 0.00 22,609,154
292 1.97 212 35,413 650,137 1.15 1 0.00 22,625,986
308 2.08 227 36,943 651,698 1.15 1 0.00 22,644,852

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 132,128 98,858,528
192 0.75 43,120 98,858,528
256 1.00 43,086 98,543,190
320 1.25 43,082 98,503,773
384 1.50 42,310 91,369,246
448 1.75 42,310 91,369,246
512 2.00 42,310 91,369,246

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.34 416 0 4 15/15 0/0 0/0/0/0/0/0

Back to Undo Statistics
Back to Top

Undo Segment Stats

  • Most recent 35 Undostat rows, ordered by Time desc
End Time Num Undo Blocks Number of Transactions Max Qry Len (s) Max Tx Concy Tun Ret (mins) STO/ OOS uS/uR/uU/ eS/eR/eU
18-Apr 14:31 341 416 0 4 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 338 0.00   0 0  
AWR Alerted Metric Element list 1,963 0.00   0 0  
Consistent RBA 172 0.00   0 0  
FOB s.o list latch 67 0.00   0 0  
In memory undo latch 1,554 0.00   0 350 0.00
JS queue state obj latch 3,090 0.00   0 0  
KMG MMAN ready and startup request latch 172 0.00   0 0  
KMG resize request state object freelist 7 0.00   0 0  
KTF sga latch 1 0.00   0 141 0.00
MQL Tracking Latch 0     0 9 0.00
Memory Management Latch 81 0.00   0 172 0.00
OS process 36 0.00   0 0  
OS process allocation 195 0.00   0 0  
OS process: request allocation 24 0.00   0 0  
PL/SQL warning settings 107 0.00   0 0  
SGA IO buffer pool latch 1 0.00   0 1 0.00
SQL memory manager latch 160 0.00   0 168 0.00
SQL memory manager workarea list latch 22,296 0.00   0 0  
Shared B-Tree 20 0.00   0 0  
active checkpoint queue latch 795 0.00   0 0  
active service list 1,045 0.00   0 172 0.00
archive control 4 0.00   0 0  
buffer pool 8 0.00   0 0  
cache buffer handles 326 0.00   0 0  
cache buffers chains 72,907,781 0.00 0.00 0 33,464,391 0.00
cache buffers lru chain 4,372 3.52 0.00 0 58,707,142 0.00
cache table scan latch 502,214 0.00   0 502,214 0.00
channel handle pool latch 24 0.00   0 0  
channel operations parent latch 3,028 0.00   0 0  
checkpoint queue latch 12,006 0.00   0 3,202 0.00
client/application info 283 0.00   0 0  
compile environment latch 464 0.00   0 0  
dml lock allocation 3,008 0.00   0 0  
dummy allocation 42 0.00   0 0  
enqueue hash chains 31,594 0.01 0.00 0 0  
enqueues 28,004 0.00   0 0  
event group latch 12 0.00   0 0  
file cache latch 49 0.00   0 0  
global KZLD latch for mem in SGA 4 0.00   0 0  
hash table column usage latch 118 0.00   0 10,243 0.00
hash table modification latch 22 0.00   0 0  
job workq parent latch 0     0 18 0.00
job_queue_processes parameter latch 18 0.00   0 0  
kks stats 60,894 0.00   0 0  
ksuosstats global area 42 0.00   0 0  
ktm global data 4 0.00   0 0  
kwqbsn:qsga 20 0.00   0 0  
lgwr LWN SCN 283 0.00   0 0  
library cache 272,621 0.02 0.00 0 46,468 0.00
library cache load lock 98 0.00   0 0  
library cache lock 69,520 0.00   0 0  
library cache lock allocation 354 0.00   0 0  
library cache pin 68,006 0.00   0 0  
library cache pin allocation 74 0.00   0 0  
list of block allocation 261 0.00   0 0  
messages 4,981 0.00   0 0  
mostly latch-free SCN 283 0.00   0 0  
multiblock read objects 1,431,722 0.00 0.00 0 0  
ncodef allocation latch 8 0.00   0 0  
object queue header heap 1,594 0.00   0 50 0.00
object queue header operation 15,665,140 0.00 0.00 0 683 0.00
object stats modification 4 0.00   0 0  
parallel query alloc buffer 68 0.00   0 0  
parameter table allocation management 29 0.00   0 0  
post/wait queue 148 0.00   0 45 0.00
process allocation 24 0.00   0 12 0.00
process group creation 24 0.00   0 0  
qmn task queue latch 76 0.00   0 0  
redo allocation 1,455 0.14 0.00 0 17,199 0.06
redo copy 0     0 17,202 0.06
redo writing 1,686 0.00   0 0  
resmgr group change latch 77 0.00   0 0  
resmgr:actses active list 46 0.00   0 0  
resmgr:actses change group 21 0.00   0 0  
resmgr:free threads list 42 0.00   0 0  
resmgr:schema config 4 0.00   0 0  
row cache objects 223,255 0.00   0 404 0.00
sequence cache 89 0.00   0 0  
session allocation 19,091 0.00   0 0  
session idle bit 62,591 0.00   0 0  
session state list latch 54 0.00   0 0  
session switching 8 0.00   0 0  
session timer 172 0.00   0 0  
shared pool 343,447 0.00 0.17 0 0  
shared pool simulator 484,867 0.00   0 0  
simulator hash latch 8,685,807 0.00   0 0  
simulator lru latch 4,105,976 0.00 0.00 0 496,209 0.00
sort extent pool 13 0.00   0 0  
state object free list 8 0.00   0 0  
statistics aggregation 448 0.00   0 0  
threshold alerts latch 34 0.00   0 0  
transaction allocation 49 0.00   0 0  
transaction branch allocation 8 0.00   0 0  
undo global data 2,460 0.00   0 0  
user lock 34 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 343,447 12 2 10 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 kghalo 0 1 0
shared pool kghupr1 0 1 2

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,521,160
  • 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,488 99.72
SYS SYSAUX WRH$_SQL_PLAN_PK   INDEX 13,872 0.04
SYS SYSAUX WRH$_SQLSTAT_INDEX 18455_7846 INDEX PARTITION 10,336 0.03
SYS SYSAUX WRH$_SQL_BIND_METADATA_PK   INDEX 9,680 0.03
SYS SYSAUX WRH$_SQLSTAT_PK 18455_7846 INDEX PARTITION 8,992 0.03

Back to Segment Statistics
Back to Top

Segments by Physical Reads

  • Total Physical Reads: 7,828,406
  • 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,826,716 99.98
SYS SYSAUX WRH$_SQL_PLAN   TABLE 667 0.01
SYS SYSTEM TAB$   TABLE 533 0.01
SYS SYSAUX WRH$_SQLTEXT   TABLE 154 0.00
SYS SYSTEM IDL_UB1$   TABLE 49 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 26 0.00 0   8 1
dc_global_oids 777 0.26 0   0 25
dc_histogram_data 162 21.60 0   0 729
dc_histogram_defs 20,098 0.15 0   0 815
dc_object_grants 5 100.00 0   0 56
dc_object_ids 21,233 0.16 0   0 507
dc_objects 10,359 0.63 0   0 401
dc_profiles 13 0.00 0   0 1
dc_rollback_segments 66 0.00 0   0 12
dc_segments 20,017 0.03 0   9 179
dc_sequences 1 0.00 0   1 2
dc_tablespaces 461 0.00 0   0 5
dc_usernames 95 0.00 0   0 6
dc_users 1,154 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 114 0.00 900 0.33 3 0
CLUSTER 2 0.00 7 0.00 0 0
SQL AREA 10,470 95.62 34,828 86.34 11 0
TABLE/PROCEDURE 64 1.56 31,646 0.13 38 0
TRIGGER 8 0.00 35 0.00 0 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 78.21   3.26 6.77 26 26 24 24
  Freeable 9.13 0.00 0.76 0.37 1   12 12
  SQL 1.08 0.44 0.06 0.05 0 8 18 15
  PL/SQL 0.52 0.22 0.02 0.03 0 0 24 24
E Other 77.78   3.24 6.76 26 26 24 24
  Freeable 9.56 0.00 0.80 0.40 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

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 11.93 11.70 -1.88
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 KSFD SGA I/O b 3.79 3.79 0.00
shared KTI-UNDO 3.60 3.60 0.00
shared PCursor 10.10 11.40 12.91
shared PL/SQL MPCODE 2.05   -100.00
shared event statistics per sess 3.85 3.85 0.00
shared free memory 31.51 34.20 8.54
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.97 8.75 -2.41
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 17.19 15.98 -7.01
  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