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: 5486 12-1月 -07 14:00:01 28 14.6
End Snap: 5489 12-1月 -07 15:00:03 22 11.1
Elapsed:   60.04 (mins)    
DB Time:   37.71 (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,371.23 3,225.74
Logical reads: 14,187.82 19,300.64
Block changes: 12.70 17.27
Physical reads: 6,690.71 9,101.82
Physical writes: 0.98 1.33
User calls: 29.59 40.25
Parses: 13.39 18.21
Hard parses: 5.21 7.08
Sorts: 2.43 3.30
Logons: 0.03 0.04
Executes: 18.14 24.68
Transactions: 0.74  

% Blocks changed per Read: 0.09 Recursive Call %: 68.33
Rollback per transaction %: 1.21 Rows per Sort: 8.51

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 91.26 Redo NoWait %: 100.00
Buffer Hit %: 52.84 In-memory Sort %: 100.00
Library Hit %: 73.70 Soft Parse %: 61.12
Execute to Parse %: 26.20 Latch Hit %: 90.62
Parse CPU to Parse Elapsd %: 94.19 % Non-Parse CPU: 98.10

Shared Pool Statistics

  Begin End
Memory Usage %: 88.48 89.35
% SQL with executions>1: 92.20 20.69
% Memory for SQL w/exec>1: 82.33 21.82

Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time   1,569   69.3  
read by other session 4,468,414 743 0 32.8 User I/O
db file scattered read 807,846 341 0 15.1 User I/O
control file parallel write 1,219 50 41 2.2 System I/O
db file sequential read 1,002,908 25 0 1.1 User I/O

Main Report


Back to Top

Wait Events Statistics

Back to Top

Time Model Statistics

  • Total time in database user-calls (DB Time): 2262.8s
  • Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
  • Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
sql execute elapsed time 2,209.45 97.64
DB CPU 1,569.03 69.34
parse time elapsed 58.49 2.58
hard parse elapsed time 49.56 2.19
PL/SQL compilation elapsed time 26.25 1.16
PL/SQL execution elapsed time 3.25 0.14
hard parse (sharing criteria) elapsed time 0.71 0.03
connection management call elapsed time 0.50 0.02
repeated bind elapsed time 0.06 0.00
hard parse (bind mismatch) elapsed time 0.02 0.00
sequence load elapsed time 0.01 0.00
DB time 2,262.79  
background elapsed time 86.28  
background cpu time 9.66  

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 6,279,177 0.00 1,109 0 2,371.29
System I/O 7,114 0.00 64 9 2.69
Commit 2,351 0.00 10 4 0.89
Network 68,614 0.00 0 0 25.91
Concurrency 668 0.00 0 0 0.25
Application 304 0.00 0 0 0.11
Other 70 0.00 0 0 0.03

Back to Wait Events Statistics
Back to Top

Wait Events

  • s - second
  • cs - centisecond - 100th of a second
  • ms - millisecond - 1000th of a second
  • us - microsecond - 1000000th of a second
  • ordered by wait time desc, waits desc (idle events last)
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
read by other session 4,468,414 0.00 743 0 1,687.47
db file scattered read 807,846 0.00 341 0 305.08
control file parallel write 1,219 0.00 50 41 0.46
db file sequential read 1,002,908 0.00 25 0 378.74
log file parallel write 2,820 0.00 13 5 1.06
log file sync 2,351 0.00 10 4 0.89
SQL*Net message to client 68,511 0.00 0 0 25.87
os thread startup 4 0.00 0 41 0.00
latch: cache buffers chains 659 0.00 0 0 0.25
SQL*Net break/reset to client 304 0.00 0 0 0.11
control file sequential read 3,075 0.00 0 0 1.16
latch free 27 0.00 0 0 0.01
latch: shared pool 5 0.00 0 2 0.00
SQL*Net more data to client 58 0.00 0 0 0.02
SQL*Net more data from client 45 0.00 0 0 0.02
LGWR wait for redo copy 36 0.00 0 0 0.01
rdbms ipc reply 7 0.00 0 0 0.00
direct path write 9 0.00 0 0 0.00
SQL*Net message from client 68,517 0.00 22,212 324 25.88
Streams AQ: waiting for time management or cleanup tasks 15 46.67 4,184 278923 0.01
wait for unread message on broadcast channel 3,601 99.94 3,518 977 1.36
jobq slave wait 1,201 95.09 3,514 2926 0.45
Streams AQ: waiting for messages in the queue 721 99.72 3,513 4873 0.27
Streams AQ: qmn slave idle wait 128 0.00 3,482 27205 0.05
Streams AQ: qmn coordinator idle wait 260 50.77 3,482 13393 0.10
SGA: MMAN sleep for component shrink 19 78.95 0 10 0.01
class slave wait 3 0.00 0 0 0.00

Back to Wait Events Statistics
Back to Top

Background Wait Events

  • ordered by wait time desc, waits desc (idle events last)
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
control file parallel write 1,219 0.00 50 41 0.46
log file parallel write 2,820 0.00 13 5 1.06
db file sequential read 489 0.00 1 1 0.18
os thread startup 4 0.00 0 41 0.00
db file scattered read 46 0.00 0 1 0.02
control file sequential read 220 0.00 0 0 0.08
events in waitclass Other 43 0.00 0 0 0.02
latch: shared pool 1 0.00 0 0 0.00
direct path write 1 0.00 0 0 0.00
rdbms ipc message 14,094 81.56 31,389 2227 5.32
Streams AQ: waiting for time management or cleanup tasks 15 46.67 4,184 278923 0.01
pmon timer 1,210 100.00 3,516 2906 0.46
Streams AQ: qmn slave idle wait 128 0.00 3,482 27205 0.05
Streams AQ: qmn coordinator idle wait 260 50.77 3,482 13393 0.10
smon timer 14 78.57 3,402 243012 0.01
SGA: MMAN sleep for component shrink 19 78.95 0 10 0.01

Back to Wait Events Statistics
Back to Top

Operating System Statistics

Statistic Total
BUSY_TIME 184,637
IDLE_TIME 1,255,719
IOWAIT_TIME 9,703
NICE_TIME 1,127
SYS_TIME 55,665
USER_TIME 127,600
LOAD 0
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 21,512
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 2,253.50 1,561.40 24,099,683 51,016,586
o102ee1 9.30 7.60 396 50,896
SYS$BACKGROUND 0.00 0.00 1,503 37,618

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 6277805 110672 661 14 0 0 62552 38
o102ee1 387 71 2 1 0 0 5973 3
SYS$BACKGROUND 985 147 5 17 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
544 541 1,000 0.54 24.03 9a5wbppva8npu SQL*Plus SELECT ENAME FROM EMP WHERE EM...
7 7 100 0.07 0.30 8zt0p6avg76yz SQL*Plus SELECT COUNT(ename) FROM emp3 ...
3 3 60 0.06 0.15 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
3 3 10,000 0.00 0.12 5u3yjw57ppbsx SQL*Plus SELECT ENAME FROM EMP2 WHERE E...
2 2 2,157 0.00 0.10 cb75rw3w1tt0s OEM.SystemPool begin MGMT_JOB_ENGINE.get_sche...
2 1 2 0.85 0.07 8u809k64x3nzd Admin Connection begin DBMS_WORKLOAD_REPOSITORY...
1 1 1,522 0.00 0.05 db78fxqxwxt7r   select /*+ rule */ bucket, en...
1 1 35 0.03 0.05 b8b5jdj7khuaw Realtime Connection SELECT event#, sql_id, sql_p...
1 1 68 0.02 0.05 257rmrxgvaj4z Realtime Connection select begin_time, wait_class...
1 1 3 0.34 0.04 bunssq950snhf   insert into wrh$_sga_target_ad...

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
541 544 1,000 0.54 24.03 9a5wbppva8npu SQL*Plus SELECT ENAME FROM EMP WHERE EM...
7 7 100 0.07 0.30 8zt0p6avg76yz SQL*Plus SELECT COUNT(ename) FROM emp3 ...
3 3 60 0.05 0.15 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
3 3 10,000 0.00 0.12 5u3yjw57ppbsx SQL*Plus SELECT ENAME FROM EMP2 WHERE E...
2 2 2,157 0.00 0.10 cb75rw3w1tt0s OEM.SystemPool begin MGMT_JOB_ENGINE.get_sche...
1 2 2 0.74 0.07 8u809k64x3nzd Admin Connection begin DBMS_WORKLOAD_REPOSITORY...
1 1 35 0.03 0.05 b8b5jdj7khuaw Realtime Connection SELECT event#, sql_id, sql_p...
1 1 68 0.02 0.05 257rmrxgvaj4z Realtime Connection select begin_time, wait_class...
1 1 3 0.34 0.04 bunssq950snhf   insert into wrh$_sga_target_ad...
1 1 1,522 0.00 0.05 db78fxqxwxt7r   select /*+ rule */ bucket, en...

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: 51,108,085
  • Captured SQL account for 25.6% of Total
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
12,657,030 1,000 12,657.03 24.77 541.05 543.81 9a5wbppva8npu SQL*Plus SELECT ENAME FROM EMP WHERE EM...
155,746 100 1,557.46 0.30 6.66 6.89 8zt0p6avg76yz SQL*Plus SELECT COUNT(ename) FROM emp3 ...
40,049 10,000 4.00 0.08 2.71 2.77 5u3yjw57ppbsx SQL*Plus SELECT ENAME FROM EMP2 WHERE E...
24,086 60 401.43 0.05 2.73 3.47 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
14,735 1,000 14.74 0.03 0.45 0.49 90p005ah4wm47 SQL*Plus select distinct emp1.deptno fr...
14,018 120 116.82 0.03 0.85 0.86 8hk7xvhua40va OEM.Loader INSERT INTO MGMT_METRICS_RAW(C...
11,461 2 5,730.50 0.02 1.48 1.69 8u809k64x3nzd Admin Connection begin DBMS_WORKLOAD_REPOSITORY...
10,804 1,715 6.30 0.02 0.40 0.40 91h2x42zqagcm OEM.Loader UPDATE MGMT_CURRENT_METRICS SE...
10,008 6 1,668.00 0.02 0.11 0.11 g6vss60nrfakg Admin Connection SELECT /*+ NO_MERGE(t) USE_NL(...
9,090 2 4,545.00 0.02 0.07 0.16 fdqtcb7dbv3vr Admin Connection SELECT task_list.task_id FROM ...

Back to SQL Statistics
Back to Top

SQL ordered by Reads

  • Total Disk Reads: 24,101,618
  • Captured SQL account for 52.3% of Total
Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
12,587,733 1,000 12,587.73 52.23 541.05 543.81 9a5wbppva8npu SQL*Plus SELECT ENAME FROM EMP WHERE EM...
3,103 100 31.03 0.01 6.66 6.89 8zt0p6avg76yz SQL*Plus SELECT COUNT(ename) FROM emp3 ...
583 1 583.00 0.00 0.03 0.04 cfz686a6qp0kg   select o.obj#, u.name, o.nam...
383 1,522 0.25 0.00 0.94 1.15 db78fxqxwxt7r   select /*+ rule */ bucket, en...
282 137 2.06 0.00 0.31 0.40 cvn54b7yz0s8u   select /*+ index(idl_ub1$ i_id...
254 2 127.00 0.00 0.07 0.16 fdqtcb7dbv3vr Admin Connection SELECT task_list.task_id FROM ...
176 60 2.93 0.00 2.73 3.47 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
171 137 1.25 0.00 0.25 0.29 39m4sx9k63ba2   select /*+ index(idl_ub2$ i_id...
121 28 4.32 0.00 0.03 0.10 49s332uhbnsma   declare vsn va...
115 2 57.50 0.00 1.48 1.69 8u809k64x3nzd Admin Connection begin DBMS_WORKLOAD_REPOSITORY...

Back to SQL Statistics
Back to Top

SQL ordered by Executions

  • Total Executions: 65,353
  • Captured SQL account for 69.1% of Total
Executions Rows Processed Rows per Exec CPU per Exec (s) Elap per Exec (s) SQL Id SQL Module SQL Text
10,000 9,999 1.00 0.00 0.00 5u3yjw57ppbsx SQL*Plus SELECT ENAME FROM EMP2 WHERE E...
2,580 1,894 0.73 0.00 0.00 96g93hntrzjtr   select /*+ rule */ bucket_cnt,...
2,217 2,217 1.00 0.00 0.00 089dbukv1aanh EM_PING SELECT SYS_EXTRACT_UTC(SYSTIME...
2,157 0 0.00 0.00 0.00 6gh8gj9n09vr7 OEM.SystemPool SELECT JOB_ID, EXECUTION_ID, ...
2,157 2,157 1.00 0.00 0.00 cb75rw3w1tt0s OEM.SystemPool begin MGMT_JOB_ENGINE.get_sche...
1,715 1,715 1.00 0.00 0.00 91h2x42zqagcm OEM.Loader UPDATE MGMT_CURRENT_METRICS SE...
1,522 23,886 15.69 0.00 0.00 db78fxqxwxt7r   select /*+ rule */ bucket, en...
1,183 1,838 1.55 0.00 0.00 0h6b2sajwb74n   select privilege#, level from ...
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.00 0.00 90p005ah4wm47 SQL*Plus select distinct emp1.deptno fr...
1,000 1,000 1.00 0.54 0.54 9a5wbppva8npu SQL*Plus SELECT ENAME FROM EMP WHERE EM...
1,000 1,000 1.00 0.00 0.00 d9qr16p9z7xy2 SQL*Plus update emp4 set ename = 'aaa' ...
822 674 0.82 0.00 0.00 3c1kubcdjnppq   update sys.col_usage$ set eq...
720 60 0.08 0.00 0.00 6ssrk2dqj7jbx   select job, nvl2(last_date, ...
660 660 1.00 0.00 0.00 g2wr3u7s1gtf3   select count(*) from sys.job$ ...

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

  • Total Parse Calls: 48,233
  • Captured SQL account for 52.2% of Total
Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
10,000 10,000 20.73 5u3yjw57ppbsx SQL*Plus SELECT ENAME FROM EMP2 WHERE E...
1,183 1,183 2.45 0h6b2sajwb74n   select privilege#, level from ...
1,000 1,000 2.07 6kpzvzuv00t58 SQL*Plus update EMP5 set ename = 'aaa' ...
1,000 1,000 2.07 90p005ah4wm47 SQL*Plus select distinct emp1.deptno fr...
1,000 1,000 2.07 9a5wbppva8npu SQL*Plus SELECT ENAME FROM EMP WHERE EM...
1,000 1,000 2.07 d9qr16p9z7xy2 SQL*Plus update emp4 set ename = 'aaa' ...
485 485 1.01 2ym6hhaq30r73   select type#, blocks, extents,...
309 822 0.64 3c1kubcdjnppq   update sys.col_usage$ set eq...
309 148 0.64 53btfq0dt9bs9   insert into sys.col_usage$ val...
309 309 0.64 b2gnxm5z6r51n   lock table sys.col_usage$ in e...

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
85 167 18naypzfmabd6   INSERT INTO MGMT_SYSTEM_PERFOR...

Back to SQL Statistics
Back to Top

 

Complete List of SQL Text

SQL Id SQL Text
089dbukv1aanh SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL
0h6b2sajwb74n select privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
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#
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
49s332uhbnsma declare vsn varchar2(20); begin vsn := dbms_rcvman.getPackageVersion; :pkg_vsn:pkg_vsn_i := vsn; if vsn is not null then :pkg_vsnub4 := to_number(substr(vsn, 1, 2) || substr(vsn, 4, 2) || substr(vsn, 7, 2) || nvl(substr(vsn, 10, 2), '00')); end if; end;
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)
5u3yjw57ppbsx SELECT ENAME FROM EMP2 WHERE EMP2NO = :num
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;
6kpzvzuv00t58 update EMP5 set ename = 'aaa' where EMP5no = 100
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)
8u809k64x3nzd begin DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); end;
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
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
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
9a5wbppva8npu SELECT ENAME FROM EMP WHERE EMPNO = :num
b2gnxm5z6r51n lock table sys.col_usage$ in exclusive mode nowait
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
cb75rw3w1tt0s begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;
cfz686a6qp0kg select o.obj#, u.name, o.name, t.spare1, DECODE(bitand(t.flags, 268435456), 268435456, t.initrans, t.pctfree$) from sys.obj$ o, sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) = 1048576) and o.obj#=t.obj# and o.owner# = u.user#
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
fdqtcb7dbv3vr SELECT task_list.task_id FROM (SELECT t.task_id, t.created FROM dba_advisor_tasks t, dba_advisor_parameters p, dba_advisor_log l WHERE t.advisor_name='ADDM' AND t.task_id = p.task_id AND t.task_id = l.task_id AND t.owner = p.owner AND t.owner = l.owner AND l.status = 'COMPLETED' AND p.parameter_name = 'INSTANCE' AND p.parameter_value = sys_context('USERENV', 'INSTANCE') ORDER BY t.created DESC) task_list where rownum = 1
g2wr3u7s1gtf3 select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysdate+5/86400))
g6vss60nrfakg SELECT /*+ NO_MERGE(t) USE_NL(t) USE_NL(p) ORDERED */ t.task_id, t.task_name, to_timestamp(p.parameter_value, 'MM-DD-YYYY HH24:MI:SS'), f.num_findings FROM (SELECT task_id, task_name FROM dba_advisor_tasks WHERE advisor_id = 1 ) t, dba_advisor_parameters_proj p, dba_advisor_parameters_proj p1, dba_advisor_parameters_proj p2, (SELECT task_id, COUNT(finding_id) num_findings FROM dba_advisor_findings WHERE type<>'INFORMATION' AND type<>'WARNING' GROUP BY task_id) f WHERE p.task_id = t.task_id AND p.parameter_name = 'END_TIME' AND to_timestamp(decode(p.parameter_value, 'UNUSED', null, p.parameter_value), 'MM-DD-YYYY HH24:MI:SS') >= :1 AND to_timestamp(decode(p.parameter_value, 'UNUSED', null, p.parameter_value), 'MM-DD-YYYY HH24:MI:SS') <= :2 AND p1.task_id = t.task_id AND p1.parameter_name = 'INSTANCE' AND p1.parameter_value = to_char(:3) AND p2.task_id = t.task_id AND p2.parameter_name = 'DB_ID' AND p2.parameter_value = to_char(:4) AND t.task_id=f.task_id(+)

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 158,065 43.88 59.69
CPU used when call started 157,389 43.69 59.44
CR blocks created 584 0.16 0.22
Cached Commit SCN referenced 154,800 42.97 58.46
Commit SCN cached 1 0.00 0.00
DB time 1,309,363 363.48 494.47
DBWR checkpoint buffers written 3,471 0.96 1.31
DBWR checkpoints 0 0.00 0.00
DBWR object drop buffers written 0 0.00 0.00
DBWR transaction table writes 103 0.03 0.04
DBWR undo block writes 548 0.15 0.21
IMU CR rollbacks 248 0.07 0.09
IMU Flushes 918 0.25 0.35
IMU Redo allocation size 786,180 218.25 296.90
IMU commits 2,574 0.71 0.97
IMU contention 2 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 4,107,720 1,140.32 1,551.25
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 2 0.00 0.00
SQL*Net roundtrips to/from client 68,435 19.00 25.84
active txn count during cleanout 175 0.05 0.07
application wait time 8 0.00 0.00
background checkpoints completed 0 0.00 0.00
background checkpoints started 0 0.00 0.00
background timeouts 11,506 3.19 4.35
branch node splits 0 0.00 0.00
buffer is not pinned count 128,267 35.61 48.44
buffer is pinned count 47,688 13.24 18.01
bytes received via SQL*Net from client 9,755,230 2,708.09 3,684.00
bytes sent via SQL*Net to client 13,478,587 3,741.71 5,090.10
calls to get snapshot scn: kcmgss 115,700 32.12 43.69
calls to kcmgas 4,433 1.23 1.67
calls to kcmgcs 148 0.04 0.06
change write time 52 0.01 0.02
cleanout - number of ktugct calls 223 0.06 0.08
cleanouts and rollbacks - consistent read gets 4 0.00 0.00
cleanouts only - consistent read gets 11 0.00 0.00
cluster key scan block gets 7,307 2.03 2.76
cluster key scans 3,144 0.87 1.19
commit batch performed 0 0.00 0.00
commit batch requested 0 0.00 0.00
commit batch/immediate performed 34 0.01 0.01
commit batch/immediate requested 34 0.01 0.01
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 10 0.00 0.00
commit cleanout failures: cannot pin 0 0.00 0.00
commit cleanouts 7,239 2.01 2.73
commit cleanouts successfully completed 7,229 2.01 2.73
commit immediate performed 34 0.01 0.01
commit immediate requested 34 0.01 0.01
commit txn count during cleanout 105 0.03 0.04
concurrency wait time 30 0.01 0.01
consistent changes 599 0.17 0.23
consistent gets 51,070,660 14,177.43 19,286.50
consistent gets - examination 119,393 33.14 45.09
consistent gets direct 0 0.00 0.00
consistent gets from cache 51,070,660 14,177.43 19,286.50
cursor authentications 424 0.12 0.16
data blocks consistent reads - undo records applied 584 0.16 0.22
db block changes 45,744 12.70 17.27
db block gets 37,425 10.39 14.13
db block gets direct 14 0.00 0.01
db block gets from cache 37,411 10.39 14.13
deferred (CURRENT) block cleanout applications 5,078 1.41 1.92
dirty buffers inspected 2 0.00 0.00
enqueue conversions 731 0.20 0.28
enqueue releases 57,167 15.87 21.59
enqueue requests 57,167 15.87 21.59
enqueue timeouts 0 0.00 0.00
enqueue waits 0 0.00 0.00
execute count 65,353 18.14 24.68
failed probes on index block reclamation 0 0.00 0.00
free buffer inspected 24,127,314 6,697.84 9,111.52
free buffer requested 24,102,713 6,691.01 9,102.23
heap block compress 37 0.01 0.01
hot buffers moved to head of LRU 2,740 0.76 1.03
immediate (CR) block cleanout applications 15 0.00 0.01
immediate (CURRENT) block cleanout applications 1,083 0.30 0.41
index crx upgrade (found) 0 0.00 0.00
index crx upgrade (positioned) 10,049 2.79 3.79
index fast full scans (full) 47 0.01 0.02
index fetch by key 51,267 14.23 19.36
index scans kdiixs1 31,610 8.78 11.94
leaf node 90-10 splits 20 0.01 0.01
leaf node splits 60 0.02 0.02
lob reads 182 0.05 0.07
lob writes 134 0.04 0.05
lob writes unaligned 134 0.04 0.05
logons cumulative 95 0.03 0.04
messages received 4,246 1.18 1.60
messages sent 4,246 1.18 1.60
no buffer to keep pinned count 0 0.00 0.00
no work - consistent read gets 50,890,611 14,127.44 19,218.51
opened cursors cumulative 53,396 14.82 20.16
parse count (failures) 0 0.00 0.00
parse count (hard) 18,753 5.21 7.08
parse count (total) 48,233 13.39 18.21
parse time cpu 2,982 0.83 1.13
parse time elapsed 3,166 0.88 1.20
physical read IO requests 1,810,762 502.67 683.82
physical read bytes ############### 54,810,283.86 74,562,105.23
physical read total IO requests 1,813,869 503.54 685.00
physical read total bytes ############### 54,824,334.02 74,581,218.61
physical read total multi block requests 807,846 224.26 305.08
physical reads 24,101,618 6,690.71 9,101.82
physical reads cache 24,101,618 6,690.71 9,101.82
physical reads cache prefetch 22,290,856 6,188.03 8,418.00
physical reads direct 0 0.00 0.00
physical reads direct (lob) 0 0.00 0.00
physical reads direct temporary tablespace 0 0.00 0.00
physical reads prefetch warmup 0 0.00 0.00
physical write IO requests 1,563 0.43 0.59
physical write bytes 28,844,032 8,007.22 10,892.76
physical write total IO requests 8,046 2.23 3.04
physical write total bytes 98,872,320 27,447.36 37,338.49
physical write total multi block requests 3,221 0.89 1.22
physical writes 3,521 0.98 1.33
physical writes direct 14 0.00 0.01
physical writes direct (lob) 8 0.00 0.00
physical writes direct temporary tablespace 0 0.00 0.00
physical writes from cache 3,507 0.97 1.32
physical writes non checkpoint 807 0.22 0.30
pinned buffers inspected 24,999 6.94 9.44
prefetch warmup blocks aged out before use 0 0.00 0.00
prefetched blocks aged out before use 0 0.00 0.00
recursive calls 229,924 63.83 86.83
recursive cpu usage 6,302 1.75 2.38
redo blocks written 19,654 5.46 7.42
redo buffer allocation retries 0 0.00 0.00
redo entries 18,978 5.27 7.17
redo log space requests 0 0.00 0.00
redo log space wait time 0 0.00 0.00
redo ordering marks 366 0.10 0.14
redo size 8,541,772 2,371.23 3,225.74
redo subscn max counts 1,138 0.32 0.43
redo synch time 1,014 0.28 0.38
redo synch writes 5,727 1.59 2.16
redo wastage 1,156,788 321.13 436.85
redo write time 1,348 0.37 0.51
redo writer latching time 1 0.00 0.00
redo writes 2,821 0.78 1.07
rollback changes - undo records applied 56 0.02 0.02
rollbacks only - consistent read gets 580 0.16 0.22
rows fetched via callback 22,003 6.11 8.31
session connect time 0 0.00 0.00
session cursor cache hits 29,491 8.19 11.14
session logical reads 51,108,085 14,187.82 19,300.64
session pga memory 72,944,964 20,249.82 27,547.19
session pga memory max 183,438,660 50,923.33 69,274.42
session uga memory ############### 71,537,387.32 97,317,105.94
session uga memory max 74,697,244 20,736.26 28,208.93
shared hash latch upgrades - no wait 11,807 3.28 4.46
shared hash latch upgrades - wait 0 0.00 0.00
sorts (disk) 0 0.00 0.00
sorts (memory) 8,743 2.43 3.30
sorts (rows) 74,366 20.64 28.08
sql area evicted 2,251 0.62 0.85
sql area purged 0 0.00 0.00
summed dirty queue length 2 0.00 0.00
switch current to new buffer 155 0.04 0.06
table fetch by rowid 60,683 16.85 22.92
table fetch continued row 285 0.08 0.11
table scan blocks gotten 50,752,718 14,089.16 19,166.43
table scan rows gotten 8,416,185,963 2,336,367.91 3,178,317.96
table scans (long tables) 4,002 1.11 1.51
table scans (short tables) 2,219 0.62 0.84
total number of times SMON posted 3 0.00 0.00
transaction rollbacks 34 0.01 0.01
undo change vector size 2,894,020 803.39 1,092.91
user I/O wait time 110,893 30.78 41.88
user calls 106,573 29.59 40.25
user commits 2,616 0.73 0.99
user rollbacks 32 0.01 0.01
workarea executions - onepass 0 0.00 0.00
workarea executions - optimal 4,780 1.33 1.81
write clones created in background 0 0.00 0.00
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 453,199 454,776
opened cursors current 410 245
logons current 28 22

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

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

Back to Instance Activity Statistics
Back to Top

 

IO Stats

Back to Top

Tablespace IO Stats

  • ordered by IOs (Reads + Writes) desc
Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
USERS 1,808,336 502 0.20 13.33 3 0 4,468,162 0.18
SYSAUX 1,007 0 1.30 1.04 1,233 0 0 0.00
SYSTEM 1,363 0 1.83 1.40 91 0 0 0.00
UNDOTBS1 10 0 1.00 1.00 235 0 0 0.00
TEMP 0 0 0.00   1 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,007 0 1.30 1.04 1,233 0 0 0.00
SYSTEM /opt/app/oracle/oradata/o102ee1/system01.dbf 1,363 0 1.83 1.40 91 0 0 0.00
TEMP /opt/app/oracle/oradata/o102ee1/temp01.dbf 0 0     1 0 0  
UNDOTBS1 /opt/app/oracle/oradata/o102ee1/undotbs01.dbf 10 0 1.00 1.00 235 0 0 0.00
USERS /opt/app/oracle/oradata/o102ee1/users01.dbf 1,808,336 502 0.20 13.33 3 0 4,468,162 0.18

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,466 53 51,042,194 24,101,570 3,507 0 0 4,468,409


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 322 791 2663 184320 2663  
E 0 8 283 628 6753 184320 6753  

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.06 41,242,116
D 16 0.17 1,996 1.04 40,385,894
D 24 0.26 2,994 1.03 39,999,775
D 32 0.35 3,992 1.02 39,622,202
D 40 0.43 4,990 1.01 39,234,374
D 48 0.52 5,988 1.00 39,112,442
D 56 0.61 6,986 1.00 39,075,844
D 64 0.70 7,984 1.00 39,044,138
D 72 0.78 8,982 1.00 39,006,636
D 80 0.87 9,980 1.00 38,983,785
D 88 0.96 10,978 1.00 38,960,897
D 92 1.00 11,477 1.00 38,952,463
D 96 1.04 11,976 1.00 38,944,016
D 104 1.13 12,974 1.00 38,933,179
D 112 1.22 13,972 1.00 38,919,642
D 120 1.30 14,970 1.00 38,909,623
D 128 1.39 15,968 1.00 38,899,690
D 136 1.48 16,966 1.00 38,888,741
D 144 1.57 17,964 1.00 38,880,196
D 152 1.65 18,962 1.00 38,870,845
D 160 1.74 19,960 0.82 32,054,944

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 149 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 67.99 0.00 0.00 0.00 0.00 512
E 16 4 60.63 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 4,556 4,556 0 0
64K 128K 7 7 0 0
256K 512K 2 2 0 0
512K 1024K 188 188 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 13,946.62 426.07 97.00 94
16 1.00 13,946.62 7.57 100.00 94
19 1.20 13,946.62 7.57 100.00 94
22 1.40 13,946.62 7.57 100.00 94
26 1.60 13,946.62 7.57 100.00 93
29 1.80 13,946.62 7.57 100.00 92
32 2.00 13,946.62 7.57 100.00 80
48 3.00 13,946.62 0.00 100.00 2
64 4.00 13,946.62 0.00 100.00 0
96 6.00 13,946.62 0.00 100.00 0
128 8.00 13,946.62 0.00 100.00 0

Back to Advisory Statistics
Back to Top

Shared Pool Advisory

  • SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
  • Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.
Shared Pool Size(M) SP Size Factr Est LC Size (M) Est LC Mem Obj Est LC Time Saved (s) Est LC Time Saved Factr Est LC Load Time (s) Est LC Load Time Factr Est LC Mem Obj Hits
84 0.57 17 3,305 298,880 0.78 83,182 219.48 12,374,380
100 0.68 32 4,904 326,855 0.86 55,207 145.66 12,387,430
116 0.78 47 6,512 352,104 0.92 29,958 79.04 12,399,277
132 0.89 62 8,155 370,448 0.97 11,614 30.64 12,408,278
148 1.00 77 9,725 381,683 1.00 379 1.00 12,414,278
164 1.11 92 11,324 389,363 1.02 1 0.00 12,418,075
180 1.22 107 12,799 396,448 1.04 1 0.00 12,420,623
196 1.32 122 14,460 402,795 1.06 1 0.00 12,422,276
212 1.43 137 16,342 406,602 1.07 1 0.00 12,423,338
228 1.54 152 17,654 407,924 1.07 1 0.00 12,423,986
244 1.65 167 19,748 408,164 1.07 1 0.00 12,424,368
260 1.76 182 21,775 408,182 1.07 1 0.00 12,424,617
276 1.86 197 23,555 408,182 1.07 1 0.00 12,424,774
292 1.97 212 25,641 408,182 1.07 1 0.00 12,424,899
308 2.08 227 27,582 408,182 1.07 1 0.00 12,424,993

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 66,467 41,184,275
192 0.75 8,725 41,184,275
256 1.00 8,468 38,952,308
320 1.25 8,454 38,827,661
384 1.50 8,211 32,011,007
448 1.75 8,211 32,011,007
512 2.00 8,211 32,011,007

Back to Advisory Statistics
Back to Top

 

Streams Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

 

Java Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

 

Wait Statistics

Back to Top

Buffer Wait Statistics

  • ordered by wait time desc, waits desc
Class Waits Total Wait Time (s) Avg Time (ms)
data block 4,467,916 786 0

Back to Wait Statistics
Back to Top

 

Enqueue Activity

No data exists for this section of the report.

Back to Wait Statistics
Back to Top

 

Undo Statistics

Back to Top

Undo Segment Summary

  • Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
  • STO - Snapshot Too Old count, OOS - Out of Space count
  • Undo segment block stats:
  • uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
  • eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo TS# Num Undo Blocks (K) Number of Transactions Max Qry Len (s) Max Tx Concurcy Min/Max TR (mins) STO/ OOS uS/uR/uU/ eS/eR/eU
1 0.71 3,525 88 3 15/16.4666666666666666666666666666666666667 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
12-1譛・ 15:04 251 238 0 3 15 0/0 0/0/0/0/0/0
12-1譛・ 14:54 31 312 0 2 15 0/0 0/0/0/0/0/0
12-1譛・ 14:44 20 110 0 1 15 0/0 0/0/0/0/0/0
12-1譛・ 14:34 59 2,241 0 2 15 0/0 0/0/0/0/0/0
12-1譛・ 14:24 17 87 88 1 16 0/0 0/0/0/0/0/0
12-1譛・ 14:14 134 334 0 3 15 0/0 0/0/0/0/0/0
12-1譛・ 14:04 195 203 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 2,375 0.00   0 0  
AWR Alerted Metric Element list 13,058 0.00   0 0  
Consistent RBA 2,821 0.00   0 0  
FIB s.o chain latch 8 0.00   0 0  
FOB s.o list latch 144 0.00   0 0  
In memory undo latch 19,859 0.00   0 4,367 0.00
JS queue state obj latch 21,630 0.00   0 0  
JS slv state obj latch 3 0.00   0 0  
KMG MMAN ready and startup request latch 1,219 0.00   0 0  
KMG resize request state object freelist 16 0.00   0 0  
KTF sga latch 7 0.00   0 1,019 0.00
KWQMN job cache list latch 32 0.00   0 0  
MQL Tracking Latch 0     0 72 0.00
Memory Management Latch 179 0.56 0.00 0 1,219 0.00
OS process 144 0.00   0 0  
OS process allocation 1,291 0.00   0 0  
OS process: request allocation 74 0.00   0 0  
PL/SQL warning settings 10,786 0.00   0 0  
SGA IO buffer pool latch 9 0.00   0 9 0.00
SQL memory manager latch 1,297 0.00   0 1,184 0.00
SQL memory manager workarea list latch 168,883 0.00   0 0  
Shared B-Tree 134 0.00   0 0  
active checkpoint queue latch 2,742 0.00   0 0  
active service list 6,940 0.00   0 1,210 0.00
archive control 71 0.00   0 0  
begin backup scn array 9 0.00   0 0  
buffer pool 49 0.00   0 0  
cache buffer handles 402 0.00   0 0  
cache buffers chains 130,790,194 13.89 0.00 0 91,363,763 0.00
cache buffers lru chain 12,256 3.57 0.00 0 115,301,357 4.04
cache table scan latch 807,832 0.00 0.00 0 807,832 0.00
channel handle pool latch 77 0.00   0 0  
channel operations parent latch 21,198 0.00   0 0  
checkpoint queue latch 60,259 0.00   0 5,202 0.00
client/application info 1,977 0.00   0 0  
commit callback allocation 8 0.00   0 0  
compile environment latch 5,392 0.00   0 0  
dml lock allocation 19,750 0.00   0 0  
dummy allocation 194 0.00   0 0  
enqueue hash chains 114,996 0.00   0 0  
enqueues 93,226 0.00 0.00 0 0  
event group latch 34 0.00   0 0  
file cache latch 147 0.00   0 0  
global KZLD latch for mem in SGA 28 0.00   0 0  
hash table column usage latch 388 0.00   0 32,819 0.00
hash table modification latch 62 0.00   0 0  
job workq parent latch 0     0 120 0.00
job_queue_processes parameter latch 120 0.00   0 0  
kks stats 119,931 0.00 0.00 0 0  
ksuosstats global area 248 0.00   0 0  
ktm global data 18 0.00   0 0  
kwqbsn:qsga 134 0.00   0 0  
lgwr LWN SCN 3,636 0.00   0 0  
library cache 604,061 0.01 0.00 0 60,190 0.01
library cache load lock 2,664 0.00   0 0  
library cache lock 216,698 0.00 0.00 0 0  
library cache lock allocation 2,439 0.00   0 0  
library cache pin 211,216 0.00 0.00 0 0  
library cache pin allocation 719 0.00   0 0  
list of block allocation 355 0.00   0 0  
loader state object freelist 30 0.00   0 0  
message pool operations parent latch 6 0.00   0 0  
messages 34,262 0.00 0.00 0 0  
mostly latch-free SCN 3,638 0.03 0.00 0 0  
multiblock read objects 3,601,898 0.00 0.00 0 0  
ncodef allocation latch 58 0.00   0 0  
object queue header heap 5,640 0.00   0 60 0.00
object queue header operation 48,218,550 0.00 0.00 0 1,979 0.00
object stats modification 4 0.00   0 0  
parallel query alloc buffer 476 0.00   0 0  
parameter list 35 0.00   0 0  
parameter table allocation management 118 0.00   0 0  
post/wait queue 6,055 0.00   0 2,352 0.00
process allocation 74 0.00   0 34 0.00
process group creation 74 0.00   0 0  
qmn task queue latch 512 0.00   0 0  
redo allocation 18,797 0.00   0 19,001 0.00
redo copy 0     0 19,001 0.19
redo writing 13,721 0.00   0 0  
resmgr group change latch 508 0.00   0 0  
resmgr:actses active list 231 0.00   0 0  
resmgr:actses change group 168 0.00   0 0  
resmgr:free threads list 188 0.00   0 0  
resmgr:schema config 4 0.00   0 0  
row cache objects 448,876 0.01 0.00 0 4,508 0.00
rules engine aggregate statistics 2 0.00   0 0  
rules engine rule set statistics 4 0.00   0 0  
sequence cache 249 0.00   0 0  
session allocation 225,255 0.00 0.00 0 0  
session idle bit 218,432 0.32 0.00 0 0  
session state list latch 183 0.00   0 0  
session switching 58 0.00   0 0  
session timer 1,210 0.00   0 0  
shared pool 752,855 0.06 0.01 0 0  
shared pool sim alloc 544 0.00   0 0  
shared pool simulator 751,296 0.00   0 0  
simulator hash latch 11,182,345 0.00 0.00 0 0  
simulator lru latch 6,341,903 16.98 0.00 0 1,503,455 0.00
slave class 3 0.00   0 0  
slave class create 12 0.00   0 0  
sort extent pool 96 0.00   0 0  
state object free list 6 0.00   0 0  
statistics aggregation 336 0.00   0 0  
temp lob duration state obj allocation 2 0.00   0 0  
threshold alerts latch 217 0.00   0 0  
transaction allocation 154 0.00   0 0  
transaction branch allocation 58 0.00   0 0  
undo global data 17,626 0.00   0 0  
user lock 240 0.00   0 0  

Back to Latch Statistics
Back to Top

Latch Sleep Breakdown

  • ordered by misses desc
Latch Name Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3
cache buffers chains 130,790,194 18,163,319 659 18,162,710 0 0 0
simulator lru latch 6,341,903 1,077,163 26 1,077,139 0 0 0
session idle bit 218,432 707 1 706 0 0 0
shared pool 752,855 469 5 464 0 0 0

Back to Latch Statistics
Back to Top

Latch Miss Sources

  • only latches with sleeps are shown
  • ordered by name, sleeps desc
Latch Name Where NoWait Misses Sleeps Waiter Sleeps
cache buffers chains kcbgtcr: kslbegin excl 0 1,221 1,278
cache buffers chains kcbrls: kslbegin 0 460 353
cache buffers chains kcbzwb 0 54 107
cache buffers chains kcbzib: finish free bufs 0 17 19
cache buffers chains kcbzib: multi-block read: nowait 0 6 0
session idle bit ksupuc: clear busy 0 1 1
shared pool kghalo 0 3 3
shared pool kghupr1 0 2 0
simulator lru latch kcbs_simulate: simulate set 0 26 26

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: 51,108,085
  • Captured Segments account for 99.9% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Logical Reads %Total
SCOTT USERS EMP   TABLE 50,629,632 99.06
SCOTT USERS EMP3   TABLE 155,888 0.31
SYS SYSAUX SYS_IOT_TOP_8813   INDEX 52,272 0.10
SCOTT USERS PK_EMP2   INDEX 30,432 0.06
SCOTT USERS IND_EMP1_EMP1NO   INDEX 26,976 0.05

Back to Segment Statistics
Back to Top

Segments by Physical Reads

  • Total Physical Reads: 24,101,618
  • Captured Segments account for 100.0% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
SCOTT USERS EMP   TABLE 24,095,340 99.97
SCOTT USERS EMP3   TABLE 3,100 0.01
SYS SYSTEM TAB$   TABLE 583 0.00
SYS SYSAUX WRI$_ADV_PARAMETERS_PK   INDEX 256 0.00
SYS SYSAUX WRH$_SQL_PLAN   TABLE 159 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 74 4.05 0   6 1
dc_global_oids 5,210 0.56 0   0 22
dc_histogram_data 5,442 22.86 0   0 988
dc_histogram_defs 22,418 9.41 0   0 1,758
dc_object_grants 202 37.13 0   0 59
dc_object_ids 58,725 0.51 0   0 492
dc_objects 10,558 4.73 0   0 557
dc_profiles 88 0.00 0   0 1
dc_rollback_segments 337 0.00 0   0 12
dc_segments 25,291 1.37 0   9 372
dc_sequences 6 33.33 0   6 1
dc_tablespaces 4,325 0.14 0   0 5
dc_usernames 579 1.21 0   0 5
dc_users 19,354 0.13 0   0 26
outstanding_alerts 74 14.86 0   2 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 698 0.86 6,595 0.44 23 0
CLUSTER 73 0.00 168 1.79 3 0
INDEX 17 35.29 47 12.77 0 0
SQL AREA 19,339 95.34 141,769 39.67 452 115
TABLE/PROCEDURE 2,257 3.94 70,006 1.84 654 0
TRIGGER 61 1.64 363 2.20 6 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 57.78   2.06 4.29 22 22 28 28
  Freeable 7.94 0.00 0.66 0.39 1   12 12
  SQL 1.72 0.66 0.08 0.09 0 8 22 19
  PL/SQL 0.69 0.29 0.02 0.03 0 0 28 28
E Other 52.33   2.38 4.81 22 22 22 22
  Freeable 6.88 0.00 0.76 0.35 1   9 9
  SQL 0.97 0.39 0.06 0.05 0 8 16 13
  PL/SQL 0.47 0.17 0.02 0.03 0 0 22 22

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 7.86 11.27 43.45
shared Heap0: KGL 4.09 3.21 -21.52
shared KCB Table Scan Buffer 3.80 3.80 0.00
shared KGH: NO ACCESS   11.91  
shared KGLS heap 4.18 2.35 -43.76
shared KQR M PO 2.52   -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 3.72 7.34 97.09
shared PL/SQL DIANA 1.55   -100.00
shared PL/SQL MPCODE 4.37 1.98 -54.82
shared event statistics per sess 3.85 3.85 0.00
shared free memory 17.05 15.76 -7.60
shared kglsim hash table bkts 2.00 2.00 0.00
shared kglsim heap   1.67  
shared kglsim object batch   2.68  
shared library cache 7.79 9.66 24.02
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 35.88 7.46 -79.21
shared sql area:PLSQL   27.57  
  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