| 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 1204 1 1204 .993 8
0
CLUSTER 54 1 31 1 0
0
INDEX 0 1 0 1 0
0
OBJECT 0 1 0 1 0
0
PIPE 5958 1 5980 1 1
0
SQL AREA 1064 .972 10406 .969 219
8
TABLE/PROCED 2486 .998 5284 .961 156
0
TRIGGER 0 1 0 1 0
0
Charwidth 27 SVRMGR> set numwidth 12; Numwidth 12
------------------- ------------------ - ------------ Users connected at 09-apr-01 10:39:14 : 231 row selected.
------------------- ------------------ - ------------ Users connected at 09-apr-01 10:55:36 : 201 row selected.
---------------------- ------------ avg # of connections: 21.5
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
--------------------------- ------------ ------------ ------------ ------------ CPU used by this session 3973 12.61 184.79 4.05 CPU used when call started 4000 12.7 186.05 4.07 CR blocks created 13 .04 .620.27
.01
DBWR buffers scanned 2479 7.87 115.3 2.52 DBWR free buffers found 2479 7.87 115.3 2.52 DBWR lru scans 1 0 .05 0 DBWR make free requests 1 0 .05 0 DBWR summed scan depth 2479 7.87 115.3 2.52 SQL*Net roundtrips to/from 1246 3.96 57.95 1.27 SQL*Net roundtrips to/from 21 .07 .98
.02
background timeouts 981 3.11 45.63 1 buffer is not pinned count 111860 355.11 5202.79 113.91 buffer is pinned count 21547 68.4 1002.19 21.94 bytes received via SQL*Net 3809971 12095.15 177207.95 3879.81 bytes received via SQL*Net 2532 8.04 117.77 2.58 bytes sent via SQL*Net to c 5617132 17832.17 261261.95 5720.09 bytes sent via SQL*Net to d 1700 5.4 79.07 1.73 calls to get snapshot scn: 6184 19.63 287.63 6.3 calls to kcmgas 324 1.03 15.07
.33
calls to kcmgcs 71 .23 3.3
.07
change write time 16 .05 .74
.02
cleanouts and rollbacks - c 2 .01 .09 0 cleanouts only - consistent 2 .01 .09 0 cluster key scan block gets 5237 16.63 243.58 5.33 cluster key scans 3710 11.78 172.56 3.78 commit cleanout failures: b 1 0 .05 0 commit cleanouts 2135 6.78 99.3 2.17 commit cleanouts successful 2134 6.77 99.26 2.17 consistent changes 13 .04 .6
.01
consistent gets 119595 379.67 5562.56 121.79 cursor authentications 187 .59 8.7
.19
data blocks consistent read 13 .04 .6
.01
db block changes 12980 41.21 603.72 13.22 db block gets 20143 63.95 936.88 20.51 deferred (CURRENT) block cl 278 .88 12.93
.28
enqueue conversions 42 .13 1.95
.04
enqueue releases 1946 6.18 90.51 1.98 enqueue requests 1940 6.16 90.23 1.98 execute count 6026 19.13 280.28 6.14 free buffer inspected 140 .44 6.51
.14
free buffer requested 20046 63.64 932.37 20.41 immediate (CR) block cleano 4 .01 .19 0 immediate (CURRENT) block c 1060 3.37 49.3 1.08 logons cumulative 6 .02 .28
.01
logons current -3 -.01 -.14 0 messages received 345 1.1 16.05
.35
messages sent 345 1.1 16.05
.35
no work - consistent read g 114210 362.57 5312.09 116.3 opened cursors cumulative 1517 4.82 70.56 1.54 opened cursors current -35 -.11 -1.63 -.04 parse count (hard) 256 .81 11.91
.26
parse count (total) 1806 5.73 84 1.84 parse time cpu 183 .58 8.51
.19
parse time elapsed 244 .77 11.35
.25
physical reads 19902 63.18 925.67
recursive calls 31344 99.5 1457.86
31.92
recursive cpu usage 2519 8 117.16
2.57
redo blocks written 3869 12.28 179.95
3.94
redo entries 6990 22.19 325.12
7.12
redo ordering marks 1 0 .05
0
redo size 1784172 5664.04 82984.74
1816.88
redo synch time 7 .02 .33
.01
redo synch writes 37 .12 1.72
.04
redo wastage 131636 417.89 6122.6
134.05
redo write time 76 .24 3.53
.08
redo writes 355 1.13 16.51
.36
rollbacks only - consistent 11 .03 .51
.01
session connect time 1706382177 5417086.28 79366612.88
1737660.06
session logical reads 139725 443.57 6498.84
142.29
session pga memory 5045104 16016.2 234656
5137.58
session pga memory max 5270540 16731.87 245141.4
5367.15
session uga memory -714472 -2268.17 -33231.26
-727.57
session uga memory max 1509108 4790.82 70191.07
1536.77
sorts (memory) 2065 6.56 96.05
2.1
sorts (rows) 333714 1059.41 15521.58
339.83
table fetch by rowid 20191 64.1 939.12
20.56
table fetch continued row 621 1.97 28.88
.63
table scan blocks gotten 85890 272.67 3994.88
87.46
table scan rows gotten 2911891 9244.1 135436.79
2965.27
table scans (long tables) 28 .09 1.3
.03
table scans (short tables) 3141 9.97 146.09
3.2
total file opens 16 .05 .74
.02
user calls 1394 4.43 64.84
1.42
user commits 315 1 14.65
.32
user rollbacks 104 .33 4.84
.11
86 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 message from client 1288 931269
723.03
pipe get 1975 196280
99.38
db file scattered read 1589 1084
.68
db file sequential read 1087 920
.85
SQL*Net more data to client 2569 47
.02
SQL*Net more data from client 1773 39
.02
file open 28 10
.36
refresh controlfile command 10 9
.9
control file sequential read 36 7
.19
log file sync 14 7
.5
file identify 4 3
.75
SQL*Net message to client 1285 2
0
SQL*Net break/reset to client 8 1
.13
SQL*Net message from dblink 21 0
0
SQL*Net message to dblink 21 0
0
latch free 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
-------------------------------- ------------- -------------
-------------
rdbms ipc message 1331 294275
221.09
pmon timer 327 98100
300
smon timer 3 90000
30000
log file parallel write 355 76
.21
control file sequential read 20 6
.3
control file parallel write 327 5
.02
latch free 1 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 327 0 1 0
0
Checkpoint queue l 22942 0 1 0
0
Token Manager 12 0 1 0
0
cache buffer handl 312 0 1 0
0
cache buffers chai 321413 0 1 0
0
cache buffers lru 20052 0 1 0
0
dml lock allocatio 1931 0 1 0
0
enqueue hash chain 3916 0 1 0
0
enqueues 5567 0 1 0
0
global transaction 34 0 1 0
0
global tx free lis 8 0 1 0
0
global tx hash map 18 0 1 0
0
ktm global data 3 0 1 0
0
latch wait list 2 0 1 0
0
library cache 83141 21 1 2
.095
library cache load 420 0 1 0
0
list of block allo 719 0 1 0
0
messages 3027 0 1 0
0
modify parameter v 21 0 1 0
0
multiblock read ob 3252 0 1 0
0
ncodef allocation 15 0 1 0
0
process allocation 6 0 1 0
0
redo allocation 7985 6 .999 1
.167
redo copy 1 1 0 1
1
redo writing 1384 0 1 0
0
row cache objects 14008 0 1 0
0
sequence cache 48 0 1 0
0
session allocation 819 0 1 0
0
session idle bit 3170 0 1 0
0
session switching 15 0 1 0
0
shared pool 12382 0 1 0
0
sort extent pool 3 0 1 0
0
transaction alloca 1811 0 1 0
0
transaction branch 43 0 1 0
0
undo global data 1095 0 1 0
0
user lock 68 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 8 0 1
cache buffers chai 37671 0 1
cache buffers lru 19703 0 1
latch wait list 2 0 1
library cache 432 0 1
process allocation 6 0 1
redo copy 8392 2 1
row cache objects 8 0 1
vecio buf des 8 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
------------------ ---------------- ----------------
0 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 4 0
0 401408 0 0
0
2 104 0
67818 565248 0 0
0
3 142 0
198432 565248 1 0
1
4 125 0
167824 442368 0 0
2
5 118 0
162480 565248 0 0
0
6 100 0
52320 565248 -1 0
0
7 113 0
39474 851968 0 0
0
8 101 0
39192 851968 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 0
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
dc tablespaces 2 0 0 0 0 12
8
dc free extents 250 0 1 0 1 331
305
dc segments 596 0 0 0 1 1112
1092
dc rollback seg 66 0 0 0 0 28
12
dc used extents 165 1 0 0 1 824
822
dc users 220 0 0 0 0 31
23
dc user grants 86 0 0 0 0 51
20
dc objects 441 1 0 0 2 843
840
dc synonyms 31 0 0 0 0 24
23
dc usernames 160 0 0 0 0 21
20
dc object ids 2411 0 0 0 0 795
789
dc sequences 5 0 0 0 0 20
9
dc profiles 4 0 0 0 0 2
1
dc database lin 2 0 0 0 0 11
10
dc histogram de 291 0 0 0 0 4233
4230
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 Mon Apr 09 2001 - 09:59:00 CDT
 |
 |