| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> forcedirectio on; more disks, performance degrading? (Solaris, Veritas VM, Oracle 7)
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 11R
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,429ent
SQL*Net roundtrips to/from dbl 0ink
Unnecesary process cleanup for 0SCN batching
background checkpoints complet 99ed
background checkpoints started 105 background timeouts 113,481 bytes received via SQL*Net fro 13,373,744,087m client
bytes received via SQL*Net fro 0m dblink
bytes sent via SQL*Net to clie 15,478,541,281 nt
bytes sent via SQL*Net to dbli 0nk
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,713istent read gets
cleanouts only - consistent re 211,014ad gets
cluster key scan block gets 724,115 cluster key scans 699,215 commit cleanout failure: write 0disabled
commit cleanout failures: buff 32er being written
commit cleanout failures: call 29back failure
commit cleanout failures: hot 0backup in progress
commit cleanout number success 3,599,067fully 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,794undo records applied
db block changes 17,048,586 db block gets 29,149,565 deferred (CURRENT) block clean 1,007,220out 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 0c)
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 0c)
hash latch wait gets 0 immediate (CR) block cleanout 220,727applications
immediate (CURRENT) block clea 1,045,847nout applications
kcmccs called get current scn 0 kcmgss read scn without going 0to 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 0to 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 0tes
remote instance undo header wr 0ites
remote instance undo requests 0 rollback changes - undo record 5,472s applied
rollbacks only - consistent re 192,322ad 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,150alls
transaction lock background ge 0t time
transaction lock background ge 0ts
transaction lock foreground re 0quests
transaction lock foreground wa 0it time
transaction rollbacks 50 transaction tables consistent 21read rollbacks
transaction tables consistent 3,797reads - undo records applied
user calls 182,619,625 user commits 495,020 user rollbacks 47 write requests 151,251
Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 750-3630 AOL: NetComrade ICQ: 11340726 remove NSPAM to emailReceived on Sat Feb 05 2000 - 16:23:41 CST
 |
 |