Oracle Database Discussions

Hi,

I´m new with tuning and i have a little problem, i read a lot of blogs and articles about awr and stackspack. Here is part of my awr report.

Server – DELL 2850 – Windows 2003 SP2 x86 – 4GB RAM (/3GB) 2CPU.

Snap Id Snap Time Sessions Curs/Sess
——— ——————- ——– ———
Begin Snap: 11592 20-May-09 09:00:49 110 50.3
End Snap: 11761 27-May-09 10:01:00 115 45.3
Elapsed: 10,140.18 (mins)
DB Time: 19,151.07 (mins)

Cache Sizes
~~~~~~~~~~~ Begin End
———- ———-
Buffer Cache: 316M 316M Std Block Size: 8K
Shared Pool Size: 576M 576M Log Buffer: 6,924K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
————— —————
Redo size: 196,591.16 6,808.93
Logical reads: 68,060.52 2,357.27
Block changes: 1,664.88 57.66
Physical reads: 157.76 5.46
Physical writes: 34.21 1.18
User calls: 1,689.77 58.53
Parses: 675.63 23.40
Hard parses: 2.86 0.10
Sorts: 7,059.70 244.51
Logons: 2.41 0.08
Executes: 2,377.07 82.33
Transactions: 28.87

% Blocks changed per Read: 2.45 Recursive Call %: 74.45
Rollback per transaction %: 76.03 Rows per Sort: 0.73

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: 100.00
Buffer Hit %: 99.77 In-memory Sort %: 100.00
Library Hit %: 102.97 Soft Parse %: 99.58
Execute to Parse %: 71.58 Latch Hit %: 99.93
Parse CPU to Parse Elapsd %: 78.73 % Non-Parse CPU: 98.55

Shared Pool Statistics Begin End
—— ——
Memory Usage %: 47.32 47.64
% SQL with executions>1: 58.04 69.45
% Memory for SQL w/exec>1: 68.71 76.32

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
—————————— ———— ———– —— —— ———-
CPU time 873,110 76.0
log file sync 5,161,812 76,211 15 6.6 Commit
enq: UL – contention 243,039 54,470 224 4.7 Applicatio
db file sequential read 12,534,382 41,279 3 3.6 User I/O
log file parallel write 8,215,709 25,760 3 2.2 System I/O
————————————————————-

I see that CPU time is very high and the other wait events not are very significant.

My Database reports Rollback per transaction %age to 76%.

Statistic Total per Second per Trans
——————————– —————— ————– ————-
user commits 4,210,155 6.9 0.2
user rollbacks 13,356,226 22.0 0.8

Transacciones = user commits + user rollbacks
Transacciones = 4.210.155 + 13.356.226 = 17.566.381
Rollbacks por transacción = (100*13.356.226/17.566.381)= 76,03%

I review this other statistics.

Statistic Total per Second per Trans
——————————– —————— ————– ————-
db block changes 1,012,928,251 1,664.9 57.7
rollback changes – undo records 177,118,776 291.1 10.1

In the section on sql statistics i don´t see any query with delete statement or something that is producing the rollback.

For the other hand, i saw high values for latches:

Latch Activity
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
———————— ————– —— —— —— ———— ——
cache buffer handles 594,141,662 0.0 0.0 0 0 N/A
cache buffers chains ############## 0.0 0.0 24 256,952,500 0.0
cache buffers lru chain 57,432,611 0.1 0.0 0 407,926,828 0.0
cache table scan latch 0 N/A N/A 0 7,211,848 0.0
channel handle pool latc 248,878 0.0 0.0 0 0 N/A
channel operations paren 4,191,138 0.0 0.0 0 0 N/A
checkpoint queue latch 93,701,454 0.0 0.0 0 18,705,338 0.0
client/application info 74,841,233 0.0 0.0 0 0 N/A
In memory undo latch 319,086,961 0.2 0.0 3 48,465,421 0.1
row cache objects 2,204,749,411 0.1 0.0 0 630,441 0.0
lgwr LWN SCN 8,230,336 0.0 0.0 0 0 N/A
library cache 2,606,820,844 0.2 0.0 1106 8,043,804 0.4
library cache load lock 841,281 0.0 0.0 0 74 0.0
library cache lock 611,525,102 0.1 0.0 0 205 0.0
library cache lock alloc 32,549,163 0.0 0.0 0 0 N/A
library cache pin 1,349,787,907 0.1 0.0 1 4,386 0.0
library cache pin alloca 2,617,831 0.0 0.0 0 0 N/A

Latch Name
—————————————-
Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3
————– ———– ———– ———- ——– ——– ——–
cache buffers chains
############## 22,397,249 127,771 ########## 0 0 0
session allocation
-953,498,830 5,328,052 341 5,327,714 0 0 0
library cache
2,606,820,844 5,076,626 82,635 4,995,862 0 0 0
simulator lru latch
-3,426,351,079 4,816,176 244 4,815,941 0 0 0
row cache objects
2,204,749,411 2,659,953 25 2,659,928 0 0 0
library cache pin
1,349,787,907 1,566,144 2,726 1,563,447 0 0 0
library cache lock
611,525,102 801,491 75 801,416 0 0 0
In memory undo latch
319,086,961 724,884 5,850 719,534 0 0 0
enqueues
256,944,623 546,555 1,748 544,980 0 0 0
shared pool
190,887,687 515,397 78,393 439,711 0 0 0
session idle bit
2,097,791,961 512,800 2,111 510,722 0 0 0
enqueue hash chains
325,688,224 316,885 2,067 314,956 0 0 0
undo global data
395,144,808 249,509 395 249,136 0 0 0
dml lock allocation
82,685,629 38,906 213 38,738 0 0 0

Latch Miss Sources
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
———————— ————————– ——- ———- ——–
cache buffers chains kcbchg: kslbegin: bufs not 0 124,497 72,893
cache buffers chains kcbgtcr: fast path 0 84,073 73,823
cache buffers chains kcbgtcr: kslbegin excl 0 69,785 47,848
cache buffers chains kcbzwb 0 47,844 28,553
cache buffers chains kcbgcur: kslbegin 0 46,897 10,928
cache buffers chains kcbrls: kslbegin 0 11,584 58,227
cache buffers chains kcbchg: kslbegin: call CR 0 5,786 50,971
cache buffers chains kcbget: pin buffer 0 3,802 691
cache buffers chains kcb_is_private 0 2,362 42,467
cache buffers chains kcbbxsv 0 1,421 79
cache buffers chains kcbnlc 0 1,197 5,431
library cache kglpnp: child 0 25,131 56,619
library cache kglpndl: child: after proc 0 8,800 216
library cache kglhdgn: child: 0 7,515 16,401
library cache kglLockCursor 0 3,962 1,328
library cache kglpndl: child: before pro 0 990 5,918
library cache kglic 0 750 28
shared pool kghasp 0 54,200 40
shared pool kghupr1 0 13,535 56,783
shared pool kghalo 0 6,161 12,243
shared pool kghfre 0 3,081 8,630
shared pool kgh_next_free 0 887 9

I´m a bit lost… i need to look for hot blocks, sql statments that are consuming a lot of cpu, set pct_free of hot_blocks to a high value¿?

Edited by: Roberto Marotta on Jun 22, 2009 3:45 AM