Re: AWR Sample Report
From: raja <dextersunil_at_gmail.com>
Date: Mon, 17 Nov 2008 22:38:11 -0800 (PST)
Message-ID: <ebc7afb7-c463-4ce4-9d80-715b1f004e5f@d36g2000prf.googlegroups.com>
DB Time: 1,723.72 (mins)
Logical reads: 393,203.94 349,292.97
Block changes: 3,687.61 3,275.79
% SQL with executions>1: 95.58 97.42
% Memory for SQL w/exec>1: 95.17 97.31
log file sync 17,706 0.10 387 22 0.73
direct path write temp 98,515 0.00 313 3 4.06 SQL*Net more data to client 4,642,420 0.00 154 0 191.21 enq: TX - row lock contention 1,475 3.19 138 94 0.06 control file parallel write 10,484 0.00 107 10 0.43 log file sequential read 12,839 0.00 87 7 0.53 read by other session 40,059 0.00 79 2 1.65 direct path read 272,647 0.00 54 0 11.23 log buffer space 181 0.00 46 256 0.01
enq: WL - contention 16 87.50 44 2742 0.00 latch: cache buffers chains 363,571 0.00 37 0 14.97 control file single write 448 0.00 28 63 0.02 os thread startup 424 0.00 23 55 0.02
RMAN backup & recovery I/O 6,130 0.00 18 3 0.25 control file sequential read 955,614 0.00 9 0 39.36 log file switch completion 75 0.00 7 89 0.00 SQL*Net more data from dblink 127,162 0.00 6 0 5.24 cursor: pin S wait on X 365 100.00 4 10 0.02 SQL*Net message to client 4,819,165 0.00 4 0 198.49 switch logfile command 7 0.00 2 268 0.00 Backup: sbtinfo2 41 0.00 2 37 0.00
enq: CF - contention 14 0.00 1 89 0.00
latch free 181 0.00 1 6 0.01
enq: KO - fast object checkpoint 47 0.00 1 21 0.00 Backup: sbtremove2 30 0.00 1 30 0.00
buffer busy waits 2,272 0.00 1 0 0.09
Backup: sbtclose2 22 0.00 1 29 0.00
Backup: sbtbackup 22 0.00 1 24 0.00
rdbms ipc reply 12 0.00 1 43 0.00
SQL*Net more data from client 5,009 0.00 0 0 0.21 log file single write 80 0.00 0 4 0.00
enq: RO - fast object reuse 15 0.00 0 19 0.00 db file parallel read 14 0.00 0 17 0.00
PX Deq: Signal ACK 28 21.43 0 8 0.00
direct path write 1,712 0.00 0 0 0.07
latch: shared pool 24 0.00 0 9 0.00
Backup: sbtinit 16 0.00 0 9 0.00
enq: PR - contention 3 0.00 0 42 0.00
SQL*Net more data to dblink 3,080 0.00 0 0 0.13 SQL*Net break/reset to client 508 0.00 0 0 0.02 kksfbc child completion 2 100.00 0 49 0.00 SQL*Net message to dblink 43,646 0.00 0 0 1.80 local write wait 6 0.00 0 12 0.00
LGWR wait for redo copy 936 0.00 0 0 0.04 jobq slave TJ process wait 5 100.00 0 10 0.00 latch: cache buffers lru chain 25 0.00 0 2 0.00 direct path read temp 579 0.00 0 0 0.02
latch: object queue header operation 44 0.00 0 1 0.00 reliable message 61 0.00 0 0 0.00
latch: library cache 11 0.00 0 2 0.00
Backup: sbtend 17 0.00 0 1 0.00
latch: session allocation 6 0.00 0 2 0.00 PX Deq Credit: send blkd 10 0.00 0 0 0.00
enq: PS - contention 2 0.00 0 0 0.00
Backup: sbtinit2 16 0.00 0 0 0.00
latch: redo writing 1 0.00 0 0 0.00
cursor: mutex S 15 0.00 0 0 0.00
cursor: pin S 2 0.00 0 0 0.00
SQL*Net message from client 4,819,163 0.00 552,203 115 198.49 jobq slave wait 25,556 86.15 69,777 2730 1.05 Streams AQ: qmn slave idle wait 766 0.00 21,012 27431 0.03 Streams AQ: qmn coordinator idle wait 1,583 51.61 21,012 13273 0.07 PX Idle Wait 4,235 98.70 8,209 1938 0.17 Streams AQ: waiting for time management or cleanup tasks 5 100.00 5,361 1072103 0.00
SQL*Net message from dblink 43,646 0.00 2,188 50 1.80 single-task message 3,080 0.00 533 173 0.13
class slave wait 46 0.00 0 0 0.00
rdbms ipc message 152,029 73.00 344,964 2269 6.26 Streams AQ: qmn slave idle wait 766 0.00 21,012 27431 0.03 Streams AQ: qmn coordinator idle wait 1,583 51.61 21,012 13273 0.07 pmon timer 7,148 100.00 20,935 2929 0.29 smon timer 111 53.15 20,131 181360 0.00
Streams AQ: waiting for time management or cleanup tasks 5 100.00 5,361 1072103 0.00
AVG_SYS_TIME 112,958
AVG_USER_TIME 659,771
OS_CPU_WAIT_TIME 11,375,200
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 82,678,112,256
NUM_CPUS 14
NUM_CPU_CORES 7 Instance Activity Stats
Statistic Total per Second per Trans
CPU used by this session 2,760,501 127.99 113.70 CPU used when call started 2,065,134 95.75 85.06 CR blocks created 116,826 5.42 4.81
Cached Commit SCN referenced 35,858,913 1,662.63 1,476.95 Commit SCN cached 1,088 0.05 0.04
DB time 14,230,583 659.81 586.13
DBWR checkpoint buffers written 2,786,759 129.21 114.78 DBWR checkpoints 82 0.00 0.00
DBWR object drop buffers written 28 0.00 0.00 DBWR parallel query checkpoint buffers written 0 0.00 0.00 DBWR revisited being-written buffer 4 0.00 0.00 DBWR tablespace checkpoint buffers written 579 0.03 0.02 DBWR thread checkpoint buffers written 0 0.00 0.00 DBWR transaction table writes 665 0.03 0.03 DBWR undo block writes 879,979 40.80 36.24 DFO trees parallelized 2 0.00 0.00
IMU CR rollbacks 1,266 0.06 0.05
IMU Flushes 6,465 0.30 0.27
IMU Redo allocation size 11,813,948 547.76 486.59 IMU commits 17,702 0.82 0.73
IMU contention 1,089 0.05 0.04
IMU ktichg flush 1,657 0.08 0.07
IMU pool not allocated 112 0.01 0.00
IMU recursive-transaction flush 3 0.00 0.00 IMU undo allocation size 65,066,320 3,016.85 2,679.94 IMU- failed to get a private strand 112 0.01 0.00 Misses for writing mapping 0 0.00 0.00
PX local messages recv'd 368 0.02 0.02
PX local messages sent 368 0.02 0.02
Parallel operations not downgraded 2 0.00 0.00 SMON posted for undo segment shrink 0 0.00 0.00
background checkpoints completed 21 0.00 0.00 background checkpoints started 20 0.00 0.00 background timeouts 111,870 5.19 4.61
branch node splits 19 0.00 0.00
buffer is not pinned count 7,406,205,468 343,394.21 305,045.74 buffer is pinned count 1,342,723,816 62,256.39 55,303.92 bytes received via SQL*Net from client 606,443,091 28,118.18 24,978.09
bytes received via SQL*Net from dblink 265,807,835 12,324.38 10,948.06
bytes sent via SQL*Net to client 18,910,995,749 876,822.35 778,903.40 bytes sent via SQL*Net to dblink 25,547,081 1,184.51 1,052.23 calls to get snapshot scn: kcmgss 11,936,890 553.46 491.65 calls to kcmgas 1,248,240 57.88 51.41
calls to kcmgcs 106,173 4.92 4.37
change write time 44,255 2.05 1.82
cleanout - number of ktugct calls 547,076 25.37 22.53 cleanouts and rollbacks - consistent read gets 111,031 5.15 4.57 cleanouts only - consistent read gets 136,836 6.34 5.64 cluster key scan block gets 382,786 17.75 15.77 cluster key scans 278,819 12.93 11.48
commit batch performed 3,080 0.14 0.13
commit batch requested 3,080 0.14 0.13
commit cleanouts successfully completed 708,251 32.84 29.17 commit immediate performed 3 0.00 0.00
commit immediate requested 3 0.00 0.00
commit txn count during cleanout 351,189 16.28 14.46 concurrency wait time 6,507 0.30 0.27
consistent changes 5,328,067 247.04 219.45 consistent gets 8,341,439,937 386,757.05 343,566.04 consistent gets - examination 3,757,741,068 174,230.45 154,773.30 consistent gets direct 6,617,029 306.80 272.54 consistent gets from cache 8,334,822,915 386,450.25 343,293.50 current blocks converted for CR 887 0.04 0.04 cursor authentications 5,542 0.26 0.23
data blocks consistent reads - undo records applied 5,319,562 246.65 219.10
db block changes 79,532,990 3,687.61 3,275.79 db block gets 139,044,324 6,446.89 5,726.94 db block gets direct 1,283 0.06 0.05
db block gets from cache 139,043,041 6,446.83 5,726.89 deferred (CURRENT) block cleanout applications 297,057 13.77 12.24 dirty buffers inspected 833,757 38.66 34.34
enqueue timeouts 1,886 0.09 0.08
enqueue waits 1,397 0.06 0.06
execute count 11,673,423 541.25 480.80
failed probes on index block reclamation 714 0.03 0.03 free buffer inspected 11,179,231 518.33 460.45 free buffer requested 10,381,781 481.36 427.60 heap block compress 164,584 7.63 6.78
hot buffers moved to head of LRU 5,449,531 252.67 224.45 immediate (CR) block cleanout applications 247,867 11.49 10.21 immediate (CURRENT) block cleanout applications 385,730 17.88 15.89 index crx upgrade (found) 452 0.02 0.02
index crx upgrade (positioned) 62,987 2.92 2.59 index fast full scans (full) 2,384 0.11 0.10 index fast full scans (rowid ranges) 0 0.00 0.00 index fetch by key 2,698,249,086 125,106.32 111,135.10 index scans kdiixs1 141,091,704 6,541.82 5,811.27 java call heap collected bytes 807,424 37.44 33.26 java call heap collected count 12,592 0.58 0.52 java call heap gc count 48 0.00 0.00
java call heap live object count 12,880 0.60 0.53 java call heap live object count max 12,880 0.60 0.53
leaf node splits 1,553 0.07 0.06
lob reads 2,540 0.12 0.10
lob writes 3,993 0.19 0.16
lob writes unaligned 3,993 0.19 0.16
logons cumulative 5,645 0.26 0.23
messages received 709,812 32.91 29.24
messages sent 709,812 32.91 29.24
no buffer to keep pinned count 0 0.00 0.00 no work - consistent read gets 4,583,137,118 212,500.55 188,769.60 opened cursors cumulative 306,678 14.22 12.63
parse time cpu 51,060 2.37 2.10
parse time elapsed 64,460 2.99 2.65
physical read IO requests 5,242,302 243.06 215.92 physical read bytes 94,035,378,176 4,360,020.07 3,873,115.79 physical read total IO requests 6,234,776 289.08 256.80 physical read total bytes ############### 6,008,005.33 5,337,062.65 physical read total multi block requests 1,241,703 57.57 51.14 physical reads 11,478,928 532.23 472.79
physical reads cache 9,309,733 431.65 383.45 physical reads cache prefetch 4,341,654 201.30 178.82 physical reads direct 2,169,195 100.58 89.34 physical reads direct (lob) 289 0.01 0.01 physical reads direct temporary tablespace 8,813 0.41 0.36 physical reads prefetch warmup 0 0.00 0.00
physical writes direct 3,046,704 141.26 125.49 physical writes direct (lob) 900 0.04 0.04 physical writes direct temporary tablespace 3,044,692 141.17 125.40 physical writes from cache 4,315,990 200.11 177.77 physical writes non checkpoint 5,207,133 241.43 214.47 pinned buffers inspected 764 0.04 0.03
prefetch warmup blocks aged out before use 0 0.00 0.00 prefetched blocks aged out before use 1 0.00 0.00 process last non-idle time 19,633 0.91 0.81 queries parallelized 2 0.00 0.00
recursive aborts on index block reclamation 0 0.00 0.00 recursive calls 12,928,244 599.43 532.49
redo log space wait time 688 0.03 0.03
redo ordering marks 872,628 40.46 35.94
redo size 12,829,048,628 594,828.36 528,401.03 redo subscn max counts 1,425,256 66.08 58.70 redo synch time 39,573 1.83 1.63
redo synch writes 57,879 2.68 2.38
redo wastage 13,394,880 621.06 551.71
redo write time 105,026 4.87 4.33
redo writer latching time 6 0.00 0.00
redo writes 38,622 1.79 1.59
rollback changes - undo records applied 177 0.01 0.01 rollbacks only - consistent read gets 7,194 0.33 0.30 rows fetched via callback 997,973,536 46,271.78 41,104.39 session connect time 0 0.00 0.00
session cursor cache hits 87,057 4.04 3.59 session logical reads 8,480,484,110 393,203.94 349,292.97 session pga memory 7,148,528,160 331,446.81 294,432.56 session pga memory max 18,255,207,648 846,416.25 751,892.90 session uga memory ############### 669,514,241.36 594,746,385.30 session uga memory max 7,658,125,248 355,074.66 315,421.77 shared hash latch upgrades - no wait 6,882,720 319.12 283.48 shared hash latch upgrades - wait 582 0.03 0.02
sql area purged 785 0.04 0.03
summed dirty queue length 2,721,198 126.17 112.08 switch current to new buffer 71,026 3.29 2.93 table fetch by rowid 2,954,474,371 136,986.40 121,688.47 table fetch continued row 21,150 0.98 0.87 table scan blocks gotten 44,917,305 2,082.62 1,850.05 table scan rows gotten 2,282,015,917 105,807.36 93,991.35
transaction tables consistent read rollbacks 13 0.00 0.00 transaction tables consistent reads - undo records applied 636 0.03 0.03
undo change vector size 6,943,980,192 321,962.79 286,007.67 user I/O wait time 1,513,678 70.18 62.35 user calls 4,836,293 224.24 199.20
user commits 24,259 1.12 1.00
user rollbacks 20 0.00 0.00
workarea executions - onepass 29 0.00 0.00 workarea executions - optimal 58,361 2.71 2.40 write clones created in background 6 0.00 0.00 write clones created in foreground 1,658 0.08 0.07
OS process allocation 13,409 0.52 0.00 0 0 OS process: request allocation 4,524 0.24 0.00 0 0 PL/SQL warning settings 19,858 0.00 0 0 SGA IO buffer pool latch 11,021 0.00 0 11,146 0.01 SQL memory manager latch 24 0.00 0 7,151 0.00 SQL memory manager workarea list latch 826,430 0.00 0 0 Shared B-Tree 766 0.00 0 0
X$KSFQP 154 0.00 0 0
active checkpoint queue latch 719,515 1.58 0.00 0 0 active service list 62,196 0.22 0.00 0 7,148 0.00 archive control 1,874 0.00 0 0
archive process latch 7,621 0.00 0 0
begin backup scn array 940 0.00 0 0
cache buffer handles 819,812 0.00 0.00 0 0 cache buffers chains 5,280,355,952 5.32 0.00 37 21,935,227 0.00 cache buffers lru chain 8,818,130 0.11 0.00 0 25,512,853 0.04 cache table scan latch 0 0 934,249 0.01 channel handle pool latch 4,635 0.47 0.00 0 0 channel operations parent latch 109,742 0.01 0.00 0 0 checkpoint queue latch 8,494,017 0.01 0.00 0 4,298,746 0.01 client/application info 33,016 0.00 0 0 commit callback allocation 6,476 0.26 0.00 0 0 compile environment latch 59,891 0.00 0 0 constraint object allocation 6,448 4.23 0.00 0 0 dictionary lookup 18 0.00 0 0
dml lock allocation 122,190 1.68 0.00 0 0 dummy allocation 11,360 0.40 0.00 0 0
enqueue hash chains 789,439 0.26 0.00 0 1,605 0.00 enqueues 585,379 0.23 0.00 0 0
error message lists 114 1.75 0.00 0 0
event group latch 2,255 0.00 0 0
file cache latch 2,148 0.00 0 0
global KZLD latch for mem in SGA 1,644 0.00 0 0 global ctx hash table latch 1 0.00 0 0 global tx hash mapping 41,173 0.01 0.00 0 0 hash table column usage latch 1,511 0.07 0.00 0 5,245,016 0.00 hash table modification latch 189 0.00 0 0 internal temp table object number allocation latc 1,221 0.00 0 0 job workq parent latch 15 100.00 0.00 0 5,952 6.03 job_queue_processes parameter latch 1,736 0.00 0 0 kks stats 33,820 0.00 0 0
krbmrosl 4,485 0.00 0 0
ksuosstats global area 1,486 0.00 0 0
ktm global data 112 0.00 0 0
kwqbsn:qsga 766 0.00 0 0
lgwr LWN SCN 39,301 0.16 0.00 0 0
library cache 2,288,082 1.90 0.00 0 21,060 0.00 library cache load lock 990 0.00 0 0
library cache lock 1,526,021 0.99 0.00 0 0 library cache lock allocation 121,627 0.01 0.00 0 0 library cache pin 604,460 0.90 0.00 0 0
library cache pin allocation 35,107 0.09 0.00 0 0 list of block allocation 87,703 0.52 0.00 0 0 loader state object freelist 4,281 0.00 0 0 longop free list parent 1,334 0.00 0 71 0.00 message pool operations parent latch 292 0.00 0 0 messages 1,694,814 1.06 0.00 0 0
mostly latch-free SCN 39,725 0.98 0.00 0 0 multiblock read objects 3,291,270 0.00 0.00 0 0 ncodef allocation latch 340 0.00 0 0
object queue header heap 654,313 0.00 0 22,441 0.00 object queue header operation 34,346,629 0.02 0.01 0 0 object stats modification 73 0.00 0 0
parallel query alloc buffer 3,420 0.18 0.00 0 0 parallel query stats 62 14.52 0.00 0 0
parameter table allocation management 12,199 1.34 0.00 0 0 post/wait queue 45,298 0.23 0.00 0 17,968 0.00 process allocation 4,526 0.00 0 2,255 0.00 process group creation 4,524 1.17 0.00 0 0 process queue 396 1.01 0.00 0 0
process queue reference 13,762 0.00 0 671 118.78 qmn task queue latch 3,064 0.20 0.00 0 0 query server freelists 286 3.50 0.00 0 0 query server process 28 0.00 0 28 0.00 redo allocation 196,729 0.43 0.00 0 40,747,233 0.05 redo copy 0 0 40,749,514 0.00
redo writing 867,413 1.06 0.00 0 0
resmgr group change latch 7,573 0.00 0 0
row cache objects 9,463,000 0.18 0.00 0 818 0.00 rules engine aggregate statistics 2 0.00 0 0 rules engine rule set statistics 4 0.00 0 0 sequence cache 11,055 0.00 0 0
session allocation 23,149,602 0.30 0.00 0 0 session idle bit 9,769,326 0.00 0.00 0 0 session state list latch 24,658 9.81 0.00 0 0 session switching 340 0.00 0 0
session timer 7,148 0.00 0 0
shared pool 790,880 0.19 0.02 0 0
shared pool sim alloc 3 0.00 0 0
shared pool simulator 366,162 0.00 0 0 simulator hash latch 295,089,755 0.00 0.00 0 0 simulator lru latch 290,100,417 0.33 0.00 0 3,683,779 0.00 slave class 48 0.00 0 0
slave class create 192 7.81 1.07 1 0
sort extent pool 25,433 0.00 0 0
state object free list 48 0.00 0 0
statistics aggregation 2,688 0.00 0 0
temp lob duration state obj allocation 36 0.00 0 0 temporary table state object allocation 13 0.00 0 0 threshold alerts latch 1,575 0.00 0 0
transaction allocation 14,048 0.31 0.00 0 0 transaction branch allocation 13,685 0.31 0.00 0 0 undo global data 35,333,792 0.01 0.00 0 0 user lock 13,415 5.65 0.01 0 0
Variable Size 1,409,288,552
Date: Mon, 17 Nov 2008 22:38:11 -0800 (PST)
Message-ID: <ebc7afb7-c463-4ce4-9d80-715b1f004e5f@d36g2000prf.googlegroups.com>
Hi,
I have another AWR Report generated with 1hr interval. I dont know how to approach this. Please help on this...
AWR Report :
Snap Id Snap Time Sessions Cursors/Session Begin Snap: 10739 13-Nov-08 09:00:45 105 58.2 End Snap: 10763 13-Nov-08 15:00:12 91 75.2Elapsed: 359.46 (mins)
DB Time: 1,723.72 (mins)
Cache Sizes
Begin End Buffer Cache: 6,832M 6,832M Std Block Size: 8KShared Pool Size: 1,264M 1,264M Log Buffer: 14,344K
Load Profile
Per Second Per Transaction
Redo size: 594,828.36 528,401.03
Logical reads: 393,203.94 349,292.97
Block changes: 3,687.61 3,275.79
Physical reads: 532.23 472.79 Physical writes: 341.38 303.25 User calls: 224.24 199.20 Parses: 14.60 12.97 Hard parses: 0.32 0.29 Sorts: 3.20 2.85 Logons: 0.26 0.23 Executes: 541.25 480.80 Transactions: 1.13
% Blocks changed per Read: 0.94 Recursive Call %: 72.78
Rollback per transaction %: 0.08 Rows per Sort: 6101.47 Instance Efficiency Percentages (Target 100%) Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.89 In-memory Sort %: 100.00 Library Hit %: 97.45 Soft Parse %: 97.79 Execute to Parse %: 97.30 Latch Hit %: 95.31 Parse CPU to Parse Elapsd %: 79.21 % Non-Parse CPU: 99.10 Shared Pool Statistics Begin End Memory Usage %: 89.36 92.12
% SQL with executions>1: 95.58 97.42
% Memory for SQL w/exec>1: 95.17 97.31
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class CPU time 56,931 55.0 db file sequential read 4,036,055 13,310 3 12.9 User I/ O db file parallel write 567,009 6,515 11 6.3 System I/O db file scattered read 934,341 1,383 1 1.3 User I/ O log file parallel write 38,621 1,019 26 1.0 SystemI/O
Time Model Statistics
Statistic Name Time (s) % of DB Time sql execute elapsed time 102,885.27 99.48 DB CPU 56,931.50 55.05 Java execution elapsed time 18,173.85 17.57 PL/SQL execution elapsed time 1,146.88 1.11 RMAN cpu time (backup/restore) 782.75 0.76 parse time elapsed 649.38 0.63 hard parse elapsed time 619.03 0.60 failed parse elapsed time 13.16 0.01 connection management call elapsed time 10.55 0.01 inbound PL/SQL rpc elapsed time 7.46 0.01 hard parse (sharing criteria) elapsed time 7.30 0.01 PL/SQL compilation elapsed time 1.10 0.00 repeated bind elapsed time 0.34 0.00 sequence load elapsed time 0.03 0.00 hard parse (bind mismatch) elapsed time 0.02 0.00 DB time 103,423.35 background elapsed time 9,817.43 background cpu time 1,169.73 Wait Class Wait Class Waits %Time-outs Total Wait Time (s) Avg wait (ms) Waits /txn User I/O 5,383,928 0.00 15,139 3 221.75 System I/O 1,606,400 0.00 8,442 5 66.16 Administrative 51,609 0.00 901 17 2.13 Commit 17,706 0.10 387 22 0.73 Network 9,640,482 0.00 165 0 397.07 Application 2,045 2.30 140 68 0.08 Concurrency 366,686 0.10 65 0 15.10 Configuration 257 0.00 53 206 0.01 Other 2,147 38.33 47 22 0.09
Wait Events
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
db file sequential read 4,036,055 0.00 13,310 3 166.24
db file parallel write 567,009 0.00 6,515 11 23.35 db file scattered read 934,341 0.00 1,383 1 38.48 log file parallel write 38,621 0.00 1,019 26 1.59Backup: sbtwrite2 51,438 0.00 896 17 2.12 Log archive I/O 15,175 0.00 658 43 0.63
log file sync 17,706 0.10 387 22 0.73
direct path write temp 98,515 0.00 313 3 4.06 SQL*Net more data to client 4,642,420 0.00 154 0 191.21 enq: TX - row lock contention 1,475 3.19 138 94 0.06 control file parallel write 10,484 0.00 107 10 0.43 log file sequential read 12,839 0.00 87 7 0.53 read by other session 40,059 0.00 79 2 1.65 direct path read 272,647 0.00 54 0 11.23 log buffer space 181 0.00 46 256 0.01
enq: WL - contention 16 87.50 44 2742 0.00 latch: cache buffers chains 363,571 0.00 37 0 14.97 control file single write 448 0.00 28 63 0.02 os thread startup 424 0.00 23 55 0.02
RMAN backup & recovery I/O 6,130 0.00 18 3 0.25 control file sequential read 955,614 0.00 9 0 39.36 log file switch completion 75 0.00 7 89 0.00 SQL*Net more data from dblink 127,162 0.00 6 0 5.24 cursor: pin S wait on X 365 100.00 4 10 0.02 SQL*Net message to client 4,819,165 0.00 4 0 198.49 switch logfile command 7 0.00 2 268 0.00 Backup: sbtinfo2 41 0.00 2 37 0.00
enq: CF - contention 14 0.00 1 89 0.00
latch free 181 0.00 1 6 0.01
enq: KO - fast object checkpoint 47 0.00 1 21 0.00 Backup: sbtremove2 30 0.00 1 30 0.00
buffer busy waits 2,272 0.00 1 0 0.09
Backup: sbtclose2 22 0.00 1 29 0.00
Backup: sbtbackup 22 0.00 1 24 0.00
rdbms ipc reply 12 0.00 1 43 0.00
SQL*Net more data from client 5,009 0.00 0 0 0.21 log file single write 80 0.00 0 4 0.00
enq: RO - fast object reuse 15 0.00 0 19 0.00 db file parallel read 14 0.00 0 17 0.00
PX Deq: Signal ACK 28 21.43 0 8 0.00
direct path write 1,712 0.00 0 0 0.07
latch: shared pool 24 0.00 0 9 0.00
Backup: sbtinit 16 0.00 0 9 0.00
enq: PR - contention 3 0.00 0 42 0.00
SQL*Net more data to dblink 3,080 0.00 0 0 0.13 SQL*Net break/reset to client 508 0.00 0 0 0.02 kksfbc child completion 2 100.00 0 49 0.00 SQL*Net message to dblink 43,646 0.00 0 0 1.80 local write wait 6 0.00 0 12 0.00
LGWR wait for redo copy 936 0.00 0 0 0.04 jobq slave TJ process wait 5 100.00 0 10 0.00 latch: cache buffers lru chain 25 0.00 0 2 0.00 direct path read temp 579 0.00 0 0 0.02
latch: object queue header operation 44 0.00 0 1 0.00 reliable message 61 0.00 0 0 0.00
latch: library cache 11 0.00 0 2 0.00
Backup: sbtend 17 0.00 0 1 0.00
latch: session allocation 6 0.00 0 2 0.00 PX Deq Credit: send blkd 10 0.00 0 0 0.00
latch: redo allocation 3 0.00 0 1 0.00 latch: library cache lock 3 0.00 0 1 0.00 latch: row cache objects 1 0.00 0 3 0.00PX qref latch 797 99.87 0 0 0.03
enq: PS - contention 2 0.00 0 0 0.00
Backup: sbtinit2 16 0.00 0 0 0.00
latch: redo writing 1 0.00 0 0 0.00
cursor: mutex S 15 0.00 0 0 0.00
cursor: pin S 2 0.00 0 0 0.00
SQL*Net message from client 4,819,163 0.00 552,203 115 198.49 jobq slave wait 25,556 86.15 69,777 2730 1.05 Streams AQ: qmn slave idle wait 766 0.00 21,012 27431 0.03 Streams AQ: qmn coordinator idle wait 1,583 51.61 21,012 13273 0.07 PX Idle Wait 4,235 98.70 8,209 1938 0.17 Streams AQ: waiting for time management or cleanup tasks 5 100.00 5,361 1072103 0.00
SQL*Net message from dblink 43,646 0.00 2,188 50 1.80 single-task message 3,080 0.00 533 173 0.13
PX Deq: Execution Msg 237 0.00 9 39 0.01 PX Deq: Msg Fragment 42 0.00 0 3 0.00 PX Deq: Parse Reply 31 0.00 0 3 0.00 PX Deq: Execute Reply 19 0.00 0 2 0.00 PX Deq: Join ACK 26 0.00 0 1 0.00
class slave wait 46 0.00 0 0 0.00
Background Wait Events
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
db file parallel write 567,009 0.00 6,515 11 23.35
log file parallel write 38,621 0.00 1,019 26 1.59
Log archive I/O 13,753 0.00 574 42 0.57
control file parallel write 9,060 0.00 90 10 0.37
log file sequential read 11,638 0.00 69 6 0.48
os thread startup 396 0.00 22 55 0.02
db file sequential read 552 0.00 2 4 0.02
log file sync 31 0.00 1 45 0.00
events in waitclass Other 1,025 0.00 1 1 0.04
db file scattered read 178 0.00 1 4 0.01
direct path read 840 0.00 1 1 0.03
control file sequential read 10,683 0.00 0 0 0.44
log file single write 80 0.00 0 4 0.00
direct path write 840 0.00 0 0 0.03
log buffer space 14 0.00 0 2 0.00
latch: library cache 1 0.00 0 5 0.00 latch: cache buffers chains 2 0.00 0 0 0.00 latch: redo writing 1 0.00 0 0 0.00
rdbms ipc message 152,029 73.00 344,964 2269 6.26 Streams AQ: qmn slave idle wait 766 0.00 21,012 27431 0.03 Streams AQ: qmn coordinator idle wait 1,583 51.61 21,012 13273 0.07 pmon timer 7,148 100.00 20,935 2929 0.29 smon timer 111 53.15 20,131 181360 0.00
Streams AQ: waiting for time management or cleanup tasks 5 100.00 5,361 1072103 0.00
Operating System Statistics
Statistic Total NUM_LCPUS 0 NUM_VCPUS 0 AVG_BUSY_TIME 773,431 AVG_IDLE_TIME 1,382,735 AVG_IOWAIT_TIME 198,722
AVG_SYS_TIME 112,958
AVG_USER_TIME 659,771
BUSY_TIME 10,837,634 IDLE_TIME 19,367,934 IOWAIT_TIME 2,791,750 SYS_TIME 1,591,031 USER_TIME 9,246,603 LOAD 0
OS_CPU_WAIT_TIME 11,375,200
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 82,678,112,256
NUM_CPUS 14
NUM_CPU_CORES 7 Instance Activity Stats
Statistic Total per Second per Trans
CPU used by this session 2,760,501 127.99 113.70 CPU used when call started 2,065,134 95.75 85.06 CR blocks created 116,826 5.42 4.81
Cached Commit SCN referenced 35,858,913 1,662.63 1,476.95 Commit SCN cached 1,088 0.05 0.04
DB time 14,230,583 659.81 586.13
DBWR checkpoint buffers written 2,786,759 129.21 114.78 DBWR checkpoints 82 0.00 0.00
DBWR object drop buffers written 28 0.00 0.00 DBWR parallel query checkpoint buffers written 0 0.00 0.00 DBWR revisited being-written buffer 4 0.00 0.00 DBWR tablespace checkpoint buffers written 579 0.03 0.02 DBWR thread checkpoint buffers written 0 0.00 0.00 DBWR transaction table writes 665 0.03 0.03 DBWR undo block writes 879,979 40.80 36.24 DFO trees parallelized 2 0.00 0.00
IMU CR rollbacks 1,266 0.06 0.05
IMU Flushes 6,465 0.30 0.27
IMU Redo allocation size 11,813,948 547.76 486.59 IMU commits 17,702 0.82 0.73
IMU contention 1,089 0.05 0.04
IMU ktichg flush 1,657 0.08 0.07
IMU pool not allocated 112 0.01 0.00
IMU recursive-transaction flush 3 0.00 0.00 IMU undo allocation size 65,066,320 3,016.85 2,679.94 IMU- failed to get a private strand 112 0.01 0.00 Misses for writing mapping 0 0.00 0.00
PX local messages recv'd 368 0.02 0.02
PX local messages sent 368 0.02 0.02
Parallel operations not downgraded 2 0.00 0.00 SMON posted for undo segment shrink 0 0.00 0.00
SQL*Net roundtrips to/from client 4,813,090 223.16 198.24 SQL*Net roundtrips to/from dblink 43,646 2.02 1.80 active txn count during cleanout 211,905 9.83 8.73application wait time 13,949 0.65 0.57
background checkpoints completed 21 0.00 0.00 background checkpoints started 20 0.00 0.00 background timeouts 111,870 5.19 4.61
branch node splits 19 0.00 0.00
buffer is not pinned count 7,406,205,468 343,394.21 305,045.74 buffer is pinned count 1,342,723,816 62,256.39 55,303.92 bytes received via SQL*Net from client 606,443,091 28,118.18 24,978.09
bytes received via SQL*Net from dblink 265,807,835 12,324.38 10,948.06
bytes sent via SQL*Net to client 18,910,995,749 876,822.35 778,903.40 bytes sent via SQL*Net to dblink 25,547,081 1,184.51 1,052.23 calls to get snapshot scn: kcmgss 11,936,890 553.46 491.65 calls to kcmgas 1,248,240 57.88 51.41
calls to kcmgcs 106,173 4.92 4.37
change write time 44,255 2.05 1.82
cleanout - number of ktugct calls 547,076 25.37 22.53 cleanouts and rollbacks - consistent read gets 111,031 5.15 4.57 cleanouts only - consistent read gets 136,836 6.34 5.64 cluster key scan block gets 382,786 17.75 15.77 cluster key scans 278,819 12.93 11.48
commit batch performed 3,080 0.14 0.13
commit batch requested 3,080 0.14 0.13
commit batch/immediate performed 3,083 0.14 0.13 commit batch/immediate requested 3,083 0.14 0.13 commit cleanout failures: block lost 41,023 1.90 1.69 commit cleanout failures: buffer being written 4 0.00 0.00commit cleanout failures: callback failure 157 0.01 0.01 commit cleanout failures: cannot pin 231 0.01 0.01 commit cleanouts 749,666 34.76 30.88
commit cleanouts successfully completed 708,251 32.84 29.17 commit immediate performed 3 0.00 0.00
commit immediate requested 3 0.00 0.00
commit txn count during cleanout 351,189 16.28 14.46 concurrency wait time 6,507 0.30 0.27
consistent changes 5,328,067 247.04 219.45 consistent gets 8,341,439,937 386,757.05 343,566.04 consistent gets - examination 3,757,741,068 174,230.45 154,773.30 consistent gets direct 6,617,029 306.80 272.54 consistent gets from cache 8,334,822,915 386,450.25 343,293.50 current blocks converted for CR 887 0.04 0.04 cursor authentications 5,542 0.26 0.23
data blocks consistent reads - undo records applied 5,319,562 246.65 219.10
db block changes 79,532,990 3,687.61 3,275.79 db block gets 139,044,324 6,446.89 5,726.94 db block gets direct 1,283 0.06 0.05
db block gets from cache 139,043,041 6,446.83 5,726.89 deferred (CURRENT) block cleanout applications 297,057 13.77 12.24 dirty buffers inspected 833,757 38.66 34.34
enqueue conversions 26,766 1.24 1.10 enqueue releases 379,365 17.59 15.63 enqueue requests 381,254 17.68 15.70
enqueue timeouts 1,886 0.09 0.08
enqueue waits 1,397 0.06 0.06
execute count 11,673,423 541.25 480.80
failed probes on index block reclamation 714 0.03 0.03 free buffer inspected 11,179,231 518.33 460.45 free buffer requested 10,381,781 481.36 427.60 heap block compress 164,584 7.63 6.78
hot buffers moved to head of LRU 5,449,531 252.67 224.45 immediate (CR) block cleanout applications 247,867 11.49 10.21 immediate (CURRENT) block cleanout applications 385,730 17.88 15.89 index crx upgrade (found) 452 0.02 0.02
index crx upgrade (positioned) 62,987 2.92 2.59 index fast full scans (full) 2,384 0.11 0.10 index fast full scans (rowid ranges) 0 0.00 0.00 index fetch by key 2,698,249,086 125,106.32 111,135.10 index scans kdiixs1 141,091,704 6,541.82 5,811.27 java call heap collected bytes 807,424 37.44 33.26 java call heap collected count 12,592 0.58 0.52 java call heap gc count 48 0.00 0.00
java call heap live object count 12,880 0.60 0.53 java call heap live object count max 12,880 0.60 0.53
java call heap live size 4,392,064 203.64 180.90 java call heap live size max 4,392,064 203.64 180.90 java call heap object count 14,736 0.68 0.61java call heap object count max 22,560 1.05 0.93 java call heap total size 8,409,088 389.89 346.35 java call heap total size max 8,409,088 389.89 346.35 java call heap used size 4,607,488 213.63 189.77 java call heap used size max 4,607,488 213.63 189.77 leaf node 90-10 splits 271 0.01 0.01
leaf node splits 1,553 0.07 0.06
lob reads 2,540 0.12 0.10
lob writes 3,993 0.19 0.16
lob writes unaligned 3,993 0.19 0.16
logons cumulative 5,645 0.26 0.23
messages received 709,812 32.91 29.24
messages sent 709,812 32.91 29.24
no buffer to keep pinned count 0 0.00 0.00 no work - consistent read gets 4,583,137,118 212,500.55 188,769.60 opened cursors cumulative 306,678 14.22 12.63
parse count (failures) 719 0.03 0.03 parse count (hard) 6,958 0.32 0.29 parse count (total) 314,916 14.60 12.97
parse time cpu 51,060 2.37 2.10
parse time elapsed 64,460 2.99 2.65
physical read IO requests 5,242,302 243.06 215.92 physical read bytes 94,035,378,176 4,360,020.07 3,873,115.79 physical read total IO requests 6,234,776 289.08 256.80 physical read total bytes ############### 6,008,005.33 5,337,062.65 physical read total multi block requests 1,241,703 57.57 51.14 physical reads 11,478,928 532.23 472.79
physical reads cache 9,309,733 431.65 383.45 physical reads cache prefetch 4,341,654 201.30 178.82 physical reads direct 2,169,195 100.58 89.34 physical reads direct (lob) 289 0.01 0.01 physical reads direct temporary tablespace 8,813 0.41 0.36 physical reads prefetch warmup 0 0.00 0.00
physical write IO requests 1,774,921 82.30 73.11 physical write bytes 60,315,189,248 2,796,558.50 2,484,253.44 physical write total IO requests 1,919,227 88.99 79.05physical write total bytes ############### 4,671,166.82 4,149,515.29 physical write total multi block requests 828,012 38.39 34.10 physical writes 7,362,694 341.38 303.25
physical writes direct 3,046,704 141.26 125.49 physical writes direct (lob) 900 0.04 0.04 physical writes direct temporary tablespace 3,044,692 141.17 125.40 physical writes from cache 4,315,990 200.11 177.77 physical writes non checkpoint 5,207,133 241.43 214.47 pinned buffers inspected 764 0.04 0.03
prefetch warmup blocks aged out before use 0 0.00 0.00 prefetched blocks aged out before use 1 0.00 0.00 process last non-idle time 19,633 0.91 0.81 queries parallelized 2 0.00 0.00
recursive aborts on index block reclamation 0 0.00 0.00 recursive calls 12,928,244 599.43 532.49
recursive cpu usage 788,961 36.58 32.50 redo blocks written 25,938,241 1,202.65 1,068.34 redo buffer allocation retries 509 0.02 0.02redo entries 40,749,357 1,889.37 1,678.38 redo log space requests 162 0.01 0.01
redo log space wait time 688 0.03 0.03
redo ordering marks 872,628 40.46 35.94
redo size 12,829,048,628 594,828.36 528,401.03 redo subscn max counts 1,425,256 66.08 58.70 redo synch time 39,573 1.83 1.63
redo synch writes 57,879 2.68 2.38
redo wastage 13,394,880 621.06 551.71
redo write time 105,026 4.87 4.33
redo writer latching time 6 0.00 0.00
redo writes 38,622 1.79 1.59
rollback changes - undo records applied 177 0.01 0.01 rollbacks only - consistent read gets 7,194 0.33 0.30 rows fetched via callback 997,973,536 46,271.78 41,104.39 session connect time 0 0.00 0.00
session cursor cache hits 87,057 4.04 3.59 session logical reads 8,480,484,110 393,203.94 349,292.97 session pga memory 7,148,528,160 331,446.81 294,432.56 session pga memory max 18,255,207,648 846,416.25 751,892.90 session uga memory ############### 669,514,241.36 594,746,385.30 session uga memory max 7,658,125,248 355,074.66 315,421.77 shared hash latch upgrades - no wait 6,882,720 319.12 283.48 shared hash latch upgrades - wait 582 0.03 0.02
sorts (disk) 0 0.00 0.00 sorts (memory) 69,110 3.20 2.85 sorts (rows) 421,672,915 19,551.18 17,367.80sql area evicted 2,528 0.12 0.10
sql area purged 785 0.04 0.03
summed dirty queue length 2,721,198 126.17 112.08 switch current to new buffer 71,026 3.29 2.93 table fetch by rowid 2,954,474,371 136,986.40 121,688.47 table fetch continued row 21,150 0.98 0.87 table scan blocks gotten 44,917,305 2,082.62 1,850.05 table scan rows gotten 2,282,015,917 105,807.36 93,991.35
table scans (cache partitions) 1 0.00 0.00 table scans (direct read) 48 0.00 0.00 table scans (long tables) 73 0.00 0.00 table scans (rowid ranges) 0 0.00 0.00 table scans (short tables) 53,427 2.48 2.20total number of times SMON posted 52 0.00 0.00 transaction rollbacks 3,083 0.14 0.13
transaction tables consistent read rollbacks 13 0.00 0.00 transaction tables consistent reads - undo records applied 636 0.03 0.03
undo change vector size 6,943,980,192 321,962.79 286,007.67 user I/O wait time 1,513,678 70.18 62.35 user calls 4,836,293 224.24 199.20
user commits 24,259 1.12 1.00
user rollbacks 20 0.00 0.00
workarea executions - onepass 29 0.00 0.00 workarea executions - optimal 58,361 2.71 2.40 write clones created in background 6 0.00 0.00 write clones created in foreground 1,658 0.08 0.07
Instance Activity Stats - Absolute Values Statistic Begin Value End Value
session cursor cache count 9,385,330 9,602,685 opened cursors current 6,109 6,840 workarea memory allocated 7,708 9,606 logons current 105 91
Instance Activity Stats - Thread Activity
Statistic Total per Hour
log switches (derived) 20 3.34
Buffer Pool Statistics
P Number of Buffers Pool Hit% Buffer Gets Physical Reads Physical
Writes Free Buff Wait Writ Comp Wait Buffer Busy Waits
D 845,033 108 -122,414,827 9,309,740 4,315,984 0 0 42,331
PGA Aggr Summary
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
87.75 16,383 2,288
Buffer Wait Statistics
Class Waits Total Wait Time (s) Avg Time (ms)
data block 41,790 81 2
undo header 517 0 1
1st level bmb 3 0 3
2nd level bmb 1 0 10
segment header 6 0 0
undo block 3 0 0
Enqueue Activity
Enqueue Type (Request Reason) Requests Succ Gets Failed Gets Waits Wt
Time (s) Av Wt Time(ms)
TX-Transaction (row lock contention) 1,322 1,322 0 1,315 141 107.60 WL-Being Written Redo Log 39 22 17 2 45 22,459.00 CF-Controlfile Transaction 12,219 12,217 2 14 1 91.36 KO-Multiple Object Checkpoint (fast object checkpoint) 432 432 0 47 121.47
RO-Multiple Object Reuse (fast object reuse) 126 126 0 14 0 20.29 PR-Process Startup 424 424 0 3 0 43.00 PS-PX Process Reservation 252 251 0 2 0 0.00
Undo Segment Summary
Undo TS# Num Undo Blocks (K) Number of Transactions Max Qry Len (s)
Max Tx Concurcy Min/Max TR (mins) STO/ OOS uS/uR/uU/ eS/eR/eU
1 879.58 49,869 192,545 9
2862.38333333333333333333333333333333333/3223.06666666666666666666666666666666660/0 0/0/0/0/0/0
Latch Activity
Latch Name Get Requests Pct Get Miss Avg Slps /Miss Wait Time (s)
NoWait Requests Pct NoWait Miss
ASM db client latch 14,472 0.00 0 0
AWR Alerted Metric Element list 111,345 0.00 0 0
Consistent RBA 38,650 0.02 0.00 0 0
FAL request queue 470 0.00 0 0
FAL subheap alocation 470 0.00 0 0
FIB s.o chain latch 737 0.00 0 0
FOB s.o list latch 12,527 0.03 0.00 0 0
In memory undo latch 4,290,987 0.04 0.00 0 29,423 1.67
JOX SGA heap latch 941 0.00 0 0
JS queue state obj latch 129,240 0.00 0 0
JS slv state obj latch 1,044 0.00 0 0
KMG MMAN ready and startup request latch 7,189 0.00 0 0
KTF sga latch 1,282 0.00 0 6,066 0.00
KWQMN job cache list latch 6 0.00 0 0
MQL Tracking Latch 0 0 429 0.00 Memory Management Latch 0 0 7,189 0.00OS process 9,351 0.00 0 0
OS process allocation 13,409 0.52 0.00 0 0 OS process: request allocation 4,524 0.24 0.00 0 0 PL/SQL warning settings 19,858 0.00 0 0 SGA IO buffer pool latch 11,021 0.00 0 11,146 0.01 SQL memory manager latch 24 0.00 0 7,151 0.00 SQL memory manager workarea list latch 826,430 0.00 0 0 Shared B-Tree 766 0.00 0 0
X$KSFQP 154 0.00 0 0
active checkpoint queue latch 719,515 1.58 0.00 0 0 active service list 62,196 0.22 0.00 0 7,148 0.00 archive control 1,874 0.00 0 0
archive process latch 7,621 0.00 0 0
begin backup scn array 940 0.00 0 0
cache buffer handles 819,812 0.00 0.00 0 0 cache buffers chains 5,280,355,952 5.32 0.00 37 21,935,227 0.00 cache buffers lru chain 8,818,130 0.11 0.00 0 25,512,853 0.04 cache table scan latch 0 0 934,249 0.01 channel handle pool latch 4,635 0.47 0.00 0 0 channel operations parent latch 109,742 0.01 0.00 0 0 checkpoint queue latch 8,494,017 0.01 0.00 0 4,298,746 0.01 client/application info 33,016 0.00 0 0 commit callback allocation 6,476 0.26 0.00 0 0 compile environment latch 59,891 0.00 0 0 constraint object allocation 6,448 4.23 0.00 0 0 dictionary lookup 18 0.00 0 0
dml lock allocation 122,190 1.68 0.00 0 0 dummy allocation 11,360 0.40 0.00 0 0
enqueue hash chains 789,439 0.26 0.00 0 1,605 0.00 enqueues 585,379 0.23 0.00 0 0
error message lists 114 1.75 0.00 0 0
event group latch 2,255 0.00 0 0
file cache latch 2,148 0.00 0 0
global KZLD latch for mem in SGA 1,644 0.00 0 0 global ctx hash table latch 1 0.00 0 0 global tx hash mapping 41,173 0.01 0.00 0 0 hash table column usage latch 1,511 0.07 0.00 0 5,245,016 0.00 hash table modification latch 189 0.00 0 0 internal temp table object number allocation latc 1,221 0.00 0 0 job workq parent latch 15 100.00 0.00 0 5,952 6.03 job_queue_processes parameter latch 1,736 0.00 0 0 kks stats 33,820 0.00 0 0
krbmrosl 4,485 0.00 0 0
ksuosstats global area 1,486 0.00 0 0
ktm global data 112 0.00 0 0
kwqbsn:qsga 766 0.00 0 0
lgwr LWN SCN 39,301 0.16 0.00 0 0
library cache 2,288,082 1.90 0.00 0 21,060 0.00 library cache load lock 990 0.00 0 0
library cache lock 1,526,021 0.99 0.00 0 0 library cache lock allocation 121,627 0.01 0.00 0 0 library cache pin 604,460 0.90 0.00 0 0
library cache pin allocation 35,107 0.09 0.00 0 0 list of block allocation 87,703 0.52 0.00 0 0 loader state object freelist 4,281 0.00 0 0 longop free list parent 1,334 0.00 0 71 0.00 message pool operations parent latch 292 0.00 0 0 messages 1,694,814 1.06 0.00 0 0
mostly latch-free SCN 39,725 0.98 0.00 0 0 multiblock read objects 3,291,270 0.00 0.00 0 0 ncodef allocation latch 340 0.00 0 0
object queue header heap 654,313 0.00 0 22,441 0.00 object queue header operation 34,346,629 0.02 0.01 0 0 object stats modification 73 0.00 0 0
parallel query alloc buffer 3,420 0.18 0.00 0 0 parallel query stats 62 14.52 0.00 0 0
parameter table allocation management 12,199 1.34 0.00 0 0 post/wait queue 45,298 0.23 0.00 0 17,968 0.00 process allocation 4,526 0.00 0 2,255 0.00 process group creation 4,524 1.17 0.00 0 0 process queue 396 1.01 0.00 0 0
process queue reference 13,762 0.00 0 671 118.78 qmn task queue latch 3,064 0.20 0.00 0 0 query server freelists 286 3.50 0.00 0 0 query server process 28 0.00 0 28 0.00 redo allocation 196,729 0.43 0.00 0 40,747,233 0.05 redo copy 0 0 40,749,514 0.00
redo writing 867,413 1.06 0.00 0 0
resmgr group change latch 7,573 0.00 0 0
resmgr:actses active list 11,288 0.00 0 0 resmgr:actses change group 5,248 0.00 0 0 resmgr:free threads list 11,264 0.49 0.00 0 0 resmgr:schema config 24 0.00 0 0
row cache objects 9,463,000 0.18 0.00 0 818 0.00 rules engine aggregate statistics 2 0.00 0 0 rules engine rule set statistics 4 0.00 0 0 sequence cache 11,055 0.00 0 0
session allocation 23,149,602 0.30 0.00 0 0 session idle bit 9,769,326 0.00 0.00 0 0 session state list latch 24,658 9.81 0.00 0 0 session switching 340 0.00 0 0
session timer 7,148 0.00 0 0
shared pool 790,880 0.19 0.02 0 0
shared pool sim alloc 3 0.00 0 0
shared pool simulator 366,162 0.00 0 0 simulator hash latch 295,089,755 0.00 0.00 0 0 simulator lru latch 290,100,417 0.33 0.00 0 3,683,779 0.00 slave class 48 0.00 0 0
slave class create 192 7.81 1.07 1 0
sort extent pool 25,433 0.00 0 0
state object free list 48 0.00 0 0
statistics aggregation 2,688 0.00 0 0
temp lob duration state obj allocation 36 0.00 0 0 temporary table state object allocation 13 0.00 0 0 threshold alerts latch 1,575 0.00 0 0
transaction allocation 14,048 0.31 0.00 0 0 transaction branch allocation 13,685 0.31 0.00 0 0 undo global data 35,333,792 0.01 0.00 0 0 user lock 13,415 5.65 0.01 0 0
Latch Sleep Breakdown
Latch Name Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3
cache buffers chains 5,280,355,952 280,822,854 363,585 280,494,637 0 0
0
simulator lru latch 290,100,417 965,004 154 964,866 0 0 0
session allocation 23,149,602 68,947 6 68,941 0 0 0
library cache 2,288,082 43,386 11 43,375 0 0 0
row cache objects 9,463,000 16,632 1 16,631 0 0 0
library cache lock 1,526,021 15,074 3 15,071 0 0 0
active checkpoint queue latch 719,515 11,386 4 11,382 0 0 0
simulator hash latch 295,089,755 11,102 2 11,100 0 0 0
cache buffers lru chain 8,818,130 9,639 25 9,614 0 0 0
redo writing 867,413 9,177 1 9,176 0 0 0
object queue header operation 34,346,629 7,960 44 7,917 0 0 0
shared pool 790,880 1,520 24 1,496 0 0 0
redo allocation 196,729 855 3 852 0 0 0
user lock 13,415 758 5 753 0 0 0
slave class create 192 15 16 0 0 0 0
Dictionary Cache Stats
Cache Get Requests Pct Miss Scan Reqs Pct Miss Mod Reqs Final Usage dc_awr_control 442 0.00 0 48 1 dc_database_links 9,562 0.02 0 0 3 dc_files 80 0.00 0 0 40 dc_global_oids 571 0.88 0 0 55 dc_histogram_data 93,357 0.10 0 0 1,379 dc_histogram_defs 210,451 0.37 0 162 4,934 dc_object_grants 1,005 10.85 0 0 630 dc_object_ids 698,144 0.02 0 6 1,142 dc_objects 90,831 0.26 0 140 1,761 dc_profiles 5,065 0.00 0 0 3 dc_rollback_segments 7,045 0.00 0 0 31 dc_segments 178,048 0.04 0 134 1,008 dc_sequences 156 1.92 0 156 14 dc_table_scns 2,804 0.00 0 74 10 dc_tablespace_quotas 88 0.00 0 0 12 dc_tablespaces 904,886 0.00 0 0 17 dc_usernames 30,976 0.02 0 0 41 dc_users 993,615 0.00 0 6 139 global database name 6,451 0.00 0 0 1 outstanding_alerts 577 0.00 0 2 14 Library Cache Activity Namespace Get Requests Pct Miss Pin Requests Pct Miss Reloads Invali-dations BODY 61,206 0.01 88,056 0.01 3 0 CLUSTER 92 0.00 261 0.00 0 0 INDEX 26 0.00 249 0.00 0 0 JAVA DATA 37 0.00 0 0 0 JAVA SOURCE 57 0.00 114 1.75 1 0 SQL AREA 4,394 95.97 430,992 4.60 2,542 784 TABLE/PROCEDURE 15,299 0.73 276,194 0.17 170 0 TRIGGER 109 1.83 512 0.59 1 0 SGA Memory Summary SGA regions Begin Size (Bytes) Database Buffers 7,163,871,232 Fixed Size 2,086,552 Redo Buffers 14,688,256
Variable Size 1,409,288,552
init.ora Parameters
Parameter Name value _newsort_enabled FALSE _optim_peek_user_binds FALSE compatible 10.2.0.3 cursor_space_for_time TRUE db_block_size 8192 db_cache_advice on db_file_multiblock_read_count 8 db_files 1024 db_writer_processes 7 job_queue_processes 6 log_buffer 14242816 max_dump_file_size 16384 open_cursors 1024 optimizer_index_caching 90 optimizer_index_cost_adj 5 optimizer_mode choose pga_aggregate_target 4294967296 processes 300 recovery_parallelism 4 resource_limit TRUE session_cached_cursors 256 session_max_open_files 128 sga_target 8589934592 statistics_level typical timed_statistics TRUE undo_management AUTO undo_retention 6000 undo_tablespace UNDO
Thanks in Advance.
With Regards,
Raja.
Received on Tue Nov 18 2008 - 00:38:11 CST