| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Re: High Redo Copy Contention
SVRMGR>
SVRMGR> set charwidth 12
Charwidth 12 SVRMGR> set numwidth 10 Numwidth 10
SVRMGR> select namespace library,
2> gets,
3>
round(decode(gethits,0,1,gethits)/decode(gets,0,1,gets),3)
4> gethitratio,
5> pins,
6>
round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3)
7> pinhitratio,
8> reloads, invalidations
9> from stats$lib;
LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADS
INVALIDATI
BODY 1372 1 1372 1 0
0
CLUSTER 0 1 0 1 0
0
INDEX 0 1 0 1 0
0
OBJECT 0 1 0 1 0
0
PIPE 10895 1 10914 1 0
0
SQL AREA 436 .94 11142 .994 11
0
TABLE/PROCED 257 .988 5034 .998 7
0
TRIGGER 0 1 0 1 0
0
Charwidth 27 SVRMGR> set numwidth 12; Numwidth 12
------------------- ------------------ - ------------ Users connected at 06-apr-01 13:45:06 : 181 row selected.
------------------- ------------------ - ------------ Users connected at 06-apr-01 14:40:25 : 221 row selected.
---------------------- ------------ avg # of connections: 20
SVRMGR>
SVRMGR> select n1.name "Statistic",
2> n1.change "Total",
3> round(n1.change/trans.change,2) "Per Transaction",
4> round(n1.change/((start users + end users)/2),2) "Per
Logon",
5> round(n1.change/(to number(to char(end time,
'J'))*60*60*24 -
6> to number(to char(start time,
'J'))*60*60*24 +
7> to number(to char(end time, 'SSSSS')) -
8> to number(to char(start time, 'SSSSS')))
9> , 2) "Per Second"
10> from
11> stats$stats n1,
12> stats$stats trans,
13> stats$dates
14> where
15> trans.name='user commits'
16> and n1.change != 0
17> order by n1.name;
Statistic Total Per Transact Per Logon Per
Second
--------------------------- ------------ ------------ ------------
------------
CR blocks created 9 .01 .45
0
DBWR buffers scanned 61575 86.24 3078.75
18.55
DBWR checkpoint buffers wri 504 .71 25.2
.15
DBWR checkpoint write reque 24 .03 1.2
.01
DBWR checkpoints 1 0 .05
0
DBWR lru scans 25 .04 1.25
.01
DBWR summed scan depth 61575 86.24 3078.75
18.55
DBWR transaction table writ 8 .01 .4
0
DBWR undo block writes 169 .24 8.45
.05
SQL*Net roundtrips to/from 1603 2.25 80.15
.48
background checkpoints comp 1 0 .05
0
background checkpoints star 1 0 .05
0
background timeouts 3320 4.65 166
1
buffer is not pinned count 3539877 4957.81 176993.85
1066.55
buffer is pinned count 17507 24.52 875.35
5.27
bytes received via SQL*Net 5161988 7229.68 258099.4
1555.28
bytes sent via SQL*Net to c 5905565 8271.1 295278.25
1779.32
calls to get snapshot scn: 9308 13.04 465.4
2.8
calls to kcmgas 719 1.01 35.95
.22
calls to kcmgcs 13 .02 .65
0
cleanouts and rollbacks - c 6 .01 .3
0
cleanouts only - consistent 6 .01 .3
0
cluster key scan block gets 1187 1.66 59.35
.36
cluster key scans 88 .12 4.4
.03
commit cleanout failures: b 2 0 .1
0
commit cleanouts 4265 5.97 213.25
1.29
commit cleanouts successful 4263 5.97 213.15
1.28
consistent changes 9 .01 .45
0
consistent gets 3548686 4970.15 177434.3
1069.2
cursor authentications 11 .02 .55
0
data blocks consistent read 9 .01 .45
0
db block changes 17343 24.29 867.15
5.23
db block gets 31392 43.97 1569.6
9.46
deferred (CURRENT) block cl 667 .93 33.35
.2
enqueue releases 3436 4.81 171.8
1.04
enqueue requests 3432 4.81 171.6
1.03
enqueue timeouts 2 0 .1
0
execute count 8403 11.77 420.15
2.53
free buffer inspected 47575 66.63 2378.75
14.33
free buffer requested 2641502 3699.58 132075.1
795.87
immediate (CR) block cleano 12 .02 .6
0
immediate (CURRENT) block c 2954 4.14 147.7
.89
logons cumulative 11 .02 .55
0
logons current 4 .01 .2
0
messages received 377 .53 18.85
.11
messages sent 377 .53 18.85
.11
no work - consistent read g 3544476 4964.25 177223.8
1067.93
opened cursors cumulative 1357 1.9 67.85
.41
opened cursors current 41 .06 2.05
.01
parse count (hard) 39 .05 1.95
.01
parse count (total) 1367 1.91 68.35
.41
physical reads 2641461 3699.53 132073.05
795.86
physical writes 505 .71 25.25
.15
recursive calls 23536 32.96 1176.8
7.09
redo blocks written 4483 6.28 224.15
1.35
redo entries 10133 14.19 506.65
3.05
redo size 2049448 2870.38 102472.4
617.49
redo small copies 5030 7.04 251.5
1.52
redo synch writes 20 .03 1
.01
redo wastage 171224 239.81 8561.2
51.59
redo writes 532 .75 26.6
.16
rollbacks only - consistent 3 0 .15
0
session logical reads 3580071 5014.11 179003.55
1078.66
session pga memory 6706164 9392.39 335308.2
2020.54
session pga memory max 7133356 9990.69 356667.8
2149.25
session uga memory 517472 724.75 25873.6
155.91
session uga memory max 2619148 3668.27 130957.4
789.14
sorts (memory) 3197 4.48 159.85
.96
sorts (rows) 295010 413.18 14750.5
88.89
table fetch by rowid 20905 29.28 1045.25
6.3
table fetch continued row 895 1.25 44.75
.27
table scan blocks gotten 3516295 4924.78 175814.75
1059.44
table scan rows gotten 210765204 295189.36 10538260.2
63502.62
table scans (long tables) 383 .54 19.15
.12
table scans (short tables) 5025 7.04 251.25
1.51
total file opens 19 .03 .95
.01
user calls 1666 2.33 83.3
.5
user commits 714 1 35.7
.22
user rollbacks 140 .2 7
.04
write requests 25 .04 1.25
.01
80 rows selected.
SVRMGR>
SVRMGR> set charwidth 32;
Charwidth 32 SVRMGR> set numwidth 13; Numwidth 13
SVRMGR> Rem SMON, etc). Times are in hundreths of seconds. Each one
of
SVRMGR> Rem these is a context switch which costs CPU time. By looking
at
SVRMGR> Rem the Total Time you can often determine what is the
bottleneck
SVRMGR> Rem that processes are waiting for. This shows the total time
spent
SVRMGR> Rem waiting for a specific event and the average time per wait
on
SVRMGR> Rem that event.
SVRMGR> select n1.event "Event Name",
2> n1.event count "Count",
3> n1.time waited "Total Time",
4> round(n1.time waited/n1.event count, 2) "Avg Time"
5> from stats$event n1
6> where n1.event count > 0
7> order by n1.time waited desc;
Event Name Count Total Time Avg Time
-------------------------------- ------------- -------------
-------------
SQL*Net break/reset to client 42 0
0
SQL*Net message from client 1648 0
0
SQL*Net message to client 1652 0
0
SQL*Net more data from client 2434 0
0
SQL*Net more data to client 2670 0
0
buffer busy waits 54410 0
0
control file sequential read 20 0
0
db file scattered read 184546 0
0
db file sequential read 38920 0
0
enqueue 219 0
0
file identify 6 0
0
file open 30 0
0
latch free 766 0
0
library cache pin 143 0
0
log file sync 8 0
0
pipe get 3604 0
0
refresh controlfile command 6 0
0
undo segment extension 121 0
0
write complete waits 3 0
0
SVRMGR>
SVRMGR>
SVRMGR> Rem System wide wait events for background processes (PMON,
SMON, etc)
SVRMGR> select n1.event "Event Name",
2> n1.event count "Count",
3> n1.time waited "Total Time",
4> round(n1.time waited/n1.event count, 2) "Avg Time"
5> from stats$bck event n1
6> where n1.event count > 0
7> order by n1.time waited desc;
Event Name Count Total Time Avg Time
-------------------------------- ------------- -------------
-------------
buffer busy waits 4 0
0
control file parallel write 1108 0
0
control file sequential read 18 0
0
db file parallel write 25 0
0
latch free 6 0
0
log file parallel write 532 0
0
pmon timer 1106 0
0
rdbms ipc message 3654 0
0
rdbms ipc reply 2 0
0
smon timer 12 0
0
SVRMGR> SVRMGR> SVRMGR> set charwidth 18; Charwidth 18 SVRMGR> set numwidth 11; Numwidth 11
SVRMGR> Rem the 'latch free' event in the wait events above.
SVRMGR> Rem Sleeps should be low. The hit ratio should be high.
SVRMGR> select name latch name, gets, misses,
2> round((gets-misses)/decode(gets,0,1,gets),3)
3> hit ratio,
4> sleeps,
5> round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS"
6> from stats$latches
7> where gets != 0
8> order by name;
LATCH NAME GETS MISSES HIT RATIO SLEEPS
SLEEPS/MISS
------------------ ----------- ----------- ----------- -----------
-----------
Active checkpoint 1157 0 1 0
0
Checkpoint queue l 2648313 12 1 1
.083
Token Manager 23 0 1 0
0
cache buffer handl 90 0 1 0
0
cache buffers chai 9899244 59879 .994 461
.008
cache buffers lru 2641553 82546 .969 283
.003
dml lock allocatio 3659 0 1 0
0
enqueue hash chain 7077 4 .999 0
0
enqueues 8220 6 .999 0
0
ktm global data 16 0 1 0
0
latch wait list 16 0 1 0
0
library cache 89531 943 .989 16
.017
library cache load 20 0 1 0
0
list of block allo 1461 0 1 0
0
messages 8152 3 1 1
.333
modify parameter v 64 0 1 0
0
multiblock read ob 444277 17 1 2
.118
ncodef allocation 53 0 1 0
0
process allocation 11 0 1 0
0
redo allocation 12270 22 .998 3
.136
redo copy 4 4 0 1
.25
redo writing 3547 3 .999 3
1
row cache objects 8591 0 1 0
0
sequence cache 53 0 1 0
0
session allocation 910 0 1 0
0
session idle bit 3757 0 1 0
0
session switching 53 0 1 0
0
shared pool 3391 0 1 0
0
sort extent pool 12 0 1 0
0
transaction alloca 3664 0 1 0
0
transaction branch 53 0 1 0
0
undo global data 2515 0 1 0
0
user lock 40 0 1 0
0
Numwidth 16
SVRMGR> select name latch name,
2> immed gets nowait gets,
3> immed miss nowait misses,
4> round((immed gets/(immed gets+immed miss)), 3)
5> nowait hit ratio
6> from stats$latches
7> where immed gets + immed miss != 0
8> order by name;
LATCH NAME NOWAIT GETS NOWAIT MISSES NOWAIT HIT RATIO
------------------ ---------------- ---------------- ----------------
Token Manager 4 0 1
cache buffers chai 5180146 2065 1
cache buffers lru 3450087 51912 .985
latch wait list 16 0 1
library cache 11 0 1
process allocation 11 0 1
redo copy 7224 13 .998
row cache objects 4 0 1
vecio buf des 4 0 1
9 rows selected.
SVRMGR> Rem contention.
SVRMGR> select * from stats$waitstat
2> where count != 0
3> order by count desc;
CLASS COUNT TIME
------------------ ---------------- ----------------
data block 54420 0
undo header 5 0
2 rows selected.
SVRMGR> SVRMGR> SVRMGR> set numwidth 19; Numwidth 19
UNDO SEGMENT TRANS TBL GETS TRANS TBL WAITS
UNDO BYTES WRITTEN SEGMENT SIZE BYTES XACTS SHRINKS
WRAPS
------------------- ------------------- -------------------
------------------- ------------------- -------------------
------------------- -------------------
0 13 0
0 401408 0 0
0
2 619 4
257552 565248 0 1
1
3 223 0
67650 851968 0 0
1
4 232 0
60834 237568 0 1
2
5 224 0
65356 851968 1 0
0
6 224 0
77802 565248 0 0
1
7 226 0
86418 565248 -1 0
1
8 227 0
84866 565248 0 0
0
Charwidth 39
2> order by name;
NAME VALUE
---------------------------------------
---------------------------------------
background dump dest C:\ORANT\rdbms80\trace
compatible 8.0.5.0.0
control files C:\ORANT\database\ctl1sid9.ora,
C:\ORAN
db block buffers 10000
db block size 8192
db file multiblock read count 16
db files 1020
db name sid9
dml locks 200
log buffer 153600
log checkpoint interval 8000
log checkpoint timeout 0
log simultaneous copies 4
log small entry max size 200
max dump file size 10240
open cursors 250
open links 8
processes 250
remote login passwordfile SHARED
sequence cache entries 30
sequence cache hash buckets 23
shared pool size 20000000
sort area size 262144
text enable TRUE
user dump dest C:\ORANT\rdbms80\trace
utl file dir *
26 rows selected.
SVRMGR>
SVRMGR> set charwidth 15;
Charwidth 15 SVRMGR> set numwidth 8; Numwidth 8
2> where get reqs != 0 or scan reqs != 0 or mod reqs != 0;
NAME GET REQS GET MISS SCAN REQ SCAN MIS MOD REQS COUNT
CUR USAG
295
dc segments 112 0 0 0 2 392
376
dc rollback seg 266 0 0 0 0 28
12
dc used extents 102 0 0 0 4 451
446
dc users 132 0 0 0 0 31
23
dc user grants 80 0 0 0 0 51
20
dc objects 46 0 0 0 0 427
424
dc synonyms 1 0 0 0 0 24
23
dc usernames 20 0 0 0 0 21
20
dc object ids 1265 0 0 0 0 404
401
dc sequences 1 0 0 0 1 20
9
dc profiles 8 0 0 0 0 2
1
dc histogram de 15 0 0 0 0 148
142
13 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set charwidth 80;
Charwidth 80
SVRMGR> set numwidth 10;
Numwidth 10
SVRMGR> Rem Sum IO operations over tablespaces.
SVRMGR> select
2> table space||'
'
3> table space,
4> sum(phys reads) reads, sum(phys blks rd) blks read,
5> sum(phys rd time) read time, sum(phys writes) writes,
6> sum(phys blks wr) blks wrt, sum(phys wrt tim) write time,
7> sum(megabytes size) megabytes
8> from stats$files
9> group by table space
10> order by table space;
TABLE SPACE
READS BLKS READ READ TIME WRITES BLKS WRT
WRITE TIME MEGABYTES
--Received on Fri Apr 06 2001 - 16:38:25 CDT
 |
 |