| 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: Performence on Oracle instance running on Sun Solaris/Sparc
Hi!
Below I post a output of utlbstat.sql och utlestat.sql running during
more
then 12hour during intensive report running on our Norway server
---
SVRMGR>
SVRMGR> set charwidth 12
Charwidth 12
SVRMGR> set numwidth 10
Numwidth 10
SVRMGR> Rem Select Library cache statistics. The pin hit rate shoule
be high.
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 19454 .998 19454 .998 0
0
CLUSTER 0 1 0 1 0
0
INDEX 0 1 0 1 0
0
OBJECT 0 1 0 1 0
0
PIPE 0 1 0 1 0
0
SQL AREA 55767 .962 391304 .993 0
1
TABLE/PROCED 2992 .505 232723 .994 0
0
TRIGGER 0 1 0 1 0
0
8 rows selected.
SVRMGR>
SVRMGR> set charwidth 27;
Charwidth 27
SVRMGR> set numwidth 12;
Numwidth 12
SVRMGR> Rem The total is the total value of the statistic between the
time
SVRMGR> Rem bstat was run and the time estat was run. Note that the
estat
SVRMGR> Rem script logs on as "internal" so the per_logon statistics
will
SVRMGR> Rem always be based on at least one logon.
SVRMGR> select n1.name "Statistic",
2> n1.change "Total",
3> round(n1.change/trans.change,2) "Per Transaction",
4> round(n1.change/logs.change,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 stats$stats n1, stats$stats trans, stats$stats logs,
stats$dates
11> where trans.name='user commits'
12> and logs.name='logons cumulative'
13> and n1.change != 0
14> order by n1.name;
Statistic Total Per Transact Per Logon Per
Second
--------------------------- ------------ ------------ ------------
------------
CPU used by this session 2219381 228.92 685.21
57.26
CPU used when call started 2219381 228.92 685.21
57.26
CR blocks created 927 .1 .29
.02
DBWR buffers scanned 46606 4.81 14.39
1.2
DBWR checkpoints 328 .03 .1
.01
DBWR free buffers found 32357 3.34 9.99
.83
DBWR lru scans 227 .02 .07
.01
DBWR make free requests 114 .01 .04
0
DBWR summed scan depth 46640 4.81 14.4
1.2
DBWR timeouts 12296 1.27 3.8
.32
SQL*Net roundtrips to/from 2830687 291.97 873.94
73.03
background checkpoints comp 15 0 0
0
background checkpoints star 15 0 0
0
background timeouts 41857 4.32 12.92
1.08
bytes received via SQL*Net 36120900 3725.72 11151.87
931.91
bytes sent via SQL*Net to c 614684350 63402.2 189775.96
15858.73
calls to get snapshot scn: 176513 18.21 54.5
4.55
calls to kcmgas 9763 1.01 3.01
.25
calls to kcmgcs 420 .04 .13
.01
calls to kcmgrs 204742 21.12 63.21
5.28
change write time 3293 .34 1.02
.08
cleanouts and rollbacks - c 113 .01 .03
0
cleanouts only - consistent 100 .01 .03
0
cluster key scan block gets 425791 43.92 131.46
10.99
cluster key scans 180004 18.57 55.57
4.64
commit cleanout failures: b 2 0 0
0
commit cleanout failures: c 78 .01 .02
0
commit cleanout number succ 20379 2.1 6.29
.53
consistent changes 1299 .13 .4
.03
consistent gets 1051878703 108497.03 324754.15
27138.25
cursor authentications 319 .03 .1
.01
data blocks consistent read 1275 .13 .39
.03
db block changes 144254 14.88 44.54
3.72
db block gets 173999 17.95 53.72
4.49
deferred (CURRENT) block cl 11667 1.2 3.6
.3
dirty buffers inspected 1 0 0
0
enqueue conversions 4257 .44 1.31
.11
enqueue releases 32589 3.36 10.06
.84
enqueue requests 35135 3.62 10.85
.91
enqueue timeouts 2551 .26 .79
.07
enqueue waits 22 0 .01
0
execute count 210491 21.71 64.99
5.43
free buffer inspected 73 .01 .02
0
free buffer requested 66609 6.87 20.56
1.72
immediate (CR) block cleano 213 .02 .07
.01
immediate (CURRENT) block c 1666 .17 .51
.04
logons cumulative 3239 .33 1
.08
messages received 4984 .51 1.54
.13
messages sent 4984 .51 1.54
.13
no work - consistent read g 1050974193 108403.73 324474.9
27114.92
opened cursors cumulative 55704 5.75 17.2
1.44
opened cursors current 12 0 0
0
parse count 66524 6.86 20.54
1.72
parse time cpu 4379 .45 1.35
.11
parse time elapsed 22711 2.34 7.01
.59
physical reads 61619 6.36 19.02
1.59
physical writes 13519 1.39 4.17
.35
process last non-idle time 1291579595 133221.21 398758.75
33322.49
recursive calls 1195197 123.28 369
30.84
recursive cpu usage 13748 1.42 4.24
.35
redo blocks written 30665 3.16 9.47
.79
redo buffer allocation retr 18 0 .01
0
redo entries 72925 7.52 22.51
1.88
redo log space requests 28 0 .01
0
redo log space wait time 1372 .14 .42
.04
redo size 13969988 1440.95 4313.06
360.42
redo small copies 72906 7.52 22.51
1.88
redo synch time 32 0 .01
0
redo synch writes 3205 .33 .99
.08
redo wastage 1156728 119.31 357.13
29.84
redo write time 24214 2.5 7.48
.62
redo writes 7101 .73 2.19
.18
rollback changes - undo rec 14 0 0
0
rollbacks only - consistent 805 .08 .25
.02
session connect time 1291579595 133221.21 398758.75
33322.49
session logical reads 1052048335 108514.53 324806.53
27142.63
session pga memory 2832210792 292131.08 874409.01
73070.45
session pga memory max 3010660596 310537.45 929503.12
77674.42
session uga memory 9562224 986.3 2952.21
246.7
session uga memory max 265006144 27334.31 81817.27
6837.1
sorts (disk) 212 .02 .07
.01
sorts (memory) 33602 3.47 10.37
.87
sorts (rows) 1273738 131.38 393.25
32.86
table fetch by rowid 1025678874 105794.62 316665.29
26462.3
table fetch continued row 6859047 707.48 2117.64
176.96
table scan blocks gotten 1721928 177.61 531.62
44.43
table scan rows gotten 4518355 466.05 1394.98
116.57
table scans (long tables) 35 0 .01
0
table scans (short tables) 17008 1.75 5.25
.44
total number commit cleanou 20481 2.11 6.32
.53
transaction rollbacks 6 0 0
0
user calls 2833906 292.31 874.93
73.11
user commits 9695 1 2.99
.25
write requests 6704 .69 2.07
.17
94 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set numwidth 27
Numwidth 27
SVRMGR> Rem Average length of the dirty buffer write queue. If this
is larger
SVRMGR> Rem than the value of:
SVRMGR> Rem 1. (db_files * db_file_simultaneous_writes)/2
SVRMGR> Rem or
SVRMGR> Rem 2. 1/4 of db_block_buffers
SVRMGR> Rem which ever is smaller and also there is a platform
specific limit
SVRMGR> Rem on the write batch size (normally 1024 or 2048 buffers).
If the average
SVRMGR> Rem length of the dirty buffer write queue is larger than the
value
SVRMGR> Rem calculated before, increase db_file_simultaneous_writes or
db_files.
SVRMGR> Rem Also check for disks that are doing many more IOs than
other disks.
SVRMGR> select queue.change/writes.change "Average Write Queue Length"
2> from stats$stats queue, stats$stats writes
3> where queue.name = 'summed dirty queue length'
4> and writes.name = 'write requests';
Average Write Queue Length
---------------------------
0
1 row selected.
SVRMGR>
SVRMGR>
SVRMGR> set charwidth 32;
Charwidth 32
SVRMGR> set numwidth 13;
Numwidth 13
SVRMGR> Rem System wide wait events for non-background processes
(PMON,
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
-------------------------------- ------------- -------------
-------------
rdbms ipc message 3260 9708613
2978.1
dispatcher timer 646 3880557
6007.05
virtual circuit status 1291 3877574
3003.54
SQL*Net message from client 2830722 289956
.1
db file sequential read 28352 16464
.58
SQL*Net message to client 2830722 8230
0
db file scattered read 4304 2063
.48
latch free 462 1742
3.77
log file switch completion 28 1372
49
rdbms ipc reply 69 966
14
buffer busy waits 101 358
3.54
write complete waits 53 235
4.43
enqueue 20 69
3.45
SQL*Net more data to client 1045 20
.02
log file sync 3 8
2.67
instance recovery 1 6
6
control file sequential read 125 0
0
SQL*Net more data from client 3 0
0
18 rows selected.
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 43238 15434099
356.96
pmon timer 12892 3876067
300.66
smon timer 133 3864849
29059.02
db file parallel write 6704 28838
4.3
log file parallel write 7101 24203
3.41
control file parallel write 436 2895
6.64
log file sync 821 2680
3.26
latch free 313 949
3.03
timer in sksawat 347 729
2.1
db file single write 150 259
1.73
enqueue 2 103
51.5
log file single write 60 102
1.7
rdbms ipc reply 2 42
21
db file sequential read 168 38
.23
db file scattered read 29 29
1
control file sequential read 751 22
.03
log file sequential read 30 11
.37
buffer busy waits 3 6
2
18 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set charwidth 18;
Charwidth 18
SVRMGR> set numwidth 11;
Numwidth 11
SVRMGR> Rem Latch statistics. Latch contention will show up as a large
value for
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
------------------ ----------- ----------- ----------- -----------
-----------
archive control 15 0 1 0
0
cache buffer handl 21 0 1 0
0
cache buffers chai 2104481738 232 1 574
2.474
cache buffers lru 87182 0 1 0
0
dml lock allocatio 33217 0 1 0
0
enqueue hash chain 71993 2 1 3
1.5
enqueues 92446 0 1 0
0
ktm global data 145 0 1 0
0
latch wait list 190 0 1 0
0
library cache 1887509 94 1 161
1.713
library cache load 2802 0 1 0
0
list of block allo 19455 0 1 0
0
messages 106628 2 1 2
1
modify parameter v 3239 0 1 0
0
multiblock read ob 8956 0 1 0
0
process allocation 4 0 1 0
0
redo allocation 109863 15 1 25
1.667
row cache objects 236144 2 1 3
1.5
sequence cache 11 0 1 0
0
session allocation 448490 1 1 2
2
session idle bit 5676559 2 1 2
1
session switching 626 0 1 0
0
shared pool 108173 0 1 0
0
sort extent pool 1357 0 1 0
0
system commit numb 233533 1 1 2
2
transaction alloca 30455 1 1 1
1
undo global data 31362 0 1 0
0
user lock 12 0 1 0
0
virtual circuit qu 2583 0 1 0
0
29 rows selected.
SVRMGR>
SVRMGR> set numwidth 16
Numwidth 16
SVRMGR> Rem Statistics on no_wait gets of latches. A no_wait get does
not
SVRMGR> Rem wait for the latch to become free, it immediately times
out.
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
------------------ ---------------- ---------------- ----------------
cache buffers chai 9896870 885 886
cache buffers lru 95586 0 1
process allocation 4 0 1
3 rows selected.
SVRMGR>
SVRMGR> Rem Buffer busy wait statistics. If the value for 'buffer
busy wait' in
SVRMGR> Rem the wait event statistics is high, then this table will
identify
SVRMGR> Rem which class of blocks is having high contention. If there
are high
SVRMGR> Rem 'undo header' waits then add more rollback segments. If
there are
SVRMGR> Rem high 'segment header' waits then adding freelists might
help. Check
SVRMGR> Rem v$session_wait to get the addresses of the actual blocks
having
SVRMGR> Rem contention.
SVRMGR> select * from stats$waitstat
2> where count != 0
3> order by count desc;
CLASS COUNT TIME
------------------ ---------------- ----------------
data block 53 311
undo header 51 53
2 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set numwidth 19;
Numwidth 19
SVRMGR> Rem Waits_for_trans_tbl high implies you should add rollback
segments.
SVRMGR> select * from stats$roll;
UNDO_SEGMENT TRANS_TBL_GETS TRANS_TBL_WAITS
UNDO_BYTES_WRITTEN SEGMENT_SIZE_BYTES XACTS SHRINKS
WRAPS
------------------- ------------------- -------------------
------------------- ------------------- -------------------
------------------- -------------------
0 134 0
0 237568 0 0
0
1 5444 0
1422494 15966208 0 0
2
2 4895 0
1382975 26615808 0 0
1
3 4736 2
1892249 18096128 0 0
2
4 5316 1
1316371 26615808 0 0
1
5 rows selected.
SVRMGR>
SVRMGR> set charwidth 39
Charwidth 39
SVRMGR> Rem The init.ora parameters currently in effect:
SVRMGR> select name, value from v$parameter where isdefault = 'FALSE'
2> order by name;
NAME VALUE
---------------------------------------
---------------------------------------
always_anti_join NESTED_LOOPS
audit_trail NONE
background_dump_dest /usr/oracle/admin/prod/bdump
cache_size_threshold 1000
compatible 7.3.0.0
control_files /var/opt/oradata/prod/cnt1,
/usr/oracle
core_dump_dest /usr/oracle/admin/prod/cdump
cpu_count 1
db_block_buffers 10000
db_block_lru_latches 1
db_block_size 8192
db_files 200
db_name prod
distributed_transactions 31
dml_locks 100
enqueue_resources 265
gc_freelist_groups 50
gc_releasable_locks 10000
job_queue_interval 30
job_queue_keep_connections TRUE
job_queue_processes 3
log_archive_dest /usr/oracle/archive/prod
log_archive_format prod_us%s.arc
log_archive_start TRUE
log_buffer 163840
log_checkpoint_interval 10000
log_simultaneous_copies 0
max_dump_file_size 10240
mts_dispatchers
(ADDRESS=(PARTIAL=yes)(PROTOCOL=ipc)),1
mts_listener_address
(ADDRESS=(PROTOCOL=ipc)(KEY=prod))
mts_max_dispatchers 10
mts_max_servers 10
mts_servers 1
mts_service prod
nls_date_format DD-MON-RR
nls_language american
nls_territory america
open_cursors 500
optimizer_mode RULE
processes 100
remote_login_passwordfile NONE
rollback_segments rbseg10, rbseg20, rbseg11,
rbseg21
sequence_cache_entries 30
sequence_cache_hash_buckets 30
sessions 115
shared_pool_size 200000000
sort_area_retained_size 65536
sort_direct_writes AUTO
sql_trace TRUE
temporary_table_locks 115
timed_statistics TRUE
transactions 126
transactions_per_rollback_segment 71
user_dump_dest /usr/oracle/admin/prod/udump
utl_file_dir *
55 rows selected.
SVRMGR>
SVRMGR> set charwidth 15;
Charwidth 15
SVRMGR> set numwidth 8;
Numwidth 8
SVRMGR> Rem get_miss and scan_miss should be very low compared to the
requests.
SVRMGR> Rem cur_usage is the number of entries in the cache that are
being used.
SVRMGR> select * from stats$dc
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 243 1 0 0 0 5
2
dc_free_extents 3040 23 16 0 16 140
115
dc_segments 3236 2602 0 0 16 2671
2656
dc_rollback_seg 2666 0 0 0 0 18
11
dc_used_extents 16 16 0 0 16 47
35
dc_users 19770 12 0 0 0 18
15
dc_user_grants 2485 12 0 0 0 24
12
dc_objects 3008 1364 0 0 0 1541
1539
dc_tables 8503 1266 0 0 0 1354
1349
dc_columns 60027 14355 4461 1265 0 15173
15168
dc_table_grants 6341 6226 0 0 0 6281
6231
dc_indexes 2151 1427 1617 1202 0 1517
1491
dc_constraint_d 88 71 37 17 0 158
156
dc_constraint_d 5 5 19 17 0 6
5
dc_synonyms 8 4 0 0 0 12
5
dc_usernames 16577 2 0 0 0 20
4
dc_object_ids 1907 1426 0 0 0 1475
1468
dc_sequences 2 1 0 0 1 14
1
dc_tablespaces 16 1 0 0 16 7
2
19 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
-------------------------------------------------------------------------------
---------- ---------- ---------- ---------- ---------- ----------
----------
FND_DATA
14647 42164 5591 1001 1001
5420 577
FND_INDEX
1418 1418 529 1063 1063
5307 786
FND_REPORT
248 1550 150 20 20
154 315
RBS1
2 2 2 4096 4096
16681 419
RBS2
1 1 0 4104 4104
16073 419
SYSTEM
16347 16482 13199 3002 3002
10163 419
TEMP
5 14 16 233 233
1980 157
TOOLS
0 0 0 0 0
0 52
USERS
0 0 0 0 0
0 52
USER_DATA
0 0 0 0 0
0 52
10 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set charwidth 48;
Charwidth 48
SVRMGR> set numwidth 10;
Numwidth 10
SVRMGR> Rem I/O should be spread evenly accross drives. A big
difference between
SVRMGR> Rem phys_reads and phys_blks_rd implies table scans are going
on.
SVRMGR> select table_space, file_name,
2> phys_reads reads, phys_blks_rd blks_read, phys_rd_time
read_time,
3> phys_writes writes, phys_blks_wr blks_wrt, phys_wrt_tim
write_time,
4> megabytes_size megabytes
5> from stats$files order by table_space, file_name;
TABLE_SPACE FILE_NAME
READS BLKS_READ READ_TIME WRITES BLKS_WRT
WRITE_TIME MEGABYTES
------------------------------
------------------------------------------------ ---------- ----------
---------- ---------- ---------- ---------- ----------
FND_DATA /d/prod/fnd_data.dbf
14647 42164 5591 1001 1001
5420 577
FND_INDEX /d/prod/fnd_index.dbf
1418 1418 529 1063 1063
5307 786
FND_REPORT /d/prod/fnd_report.dbf
248 1550 150 20 20
154 315
RBS1 /d/prod/rbs1
2 2 2 4096 4096
16681 419
RBS2 /d/prod/rbs2
1 1 0 4104 4104
16073 419
SYSTEM /d/prod/system01
16347 16482 13199 3002 3002
10163 419
TEMP /d/prod/temp01
5 14 16 233 233
1980 157
TOOLS /d/prod/tools.dbf
0 0 0 0 0
0 52
USERS /d/prod/users.dbf
0 0 0 0 0
0 52
USER_DATA /d/prod/user_data1
0 0 0 0 0
0 52
10 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> set charwidth 25
Charwidth 25
SVRMGR> Rem The times that bstat and estat were run.
SVRMGR> select to_char(start_time, 'dd-mon-yy hh24:mi:ss') start_time,
2> to_char(end_time, 'dd-mon-yy hh24:mi:ss') end_time
3> from stats$dates;
START_TIME END_TIME
------------------------- -------------------------
23-may-01 19:08:58 24-may-01 05:54:58
1 row selected.
SVRMGR>
SVRMGR> set charwidth 75
Charwidth 75
SVRMGR> Rem Versions
SVRMGR> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle7 Server Release 7.3.4.2.0 - Production
PL/SQL Release 2.3.4.2.0 - Production
CORE Version 3.5.4.0.0 - Production
TNS for Solaris: Version 2.3.4.0.0 - Production
NLSRTL Version 3.2.4.0.0 - Production
5 rows selected.
SVRMGR>
SVRMGR>
SVRMGR> spool off;
Received on Thu May 24 2001 - 05:09:04 CDT
 |
 |