WORKLOAD REPOSITORY COMPARE PERIOD REPORT

Snapshot Set DB Name DB Id Instance Inst num Release Cluster Host
First (1st) O102EE1 2384218455 o102ee1 1 10.2.0.3.0 NO hpap0601
Second (2nd) O102EE1 2384218455 o102ee1 1 10.2.0.3.0 NO hpap0601

 

Snapshot Set Begin Snap Id Begin Snap Time End Snap Id End Snap Time Elapsed Time (min) DB Time (min) Avg Active Users
1st 5486 12-1月 -07 14:00:01 5489 12-1月 -07 15:00:03 60.04 37.71 0.63
2nd 5536 14-1月 -07 14:00:57 5537 14-1月 -07 15:00:58 60.02 0.15 0.00

 

Configuration Comparison

  1st 2nd %Diff
Buffer Cache: 92M 92M 0.00
Std Block Size: 8K 8K 0.00
Shared Pool Size: 148M 148M 0.00
Log Buffer: 6,960K 6,960K 0.00
SGA Target: 268M 268M 0.00
PGA Aggregate Target: 17M 17M 0.00
Undo Management: AUTO AUTO  

Load Profile

  1st Per Sec 2nd Per Sec %Diff 1st Per Txn 2nd Per Txn %Diff
Redo size: 2,371.23 1,051.11 -55.67 3,225.74 6,144.88 90.50
Logical reads: 14,187.82 35.99 -99.75 19,300.64 210.37 -98.91
Block changes: 12.70 6.03 -52.52 17.27 17.27 0.00
Physical reads: 6,690.71 0.00 -100.00 9,101.82 0.01 -100.00
Physical writes: 0.98 0.65 -33.67 1.33 3.82 187.22
User calls: 29.59 2.06 -93.04 40.25 12.04 -70.09
Parses: 13.39 1.39 -89.62 18.21 8.15 -55.24
Hard parses: 5.21 0.00 -100.00 7.08 0.00 -100.00
Sorts: 2.43 1.45 -40.33 3.30 8.46 156.36
Logons: 0.03 0.02 -33.33 0.04 0.13 225.00
Executes: 18.14 4.81 -73.48 24.68 28.14 14.02
Transactions: 0.74 0.17 -77.03      
  1st 2nd Diff
% Blocks changed per Read: 0.09 16.75 16.66
Recursive Call %: 68.33 88.10 19.77
Rollback per transaction %: 1.21 5.19 3.99
Rows per Sort: 8.51 4.16 -4.34
Avg Length of Calls (sec): 0.02 0.02 0.00

Top 5 Timed Events

1st 2nd
Event Waits Time(s) Percent Total DB Time Wait Class Event Waits Time(s) Percent Total DB Time Wait Class
CPU time   1,569.0 69.34   control file parallel write 1,183 48.8 554.23 System I/O
*read by other session 4,468,414 743.0 32.84 User I/O CPU time   7.2 82.18  
*db file scattered read 807,846 341.1 15.07 User I/O *log file parallel write 665 5.0 57.27 System I/O
control file parallel write 1,219 50.2 2.22 System I/O *log file sync 352 2.5 28.45 Commit
*db file sequential read 1,002,908 24.7 1.09 User I/O *os thread startup 3 0.1 1.40 Concurrency
-log file parallel write 2,820 13.2 .59 System I/O -db file sequential read 23 0.0 .01 User I/O
-log file sync 2,351 9.6 .42 Commit          
-os thread startup 4 0.2 .01 Concurrency          

Events with a "*" did not make the top 5 in the other snapshot set.

 

Events with a "-" did make the top 5 in the other snapshot set.

Report Details


Back to Top

Wait Events

  • Ordered by 'Diff' column of 'DB time %' descending (idle events last)
  % DB Time # Waits/sec (Elapsed Time) Avg Wait Time (ms)
Event 1st 2nd Diff 1st 2nd %Diff 1st 2nd %Diff
control file parallel write 2.22 554.23 552.01 0.34 0.33 -2.94 41.20 41.25 0.12
log file parallel write 0.59 57.27 56.69 0.78 0.18 -76.92 4.70 7.58 61.28
read by other session 32.84 0.00 -32.84 1,240.45 0.00 -100.00 0.17 0.00 -100.00
log file sync 0.42 28.45 28.02 0.65 0.10 -84.62 4.09 7.12 74.08
db file scattered read 15.07 0.00 -15.07 224.26 0.00 -100.00 0.42 0.00 -100.00
os thread startup 0.01 1.40 1.39 0.00 0.00 0.00 40.75 41.05 0.74
db file sequential read 1.09 0.01 -1.09 278.41 0.01 -100.00 0.02 0.03 50.00
SQL*Net break/reset to client 0.00 0.64 0.64 0.08 0.08 0.00 0.26 0.18 -30.77
control file sequential read 0.00 0.29 0.29 0.85 0.43 -49.41 0.02 0.02 0.00
latch free 0.00 0.23 0.23 0.01 0.00 -100.00 0.33 20.05 5,975.76
SQL*Net message to client 0.02 0.24 0.22 19.02 1.92 -89.91 0.01 0.00 -100.00
latch: cache buffers chains 0.01 0.00 -0.01 0.18 0.00 -100.00 0.19 0.00 -100.00
SQL*Net more data from client 0.00 0.00 0.00 0.01 0.00 -100.00 0.04 0.03 -25.00
rdbms ipc reply 0.00 0.00 0.00 0.00 0.00 0.00 0.10 0.06 -40.00
LGWR wait for redo copy 0.00 0.00 0.00 0.01 0.00 -100.00 0.04 0.03 -25.00
latch: shared pool 0.00 0.00 -0.00 0.00 0.00 0.00 1.78 0.00 -100.00
SQL*Net more data to client 0.00 0.00 -0.00 0.02 0.00 -100.00 0.04 0.00 -100.00
direct path write 0.00 0.00 -0.00 0.00 0.00 0.00 0.01 0.00 -100.00
SQL*Net message from client 981.64 123,969.94 122,988.30 19.02 1.92 -89.91 324.19 1,575.79 386.07
Streams AQ: waiting for messages in the queue 155.27 39,932.29 39,777.02 0.20 0.20 0.00 4,873.01 4,876.59 0.07
wait for unread message on broadcast channel 155.46 39,931.33 39,775.86 1.00 1.00 0.00 976.89 977.19 0.03
jobq slave wait 155.28 39,911.80 39,756.52 0.33 0.33 0.00 2,925.53 2,928.51 0.10
Streams AQ: qmn slave idle wait 153.89 39,796.52 39,642.62 0.04 0.04 0.00 27,205.40 27,375.52 0.63
Streams AQ: qmn coordinator idle wait 153.89 39,796.43 39,642.53 0.07 0.07 0.00 13,393.38 13,581.62 1.41
Streams AQ: waiting for time management or cleanup tasks 184.90 38,311.94 38,127.05 0.00 0.01 100.00 278,923.14 177,544.77 -36.35
SGA: MMAN sleep for component shrink 0.01 0.00 -0.01 0.01 0.00 -100.00 9.61 0.00 -100.00
class slave wait 0.00 0.00 0.00 0.00 0.00 0.00 0.11 0.10 -9.09


Back to Top

 

Time Model Statistics

  • Ordered by 'Diff' column of '% DB Time' descending (DB Time statistic first, background statistics last)
  % DB Time Time (seconds) Time per Trans (seconds)
Statistic Name 1st 2nd Diff 1st 2nd 1st 2nd %Diff
DB time 100.00 100.00 0.00 2,262.79 8.80 0.85 0.01 -98.82
PL/SQL execution elapsed time 0.14 19.37 19.23 3.25 1.71 0.00 0.00 0.00
sql execute elapsed time 97.64 78.66 -18.98 2,209.45 6.93 0.83 0.01 -98.80
DB CPU 69.34 82.18 12.84 1,569.03 7.24 0.59 0.01 -98.31
hard parse elapsed time 2.19 0.00 -2.19 49.56 0.00 0.02 0.00 -100.00
PL/SQL compilation elapsed time 1.16 0.00 -1.16 26.25 0.00 0.01 0.00 -100.00
connection management call elapsed time 0.02 1.13 1.11 0.50 0.10 0.00 0.00 0.00
parse time elapsed 2.58 3.19 0.60 58.49 0.28 0.02 0.00 -100.00
hard parse (sharing criteria) elapsed time 0.03 0.00 -0.03 0.71 0.00 0.00 0.00 0.00
repeated bind elapsed time 0.00 0.01 0.01 0.06 0.00 0.00 0.00 0.00
hard parse (bind mismatch) elapsed time 0.00 0.00 -0.00 0.02 0.00 0.00 0.00 0.00
sequence load elapsed time 0.00 0.00 -0.00 0.01 0.00 0.00 0.00 0.00
Java execution elapsed time 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
RMAN cpu time (backup/restore) 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
failed parse (out of shared memory) elapsed t 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
failed parse elapsed time 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
inbound PL/SQL rpc elapsed time 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
background elapsed time 3.81 752.48 748.67 86.28 66.26 0.03 0.11 266.67
background cpu time 0.43 61.65 61.22 9.66 5.43 0.00 0.01 100.00


Back to Top

 

Operating System Statistics

  • Ordered by 'Statistic Name'
  Value per Second (DB Time) per Second (Elapsed Time) per Trans
Statistic Name 1st 2nd 1st 2nd %Diff 1st 2nd %Diff 1st 2nd %Diff
BUSY_TIME 184,637 4,559 82 518 534.53 51 1 -97.52 70 7 -89.39
IDLE_TIME 1,255,719 1,435,035 555 162,980 29,269.00 349 398 14.31 474 2,330 391.26
IOWAIT_TIME 9,703 7,848 4 891 20,676.69 3 2 -18.96 4 13 248.09
LOAD -0 0 -0 0 0.00 -0 0 0.00 -0 0 0.00
NICE_TIME 1,127 21 0 2 378.00 0 0 -96.77 0 0 -93.02
NUM_CPU_SOCKETS 0 0 0 0 0.00 0 0 0.00 0 0 0.00
PHYSICAL_MEMORY_BYTES 1,144 -872 1 -99 -19,519.61 0 -0 -175.00 0 -1 -430.23
RSRC_MGR_CPU_WAIT_TIME 0 0 0 0 0.00 0 0 0.00 0 0 0.00
SYS_TIME 55,665 1,335 25 152 516.34 15 0 -97.61 21 2 -89.68
USER_TIME 127,600 3,054 56 347 515.09 35 1 -97.60 48 5 -89.71


Back to Top

 

Service Statistics

Back to Top

Service Statistics

  • Ordered by 'Diff' column of 'DB time %' descending
  • Total DB Time First: 2262.79 seconds, Second: 8.8 seconds
  • Total CPU Time First: 1569.03 seconds, Second: 7.24 seconds
  • Total Physical Reads First: 24101618, Second: 5
  • Total Logical Reads First: 51108085, Second: 129590
  % DB Time % CPU Time % Physical Reads % Logical Reads
Service Name 1st 2nd Diff 1st 2nd Diff 1st 2nd Diff 1st 2nd Diff
o102ee1 0.41 63.53 63.11 0.48 66.08 65.60 0.00 0.00 -0.00 0.10 30.07 29.98
SYS$USERS 99.59 36.47 -63.11 99.52 33.92 -65.60 99.99 20.00 -79.99 99.82 58.33 -41.49
SYS$BACKGROUND 0.00 0.00 0.00 0.00 0.00 0.00 0.01 60.00 59.99 0.07 11.47 11.39

Back to Service Statistics
Back to Top

Service Statistics (RAC)

  • Ordered by total Diff of '% of CR Blocks Received' and '% of Current Blocks Received'
  • Total GC CR Blks Received First: 1, Second: 1
  • Total GC CURRENT Blks Received First: 1, Second: 1
  % CR Blks Received % Current Blks Received
Service Name 1st 2nd Diff 1st 2nd Diff
SYS$BACKGROUND 0.00 0.00 0.00 0.00 0.00 0.00
SYS$USERS 0.00 0.00 0.00 0.00 0.00 0.00
o102ee1 0.00 0.00 0.00 0.00 0.00 0.00

Back to Service Statistics
Back to Top

 

SQL Statistics

Back to Top

Top 10 SQL Comparison by Execution Time

  • Ordered by 'Diff' column of 'Exec Time % of DB Time' descending.
  • 'N/A' indicates no data was captured for the statement in the period
  • 'Multiple Plans' column indicates whether more than one plan exists for the statement in the two periods
  • Total SQL Execution as a % DB Time First: 97.64%, Second: 78.66%
  Exec Time % of DB Time Exec Time (ms) / Exec #Exec/sec (DB Time) CPU Time (ms) / Exec Physical Reads / Exec #Rows Processed / Exec    
SQL Id 1st 2nd Diff 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd Multiple Plans SQL Text
9a5wbppva8npu 24.03   -24.03 544   0.44   541   12,587.73   1.00   No SELECT ENAME FROM EMP WHERE EM...
cb75rw3w1tt0s 0.10 19.65 19.55 1 1 0.95 244.98 1 1 0.00 0.00 1.00 1.00 Yes begin MGMT_JOB_ENGINE.get_sche...
6gvch1xu9ca3g 0.15 15.82 15.67 58 23 0.03 6.81 46 19 2.93 0.02 1.00 1.00 Yes DECLARE job BINARY_INTEGER := ...
8hk7xvhua40va 0.04 8.33 8.29 7 6 0.05 13.40 7 6 0.12 0.00 13.20 13.33 Yes INSERT INTO MGMT_METRICS_RAW(C...
2b064ybzkwf1y 0.02 4.77 4.74 5 3 0.05 13.63 4 3 0.02 0.00 0.00 0.00 Yes BEGIN EMD_NOTIFICATION.QUEUE_R...
91h2x42zqagcm 0.02 4.20 4.19 0 0 0.76 194.55 0 0 0.00 0.00 1.00 1.00 Yes UPDATE MGMT_CURRENT_METRICS SE...
bunssq950snhf 0.04 3.74 3.69 337 329 0.00 0.11 336 329 1.67 0.00 7.00 7.00 Yes insert into wrh$_sga_target_ad...
cydnuss99swtd 0.02 2.85 2.83 9 4 0.02 6.81 8 4 0.50 0.00 1.00 1.00 Yes BEGIN EM_PING.RECORD_BATCH_HEA...
6gh8gj9n09vr7 0.01 2.00 1.99 0 0 0.95 244.98 0 0 0.00 0.00 0.00 0.00 Yes SELECT JOB_ID, EXECUTION_ID, ...
abtp0uqvdb1d3 0.02 1.51 1.49 21 3 0.01 4.77 15 3 1.15 0.00 0.00 0.00 Yes CALL MGMT_ADMIN_DATA.EVALUATE_...
089dbukv1aanh 0.01 1.32 1.31 0 0 0.98 251.79 0 0 0.00 0.00 1.00 1.00 Yes SELECT SYS_EXTRACT_UTC(SYSTIME...
8zt0p6avg76yz 0.30   -0.30 69   0.04   67   31.03   1.00   No SELECT COUNT(ename) FROM emp3 ...
5u3yjw57ppbsx 0.12   -0.12 0   4.42   0   0.01   1.00   No SELECT ENAME FROM EMP2 WHERE E...
8u809k64x3nzd 0.07   -0.07 847   0.00   741   57.50   1.00   Yes begin DBMS_WORKLOAD_REPOSITORY...
db78fxqxwxt7r 0.05   -0.05 1   0.67   1   0.25   15.69   No select /*+ rule */ bucket, en...
b8b5jdj7khuaw 0.05   -0.05 32   0.02   32   0.00   1.71   Yes SELECT event#, sql_id, sql_p...
257rmrxgvaj4z 0.05   -0.05 16   0.03   16   0.00   13.00   Yes select begin_time, wait_class...

Back to SQL Statistics
Back to Top

Top 10 SQL Comparison by CPU Time

  • Ordered by Diff column of CPU Time % of DB Time descending.
  • 'N/A' in this section indicates no data was captured for the statement in the period
  • 'Multiple Plans' column in this section indicates whether more than one plan exist for the statement in the two periods
  • CPU Time as a % DB Time First: 69.34%, Second: 82.18%
  CPU Time % of DB Time CPU Time (ms) / Exec #Exec/sec (DB Time) Exec Time (ms) / Exec Physical Reads / Exec #Rows Processed / Exec    
SQL Id 1st 2nd Diff 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd Multiple Plans SQL Text
9a5wbppva8npu 23.91   -23.91 541   0.44   544   12,587.73   1.00   No SELECT ENAME FROM EMP WHERE EM...
cb75rw3w1tt0s 0.10 19.65 19.55 1 1 0.95 244.98 1 1 0.00 0.00 1.00 1.00 Yes begin MGMT_JOB_ENGINE.get_sche...
6gvch1xu9ca3g 0.12 12.84 12.72 46 19 0.03 6.81 58 23 2.93 0.02 1.00 1.00 Yes DECLARE job BINARY_INTEGER := ...
8hk7xvhua40va 0.04 8.33 8.29 7 6 0.05 13.40 7 6 0.12 0.00 13.20 13.33 Yes INSERT INTO MGMT_METRICS_RAW(C...
2b064ybzkwf1y 0.02 4.77 4.74 4 3 0.05 13.63 5 3 0.02 0.00 0.00 0.00 Yes BEGIN EMD_NOTIFICATION.QUEUE_R...
91h2x42zqagcm 0.02 4.20 4.19 0 0 0.76 194.55 0 0 0.00 0.00 1.00 1.00 Yes UPDATE MGMT_CURRENT_METRICS SE...
bunssq950snhf 0.04 3.74 3.69 336 329 0.00 0.11 337 329 1.67 0.00 7.00 7.00 Yes insert into wrh$_sga_target_ad...
cydnuss99swtd 0.01 2.85 2.84 8 4 0.02 6.81 9 4 0.50 0.00 1.00 1.00 Yes BEGIN EM_PING.RECORD_BATCH_HEA...
6gh8gj9n09vr7 0.01 2.00 1.99 0 0 0.95 244.98 0 0 0.00 0.00 0.00 0.00 Yes SELECT JOB_ID, EXECUTION_ID, ...
abtp0uqvdb1d3 0.02 1.51 1.49 15 3 0.01 4.77 21 3 1.15 0.00 0.00 0.00 Yes CALL MGMT_ADMIN_DATA.EVALUATE_...
089dbukv1aanh 0.01 1.32 1.31 0 0 0.98 251.79 0 0 0.00 0.00 1.00 1.00 Yes SELECT SYS_EXTRACT_UTC(SYSTIME...
8zt0p6avg76yz 0.29   -0.29 67   0.04   69   31.03   1.00   No SELECT COUNT(ename) FROM emp3 ...
5u3yjw57ppbsx 0.12   -0.12 0   4.42   0   0.01   1.00   No SELECT ENAME FROM EMP2 WHERE E...
8u809k64x3nzd 0.07   -0.07 741   0.00   847   57.50   1.00   Yes begin DBMS_WORKLOAD_REPOSITORY...
b8b5jdj7khuaw 0.05   -0.05 32   0.02   32   0.00   1.71   Yes SELECT event#, sql_id, sql_p...
257rmrxgvaj4z 0.05   -0.05 16   0.03   16   0.00   13.00   Yes select begin_time, wait_class...
db78fxqxwxt7r 0.04   -0.04 1   0.67   1   0.25   15.69   No select /*+ rule */ bucket, en...

Back to SQL Statistics
Back to Top

Top 10 SQL Comparison by Buffer Gets

  • Ordered by Diff column of % Total Gets descending.
  • 'N/A' in this section indicates no data was captured for the statement in the period
  • 'Multiple Plans' column in this section indicates whether more than one plan exist for the statement in the two periods
  • Total Buffer Gets First:51108085, Second: 129590
  % Total Gets Gets / Exec #Executions Exec Time (ms) / Exec CPU Time (ms) / Exec Physical Reads / Exec #Rows Processed / Exec    
SQL Id 1st 2nd Diff 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd Multiple Plans SQL Text
9a5wbppva8npu 24.77   -24.77 12,657.03   1,000   544   541   12,587.73   1.00   No SELECT ENAME FROM EMP WHERE EM...
8hk7xvhua40va 0.03 14.87 14.84 116.82 163.28 120 118 7 6 7 6 0.12 0.00 13.20 13.33 Yes INSERT INTO MGMT_METRICS_RAW(C...
6gvch1xu9ca3g 0.05 13.55 13.50 401.43 292.63 60 60 58 23 46 19 2.93 0.02 1.00 1.00 Yes DECLARE job BINARY_INTEGER := ...
91h2x42zqagcm 0.02 12.44 12.42 6.30 9.41 1,715 1,713 0 0 0 0 0.00 0.00 1.00 1.00 Yes UPDATE MGMT_CURRENT_METRICS SE...
cb75rw3w1tt0s 0.02 6.66 6.64 4.00 4.00 2,157 2,157 1 1 1 1 0.00 0.00 1.00 1.00 Yes begin MGMT_JOB_ENGINE.get_sche...
2zwjrv2186835 0.01 3.10 3.10 748.00 804.60 5 5 12 11 11 11 0.40 0.20 346.80 370.00 No DELETE FROM MGMT_METRICS_RAW W...
2b064ybzkwf1y 0.01 2.59 2.59 28.24 28.00 119 120 5 3 4 3 0.02 0.00 0.00 0.00 Yes BEGIN EMD_NOTIFICATION.QUEUE_R...
cydnuss99swtd 0.01 2.50 2.50 74.71 54.07 42 60 9 4 8 4 0.50 0.00 1.00 1.00 Yes BEGIN EM_PING.RECORD_BATCH_HEA...
0h6b2sajwb74n 0.00 1.73 1.73 1.92 1.93 1,183 1,161 0 0 0 0 0.00 0.00 1.55 1.57 Yes select privilege#, level from ...
6ssrk2dqj7jbx 0.00 1.67 1.66 3.03 3.00 720 720 0 0 0 0 0.00 0.00 0.08 0.08 Yes select job, nvl2(last_date, ...
6gh8gj9n09vr7 0.00 1.66 1.66 1.00 1.00 2,157 2,157 0 0 0 0 0.00 0.00 0.00 0.00 Yes SELECT JOB_ID, EXECUTION_ID, ...
8zt0p6avg76yz 0.30   -0.30 1,557.46   100   69   67   31.03   1.00   No SELECT COUNT(ename) FROM emp3 ...
5u3yjw57ppbsx 0.08   -0.08 4.00   10,000   0   0   0.01   1.00   No SELECT ENAME FROM EMP2 WHERE E...
90p005ah4wm47 0.03   -0.03 14.74   1,000   0   0   0.05   1.00   No select distinct emp1.deptno fr...
8u809k64x3nzd 0.02   -0.02 5,730.50   2   847   741   57.50   1.00   Yes begin DBMS_WORKLOAD_REPOSITORY...
g6vss60nrfakg 0.02   -0.02 1,668.00   6   19   18   1.17   24.83   Yes SELECT /*+ NO_MERGE(t) USE_NL(...
fdqtcb7dbv3vr 0.02   -0.02 4,545.00   2   81   34   127.00   1.00   Yes SELECT task_list.task_id FROM ...

Back to SQL Statistics
Back to Top

Top 10 SQL Comparison by Physical Reads

  • Ordered by Diff column of % Total Physical Reads descending.
  • 'N/A' in this section indicates no data was captured for the statement in the period
  • 'Multiple Plans' column in this section indicates whether more than one plan exist for the statement in the two periods
  • User I/O Class Wait Time as a % DB Time First: 49.01%, Second: .01%
  • Total Physical Reads First: 24101618, Second: 5
  % Total Physical Reads Physical Reads / Exec #Executions Exec Time (ms) / Exec CPU Time (ms) / Exec #Rows Processed / Exec    
SQL Id 1st 2nd Diff 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd Multiple Plans SQL Text
9a5wbppva8npu 52.23   -52.23 12,587.73   1,000   544   541   1.00   No SELECT ENAME FROM EMP WHERE EM...
btu8ypwfgmnus 0.00 40.00 40.00 3.67 2.00 3 1 12 1 7 1 11.00 11.00 Yes insert into wrh$_pga_target_ad...
2zwjrv2186835 0.00 20.00 20.00 0.40 0.20 5 5 12 11 11 11 346.80 370.00 No DELETE FROM MGMT_METRICS_RAW W...
agpd044zj368m 0.00 20.00 20.00 1.67 1.00 3 1 12 13 12 13 68.00 68.00 Yes insert into wrh$_system_event ...
6hwjmjgrpsuaa 0.00 20.00 20.00 5.33 1.00 3 1 23 4 12 4 65.00 66.00 Yes insert into wrh$_enqueue_stat ...
6gvch1xu9ca3g 0.00 20.00 20.00 2.93 0.02 60 60 58 23 46 19 1.00 1.00 Yes DECLARE job BINARY_INTEGER := ...
8zt0p6avg76yz 0.01   -0.01 31.03   100   69   67   1.00   No SELECT COUNT(ename) FROM emp3 ...
cfz686a6qp0kg 0.00 0.00 -0.00 583.00 0.00 1 1 38 3 25 3 0.00 0.00 No select o.obj#, u.name, o.nam...
db78fxqxwxt7r 0.00   -0.00 0.25   1,522   1   1   15.69   No select /*+ rule */ bucket, en...
cvn54b7yz0s8u 0.00   -0.00 2.06   137   3   2   2.04   Yes select /*+ index(idl_ub1$ i_id...
fdqtcb7dbv3vr 0.00   -0.00 127.00   2   81   34   1.00   Yes SELECT task_list.task_id FROM ...
39m4sx9k63ba2 0.00   -0.00 1.25   137   2   2   1.09   Yes select /*+ index(idl_ub2$ i_id...
49s332uhbnsma 0.00 0.00 -0.00 4.32 0.00 28 56 4 0 1 0 1.00 1.00 No declare vsn va...
8u809k64x3nzd 0.00   -0.00 57.50   2   847   741   1.00   Yes begin DBMS_WORKLOAD_REPOSITORY...
07hubrj8b4110 0.00 0.00 -0.00 3.00 0.00 4 6 32 1 28 1 1.00 1.00 Yes /* OracleOEM */ declare ...
072t81cu41xfj 0.00 0.00 -0.00 6.00 0.00 1 1 19 7 6 7 1.00 1.00 Yes SELECT DECODE(COUNT(*), 0, 1...
00prz3j9n4gyj 0.00 0.00 0.00 0.00 0.00 5 5 8 8 8 8 44.80 44.80 No SELECT METRIC_GUID, TRUNC(COL...
04q097xbk1vtx 0.00 0.00 0.00 0.00 0.00 61 60 1 0 1 0 1.00 1.00 Yes UPDATE MGMT_EMD_PING SET LAST_...
062savj8zgzut 0.00 0.00 0.00 0.00 0.00 13 3 0 0 0 0 1.00 1.00 Yes UPDATE sys.wri$_adv_parameters...

Back to SQL Statistics
Back to Top

Top 10 SQL Comparison by Executions

  • Ordered by Diff column of #Executions/sec (DB Time) descending.
  • 'N/A' in this section indicates no data was captured for the statement in the period
  • 'Multiple Plans' column in this section indicates whether more than one plan exist for the statement in the two periods
  • Total SQL Execution as a % DB Time First: 97.64%, Second: 78.66%
  #Executions/sec (DB Time) #Executions Exec Time (ms) / Exec CPU Time (ms) / Exec Physical Reads / Exec #Rows Processed / Exec    
SQL Id 1st 2nd Diff 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd Multiple Plans SQL Text
089dbukv1aanh 0.98 251.79 250.81 2,217 2,217 0 0 0 0 0.00 0.00 1.00 1.00 Yes SELECT SYS_EXTRACT_UTC(SYSTIME...
6gh8gj9n09vr7 0.95 244.98 244.02 2,157 2,157 0 0 0 0 0.00 0.00 0.00 0.00 Yes SELECT JOB_ID, EXECUTION_ID, ...
cb75rw3w1tt0s 0.95 244.98 244.02 2,157 2,157 1 1 1 1 0.00 0.00 1.00 1.00 Yes begin MGMT_JOB_ENGINE.get_sche...
91h2x42zqagcm 0.76 194.55 193.79 1,715 1,713 0 0 0 0 0.00 0.00 1.00 1.00 Yes UPDATE MGMT_CURRENT_METRICS SE...
0h6b2sajwb74n 0.52 131.86 131.33 1,183 1,161 0 0 0 0 0.00 0.00 1.55 1.57 Yes select privilege#, level from ...
6ssrk2dqj7jbx 0.32 81.77 81.45 720 720 0 0 0 0 0.00 0.00 0.08 0.08 Yes select job, nvl2(last_date, ...
g2wr3u7s1gtf3 0.29 74.96 74.67 660 660 0 0 0 0 0.00 0.00 1.00 1.00 Yes select count(*) from sys.job$ ...
52td38mkm2jh3 0.09 21.92 21.83 193 193 0 0 0 0 0.00 0.00 0.00 0.00 No SELECT ROWID FROM MGMT_CURRENT...
24dkx03u3rj6k 0.08 20.44 20.36 181 180 0 0 0 0 0.00 0.00 1.00 1.00 Yes SELECT COUNT(*) FROM MGMT_PARA...
444ygb1fcndkz 0.08 19.08 19.00 171 168 0 0 0 0 0.00 0.00 1.00 1.00 Yes SELECT STRING_VALUE FROM MGMT_...
5u3yjw57ppbsx 4.42   -4.42 10,000   0   0   0.01   1.00   No SELECT ENAME FROM EMP2 WHERE E...
96g93hntrzjtr 1.14   -1.14 2,580   0   0   0.02   0.73   Yes select /*+ rule */ bucket_cnt,...
db78fxqxwxt7r 0.67   -0.67 1,522   1   1   0.25   15.69   No select /*+ rule */ bucket, en...
6kpzvzuv00t58 0.44   -0.44 1,000   1   1   0.01   1.00   No update EMP5 set ename = 'aaa' ...
90p005ah4wm47 0.44   -0.44 1,000   0   0   0.05   1.00   No select distinct emp1.deptno fr...

Back to SQL Statistics
Back to Top

Top 10 SQL Comparison by Parse Calls

  • Ordered by Diff column of % Total Parses descending.
  • 'N/A' in this section indicates no data was captured for the statement in the period
  • 'Multiple Plans' column in this section indicates whether more than one plan exist for the statement in the two periods
  • Parse Time as a % DB Time First: 0%, Second: 0%
  • Total Parse Calls First: 48233, Second: 5020
  % Total Parses Parses #Executions    
SQL Id 1st 2nd Diff 1st 2nd 1st 2nd Multiple Plans SQL Text
5u3yjw57ppbsx 20.73   -20.73 10,000   10,000   No SELECT ENAME FROM EMP2 WHERE E...
0h6b2sajwb74n 2.45 23.13 20.67 1,183 1,161 1,183 1,161 Yes select privilege#, level from ...
6129566gyvx21 0.27 2.53 2.26 132 127 132 127 Yes SELECT INSTANTIABLE, supertyp...
2b064ybzkwf1y 0.25 2.39 2.14 119 120 119 120 Yes BEGIN EMD_NOTIFICATION.QUEUE_R...
24dkx03u3rj6k 0.25 2.39 2.14 120 120 181 180 Yes SELECT COUNT(*) FROM MGMT_PARA...
6kpzvzuv00t58 2.07   -2.07 1,000   1,000   No update EMP5 set ename = 'aaa' ...
90p005ah4wm47 2.07   -2.07 1,000   1,000   No select distinct emp1.deptno fr...
9a5wbppva8npu 2.07   -2.07 1,000   1,000   No SELECT ENAME FROM EMP WHERE EM...
d9qr16p9z7xy2 2.07   -2.07 1,000   1,000   No update emp4 set ename = 'aaa' ...
350f5yrnnmshs 0.24 2.17 1.93 117 109 117 109 Yes lock table sys.mon_mods$ in ex...
g00cj285jmgsw 0.40 2.17 1.77 192 109 192 109 Yes update sys.mon_mods$ set inser...
0k8522rmdzg4k 0.18 1.59 1.41 89 80 89 80 Yes select privilege# from sysauth...
18naypzfmabd6 0.13 1.22 1.09 61 61 167 158 Yes INSERT INTO MGMT_SYSTEM_PERFOR...
089dbukv1aanh 0.12 1.20 1.07 60 60 2,217 2,217 Yes SELECT SYS_EXTRACT_UTC(SYSTIME...
08vznc16ycuag 0.12 1.20 1.07 60 60 60 60 Yes SELECT SYS_GUID() FROM SYS.DUA...
2ym6hhaq30r73 1.01   -1.01 485   485   Yes select type#, blocks, extents,...
3c1kubcdjnppq 0.64   -0.64 309   822   Yes update sys.col_usage$ set eq...
53btfq0dt9bs9 0.64   -0.64 309   148   Yes insert into sys.col_usage$ val...
b2gnxm5z6r51n 0.64   -0.64 309   309   Yes lock table sys.col_usage$ in e...

Back to SQL Statistics
Back to Top

 

Complete List of SQL Text

SQL Id SQL Text
00prz3j9n4gyj SELECT METRIC_GUID, TRUNC(COLLECTION_TIMESTAMP, 'HH24'), KEY_VALUE, COUNT(TARGET_GUID), AVG(VALUE), MIN(VALUE), MAX(VALUE), STDDEV(VALUE) FROM MGMT_METRICS_RAW WHERE TARGET_GUID = :B3 AND COLLECTION_TIMESTAMP >= (:B2 +(1/24)) AND COLLECTION_TIMESTAMP < (:B1 +(1/24)) AND VALUE IS NOT NULL GROUP BY TARGET_GUID, METRIC_GUID, KEY_VALUE, TRUNC(COLLECTION_TIMESTAMP, 'HH24')
04q097xbk1vtx UPDATE MGMT_EMD_PING SET LAST_HEARTBEAT_TS = :B6 , LAST_HEARTBEAT_UTC = CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE), CLEAN_HEARTBEAT_UTC = :B5 , STATUS_SYNC_UTC = :B4 , EMD_UPTIME_UTC = :B3 , HEARTBEAT_RECORDER_URL = SUBSTR(:B1 , 0, 256) WHERE TARGET_GUID = :B2
062savj8zgzut UPDATE sys.wri$_adv_parameters SET datatype = :1, value = :2, flags = :3, description = :4 WHERE task_id = :5 AND name = :6
072t81cu41xfj SELECT DECODE(COUNT(*), 0, 1, 0) FROM MGMT_SEVERITY WHERE TARGET_GUID = :B3 AND METRIC_GUID = :B2 AND KEY_VALUE = :B1
07hubrj8b4110 /* OracleOEM */ declare TYPE data_cursor_type IS REF CURSOR; data_cursor data_cursor_type; resultsql varchar2(32767); rquery varchar2(1000); wquery varchar2(1000); propagation_name varchar2(100) ; i number :=0; rdy NUMBER := 0; wtg NUMBER := 0; begin resultsql := ''; FOR cur_row IN (select propagation_name, queue_table, source_queue_owner, source_queue_name from dba_queues, dba_propagation where owner=source_queue_owner and source_queue_name=name) LOOP rquery := 'SELECT NVL(COUNT(MSG_STATE), 0) FROM '||cur_row.source_queue_owner||'.AQ$'||cur_row.queue_table||' WHERE consumer_name is null AND MSG_STATE=''READY'' and queue = '''||cur_row.source_queue_name||''' and address IN (select ''"''||destination_queue_owner ||''"'' ||''.''||''"'' ||destination_queue_name||''"@''||destination_dblink from dba_propagation)'; execute immediate rquery into rdy; wquery := 'SELECT NVL(COUNT(MSG_STATE), 0) FROM '||cur_row.source_queue_owner||'.AQ$'||cur_row.queue_table||' WHERE consumer_name is null AND MSG_STATE=''WAITING'' and queue = '''||cur_row.source_queue_name||''' and address IN (select ''"''||destination_queue_owner ||''"'' ||''.''||''"'' ||destination_queue_name||''"@''||destination_dblink from dba_propagation)'; execute immediate wquery into wtg; if(i > 0) then resultsql := resultsql || ' UNION '; end if; propagation_name := cur_row.propagation_name; resultsql := resultsql || 'select '''||propagation_name||''' propagation_name, '||rdy || ' ready, '||wtg||' waiting from dual'; i := i + 1; END LOOP; ---- -- If no propagation jobs are there , return an empty result set ---- if(resultsql IS NULL) then OPEN data_cursor FOR SELECT -1 FROM dual WHERE 0=1; else OPEN data_cursor FOR resultsql; end if; :1 := data_cursor; end;
089dbukv1aanh SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL
08vznc16ycuag SELECT SYS_GUID() FROM SYS.DUAL
0h6b2sajwb74n select privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
0k8522rmdzg4k select privilege# from sysauth$ where (grantee#=:1 or 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))
24dkx03u3rj6k SELECT COUNT(*) FROM MGMT_PARAMETERS WHERE PARAMETER_NAME=:B1 AND UPPER(PARAMETER_VALUE)='TRUE'
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#
2b064ybzkwf1y BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
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
2zwjrv2186835 DELETE FROM MGMT_METRICS_RAW WHERE ROWID = :B1
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
444ygb1fcndkz SELECT STRING_VALUE FROM MGMT_CURRENT_METRICS WHERE TARGET_GUID = :B3 AND METRIC_GUID = :B2 AND KEY_VALUE = :B1
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;
52td38mkm2jh3 SELECT ROWID FROM MGMT_CURRENT_METRICS WHERE TARGET_GUID = :B3 AND METRIC_GUID = :B2 AND COLLECTION_TIMESTAMP < :B1 FOR UPDATE SKIP LOCKED
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
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;
6hwjmjgrpsuaa insert into wrh$_enqueue_stat (snap_id, dbid, instance_number, eq_type, req_reason, total_req#, total_wait#, succ_req#, failed_req#, cum_wait_time, event#) select :snap_id, :dbid, :instance_number, eq_type, req_reason, total_req#, total_wait#, succ_req#, failed_req#, cum_wait_time, event# from v$enqueue_statistics where total_req# != 0 order by eq_type, req_reason
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
abtp0uqvdb1d3 CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mguid, :result)
agpd044zj368m insert into wrh$_system_event (snap_id, dbid, instance_number, event_id, total_waits, total_timeouts, time_waited_micro) select :snap_id, :dbid, :instance_number, event_id, total_waits, total_timeouts, time_waited_micro from v$system_event order by event_id
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
btu8ypwfgmnus insert into wrh$_pga_target_advice (snap_id, dbid, instance_number, pga_target_for_estimate, pga_target_factor, advice_status, bytes_processed, estd_extra_bytes_rw, estd_pga_cache_hit_percentage, estd_overalloc_count) select :snap_id, :dbid, :instance_number, pga_target_for_estimate, pga_target_factor, advice_status, bytes_processed, estd_extra_bytes_rw, estd_pga_cache_hit_percentage, estd_overalloc_count from v$pga_target_advice where advice_status = 'ON'
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#
cydnuss99swtd BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END;
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
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))
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

Key Instance Activity Stats

  • Ordered by statistic name.
  Value per Second (DB Time) per Second (Elapsed Time) per Trans
Statistic 1st 2nd 1st 2nd %Diff 1st 2nd %Diff 1st 2nd %Diff
application wait time 8 6 0.00 0.68 100.00 0.00 0.00 0.00 0.00 0.01 100.00
concurrency wait time 30 12 0.01 1.36 13,500.00 0.01 0.00 -100.00 0.01 0.02 100.00
db block changes 45,744 21,708 20.22 2,465.43 12,093.03 12.70 6.03 -52.52 17.27 35.24 104.05
execute count 65,353 17,337 28.88 1,969.00 6,717.87 18.14 4.81 -73.48 24.68 28.14 14.02
logons cumulative 95 83 0.04 9.43 23,475.00 0.03 0.02 -33.33 0.04 0.13 225.00
opened cursors cumulative 53,396 4,860 23.60 551.96 2,238.81 14.82 1.35 -90.89 20.16 7.89 -60.86
parse count (total) 48,233 5,020 21.32 570.13 2,574.16 13.39 1.39 -89.62 18.21 8.15 -55.24
parse time elapsed 3,166 19 1.40 2.16 54.29 0.88 0.01 -98.86 1.20 0.03 -97.50
physical reads 24,101,618 5 10,651.29 0.57 -99.99 6,690.71 0.00 -100.00 9,101.82 0.01 -100.00
physical writes 3,521 2,352 1.56 267.12 17,023.08 0.98 0.65 -33.67 1.33 3.82 187.22
redo size 8,541,772 3,785,244 3,774.89 429,899.15 11,288.39 2,371.23 1,051.11 -55.67 3,225.74 6,144.88 90.50
session cursor cache hits 29,491 1,872 13.03 212.61 1,531.70 8.19 0.52 -93.65 11.14 3.04 -72.71
session logical reads 51,108,085 129,590 22,586.33 14,717.84 -34.84 14,187.82 35.99 -99.75 19,300.64 210.37 -98.91
user I/O wait time 110,893 0 49.01 0.00 -100.00 30.78 0.00 -100.00 41.88 0.00 -100.00
user calls 106,573 7,417 47.10 842.37 1,688.47 29.59 2.06 -93.04 40.25 12.04 -70.09
user commits 2,616 584 1.16 66.33 5,618.10 0.73 0.16 -78.08 0.99 0.95 -4.04
user rollbacks 32 32 0.01 3.63 36,200.00 0.01 0.01 0.00 0.01 0.05 400.00
workarea executions - onepass 0 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
workarea executions - optimal 4,780 2,428 2.11 275.75 12,968.72 1.33 0.67 -49.62 1.81 3.94 117.68

Back to Instance Activity Statistics
Back to Top

Other Instance Activity Stats

  • Ordered by statistic name.
  Value per Second (DB Time) per Second (Elapsed Time) per Trans
Statistic 1st 2nd 1st 2nd %Diff 1st 2nd %Diff 1st 2nd %Diff
CPU used by this session 158,065 822 69.85 93.36 33.66 43.88 0.23 -99.48 59.69 1.33 -97.77
CPU used when call started 157,389 602 69.56 68.37 -1.71 43.69 0.17 -99.61 59.44 0.98 -98.35
CR blocks created 584 1,020 0.26 115.84 44,453.85 0.16 0.28 75.00 0.22 1.66 654.55
Cached Commit SCN referenced 154,800 0 68.41 0.00 -100.00 42.97 0.00 -100.00 58.46 0.00 -100.00
Commit SCN cached 1 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
DBWR checkpoint buffers written 3,471 2,352 1.53 267.12 17,358.82 0.96 0.65 -32.29 1.31 3.82 191.60
DBWR transaction table writes 103 100 0.05 11.36 22,620.00 0.03 0.03 0.00 0.04 0.16 300.00
DBWR undo block writes 548 310 0.24 35.21 14,570.83 0.15 0.09 -40.00 0.21 0.50 138.10
IMU CR rollbacks 248 343 0.11 38.96 35,318.18 0.07 0.10 42.86 0.09 0.56 522.22
IMU Flushes 918 909 0.41 103.24 25,080.49 0.25 0.25 0.00 0.35 1.48 322.86
IMU Redo allocation size 786,180 657,860 347.44 74,714.72 21,404.35 218.25 182.68 -16.30 296.90 1,067.95 259.70
IMU commits 2,574 552 1.14 62.69 5,399.12 0.71 0.15 -78.87 0.97 0.90 -7.22
IMU contention 2 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
IMU recursive-transaction flush 0 1 0.00 0.11 100.00 0.00 0.00 0.00 0.00 0.00 0.00
IMU undo allocation size 4,107,720 2,154,696 1,815.34 244,713.94 13,380.34 1,140.32 598.33 -47.53 1,551.25 3,497.88 125.49
SMON posted for undo segment shrink 2 1 0.00 0.11 100.00 0.00 0.00 0.00 0.00 0.00 0.00
SQL*Net roundtrips to/from client 68,435 6,866 30.24 779.79 2,478.67 19.00 1.91 -89.95 25.84 11.15 -56.85
active txn count during cleanout 175 59 0.08 6.70 8,275.00 0.05 0.02 -60.00 0.07 0.10 42.86
background timeouts 11,506 11,501 5.08 1,306.20 25,612.60 3.19 3.19 0.00 4.35 18.67 329.20
buffer is not pinned count 128,267 23,288 56.69 2,644.87 4,565.50 35.61 6.47 -81.83 48.44 37.81 -21.94
buffer is pinned count 47,688 8,091 21.07 918.91 4,261.22 13.24 2.25 -83.01 18.01 13.13 -27.10
bytes received via SQL*Net from client 9,755,230 747,374 4,311.16 84,881.04 1,868.87 2,708.09 207.54 -92.34 3,684.00 1,213.27 -67.07
bytes sent via SQL*Net to client 13,478,587 665,033 5,956.63 75,529.38 1,167.99 3,741.71 184.67 -95.06 5,090.10 1,079.60 -78.79
calls to get snapshot scn: kcmgss 115,700 28,851 51.13 3,276.68 6,308.53 32.12 8.01 -75.06 43.69 46.84 7.21
calls to kcmgas 4,433 2,157 1.96 244.98 12,398.98 1.23 0.60 -51.22 1.67 3.50 109.58
calls to kcmgcs 148 55 0.07 6.25 8,828.57 0.04 0.02 -50.00 0.06 0.09 50.00
change write time 52 10 0.02 1.14 5,600.00 0.01 0.00 -100.00 0.02 0.02 0.00
cleanout - number of ktugct calls 223 70 0.10 7.95 7,850.00 0.06 0.02 -66.67 0.08 0.11 37.50
cleanouts and rollbacks - consistent read gets 4 4 0.00 0.45 100.00 0.00 0.00 0.00 0.00 0.01 100.00
cleanouts only - consistent read gets 11 1 0.00 0.11 100.00 0.00 0.00 0.00 0.00 0.00 0.00
cluster key scan block gets 7,307 2,754 3.23 312.78 9,583.59 2.03 0.76 -62.56 2.76 4.47 61.96
cluster key scans 3,144 86 1.39 9.77 602.88 0.87 0.02 -97.70 1.19 0.14 -88.24
commit batch/immediate performed 34 34 0.02 3.86 19,200.00 0.01 0.01 0.00 0.01 0.06 500.00
commit batch/immediate requested 34 34 0.02 3.86 19,200.00 0.01 0.01 0.00 0.01 0.06 500.00
commit cleanout failures: callback failure 10 5 0.00 0.57 100.00 0.00 0.00 0.00 0.00 0.01 100.00
commit cleanouts 7,239 3,669 3.20 416.70 12,921.88 2.01 1.02 -49.25 2.73 5.96 118.32
commit cleanouts successfully completed 7,229 3,664 3.19 416.13 12,944.83 2.01 1.02 -49.25 2.73 5.95 117.95
commit immediate performed 34 34 0.02 3.86 19,200.00 0.01 0.01 0.00 0.01 0.06 500.00
commit immediate requested 34 34 0.02 3.86 19,200.00 0.01 0.01 0.00 0.01 0.06 500.00
commit txn count during cleanout 105 29 0.05 3.29 6,480.00 0.03 0.01 -66.67 0.04 0.05 25.00
consistent changes 599 1,020 0.26 115.84 44,453.85 0.17 0.28 64.71 0.23 1.66 621.74
consistent gets 51,070,660 112,897 22,569.79 12,821.98 -43.19 14,177.43 31.35 -99.78 19,286.50 183.27 -99.05
consistent gets - examination 119,393 21,278 52.76 2,416.59 4,480.34 33.14 5.91 -82.17 45.09 34.54 -23.40
consistent gets from cache 51,070,660 112,897 22,569.79 12,821.98 -43.19 14,177.43 31.35 -99.78 19,286.50 183.27 -99.05
cursor authentications 424 1 0.19 0.11 -42.11 0.12 0.00 -100.00 0.16 0.00 -100.00
data blocks consistent reads - undo records applied 584 1,020 0.26 115.84 44,453.85 0.16 0.28 75.00 0.22 1.66 654.55
db block gets 37,425 16,693 16.54 1,895.86 11,362.27 10.39 4.64 -55.34 14.13 27.10 91.79
db block gets direct 14 0 0.01 0.00 -100.00 0.00 0.00 0.00 0.01 0.00 -100.00
db block gets from cache 37,411 16,693 16.53 1,895.86 11,369.21 10.39 4.64 -55.34 14.13 27.10 91.79
deferred (CURRENT) block cleanout applications 5,078 2,113 2.24 239.98 10,613.39 1.41 0.59 -58.16 1.92 3.43 78.65
dirty buffers inspected 2 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
enqueue conversions 731 723 0.32 82.11 25,559.38 0.20 0.20 0.00 0.28 1.17 317.86
enqueue releases 57,167 32,959 25.26 3,743.23 14,718.80 15.87 9.15 -42.34 21.59 53.50 147.80
enqueue requests 57,167 32,959 25.26 3,743.23 14,718.80 15.87 9.15 -42.34 21.59 53.50 147.80
free buffer inspected 24,127,314 171 10,662.65 19.42 -99.82 6,697.84 0.05 -100.00 9,111.52 0.28 -100.00
free buffer requested 24,102,713 1,361 10,651.78 154.57 -98.55 6,691.01 0.38 -99.99 9,102.23 2.21 -99.98
heap block compress 37 17 0.02 1.93 9,550.00 0.01 0.00 -100.00 0.01 0.03 200.00
hot buffers moved to head of LRU 2,740 174 1.21 19.76 1,533.06 0.76 0.05 -93.42 1.03 0.28 -72.82
immediate (CR) block cleanout applications 15 5 0.01 0.57 5,600.00 0.00 0.00 0.00 0.01 0.01 0.00
immediate (CURRENT) block cleanout applications 1,083 930 0.48 105.62 21,904.17 0.30 0.26 -13.33 0.41 1.51 268.29
index crx upgrade (positioned) 10,049 9,174 4.44 1,041.91 23,366.44 2.79 2.55 -8.60 3.79 14.89 292.88
index fast full scans (full) 47 30 0.02 3.41 16,950.00 0.01 0.01 0.00 0.02 0.05 150.00
index fetch by key 51,267 18,920 22.66 2,148.79 9,382.74 14.23 5.25 -63.11 19.36 30.71 58.63
index scans kdiixs1 31,610 15,539 13.97 1,764.80 12,532.78 8.78 4.31 -50.91 11.94 25.23 111.31
leaf node 90-10 splits 20 10 0.01 1.14 11,300.00 0.01 0.00 -100.00 0.01 0.02 100.00
leaf node splits 60 19 0.03 2.16 7,100.00 0.02 0.01 -50.00 0.02 0.03 50.00
lob reads 182 0 0.08 0.00 -100.00 0.05 0.00 -100.00 0.07 0.00 -100.00
lob writes 134 0 0.06 0.00 -100.00 0.04 0.00 -100.00 0.05 0.00 -100.00
lob writes unaligned 134 0 0.06 0.00 -100.00 0.04 0.00 -100.00 0.05 0.00 -100.00
messages received 4,246 1,550 1.88 176.04 9,263.83 1.18 0.43 -63.56 1.60 2.52 57.50
messages sent 4,246 1,550 1.88 176.04 9,263.83 1.18 0.43 -63.56 1.60 2.52 57.50
no work - consistent read gets 50,890,611 77,279 22,490.22 8,776.76 -60.98 14,127.44 21.46 -99.85 19,218.51 125.45 -99.35
parse count (hard) 18,753 0 8.29 0.00 -100.00 5.21 0.00 -100.00 7.08 0.00 -100.00
parse time cpu 2,982 26 1.32 2.95 123.48 0.83 0.01 -98.80 1.13 0.04 -96.46
physical read IO requests 1,810,762 5 800.23 0.57 -99.93 502.67 0.00 -100.00 683.82 0.01 -100.00
physical read bytes ############### 40,960 87,255,391.27 4,651.92 -99.99 54,810,283.86 11.37 -100.00 74,562,105.23 66.49 -100.00
physical read total IO requests 1,813,869 1,556 801.61 176.72 -77.95 503.54 0.43 -99.91 685.00 2.53 -99.63
physical read total bytes ############### 25,288,704 87,277,758.47 2,872,098.20 -96.71 54,824,334.02 7,022.32 -99.99 74,581,218.61 41,053.09 -99.94
physical read total multi block requests 807,846 0 357.01 0.00 -100.00 224.26 0.00 -100.00 305.08 0.00 -100.00
physical reads cache 24,101,618 5 10,651.29 0.57 -99.99 6,690.71 0.00 -100.00 9,101.82 0.01 -100.00
physical reads cache prefetch 22,290,856 0 9,851.06 0.00 -100.00 6,188.03 0.00 -100.00 8,418.00 0.00 -100.00
physical write IO requests 1,563 990 0.69 112.44 16,195.65 0.43 0.27 -37.21 0.59 1.61 172.88
physical write bytes 28,844,032 19,267,584 12,747.12 2,188,265.29 17,066.74 8,007.22 5,350.34 -33.18 10,892.76 31,278.55 187.15
physical write total IO requests 8,046 5,205 3.56 591.14 16,505.06 2.23 1.45 -34.98 3.04 8.45 177.96
physical write total bytes 98,872,320 81,462,272 43,694.91 9,251,863.78 21,073.78 27,447.36 22,620.94 -17.58 37,338.49 132,243.95 254.18
physical write total multi block requests 3,221 945 1.42 107.33 7,458.45 0.89 0.26 -70.79 1.22 1.53 25.41
physical writes direct 14 0 0.01 0.00 -100.00 0.00 0.00 0.00 0.01 0.00 -100.00
physical writes direct (lob) 8 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
physical writes from cache 3,507 2,352 1.55 267.12 17,133.55 0.97 0.65 -32.99 1.32 3.82 189.39
physical writes non checkpoint 807 153 0.36 17.38 4,727.78 0.22 0.04 -81.82 0.30 0.25 -16.67
pinned buffers inspected 24,999 0 11.05 0.00 -100.00 6.94 0.00 -100.00 9.44 0.00 -100.00
recursive calls 229,924 54,921 101.61 6,237.51 6,038.68 63.83 15.25 -76.11 86.83 89.16 2.68
recursive cpu usage 6,302 345 2.79 39.18 1,304.30 1.75 0.10 -94.29 2.38 0.56 -76.47
redo blocks written 19,654 7,906 8.69 897.90 10,232.57 5.46 2.20 -59.71 7.42 12.83 72.91
redo entries 18,978 8,573 8.39 973.66 11,505.01 5.27 2.38 -54.84 7.17 13.92 94.14
redo ordering marks 366 169 0.16 19.19 11,893.75 0.10 0.05 -50.00 0.14 0.27 92.86
redo subscn max counts 1,138 793 0.50 90.06 17,912.00 0.32 0.22 -31.25 0.43 1.29 200.00
redo synch time 1,014 257 0.45 29.19 6,386.67 0.28 0.07 -75.00 0.38 0.42 10.53
redo synch writes 5,727 3,605 2.53 409.43 16,083.00 1.59 1.00 -37.11 2.16 5.85 170.83
redo wastage 1,156,788 215,036 511.22 24,422.15 4,677.23 321.13 59.71 -81.41 436.85 349.08 -20.09
redo write time 1,348 524 0.60 59.51 9,818.33 0.37 0.15 -59.46 0.51 0.85 66.67
redo writer latching time 1 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
redo writes 2,821 665 1.25 75.53 5,942.40 0.78 0.18 -76.92 1.07 1.08 0.93
rollback changes - undo records applied 56 36 0.02 4.09 20,350.00 0.02 0.01 -50.00 0.02 0.06 200.00
rollbacks only - consistent read gets 580 1,016 0.26 115.39 44,280.77 0.16 0.28 75.00 0.22 1.65 650.00
rows fetched via callback 22,003 4,679 9.72 531.41 5,367.18 6.11 1.30 -78.72 8.31 7.60 -8.54
session pga memory 72,944,964 74,177,456 32,236.76 8,424,509.92 26,033.24 20,249.82 20,598.05 1.72 27,547.19 120,417.95 337.13
session pga memory max 183,438,660 365,288,368 81,067.54 41,486,667.85 51,075.44 50,923.33 101,435.49 99.19 69,274.42 593,000.60 756.02
session uga memory ############### ############### 113,884,152.41 29,267,625,476.92 25,599.47 71,537,387.32 71,559,757.93 0.03 97,317,105.94 418,344,501.76 329.88
session uga memory max 74,697,244 58,593,148 33,011.15 6,654,563.03 20,058.53 20,736.26 16,270.50 -21.54 28,208.93 95,118.75 237.19
shared hash latch upgrades - no wait 11,807 9,391 5.22 1,066.56 20,332.18 3.28 2.61 -20.43 4.46 15.25 241.93
sorts (memory) 8,743 5,214 3.86 592.17 15,241.19 2.43 1.45 -40.33 3.30 8.46 156.36
sorts (rows) 74,366 21,699 32.86 2,464.41 7,399.73 20.64 6.03 -70.78 28.08 35.23 25.46
sql area evicted 2,251 0 0.99 0.00 -100.00 0.62 0.00 -100.00 0.85 0.00 -100.00
summed dirty queue length 2 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
switch current to new buffer 155 148 0.07 16.81 23,914.29 0.04 0.04 0.00 0.06 0.24 300.00
table fetch by rowid 60,683 8,874 26.82 1,007.84 3,657.79 16.85 2.46 -85.40 22.92 14.41 -37.13
table fetch continued row 285 0 0.13 0.00 -100.00 0.08 0.00 -100.00 0.11 0.00 -100.00
table scan blocks gotten 50,752,718 5,352 22,429.29 607.84 -97.29 14,089.16 1.49 -99.99 19,166.43 8.69 -99.95
table scan rows gotten 8,416,185,963 82,768 3,719,387.70 9,400.16 -99.75 2,336,367.91 22.98 -100.00 3,178,317.96 134.36 -100.00
table scans (long tables) 4,002 0 1.77 0.00 -100.00 1.11 0.00 -100.00 1.51 0.00 -100.00
table scans (short tables) 2,219 1,767 0.98 200.68 20,377.55 0.62 0.49 -20.97 0.84 2.87 241.67
total number of times SMON posted 3 1 0.00 0.11 100.00 0.00 0.00 0.00 0.00 0.00 0.00
transaction rollbacks 34 34 0.02 3.86 19,200.00 0.01 0.01 0.00 0.01 0.06 500.00
undo change vector size 2,894,020 1,463,040 1,278.96 166,160.93 12,891.88 803.39 406.27 -49.43 1,092.91 2,375.06 117.32

Back to Instance Activity Statistics
Back to Top

 

IO Stats

Back to Top

Tablespace IO Stats

  • Ordered by Differences of normalized IOs (Avg Reads/Sec + Avg Writes/Sec) desc
  • Only contains tablespaces with at least 100 IOs
  • 'N/A' indicates the tablespace did not exist in the database for the period
  Avg Reads / Sec Avg Writes / Sec Reads Avg Read Time (ms) Avg Blocks / Read Writes Buffer Waits Avg Buf Wait Time (ms)
Tablespace 1st 2nd %Diff 1st 2nd %Diff 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd
SYSAUX 0.28 0.00 -100.00 0.34 0.20 -41.18 1,007 4 1.30 0.00 1.04 1.00 1,233 705 0 0 0.00 0.00
SYSTEM 0.38 0.00 -100.00 0.03 0.02 -33.33 1,363 0 1.83 0.00 1.40 0.00 91 78 0 0 0.00 0.00
USERS 502.00 0.00 -100.00 0.00 0.00 0.00 1,808,336 0 0.20 0.00 13.33 0.00 3 0 4,468,162 0 0.18 0.00
UNDOTBS1 0.00 0.00 0.00 0.07 0.06 -14.29 10 0 1.00 0.00 1.00 0.00 235 207 0 0 0.00 0.00

Back to IO Stats
Back to Top

Top 10 File Comparison by IO

  • Ordered by files with greatest normalized IOs (Avg Reads/Sec + Avg Writes/Sec) from either period descending
  • Only contains files with at least 100 IOs
    Avg Reads / Sec Avg Writes / Sec Reads Avg Read Time (ms) Avg Blocks / Read Writes Buffer Waits Avg Buf Wait Time (ms)
Tablespace Filename 1st 2nd %Diff 1st 2nd %Diff 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd
USERS /opt/app/oracle/oradata/o102ee1/users01.dbf 502.00 0.00 -100.00 0.00 0.00 0.00 1,808,336 0 0.20 0.00 13.33 0.00 3 0 4,468,162 0 0.18 0.00
SYSAUX /opt/app/oracle/oradata/o102ee1/sysaux01.dbf 0.28 0.00 -100.00 0.34 0.20 -41.18 1,007 4 1.30 0.00 1.04 1.00 1,233 705 0 0 0.00 0.00
SYSTEM /opt/app/oracle/oradata/o102ee1/system01.dbf 0.38 0.00 -100.00 0.03 0.02 -33.33 1,363 0 1.83 0.00 1.40 0.00 91 78 0 0 0.00 0.00
UNDOTBS1 /opt/app/oracle/oradata/o102ee1/undotbs01.dbf 0.00 0.00 0.00 0.07 0.06 -14.29 10 0 1.00 0.00 1.00 0.00 235 207 0 0 0.00 0.00

Back to IO Stats
Back to Top

Top 10 File Comparison by Read Time

  • Ordered by files with greatest 'Read Time % of DB Time' from either period descending
  • Only contains files with at least 100 IOs
    Read Time % of DB Time Avg Read Time (ms) Reads Avg Reads / Sec Avg Blocks / Read Writes Avg Writes / Sec Buffer Waits Avg Buf Wait Time (ms)
Tablespace Filename 1st 2nd Diff 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd
USERS /opt/app/oracle/oradata/o102ee1/users01.dbf 15.92 0.00 -15.92 0.20 0.00 1,808,336 0 502.00 0.00 13.33 0.00 3 0 0.00 0.00 4,468,162 0 0.02 0.00
SYSTEM /opt/app/oracle/oradata/o102ee1/system01.dbf 0.11 0.00 -0.11 1.83 0.00 1,363 0 0.38 0.00 1.40 0.00 91 78 0.03 0.02 0 0 0.00 0.00
SYSAUX /opt/app/oracle/oradata/o102ee1/sysaux01.dbf 0.06 0.00 -0.06 1.30 0.00 1,007 4 0.28 0.00 1.04 1.00 1,233 705 0.34 0.20 0 0 0.00 0.00
UNDOTBS1 /opt/app/oracle/oradata/o102ee1/undotbs01.dbf 0.00 0.00 -0.00 1.00 0.00 10 0 0.00 0.00 1.00 0.00 235 207 0.07 0.06 0 0 0.00 0.00

Back to IO Stats
Back to Top

Top 10 File Comparison by Buffer Waits

  • Ordered by files with greatest 'Wait Time % of DB Time' from either period descending
  • Contains only entries with at least 1 second of wait time
    Wait Time % of DB Time Buffer Waits Avg Buf Wait Time (ms) Reads Avg Reads / Sec Avg Read Time (ms) Avg Blocks / Read Writes Avg Writes / Sec
Tablespace Filename 1st 2nd Diff 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd
USERS /opt/app/oracle/oradata/o102ee1/users01.dbf 34.72 0.00 -34.72 4,468,162 0 0.02 0.00 1,808,336 0 502.00 0.00 0.20 0.00 13.33 0.00 3 0 0 0

Back to IO Stats
Back to Top

 

Advisory Statistics

Back to Top

PGA Aggregate Summary

  • PGA Cache Hit % - percentage of W/A (Work Area) data processed in-memory only
PGA Cache Hit % W/A Bytes/Sec (DB Time) W/A MB Processed Read/Written W/A Bytes/Sec (DB Time) Read/Written W/A MB
1st 2nd Diff 1st 2nd %Diff 1st 2nd 1st 2nd %Diff 1st 2nd
100.00 100.00 0.00 68,838 3,484,524 4,961.89 149 29 0 0 0.00 0 0

Back to Advisory Statistics
Back to Top

PGA Aggr Target Stats

  • B1: 1st begin snap E1: 1st end snap B2: 2nd begin snap E2: 2nd end snap
  • Data shown in the table represent absolute value, 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)
B1 16 4 68.0 0.0 0.0 0.0 0.0 512
E1 16 4 60.6 0.0 0.0 0.0 0.0 3,276
                 
B2 16 4 59.9 0.0 0.0 0.0 0.0 3,276
E2 16 4 60.8 0.0 0.0 0.0 0.0 3,276

Back to Advisory Statistics
Back to Top

 

Wait Stats

Back to Top

Buffer Wait Statistics

  • Ordered by 'Diff' column of 'Wait Time % of DB time' descending
  Wait Time % DB Time Total Wait Time (s) # Waits Avg Wait Time (ms)
Class 1st 2nd Diff 1st 2nd 1st 2nd 1st 2nd %Diff
data block 0.35 0.00 -0.35 785.68 0.00 4,467,916 0 0.00 0.00 0.00

Back to Wait Stats
Back to Top

 

Enqueue Activity

No data exists for this section of the report.

Back to Wait Stats
Back to Top

 

Latch Sleep Breakdown

  • Ordered by 'Diff' Column of '% of Total Sleeps' descending
  % of Total Sleeps Get Requests Misses Spin Gets Sleeps Sleep1 Sleep2 Sleep3
Latch Name 1st 2nd Diff 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd
slave class create 0.00 100.00 100.00 12 12 0 1 0 0 0 1 0 0 0 0 0 0
cache buffers chains 95.37 0.00 -95.37 130,790,194 302,821 18,163,319 0 18,162,710 0 659 0 0 0 0 0 0 0
simulator lru latch 3.76 0.00 -3.76 6,341,903 9,650 1,077,163 0 1,077,139 0 26 0 0 0 0 0 0 0
shared pool 0.72 0.00 -0.72 752,855 6,249 469 0 464 0 5 0 0 0 0 0 0 0
session idle bit 0.14 0.00 -0.14 218,432 17,770 707 0 706 0 1 0 0 0 0 0 0 0


Back to Top

 

Segment Statistics

Back to Top

Top 5 Segments Comparison by Logical Reads

  • Ordered by 'Diff' column of '% of Total Logical Reads'
  • 'N/A' indicates no data was captured for the segment in the period
          % of Total Logical Reads Logical Reads
Owner Tablespace Object Name Subobject Name Type 1st 2nd Diff 1st 2nd
SCOTT USERS EMP   TABLE 99.06   -99.06 50,629,632  
SYS SYSAUX SYS_IOT_TOP_8813   INDEX 0.10 40.05 39.95 52,272 51,904
SYSMAN SYSAUX MGMT_CURRENT_METRICS_PK   INDEX 0.02 9.51 9.49 9,200 12,320
SYSMAN SYSAUX MGMT_METRICS_RAW_PK   INDEX 0.01 5.98 5.96 7,072 7,744
SYS SYSTEM SYS_C00648   INDEX 0.01 3.90 3.89 5,664 5,056
SYSMAN SYSAUX MGMT_CURRENT_METRICS_IDX_01   INDEX 0.01 3.15 3.14 2,704 4,080
SCOTT USERS EMP3   TABLE 0.31   -0.31 155,888  
SCOTT USERS PK_EMP2   INDEX 0.06   -0.06 30,432  
SCOTT USERS IND_EMP1_EMP1NO   INDEX 0.05   -0.05 26,976  

Back to Segment Statistics
Back to Top

Top 5 Segments Comparison by Physical Reads

  • Ordered by 'Diff' column of '% of Total Physical Reads'
  • 'N/A' indicates no data was captured for the segment in the period
          % of Total Physical Reads Physical Reads
Owner Tablespace Object Name Subobject Name Type 1st 2nd Diff 1st 2nd
SCOTT USERS EMP   TABLE 99.97   -99.97 24,095,340  
SYS SYSAUX WRH$_SERVICE_WAIT_CLASS_PK WRH$_SERVIC_2384218455_5500 INDEX PARTITION   20.00 20.00   1
SYS SYSAUX WRH$_SYSTEM_EVENT WRH$_SYSTEM_2384218455_5500 TABLE PARTITION   20.00 20.00   1
SYSMAN SYSAUX SYS_IOT_OVER_10485   TABLE 0.00 20.00 20.00 3 1
SCOTT USERS EMP3   TABLE 0.01   -0.01 3,100  
SYS SYSTEM TAB$   TABLE 0.00 0.00 -0.00 583 0
SYS SYSAUX WRI$_ADV_PARAMETERS_PK   INDEX 0.00 0.00 -0.00 256 0
SYS SYSAUX WRH$_SQL_PLAN   TABLE 0.00   -0.00 159  
SYS SYSTEM USER$   TABLE   0.00 0.00   0

Back to Segment Statistics
Back to Top

Top 5 Segments by Row Lock Waits

  • Ordered by 'Owner', 'Object Name' and 'Subobject' columns
  • 'N/A' indicates no data was captured for the segment in the period
          Row Lock Waits % of Captured
Owner Tablespace Object Name Subobject Name Type 1st 2nd 1st 2nd Diff
SYS SYSAUX WRH$_SEG_STAT_PK WRH$_SEG_ST_2384218455_5500 INDEX PARTITION   1   100.00 100.00

Back to Segment Statistics
Back to Top

 

Top 5 Segments by ITL Waits

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

 

Top 5 Segments by Buffer Busy Waits

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

 

Dictionary Cache Statistics

  • Ordered by cache
  Get Requests / Sec (DB Time) Get Requests / Sec (Elapsed Time) Get Requests Pct Miss Scan Requests Pct Miss Mod Reqs Final Usage
Cache 1st 2nd %Diff 1st 2nd %Diff 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd
dc_awr_control 0.03 7.50 24,900.00 0.02 0.02 0.00 74 66 4.05 0.00 0 0 0.00 0.00 6 2 1 1
dc_global_oids 2.30 567.18 24,560.00 1.45 1.39 -4.14 5,210 4,994 0.56 0.00 0 0 0.00 0.00 0 0 22 52
dc_histogram_data 2.40 0.00 -100.00 1.51 0.00 -100.00 5,442 0 22.86 0.00 0 0 0.00 0.00 0 0 988 3,652
dc_histogram_defs 9.91 0.00 -100.00 6.22 0.00 -100.00 22,418 0 9.41 0.00 0 0 0.00 0.00 0 0 1,758 2,969
dc_object_grants 0.09 0.00 -100.00 0.06 0.00 -100.00 202 0 37.13 0.00 0 0 0.00 0.00 0 0 59 54
dc_object_ids 25.95 668.49 2,476.07 16.30 1.63 -90.00 58,725 5,886 0.51 0.00 0 0 0.00 0.00 0 0 492 1,018
dc_objects 4.67 96.20 1,959.96 2.93 0.24 -91.81 10,558 847 4.73 0.00 0 0 0.00 0.00 0 0 557 862
dc_profiles 0.04 9.09 22,625.00 0.02 0.02 0.00 88 80 0.00 0.00 0 0 0.00 0.00 0 0 1 1
dc_rollback_segments 0.15 38.16 25,340.00 0.09 0.09 0.00 337 336 0.00 0.00 0 0 0.00 0.00 0 0 12 12
dc_segments 11.18 1.59 -85.78 7.02 0.00 -100.00 25,291 14 1.37 0.00 0 0 0.00 0.00 9 4 372 938
dc_sequences 0.00 0.11 100.00 0.00 0.00 0.00 6 1 33.33 0.00 0 0 0.00 0.00 6 1 1 4
dc_tablespaces 1.91 93.13 4,775.92 1.20 0.23 -80.83 4,325 820 0.14 0.00 0 0 0.00 0.00 0 0 5 6
dc_usernames 0.26 4.88 1,776.92 0.16 0.01 -93.75 579 43 1.21 0.00 0 0 0.00 0.00 0 0 5 5
dc_users 8.55 859.52 9,952.87 5.37 2.10 -60.89 19,354 7,568 0.13 0.00 0 0 0.00 0.00 0 0 26 23
outstanding_alerts 0.03 8.40 27,900.00 0.02 0.02 0.00 74 74 14.86 1.35 0 0 0.00 0.00 2 2 10 10


Back to Top

 

Library Cache Activity

  • Ordered by namespace
  Get Requests / Sec (DB Time) Get Requests / Sec (Elapsed Time) Get Requests Pct Miss Pin Requests Pct Miss Reloads Invalidations
Namespace 1st 2nd %Diff 1st 2nd %Diff 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd
BODY 0.31 67.80 21,770.97 0.19 0.17 -10.53 698 597 0.86 0.00 6,595 6,160 0.44 0.00 23 0 0 0
CLUSTER 0.03 0.00 -100.00 0.02 0.00 -100.00 73 0 0.00   168 0 1.79   3 0 0 0
INDEX 0.01 0.00 -100.00 0.00 0.00 0.00 17 0 35.29   47 0 12.77   0 0 0 0
SQL AREA 8.55 0.00 -100.00 5.37 0.00 -100.00 19,339 0 95.34   141,769 19,651 39.67 0.02 452 0 115 0
TABLE/PROCEDURE 1.00 13.74 1,274.00 0.63 0.03 -95.24 2,257 121 3.94 0.00 70,006 9,198 1.84 0.00 654 0 0 0
TRIGGER 0.03 4.20 13,900.00 0.02 0.01 -50.00 61 37 1.64 0.00 363 353 2.20 0.00 6 0 0 0


Back to Top

 

SGA Statistics

Back to Top

SGA Memory Summary

  Size (KB)
SGA regions 1st 2nd
Database Buffers 94,208.00 94,208.00
Fixed Size 1,231.69 1,231.69
Redo Buffers 6,960.00 6,960.00
Variable Size 159,744.31 159,744.31

Back to SGA Statistics
Back to Top

SGA breakdown difference

    1st 2nd    
Pool Name Begin Value End Value %Diff Begin Value End Value %Diff %Diff of Begin Values %Diff of End Values
java free memory 4,194,304 4,194,304 0.00 4,194,304 4,194,304 0.00 0.00 0.00
large PX msg pool 1,074,012 1,074,012 0.00 1,074,012 1,074,012 0.00 0.00 0.00
large free memory 3,120,292 3,120,292 0.00 3,120,292 3,120,292 0.00 0.00 0.00
shared ASH buffers 5,368,712 5,368,712 0.00 5,368,712 5,368,712 0.00 0.00 0.00
shared CCursor 8,239,208 11,818,812 43.45 7,420,760 7,420,760 0.00 -9.93 -37.21
shared Heap0: KGL 4,285,280 3,362,972 -21.52 2,435,312 2,435,312 0.00 -43.17 -27.58
shared KCB Table Scan Buffer 3,981,204 3,981,204 0.00 3,981,204 3,981,204 0.00 0.00 0.00
shared KGLS heap 4,379,536 2,462,872 -43.76 4,258,056 4,258,056 0.00 -2.77 72.89
shared KSFD SGA I/O b 3,977,140 3,977,140 0.00 3,977,140 3,977,140 0.00 0.00 0.00
shared KTI-UNDO 3,774,540 3,774,540 0.00 3,774,540 3,774,540 0.00 0.00 0.00
shared PCursor 3,903,460 7,693,268 97.09 4,513,708 4,513,708 0.00 15.63 -41.33
shared PL/SQL MPCODE 4,584,008 2,071,076 -54.82 4,209,916 4,209,916 0.00 -8.16 103.27
shared event statistics per sess 4,032,000 4,032,000 0.00 4,032,000 4,032,000 0.00 0.00 0.00
shared free memory 17,881,224 16,522,568 -7.60 24,616,512 24,616,512 0.00 37.67 48.99
shared kglsim hash table bkts 2,097,152 2,097,152 0.00 2,097,152 2,097,152 0.00 0.00 0.00
shared library cache 8,165,672 10,127,460 24.02 7,772,944 7,772,944 0.00 -4.81 -23.25
shared private strands 3,660,800 3,660,800 0.00 3,660,800 3,660,800 0.00 0.00 0.00
shared row cache 3,755,444 3,755,444 0.00 3,755,444 3,755,444 0.00 0.00 0.00
shared sessions 2,410,004 2,410,004 0.00 2,410,004 2,410,004 0.00 0.00 0.00
shared sql area 37,622,196 7,820,060 -79.21 31,342,608 31,342,608 0.00 -16.69 300.80
  buffer_cache 96,468,992 96,468,992 0.00 96,468,992 96,468,992 0.00 0.00 0.00
  fixed_sga 1,261,248 1,261,248 0.00 1,261,248 1,261,248 0.00 0.00 0.00
  log_buffer 7,127,040 7,127,040 0.00 7,127,040 7,127,040 0.00 0.00 0.00

Back to SGA Statistics
Back to Top

 

init.ora Parameters

  • Only parameters that are set differently are shown for the second period.
Parameter Name 1st End Snap Value 2nd End Snap Value (if different)
O7_DICTIONARY_ACCESSIBILITY FALSE  
__db_cache_size 96468992  
__dg_broker_service_names o102ee1_XPT  
__java_pool_size 4194304  
__large_pool_size 4194304  
__shared_pool_size 155189248  
__streams_pool_size 0  
aq_tm_processes 0  
archive_lag_target 0  
asm_power_limit 1  
audit_file_dest /opt/app/oracle/admin/o102ee1/ad  
audit_sys_operations FALSE  
audit_trail NONE  
background_core_dump partial  
background_dump_dest /opt/app/oracle/admin/o102ee1/bd  
backup_tape_io_slaves FALSE  
bitmap_merge_area_size 1048576  
blank_trimming FALSE  
cluster_database FALSE  
cluster_database_instances 1  
commit_point_strength 1  
compatible 10.2.0.3.0  
control_file_record_keep_time 7  
control_files /opt/app/oracle/oradata/o102ee1/  
core_dump_dest /opt/app/oracle/admin/o102ee1/cd  
cpu_count 4  
create_bitmap_area_size 8388608  
cursor_sharing EXACT  
cursor_space_for_time FALSE  
db_16k_cache_size 0  
db_2k_cache_size 0  
db_32k_cache_size 0  
db_4k_cache_size 0  
db_8k_cache_size 0  
db_block_buffers 0  
db_block_checking FALSE  
db_block_checksum TRUE  
db_block_size 8192  
db_cache_advice ON  
db_cache_size 0  
db_file_multiblock_read_count 41  
db_files 200  
db_flashback_retention_target 1440  
db_keep_cache_size 0  
db_name o102ee1  
db_recovery_file_dest /opt/app/oracle/flash_recovery_a  
db_recovery_file_dest_size 2147483648  
db_recycle_cache_size 0  
db_unique_name o102ee1  
db_writer_processes 1  
dbwr_io_slaves 0  
ddl_wait_for_locks FALSE  
dg_broker_config_file1 /opt/app/oracle/product/10.2.0/d  
dg_broker_config_file2 /opt/app/oracle/product/10.2.0/d  
dg_broker_start FALSE  
disk_asynch_io TRUE  
distributed_lock_timeout 60  
dml_locks 2200  
drs_start FALSE  
fast_start_io_target 0  
fast_start_mttr_target 0  
fast_start_parallel_rollback LOW  
file_mapping FALSE  
filesystemio_options none  
gcs_server_processes 0  
global_names FALSE  
hash_area_size 131072  
hi_shared_memory_address 0  
hs_autoregister TRUE  
instance_name o102ee1  
instance_number 0  
instance_type RDBMS  
java_max_sessionspace_size 0  
java_pool_size 0  
java_soft_sessionspace_limit 0  
job_queue_processes 10  
large_pool_size 0  
ldap_directory_access NONE  
license_max_sessions 0  
license_max_users 0  
license_sessions_warning 0  
lock_sga FALSE  
log_archive_dest_state_1 enable  
log_archive_dest_state_10 enable  
log_archive_dest_state_2 enable  
log_archive_dest_state_3 enable  
log_archive_dest_state_4 enable  
log_archive_dest_state_5 enable  
log_archive_dest_state_6 enable  
log_archive_dest_state_7 enable  
log_archive_dest_state_8 enable  
log_archive_dest_state_9 enable  
log_archive_format %t_%s_%r.dbf  
log_archive_local_first TRUE  
log_archive_max_processes 2  
log_archive_min_succeed_dest 1  
log_archive_start FALSE  
log_archive_trace 0  
log_buffer 7016448  
log_checkpoint_interval 0  
log_checkpoint_timeout 1800  
log_checkpoints_to_alert FALSE  
logmnr_max_persistent_sessions 1  
max_commit_propagation_delay 0  
max_dump_file_size UNLIMITED  
max_enabled_roles 150  
nls_language JAPANESE  
nls_length_semantics BYTE  
nls_nchar_conv_excp FALSE  
nls_territory JAPAN  
object_cache_max_size_percent 10  
object_cache_optimal_size 102400  
olap_page_pool_size 0  
open_cursors 1024  
open_links 4  
open_links_per_instance 4  
optimizer_dynamic_sampling 2  
optimizer_features_enable 10.2.0.3  
optimizer_index_caching 0  
optimizer_index_cost_adj 100  
optimizer_mode ALL_ROWS  
optimizer_secure_view_merging TRUE  
os_authent_prefix ops$  
os_roles FALSE  
parallel_adaptive_multi_user TRUE  
parallel_automatic_tuning FALSE  
parallel_execution_message_size 2148  
parallel_max_servers 80  
parallel_min_percent 0  
parallel_min_servers 0  
parallel_server FALSE  
parallel_server_instances 1  
parallel_threads_per_cpu 2  
pga_aggregate_target 16777216  
plsql_code_type INTERPRETED  
plsql_compiler_flags INTERPRETED, NON_DEBUG  
plsql_debug FALSE  
plsql_native_library_subdir_count 0  
plsql_optimize_level 2  
plsql_v2_compatibility FALSE  
plsql_warnings DISABLE:ALL  
pre_page_sga FALSE  
processes 150  
query_rewrite_enabled TRUE  
query_rewrite_integrity enforced  
read_only_open_delayed FALSE  
recovery_parallelism 0  
recyclebin on  
remote_archive_enable true  
remote_dependencies_mode TIMESTAMP  
remote_login_passwordfile EXCLUSIVE  
remote_os_authent FALSE  
remote_os_roles FALSE  
replication_dependency_tracking TRUE  
resource_limit FALSE  
resumable_timeout 0  
serial_reuse disable  
service_names o102ee1  
session_cached_cursors 20  
session_max_open_files 10  
sessions 500  
sga_max_size 268435456  
sga_target 268435456  
shadow_core_dump partial  
shared_memory_address 0  
shared_pool_reserved_size 3984588  
shared_pool_size 0  
shared_servers 0  
skip_unusable_indexes TRUE  
sort_area_retained_size 0  
sort_area_size 65536  
sql92_security FALSE  
sql_trace FALSE  
sql_version NATIVE  
sqltune_category DEFAULT  
standby_archive_dest ?/dbs/arch  
standby_file_management MANUAL  
star_transformation_enabled FALSE  
statistics_level TYPICAL  
streams_pool_size 0  
tape_asynch_io TRUE  
thread 0  
timed_os_statistics 0  
timed_statistics TRUE  
trace_enabled TRUE  
transactions 550  
transactions_per_rollback_segment 5  
undo_management AUTO  
undo_retention 900  
undo_tablespace UNDOTBS1  
use_indirect_data_buffers FALSE  
user_dump_dest /opt/app/oracle/admin/o102ee1/ud  
workarea_size_policy AUTO  


Back to Top