Ravi Kommalapati
[email protected]
OPERATIONS
BLOCKS
AVG TIME(ms)
TYP NAME
READ WRITE READ
WRITE READ WRITE
vol REDO_LOG 24
1131 12288
18096 25.8 10.9
sd disk3480-05
16 195
2048 3120 20.6 10.5
sd disk3490-05
16 183
2048 2928 17.5 12.3
sd disk3500-05
16 182
2048 2912
21.2 11.0
sd disk3510-05
16 203
2048 3248
18.8 11.3
sd disk3520-05
16 180
2048 2880
18.8 9.6
sd disk3530-05
16 187
2048 2992
18.8 10.4
vol REDO_LOG 23
1076 11280
17216
24.8 11.3
sd disk3480-05
15 184
1920 2944
18.7 9.9
sd disk3490-05
15 198
1920 3168
20.0 9.8
sd disk3500-05
15 172
1920 2752
22.7 12.8
sd disk3510-05
15 165
1920 2640
20.0 11.4
sd disk3520-05
14 175
1792 2800
15.7 12.7
sd disk3530-05
15 182
1808 2912
14.7 11.4
Init.ora
db_name = usoddevl
# Database block size.
db_block_size =
8192
# Set the compatible parameter to utilize 7.3.4 features.
compatible=7.3.4
# Location of Archive Log files and their format.
#log_archive_dest = /usodunit/u42/archivelog/
#log_archive_format = usodunit%s.alg
# Enable the Archiver Background process so that the Redo Log files
# are automatically archived to the Archive Log Destination.
#log_archive_start = true
db_domain = wolfdev
# Turn off hash joins in 7.3.x per recommendation by Oracle Worldwide
Support.
#hash_join_enabled = false
# Asynchronous Reads and Writes available for cooked files on Solaris.
async_write = true
async_read = true
# Need only ONE dbwr process when Asynchronous Reads and Writes are
enabled.
db_writers = 1
# Enable the Checkpoint process to speed up checkpoints.
checkpoint_process = true
# Maximum number of open cursors.
open_cursors = 250
# pre_page_sga(shared memory locking) is set to true
# with the use_ism set to false for sun solaris
#pre_page_sga = true
sort_direct_writes = true
sort_read_fac = 32
spin_count = 2000
# added on apr 9
#sort_write_buffers = 4
#sort_write_buffer_size = 65536
# The always_anti_join = hash is added for 7.3.4 Mar 15
always_anti_join = hash
# The Intimate Shared Memory parameter allows different processes
# attaching to the Shared Memory Address to share the same Page Table.
# Only applicable for Sun-4m and Sun-4d systems.
# WOLFDEV is a Sun-4u system.
# set this true on mar 25th
use_ism = true
# readv() system call increases I/O throughput for sequential read
# activity by reducing CPU overhead associated with buffer copying.
use_readv = true
# Maximum number of files allowed for the database.
db_files = 1000
max_enabled_roles=40
db_file_multiblock_read_count = 32
#db_block_buffers = 70000
#db_block_buffers = 19200
#db_block_buffers = 58000
db_block_buffers = 179000
#db_block_buffers = 200000
#db_block_buffers = 105000 changed on mar 25th
#db_block_buffers = 85000
shared_pool_size = 72000000
#shared_pool_size = 29000000
#shared_pool_size = 45000000
#shared_pool_size = 20000000
processes = 200
# Total number of concurrent locks that can be held in the database.
dml_locks = 500
#
LARGE
#log_buffer = 16380 apr 7
log_buffer = 4915200 #8192*600
#apr 8
#log_buffer = 2048000 #8192*250
#apr 8
#log_buffer = 1064960 #8192*130
#log_entry_prebuild_threshold = 65536 # apr 8
# Number of O/S blocks to modify before checkpointing.
log_checkpoint_interval = 50000000
log_simultaneous_copies = 48 # apr 8
# Determines the time for which redo log buffer latches are held.
# SUN Microsystems recommends that this value be set to zero.
log_small_entry_max_size = 0 # default 800
sequence_cache_entries = 100
#
LARGE
sequence_cache_hash_buckets = 89
#
LARGE
timed_statistics = false # apr 5th
max_dump_file_size = 153600 # limit trace file size to 10 Meg each
#sort_area_retained_size = 3145728 commented
sort_area_retained_size = 20000000
#sort_area_retained_size = 4000000
#sort_area_retained_size = 15728640
#sort_area_retained_size = 7862820
#sort_area_retained_size = 1048576
#sort_area_size = 3145728 commented
#sort_area_size = 400000000 # changed to 400 M apr 6th
sort_area_size = 300000000 # changed to 300 M apr 7th
#sort_area_size = 200000000 commented apr 1st
#sort_area_size = 4000000
#sort_area_size = 15728640
#sort_area_size = 7862820
#sort_area_size = 1048576
# Date format for Y2K compliance.
nls_date_format = DD-MON-RR
optimizer_mode = ALL_ROWS
optimizer_percent_parallel = 100
rollback_segments = (RBS01,RBS02, RBS03, RBS04,RBS05, RBS06, RBS07
RBS08,RBS09, RBS10, RBS11, RBS12, RBS13, RBS14,
RBS15,
RBS16, RBS17, RBS18, RBS19, RBS20)
resource_limit = true
#########################################################################
# <<Parallel Query Option Parameters>>
# Maximum number of PQ servers allowed to exist simultaneously.
# 2 * max_degree(12) * number_of_current_users(36)
parallel_max_servers = 256
# Number of PQ server created when the instance starts.
# More PQ servers will be spawned as required.
parallel_min_servers = 10 # changed back 18Oct96
# Maximum degree of parallelism available for any Full Table Scans.
# Should be set to number of disks in stripe.
#parallel_default_max_scans = 24
# Used to calculate the degree of parallelism for a table created or
altered
# using the PARALLEL option without the degree of parallelism being
specified.
#parallel_default_scansize= 24
# Number of minutes that at PQ Server can remain idle before being
terminated.
#parallel_server_idle_time = 5
##########################################################################
Hmmm ... Interesting . I'm not sure whether a striped FS is the best
approach
for redo logs. They are, depending on the applications you run,
flushed quite
regularly so to what degree any parallelism can be used for the write
is
questionable. Also, do you need it. It's more important for the datafiles
to
be striped. Unless your doing mass loads then redo logs performance
on a
single disk should be OK. I did put up a thread on this, but had no
replies.
I'll watch this one with interest.
Striping REDO LOGS is not a good idea since LGWR writes sequencially
** Tuning Redo Log Buffer **
- to see when check points occure set INIT.ORA you'll get it in the
ALERT FILE
!!!
LOG_CHECKPOINTS_TO_ALERT=TRUE
- to turn EXTRA checkpoints OFF set:
LOG_CHECKPOINT_INTERVAL=<LARGEST LOG SIZE>
LOG_CHECKPOINT_TIMEOUT=FALSE
Run this select statement:
select (req.value*5000)/entries.value "Ratio"
from v$sysstat req
, v$sysstat entries
where req.name = 'redo
log space requests'
and entries.name = 'redo entries'
=========> "Ratio" should be < 1
!!!!!!!!!
Thanks for the response,
The question I have is why is striping redo logs not a good idea,
The
understanding I have is that disk striping using a volume manager is
good no
matter what type of wites, the reasoning being that if say the log
buffer were to
flush out 150M of data, this request would go to the volume manager.
the volume
manager seeing that the disks are striped, sends out the data to the
disks in
parallell according to the stripe width and each disk would still be
doing a
sequential write, even if the controller is the same for those disks,
it is still
advantageous because the controller delay is a lot less than disk delays.
Ans to the Reply:
When you write out redo buffer to the disk using Veritas Manages or
any other
software raid
you are using extra CPU about 30%
If you want take a good use of striping you should use multiple controllers
--->
HARDWARE raid,
you'll ask why, because controllers have their own CPUs and RAM
on board!
Oracle writes to the log files in groups of log blocks. The number of
block
written in a single write depends on what is available in the log_buffer
at
the time. On Solaris a log block is just 512 bytes. So Oracle is doing
writes
of sizes much smaller than your filesystem block size, and they are
sync
write. To satisfy such a write, the file system layer has to read the
target
filesystem block, modify it, and then write it back. That is probably
where
your reads are coming from. The only solution to this prevalent problem
is to
use raw plexes for you log files.
By the way, striping log files is only of benefit to databases that
do large
DML operations with very few commits.
Steve Adams