Sun Solaris 9 and Oracle 9i performance issue
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Sun Solaris 9 and Oracle 9i performance issue

  1. #1
    Join Date
    Mar 2001
    Posts
    149

    Angry 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.

  2. #2
    Join Date
    Nov 2001
    Location
    Planet Earth
    Posts
    116
    in Solaris:
    What RAID you use?
    Any other DB/application running on the machine?
    What is your swap size?
    The man called Zorro

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Mar 2001
    Posts
    149
    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'

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    *._disable_logging=TRUE

    funny

  7. #7
    Join Date
    Mar 2001
    Posts
    149
    Pando, we turned that paramter on to test the import. Won't use it for normal operations though.

  8. #8
    Join Date
    Feb 2001
    Location
    Adelaide, Australia
    Posts
    159
    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

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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
    Last edited by tamilselvan; 06-11-2004 at 11:27 AM.

  10. #10
    Join Date
    Mar 2001
    Posts
    149
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width