Issue: Slow db data access. Oracle 7.3.4
OS: Sol8 +current patches
Hardware:
Model: E3600
CPU: (4) 336 US-II
Memory: 2048
Disks Arrays: (2) D1000 (each on its own io card)
Drives: (5/per disk array)Cheetah 9LP
Capacity:9.1 GB
Speed:10000 rpm
Average Read Time: 5.4ms
FS: UFS
Raid: 0+1
Ok to break the disks down this is how it is layed out:
# Creates one stripe across 4 disks with 128k stripe width
metainit d101 1 4 c1t1d0s0 c1t2d0s0 c1t8d0s0 c1t9d0s0 -i 128k
# Creates one stripe across 4 disks with 128k stripe width
metainit d102 1 4 c2t1d0s0 c2t2d0s0 c2t8d0s0 c2t9d0s0 -i 128k
# Mirrors the newly created striped volumes
metainit d100 -m d101 d102
# Creates a filesystem of 70652925 blocks (the entire volume) on the new volume
mkfs -F ufs /dev/md/rdsk/d100 70652925
# Add to /etc/vfstab
/dev/md/dsk/d100 /dev/md/rdsk/d100 /lv01 ufs 2 yes la
rgefiles,logging
There is never any waite time on the d101 or d102 but the wait time comes from the mirror which i think is slowing down the db r/w. Has this happend to anyone? Any ideas on how i can implament mirroring a bit more optimized or any alternatives to disk redundency w/ the current hardware? I think the huge amount of faults is from the r/w to d100. the avg amount of SYS faults is about 200-300 when the db is turned off. any help would be appricated.
Be aware your version of Oracle isn't supported anymore (except with an extened contract) and you won't be able to use it if you upgrade to Solaris 9 ( Compatibility matrix ).
Another factor may be your choice of DiskSuite over Veritas. Only opinion but I've never seen Oracle without Veritas on Sun - it's just not worth it when dealing with Oracle.
Can you post your /etc/system file? There should be changes made for the Oracle processes.
You should also be looking at the Oracle log files for errors - it may be that there are changes on the Oracle side that really need to be done. Also, what exactly is on the one disk that you are showing as having a problem? How many different databases are there (on that disk and all)? Are the logging files on different drives than the database(s)? Who is running what inside Oracle when you see these problems (sorts inside Oracle will kill a server)?
set shmsys:shminfo_shmmax=2147483648
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmns=830
set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=210
set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767
* Begin MDD root info (do not edit)
forceload: misc/md_trans
forceload: misc/md_raid
forceload: misc/md_hotspares
forceload: misc/md_stripe
forceload: misc/md_mirror
forceload: drv/sbus
forceload: drv/isp
forceload: drv/sd
rootdev:/pseudo/md@0:0,12,blk
* End MDD root info (do not edit)
* Begin MDD database info (do not edit)
set md:mddb_bootlist1="sd:14:16 sd:14:1050 sd:14:2084 sd:22:16 sd:22:1050"
set md:mddb_bootlist2="sd:22:2084 sd:62:16 sd:62:1050 sd:62:2084 sd:70:16"
set md:mddb_bootlist3="sd:70:1050 sd:70:2084 sd:6:16 sd:6:1050 sd:126:16"
set md:mddb_bootlist4="sd:126:1050 sd:134:16 sd:134:1050 sd:134:2084"
set md:mddb_bootlist5="sd:142:16 sd:142:1050 sd:142:2084 sd:182:16"
set md:mddb_bootlist6="sd:182:1050 sd:182:2084 sd:190:16 sd:190:1050"
set md:mddb_bootlist7="sd:190:2084"
* End MDD database info (do not edit)
unfortunetly our oracle dba is pretty demanding that it is not the db but the system.
the dba said he is not seeing any errors in his logs.
there is only 1 db running. all users connect remotely thru a software package (i2 which is a transportaion software suite)
i cant disagree but i cant agree either.
i truly wish i could just test differant parm's but alas it is a prod box that we were forced to use the current versions of software. and hardware does not look like a fesable idea in my future.
How long has this server been set up this way (current versions of OS and Oracle)?
Have you had sar collecting information on the server before this?
Were there any old statitics that you could refer to - compare original to what you are seeing now (whether it be from sar or xxstat commands) - if you can't compare you have a harder time showing what is really wrong.
Post the init.ora file (should be able to find it in a directory below $ORACLE_HOME). I'll ask our DBA (who does work with us on problems here) what he thinks of all of this and what else you can look for to see if you have to put your DBA in his/her place.
For you to fix the problem you need to know what you can look at to help solve it (from both the OS and Oracle sides).
The following lines are those that the DBA ask me to put
in a box with 4 Gb of RAM:
set maxusers=2048
set msgsys:msginfo_msgmax=16384
set msgsys:msginfo_msgmnb=16384
set msgsys:msginfo_msgmni=2200
set msgsys:msginfo_msgtql=2500
set semsys:seminfo_semmni=100
set semsys:seminfo_semmns=2500
set semsys:seminfo_semmnu=2500
set semsys:seminfo_semmsl=300
set semsys:seminfo_semopm=100
set semsys:seminfo_semume=2500
set shmsys:shminfo_shmmax=3865470566
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmseg=1024
Optimus_P, I wasn't asking for the versions - I know you posted them - I was asking how long the server has been set up this way (sorry about the misunderstanding).
And if Oracle isn't set up properly or the multitude of variables that an Oracle DBA could change to effect performance (for both good and bad results) could cause this.
You really need to be able to work with the DBA - or learn enough about Oracle to be able to say for certain it isn't on that side.
The machine has been setup and running for about two weeks.
Unfortunetly here at my work. they dont understand the importance of testing or a well planned upgrade stratigy.
These are my 4 steps to do over the weekend w/ this machine as far as fs tuening goes.
1) add the following entries to the /etc/system file (to enable larger cluster sizes then 128k)
*
* Allow larger SCSI I/O transfers, parameter is bytes (i can prolly scale this down to 512)
*
set maxphys = 1048576
*
* Allow larger DiskSuite I/O transfers, parameter is bytes (i can prolly scale this down to 512)
*
set md_maxphys = 1048576
2) set new cluster size to 512 for the FS (/dev/md/dsk/d100)
#RAID level 0, striping - Cluster size = number of stripe members x interlace size
tunefs -a 64 /dev/md/dsk/d100
3) set the ufs write throttle
*
* ufs_LW = 1/128th of memory
* ufs_HW = 1/64th of memory
*
set ufs_LW=16777216
set ufs_HW=33554432
4) disable fs caching edit /etc/vfstab and add option
nologging,forcedirectio
Here is the init.ora file
db_name = DEFAULT
db_file_multiblock_read_count = 8 # SMALL
db_block_buffers = 60 # SMALL
shared_pool_size = 3500000 # SMALL
log_checkpoint_interval = 10000
processes = 50 # SMALL
dml_locks = 100 # SMALL
log_buffer = 8192 # SMALL
sequence_cache_entries = 10 # SMALL
sequence_cache_hash_buckets = 10 # SMALL
max_dump_file_size = 10240 # limit trace file size to 5 Meg each
# Global Naming -- enforce that a dblink has same name as the db it connects to
global_names = TRUE
# Edit and uncomment the following line to provide the suffix that will be
# appended to the db_name parameter (separated with a dot) and stored as the
# global database name when a database is created. If your site uses
# Internet Domain names for e-mail, then the part of your e-mail address after
# the '@' is a good candidate for this parameter value.
# db_domain = us.acme.com # global database name is db_name.db_domain
# FOR DEVELOPMENT ONLY, DEFAULT TO SINGLE-PROCESS
# single_process = TRUE
# FOR DEVELOPMENT ONLY, ALWAYS TRY TO USE SYSTEM BACKING STORE
# vms_sga_use_gblpagfil = TRUE
# FOR BETA RELEASE ONLY. Enable debugging modes. Note that these can
# adversely affect performance. On some non-VMS ports the db_block_cache_*
# debugging modes have a severe effect on performance.
_db_block_cache_protect = true # memory protect buffers
event = "10210 trace name context forever, level 2" # data block checking
event = "10211 trace name context forever, level 2" # index block checking
event = "10235 trace name context forever, level 1" # memory heap checking
event = "10049 trace name context forever, level 2" # memory protect cursors
# define two control files by default
control_files = (ora_control1, ora_control2)
I looked at the init.ora parameters. I dont think that the init.ora is set optimally. It seems that the DBA is bluffing because these parms are out of the box....
he needs to do some performance tuning on his side...
Have you ever tried to set the ASYNC_WRITE=TRUE and
ASYNC_READ=TRUE parameters in your init.ora file?
On solaris it is supported on both Filesystems and RAW devices.