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;
NAMEVALUE
---
---
background_dump_deste:\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_buffers7
db_block_checking FALSE
db_block_checksum FALSE
db_block_lru_latches2
db_block_max_dirty_target 7
db_block_size 8192
db_file_multiblock_read_count 114
db_files1024
db_name opt1
dml_locks 700
enqueue_resources 1548
fast_start_io_target7
instance_name opt1
java_pool_size 32768
job_queue_interval 60
job_queue_processes 10
large_pool_size 200
lm_locks12000
lm_ress 6000
log_archive_destj:\optimabackups
log_archive_start TRUE
log_buffer 655360
log_checkpoint_interval 1
log_checkpoints_to_alertFALSE
max_dump_file_size 10240
max_enabled_roles 100
max_rollback_segments 30
object_cache_optimal_size 102400
open_cursors300
optimizer_features_enable 8.1.6
optimizer_max_permutations 8
optimizer_mode CHOOSE
parallel_automatic_tuning TRUE
parallel_min_servers2
processes 150
remote_login_passwordfile EXCLUSIVE
service_names opt1
shared_pool_reserved_size 1600
shared_pool_size7500
sort_area_retained_size 400
sort_area_size 400
sort_multiblock_read_count 2
sql_trace FALSE
timed_statisticsTRUE
user_dump_dest e:\oracle\ora81\rdbms\trace
50 rows selected.
SVRMGR select n1.name Statistic,
2n1.change Total,
3round(n1.change/trans.change,2) Per Transaction,
4round(n1.change/((start_users + end_users)/2),2) Per Logon,
5round(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, 'S')) -
8 to_number(to_char(start_time, 'S')))
9 , 2) Per Second
10from
11 stats$stats n1,
12 stats$stats trans,
13 stats$dates
14where
15 trans.name='user commits'
16 and n1.change != 0
17order by n1.name;
Statistic TotalPer Transact Per LogonPer Second
---
CPU used by this session 6890325 1490.77135104.41
20030.01
CPU used when call started 15893 3.44 311.63
46.2
CR blocks created224 .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 20138543.57 3948.73
585.42
DBWR lru scans64 .01 1.25
.19
DBWR make free requests 68 .01 1.33
.2
DBWR summed scan depth201533 43.6 3951.63
585.85
DBWR transaction table writ 110 .22
.03
DBWR undo block writes 193 .04 3.78
.56
Parallel operations downgra10 .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 count791315 171.21 15515.98
2300.33
buffer is pinned count 8775118.99