Results 1 to 8 of 8

Thread: Analyze table

  1. #1
    Join Date
    Aug 2003
    Posts
    123

    Unanswered: Analyze table

    What is the use of analyze tabel,index
    when I should I do this.
    some time it takes lot of time to analyze table

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Analyze table

    Analyze builds statistics about the objects(table, index, cluster, etc) and stores it in the data dictionary. These are used by the optimizer when querying, often increasing the speed of the query.

    As to the frequency of the analyze, it depends on the amount of change to the data. We happen to run an analyze nightly on tables that change frequently. Others that do not change frequently, we run an analyze weekly.


    Originally posted by jaggu
    What is the use of analyze tabel,index
    when I should I do this.
    some time it takes lot of time to analyze table

  3. #3
    Join Date
    Aug 2003
    Posts
    123

    Re: Analyze table

    Thanks a lot.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    use dbms_stats instead.
    much easier and it analyzes much quicker.
    PLUS, it works with partitioned tables while 'analyze table compute statistics' does not work with partitioning.

    to analyze a whole schema use:
    EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('schema_name');
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by The_Duck
    use dbms_stats instead.
    much easier and it analyzes much quicker.
    PLUS, it works with partitioned tables while 'analyze table compute statistics' does not work with partitioning.

    to analyze a whole schema use:
    EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('schema_name');
    In 8i you do not want to analyse the SYS schema. This is bad juju. Possibly not in 9i as well (we're on 8i so I have not tested on 9i).
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    i was just referring to non-system/sys schemas.
    not the sys schema.

    good to point that out however.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by The_Duck
    i was just referring to non-system/sys schemas.
    not the sys schema.

    good to point that out however.
    I got burned on that real bad. It was taking hours to do my export . I had a "minor" patch to my apps software from the vendor. I finally got frustrated and started applying the patch before the export was done.

    The patch went totally FUBAR. The last good dump I had was three days out of date. My accounting dept cursed me for a month .

    That is how I found out about the sys/sytem analyze schema bug.

    Just don't want anyone else to have like I did.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  8. #8
    Join Date
    Jul 2003
    Posts
    15
    Another thing you can do is turn on monitoring on the tables that you need analyzed, eg:

    alter table scott.emp monitoring;

    and then use

    dbms_stats.gather_database_stats( cascade => true, options => 'GATHER STALE' );

    This will only analyze tables that have had more than 10% of their records changed since the last analyze. This keeps you from wasting time and cpu analyzing tables that haven't changed much since the last analyze. As long as you DON'T turn on monitoring on any sys or system tables then you won't have to worry about them.

Posting Permissions

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