Sun Solaris 9 and Oracle 9i performance issue

[newbie]

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.

[zainalj]

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

[tamilselvan]

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

[marist89]

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.

[newbie]

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'

[pando]

*._disable_logging=TRUE

funny

[newbie]

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

[oddster]

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

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

[tamilselvan]

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

[newbie]

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