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.
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
* 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
* 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.
* (Swap device configuration should be specified in /etc/vfstab.)
* 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.
* exclude: win
* exclude: sys/shmsys
* 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.
* forceload: drv/foo
* Set an integer variable in the kernel or a module to a new value.
* This facility should be used with caution. See system(4).
* 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 *****
* Begin MDD root info (do not edit)
* End MDD root info (do not edit)
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