Suggestion for init parameters

[diepa]

Hi,
My customer's site is running Oracle 8.1.7.0 on Windows NT 4.0. There are about 150 concurrent users. Transaction are saved very quickly (a loot of inserts per row), but the problem is in some reports or process which scan thru enormous amount of data. When such reports are run, the rest of the users go for a toss, waiting endlessly for their transactions.
I am attaching the parameters in the init.ora file. Apart from SQL level tuning, which we have already optimized, pls suggest any improvements. I tried several thing, but we still have this huge problem.
Thanks in advance
DEP

db_files = 1024
open_cursors = 600
max_enabled_roles = 30
db_file_multiblock_read_count = 7
log_buffer = 65536
db_block_buffers = 148176
shared_pool_size = 103246208
large_pool_size = 0
java_pool_size = 0
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 400
optimizer_index_cost_adj = 30
# turn on parallel query and let Oracle tune it
parallel_automatic_tuning = TRUE #previous value: FALSE
parallel_min_servers = 2
parallel_max_servers = 3
# allow the use of function-based indexes in the optimizer
query_rewrite_enabled = true

job_queue_processes = 5
job_queue_interval = 3600

#audit_trail = true # if you want auditing
timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5M each

log_archive_start = TRUE
log_archive_dest_1 = "location=F:\Oracle_Backup\ARC_Files"
log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

db_block_size = 8192

compatible = 8.1.7.0.0 #previous value: 8.1.0
sort_area_size = 65536 #previous 65536
sort_area_retained_size = 65536 #previous 65536
db_block_lru_latches = 8
db_block_lru_statistics=FALSE #obsolete parameter
db_writer_processes = 4 #previous value: 1
session_cached_cursors=100

utl_file_dir = f:\

 

[carp]

You might want to look at db_file_multiblock_read_count.

Also, would it be possible to run reports when your users are NOT running a lot of DML? We found that our users were OK with a 10 hour lag time (especially for historical data!), so we automatically run a lot of reports just before the start of the business day (about 0630). This gets a lot of the reports out of the way.

Other tricks you might try would involve parallel queries, reviewing your index strategy, or materialized views.

Good luck!!