Sun Solaris 9 and Oracle 9i performance issue

Hi, I'm not sure if this is a right place to post this question but I guess the gurus in this forum can lead me to the right place. Here is my problem, I'm trying to import an Oracle dmp file into a 9.2 db running on a Sunfire v880 (4cpu, 8gb ram) w/ 4 FiberChannel disks. The import took 10 hrs to complete and the Disk I/O is pretty bad (write is at 1mb/s).

I was not convinced with the performance on Solaris 9 so we took the same dump file and import that into a 9.2 db running (with identical init parameters w/ even smaller buffer cache) on a windows 2003 server (2CPU, 2 SCSI internal disks) and guess what the import done successfully in only 2hrs ( 500% better).

We discussed w/ Oracle support about our problem and tried all of their tuning recommendations (force directio, separate drive for redo logs..etc) but the performance on Sun Solaris does not improve much. I suspect that there are kernel parameters in Solaris that we can tune to improve this. From the specs, sun v880 server should perform much (like a whole lot better) better than the server running 2003. If you have experience with this situation or have any advice, please feel free to post your comments. Thanks for your help.

in Solaris:
What RAID you use?
Any other DB/application running on the machine?
What is your swap size?

Do you guys still buy Sun boxes?
And do you know when Sun Microsystems will be closed?

Did you do disk burning tests in Sun Server?
What is the max disk IO throughput achieved?

1 Check the cache options for the disks.
2 Post /etc/system file
3 Post init.ora
4 Post import script

Tamil

Quote:

---

Originally posted by tamilselvan
Do you guys still buy Sun boxes?

---

Not me, unless it is a rare occasion where we just want to upgrade the box in place.

Hi all,
We use RAID0 for all oracle mounts, our Unix guys, from their perf test, the disk i/o on v880 is at optimal. Below are the files for our settings. Thanks


here is our /etc/system:
---------------------------------
*ident "@(#)system 1.18 97/06/27 SMI" /* SVR4 1.5 */
*
* SYSTEM SPECIFICATION FILE
*

* moddir:
*
* Set the search path for modules. This has a format similar to the
* csh path variable. If the module isn't found in the first directory
* it tries the second and so on. The default is /kernel /usr/kernel
*
* Example:
* moddir: /kernel /usr/kernel /other/modules



* root device and root filesystem configuration:
*
* The following may be used to override the defaults provided by
* the boot program:
*
* rootfs: Set the filesystem type of the root.
*
* rootdev: Set the root device. This should be a fully
* expanded physical pathname. The default is the
* physical pathname of the device where the boot
* program resides. The physical pathname is
* highly platform and configuration dependent.
*
* Example:
* rootfs:ufs
* rootdev:/sbus@1,f8000000/esp@0,800000/sd@3,0:a
*
* (Swap device configuration should be specified in /etc/vfstab.)



* exclude:
*
* Modules appearing in the moddir path which are NOT to be loaded,
* even if referenced. Note that `exclude' accepts either a module name,
* or a filename which includes the directory.
*
* Examples:
* exclude: win
* exclude: sys/shmsys



* forceload:
*
* Cause these modules to be loaded at boot time, (just before mounting
* the root filesystem) rather than at first reference. Note that
* forceload expects a filename which includes the directory. Also
* note that loading a module does not necessarily imply that it will
* be installed.
*
* Example:
* forceload: drv/foo



* set:
*
* Set an integer variable in the kernel or a module to a new value.
* This facility should be used with caution. See system(4).
*
* Examples:
*
* To set variables in 'unix':
*
* set nautopush=32
* set maxusers=40
*
* To set a variable named 'debug' in the module named 'test_module'
*
* set test_module:debug = 0x13

set eri:adv_autoneg_cap = 0
set eri:adv_100fdx_cap = 1

*** ORACLE SETTINGS *****
set shmsys:shminfo_shmmax=4294967295
set semsys:seminfo_semmni=100
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmns=1024
set semsys:seminfo_semmsl=256
* Begin MDD root info (do not edit)
forceload: misc/md_trans
forceload: misc/md_raid
forceload: misc/md_hotspares
forceload: misc/md_sp
forceload: misc/md_stripe
forceload: misc/md_mirror
forceload: drv/pcisch
forceload: drv/qlc
forceload: drv/fp
forceload: drv/ssd
rootdev:/pseudo/md@0:0,0,blk
* End MDD root info (do not edit)
set fastscan=250000
set handspreadpages=250000
set maxpgio=1024
-----------------

imp.par script:

userid=/
buffer=104857600
log=test.log
full=y
commit=y
analyze=n
ignore=y
file=full_test.dmp
feedback=10000


init file:

*._disable_logging=TRUE
*.aq_tm_processes=1
*.background_dump_dest='/u01/oracle/product/admin/mytest/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/u01/oracle/product/oradata/mytest/control01.ctl','/u01/oracle/product/oradata/mytest/control02.ctl','/u01/oracle/product/oradata/mytest/control03.ctl'
*.core_dump_dest='/u01/oracle/product/admin/mytest/cdump'
*.db_block_size=32768
*.db_cache_size=2097152000
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='apsprd'
*.db_writer_processes=2
*.disk_asynch_io=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mytestXDB)'
*.fast_start_mttr_target=60
*.filesystemio_options='ASYNCH'
*.hash_join_enabled=TRUE
*.instance_name='mytest'
*.java_pool_size=52428800
*.job_queue_processes=10
*.large_pool_size=136314880
*.log_buffer=10485760
*.open_cursors=300
*.pga_aggregate_target=2097152000
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=524288000
*.sort_area_retained_size=0
*.sort_area_size=0
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=60
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/product/admin/mytest/udump'

*._disable_logging=TRUE

funny

Pando, we turned that paramter on to test the import. Won't use it for normal operations though.

So I don't think you answered the question...:

Quote:

---

What is the max disk IO throughput achieved?

---

Here's a stupid question, but you're not importing across a network link are you?
How is your storage system attached to the server?

Brds,

Andy

I think the *.filesystemio_options='ASYNCH' may be wrong. It seems to be a bug in oracle 9i.

Once I had a perf problem when I set this parameter in my init.ora. Every thing ran slow. Then I set it to NONE. The bottleneck was gone.

This is my initial thought. I will see anything can be done in other valeus.

Tamil

Hi Tamil, I changed the filesystemio_options to NONE as suggested and the performance didn't improve.

Hi Oddster, the maximum disk io we get when running oracle import is 1.2mb/s, Unix guys did a regular os file copy (cp) and they got 40mb/s


Okay, here is what we tried yesterday, we move the TEMP tablespace and the redo logfile to /tmp (memory)and rerun the import for one of the biggest tables and the import peformed much better (at least 10x faster, a full table scan now took 2.46 minutes instead of 30+ minutes). This shows that Oracle performance is caused by slow disk I/O (Oracle spends alot of time waiting). Unix guys in our company don't think this is the case. Maybe we need to tune a kernel parameter as well. Can you please advise? Thanks

Quote:

---

Originally posted by newbie
This shows that Oracle performance is caused by slow disk I/O (Oracle spends alot of time waiting).

---

This was a surprise?

Quote:

---

Originally posted by newbie
Unix guys in our company don't think this is the case. Maybe we need to tune a kernel parameter as well. Can you please advise? Thanks

---

Well they got the proof that i/o is the bottle neck what makes them that i/o is not the cause? Ask them to state the fact, written

Before you change any parameter, first do some disk tests to know about the disk throughput ( I would say stress test on the disk system). Use dd command.

===
set maxpgio=1024
===

I think maxpgio is set very low. Change this to 65536.

And run the test. Also during the import, collect system wise statistics using vmstat, iostat, sar etc.

Tamil

I did some research online and found several cases people complain about the internal FibreChannel disk in Sunfire v880. Many seem to point out that the internal FC disks perform well w/o Oracle but as soon as you put Oracle on it..it just crawling...your thoughts?? Thanks

We use Sunfire with EMC disk array. No problem so far.

In general, EMC disk array is superior than Sun's Disk Array in all aspects.

Did you do dd testing?

Tamil

Hi Tamil & everyone, thank you so much for helping me for the last few days, well we just got the official words from Sun; the v880 internal disks are not suitable for Oracle databases due to lack of io caching(it doesn't have one). They recommended to us to hook up the Sunfire v880 box to an external disk array. Thanks

Hi,
we have exctly the same problem with a V280 and oracle installed on internal fibre channel disks. Is there an official statement from sun on the website somewhere or did you got this information by mail/phone?

Quote:

---

Originally posted by tamilselvan
I think the *.filesystemio_options='ASYNCH' may be wrong. It seems to be a bug in oracle 9i.

---

Why a bug?

Why not set filesystemio_options=directio. This will enable direct I/O.

BTW, filesystemio_options=none is the default for AIX.

==========
Why a bug?

Why not set filesystemio_options=directio. This will enable direct I/O.

===============

8 months ago, after we built the IBM p690 server with EMC DMX disk array 2000, I was testing the server. W/o async IO, Oracle cannot be installed on IBM AIX 5.2. I played around the parameter filesystem_options. Since, async IO is enabled on the server, I set this parameter to ASYNC. To my surprise, the system's performance nose dived. When I set it to NONE, I got better performance. This test was conducted on pre-prod env where raw devices are used.

On the JFS, I did the same test with ASYNC value, again the perf went down. But I did not do the test with directio option. I can do it today and post the result.

Tamil

I did a test on JFS with filesystemio_options=directio. The perf is same. No diff at all.

Tamil