Dear all,
We are running 11r2 on windows server 2008 R2.
Our users connect through an application and they are facing slowness, data when loading into the appcliation is low and the connection time is also high.
below is awr report,
Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
Microsoft Windows x86 64-bit 8 8 2 47.99
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 3044 09-Apr-12 08:00:33 68 1.7
End Snap: 3059 09-Apr-12 23:00:10 61 2.1
Elapsed: 899.61 (mins)
DB Time: 687.41 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 17,536M 17,536M Std Block Size: 8K
Shared Pool Size: 2,496M 2,496M Log Buffer: 100,840K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 0.8 0.2 0.00 0.00
DB CPU(s): 0.7 0.2 0.00 0.00
Redo size: 13,364.5 3,495.8
Logical reads: 167,184.0 43,730.8
Block changes: 79.8 20.9
Physical reads: 44.0 11.5
Physical writes: 10.0 2.6
User calls: 394.8 103.3
Parses: 199.8 52.3
Hard parses: 1.1 0.3
W/A MB processed: 11.4 3.0
Logons: 3.8 1.0
Executes: 1,494.3 390.9
Rollbacks: 0.0 0.0
Transactions: 3.8
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 99.77 Soft Parse %: 99.43
Execute to Parse %: 86.63 Latch Hit %: 99.91
Parse CPU to Parse Elapsd %: 98.04 % Non-Parse CPU: 99.55
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 85.61 85.76
% SQL with executions>1: 96.11 89.76
% Memory for SQL w/exec>1: 91.78 93.46
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 38,854 94.2
log file sync 308,227 1,607 5 3.9 Commit
control file sequential read 288,900 639 2 1.5 System I/O
Disk file operations I/O 95,676 281 3 .7 User I/O
enq: CF - contention 61 204 3341 .5 Other
Host CPU (CPUs: 8 Cores: 8 Sockets: 2)
~~~~~~~~ Load Average
Begin End %User %System %WIO %Idle
--------- --------- --------- --------- --------- ---------
9.0 1.2 89.8
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 9.0
% of busy CPU for Instance: 88.1
%DB time waiting for CPU - Resource Mgr: 0.0
Memory Statistics
~~~~~~~~~~~~~~~~~ Begin End
Host Mem (MB): 49,140.1 49,140.1
SGA use (MB): 20,480.0 20,480.0
PGA use (MB): 430.8 420.6
% Host Mem used for SGA+PGA: 42.55 42.53
Time Model Statistics
-> Total time in database user-calls (DB Time): 41244.6s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
DB CPU 38,854.3 94.2
sql execute elapsed time 38,166.1 92.5
PL/SQL execution elapsed time 1,213.5 2.9
connection management call elapsed time 502.5 1.2
parse time elapsed 294.6 .7
hard parse elapsed time 100.6 .2
hard parse (sharing criteria) elapsed time 49.3 .1
failed parse elapsed time 35.7 .1
PL/SQL compilation elapsed time 12.4 .0
hard parse (bind mismatch) elapsed time 3.3 .0
repeated bind elapsed time 1.4 .0
sequence load elapsed time 0.5 .0
RMAN cpu time (backup/restore) 0.2 .0
Java execution elapsed time 0.0 .0
DB time 41,244.6
background elapsed time 3,860.4
background cpu time 80.4
-------------------------------------------------------------
-> *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 551,012
AVG_IDLE_TIME 4,843,605
AVG_SYS_TIME 65,573
AVG_USER_TIME 483,911
BUSY_TIME 4,420,709
IDLE_TIME 38,761,431
SYS_TIME 537,172
USER_TIME 3,884,000
RSRC_MGR_CPU_WAIT_TIME 63
VM_IN_BYTES -1.398261775013032E+18
VM_OUT_BYTES -9,746,071,198,322,688
PHYSICAL_MEMORY_BYTES 51,527,151,616
NUM_CPUS 8
NUM_CPU_CORES 8
NUM_CPU_SOCKETS 2
-------------------------------------------------------------
Avg
%Time Total Wait wait
Wait Class Waits -outs Time (s) (ms) %DB time
-------------------- ---------------- ----- ---------------- -------- ---------
DB CPU 38,854 94.2
Commit 308,227 0 1,607 5 3.9
System I/O 288,952 0 639 2 1.5
User I/O 130,325 0 455 3 1.1
Other 839,422 44 239 0 0.6
Network 12,680,373 0 36 0 0.1
Concurrency 2,990 0 16 5 0.0
Application 13,463 0 3 0 0.0
Configuration 252 0 1 3 0.0
Scheduler 33 0 1 20 0.0
-------------------------------------------------------------
Avg
%Time Total Wait wait Waits % DB
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
log file sync 308,227 0 1,607 5 1.5 3.9
control file sequential re 288,900 0 639 2 1.4 1.5
Disk file operations I/O 95,676 0 281 3 0.5 .7
enq: CF - contention 61 0 204 3341 0.0 .5
direct path read 13,300 0 57 4 0.1 .1
direct path write 666 0 45 68 0.0 .1
db file sequential read 6,730 0 27 4 0.0 .1
SQL*Net more data to clien 788,516 0 25 0 3.8 .1
direct path write temp 6,883 0 21 3 0.0 .1
PX Deq: Slave Session Stat 238,362 0 13 0 1.2 .0
os thread startup 801 0 11 14 0.0 .0
SQL*Net message to client 11,889,651 0 11 0 57.6 .0
PX Deq: Signal ACK RSG 122,472 46 10 0 0.6 .0
direct path read temp 4,648 0 9 2 0.0 .0
utl_file I/O 1,442 0 9 6 0.0 .0
PX Deq: Signal ACK EXT 110,517 0 5 0 0.5 .0
db file scattered read 972 0 5 5 0.0 .0
enq: PS - contention 47,320 0 4 0 0.2 .0
cursor: pin S wait on X 101 0 3 29 0.0 .0
SQL*Net break/reset to cli 13,270 0 1 0 0.1 .0
PX qref latch 11,156 100 1 0 0.1 .0
buffer busy waits 835 0 1 1 0.0 .0
enq: CR - block range reus 111 0 1 7 0.0 .0
latch: cache buffers chain 440 0 1 2 0.0 .0
TCP Socket (KGAS) 1,521 45 1 0 0.0 .0
resmgr:cpu quantum 30 0 1 22 0.0 .0
enq: TM - contention 4 0 1 154 0.0 .0
kksfbc child completion 9 100 0 55 0.0 .0
asynch descriptor resize 301,508 100 0 0 1.5 .0
log file switch completion 3 0 0 148 0.0 .0
control file parallel writ 52 0 0 8 0.0 .0
enq: KO - fast object chec 89 0 0 4 0.0 .0
latch free 2,913 0 0 0 0.0 .0
library cache lock 44 0 0 8 0.0 .0
enq: RO - fast object reus 10 0 0 32 0.0 .0
enq: TX - allocate ITL ent 247 0 0 1 0.0 .0
read by other session 8 0 0 20 0.0 .0
latch: call allocation 4,512 0 0 0 0.0 .0
latch: row cache objects 17 0 0 3 0.0 .0
PX Deq: Table Q Get Keys 23 0 0 2 0.0 .0
latch: shared pool 590 0 0 0 0.0 .0
log file switch (private s 2 0 0 18 0.0 .0
reliable message 225 0 0 0 0.0 .0
enq: TX - row lock content 90 0 0 0 0.0 .0
library cache: mutex X 134 0 0 0 0.0 .0
enq: PR - contention 1 0 0 10 0.0 .0
SQL*Net more data from cli 685 0 0 0 0.0 .0
ADR block file read 12 0 0 1 0.0 .0
wait list latch free 1 0 0 6 0.0 .0
rdbms ipc reply 97 0 0 0 0.0 .0
buffer deadlock 66 100 0 0 0.0 .0
latch: parallel query allo 8 0 0 0 0.0 .0
SQL*Net message from clien 11,889,667 0 2,066,321 174 57.6
jobq slave wait 118,798 100 61,089 514 0.6
PX Deq Credit: send blkd 437,092 2 20,537 47 2.1
PX Deq: Execution Msg 1,989,712 0 6,509 3 9.6
PX Deq: Table Q Normal 582,826 0 3,161 5 2.8
PX Deq: Execute Reply 1,504,044 0 2,931 2 7.3
PX Deq: Parse Reply 124,122 0 74 1 0.6
PX Deq Credit: need buffer 576,701 0 37 0 2.8
tatistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
Batched IO (bound) vector count 0 0.0 0.0
Batched IO (full) vector count 0 0.0 0.0
Batched IO block miss count 0 0.0 0.0
Batched IO buffer defrag count 0 0.0 0.0
Batched IO double miss count 0 0.0 0.0
Batched IO same unit count 0 0.0 0.0
Batched IO single block count 0 0.0 0.0
Batched IO slow jump count 0 0.0 0.0
Batched IO vector block count 0 0.0 0.0
Batched IO vector read count 0 0.0 0.0
Block Cleanout Optim referenced 1,650 0.0 0.0
CCursor + sql area evicted 314 0.0 0.0
CPU used by this session 3,709,051 68.7 18.0
CPU used when call started 3,687,172 68.3 17.9
CR blocks created 4,290 0.1 0.0
Cached Commit SCN referenced 14,076 0.3 0.1
Commit SCN cached 343 0.0 0.0
DB time 13,308,314 246.6 64.5
DBWR checkpoint buffers written 213,300 4.0 1.0
DBWR checkpoints 146 0.0 0.0
DBWR object drop buffers written 799 0.0 0.0
DBWR parallel query checkpoint b 0 0.0 0.0
DBWR revisited being-written buf 0 0.0 0.0
DBWR tablespace checkpoint buffe 748 0.0 0.0
DBWR transaction table writes 17,603 0.3 0.1
DBWR undo block writes 44,740 0.8 0.2
DDL statements parallelized 2 0.0 0.0
DFO trees parallelized 42,648 0.8 0.2
DML statements parallelized 8 0.0 0.0
HSC Heap Segment Block Changes 806,662 14.9 3.9
HSC IDL Compressed Blocks 2,224 0.0 0.0
Heap Segment Array Inserts 2,676 0.1 0.0
Heap Segment Array Updates 6,403 0.1 0.0
IMU CR rollbacks 3,237 0.1 0.0
IMU Flushes 15,569 0.3 0.1
IMU Redo allocation size 46,089,292 853.9 223.4
IMU commits 197,787 3.7 1.0
IMU contention 145 0.0 0.0
IMU ktichg flush 12 0.0 0.0
IMU pool not allocated 48 0.0 0.0
IMU recursive-transaction flush 88 0.0 0.0
IMU undo allocation size 388,995,952 7,206.7 1,885.1
IMU- failed to get a private str 48 0.0 0.0
LOB table id lookup cache misses 1 0.0 0.0
Misses for writing mapping 0 0.0 0.0
Number of read IOs issued 21,788 0.4 0.1
PX local messages recv'd 5,064,947 93.8 24.5
PX local messages sent 5,069,762 93.9 24.6
Parallel operations downgraded t 1,015 0.0 0.0
Parallel operations not downgrad 42,648 0.8 0.2
RowCR - row contention 66 0.0 0.0
RowCR attempts 141,529 2.6 0.7
RowCR hits 141,413 2.6 0.7
SMON posted for undo segment rec 0 0.0 0.0
SMON posted for undo segment shr 2 0.0 0.0
SQL*Net roundtrips to/from clien 11,890,661 220.3 57.6
SQL*Net roundtrips to/from dblin 0 0.0 0.0
TBS Extension: bytes extended 52,428,800 971.3 254.1
TBS Extension: files extended 1 0.0 0.0
TBS Extension: tasks created 1 0.0 0.0
TBS Extension: tasks executed 1 0.0 0.0
active txn count during cleanout 8,121 0.2 0.0
application wait time 277 0.0 0.0
background checkpoints completed 5 0.0 0.0
background checkpoints started 5 0.0 0.0
background timeouts 352,081 6.5 1.7
branch node splits 8 0.0 0.0
buffer is not pinned count 5,172,969,117 95,836.8 25,068.3
buffer is pinned count 20,999,743,611 389,050.9 101,765.1
bytes received via SQL*Net from 3,846,444,292 71,261.0 18,639.9
bytes received via SQL*Net from 0 0.0 0.0
bytes sent via SQL*Net to client 20,087,243,062 372,145.5 97,343.1
bytes sent via SQL*Net to dblink 0 0.0 0.0
calls to get snapshot scn: kcmgs 76,833,607 1,423.5 372.3
calls to kcmgas 420,834 7.8 2.0
calls to kcmgcs 12,699,355 235.3 61.5
cell physical IO interconnect by 33,746,255,872 625,198.6 163,535.0
change write time 2,452 0.1 0.0
cleanout - number of ktugct call 7,826 0.1 0.0
cleanouts and rollbacks - consis 672 0.0 0.0
cleanouts only - consistent read 1,313 0.0 0.0
cluster key scan block gets 7,092,091 131.4 34.4
cluster key scans 6,802,560 126.0 33.0
commit batch performed 0 0.0 0.0
commit batch requested 0 0.0 0.0
commit batch/immediate performed 144 0.0 0.0
commit batch/immediate requested 144 0.0 0.0
commit cleanout failures: block 144 0.0 0.0
commit cleanout failures: buffer 2 0.0 0.0
commit cleanout failures: callba 796 0.0 0.0
commit cleanout failures: cannot 167 0.0 0.0
commit cleanouts 756,512 14.0 3.7
commit cleanouts successfully co 755,403 14.0 3.7
commit immediate performed 144 0.0 0.0
commit immediate requested 144 0.0 0.0
commit txn count during cleanout 18,408 0.3 0.1
concurrency wait time 4,465 0.1 0.0
consistent changes 615,091 11.4 3.0
consistent gets 9,017,393,786 167,060.4 43,698.5
consistent gets - examination 4,171,011,877 77,274.1 20,212.8
consistent gets direct 2,040,389 37.8 9.9
consistent gets from cache 9,015,353,397 167,022.6 43,688.6
consistent gets from cache (fast 4,186,301,902 77,557.4 20,286.9
cursor authentications 3,114 0.1 0.0
data blocks consistent reads - u 27,917 0.5 0.1
db block changes 4,307,652 79.8 20.9
db block gets 6,671,464 123.6 32.3
db block gets direct 19,375 0.4 0.1
db block gets from cache 6,652,089 123.2 32.2
db block gets from cache (fastpa 1,120,388 20.8 5.4
deferred (CURRENT) block cleanou 309,841 5.7 1.5
enqueue conversions 313,426 5.8 1.5
enqueue deadlocks 0 0.0 0.0
enqueue releases 3,076,491 57.0 14.9
enqueue requests 3,076,681 57.0 14.9
enqueue timeouts 158 0.0 0.0
enqueue waits 48,027 0.9 0.2
exchange deadlocks 66 0.0 0.0
execute count 80,659,780 1,494.3 390.9
failed probes on index block rec 8 0.0 0.0
Number of Pool Buffer Physical Physical Buff Comp Busy
P Buffers Hit% Gets Reads Writes Wait Wait Waits
--- ---------- ---- ------------ ------------ ----------- ------ ------ --------
D 2,161,586 100 430,496,369 28,050 214,528 0 0 1,015
-------------------------------------------------------------
Edited by: user10243788 on Apr 10, 2012 1:18 AM
Edited by: user10243788 on Apr 10, 2012 1:22 AM