Home » RDBMS Server » Performance Tuning » awr report
| awr report [message #452911] |
Mon, 26 April 2010 03:38  |
zodiacsom
Messages: 37
Registered: December 2009
Location: pune
|
Member |
|
|
Hi All,
I have awr report with me nd need to tune my database but i dont have expert level of knowlege about awr , so plesae help me out in study and how to start awr report to trace the performance issue.
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst num Startup Time Release RAC
MPQA 1846971816 mpqa 1 21-Nov-09 15:11 11.1.0.6.0 NO
Host Name Platform CPUs Cores Sockets Memory (GB)
n01dou402 AIX-Based Systems (64-bit) 8 3 10.00
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 82 24-Nov-09 08:00:23 61 6.9
End Snap: 86 24-Nov-09 12:00:35 57 7.0
Elapsed: 240.20 (mins)
DB Time: 1,022.32 (mins)
Report Summary
Cache Sizes
Begin End
Buffer Cache: 1,664M 1,664M Std Block Size: 8K
Shared Pool Size: 2,880M 2,880M Log Buffer: 25,984K
Load Profile
Per Second Per Transaction Per Exec Per Call
DB Time(s): 4.3 14.4 0.04 0.02
DB CPU(s): 1.5 5.2 0.01 0.01
Redo size: 9,795.7 33,031.8
Logical reads: 25,368.3 85,543.6
Block changes: 62.5 210.8
Physical reads: 0.2 0.6
Physical writes: 1.9 6.4
User calls: 255.6 861.9
Parses: 49.5 167.0
Hard parses: 11.8 39.8
W/A MB processed: 31,928.9 107,666.2
Logons: 0.0 0.0
Executes: 113.5 382.6
Rollbacks: 0.0 0.0
Transactions: 0.3
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 80.07 Soft Parse %: 76.15
Execute to Parse %: 56.35 Latch Hit %: 99.81
Parse CPU to Parse Elapsd %: 0.00 % Non-Parse CPU: 99.86
Shared Pool Statistics
Begin End
Memory Usage %: 16.41 20.86
% SQL with executions>1: 70.69 12.04
% Memory for SQL w/exec>1: 81.71 16.99
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU 22,125 36.07
latch: cache buffers chains 3,774 79 21 0.13 Concurrency
log file sync 12,375 36 3 0.06 Commit
latch free 937 20 21 0.03 Other
SQL*Net break/reset to client 24,586 17 1 0.03 Application
Host CPU (CPUs: 8 Cores: 3 Sockets: )
Load Average Begin Load Average End %User %System %WIO %Idle
0.54 9.85 59.4 1.5 0.0 39.0
Instance CPU
%Total CPU %Busy CPU %DB time waiting for CPU (Resource Manager)
33.3 54.6 0.0
Memory Statistics
Begin End
Host Mem (MB): 10,240.0 10,240.0
SGA use (MB): 4,864.0 4,864.0
PGA use (MB): 221.4 216.4
% Host Mem used for SGA+PGA: 49.66 49.66
Main Report
Report Summary
Wait Events Statistics
SQL Statistics
Instance Activity Statistics
IO Stats
Buffer Pool Statistics
Advisory Statistics
Wait Statistics
Undo Statistics
Latch Statistics
Segment Statistics
Dictionary Cache Statistics
Library Cache Statistics
Memory Statistics
Streams Statistics
Resource Limit Statistics
init.ora Parameters
Back to Top
Wait Events Statistics
Time Model Statistics
Operating System Statistics
Operating System Statistics - Detail
Foreground Wait Class
Foreground Wait Events
Background Wait Events
Wait Event Histogram
Service Statistics
Service Wait Class Stats
Back to Top
Time Model Statistics
Total time in database user-calls (DB Time): 61339.3s
Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
sql execute elapsed time 60,895.67 99.28
DB CPU 22,124.69 36.07
parse time elapsed 420.55 0.69
hard parse elapsed time 353.86 0.58
hard parse (sharing criteria) elapsed time 2.16 0.00
PL/SQL execution elapsed time 1.48 0.00
repeated bind elapsed time 1.24 0.00
Java execution elapsed time 1.21 0.00
PL/SQL compilation elapsed time 0.90 0.00
connection management call elapsed time 0.40 0.00
sequence load elapsed time 0.25 0.00
hard parse (bind mismatch) elapsed time 0.20 0.00
failed parse elapsed time 0.01 0.00
DB time 61,339.35
background elapsed time 147.84
background cpu time 46.16
Back to Wait Events Statistics
Back to Top
Operating System Statistics
*TIME statistic values are diffed. All others display actual values. End Value is displayed if different
ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name
Statistic Value End Value
AVG_BUSY_TIME 782,318
AVG_IDLE_TIME 558,648
AVG_IOWAIT_TIME 131
AVG_SYS_TIME 20,921
AVG_USER_TIME 761,045
BUSY_TIME 4,062,714
IDLE_TIME 2,600,898
IOWAIT_TIME 1,313
SYS_TIME 101,825
USER_TIME 3,960,889
LOAD 1 10
OS_CPU_WAIT_TIME 6,270,700
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 10,737,418,240
NUM_CPUS 4 8
NUM_CPU_CORES 2 3
GLOBAL_RECEIVE_SIZE_MAX 1,048,576
GLOBAL_SEND_SIZE_MAX 1,048,576
TCP_RECEIVE_SIZE_DEFAULT 16,384
TCP_RECEIVE_SIZE_MAX 9,223,372,036,854,775,807
TCP_RECEIVE_SIZE_MIN 4,096
TCP_SEND_SIZE_DEFAULT 16,384
TCP_SEND_SIZE_MAX 9,223,372,036,854,775,807
TCP_SEND_SIZE_MIN 4,096
Back to Wait Events Statistics
Back to Top
Operating System Statistics - Detail
Snap Time Load %busy %user %sys %idle %iowait
24-Nov 08:00:23 0.54
24-Nov 09:00:26 1.15 45.20 43.55 1.65 0.03 54.80
24-Nov 10:00:29 1.14 55.67 53.99 1.68 0.02 44.33
24-Nov 11:00:32 6.13 63.49 61.74 1.74 0.02 36.51
24-Nov 12:00:35 9.85 73.15 71.96 1.19 0.01 26.85
Back to Wait Events Statistics
Back to Top
Foreground Wait Class
s - second, ms - millisecond - 1000th of a second
ordered by wait time desc, waits desc
%Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
Captured Time accounts for 36.3% of Total DB time 61,339.35 (s)
Total FG Wait Time: 165.29 (s) DB CPU time: 22,124.69 (s)
Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) %DB time
DB CPU 22,125 36.07
Concurrency 44,767 90 88 2 0.14
Commit 12,375 0 36 3 0.06
Other 969 0 20 20 0.03
Application 24,586 0 17 1 0.03
Network 3,011,516 0 4 0 0.01
Configuration 20 60 0 3 0.00
User I/O 401 0 0 0 0.00
System I/O 0 0 0.00
Back to Wait Events Statistics
Back to Top
Foreground Wait Events
s - second, ms - millisecond - 1000th of a second
Only events with Total Wait Time (s) >= .001 are shown
ordered by wait time desc, waits desc (idle events last)
%Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn % DB time
latch: cache buffers chains 3,774 0 79 21 0.88 0.13
log file sync 12,375 0 36 3 2.90 0.06
latch free 937 0 20 21 0.22 0.03
SQL*Net break/reset to client 24,586 0 17 1 5.75 0.03
latch: shared pool 613 0 6 9 0.14 0.01
SQL*Net message to client 3,011,510 0 4 0 704.61 0.01
library cache: mutex X 39,508 100 3 0 9.24 0.00
latch: row cache objects 81 0 0 3 0.02 0.00
buffer busy waits 190 0 0 1 0.04 0.00
log file switch completion 8 0 0 8 0.00 0.00
db file scattered read 142 0 0 0 0.03 0.00
cursor: pin S wait on X 1 100 0 10 0.00 0.00
latch: cache buffers lru chain 1 0 0 8 0.00 0.00
db file sequential read 258 0 0 0 0.06 0.00
latch: enqueue hash chains 29 0 0 0 0.01 0.00
cursor: mutex X 600 100 0 0 0.14 0.00
SQL*Net message from client 3,011,514 0 425,318 141 704.61
jobq slave wait 356 99 1,036 2911 0.08
Back to Wait Events Statistics
Back to Top
Background Wait Events
ordered by wait time desc, waits desc (idle events last)
Only events with Total Wait Time (s) >= .001 are shown
%Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn % bg time
log file parallel write 14,263 0 29 2 3.34 19.32
db file parallel write 7,333 0 18 2 1.72 11.94
control file parallel write 5,265 0 16 3 1.23 10.85
os thread startup 96 0 9 98 0.02 6.37
Log archive I/O 328 0 2 5 0.08 1.17
control file sequential read 15,607 0 0 0 3.65 0.21
latch: shared pool 23 0 0 11 0.01 0.17
log file sequential read 164 0 0 1 0.04 0.14
events in waitclass Other 583 0 0 0 0.14 0.12
direct path write 24 0 0 1 0.01 0.02
buffer busy waits 13 0 0 0 0.00 0.00
log file single write 8 0 0 0 0.00 0.00
latch: cache buffers chains 1 0 0 3 0.00 0.00
rdbms ipc message 69,491 81 211,397 3042 16.26
DIAG idle wait 28,810 100 28,138 977 6.74
fbar timer 47 100 14,096 299909 0.01
Streams AQ: qmn coordinator idle wait 1,010 50 14,090 13951 0.24
Streams AQ: qmn slave idle wait 505 0 14,090 27901 0.12
pmon timer 4,870 99 14,069 2889 1.14
smon timer 66 52 13,942 211237 0.02
JOX Jit Process Sleep 2 100 13 6387 0.00
Back to Wait Events Statistics
Back to Top
Wait Event Histogram
Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
% of Waits: value of .0 indicates value was <.05%. Value of null is truly 0
% of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
Ordered by Event (idle events last)
% of Waits
Event Total Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
ARCH wait for archivelog lock 4 100.0
LGWR wait for redo copy 540 94.4 2.6 1.9 1.1
Log archive I/O 328 75.0 1.5 2.4 9.5 5.8 2.1 3.7
SQL*Net break/reset to client 24K 88.2 4.2 4.3 3.0 .2 .0 .0 .0
SQL*Net message to client 3011K 100.0 .0 .0 .0 .0
SQL*Net more data from client 5 100.0
SQL*Net more data to client 11 100.0
buffer busy waits 203 88.2 3.9 4.9 2.5 .5
control file parallel write 5266 18.6 52.9 10.8 11.6 5.5 .2 .3
control file sequential read 18K 100.0 .0 .0 .0 .0
cursor: mutex X 600 100.0
cursor: pin S 2 100.0
cursor: pin S wait on X 1 100.0
db file parallel read 1 100.0
db file parallel write 7333 55.9 11.7 14.9 10.2 7.0 .2 .1
db file scattered read 206 99.0 .5 .5
db file sequential read 396 100.0
direct path read 24 100.0
direct path write 32 84.4 6.3 6.3 3.1
latch free 950 23.9 1.7 3.8 11.6 21.3 21.7 16.1
latch: cache buffers chains 3775 31.2 1.6 3.9 8.6 18.5 19.8 16.4
latch: cache buffers lru chain 1 100.0
latch: enqueue hash chains 30 93.3 6.7
latch: object queue header operation 1 100.0
latch: redo allocation 2 100.0
latch: row cache objects 81 72.8 13.6 6.2 3.7 1.2 2.5
latch: session allocation 2 50.0 50.0
latch: shared pool 638 52.0 5.2 10.3 9.2 5.3 7.1 10.8
library cache: mutex X 40K 99.8 .0 .0 .1 .0 .0 .0
log file parallel write 14K 59.8 18.8 7.9 9.1 4.1 .1 .1
log file sequential read 164 34.8 63.4 .6 .6 .6
log file single write 8 100.0
log file switch completion 8 12.5 37.5 50.0
log file sync 12K 55.0 14.9 6.6 11.8 10.5 1.0 .2
os thread startup 96 1.0 99.0
rdbms ipc reply 23 69.6 4.3 13.0 13.0
undo segment extension 12 100.0
DIAG idle wait 28K 100.0
JOX Jit Process Sleep 58 1.7 3.4 94.8
SQL*Net message from client 3011K 65.4 10.7 10.2 8.0 1.4 .2 2.4 1.8
Space Manager: slave idle wait 2888 .1 99.9
Streams AQ: qmn coordinator idle wait 1010 43.4 1.2 1.9 2.9 .7 50.0
Streams AQ: qmn slave idle wait 505 100.0
class slave wait 55 52.7 18.2 14.5 10.9 3.6
dispatcher timer 235 100.0
fbar timer 47 100.0
jobq slave wait 356 .6 99.4
pmon timer 4871 .3 .9 .0 .1 98.6
rdbms ipc message 69K .8 .1 .1 .2 .3 .6 37.5 60.4
smon timer 66 3.0 97.0
virtual circuit status 470 100.0
Back to Wait Events Statistics
Back to Top
Service Statistics
ordered by DB Time
Service Name DB Time (s) DB CPU (s) Physical Reads (K) Logical Reads (K)
SYS$USERS 61,328 22,120 0 365,429
mpqa 2 1 2 10
SYS$BACKGROUND 0 0 1 127
mpqaXDB 0 0 0 0
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 seconds
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 54 0 44767 88 0 0 3011320 4
mpqa 347 0 0 0 0 0 173 0
SYS$BACKGROUND 264 0 1123 10 0 0 0 0
Back to Wait Events Statistics
Back to Top
SQL Statistics
SQL ordered by Elapsed Time
SQL ordered by CPU Time
SQL ordered by Gets
SQL ordered by Reads
SQL ordered by Executions
SQL ordered by Parse Calls
SQL ordered by Sharable Memory
SQL ordered by Version Count
Complete List of SQL Text
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
Total DB Time (s): 61,339
Captured SQL account for 98.8% of Total
Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
60,296 21,388 4,231 14.25 98.30 d92ts0124bscp select membership0_.AGREEMENT_...
105 39 9,399 0.01 0.17 gs2m7z4tncs8t update MONEY_SCHEDULER set AGR...
35 1 122,959 0.00 0.06 21tvtb1gu1n00 select contactpoi0_.Contact_pr...
26 2 24,582 0.00 0.04 cj2s9rkux0hqq select moneysched0_.AGREEMENT_...
23 2 12,290 0.00 0.04 2u2rnct1bzufh select membercove0_.MEMBERSHIP...
9 0 122,958 0.00 0.02 69n4gubwkr9ky select rolesinmem0_.CONTEXT_FI...
7 0 122,960 0.00 0.01 amn7d6akj2jrh select allnames0_.role_player_...
6 0 122,959 0.00 0.01 f03t8u1xdg5bv select partyregis0_.REGISTERED...
5 0 110,665 0.00 0.01 4t7tkmtzh0hn3 select financials0_.AGREEMENT_...
5 0 110,668 0.00 0.01 0d8za8ru194yt select contactpre0_.subject_ro...
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 is the CPU Time divided into the Total CPU Time times 100
Total CPU Time (s): 22,125
Captured SQL account for 96.9% of Total
CPU Time (s) Elapsed Time (s) Executions CPU per Exec (s) % Total % Total DB Time SQL Id SQL Module SQL Text
21,388 60,296 4,231 5.05 96.67 98.30 d92ts0124bscp select membership0_.AGREEMENT_...
39 105 9,399 0.00 0.18 0.17 gs2m7z4tncs8t update MONEY_SCHEDULER set AGR...
2 23 12,290 0.00 0.01 0.04 2u2rnct1bzufh select membercove0_.MEMBERSHIP...
2 3 71 0.02 0.01 0.00 1rswbxwhbpmr7 select decode(bitand(a.flags, ...
2 26 24,582 0.00 0.01 0.04 cj2s9rkux0hqq select moneysched0_.AGREEMENT_...
1 2 4 0.21 0.00 0.00 bunssq950snhf insert into wrh$_sga_target_ad...
1 35 122,959 0.00 0.00 0.06 21tvtb1gu1n00 select contactpoi0_.Contact_pr...
0 2 4 0.12 0.00 0.00 0f04wnwfz1bym DECLARE job BINARY_INTEGER := ...
0 1 12 0.04 0.00 0.00 7p94zfu5dhh8s select decode(bitand(a.flags, ...
0 4 12,294 0.00 0.00 0.01 a6u3yjca29nqc TOAD 9.7.2.5 declare m_stmt varchar2(512...
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: 365,613,419
Captured SQL account for 98.7% of Total
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
346,663,410 4,231 81,934.16 94.82 21387.61 60295.69 d92ts0124bscp select membership0_.AGREEMENT_...
5,784,009 122,959 47.04 1.58 0.52 35.43 21tvtb1gu1n00 select contactpoi0_.Contact_pr...
2,960,807 9,399 315.01 0.81 38.75 105.10 gs2m7z4tncs8t update MONEY_SCHEDULER set AGR...
738,707 122,958 6.01 0.20 0.08 9.34 69n4gubwkr9ky select rolesinmem0_.CONTEXT_FI...
686,254 24,582 27.92 0.19 1.56 25.72 cj2s9rkux0hqq select moneysched0_.AGREEMENT_...
639,503 12,290 52.03 0.17 2.12 23.09 2u2rnct1bzufh select membercove0_.MEMBERSHIP...
370,252 122,959 3.01 0.10 0.04 6.32 f03t8u1xdg5bv select partyregis0_.REGISTERED...
369,699 122,960 3.01 0.10 0.01 6.66 amn7d6akj2jrh select allnames0_.role_player_...
333,018 110,668 3.01 0.09 0.01 5.33 0d8za8ru194yt select contactpre0_.subject_ro...
239,047 24,660 9.69 0.07 0.06 3.69 7pkqxr43gxnff insert into CONTACT_POINT (TYP...
Back to SQL Statistics
Back to Top
SQL ordered by Reads
Total Disk Reads: 2,469
Captured SQL account for 34.8% of Total
Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
785 1 785.00 31.79 0.09 0.19 5sgs7q9pjnzg5 TOAD 9.7.2.5 SELECT table_name, table_own...
38 4 9.50 1.54 0.48 1.53 0f04wnwfz1bym DECLARE job BINARY_INTEGER := ...
19 96 0.20 0.77 0.02 0.03 3ktacv9r56b51 select owner#, name, namespace...
17 102 0.17 0.69 0.01 0.01 04xtrk7uyhknh select obj#, type#, ctime, mti...
9 4 2.25 0.36 0.29 0.74 bqnn4c3gjtmgu insert into wrh$_bg_event_summ...
6 1,068 0.01 0.24 0.37 0.88 7ng34ruy5awxq select i.obj#, i.ts#, i.file#,...
6 2 3.00 0.24 0.04 0.11 fnk7155mk2jq6 insert into wrh$_sysmetric_his...
4 135 0.03 0.16 0.03 0.04 39m4sx9k63ba2 select /*+ index(idl_ub2$ i_id...
4 135 0.03 0.16 0.02 0.04 ga9j9xk5cy9s0 select /*+ index(idl_sb4$ i_id...
2 4 0.50 0.08 0.07 0.15 1cq3qr774cu45 insert into WRH$_IOSTAT_FILETY...
Back to SQL Statistics
Back to Top
SQL ordered by Executions
Total Executions: 1,635,146
Captured SQL account for 78.8% of Total
Executions Rows Processed Rows per Exec CPU per Exec (s) Elap per Exec (s) SQL Id SQL Module SQL Text
122,960 122,960 1.00 0.00 0.00 amn7d6akj2jrh select allnames0_.role_player_...
122,959 737,742 6.00 0.00 0.00 21tvtb1gu1n00 select contactpoi0_.Contact_pr...
122,959 122,958 1.00 0.00 0.00 f03t8u1xdg5bv select partyregis0_.REGISTERED...
122,958 122,957 1.00 0.00 0.00 69n4gubwkr9ky select rolesinmem0_.CONTEXT_FI...
122,958 0 0.00 0.00 0.00 d9txynrwrf1sx select partyintro0_.ROLE_PLAYE...
110,668 110,668 1.00 0.00 0.00 0d8za8ru194yt select contactpre0_.subject_ro...
110,665 110,665 1.00 0.00 0.00 4t7tkmtzh0hn3 select financials0_.AGREEMENT_...
28,723 0 0.00 0.00 0.00 crgrgafjskcz4 select contractco0_.FINANCIAL_...
24,660 24,660 1.00 0.00 0.00 7pkqxr43gxnff insert into CONTACT_POINT (TYP...
24,616 24,616 1.00 0.00 0.00 7mnvpzybmajra select moneyprovi0_.MONEY_PROV...
24,616 24,616 1.00 0.00 0.00 8ww1g6rr0waht select hasparticu0_.AGREEMENT_...
24,583 24,582 1.00 0.00 0.00 21n5g1xda24d1 select postaladdr0_.CONTACT_PO...
24,582 24,582 1.00 0.00 0.00 11ygpwhuqf95t select electronic0_.CONTACT_PO...
24,582 24,584 1.00 0.00 0.00 852a2d3h25q92 select telephonen0_.CONTACT_PO...
24,582 12,291 0.50 0.00 0.00 cj2s9rkux0hqq select moneysched0_.AGREEMENT_...
Back to SQL Statistics
Back to Top
SQL ordered by Parse Calls
Total Parse Calls: 713,719
Captured SQL account for 56.6% of Total
Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
16,387 122,960 2.30 amn7d6akj2jrh select allnames0_.role_player_...
16,383 122,958 2.30 69n4gubwkr9ky select rolesinmem0_.CONTEXT_FI...
16,075 122,959 2.25 21tvtb1gu1n00 select contactpoi0_.Contact_pr...
16,069 122,958 2.25 d9txynrwrf1sx select partyintro0_.ROLE_PLAYE...
15,469 24,616 2.17 7mnvpzybmajra select moneyprovi0_.MONEY_PROV...
15,138 28,723 2.12 crgrgafjskcz4 select contractco0_.FINANCIAL_...
14,379 24,616 2.01 8ww1g6rr0waht select hasparticu0_.AGREEMENT_...
14,148 122,959 1.98 f03t8u1xdg5bv select partyregis0_.REGISTERED...
12,294 12,294 1.72 a6u3yjca29nqc TOAD 9.7.2.5 declare m_stmt varchar2(512...
12,292 12,292 1.72 4drzfdv0xz7d5 select payments0_.MONEY_SCHEDU...
12,292 12,292 1.72 5ujjrq53n2jqw select particular0_.MONEY_PROV...
12,292 12,292 1.72 792444k43js78 select moneyprovi0_.MONEY_PROV...
12,292 12,292 1.72 873gfuh45xdk8 select membership0_.AGREEMENT_...
12,292 12,292 1.72 anz3ykuv9rqbn select membership0_.AGREEMENT_...
12,292 12,292 1.72 bgw5sq5rghdvj select membership0_.SPECIFICAT...
12,292 12,292 1.72 fmrnwrzs58uqm select partyinvol0_.FINANCIAL_...
12,291 24,582 1.72 11ygpwhuqf95t select electronic0_.CONTACT_PO...
12,291 24,582 1.72 852a2d3h25q92 select telephonen0_.CONTACT_PO...
12,291 24,582 1.72 cj2s9rkux0hqq select moneysched0_.AGREEMENT_...
12,250 12,292 1.72 fuak1krkkm5dk select personname0_.party_name...
12,215 12,292 1.71 7hafjssbmdfmg select moneysched0_.MONEY_SCHE...
12,175 12,290 1.71 2u2rnct1bzufh select membercove0_.MEMBERSHIP...
12,166 12,292 1.70 dt7g7q82h9zpz select partyregis0_.PARTY_REGI...
12,140 12,291 1.70 8t28u0mpry0tb update FINANCIAL_TXN_MED set E...
11,329 12,292 1.59 fprmshrw6rd1j select payment0_.FINANCIAL_TRA...
10,800 24,583 1.51 21n5g1xda24d1 select postaladdr0_.CONTACT_PO...
8,986 8,986 1.26 3972rvxu3knn3 TOAD 9.7.2.5 delete from sdo_geor_ddl__tabl...
8,195 8,195 1.15 9xzqvm5wd0fd2 update AGREEMENT set SPECIFICA...
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
No data exists for this section of the report.
Back to SQL Statistics
Back to Top
Complete List of SQL Text
SQL Id SQL Text
04xtrk7uyhknh select obj#, type#, ctime, mtime, stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
0d8za8ru194yt select contactpre0_.subject_role_player_id as subject2_1_, contactpre0_.Contact_preference_id as Contact1_1_, contactpre0_.Contact_preference_id as Contact1_10_0_ from CONTACT_PREFERENCE contactpre0_ where contactpre0_.subject_role_player_id=:1
0f04wnwfz1bym DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN wksys.wk_job.invoke(22, 24); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
11ygpwhuqf95t select electronic0_.CONTACT_POINT_ID as Contact1_11_0_, electronic0_1_.TYPE_ID as TYPE2_11_0_, electronic0_.Address as Address13_0_, electronic0_.DOMAIN as DOMAIN13_0_, electronic0_.MAXIMUM_MESSAGE_SIZE as MAXIMUM3_13_0_, electronic0_.NODE as NODE13_0_, electronic0_.userid as userid13_0_ from ELECTRONIC_ADDRESS electronic0_, CONTACT_POINT electronic0_1_ where electronic0_.CONTACT_POINT_ID=electronic0_1_.Contact_point_id and electronic0_.CONTACT_POINT_ID=:1
1cq3qr774cu45 insert into WRH$_IOSTAT_FILETYPE (snap_id, dbid, instance_number, filetype_id, small_read_megabytes, small_write_megabytes, large_read_megabytes, large_write_megabytes, small_read_reqs, small_write_reqs, small_sync_read_reqs, large_read_reqs, large_write_reqs, small_read_servicetime, small_write_servicetime, small_sync_read_latency, large_read_servicetime, large_write_servicetime, retries_on_error) (select :snap_id, :dbid, :instance_number, filetype_id, sum(small_read_megabytes) small_read_megabytes, sum(small_write_megabytes) small_write_megabytes, sum(large_read_megabytes) large_read_megabytes, sum(large_write_megabytes) large_write_megabytes, sum(small_read_reqs) small_read_reqs, sum(small_write_reqs) small_write_reqs, sum(small_sync_read_reqs) small_sync_read_reqs, sum(large_read_reqs) large_read_reqs, sum(large_write_reqs) large_write_reqs, sum(small_read_servicetime) small_read_servicetime, sum(small_write_servicetime) small_write_servicetime, sum(small_sync_read_latency) small_sync_read_latency, sum(large_read_servicetime) large_read_servicetime, sum(large_write_servicetime) large_write_servicetime, sum(retries_on_error) retries_on_error from v$iostat_file group by filetype_id)
1rswbxwhbpmr7 select decode(bitand(a.flags, 16384), 0, a.next_run_date, a.last_enabled_time), a.obj#, decode(bitand(a.flags, 16384), 0, 0, 1), a.sch_job from (select p.obj# obj#, p.flags flags, p.next_run_date next_run_date, p.job_status job_status, p.class_oid class_oid, p.last_enabled_time last_enabled_time, p.instance_id instance_id, 1 sch_job from sys.scheduler$_job p UNION ALL select q.obj#, q.flags, q.next_run_date, q.job_status, q.class_oid, q.last_enabled_time, q.instance_id, 1 from sys.scheduler$_lightweight_job q UNION ALL select j.job, 0, cast(j.next_date as timestamp with time zone), 1, NULL, cast(j.next_date as timestamp with time zone), NULL, 0 from sys.job$ j where (:1 = 1) and (j.field1 is null or j.field1 = 0) and j.job not in (select v.id2 from v$lock v where v.type = 'JQ')) a where bitand(a.job_status, 3) = 1 and ((bitand(a.flags, 134217728 + 268435456) = 0) or (bitand(a.job_status, 1024) <> 0)) and bitand(a.flags, 4096) = 0 and a.instance_id is NULL and (a.class_oid is null or (a.class_oid is not null and a.class_oid in (select b.obj# from sys.scheduler$_class b where b.affinity is null))) and decode(bitand(a.flags, 16384), 0, a.next_run_date, a.last_enabled_time) = (select min(decode(bitand(c.flags, 16384), 0, c.next_run_date, c.last_enabled_time)) from (sele ct r.flags flags, r.next_run_date next_run_date, r.job_status job_status, r.class_oid class_oid, r.last_enabled_time last_enabled_time, r.instance_id instance_id from sys.scheduler$_job r UNION ALL select s.flags, s.next_run_date, s.job_status, s.class_oid, s.last_enabled_time, s.instance_id from sys.scheduler$_lightweight_job s UNION ALL select 0, cast(k.next_date as timestamp with time zone), 1, NULL, cast(k.next_date as timestamp with time zone), NULL from sys.job$ k where (:2 = 1) and (k.field1 is null or k.field1 = 0) and k.job not in (select w.id2 from v$lock w where w.type = 'JQ')) c where bitand(c.job_status, 3) = 1 and ((bitand(c.flags, 134217728 + 268435456) = 0) or (bitand(c.job_status, 1024) <> 0)) and bitand(c.flags, 4096) = 0 and c.instance_id is NULL and (c.class_oid is null or (c.class_oid is not null and c.class_oid in (select d.obj# from sys.scheduler$_class d where d.affinity is null))))
21n5g1xda24d1 select postaladdr0_.CONTACT_POINT_ID as Contact1_11_0_, postaladdr0_1_.TYPE_ID as TYPE2_11_0_, postaladdr0_.Address_Line1 as Address1_12_0_, postaladdr0_.Address_Line2 as Address2_12_0_, postaladdr0_.BOX_NUMBER as BOX3_12_0_, postaladdr0_.BUILDING_NAME as BUILDING4_12_0_, postaladdr0_.CITY as CITY12_0_, postaladdr0_.COUNTRY as COUNTRY12_0_, postaladdr0_.FLOOR_NUMBER as FLOOR7_12_0_, postaladdr0_.HOUSE_NUMBER as HOUSE8_12_0_, postaladdr0_.POSTAL_BARCODE as POSTAL9_12_0_, postaladdr0_.POSTAL_CODE as POSTAL10_12_0_, postaladdr0_.REGION as REGION12_0_, postaladdr0_.STREET as STREET12_0_, postaladdr0_.SUBREGION as SUBREGION12_0_, postaladdr0_.UNIT_NUMBER as UNIT14_12_0_ from POSTAL_ADDRESS postaladdr0_, CONTACT_POINT postaladdr0_1_ where postaladdr0_.CONTACT_POINT_ID=postaladdr0_1_.Contact_point_id and postaladdr0_.CONTACT_POINT_ID=:1
21tvtb1gu1n00 select contactpoi0_.Contact_preference_id as Contact1_1_, contactpoi0_.Contact_point_id as Contact2_1_, contactpoi1_.Contact_point_id as Contact1_11_0_, contactpoi1_.TYPE_ID as TYPE2_11_0_, contactpoi1_1_.Address_Line1 as Address1_12_0_, contactpoi1_1_.Address_Line2 as Address2_12_0_, contactpoi1_1_.BOX_NUMBER as BOX3_12_0_, contactpoi1_1_.BUILDING_NAME as BUILDING4_12_0_, contactpoi1_1_.CITY as CITY12_0_, contactpoi1_1_.COUNTRY as COUNTRY12_0_, contactpoi1_1_.FLOOR_NUMBER as FLOOR7_12_0_, contactpoi1_1_.HOUSE_NUMBER as HOUSE8_12_0_, contactpoi1_1_.POSTAL_BARCODE as POSTAL9_12_0_, contactpoi1_1_.POSTAL_CODE as POSTAL10_12_0_, contactpoi1_1_.REGION as REGION12_0_, contactpoi1_1_.STREET as STREET12_0_, contactpoi1_1_.SUBREGION as SUBREGION12_0_, contactpoi1_1_.UNIT_NUMBER as UNIT14_12_0_, contactpoi1_2_.Address as Address13_0_, contactpoi1_2_.DOMAIN as DOMAIN13_0_, contactpoi1_2_.MAXIMUM_MESSAGE_SIZE as MAXIMUM3_13_0_, contactpoi1_2_.NODE as NODE13_0_, contactpoi1_2_.userid as userid13_0_, contactpoi1_3_.AREA_CODE as AREA1_14_0_, contactpoi1_3_.COUNTRY_PHONE_CODE as COUNTRY2_14_0_, contactpoi1_3_.EXTENSION as EXTENSION14_0_, contactpoi1_3_.LOCAL_NUMBER as LOCAL4_14_0_, decode(contactpoi1_.Contact_point_id, contactpoi1_1_.CONTACT_POINT_ID, 1, contactpoi1_2_.CONTACT_POINT_ID, 2, contactpoi1_3_.CONTACT_POINT_ID, 3, 0) as clazz_0_ from CNTCT_PREF_PT_RL contactpoi0_, CONTACT_POINT contactpoi1_, POSTAL_ADDRESS contactpoi1_1_, ELECTRONIC_ADDRESS co ntactpoi1_2_, TELEPHONE_NUMBER contactpoi1_3_ where contactpoi0_.Contact_point_id=contactpoi1_.Contact_point_id(+) and contactpoi1_.Contact_point_id=contactpoi1_1_.CONTACT_POINT_ID(+) and contactpoi1_.Contact_point_id=contactpoi1_2_.CONTACT_POINT_ID(+) and contactpoi1_.Contact_point_id=contactpoi1_3_.CONTACT_POINT_ID(+) and contactpoi0_.Contact_preference_id=:1
2u2rnct1bzufh select membercove0_.MEMBERSHIP_AGREEMENT_ID as MEMBERSHIP3_3_, membercove0_.AGREEMENT_ID as AGREEMENT1_3_, membercove0_.AGREEMENT_ID as AGREEMENT1_0_2_, membercove0_1_.SPECIFICATION_ID as SPECIFIC5_0_2_, membercove0_1_.DESCRIPTION as DESCRIPT2_0_2_, membercove0_1_.END_DATE as END3_0_2_, membercove0_1_.START_DATE as START4_0_2_, membercove0_.AGREEMENT_STATUS_ID as AGREEMENT2_2_2_, contractsp1_.SPECIFICATION_ID as SPECIFIC1_18_0_, contractsp1_.NAME as NAME18_0_, contractsp1_1_.TYPE_ID as TYPE1_19_0_, decode(contractsp1_.SPECIFICATION_ID, contractsp1_1_.SPECIFICATION_ID, 1, 0) as clazz_0_, financials2_.AGREEMENT_STATUS_ID as AGREEMENT1_4_1_, financials2_.STATUS as STATUS4_1_ from MEMBERSHIP_COVERAGE_COMPONENT membercove0_, AGREEMENT membercove0_1_, SPECIFICATION contractsp1_, FS_PRODUCT contractsp1_1_, AGREEMENT_STATUS financials2_ where membercove0_1_.SPECIFICATION_ID=contractsp1_.SPECIFICATION_ID(+) and contractsp1_.SPECIFICATION_ID=contractsp1_1_.SPECIFICATION_ID(+) and membercove0_.AGREEMENT_STATUS_ID=financials2_.AGREEMENT_STATUS_ID(+) and membercove0_.AGREEMENT_ID=membercove0_1_.AGREEMENT_ID and membercove0_.MEMBERSHIP_AGREEMENT_ID=:1
3972rvxu3knn3 delete from sdo_geor_ddl__table$$
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#
3ktacv9r56b51 select owner#, name, namespace, remoteowner, linkname, p_timestamp, p_obj#, nvl(property, 0), subname, type#, d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
4drzfdv0xz7d5 select payments0_.MONEY_SCHEDULER_ID as MONEY3_1_, payments0_.FINANCIAL_TRANSACTION_ID as FINANCIAL2_1_, payments0_.FINANCIAL_TRANSACTION_ID as FINANCIAL1_16_0_, payments0_1_.AMOUNT as AMOUNT16_0_, payments0_.DESCRIPTION as DESCRIPT1_17_0_ from PAYMENT payments0_, FIN_TRANS payments0_1_ where payments0_.FINANCIAL_TRANSACTION_ID=payments0_1_.FINANCIAL_TRANSACTION_ID and payments0_.MONEY_SCHEDULER_ID=:1
4t7tkmtzh0hn3 select financials0_.AGREEMENT_STATUS_ID as AGREEMENT1_4_0_, financials0_.STATUS as STATUS4_0_ from AGREEMENT_STATUS financials0_ where financials0_.AGREEMENT_STATUS_ID=:1
5sgs7q9pjnzg5 SELECT table_name, table_owner FROM ALL_SYNONYMS WHERE owner = 'PUBLIC' AND synonym_name = 'QUEST_COM_TEAM_CODING'
5ujjrq53n2jqw select particular0_.MONEY_PROVISION_ID as MONEY1_22_1_, moneyprovi1_.PARTICULAR_MONEY_PROVISION_ID as PARTICULAR2_3_, moneyprovi1_.MONEY_PROVISION_CASH_FLOW_ID as MONEY1_3_, moneyprovi1_.MONEY_PROVISION_CASH_FLOW_ID as MONEY1_24_0_ from PARTICULAR_MP particular0_, MONEY_PROVISION particular0_1_, MP_CASH_FLOW moneyprovi1_ where particular0_.MONEY_PROVISION_ID=moneyprovi1_.PARTICULAR_MONEY_PROVISION_ID(+) and particular0_.MONEY_PROVISION_ID=particular0_1_.MONEY_PROVISION_ID and particular0_.MONEY_PROVISION_ID=:1
69n4gubwkr9ky select rolesinmem0_.CONTEXT_FIN_SVCS_AGREEMENT_ID as CONTEXT5_2_, rolesinmem0_.FINANCIAL_SERVICES_ROLE_ID as FINANCIAL1_2_, rolesinmem0_.FINANCIAL_SERVICES_ROLE_ID as FINANCIAL1_3_1_, rolesinmem0_.Type_Id as Type2_3_1_, rolesinmem0_.DESCRIPTION as DESCRIPT3_3_1_, rolesinmem0_.ROLE_PLAYER_ID as ROLE4_3_1_, person1_.ROLE_PLAYER_ID as ROLE1_5_0_, person1_.EXTERNAL_REFERENCE as EXTERNAL1_7_0_, person1_.GENDER as GENDER7_0_, person1_.BIRTH_DATE as BIRTH3_7_0_, person1_.DEATH_DATE as DEATH4_7_0_, person1_.MARITAL_STATUS as MARITAL5_7_0_ from FS_ROLE rolesinmem0_, Person person1_, ROLE_PLAYER person1_1_ where rolesinmem0_.ROLE_PLAYER_ID=person1_.ROLE_PLAYER_ID(+) and person1_.ROLE_PLAYER_ID=person1_1_.ROLE_PLAYER_ID(+) and rolesinmem0_.CONTEXT_FIN_SVCS_AGREEMENT_ID=:1
792444k43js78 select moneyprovi0_.MONEY_PROVISION_PART_ID as MONEY1_25_0_, moneyprovi0_.AMOUNT as AMOUNT25_0_, moneyprovi0_.TYPE_ID as TYPE3_25_0_ from MP_PART moneyprovi0_ where moneyprovi0_.MONEY_PROVISION_PART_ID=:1
7hafjssbmdfmg select moneysched0_.MONEY_SCHEDULER_ID as MONEY1_21_2_, moneysched0_.FINANCIAL_TRANS_MEDIUM_ID as FINANCIAL2_21_2_, particular1_.MONEY_SCHEDULER_ID as MONEY2_4_, particular1_.MONEY_PROVISION_ID as MONEY1_4_, particular1_.MONEY_PROVISION_ID as MONEY1_22_0_, paymentmet2_.FINANCIAL_TRANSACTION_MEDIUM_I as FINANCIAL1_15_1_, paymentmet2_.EXPIRATION_DATE as EXPIRATION2_15_1_, paymentmet2_.PIN as PIN15_1_, paymentmet2_.EXTERNAL_REFERENCE as EXTERNAL4_15_1_, paymentmet2_.NAME as NAME15_1_ from MONEY_SCHEDULER moneysched0_, PARTICULAR_MP particular1_, MONEY_PROVISION particular1_1_, FINANCIAL_TXN_MED paymentmet2_ where moneysched0_.MONEY_SCHEDULER_ID=particular1_.MONEY_SCHEDULER_ID(+) and particular1_.MONEY_PROVISION_ID=particular1_1_.MONEY_PROVISION_ID(+) and moneysched0_.FINANCIAL_TRANS_MEDIUM_ID=paymentmet2_.FINANCIAL_TRANSACTION_MEDIUM_I(+) and moneysched0_.MONEY_SCHEDULER_ID=:1
7mnvpzybmajra select moneyprovi0_.MONEY_PROVISION_CASH_FLOW_ID as MONEY4_1_, moneyprovi0_.MONEY_PROVISION_PART_ID as MONEY1_1_, moneyprovi0_.MONEY_PROVISION_PART_ID as MONEY1_25_0_, moneyprovi0_.AMOUNT as AMOUNT25_0_, moneyprovi0_.TYPE_ID as TYPE3_25_0_ from MP_PART moneyprovi0_ where moneyprovi0_.MONEY_PROVISION_CASH_FLOW_ID=:1
7ng34ruy5awxq select i.obj#, i.ts#, i.file#, i.block#, i.intcols, i.type#, i.flags, i.property, i.pctfree$, i.initrans, i.maxtrans, i.blevel, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey, i.clufac, i.cols, i.analyzetime, i.samplesize, i.dataobj#, nvl(i.degree, 1), nvl(i.instances, 1), i.rowcnt, mod(i.pctthres$, 256), i.indmethod#, i.trunccnt, nvl(c.unicols, 0), nvl(c.deferrable#+c.valid#, 0), nvl(i.spare1, i.intcols), i.spare4, i.spare2, i.spare6, decode(i.pctthres$, null, null, mod(trunc(i.pctthres$/256), 256)), ist.cachedblk, ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols, min(to_number(bitand(defer, 1))) deferrable#, min(to_number(bitand(defer, 4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
7p94zfu5dhh8s select decode(bitand(a.flags, 16384), 0, a.next_run_date, a.last_enabled_time), a.obj#, decode(bitand(a.flags, 16384), 0, 0, 1), a.sch_job from (select p.obj# obj#, p.flags flags, p.next_run_date next_run_date, p.job_status job_status, p.class_oid class_oid, p.last_enabled_time last_enabled_time, p.instance_id instance_id, 1 sch_job from sys.scheduler$_job p UNION ALL select q.obj#, q.flags, q.next_run_date, q.job_status, q.class_oid, q.last_enabled_time, q.instance_id, 1 from sys.scheduler$_lightweight_job q UNION ALL select j.job, 0, cast(j.next_date as timestamp with time zone), 1, NULL, cast(j.next_date as timestamp with time zone), j.field1, 0 from sys.job$ j where (:1 = 1) and j.field1 is not null and j.field1 > 0 and j.job not in (select v.id2 from v$lock v where v.type = 'JQ')) a where bitand(a.job_status, 3) = 1 and ((bitand(a.flags, 134217728 + 268435456) = 0) or (bitand(a.job_status, 1024) <> 0)) and bitand(a.flags, 4096) = 0 and ((a.instance_id is not null and (1 = :2) and (to_char(a.instance_id) = :3)) or (a.instance_id is null and a.class_oid is not null and a.class_oid in (select b.obj# from sys.scheduler$_class b where bitand(b.flags, :4) <> 0 and b.affinity = :5))) and decode(bitand(a.flags, 16384), 0, a.next_run_date, a.last_enabled_time) = (select min(decode(bi tand(c.flags, 16384), 0, c.next_run_date, c.last_enabled_time)) from (select r.flags flags, r.next_run_date next_run_date, r.job_status job_status, r.class_oid class_oid, r.last_enabled_time last_enabled_time, r.instance_id instance_id from sys.scheduler$_job r UNION ALL select s.flags, s.next_run_date, s.job_status, s.class_oid, s.last_enabled_time, s.instance_id from sys.scheduler$_lightweight_job s UNION ALL select 0, cast(k.next_date as timestamp with time zone), 1, NULL, cast(k.next_date as timestamp with time zone), k.field1 from sys.job$ k where (:6 = 1) and k.field1 is not null and k.field1 > 0 and k.job not in (select w.id2 from v$lock w where w.type = 'JQ')) c where bitand(c.job_status, 3) = 1 and ((bitand(c.flags, 134217728 + 268435456) = 0) or (bitand(c.job_status, 1024) <> 0)) and bitand(c.flags, 4096) = 0 and ((c.instance_id is not null and (1 = :7) and (to_char(c.instance_id) = :8)) or (c.instance_id is null and c.class_oid is not null and c.class_oid in (select d.obj# from sys.scheduler$_class d where bitand(d.flags, :9) <> 0 and d.affinity = :10))))
7pkqxr43gxnff insert into CONTACT_POINT (TYPE_ID, Contact_point_id) values (:1, :2)
852a2d3h25q92 select telephonen0_.CONTACT_POINT_ID as Contact1_11_0_, telephonen0_1_.TYPE_ID as TYPE2_11_0_, telephonen0_.AREA_CODE as AREA1_14_0_, telephonen0_.COUNTRY_PHONE_CODE as COUNTRY2_14_0_, telephonen0_.EXTENSION as EXTENSION14_0_, telephonen0_.LOCAL_NUMBER as LOCAL4_14_0_ from TELEPHONE_NUMBER telephonen0_, CONTACT_POINT telephonen0_1_ where telephonen0_.CONTACT_POINT_ID=telephonen0_1_.Contact_point_id and telephonen0_.CONTACT_POINT_ID=:1
873gfuh45xdk8 select membership0_.AGREEMENT_ID as AGREEMENT1_0_2_, membership0_1_.SPECIFICATION_ID as SPECIFIC5_0_2_, membership0_1_.DESCRIPTION as DESCRIPT2_0_2_, membership0_1_.END_DATE as END3_0_2_, membership0_1_.START_DATE as START4_0_2_, contractsp1_.SPECIFICATION_ID as SPECIFIC1_18_0_, contractsp1_.NAME as NAME18_0_, contractsp1_1_.TYPE_ID as TYPE1_19_0_, decode(contractsp1_.SPECIFICATION_ID, contractsp1_1_.SPECIFICATION_ID, 1, 0) as clazz_0_, allowedreq2_.SPECIFICATION_ID as SPECIFIC1_4_, contractre3_.AGREEMENT_REQUEST_ID as AGREEMENT2_4_, contractre3_.AGREEMENT_REQUEST_ID as AGREEMENT1_20_1_ from MEMBERSHIP_AGREEMENT membership0_, AGREEMENT membership0_1_, SPECIFICATION contractsp1_, FS_PRODUCT contractsp1_1_, AGMT_REQ_SPEC_RL allowedreq2_, AGMT_REQUEST contractre3_ where membership0_1_.SPECIFICATION_ID=contractsp1_.SPECIFICATION_ID(+) and contractsp1_.SPECIFICATION_ID=contractsp1_1_.SPECIFICATION_ID(+) and contractsp1_.SPECIFICATION_ID=allowedreq2_.SPECIFICATION_ID(+) and allowedreq2_.AGREEMENT_REQUEST_ID=contractre3_.AGREEMENT_REQUEST_ID(+) and membership0_.AGREEMENT_ID=membership0_1_.AGREEMENT_ID and membership0_.AGREEMENT_ID=:1
8t28u0mpry0tb update FINANCIAL_TXN_MED set EXPIRATION_DATE=:1, PIN=:2, EXTERNAL_REFERENCE=:3, NAME=:4 where FINANCIAL_TRANSACTION_MEDIUM_I=:5
8ww1g6rr0waht select hasparticu0_.AGREEMENT_REQUEST_ID as AGREEMENT1_1_, hasparticu0_.MONEY_PROVISION_ID as MONEY2_1_, particular1_.MONEY_PROVISION_ID as MONEY1_22_0_ from AGMT_REQ_MP_RL hasparticu0_, PARTICULAR_MP particular1_, MONEY_PROVISION particular1_1_ where hasparticu0_.MONEY_PROVISION_ID=particular1_.MONEY_PROVISION_ID(+) and particular1_.MONEY_PROVISION_ID=particular1_1_.MONEY_PROVISION_ID(+) and hasparticu0_.AGREEMENT_REQUEST_ID=:1
9xzqvm5wd0fd2 update AGREEMENT set SPECIFICATION_ID=:1, DESCRIPTION=:2, END_DATE=:3, START_DATE=:4 where AGREEMENT_ID=:5
a6u3yjca29nqc declare m_stmt varchar2(512); begin m_stmt:='delete from sdo_geor_ddl__table$$'; EXECUTE IMMEDIATE m_stmt; EXCEPTION WHEN OTHERS THEN NULL; end;
amn7d6akj2jrh select allnames0_.role_player_id as role10_1_, allnames0_.party_name_id as party1_1_, allnames0_.party_name_id as party1_6_0_, allnames0_.DESCRIPTION as DESCRIPT2_6_0_, allnames0_.FIRST_NAME as FIRST3_6_0_, allnames0_.FULL_NAME as FULL4_6_0_, allnames0_.LAST_NAME as LAST5_6_0_, allnames0_.MIDDLE_NAMES as MIDDLE6_6_0_, allnames0_.SALUTATION as SALUTATION6_0_, allnames0_.SHORT_FIRST_NAME as SHORT8_6_0_, allnames0_.SUFFIX_TITLES as SUFFIX9_6_0_ from PARTY_NAME allnames0_ where allnames0_.role_player_id=:1
anz3ykuv9rqbn select membership0_.AGREEMENT_ID as AGREEMENT1_0_3_, membership0_1_.SPECIFICATION_ID as SPECIFIC5_0_3_, membership0_1_.DESCRIPTION as DESCRIPT2_0_3_, membership0_1_.END_DATE as END3_0_3_, membership0_1_.START_DATE as START4_0_3_, membership0_.AGREEMENT_STATUS_ID as AGREEMENT2_2_3_, contractsp1_.SPECIFICATION_ID as SPECIFIC1_18_0_, contractsp1_.NAME as NAME18_0_, contractsp1_1_.TYPE_ID as TYPE1_19_0_, decode(contractsp1_.SPECIFICATION_ID, contractsp1_1_.SPECIFICATION_ID, 1, 0) as clazz_0_, allowedreq2_.SPECIFICATION_ID as SPECIFIC1_5_, contractre3_.AGREEMENT_REQUEST_ID as AGREEMENT2_5_, contractre3_.AGREEMENT_REQUEST_ID as AGREEMENT1_20_1_, financials4_.AGREEMENT_STATUS_ID as AGREEMENT1_4_2_, financials4_.STATUS as STATUS4_2_ from MEMBERSHIP_COVERAGE_COMPONENT membership0_, AGREEMENT membership0_1_, SPECIFICATION contractsp1_, FS_PRODUCT contractsp1_1_, AGMT_REQ_SPEC_RL allowedreq2_, AGMT_REQUEST contractre3_, AGREEMENT_STATUS financials4_ where membership0_1_.SPECIFICATION_ID=contractsp1_.SPECIFICATION_ID(+) and contractsp1_.SPECIFICATION_ID=contractsp1_1_.SPECIFICATION_ID(+) and contractsp1_.SPECIFICATION_ID=allowedreq2_.SPECIFICATION_ID(+) and allowedreq2_.AGREEMENT_REQUEST_ID=contractre3_.AGREEMENT_REQUEST_ID(+) and membership0_.AGREEMENT_STATUS_ID=financials4_.AGREEMENT_STATUS_ID(+) and membership0_.AGREEMENT_ID=membership0_1_.AGREEMENT_ID and membership0_.AGREEMENT_ID=:1
bgw5sq5rghdvj select membership0_.SPECIFICATION_ID as SPECIFIC1_18_1_, membership0_1_.NAME as NAME18_1_, membership0_.TYPE_ID as TYPE1_19_1_, allowedreq1_.SPECIFICATION_ID as SPECIFIC1_3_, contractre2_.AGREEMENT_REQUEST_ID as AGREEMENT2_3_, contractre2_.AGREEMENT_REQUEST_ID as AGREEMENT1_20_0_ from FS_PRODUCT membership0_, SPECIFICATION membership0_1_, AGMT_REQ_SPEC_RL allowedreq1_, AGMT_REQUEST contractre2_ where membership0_.SPECIFICATION_ID=allowedreq1_.SPECIFICATION_ID(+) and allowedreq1_.AGREEMENT_REQUEST_ID=contractre2_.AGREEMENT_REQUEST_ID(+) and membership0_.SPECIFICATION_ID=membership0_1_.SPECIFICATION_ID and membership0_.SPECIFICATION_ID=:1
bqnn4c3gjtmgu insert into wrh$_bg_event_summary (snap_id, dbid, instance_number, event_id, total_waits, total_timeouts, time_waited_micro) select /*+ ordered use_nl(e) */ :snap_id, :dbid, :instance_number, e.event_id, sum(e.total_waits), sum(e.total_timeouts), sum(e.time_waited_micro) from v$session bgsids, v$session_event e where bgsids.type = 'BACKGROUND' and bgsids.sid = e.sid group by e.event_id
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
cj2s9rkux0hqq select moneysched0_.AGREEMENT_ID as AGREEMENT3_2_, moneysched0_.MONEY_SCHEDULER_ID as MONEY1_2_, moneysched0_.MONEY_SCHEDULER_ID as MONEY1_21_1_, moneysched0_.FINANCIAL_TRANS_MEDIUM_ID as FINANCIAL2_21_1_, paymentmet1_.FINANCIAL_TRANSACTION_MEDIUM_I as FINANCIAL1_15_0_, paymentmet1_.EXPIRATION_DATE as EXPIRATION2_15_0_, paymentmet1_.PIN as PIN15_0_, paymentmet1_.EXTERNAL_REFERENCE as EXTERNAL4_15_0_, paymentmet1_.NAME as NAME15_0_ from MONEY_SCHEDULER moneysched0_, FINANCIAL_TXN_MED paymentmet1_ where moneysched0_.FINANCIAL_TRANS_MEDIUM_ID=paymentmet1_.FINANCIAL_TRANSACTION_MEDIUM_I(+) and moneysched0_.AGREEMENT_ID=:1
crgrgafjskcz4 select contractco0_.FINANCIAL_PRODUCT_ID as FINANCIAL1_1_, contractco0_.FINANCIAL_PRODUCT_CHILD_ID as FINANCIAL2_1_, contractsp1_.SPECIFICATION_ID as SPECIFIC1_18_0_, contractsp1_.NAME as NAME18_0_, contractsp1_1_.TYPE_ID as TYPE1_19_0_, decode(contractsp1_.SPECIFICATION_ID, contractsp1_1_.SPECIFICATION_ID, 1, 0) as clazz_0_ from FS_PRODUCT_RL contractco0_, SPECIFICATION contractsp1_, FS_PRODUCT contractsp1_1_ where contractco0_.FINANCIAL_PRODUCT_CHILD_ID=contractsp1_.SPECIFICATION_ID(+) and contractsp1_.SPECIFICATION_ID=contractsp1_1_.SPECIFICATION_ID(+) and contractco0_.FINANCIAL_PRODUCT_ID=:1
d92ts0124bscp select membership0_.AGREEMENT_ID as AGREEMENT1_0_, membership0_1_.SPECIFICATION_ID as SPECIFIC5_0_, membership0_1_.DESCRIPTION as DESCRIPT2_0_, membership0_1_.END_DATE as END3_0_, membership0_1_.START_DATE as START4_0_, membership0_.AGREEMENT_STATUS_ID as AGREEMENT2_2_ from MEMBERSHIP_COVERAGE_COMPONENT membership0_, AGREEMENT membership0_1_, FS_ROLE rolesinmem1_, Person person2_, ROLE_PLAYER person2_1_, PARTY_NAME allnames3_, CONTACT_PREFERENCE contactpre4_ where membership0_.AGREEMENT_ID=membership0_1_.AGREEMENT_ID and membership0_.AGREEMENT_ID=rolesinmem1_.CONTEXT_FIN_SVCS_AGREEMENT_ID and rolesinmem1_.ROLE_PLAYER_ID=person2_.ROLE_PLAYER_ID and person2_.ROLE_PLAYER_ID=person2_1_.ROLE_PLAYER_ID and person2_.ROLE_PLAYER_ID=allnames3_.role_player_id and person2_.ROLE_PLAYER_ID=contactpre4_.subject_role_player_id and (upper(allnames3_.FIRST_NAME) like :1) and (upper(allnames3_.LAST_NAME) like :2) and (:3 in (select contactpoi6_3_.AREA_CODE||contactpoi6_3_.LOCAL_NUMBER from CNTCT_PREF_PT_RL contactpoi5_, CONTACT_POINT contactpoi6_, POSTAL_ADDRESS contactpoi6_1_, ELECTRONIC_ADDRESS contactpoi6_2_, TELEPHONE_NUMBER contactpoi6_3_ where contactpre4_.Contact_preference_id=contactpoi5_.Contact_preference_id and contactpoi5_.Contact_point_id=contactpoi6_.Contact_point_id and contactpoi6_.Contact_point_id=contactpoi6_1_.CONTACT_POINT_ID(+) and contactpoi6_.Contact_point_id=contactpoi6_2_.CONTACT_POINT_ID(+) and contactpoi6_.Contact_point_id=contactpoi6_3_.CONTACT_POINT_ ID(+)))
d9txynrwrf1sx select partyintro0_.ROLE_PLAYER_ID as ROLE3_1_, partyintro0_.INTRODUCTION_ID as INTRODUC1_1_, partyintro0_.INTRODUCTION_ID as INTRODUC1_8_0_, partyintro0_.START_DATE as START2_8_0_ from Introduction partyintro0_ where partyintro0_.ROLE_PLAYER_ID=:1
dt7g7q82h9zpz select partyregis0_.PARTY_REGISTRATION_ID as PARTY1_9_0_, partyregis0_.EXTERNAL_REFERENCE as EXTERNAL2_9_0_, partyregis0_.COUNTRY_CODE as COUNTRY3_9_0_ from PARTY_REGISTRATION partyregis0_ where partyregis0_.PARTY_REGISTRATION_ID=:1
f03t8u1xdg5bv select partyregis0_.REGISTERED_PARTY_ID as REGISTERED4_1_, partyregis0_.PARTY_REGISTRATION_ID as PARTY1_1_, partyregis0_.PARTY_REGISTRATION_ID as PARTY1_9_0_, partyregis0_.EXTERNAL_REFERENCE as EXTERNAL2_9_0_, partyregis0_.COUNTRY_CODE as COUNTRY3_9_0_ from PARTY_REGISTRATION partyregis0_ where partyregis0_.REGISTERED_PARTY_ID=:1
fmrnwrzs58uqm select partyinvol0_.FINANCIAL_SERVICES_ROLE_ID as FINANCIAL1_3_2_, partyinvol0_.Type_Id as Type2_3_2_, partyinvol0_.DESCRIPTION as DESCRIPT3_3_2_, partyinvol0_.ROLE_PLAYER_ID as ROLE4_3_2_, person1_.ROLE_PLAYER_ID as ROLE1_5_0_, person1_.EXTERNAL_REFERENCE as EXTERNAL1_7_0_, person1_.GENDER as GENDER7_0_, person1_.BIRTH_DATE as BIRTH3_7_0_, person1_.DEATH_DATE as DEATH4_7_0_, person1_.MARITAL_STATUS as MARITAL5_7_0_, contactpre2_.subject_role_player_id as subject2_4_, contactpre2_.Contact_preference_id as Contact1_4_, contactpre2_.Contact_preference_id as Contact1_10_1_ from FS_ROLE partyinvol0_, Person person1_, ROLE_PLAYER person1_1_, CONTACT_PREFERENCE contactpre2_ where partyinvol0_.ROLE_PLAYER_ID=person1_.ROLE_PLAYER_ID(+) and person1_.ROLE_PLAYER_ID=person1_1_.ROLE_PLAYER_ID(+) and person1_.ROLE_PLAYER_ID=contactpre2_.subject_role_player_id(+) and partyinvol0_.FINANCIAL_SERVICES_ROLE_ID=:1
fnk7155mk2jq6 insert into wrh$_sysmetric_history (snap_id, dbid, instance_number, begin_time, end_time, intsize, group_id, metric_id, value) select :snap_id, :dbid, :instance_number, begtime, endtime, intsize_csec, groupid, metricid, value from x$kewmdrmv order by groupid, metricid, begtime
fprmshrw6rd1j select payment0_.FINANCIAL_TRANSACTION_ID as FINANCIAL1_16_0_, payment0_1_.AMOUNT as AMOUNT16_0_, payment0_.DESCRIPTION as DESCRIPT1_17_0_ from PAYMENT payment0_, FIN_TRANS payment0_1_ where payment0_.FINANCIAL_TRANSACTION_ID=payment0_1_.FINANCIAL_TRANSACTION_ID and payment0_.FINANCIAL_TRANSACTION_ID=:1
fuak1krkkm5dk select personname0_.party_name_id as party1_6_0_, personname0_.DESCRIPTION as DESCRIPT2_6_0_, personname0_.FIRST_NAME as FIRST3_6_0_, personname0_.FULL_NAME as FULL4_6_0_, personname0_.LAST_NAME as LAST5_6_0_, personname0_.MIDDLE_NAMES as MIDDLE6_6_0_, personname0_.SALUTATION as SALUTATION6_0_, personname0_.SHORT_FIRST_NAME as SHORT8_6_0_, personname0_.SUFFIX_TITLES as SUFFIX9_6_0_ from PARTY_NAME personname0_ where personname0_.party_name_id=:1
ga9j9xk5cy9s0 select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#, length, piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#
gs2m7z4tncs8t update MONEY_SCHEDULER set AGREEMENT_ID=null where AGREEMENT_ID=:1
Back to SQL Statistics
Back to Top
Instance Activity Statistics
Instance Activity Stats
Instance Activity Stats - Absolute Values
Instance Activity Stats - Thread Activity
Back to Top
Instance Activity Stats
Statistic Total per Second per Trans
Batched IO (bound) vector count 1 0.00 0.00
Batched IO (full) vector count 0 0.00 0.00
Batched IO block miss count 8 0.00 0.00
Batched IO buffer defrag count 0 0.00 0.00
Batched IO double miss count 1 0.00 0.00
Batched IO same unit count 4 0.00 0.00
Batched IO single block count 1 0.00 0.00
Batched IO vector block count 3 0.00 0.00
Batched IO vector read count 1 0.00 0.00
CCursor + sql area evicted 6,567 0.46 1.54
CPU used by this session 2,152,922 149.38 503.73
CPU used when call started 2,150,970 149.25 503.27
CR blocks created 49,012 3.40 11.47
Cached Commit SCN referenced 2,286 0.16 0.53
Commit SCN cached 4 0.00 0.00
DB time 6,375,565 442.37 1,491.71
DBWR checkpoint buffers written 27,030 1.88 6.32
DBWR checkpoints 4 0.00 0.00
DBWR object drop buffers written 0 0.00 0.00
DBWR tablespace checkpoint buffers written 0 0.00 0.00
DBWR transaction table writes 459 0.03 0.11
DBWR undo block writes 12,747 0.88 2.98
Effective IO time 0 0.00 0.00
HSC Heap Segment Block Changes 182,554 12.67 42.71
Heap Segment Array Inserts 4,516 0.31 1.06
Heap Segment Array Updates 162 0.01 0.04
IMU CR rollbacks 0 0.00 0.00
IMU Flushes 0 0.00 0.00
IMU Redo allocation size 0 0.00 0.00
IMU commits 48 0.00 0.01
IMU contention 0 0.00 0.00
IMU ktichg flush 0 0.00 0.00
IMU pool not allocated 1,018 0.07 0.24
IMU recursive-transaction flush 0 0.00 0.00
IMU undo allocation size 210,768 14.62 49.31
IMU- failed to get a private strand 1,018 0.07 0.24
LOB table id lookup cache misses 32 0.00 0.01
Number of read IOs issued 0 0.00 0.00
PX local messages recv'd 0 0.00 0.00
PX local messages sent 0 0.00 0.00
RowCR - row contention 16 0.00 0.00
RowCR attempts 60,367 4.19 14.12
RowCR hits 47,653 3.31 11.15
SMON posted for undo segment shrink 30 0.00 0.01
SQL*Net roundtrips to/from client 3,011,603 208.96 704.63
active txn count during cleanout 5,458 0.38 1.28
application wait time 1,731 0.12 0.41
background checkpoints completed 4 0.00 0.00
background checkpoints started 4 0.00 0.00
background timeouts 56,108 3.89 13.13
branch node splits 1 0.00 0.00
buffer is not pinned count 160,923,237 11,165.76 37,651.67
buffer is pinned count 51,795,490,371 3,593,863.25 12,118,738.97
bytes received via SQL*Net from client 586,185,724 40,672.87 137,151.55
bytes sent via SQL*Net to client 844,413,155 58,590.15 197,569.76
calls to get snapshot scn: kcmgss 2,541,188 176.32 594.57
calls to kcmgas 84,518 5.86 19.77
calls to kcmgcs 1,802 0.13 0.42
calls to kcmgrs 1,952,226 135.46 456.77
change write time 875 0.06 0.20
cleanout - number of ktugct calls 5,812 0.40 1.36
cleanouts and rollbacks - consistent read gets 3,345 0.23 0.78
cleanouts only - consistent read gets 324 0.02 0.08
cluster key scan block gets 10,714 0.74 2.51
cluster key scans 6,323 0.44 1.48
commit batch/immediate performed 8,200 0.57 1.92
commit batch/immediate requested 8,200 0.57 1.92
commit cleanout failures: block lost 0 0.00 0.00
commit cleanout failures: buffer being written 3 0.00 0.00
commit cleanout failures: callback failure 128 0.01 0.03
commit cleanout failures: cannot pin 43 0.00 0.01
commit cleanouts 232,770 16.15 54.46
commit cleanouts successfully completed 232,596 16.14 54.42
commit immediate performed 8,200 0.57 1.92
commit immediate requested 8,200 0.57 1.92
commit txn count during cleanout 1,659 0.12 0.39
concurrency wait time 9,785 0.68 2.29
consistent changes 193,665 13.44 45.31
consistent gets 364,014,214 25,257.36 85,169.45
consistent gets - examination 8,887,043 616.63 2,079.33
consistent gets direct 0 0.00 0.00
consistent gets from cache 364,014,214 25,257.36 85,169.45
consistent gets from cache (fastpath) 47,605,318 3,303.13 11,138.35
cursor authentications 6,879 0.48 1.61
data blocks consistent reads - undo records applied 193,585 13.43 45.29
db block changes 900,751 62.50 210.75
db block gets 1,599,206 110.96 374.17
db block gets direct 162 0.01 0.04
db block gets from cache 1,599,044 110.95 374.13
db block gets from cache (fastpath) 517,831 35.93 121.16
deferred (CURRENT) block cleanout applications 132,204 9.17 30.93
enqueue conversions 1,553,123 107.76 363.39
enqueue releases 2,903,724 201.48 679.39
enqueue requests 2,903,732 201.48 679.39
enqueue timeouts 4 0.00 0.00
enqueue waits 0 0.00 0.00
execute count 1,635,146 113.46 382.58
free buffer inspected 44 0.00 0.01
free buffer requested 63,946 4.44 14.96
heap block compress 503 0.03 0.12
immediate (CR) block cleanout applications 3,669 0.25 0.86
immediate (CURRENT) block cleanout applications 2,510 0.17 0.59
index crx upgrade (found) 2,808 0.19 0.66
index crx upgrade (positioned) 0 0.00 0.00
index crx upgrade (prefetch) 0 0.00 0.00
index fast full scans (full) 4 0.00 0.00
index fetch by key 4,793,431 332.60 1,121.53
index scans kdiixs1 4,369,945,419 303,211.46 1,022,448.62
java call heap collected bytes 0 0.00 0.00
java call heap collected count 0 0.00 0.00
java call heap gc count 0 0.00 0.00
java call heap live object count 0 0.00 0.00
java call heap live object count max 0 0.00 0.00
java call heap live size 0 0.00 0.00
java call heap live size max 0 0.00 0.00
java call heap object count 0 0.00 0.00
java call heap object count max 0 0.00 0.00
java call heap total size 0 0.00 0.00
java call heap total size max 0 0.00 0.00
java call heap used size 0 0.00 0.00
java call heap used size max 0 0.00 0.00
leaf node 90-10 splits 397 0.03 0.09
leaf node splits 1,051 0.07 0.25
lob reads 632 0.04 0.15
lob writes 78 0.01 0.02
lob writes unaligned 78 0.01 0.02
logons cumulative 144 0.01 0.03
messages received 21,479 1.49 5.03
messages sent 21,479 1.49 5.03
no buffer to keep pinned count 165,903 11.51 38.82
no work - consistent read gets 335,191,434 23,257.47 78,425.70
opened cursors cumulative 726,929 50.44 170.08
parse count (failures) 10 0.00 0.00
parse count (hard) 170,251 11.81 39.83
parse count (total) 713,719 49.52 166.99
parse time cpu 3,015 0.21 0.71
parse time elapsed 23,692 1.64 5.54
physical read IO requests 606 0.04 0.14
physical read bytes 20,226,048 1,403.40 4,732.35
physical read total IO requests 19,229 1.33 4.50
physical read total bytes 475,506,688 32,993.34 111,255.66
physical read total multi block requests 167 0.01 0.04
physical reads 2,469 0.17 0.58
physical reads cache 2,445 0.17 0.57
physical reads cache prefetch 1,865 0.13 0.44
physical reads direct 24 0.00 0.01
physical reads direct (lob) 0 0.00 0.00
physical reads direct temporary tablespace 0 0.00 0.00
physical reads prefetch warmup 272 0.02 0.06
physical write IO requests 7,371 0.51 1.72
physical write bytes 222,953,472 15,469.77 52,165.06
physical write total IO requests 37,761 2.62 8.84
physical write total bytes 937,385,984 65,041.13 219,322.88
physical write total multi block requests 1,236 0.09 0.29
physical writes 27,216 1.89 6.37
physical writes direct 186 0.01 0.04
physical writes direct (lob) 0 0.00 0.00
physical writes direct temporary tablespace 0 0.00 0.00
physical writes from cache 27,030 1.88 6.32
physical writes non checkpoint 6,113 0.42 1.43
prefetch warmup blocks flushed out before use 0 0.00 0.00
process last non-idle time 14,209 0.99 3.32
recovery blocks read 0 0.00 0.00
recursive calls 697,976 48.43 163.31
recursive cpu usage 5,986 0.42 1.40
redo blocks checksummed by FG (exclusive) 39,752 2.76 9.30
redo blocks read for recovery 0 0.00 0.00
redo blocks read total 0 0.00 0.00
redo blocks written 292,128 20.27 68.35
redo blocks written for direct writes 1,332,936 92.49 311.87
redo buffer allocation retries 8 0.00 0.00
redo entries 461,792 32.04 108.05
redo log space requests 8 0.00 0.00
redo log space wait time 8 0.00 0.00
redo ordering marks 13,135 0.91 3.07
redo size 141,177,884 9,795.72 33,031.79
redo subscn max counts 16,606 1.15 3.89
redo synch time 3,777 0.26 0.88
redo synch writes 12,447 0.86 2.91
redo wastage 3,571,036 247.78 835.53
redo write time 3,077 0.21 0.72
redo writer latching time 15 0.00 0.00
redo writes 14,263 0.99 3.34
rollback changes - undo records applied 41,038 2.85 9.60
rollbacks only - consistent read gets 47,328 3.28 11.07
rows fetched via callback 2,455,479 170.37 574.52
session connect time 102 0.01 0.02
session cursor cache hits 444,959 30.87 104.11
session logical reads 365,613,419 25,368.32 85,543.62
shared hash latch upgrades - no wait 19,092,010 1,324.71 4,467.01
shared hash latch upgrades - wait 2,812 0.20 0.66
sorts (memory) 19,828 1.38 4.64
sorts (rows) 123,314 8.56 28.85
sql area evicted 167,185 11.60 39.12
sql area purged 10 0.00 0.00
switch current to new buffer 6,680 0.46 1.56
table fetch by rowid 25,937,961,772 1,799,722.08 6,068,779.08
table fetch continued row 1,088 0.08 0.25
table scan blocks gotten 6,344,754 440.23 1,484.50
table scan rows gotten 1,308,931,003 90,821.02 306,254.33
table scans (short tables) 242,600 16.83 56.76
total number of times SMON posted 32 0.00 0.01
transaction rollbacks 8,200 0.57 1.92
transaction tables consistent reads - undo records applied 0 0.00 0.00
undo change vector size 45,512,852 3,157.94 10,648.77
user I/O wait time 8 0.00 0.00
user calls 3,683,915 255.61 861.94
user commits 4,240 0.29 0.99
user rollbacks 34 0.00 0.01
workarea executions - optimal 13,441 0.93 3.14
write clones created in background 0 0.00 0.00
write clones created in foreground 1 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 pga memory 1,406,105,976 1,523,859,768
session pga memory max 1,659,567,800 1,793,412,024
session cursor cache count 30,387 33,927
session uga memory 2,693,081,915,688 2,847,705,468,488
opened cursors current 419 397
logons current 61 57
session uga memory max 166,468,905,416 226,805,200,088
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) 4 1.00
Back to Instance Activity Statistics
Back to Top
IO Stats
Tablespace IO Stats
File 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)
MEMPERFTBL 4 0 0.00 1.00 2,869 0 19 0.00
MEMPERFINDX 4 0 0.00 1.00 1,616 0 36 0.28
UNDOTBS1 4 0 0.00 1.00 1,344 0 148 0.27
SYSAUX 144 0 0.35 3.09 1,138 0 0 0.00
SYSTEM 452 0 0.04 4.52 400 0 0 0.00
USERS 4 0 0.00 1.00 4 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)
MEMPERFINDX /u01/oracle/oradata/mpqa/memindx01.dbf 4 0 0.00 1.00 1,616 0 36 0.28
MEMPERFTBL /u01/oracle/oradata/mpqa/memtbl01.dbf 4 0 0.00 1.00 2,869 0 19 0.00
SYSAUX /u01/oracle/oradata/mpqa/sysaux01.dbf 144 0 0.35 3.09 1,138 0 0 0.00
SYSTEM /u01/oracle/oradata/mpqa/system01.dbf 452 0 0.04 4.52 400 0 0 0.00
UNDOTBS1 /u01/oracle/oradata/mpqa/undotbs01.dbf 4 0 0.00 1.00 1,344 0 148 0.27
USERS /u01/oracle/oradata/mpqa/users01.dbf 4 0 0.00 1.00 4 0 0 0.00
Back to IO Stats
Back to Top
Buffer Pool Statistics
Standard block size Pools D: default, K: keep, R: recycle
Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
P Number of Buffers Pool Hit% Buffer Gets Physical Reads Physical Writes Free Buff Wait Writ Comp Wait Buffer Busy Waits
D 205,140 100 365,410,438 2,460 27,030 0 0 203
Back to Top
Advisory Statistics
Instance Recovery Stats
Buffer Pool Advisory
PGA Aggr Summary
PGA Aggr Target Stats
PGA Aggr Target Histogram
PGA Memory Advisory
Shared Pool Advisory
SGA Target Advisory
Streams Pool Advisory
Java Pool Advisory
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 14 301 3399 25640 184320 25640
E 0 14 716 7926 42442 184320 42442
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 128 0.08 15,780 1.00 50,683
D 256 0.15 31,560 1.00 50,683
D 384 0.23 47,340 1.00 50,683
D 512 0.31 63,120 1.00 50,683
D 640 0.38 78,900 1.00 50,683
D 768 0.46 94,680 1.00 50,683
D 896 0.54 110,460 1.00 50,683
D 1,024 0.62 126,240 1.00 50,683
D 1,152 0.69 142,020 1.00 50,683
D 1,280 0.77 157,800 1.00 50,683
D 1,408 0.85 173,580 1.00 50,683
D 1,536 0.92 189,360 1.00 50,683
D 1,664 1.00 205,140 1.00 50,683
D 1,792 1.08 220,920 1.00 50,683
D 1,920 1.15 236,700 1.00 50,683
D 2,048 1.23 252,480 1.00 50,683
D 2,176 1.31 268,260 1.00 50,683
D 2,304 1.38 284,040 1.00 50,683
D 2,432 1.46 299,820 1.00 50,683
D 2,560 1.54 315,600 1.00 50,683
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 439 0
Back to Advisory Statistics
Back to Top
PGA Aggr Target Stats
No data exists for this section of the report.
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 13,045 13,045 0 0
64K 128K 26 26 0 0
128K 256K 6 6 0 0
512K 1024K 79 79 0 0
1M 2M 282 282 0 0
2M 4M 3 3 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 Estd Time
416 0.13 9,633.89 0.00 100.00 0 2,322,893
832 0.25 9,633.89 0.00 100.00 0 2,322,893
1,664 0.50 9,633.89 0.00 100.00 0 2,322,893
2,496 0.75 9,633.89 0.00 100.00 0 2,322,893
3,328 1.00 9,633.89 0.00 100.00 0 2,322,893
3,994 1.20 9,633.89 0.00 100.00 0 2,322,893
4,659 1.40 9,633.89 0.00 100.00 0 2,322,893
5,325 1.60 9,633.89 0.00 100.00 0 2,322,893
5,990 1.80 9,633.89 0.00 100.00 0 2,322,893
6,656 2.00 9,633.89 0.00 100.00 0 2,322,893
9,984 3.00 9,633.89 0.00 100.00 0 2,322,893
13,312 4.00 9,633.89 0.00 100.00 0 2,322,893
19,968 6.00 9,633.89 0.00 100.00 0 2,322,893
26,624 8.00 9,633.89 0.00 100.00 0 2,322,893
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 (K)
960 0.33 224 12,144 90,130 0.64 52,356 41.32 2,313
1,280 0.44 546 28,236 93,000 0.66 49,486 39.06 3,376
1,600 0.56 866 44,183 97,189 0.69 45,297 35.75 3,379
1,920 0.67 1,186 59,051 107,569 0.76 34,917 27.56 3,382
2,240 0.78 1,505 73,981 129,481 0.92 13,005 10.26 3,385
2,560 0.89 1,824 89,396 139,423 0.99 3,063 2.42 3,386
2,880 1.00 2,144 105,185 141,219 1.00 1,267 1.00 3,386
3,200 1.11 2,464 119,772 144,265 1.02 1 0.00 3,387
3,520 1.22 2,783 136,177 145,571 1.03 1 0.00 3,387
3,840 1.33 3,103 151,210 145,643 1.03 1 0.00 3,387
4,160 1.44 3,423 167,573 145,653 1.03 1 0.00 3,387
4,480 1.56 3,743 182,989 150,757 1.07 1 0.00 3,387
4,800 1.67 4,063 197,560 153,097 1.08 1 0.00 3,387
5,120 1.78 4,383 211,807 153,097 1.08 1 0.00 3,387
5,440 1.89 4,703 228,634 153,097 1.08 1 0.00 3,387
5,760 2.00 5,023 243,236 153,949 1.09 1 0.00 3,388
Back to Advisory Statistics
Back to Top
SGA Target Advisory
SGA Target Size (M) SGA Size Factor Est DB Time (s) Est Physical Reads
2,432 0.50 125,742 50,682
3,648 0.75 90,690 50,682
4,864 1.00 90,690 50,682
6,080 1.25 90,690 50,682
7,296 1.50 90,690 50,682
8,512 1.75 90,690 50,682
9,728 2.00 90,708 50,682
Back to Advisory Statistics
Back to Top
Streams Pool Advisory
Size for Est (MB) Size Factor Est Spill Count Est Spill Time (s) Est Unspill Count Est Unspill Time (s)
64 0.50 0 0 0 0
128 1.00 0 0 0 0
192 1.50 0 0 0 0
256 2.00 0 0 0 0
320 2.50 0 0 0 0
384 3.00 0 0 0 0
448 3.50 0 0 0 0
512 4.00 0 0 0 0
576 4.50 0 0 0 0
640 5.00 0 0 0 0
704 5.50 0 0 0 0
768 6.00 0 0 0 0
832 6.50 0 0 0 0
896 7.00 0 0 0 0
960 7.50 0 0 0 0
1,024 8.00 0 0 0 0
1,088 8.50 0 0 0 0
1,152 9.00 0 0 0 0
1,216 9.50 0 0 0 0
1,280 10.00 0 0 0 0
Back to Advisory Statistics
Back to Top
Java Pool Advisory
Java Pool Size(M) JP 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
64 1.00 7 198 143 1.00 1,267 1.00 1,949
128 2.00 9 242 143 1.00 1,267 1.00 2,384
Back to Advisory Statistics
Back to Top
Wait Statistics
Buffer Wait Statistics
Enqueue Activity
Back to Top
Buffer Wait Statistics
ordered by wait time desc, waits desc
Class Waits Total Wait Time (s) Avg Time (ms)
undo block 77 0 0
undo header 71 0 0
data block 54 0 0
1st level bmb 1 0 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
Undo Segment Summary
Undo Segment Stats
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, your - 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
2 12.51 15,750 1,262 5 15/33 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
24-Nov 11:51 648 788 382 4 18 0/0 0/0/0/0/0/0
24-Nov 11:41 692 838 1,011 5 29 0/0 0/0/0/0/0/0
24-Nov 11:31 459 504 1,065 3 30 0/0 0/0/0/0/0/0
24-Nov 11:21 467 599 1,038 5 29 0/0 0/0/0/0/0/0
24-Nov 11:11 547 612 438 3 19 0/0 0/0/0/0/0/0
24-Nov 11:01 689 1,040 1,066 4 30 0/0 0/0/0/0/0/0
24-Nov 10:51 637 786 466 3 20 0/0 0/0/0/0/0/0
24-Nov 10:41 450 501 1,095 3 30 0/0 0/0/0/0/0/0
24-Nov 10:31 427 552 494 3 20 0/0 0/0/0/0/0/0
24-Nov 10:21 535 904 1,122 4 31 0/0 0/0/0/0/0/0
24-Nov 10:11 575 616 521 3 21 0/0 0/0/0/0/0/0
24-Nov 10:01 658 774 1,150 3 31 0/0 0/0/0/0/0/0
24-Nov 09:51 322 356 550 3 21 0/0 0/0/0/0/0/0
24-Nov 09:41 465 586 1,178 3 32 0/0 0/0/0/0/0/0
24-Nov 09:31 543 645 578 4 22 0/0 0/0/0/0/0/0
24-Nov 09:21 576 628 1,206 3 32 0/0 0/0/0/0/0/0
24-Nov 09:11 571 969 605 3 22 0/0 0/0/0/0/0/0
24-Nov 09:01 345 349 1,233 4 33 0/0 0/0/0/0/0/0
24-Nov 08:51 524 636 632 3 23 0/0 0/0/0/0/0/0
24-Nov 08:41 522 656 1,233 4 33 0/0 0/0/0/0/0/0
24-Nov 08:31 571 627 632 4 23 0/0 0/0/0/0/0/0
24-Nov 08:21 594 966 1,262 3 33 0/0 0/0/0/0/0/0
24-Nov 08:11 475 521 661 3 23 0/0 0/0/0/0/0/0
24-Nov 08:01 222 297 115 2 15 0/0 0/0/0/0/0/0
Back to Undo Statistics
Back to Top
Latch Statistics
Latch Activity
Latch Sleep Breakdown
Latch Miss Sources
Mutex Sleep Summary
Parent Latch Statistics
Child 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 9,624 0.00 0 0
ASM map operation hash table 4 0.00 0 0
AWR Alerted Metric Element list 100,379 0.00 0 0
Change Notification Hash table latch 4,804 0.00 0 0
Consistent RBA 14,267 0.00 0 0
DML lock allocation 3,760,586 0.00 0.25 0 0
Event Group Locks 112 0.00 0 0
FAL request queue 388 0.00 0 0
FAL subheap alocation 388 0.00 0 0
FIB s.o chain latch 32 0.00 0 0
FOB s.o list latch 296 0.00 0 0
File State Object Pool Parent Latch 4 0.00 0 0
IPC stats buffer allocation latch 4 0.00 0 0
In memory undo latch 4,584 0.00 0 3,236 0.00
JOX JIT latch 4 75.00 1.00 0 4 0.00
JOX SGA heap latch 3,456 0.00 0 0
JS Sh mem access 30 26.67 1.00 0 0
JS mem alloc latch 24 0.00 0 0
JS queue access latch 28 0.00 0 0
JS queue state obj latch 104,608 0.00 0 0
JS slv state obj latch 17,622 0.00 0 0
KFC FX Hash Latch 4 0.00 0 0
KFC Hash Latch 4 0.00 0 0
KFCL LE Freelist 4 0.00 0 0
KFR redo allocation latch 4 0.00 0 0
KGNFS-NFS:SHM structure 4 0.00 0 0
KGNFS-NFS:SVR LIST 4 0.00 0 0
KJC message pool free list 4 0.00 0 0
KJCT flow control latch 4 0.00 0 0
KMG MMAN ready and startup request latch 4,804 0.00 0 0
KTF sga latch 40 0.00 0 4,036 0.00
KWQP Prop Status 8 0.00 0 0
KWQS pqueue ctx latch 8 0.00 0 0
Locator state objects pool parent latch 4 0.00 0 0
MQL Tracking Latch 0 0 288 0.00
Memory Management Latch 4 0.00 0 4,804 0.00
Memory Queue 4 0.00 0 0
Memory Queue Message Subscriber #1 4 0.00 0 0
Memory Queue Message Subscriber #2 4 0.00 0 0
Memory Queue Message Subscriber #3 4 0.00 0 0
Memory Queue Message Subscriber #4 4 0.00 0 0
Memory Queue Subscriber 4 0.00 0 0
MinActiveScn Latch 508,609 0.00 0.00 0 0
Mutex 4 0.00 0 0
Mutex Stats 4 0.00 0 0
OS process 910 0.00 0 0
OS process allocation 5,404 0.00 0 0
OS process: request allocation 220 0.00 0 0
PL/SQL warning settings 12,935 0.00 0 0
QMT 4 0.00 0 0
Real-time plan statistics latch 16,839 0.02 0.00 0 0
Reserved Space Latch 12 0.00 0 0
SGA blob parent 4 0.00 0 0
SGA bucket locks 4 0.00 0 0
SGA heap locks 4 0.00 0 0
SGA pool locks 4 0.00 0 0
SQL memory manager latch 484 0.00 0 4,797 0.00
SQL memory manager workarea list latch 325,210 0.00 0 0
Shared B-Tree 529 0.00 0 0
Streams Generic 4 0.00 0 0
Testing 4 0.00 0 0
Token Manager 4 0.00 0 0
Write State Object Pool Parent Latch 4 0.00 0 0
XDB NFS Security Latch 4 0.00 0 0
XDB unused session pool 4 0.00 0 0
XDB used session pool 4 0.00 0 0
active checkpoint queue latch 12,150 0.00 0 0
active service list 29,700 0.00 0 4,870 0.00
archive control 347 0.00 0 0
archive process latch 5,573 0.04 1.00 0 0
begin backup scn array 14 0.00 0 0
buffer pool 4 0.00 0 0
business card 4 0.00 0 0
cache buffer handles 910,665 0.01 0.00 0 0
cache buffers chains 828,398,958 0.21 0.00 79 69,837 0.06
cache buffers lru chain 67,930 0.00 1.00 0 125,544 0.00
cache table scan latch 142 0.00 0 142 0.00
cas latch 4 0.00 0 0
change notification client cache latch 4 0.00 0 0
channel handle pool latch 232 0.00 0 0
channel operations parent latch 77,816 0.00 0 0
checkpoint queue latch 271,062 0.00 0 26,583 0.00
client/application info 385 0.00 0 0
commit callback allocation 4 0.00 0 0
compile environment latch 146 0.00 0 0
corrupted undo seg lock 131,314 0.00 0 0
cp cmon/server latch 4 0.00 0 0
cp pool latch 4 0.00 0 0
cp server hash latch 4 0.00 0 0
cp sga latch 270 0.00 0 0
cv apply list lock 4 0.00 0 0
cv free list lock 4 0.00 0 0
deferred cleanup latch 270 0.00 0 0
dml lock allocation 270 0.00 0 0
done queue latch 4 0.00 0 0
dummy allocation 296 0.00 0 0
enqueue hash chains 7,360,575 0.01 0.03 0 0
enqueues 5,283,794 0.05 0.03 0 0
event range base latch 1 0.00 0 0
fifth spare latch 4 0.00 0 0
file cache latch 874 0.00 0 0
flashback archiver latch 47 0.00 0 0
flashback copy 4 0.00 0 0
gc element 4 0.00 0 0
gcs commit scn state 4 0.00 0 0
gcs partitioned table hash 4 0.00 0 0
gcs pcm hashed value bucket hash 4 0.00 0 0
gcs resource freelist 4 0.00 0 0
gcs resource hash 4 0.00 0 0
gcs resource scan list 4 0.00 0 0
gcs shadows freelist 4 0.00 0 0
ges domain table 4 0.00 0 0
ges enqueue table freelist 4 0.00 0 0
ges group table 4 0.00 0 0
ges process hash list 4 0.00 0 0
ges process parent latch 4 0.00 0 0
ges resource hash list 4 0.00 0 0
ges resource scan list 4 0.00 0 0
ges resource table freelist 4 0.00 0 0
ges value block free list 4 0.00 0 0
global KZLD latch for mem in SGA 12 0.00 0 0
global tx hash mapping 2,424,444 0.00 0.00 0 0
granule operation 4 0.00 0 0
hash table column usage latch 2,271 0.00 0 882,892 0.01
hash table modification latch 310 0.00 0 0
intra txn parallel recovery 4 0.00 0 0
io pool granule metadata list 4 0.00 0 0
job workq parent latch 4 0.00 0 72 0.00
job_queue_processes free list latch 16 0.00 0 0
job_queue_processes parameter latch 277 0.00 0 0
k2q lock allocation 4 0.00 0 0
kdlx hb parent latch 4 0.00 0 0
kgb parent 4 0.00 0 0
kks stats 683,031 0.11 0.01 0 0
kokc descriptor allocation latch 120 0.00 0 0
ksfv messages 4 0.00 0 0
kss move lock 130 0.00 0 0
ksuosstats global area 973 0.00 0 0
ksv allocation latch 522 0.00 0 0
ksv class latch 256 0.00 0 0
ksv msg queue latch 4 0.00 0 0
ksz_so allocation latch 220 0.00 0 0
ktm global data 147 0.00 0 0
kwqbsn:qsga 513 0.00 0 0
lgwr LWN SCN 14,565 0.03 0.00 0 0
library cache load lock 10,360 0.00 0 0
list of block allocation 9,337 0.00 0 0
loader state object freelist 56 0.00 0 0
lob segment dispenser latch 4 0.00 0 0
lob segment hash table latch 52 0.00 0 0
lob segment query latch 4 0.00 0 0
lock DBA buffer during media recovery 4 0.00 0 0
logical standby cache 4 0.00 0 0
logminer context allocation 8 0.00 0 0
logminer work area 4 0.00 0 0
longop free list parent 4 0.00 0 0
mapped buffers lru chain 4 0.00 0 0
message pool operations parent latch 4 0.00 0 0
messages 168,906 0.01 0.00 0 0
mostly latch-free SCN 14,582 0.15 0.00 0 0
msg queue latch 4 0.00 0 0
multiblock read objects 462 0.00 0 0
name-service namespace bucket 4 0.00 0 0
ncodef allocation latch 270 0.00 0 0
object queue header heap 24,365 0.00 0 20 0.00
object queue header operation 136,230 0.00 0.50 0 0
object stats modification 57 0.00 0 0
parallel query alloc buffer 1,924 0.00 0 0
parallel query stats 4 0.00 0 0
parameter list 43 0.00 0 0
parameter table management 304 0.00 0 0
peshm 4 0.00 0 0
pesom_free_list 2,308 0.00 0 0
pesom_hash_node 2,696 0.00 0 0
pesom_heap_alloc 28 0.00 0 0
post/wait queue 18,628 0.00 0 12,396 0.00
process allocation 315 0.00 0 108 0.00
process group creation 220 0.00 0 0
process queue 4 0.00 0 0
process queue reference 4 0.00 0 0
qmn task queue latch 2,053 0.00 0 0
queued dump request 48 0.00 0 0
recovery domain hash list 4 0.00 0 0
redo allocation 49,330 0.02 0.18 0 461,742 0.01
redo copy 4 0.00 0 461,758 0.13
redo writing 65,069 0.00 0 0
reservation so alloc latch 8 0.00 0 0
resmgr group change latch 90 0.00 0 0
resmgr:active threads 296 0.00 0 0
resmgr:actses change group 116 0.00 0 0
resmgr:actses change state 4 0.00 0 0
resmgr:free threads list 292 0.00 0 0
resmgr:plan CPU method 4 0.00 0 0
resmgr:resource group CPU method 4 0.00 0 0
resmgr:schema config 124 0.00 0 0
resmgr:session queuing 4 0.00 0 0
rm cas latch 4 0.00 0 0
row cache objects 29,439,955 0.12 0.00 0 1,070 0.37
rules engine rule set statistics 400 0.00 0 0
second spare latch 4 0.00 0 0
sequence cache 4,740 0.00 0 0
session allocation 665,556 0.00 0.08 0 0
session idle bit 7,405,425 0.00 0.04 0 0
session queue latch 4 0.00 0 0
session state list latch 448 0.00 0 0
session switching 274 0.00 0 0
session timer 4,870 0.00 0 0
shared pool 12,132,312 0.16 0.03 6 0
shared pool sim alloc 1,842 0.00 0 0
shared pool simulator 3,661,160 0.00 0.13 0 0
sim partition latch 4 0.00 0 0
simulator hash latch 25,124,311 0.00 0.05 0 0
simulator lru latch 25,122,987 0.13 0.02 20 530 0.00
sort extent pool 432 0.00 0 0
space background state object latch 30 0.00 0 0
space background task latch 10,652 0.02 0.00 0 9,608 0.00
state object free list 8 0.00 0 0
statistics aggregation 2,240 0.00 0 0
tablespace key chain 4 0.00 0 0
temp lob duration state obj allocation 8 0.00 0 0
test excl. parent l0 4 0.00 0 0
test excl. parent2 l0 4 0.00 0 0
third spare latch 4 0.00 0 0
threshold alerts latch 665 0.00 0 0
transaction allocation 795,349 0.00 0.00 0 0
transaction branch allocation 2,404,564 0.02 0.03 0 0
undo global data 555,216 0.00 0.00 0 0
user lock 134 0.00 0 0
virtual circuit buffers 4 0.00 0 0
virtual circuit holder 4 0.00 0 0
virtual circuit queues 4 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
cache buffers chains 828,398,958 1,759,265 3,926 1,755,451
row cache objects 29,439,955 34,571 82 34,490
simulator lru latch 25,122,987 33,912 833 33,095
shared pool 12,132,312 19,551 653 18,915
enqueues 5,283,794 2,635 78 2,557
enqueue hash chains 7,360,575 1,042 30 1,012
kks stats 683,031 765 5 760
transaction branch allocation 2,404,564 517 15 502
session idle bit 7,405,425 285 10 275
simulator hash latch 25,124,311 153 8 146
session allocation 665,556 26 2 24
shared pool simulator 3,661,160 24 3 21
redo allocation 49,330 11 2 9
DML lock allocation 3,760,586 8 2 6
JS Sh mem access 30 8 8 0
JOX JIT latch 4 3 3 0
archive process latch 5,573 2 2 0
object queue header operation 136,230 2 1 1
cache buffers lru chain 67,930 1 1 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
DML lock allocation ktadmc 0 2 0
JS Sh mem access jsksGetShMemLatch 0 8 8
PC and Classifier lists for WLM No latch 0 3 3
archive process latch kcrrsarc 0 2 0
cache buffers chains kcbgtcr_9 0 2,046 1,312
cache buffers chains kcbgkcbcr 0 955 805
cache buffers chains kcbrls_2 0 714 1,677
cache buffers chains kcbgtcr: fast path 0 295 41
cache buffers chains kcbgtcr: fast path (cr pin) 0 114 248
cache buffers chains kcbchg1: kslbegin: bufs not pinned 0 65 20
cache buffers chains kcbrls: fast release 0 35 112
cache buffers chains kcbgcur_2 0 15 4
cache buffers chains kcbget: pin buffer 0 15 4
cache buffers chains kcbzgb: scan from tail. nowait 0 8 0
cache buffers chains kcbchg1: kslbegin: call CR func 0 3 5
cache buffers chains kcbgcur: fast path (shr) 0 2 2
cache buffers chains kcbnew: new latch again 0 2 0
cache buffers chains kcbbxsv 0 1 1
cache buffers chains kcbgtcr: kslbegin excl 0 1 20
cache buffers lru chain kcbzgws 0 1 0
enqueue hash chains ksqrcl 0 14 1
enqueue hash chains ksqcnl 0 9 0
enqueue hash chains ksqgtl3 0 7 29
enqueues ksqgel: create enqueue 0 40 74
enqueues ksqdel 0 38 4
kks stats kksAllocChildStat 0 5 5
object queue header operation kcbo_switch_cq 0 1 0
redo allocation kcrfw_redo_gen: redo allocation 1 0 2 0
row cache objects kqrpre: find obj 0 39 78
row cache objects kqreqd: reget 0 34 0
row cache objects kqreqd 0 8 0
session allocation ksudlc 0 1 1
session allocation ksudlp 0 1 0
session idle bit ksupuc: clear busy 0 6 3
session idle bit ksupuc: set busy 0 4 7
shared pool kghalo 0 372 207
shared pool kghupr1 0 176 287
shared pool kghalp 0 80 135
shared pool kghfre 0 21 18
shared pool kgh_heap_sizes 0 3 6
shared pool kghfrunp: alloc: session dur 0 1 0
shared pool kghfrunp: clatch: wait 0 1 4
shared pool simulator kglsim_unpin_simhp 0 2 1
shared pool simulator kglsim_upd_newhp 0 1 0
simulator hash latch kcbsacc: lookup dba 0 8 8
simulator lru latch kcbs_simulate: simulate set 0 833 833
transaction branch allocation ksupuc 0 8 14
transaction branch allocation ktcdbr 0 5 0
transaction branch allocation ktcbba 0 2 1
Back to Latch Statistics
Back to Top
Mutex Sleep Summary
No data exists for this section of the report.
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
Segments by Logical Reads
Segments by Physical Reads
Segments by Row Lock Waits
Segments by ITL Waits
Segments by Buffer Busy Waits
Back to Top
Segments by Logical Reads
Total Logical Reads: 365,613,419
Captured Segments account for 99.5% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Logical Reads %Total
MEMPERF MEMPERFTBL FK_CPPR_CCT_PRF_ID INDEX 177,108,640 48.44
MEMPERF MEMPERFTBL CNTCT_PREF_PT_RL TABLE 111,935,616 30.62
MEMPERF MEMPERFTBL CONTACT_PREFERENCE TABLE 19,885,296 5.44
MEMPERF MEMPERFTBL PARTY_NAME TABLE 19,816,240 5.42
MEMPERF MEMPERFINDX XPKCONTACT_POINT INDEX 17,042,544 4.66
Back to Segment Statistics
Back to Top
Segments by Physical Reads
Total Physical Reads: 2,469
Captured Segments account for 9.5% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
SYS SYSAUX WRH$_ACTIVE_SESSION_HISTORY 6971816_75 TABLE PARTITION 153 6.20
SYS SYSAUX WRH$_SYSMETRIC_HISTORY TABLE 33 1.34
SYS SYSAUX WRH$_SQL_PLAN TABLE 18 0.73
SYS SYSAUX WRH$_MEM_DYNAMIC_COMP_PK INDEX 8 0.32
SYS SYSAUX WRH$_MEM_DYNAMIC_COMP TABLE 6 0.24
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
% of Capture shows % of Buffer Busy Waits for each top segment compared
with total Buffer Busy Waits for all segments captured by the Snapshot
Owner Tablespace Name Object Name Subobject Name Obj. Type Buffer Busy Waits % of Capture
MEMPERF MEMPERFINDX XPKCONTACT_POINT INDEX 8 14.55
MEMPERF MEMPERFINDX XPKAGREEMENT INDEX 6 10.91
MEMPERF MEMPERFINDX XPKTELEPHONE_NUMBE INDEX 4 7.27
MEMPERF MEMPERFTBL POSTAL_ADDRESS TABLE 3 5.45
MEMPERF MEMPERFINDX XPKFINANCIAL_TRANS INDEX 3 5.45
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 260 0.00 0 8 1
dc_database_links 33 0.00 0 0 1
dc_global_oids 288 0.00 0 0 134
dc_histogram_data 346,822 0.19 0 0 1,028
dc_histogram_defs 4,331,392 0.08 0 0 3,890
dc_object_grants 88 43.18 0 0 106
dc_objects 3,423,241 0.00 0 20 2,639
dc_profiles 56 0.00 0 0 1
dc_rollback_segments 4,164 0.00 0 0 27
dc_segments 1,246,048 0.02 0 165 978
dc_sequences 87 12.64 0 87 8
dc_tablespace_quotas 532 0.00 0 0 4
dc_tablespaces 135,918 0.00 0 0 10
dc_users 544,736 0.00 0 0 185
global database name 9,771 0.00 0 0 1
outstanding_alerts 192 0.00 0 0 7
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 312 0.00 717 1.67 12 0
CLUSTER 759 1.45 458 5.46 14 0
INDEX 288 12.15 240 33.33 45 0
JAVA DATA 8 0.00 0 0 0
SQL AREA 451,564 73.16 1,999,822 25.48 4,459 98
TABLE/PROCEDURE 4,837,172 0.01 568,693 0.84 3,514 0
TRIGGER 12,301 0.03 12,301 0.06 3 0
Back to Top
Memory Statistics
Memory Dynamic Components
Memory Resize Operations Summary
Memory Resize Ops
Process Memory Summary
SGA Memory Summary
SGA breakdown difference
Back to Top
Memory Dynamic Components
Min/Max sizes since instance startup
Oper Types/Modes: INItializing,GROw,SHRink,STAtic/IMMediate,DEFerred
ordered by Component
Component Begin Snap Size (Mb) Current Size (Mb) Min Size (Mb) Max Size (Mb) Oper Count Last Op Typ/Mod
ASM Buffer Cache 0.00 0.00 0.00 0.00 0 STA/
DEFAULT 16K buffer cache 0.00 0.00 0.00 0.00 0 STA/
DEFAULT 2K buffer cache 0.00 0.00 0.00 0.00 0 STA/
DEFAULT 32K buffer cache 0.00 0.00 0.00 0.00 0 STA/
DEFAULT 4K buffer cache 0.00 0.00 0.00 0.00 0 STA/
DEFAULT 8K buffer cache 0.00 0.00 0.00 0.00 0 STA/
DEFAULT buffer cache 1,664.00 1,664.00 1,664.00 3,712.00 0 SHR/DEF
KEEP buffer cache 0.00 0.00 0.00 0.00 0 STA/
PGA Target 3,328.00 3,328.00 3,328.00 3,328.00 0 STA/
RECYCLE buffer cache 0.00 0.00 0.00 0.00 0 STA/
SGA Target 4,864.00 4,864.00 4,864.00 4,864.00 0 STA/
Shared IO Pool 0.00 0.00 0.00 0.00 0 STA/
java pool 64.00 64.00 64.00 64.00 0 STA/
large pool 64.00 64.00 64.00 64.00 0 STA/
shared pool 2,880.00 2,880.00 832.00 2,880.00 0 GRO/DEF
streams pool 128.00 128.00 128.00 128.00 0 STA/
Back to Memory Statistics
Back to Top
Memory Resize Operations Summary
No data exists for this section of the report.
Back to Memory Statistics
Back to Top
Memory Resize Ops
No data exists for this section of the report.
Back to 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 193.93 3.08 5.71 22 23 63 63
Freeable 21.63 0.00 1.03 1.13 5 21 21
SQL 2.81 1.75 0.06 0.10 1 10 47 43
JAVA 2.42 2.41 1.21 1.71 2 4 2 2
PL/SQL 0.76 0.57 0.01 0.01 0 0 61 61
E Other 181.71 3.08 5.92 22 23 59 59
Freeable 29.19 0.00 0.94 0.98 5 31 31
SQL 2.56 1.43 0.06 0.05 0 10 43 40
JAVA 2.42 2.41 2.42 0.00 2 3 1 1
PL/SQL 0.67 0.51 0.01 0.01 0 0 57 57
Back to Memory Statistics
Back to Top
SGA Memory Summary
SGA regions Begin Size (Bytes) End Size (Bytes) (if different)
Database Buffers 1,744,830,464
Fixed Size 2,139,912
Redo Buffers 26,607,616
Variable Size 6,777,997,560
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 51.98 51.98 0.00
java joxlod exec hp 11.57 11.57 0.00
large PX msg pool 0.99 0.99 0.00
large free memory 63.01 63.01 0.00
shared KGL handle 175.02 187.91 7.37
shared PCursor 45.25
shared free memory 2,407.48 2,279.16 -5.33
shared kglsim object batch 34.54 39.72 15.00
shared sql area 77.70
streams free memory 128.00 128.00 0.00
buffer_cache 1,664.00 1,664.00 0.00
fixed_sga 2.04 2.04 0.00
log_buffer 25.38 25.38 0.00
Back to Memory Statistics
Back to Top
Streams Statistics
Streams CPU/IO Usage
Streams Capture
Streams Apply
Buffered Queues
Buffered Subscribers
Rule Set
Persistent Queues
Persistent Subscribers
Back to Top
Streams CPU/IO Usage
Streams processes ordered by CPU usage
CPU and I/O Time in micro seconds
Session Type CPU Time User I/O Time Sys I/O Time
QMON Slaves 210,000 0 0
QMON Coordinator 150,000 0 0
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
Rule Sets ordered by Evaluations
Ruleset Name Evals Fast Evals SQL Execs CPU Time Elapsed Time
SYS.ALERT_QUE_R 0 0 0 0 0
Back to Streams Statistics
Back to Top
Persistent Queues
No data exists for this section of the report.
Back to Streams Statistics
Back to Top
Persistent Subscribers
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
if IP/Public/Source at End snap is different a '*' is displayed
Parameter Name Begin value End value (if different)
audit_file_dest /opt/oracle/admin/mpqa/adump
audit_trail DB
compatible 11.1.0.0.0
control_files /u01/oracle/oradata/mpqa/control01.ctl, /u01/oracle/oradata/mpqa/control02.ctl, /u02/oracle/oradata/mpqa/control03.ctl
cpu_count 4 6
db_block_size 8192
db_domain
db_name mpqa
db_recovery_file_dest /u04/oracle/flash_recovery_area
db_recovery_file_dest_size 5368709120
diagnostic_dest /opt/oracle
dispatchers (PROTOCOL=TCP) (SERVICE=mpqaXDB)
log_archive_format %t_%s_%r.arc
memory_target 8589934592
open_cursors 300
processes 300
remote_login_passwordfile EXCLUSIVE
sessions 335
undo_tablespace UNDOTBS1
Back to Top
End of Report
|
|
|
|
|
|
| Re: awr report [message #452958 is a reply to message #452911] |
Mon, 26 April 2010 10:20  |
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
|
Senior Member |
|
|
First, please get yourself some books on tuning - Michel is right in that just throwing out an AWR report to the world and asking us to comment is just plain doing your job for you. Asking a specific question about something you are seeing on an AWR report would be something different, IMHO.
However, with that said - you have a single SQL statement that seems to be a cause of some of your issues - I would start there. (Hint: it's on the top of your top 5 SQL list . . . ) Also, you really aren't doing yourself any favors by doing an AWR report of snapshots that cover four hours of processing - I would suggest you run AWR reports for each hour individually. It would decrease the granularity of your report from 240 minutes to 60 minutes - assuming your system is doing the default 60 minute snapshot. Sometimes when troubleshooting performance issues, it is helpful to reduce your snapshot interval to 15 minutes or so. This will allow you to zero in on processes that run at certain times in the hour, or to see performance trends better. A four hour snapshot will give you a big picture view - performance issues usually need a more concise view.
Go to Amazon.com and search for 'oracle performance tuning'. I would recommend any of the Oracle Press books, and any books by Cary Milsap.
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 12 22:09:29 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
