| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Problem with redo logs.
Hello,
We have created a volume for redo logs by striping across 6 disks using the Veritas volume manger, and we have 4 redo log files 250M each,
there are no other files on this volume other than the redo logs.
Currently the redo logs switch every 7 minutes during peak usage. The
database is in no archivelog mode that is we are not archiving the redo
logs. We are monitoring the I/O activity through VXSTAT a veritas tool
for all our database volumes.
On analyzing the output from vxstat we find that the service wait
times on redo log volumes are terrible with a huge waits on redo reads
?!! The question is why does ORACLE read from the redo logs and that too
such a huge number of blocks, when it was never in archive log mode? and
if there is a reason for its reads, how can it be tuned? I have pasted
an extract of the vxstat output for redo log volume and a copy of the
init.ora file.
Any help will be greatly appreciated.
Ravi Kommalapati
ravi_kommalapati_at_sabre.com
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_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
##########################################################################
# Allow DBA accounts within the database to perform tasks as INTERNAL
Acct.
#REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
Received on Tue Apr 13 1999 - 14:25:17 CDT
 |
 |