Re: forcedirectio on; more disks, performance degrading? (Solaris, Veritas VM, Oracle 7)

I believe your problem is that DIRECT I/O is "synchronous". That is, the DBWR database writer process will "block".

I say DBWR database writer process (singular, not plural) because you have DB_WRITERS set to 1.

With Oracle8 (i'm not sure about 7.3) you can increase the number of database writer processes to improve performance, because the multiple DBWx processes emulate "async i/o".

But my suggestion is to re-enable "asynchronous i/o", which allows the DBWR process to queue up i/o requests without blocking.

Check the documentation again... i think "direct i/o" is not compatible with "async i/o".

I believe the veritas database edition for solaris includes an async driver that bypasses the unix file system buffers.

Good luck.

"NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message news:389c868b.530124_at_news.earthlink.net...
> After reading how forcedirectio would improve Oracle
performace and
> several books, and even some Veritas documentation I have
enabled it
> on all Oracle mounted drives(no more double buffering, etc).
We use
> Veritas Volume Manager, and therefore they're all striped
volumes (and
> mirrored too, RAID 0+1). We have also installed more disks on
one of
> the disk arrays (I had to move files in and out).
>
> All of a sudden our batch job that used to take 7-8 hours
takes 18, so
> I am trying to figure out what's going on, and when top
utility used
> to show on average 40-60% iowait, now it shows 70-90% iowait
even when
> the 'load' is very low (like 1-3). The only weird part is the
output
> of iostat -xtc, the disks don't seem to be that busy, but the
iowait
> is there. I've read in some book that direct i/o works
'sequentially'
> instead of async, but it only mentioned that on Sequent
systems you
> have to use init.ora parameter _direct_read=TRUE. but says
nothing
> about Solaris (Oracle8 and Unix performance tuning pg. 114)
> the oracle parameters say:
>
> async_read boolean TRUE
>
> async_write boolean TRUE
>
> Also, I don't know which sys view this is coming from (I got
it from Q
> Diagnostic, but I get the most for db file sequential read
(something
> like 23mil, not sure if it's bad b/c never checked).
>
> Also there seems to be no problematic SQL.
>
> Also I collect manually sar-u statistics, and they used to
show daily
> avg of 20-30% io-wait, now they how 60+...
>
> Any ideas?
> (server has been up for around 30 hours so far)
>
> Thanx all.
>
> $mount
> / on /dev/vx/dsk/rootvol read/write/setuid/largefiles on Thu
Feb 3
> 22:44:22 2000
> /usr on /dev/vx/dsk/usr read/write/setuid/largefiles on Thu
Feb 3
> 22:44:22 2000
> /proc on /proc read/write/setuid on Thu Feb 3 22:44:22 2000
> /dev/fd on fd read/write/setuid on Thu Feb 3 22:44:22 2000
> /var on /dev/vx/dsk/var read/write/setuid/largefiles on Thu
Feb 3
> 22:44:22 2000
> /opt on /dev/vx/dsk/opt setuid/read/write/largefiles on Thu
Feb 3
> 22:44:45 2000
> /tmp on swap read/write on Thu Feb 3 22:44:45 2000
> /u01 on /dev/vx/dsk/dbgroup/vol1 setuid/read/write/largefiles
on Thu
> Feb 3 22:44:45 2000
> /u01/app/oracle/export on 207.15.160.44:/oracle_backups
soft/bg/remote
> on Thu Feb 3 22:45:02 2000
> /cdrom/oracle8i on /vol/dev/dsk/c1t6d0/oracle8i read
only/nosuid on
> Thu Feb 3 22:45:07 2000
> /u08 on /dev/vx/dsk/dbgroup-2/vol8
> forcedirectio/setuid/read/write/largefiles on Fri Feb 4
02:11:15 2000
> /u02 on /dev/vx/dsk/dbgroup/vol2
> forcedirectio/setuid/read/write/largefiles on Fri Feb 4
02:20:32 2000
> /u03 on /dev/vx/dsk/dbgroup/vol3
> forcedirectio/setuid/read/write/largefiles on Fri Feb 4
02:20:52 2000
> /u04 on /dev/vx/dsk/dbgroup/vol4
> forcedirectio/setuid/read/write/largefiles on Fri Feb 4
02:21:13 2000
> /u05 on /dev/vx/dsk/dbgroup/vol5
> forcedirectio/setuid/read/write/largefiles on Fri Feb 4
02:21:31 2000
> /u06 on /dev/vx/dsk/dbgroup/vol6
> forcedirectio/setuid/read/write/largefiles on Fri Feb 4
02:21:54 2000
> /u07 on /dev/vx/dsk/dbgroup/vol7
> forcedirectio/setuid/read/write/largefiles on Fri Feb 4
02:22:12 2000
> /u10 on /dev/vx/dsk/dbgroup/vol10
> forcedirectio/setuid/read/write/largefiles on Fri Feb 4
02:22:58 2000
> /uredo on /dev/vx/dsk/dbgroup/volredo
> forcedirectio/setuid/read/write/largefiles on Fri Feb 4
02:23:21 2000
> /uredo2 on /dev/vx/dsk/dbgroup/volredo2
> forcedirectio/setuid/read/write/largefiles on Fri Feb 4
02:23:35 2000
> /uredo3 on /dev/vx/dsk/dbgroup/volredo3
> forcedirectio/setuid/read/write/largefiles on Fri Feb 4
02:23:47 2000
> /u09 on /dev/vx/dsk/dbgroup-2/vol9
> forcedirectio/setuid/read/write/largefiles on Fri Feb 4
02:44:52 2000
>
> $iostat -xtc
> device r/s w/s kr/s kw/s wait actv svc_t %w %b tin
tout us
> sy wt id
> sd6 0.0 0.0 0.0 0.0 0.0 0.0 4.3 0 0 0
89 15
> 7 64 13
> sd26 0.2 0.6 11.1 4.3 0.0 0.0 49.0 0 1
> sd27 0.0 0.8 0.4 15.0 0.0 0.1 65.4 0 1
> ssd0 12.1 2.9 39.7 14.0 0.0 0.1 7.0 0 9
> ssd1 0.0 0.0 0.0 0.0 0.0 0.0 1.3 0 0
> ssd2 12.2 3.0 40.0 13.5 0.0 0.1 7.2 0 9
> ssd3 12.3 2.9 40.3 13.3 0.0 0.1 7.1 0 9
> ssd4 0.0 0.0 0.0 0.0 0.0 0.0 2.9 0 0
> ssd5 12.0 2.8 39.5 12.0 0.0 0.1 7.1 0 9
> ssd6 0.0 0.0 0.0 0.0 0.0 0.0 1.1 0 0
> ssd7 12.2 3.2 40.1 18.2 0.0 0.1 6.9 0 9
> ssd8 12.0 3.0 39.5 16.7 0.0 0.1 7.0 0 9
> ssd9 12.0 2.8 39.3 12.0 0.0 0.1 7.1 0 9
> ssd10 12.2 2.9 39.8 13.3 0.0 0.1 7.2 0 9
> ssd11 0.0 6.4 0.0 20.6 0.0 0.0 5.3 0 3
> ssd12 12.0 2.9 39.6 12.0 0.0 0.1 7.1 0 9
> ssd13 11.8 9.3 38.5 32.6 0.0 0.2 8.6 0 14
> ssd14 12.0 2.9 39.0 14.0 0.0 0.1 7.0 0 9
> ssd15 11.8 3.0 38.5 16.7 0.0 0.1 7.0 0 9
> ssd16 12.1 3.0 39.7 13.5 0.0 0.1 7.2 0 9
> ssd17 12.1 3.2 39.4 18.2 0.0 0.1 7.0 0 9
> ssd18 7.2 3.3 89.6 23.8 0.0 0.1 6.1 0 6
> ssd19 7.2 3.3 89.7 23.9 0.0 0.1 6.1 0 6
> ssd20 7.2 3.3 89.7 24.2 0.0 0.1 6.1 0 6
> ssd21 7.2 3.3 89.7 23.8 0.0 0.1 6.1 0 6
> ssd22 7.2 3.3 89.7 23.7 0.0 0.1 6.1 0 6
> ssd23 5.4 4.3 16.2 81.5 0.0 0.1 6.8 0 6
> ssd24 5.7 4.2 28.0 81.4 0.0 0.1 6.5 0 6
> ssd25 5.4 4.3 16.3 81.8 0.0 0.1 6.7 0 6
> ssd26 5.8 4.2 28.2 81.3 0.0 0.1 6.4 0 6
> ssd27 5.8 4.2 28.1 81.3 0.0 0.1 6.5 0 6
> ssd28 0.0 0.0 0.0 0.0 0.0 0.0 4.5 0 0
> ssd29 5.4 4.2 16.2 81.4 0.0 0.1 6.7 0 6
> ssd30 5.3 4.3 16.1 82.3 0.0 0.1 6.8 0 6
> ssd31 6.8 3.3 77.7 23.8 0.0 0.1 6.2 0 6
> ssd32 0.0 0.0 0.0 0.0 0.0 0.0 4.5 0 0
> ssd33 6.8 3.4 77.7 24.7 0.0 0.1 6.3 0 6
> nfs1 2.2 3.3 56.7 105.9 65.5 0.2 11908.1 2 6
> nfs2 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
>
> (don't know about stripe width, but it should be 32)
> $vxprint -S
> Disk group: rootdg
> VOLUMES PLEXES SUBDISKS PLEXFREE SDFREE DISKS
> 5 10 11 0 0 2
>
> Disk group: dbgroup
> VOLUMES PLEXES SUBDISKS PLEXFREE SDFREE DISKS
> 11 22 115 0 0 18
>
> Disk group: dbgroup-2
> VOLUMES PLEXES SUBDISKS PLEXFREE SDFREE DISKS
> 2 4 28 0 0 26
>
> always_anti_join string NESTED_LOOPS
>
> async_read boolean TRUE
>
> async_write boolean TRUE
>
> audit_file_dest string ?/rdbms/audit
> audit_trail string NONE
>
> b_tree_bitmap_plans boolean FALSE
>
> background_core_dump string full
>
> background_dump_dest string
> /u01/app/oracle/admin/HOOP/bdu
> bitmap_merge_area_size integer 1048576
>
> blank_trimming boolean FALSE
>
> cache_size_threshold integer 55000
>
> ccf_io_size integer 131072
>
> checkpoint_process boolean TRUE
>
> cleanup_rollback_entries integer 20
>
> close_cached_open_cursors boolean FALSE
>
> commit_point_strength integer 1
>
> compatible string 7.3.4.0.1
>
> compatible_no_recovery string
>
> control_files string
> /u01/oradata/HOOP/control01.ct
> core_dump_dest string
> /u01/app/oracle/admin/HOOP/cdu
> cpu_count integer 10
>
> create_bitmap_area_size integer 8388608
>
> cursor_space_for_time boolean FALSE
>
> db_block_buffers integer 550000
>
> db_block_checkpoint_batch integer 16
>
> db_block_checksum boolean FALSE
>
> db_block_lru_extended_statistics integer 0
>
> db_block_lru_latches integer 5
>
> db_block_lru_statistics boolean FALSE
>
> db_block_size integer 2048
>
> db_domain string WORLD
>
> db_file_multiblock_read_count integer 32
>
> db_file_simultaneous_writes integer 4
>
> db_file_standby_name_convert string
>
> db_files integer 200
>
> db_name string HOOP
>
> db_writers integer 1
>
> dblink_encrypt_login boolean FALSE
>
> delayed_logging_block_cleanouts boolean TRUE
>
> discrete_transactions_enabled boolean FALSE
>
> distributed_lock_timeout integer 60
>
> distributed_recovery_connection_hol integer 200
>
> distributed_transactions integer 61
>
> dml_locks integer 500
>
> enqueue_resources integer 520
>
> event string
>
> fast_cache_flush boolean FALSE
>
> fixed_date string
>
> gc_db_locks integer 100
>
> gc_files_to_locks string
>
> gc_freelist_groups integer 50
>
> gc_lck_procs integer 1
>
> gc_releasable_locks integer 550000
>
> gc_rollback_locks integer 20
>
> gc_rollback_segments integer 20
>
> gc_save_rollback_locks integer 20
>
> gc_segments integer 10
>
> gc_tablespaces integer 5
>
> global_names boolean TRUE
>
> hash_area_size integer 0
>
> hash_join_enabled boolean TRUE
>
> hash_multiblock_io_count integer 1
>
> ifile file
> /u01/app/oracle/admin/HOOP/pfi
> instance_number integer 0
>
> job_queue_interval integer 60
>
> job_queue_keep_connections boolean FALSE
>
> job_queue_processes integer 2
>
> license_max_sessions integer 0
>
> license_max_users integer 0
>
> license_sessions_warning integer 0
>
> log_archive_buffer_size integer 64
>
> log_archive_buffers integer 4
>
> log_archive_dest string ?/dbs/arch
>
> log_archive_format string %t_%s.dbf
>
> log_archive_start boolean FALSE
>
> log_block_checksum boolean FALSE
>
> log_buffer integer 4194304
>
> log_checkpoint_interval integer 500000
>
> log_checkpoint_timeout integer 0
>
> log_checkpoints_to_alert boolean FALSE
>
> log_file_standby_name_convert string
>
> log_files integer 255
>
> log_simultaneous_copies integer 8
>
> log_small_entry_max_size integer 100
>
> max_commit_propagation_delay integer 90000
>
> max_dump_file_size string 10240
>
> max_enabled_roles integer 20
>
> max_rollback_segments integer 30
>
> max_transaction_branches integer 8
>
> mts_dispatchers string
> (ADDRESS=(PARTIAL=yes)(PROTOCO
> mts_listener_address string
> (ADDRESS=(PROTOCOL=tcp)(host=s
> mts_max_dispatchers integer 10
>
> mts_max_servers integer 20
>
> mts_multiple_listeners boolean FALSE
>
> mts_servers integer 8
>
> mts_service string HOOP
>
> nls_currency string
>
> nls_date_format string
>
> nls_date_language string
>
> nls_iso_currency string
>
> nls_language string AMERICAN
>
> nls_numeric_characters string
>
> nls_sort string
>
> nls_territory string AMERICA
>
> open_cursors integer 100
>
> open_links integer 4
>
> optimizer_mode string CHOOSE
>
> optimizer_parallel_pass boolean TRUE
>
> optimizer_percent_parallel integer 0
>
> optimizer_search_limit integer 5
>
> oracle_trace_collection_name string oracle7
>
> oracle_trace_collection_path string ?/rdbms/log
>
> oracle_trace_collection_size integer 5242880
>
> oracle_trace_enable boolean FALSE
>
> oracle_trace_facility_name string oracle7
>
> oracle_trace_facility_path string ?/rdbms/admin
>
> os_authent_prefix string ops$
>
> os_roles boolean FALSE
>
> parallel_default_max_instances integer 0
>
> parallel_max_servers integer 5
>
> parallel_min_percent integer 0
>
> parallel_min_servers integer 0
>
> parallel_server_idle_time integer 5
>
> partition_view_enabled boolean FALSE
>
> post_wait_device string
/devices/pseudo/pw_at_0:pw
> pre_page_sga boolean FALSE
>
> processes integer 200
>
> recovery_parallelism integer 0
>
> reduce_alarm boolean FALSE
>
> remote_dependencies_mode string timestamp
>
> remote_login_passwordfile string NONE
>
> remote_os_authent boolean FALSE
>
> remote_os_roles boolean FALSE
>
> resource_limit boolean FALSE
>
> rollback_segments string r01, r02, r03, r04
>
> row_cache_cursors integer 10
>
> row_locking string always
>
> sequence_cache_entries integer 30
>
> sequence_cache_hash_buckets integer 23
>
> serializable boolean FALSE
>
> session_cached_cursors integer 0
>
> sessions integer 225
>
> shadow_core_dump string full
>
> shared_pool_reserved_min_alloc integer 5000
>
> shared_pool_reserved_size integer 0
>
> shared_pool_size integer 200000000
>
> snapshot_refresh_interval integer 60
>
> snapshot_refresh_keep_connections boolean FALSE
>
> snapshot_refresh_processes integer 0
>
> sort_area_retained_size integer 1048576
>
> sort_area_size integer 4194304
>
> sort_direct_writes string AUTO
>
> sort_read_fac integer 5
>
> sort_spacemap_size integer 512
>
> sort_write_buffer_size integer 32768
>
> sort_write_buffers integer 2
>
> spin_count integer 2000
>
> sql92_security boolean FALSE
>
> sql_trace boolean FALSE
>
> temporary_table_locks integer 225
>
> text_enable boolean FALSE
>
> thread integer 0
>
> timed_statistics boolean FALSE
>
> transactions integer 247
>
> transactions_per_rollback_segment integer 16
>
> unlimited_rollback_segments boolean TRUE
>
> use_ism boolean TRUE
>
> use_post_wait_driver boolean FALSE
>
> use_readv boolean FALSE
>
> user_dump_dest string
> /u01/app/oracle/admin/HOOP/udu
> utl_file_dir string
>
> v733_plans_enabled boolean FALSE
>
> SQL> select name , value from v$sysstat order by name
> 2 /
>
> NAME VALUE
> ------------------------------ ------------------------
> CPU used by this session 0
> CPU used when call started 0
> CR blocks created 270,336
> Current blocks converted for C 11
> R
>
> DBWR buffers scanned 234,886,521
> DBWR checkpoints 479
> DBWR cross instance writes 0
> DBWR free buffers found 60,173,903
> DBWR lru scans 159,227
> DBWR make free requests 43,383
> DBWR summed scan depth 236,414,623
> DBWR timeouts 27,286
> SQL*Net roundtrips to/from cli 182,617,429
> ent
>
> SQL*Net roundtrips to/from dbl 0
> ink
>
> Unnecesary process cleanup for 0
> SCN batching
>
> background checkpoints complet 99
> ed
>
> background checkpoints started 105
> background timeouts 113,481
> bytes received via SQL*Net fro 13,373,744,087
> m client
>
> bytes received via SQL*Net fro 0
> m dblink
>
> bytes sent via SQL*Net to clie 15,478,541,281
> nt
>
> bytes sent via SQL*Net to dbli 0
> nk
>
> calls to get snapshot scn: kcm 105,977,002
> gss
>
> calls to kcmgas 539,599
> calls to kcmgcs 284,665
> calls to kcmgrs 111,015,863
> change write time 0
> cleanouts and rollbacks - cons 9,713
> istent read gets
>
> cleanouts only - consistent re 211,014
> ad gets
>
> cluster key scan block gets 724,115
> cluster key scans 699,215
> commit cleanout failure: write 0
> disabled
>
> commit cleanout failures: buff 32
> er being written
>
> commit cleanout failures: call 29
> back failure
>
> commit cleanout failures: hot 0
> backup in progress
>
> commit cleanout number success 3,599,067
> fully completed
>
> consistent changes 310,739
> consistent gets 3,387,278,476
> cross instance CR read 0
> cursor authentications 5,085,159
> data blocks consistent reads - 306,794
> undo records applied
>
> db block changes 17,048,586
> db block gets 29,149,565
> deferred (CURRENT) block clean 1,007,220
> out applications
>
> dirty buffers inspected 3
> enqueue conversions 145
> enqueue deadlocks 0
> enqueue releases 1,770,655
> enqueue requests 1,772,680
> enqueue timeouts 1,975
> enqueue waits 264
> exchange deadlocks 509
> execute count 156,461,095
> free buffer inspected 460,017
> free buffer requested 42,726,290
> global lock convert time 0
> global lock converts (async) 0
> global lock converts (non asyn 0
> c)
>
> global lock get time 0
> global lock gets (async) 0
> global lock gets (non async) 0
> global lock release time 0
> global lock releases (async) 0
> global lock releases (non asyn 0
> c)
>
> hash latch wait gets 0
> immediate (CR) block cleanout 220,727
> applications
>
> immediate (CURRENT) block clea 1,045,847
> nout applications
>
> kcmccs called get current scn 0
> kcmgss read scn without going 0
> to DLM
>
> kcmgss waited for batching 0
> logons cumulative 79,917
> logons current 54
> messages received 643,537
> messages sent 643,537
> next scns gotten without going 0
> to DLM
>
> no work - consistent read gets 2,366,149,615
> opened cursors cumulative 6,656,489
> opened cursors current 678
> parse count 36,181,853
> parse time cpu 0
> parse time elapsed 0
> physical reads 40,920,252
> physical writes 2,678,791
> process last non-idle time 0
> recovery array read time 0
> recovery array reads 0
> recovery blocks read 0
> recursive calls 129,098,386
> recursive cpu usage 0
> redo blocks written 4,288,513
> redo buffer allocation retries 186
> redo entries 9,281,675
> redo entries linearized 9,281,675
> redo log space requests 9,723
> redo log space wait time 0
> redo log switch interrupts 0
> redo ordering marks 0
> redo size 2,003,399,954
> redo small copies 2,622,362
> redo synch time 0
> redo synch writes 500,949
> redo wastage 118,320,038
> redo write time 0
> redo writer latching time 0
> redo writes 529,172
> remote instance undo block wri 0
> tes
>
> remote instance undo header wr 0
> ites
>
> remote instance undo requests 0
> rollback changes - undo record 5,472
> s applied
>
> rollbacks only - consistent re 192,322
> ad gets
>
> serializable aborts 0
> session connect time 0
> session cursor cache count 0
> session cursor cache hits 0
> session logical reads 3,415,382,983
> session pga memory 52,925,185,344
> session pga memory max 53,228,320,696
> session stored procedure space 0
> session uga memory 234,490,128
> session uga memory max 6,496,954,976
> sorts (disk) 340
> sorts (memory) 4,116,649
> sorts (rows) 182,954,463
> summed dirty queue length 562,945
> table fetch by rowid 1,513,904,818
> table fetch continued row 42,809,441
> table scan blocks gotten 60,390,532
> table scan rows gotten 612,710,280
> table scans (cache partitions) 0
> table scans (direct read) 0
> table scans (long tables) 748
> table scans (rowid ranges) 0
> table scans (short tables) 4,179,299
> total number commit cleanout c 3,629,150
> alls
>
> transaction lock background ge 0
> t time
>
> transaction lock background ge 0
> ts
>
> transaction lock foreground re 0
> quests
>
> transaction lock foreground wa 0
> it time
>
> transaction rollbacks 50
> transaction tables consistent 21
> read rollbacks
>
> transaction tables consistent 3,797
> reads - undo records applied
>
> user calls 182,619,625
> user commits 495,020
> user rollbacks 47
> write requests 151,251
> ---------------
> In case I forgot to mention:
> We use Oracle 7.3.4 on Solaris 2.6, 2.7 boxes
> ---------------
> Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 750-3630
> AOL: NetComrade ICQ: 11340726 remove NSPAM to email
Received on Sat Feb 05 2000 - 19:58:19 CST