Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Unanswered: Analyze or DBMS_STATS

    Hello Friends

    According to Oracle documentation, The Package DBMS_STATS is a replacement of the ANALYZE command and is recommended method for ORACLE 9I.But at my workplace I m coming acrosss scripts which first analyze the table using -

    ANALYZE TABLE SMT_SB_PR27330_1 COMPUTE STATISTICS;

    ANALYZE TABLE SMT_SB_PR27330_1 COMPUTE STATISTICS FOR ALL INDEXES;


    and then use DBMS_STATS using

    execute DBMS_STATS.GATHER_TABLE_STATS('WR10306','SMT_SB_PR 27330_1');

    Is it really solving any pupose ? Does 1 of two methods is not sufficient ..
    And I believe DBMS_STATS is the one whihc we should be using to gather the Stats in Oracle 9i.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I would have thought dbms_stats should be sufficient but by default it wont gather index stats. I would suggest it might be easier to use GATHER_SCHEMA_STATS (or DATABASE) with the appropriate parameters.

    Probably best to try it out on your test system first to make sure it doesnt screw things up.

    NOTE there are various bugs in different versions of dbms_stats so it is possible someone did it to overcome one of these.

    Alan

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    use dbms_stats

    example:
    PHP Code:
    begin

    DBMS_STATS
    .GATHER_SCHEMA_STATS (
       
    ownname          => 'schema_name',
       
    estimate_percent => 25,
       
    method_opt       => 'FOR ALL INDEXES',
       
    cascade          => TRUE);
    end;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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