DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Analyze Schema/table

  1. #1
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    374

    Analyze Schema/table

    Hi All,

    We have a database with high DML transactions( select, insert, update, delete).
    We run exec dbms_stats.gather_schema_stats(ownname=>'schema_name',estimate percent=>20 cascade=>true) every night Monday to Friday.

    Is there any drawback in doing it that way?
    Last edited by Ablakios; 08-18-2003 at 09:03 PM.
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    nope, but what you might like to do is turn on monitoring for the tables, and let DBMS_STATS decide which tables to analyze. You also might like to schedule a computed analysis on the weekend, or whenever the quiet time is on the database.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I think function based indexes are NOT analyzed in gather_stats.
    Just verify it one more time.

  4. #4
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    374
    Slimdave
    I think I like the idea of turning monitoring for the tables with the dbms_stats.
    thanks
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan
    I think function based indexes are NOT analyzed in gather_stats.
    Just verify it one more time.
    There's a bug in some versions that crashes dbms_stats when it encounters a function-based index. you'd have to see metalink for details
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    I've also encountered a bug in dbms_stats(gather_stale). It sometimes decides not to analyze all of the stale objects. No error, it just quits. It also seems completely random when it does this. It's not a terrible big deal, I just wrote a loop in my script to keep running it until there are no more stale objects.

    I would still recommend using the Monitoring option, even with this bug.

    HTH.
    Jodie

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