| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Database Too Slow - Suggestions???
Hi Gurus,
We have an OLTP 8i database on Win 2K RAM 1GB with about 10 million rows, Total size 100GB. Have done a bit of tuning here and there (see stats below). What do you read of these stats?
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
---------------------------------------
---------------------------------------
background_dump_dest e:\oracle\ora81\rdbms\trace
compatible 8.1.6.0
control_files f:\optima\control\ctl1opt1.ora,
g:\opti
cpu_count 2
cursor_sharing EXACT
db_block_buffers 70000
db_block_checking FALSE
db_block_checksum FALSE
db_block_lru_latches 2
db_block_max_dirty_target 70000
db_block_size 8192
db_file_multiblock_read_count 114
db_files 1024
db_name opt1
dml_locks 700
enqueue_resources 1548
fast_start_io_target 70000
instance_name opt1
java_pool_size 32768
job_queue_interval 60
job_queue_processes 10
large_pool_size 2000000
lm_locks 12000
lm_ress 6000
log_archive_dest j:\optimabackups
log_archive_start TRUE
log_buffer 655360
log_checkpoint_interval 10000
log_checkpoints_to_alert FALSE
max_dump_file_size 10240
max_enabled_roles 100
max_rollback_segments 30
object_cache_optimal_size 102400
open_cursors 300
optimizer_features_enable 8.1.6
optimizer_max_permutations 80000
optimizer_mode CHOOSE
parallel_automatic_tuning TRUE
parallel_min_servers 2
processes 150
remote_login_passwordfile EXCLUSIVE
service_names opt1
shared_pool_reserved_size 16000000
shared_pool_size 75000000
sort_area_retained_size 4000000
sort_area_size 4000000
sort_multiblock_read_count 2
sql_trace FALSE
timed_statistics TRUE
user_dump_dest e:\oracle\ora81\rdbms\trace
50 rows selected.
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 6890325 1490.77 135104.41
20030.01
CPU used when call started 15893 3.44 311.63
46.2
CR blocks created 224 .05 4.39
.65
DBWR buffers scanned 201533 43.6 3951.63
585.85
DBWR checkpoint buffers wri 84 .02 1.65
.24
DBWR free buffers found 201385 43.57 3948.73
585.42
DBWR lru scans 64 .01 1.25
.19
DBWR make free requests 68 .01 1.33
.2
DBWR summed scan depth 201533 43.6 3951.63
585.85
DBWR transaction table writ 11 0 .22
.03
DBWR undo block writes 193 .04 3.78
.56
Parallel operations downgra 1 0 .02
0
SQL*Net roundtrips to/from 9954 2.15 195.18
28.94
background timeouts 374 .08 7.33
1.09
buffer is not pinned count 791315 171.21 15515.98
2300.33
buffer is pinned count 87751 18.99 1720.61
255.09
bytes received via SQL*Net 3973701 859.74 77915.71
11551.46
bytes sent via SQL*Net to c 1335012 288.84 26176.71
3880.85
calls to get snapshot scn: 14735 3.19 288.92
42.83
calls to kcmgas 4696 1.02 92.08
13.65
calls to kcmgcs 89 .02 1.75
.26
change write time 42 .01 .82
.12
cleanouts and rollbacks - c 83 .02 1.63
.24
cluster key scan block gets 86 .02 1.69
.25
cluster key scans 64 .01 1.25
.19
commit cleanout failures: c 2 0 .04
.01
commit cleanout failures: c 1 0 .02
0
commit cleanouts 4824 1.04 94.59
14.02
commit cleanouts successful 4821 1.04 94.53
14.01
consistent changes 5053 1.09 99.08
14.69
consistent gets 2760410 597.23 54125.69
8024.45
cursor authentications 31 .01 .61
.09
data blocks consistent read 5053 1.09 99.08
14.69
db block changes 19720 4.27 386.67
57.33
db block gets 21301 4.61 417.67
61.92
deferred (CURRENT) block cl 1457 .32 28.57
4.24
dirty buffers inspected 153 .03 3
.44
enqueue conversions 2 0 .04
.01
enqueue releases 9926 2.15 194.63
28.85
enqueue requests 10008 2.17 196.24
29.09
enqueue timeouts 88 .02 1.73
.26
enqueue waits 62 .01 1.22
.18
execute count 10182 2.2 199.65
29.6
free buffer inspected 211 .05 4.14
.61
free buffer requested 763568 165.2 14971.92
2219.67
hot buffers moved to head o 73930 16 1449.61
214.91
immediate (CR) block cleano 83 .02 1.63
.24
immediate (CURRENT) block c 33 .01 .65
.1
logons cumulative 321 .07 6.29
.93
messages received 4819 1.04 94.49
14.01
messages sent 4819 1.04 94.49
14.01
no work - consistent read g 2734488 591.62 53617.41
7949.09
opened cursors cumulative 10230 2.21 200.59
29.74
opened cursors current -2 0 -.04
-.01
parse count (hard) 131 .03 2.57
.38
parse count (total) 10150 2.2 199.02
29.51
parse time cpu 88 .02 1.73
.26
parse time elapsed 82 .02 1.61
.24
physical reads 762989 165.08 14960.57
2217.99
physical writes 385 .08 7.55
1.12
physical writes non checkpo 333 .07 6.53
.97
pinned buffers inspected 1 0 .02
0
prefetched blocks 752017 162.7 14745.43
2186.1
recursive calls 15382 3.33 301.61 44.72 recursive cpu usage 15190 3.29 297.84 44.16 redo blocks written 13874 3 272.04 40.33 redo entries 9926 2.15 194.63 28.85 redo size 4825692 1044.07 94621.41 14028.17 redo synch time 5832 1.26 114.35 16.95 redo synch writes 4623 1 90.65 13.44 redo wastage 2061444 446.01 40420.47 5992.57 redo write time 5646 1.22 110.71 16.41 redo writes 4660 1.01 91.37 13.55 rollback changes - undo rec 62 .01 1.22
.18
rollbacks only - consistent 141 .03 2.76
.41
rows fetched via callback 5138 1.11 100.75 14.94 session connect time 4562646061 987158.39 89463648.25 13263505.99 session logical reads 2781711 601.84 54543.35 8086.37 session pga memory 280474752 60682.55 5499504.94 815333.58 session pga memory max 291965204 63168.59 5724807.92 848736.06 session uga memory -2187916 -473.37 -42900.31 -6360.22 session uga memory max 7223784 1562.91 141642.82 20999.37 sorts (memory) 119 .03 2.33
.35
sorts (rows) 59330 12.84 1163.33 172.47 table fetch by rowid 13961 3.02 273.75 40.58 table scan blocks gotten 763908 165.28 14978.59 2220.66 table scan rows gotten 21642138 4682.42 424355.65 62913.19 table scans (cache partitio 26 .01 .51
.08
table scans (long tables) 34 .01 .67
.1
table scans (short tables) 82 .02 1.61
.24
total file opens 3 0 .06
.01
transaction rollbacks 62 .01 1.22
.18
user calls 19664 4.25 385.57 57.16 user commits 4622 1 90.63 13.44 user rollbacks 107 .02 2.1
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 platform specific limit SVRMGR> Rem on the write batch size (normally 1024 or 2048 buffers). If theaverage
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
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 46 288638 6274.74
SQL*Net message from client 11043 253220 22.93
PX Deq: Execution Msg 658 137459 208.9
enqueue 272 67238 247.2
db file scattered read 9897 34854 3.52
log file sync 4722 6015 1.27
db file sequential read 2237 4204 1.88
file open 86 578 6.72
control file sequential read 24 38 1.58
latch free 46 37 .8
SQL*Net break/reset to client 216 24 .11
SQL*Net more data to client 141 6 .04
buffer busy waits 9 5 .56
SQL*Net message to client 11044 4 0
refresh controlfile command 4 4 1
file identify 2 3 1.5
16 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 9694 128079 13.21
pmon timer 112 34245 305.76
smon timer 1 30720 30720
log file parallel write 4763 207 .04
control file parallel write 112 19 .17
db file parallel write 84 16 .19
db file sequential read 3 10 3.33
db file scattered read 2 9 4.5
control file sequential read 12 4 .33
latch free 1 1 1
LGWR wait for redo copy 1 0 0
11 rows selected.
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 268 0 1 0 0 Checkpoint queue l 2653 0 1 0 0 Token Manager 2 0 1 0 0 cache buffers chai 6337862 94 1 140
.149
cache buffers lru 371662 119 1 12
.101
channel handle poo 230 0 1 0 0 channel operations 460 0 1 0 0 dml lock allocatio 9559 0 1 0 0 enqueue hash chain 20181 0 1 0 0 enqueues 17128 1 1 0 0 event group latch 230 0 1 0 0 job_queue_processe 6 0 1 0 0 ktm global data 1 0 1 0 0 latch wait list 4 0 1 0 0 library cache 139009 95 .999 5
.053
list of block allo 9389 0 1 0 0 longop free list 94 0 1 0 0 messages 27369 3 1 0 0 multiblock read ob 19409 1 1 1 1 ncodef allocation 6 0 1 0 0 process allocation 230 0 1 0 0 process group crea 460 0 1 0 0 process queue refe 1926 0 1 0 0 redo allocation 19227 1 1 1 1 redo writing 23746 14 .999 12
.857
row cache objects 11182 5 1 0 0 sequence cache 714 0 1 0 0 session allocation 11582 0 1 0 0 session idle bit 44539 0 1 0 0 session switching 6 0 1 0 0 shared pool 18775 4 1 0 0 sort extent pool 1 0 1 0 0 transaction alloca 14090 0 1 0 0 transaction branch 6 0 1 0 0 undo global data 19553 1 1 0 0 user lock 920 0 1 0
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
------------------ ---------------- ---------------- ----------------
Token Manager 24 0 1
cache buffers chai 1515182 61 1
cache buffers lru 763318 150 1
channel handle poo 230 0 1
channel operations 230 0 1
hash table modific 24 0 1
i/o slave adaptor 24 0 1
latch wait list 4 0 1
library cache 192 0 1
process allocation 230 0 1
redo copy 9901 1 1
row cache objects 24 0 1
vecio buf des 24 0 1
13 rows selected.
Moses
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <moyam_at_mtn.co.ug INET: moyam_at_mtn.co.ug Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Jan 29 2003 - 03:23:56 CST
 |
 |