DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: dbms_stats.gather_schema_stats

  1. #1
    Join Date
    Jun 2007
    Posts
    11

    dbms_stats.gather_schema_stats

    hi,
    What is the scenario , we require to issue
    EXEC dbms_stats.gather_schema_stats(’SCOTT’, cascade=>TRUE);

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you are gathering stats on the scott schema

  3. #3
    Join Date
    Jun 2007
    Posts
    11
    On what case, usualy you have do this step ?Or can we do this pro actively?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    You do that when you find out SCOTT owned objects have outdated or no longer representative performance statistics
    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
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by gkrishn
    On what case, usualy you have do this step ?Or can we do this pro actively?
    oh boy ... you want to ask Jenn about it in this very same forum
    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.

  6. #6
    Join Date
    Jun 2007
    Posts
    11
    My users are archiving many old datas.
    Would it be useful executing this step after archival is complete ?
    How do i know if i have to execute EXEC dbms_stats.gather_schema_stats or not?
    Can i run this in business hours ?
    ANy issue in over writing old statistics.

    I am new and with lot of concern on how to proceed.any suggestions will be helpful

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Please read http://www.dba-oracle.com/concepts/t...statistics.htm so you can grasp at least the basic concept.

    Rule of thumbs #1
    Do nothing when less than 20% of your data has changed.

    Rule of thumbs #2
    If your database is very small (under 20 Gig) many people just schedules a weekly job to take care of gathering statistics, most likely during the weekend.

    Hope this helps.
    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.

  8. #8
    Join Date
    Jun 2007
    Posts
    11
    Very much . Thank you!!!

  9. #9
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349

    Arrow

    Gathering schema stats takes long time depending on how much is the schema size and what hour of the day you are doing this. At what frequency and what sample percent is again a debatable issue. But i think you may try doing it with 25% sample and weekly basis.
    http://www.perf-engg.com
    A performance engineering forum

  10. #10
    Join Date
    Jun 2007
    Posts
    11
    Quote Originally Posted by malay_biswal
    At what frequency and what sample percent is again a debatable issue.
    Frequency - You mean to say,if its done once,next run will be faster ?eg:weekly basis

    Table-Gathering statistics for a table,will touch its indexes also ?or need to do it seperatly.

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