Re: Problem wih Oracle reads from the redo logs.

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.

Ravi Kommalapati wrote:

> 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_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
>
> ##########################################################################
>
> # Allow DBA accounts within the database to perform tasks as INTERNAL
> Acct.
> #REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
Received on Tue Apr 13 1999 - 11:22:11 CDT