Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: DBMS_STATS basics

    Being new to Oracle, I am having a time going through the documentation on DBMS_STATS. For now, can someone tell me how to update the dictionary information that the cost-based optimzer uses to determine how to access table data according to the distribution of data in indexed columns?

    In Sybase, all I ever did:
    update statistics [tablename]

    Just looking for the most basic command I can run after loading a table. In time, I'll take a class or get more expereience to better understand the complexity.

    Thanks,
    Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    go to http://asktom.oracle.com
    do a keyword search on "DBMS_STATS"
    The read & learn what to do.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Quote Originally Posted by chuck_forbes
    Just looking for [b]the most basic command[.b] I can run after loading a table. In time, I'll take a class or get more expereience to better understand the complexity.
    For the most basic, look into the analyze command:

    Code:
    ANALYZE TABLE ... COMPUTE STATISTICS...;
    Otherwise continue to study the DBMS_STATS package.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    I was told by the documentation to avoid 'analyze table', as it's current purpose is to only support backwards compatibility. If I used it until I better understood DBMS_STATS, would I have a problem later?

    I am willing to study this package in detail, but at this time I am learning about backup & recovery. I'm just looking to better the performance of queries in development by updating these statistics. I had a recommendation that the following would be appropriate:

    declare
    begin
    dbms_stats.gather_schema_stats(
    ownname=> 'SCOTT',
    estimate_percent=> 60,
    method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',
    cascade => true);
    end

    Could this suffice as an interim fix for now?

    -Chuck

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    YES, Try it.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Thanks, and last question:

    Why would both "method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1' " and "cascade => true" be present simultaneously?

    From the documentation, it seems like they perform the same function, gathering statistics on the indexes.

    -cf

  7. #7
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    There are two different things:

    - FOR ALL INDEXED COLUMNS will analyze only one index and gather statistics for all indexed columns in this index

    - CASCADE will gather statistics on all indexes on this table, not just the index mentioned


    HTH,

    clio_usa - OCP 8/8i/9i DBA

Posting Permissions

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