Performance Tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Performance Tuning

  1. #1
    Join Date
    May 2009
    Posts
    32

    Performance Tuning

    Hi,

    I am looking for testing the production database on my close test database. I started exporting the statistics from my production database and imported them to my test database. And now I am looking to go forward to do the performance testing on my test database. I need your expertise in the following scenarios.
    1) How to generate large no of user's work load?
    2) How to test the queries are using optimal execution plans.
    3) As my test Database is smaller than Prod, how can I ensure Test performance will also be optimal on PROD.

    Thanks in advance for your help.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Thomas7 View Post
    1) How to generate large no of user's work load?
    Look for a workload generator/simulator. Plenty of offer.

    Quote Originally Posted by Thomas7 View Post
    2) How to test the queries are using optimal execution plans.
    Looking at them, one at a time.

    Quote Originally Posted by Thomas7 View Post
    3) As my test Database is smaller than Prod, how can I ensure Test performance will also be optimal on PROD.
    You can't.
    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
    May 2009
    Posts
    32
    Thanks for your advice sir. How often is it recommended to gather the statistics on OLTP and OLAP databases?.

    Thanks in Advance for your help.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    As a rule of thumbs there is no reason to gather fresh stats if number of rows AND data distribution has changed less than 20%

    In the case of tables which number of rows goes up and down -like staging tables - as far as data distribution is similar you may want to gather fresh stats when table is loaded to its max size and stick with it.

    Gathering stats for no good reason only leads to problems - chances are Oracle will be changing execution plans as statistics change therfore database would not show a consistent performance.

    Bottom line, if queries are consistently performing well - don't touch statistics.
    Last edited by PAVB; 09-22-2009 at 02:16 PM.
    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
    May 2009
    Posts
    32
    Thanks for your valuable advice sir. It made me just clear to how often the statistics should be gathered.

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