| 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 folks!
Some of the people which have answer my posting ask for output of utbestat.sql/utlestat.sql output here it comes.
Kind regards
//Tommy
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 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
Charwidth 27 SVRMGR> set numwidth 12; Numwidth 12
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"
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
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
SVRMGR> SVRMGR> SVRMGR> set numwidth 27 Numwidth 27
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 platformspecific limit
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
SVRMGR> SVRMGR> SVRMGR> set charwidth 32; Charwidth 32 SVRMGR> set numwidth 13; Numwidth 13
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
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
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
------------------ ----------- ----------- ----------- -----------
-----------
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
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
------------------ ---------------- ---------------- ----------------
cache buffers chai 9896870 885 886
cache buffers lru 95586 0 1
process allocation 4 0 1
3 rows selected.
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
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
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.
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 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
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
 |
 |