| 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
Add more rollback segments (10 or more would be good) you are getting some waits due to that. Also I would change or add the init.ora parameter transactions_per_rollback_segment=3 (check the exact name, I don't have my manual in front of me) I think it will help, but probably not solve your problem. On the other hand it won't hurt. Jim
"Tommy Svensson" <tkman23_at_hotmail.com> wrote in message
news:ebf7c6db.0105240209.5934d983_at_posting.google.com...
> 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
> --------------------------------------------------------------------------
 |
 |