Oracle Database running slow on ZFS Solaris
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Oracle Database running slow on ZFS Solaris

  1. #1
    Join Date
    Sep 2004
    Posts
    54

    Oracle Database running slow on ZFS Solaris

    Hi All,

    We recently moved our oracle database (ver 9.2.0.8, Solaris 9, UFS ) to a new T5120 Sun Server.

    Since moving to ZFS oracle queries are running very slow (almost 50% slow).

    I have tried to change a oracle parameters but still no improvement.

    I have created 8K files system for oracle database files and indexes as described on a forum.

    The query is (a simple select ) taking very long time.

    Timing:

    real 2m20.192s
    user 1m52.123s
    sys 0m22.978s


    Could someone, please advise where else to look at.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by avion1 View Post
    The query is (a simple select ) taking very long time.

    Timing:

    real 2m20.192s
    user 1m52.123s
    sys 0m22.978s


    Could someone, please advise where else to look at.
    Provided information is out of context and really means nothing.

    Have you compared execution plans? old server vs. new server?
    Does the technology refresh includes new storage subsystem?
    Have you compared wait events? old server vs. new server?
    Are database setup identical? old server vs. new server?
    Have you moved to an upper version of Oracle during the technology refresh?
    Would you mind in describing "how" you moved the database to the new server?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Sep 2004
    Posts
    54
    Hi Paul

    thanks your reply.

    I created the database from scratch and then imported all the data using oracle import.

    All the hit ratio are very good

    Library Cache Data Dictionary Cache Buffer Cache Hit Ratio
    99.92 99.57 80.64
    99.91 99.56 80.21

    A simple querty (select code,sum(1) from customers ....)

    taking 3 time more time on the new server.

    Is there any parameter required for ZFS for improve performance.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I'm seeing you have answered none but the last question.

    Let me add a new one to the list... how init.ora files compare?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Sep 2004
    Posts
    54

    Wink

    Have you compared execution plans? old server vs. new server?

    Yes, I have compared both execution plan and the are identicals.

    oh the new server, plan is
    Rows Plan
    ------------ ----------------------------------------------------------------------
    30 SELECT STATEMENT
    30 SORT GROUP BY
    36,029,373 INDEX FAST FULL SCAN IDX3_WM_JOB_HEADER_NARR

    old the old server 30
    SELECT STATEMENT

    30
    SORT GROUP BY

    34,803,182
    INDEX FAST FULL SCAN IDX3_WM_JOB_HEADER_NARR

    -----






    Does the technology refresh includes new storage subsystem?
    Have you compared wait events? old server vs. new server?
    Are database setup identical? old server vs. new server?
    New Server
    ------------
    SQL*Plus: Release 9.2.0.8.0 - Production on Mon Oct 10 09:13:44 2011

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


    new serverSQL*Plus: Release 9.2.0.8.0 - Production on Mon Oct 10 09:13:44 2011

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer RSQL*Plus: Release 9.2.0.8.0 - Production on Mon Oct 10 09:17:37 2011

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


    Connected to:
    Oracle9i Release 9.2.0.8.0 - Production
    JServer Release 9.2.0.8.0 - Production
    elease 9.2.0.8.0 - Production


    ----------




    Have you moved to an upper version of Oracle during the technology refresh?

    No, we are still on the same verion and same patch no.

    By mistake, We have installed Enterprise version on the new server.

    Would you mind in describing "how" you moved the database to the new server?

    We exported the full data using oracle export import.

    Parameters Files:


    Old Server

    _pga_max_size big integer 1677721600
    cpu_count integer 4
    db_cache_size big integer 1056964608

    db_writer_processes integer 1

    db_file_multiblock_read_count integer 8
    java_pool_size big integer 16777216
    pga_aggregate_target big integer 2097152000


    New Server

    cpu_count integer 32
    db_cache_size big integer 1174405120
    db_file_multiblock_read_count integer 128
    db_writer_processes integer 4
    java_pool_size big integer 33554432
    pga_aggregate_target big integer 1048576000

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by avion1 View Post
    Have you compared execution plans? old server vs. new server?

    Yes, I have compared both execution plan and the are identicals.
    Please do a 10046 trace of the offending query, both on the old and new server.
    Check how wait events compare.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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