Performance Tuning

From: Stephen Gabor <sgabor_at_removethis.jamesway.com>
Date: Thu, 7 Oct 1999 08:17:31 -0400
Message-ID: <37fc8e19_2@news1.prserv.net>

I am relatively new to Oracle having started working with it about a year ago. I am trying to tune and understand the performance of my server. I think I must be missing something because the tuning report seems to be getting worse not better.

Server is Compaq Proliant 6000 running Oracle 7.3.4 and Baan 4c3. 4 x 450 MHz Xeon
1 GB RAM (Average 400MB Free)
Internal Storage
9.1 GB HDD - NT, OS Swap Tools (ArcServe, Scripts, etc.) 9.1 GB HDD - Baan Application
18.2 GB HDD - Archived Redo Logs
External Storage - Fiber Channel
9.1 GB HDD - Oracle Executables, Redo Log Group A

3 x 9.1 GB HDD - Baan Tables
2 x 9.1 GB HDD - Baan Indicies, and Users TEMP
1 x 9.1 GB HDD - Rollback, and Developers TEMP
1 x 9.1 GB HDD - System, Developers Tables/Indidices
1 x 9.1 GB HDD - Redo Logs Group B and Dump

Any help or guidance would be appreciated

Thanks

Stephen

From: Dave Sisk's Oracle on NT Site - Check Tuning Script:

Current Date and Time: 10/06/1999 23:55:02 Database name: BAAN

Measurement                       Goal      Value Action
------------------------------ ------ ----------- --------------------------
-----------------------
Library cache hit percent      >=0.99        1.00 Increase the

SHARED_POOL_SIZE in INIT.ORA
-- Current value: shared_pool_size = 60000000

Dictionary cache hit percent   >=0.90        0.94 OK
Buffer cache hit percent       >=0.90        0.97 OK
Sort area hit percent          >=0.90        0.74 Increase the

SORT_AREA_SIZE in INIT.ORA
-- Current value: sort_area_size = 16384
Redo Log space requests =0 14 Increase the LOG_BUFFER in INIT.ORA

"                                                 Examine Redo Log size
"                                                 Examine number of Redo Log

groups
-- Current value: log_buffer = 78643200

Current value: redo log size = 52428800
Current value: # of redo log groups = 9 Enqueue waits =0 615 Increase the ENQUEUE_RESOURCES in INIT.ORA
Current value: enqueue_resources = 10000 Checkpoints not completed <=1 2 Increase LOG_CHECKPOINT_INTERVAL in INIT.ORA " Examine LOG_CHECKPOINT_TIMEOUT in INIT.ORA " Examine Redo Log size
Current value: log_checkpoint_interval = 5000
Current value: log_checkpoint_timeout = 0
Current value: redo log size = 52428800 Rollback contention ratio <=0.01 0.00 OK Redo allocation latch miss (W) <=0.01 0.00 OK Redo allocation latch miss (I) <=0.01 0.00 OK Redo copy latch miss (WTW) <=0.01 0.74 Increase LOG_SIMULTANEOUS_COPIES 2xCPU in INIT.ORA
Current value: log_simultaneous_copies = 16
Current value: log_small_entry_max_size = 80 Redo copy latch miss (Immed) <=0.01 0.00 OK Freelist contention ratio <=0.01 0.00 OK

Init.ora File:

db_name = BAAN
db_files = 50

control_files = (K:\ORADATA\ctl1BAAN.ora,
                 G:\ORADATA\ctl2BAAN.ora,
                 E:\ORADATA\ctl3BAAN.ora,
                 J:\ORADATA\ctl4BAAN.ora )

compatible = 7.3.0
transactions = 10000
shared_pool_size = 60000000
processes = 1000
dml_locks = 8192
enqueue_resources = 10000

sequence_cache_entries = 100
sequence_cache_hash_buckets = 89
pre_page_sga = TRUE
max_dump_file_size = 10240

background_dump_dest = %RDBMS73%\trace
user_dump_dest = %RDBMS73%\trace
remote_login_passwordfile = shared
text_enable = true
# Hash

hash_area_size = 16384
hash_multiblock_io_count = 2
hash_join_enabled = true

bitmap_merge_area_size = 65536
# DB parameters

db_block_buffers = 40000
db_block_size = 8192
db_block_checkpoint_batch = 8
db_file_simultaneous_writes = 8
db_block_lru_latches = 4
db_file_multiblock_read_count = 16

# Cursors information
session_cached_cursors = 2048
open_cursors = 2048
row_cache_cursors = 1024
cursor_space_for_time = true
# Sort

sort_area_size = 16384
sort_area_retained_size = 0
sort_read_fac = 20
sort_write_buffer_size = 16384
sort_write_buffers = 3
sort_direct_writes = true

#
# Rollback information
temporary_table_locks = 2205
max_rollback_Segments = 400
# Redolog information
#

log_buffer = 78643200
log_checkpoints_to_alert = true
log_checkpoint_interval = 5000

checkpoint_process = TRUE

log_archive_start = TRUE
log_archive_dest = M:\ORADATA\ARCHIVE
log_archive_format = ARC%s.ARC
log_simultaneous_copies = 16

Received on Thu Oct 07 1999 - 07:17:31 CDT