Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: last_analyzed column not updated after analyze and dbms_stats collection

    We have a large data warehouse environment running on Oracle 8i and Solaris 9.

    I have a weird problem after I run an analyze and dbms_stats.gather_schema_stats
    for our databases. When I query the dba_tables view for the last_analyzed column it does not show that the statistics have been updated. However, when I query the dba_tab_partitions and dba_ind_partitions views the last_analyzed column is shown as updated after the analyze and dbms_stats collection process for the cost based optimizer. What can I do to ensure that the statistics are accurate and up to date?
    We use range partitioning with our environment.

    analyze script:
    ================

    ANALYZE TABLE dw.ft_inventory partition inv_part1 estimate statistics;
    ANALYZE INDEX dw.pk_inv_idx1 partition inv_part1 estimate statistics;


    dbms_stats script:
    =========================
    exec dbms_stats.delete_schema_stats('DW');
    exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'DW',estim ate_percent=>5, - method_opt=>'FOR ALL COLUMNS SIZE 1',GRANULARITY=>'DEFAULT',degree=>8, - cascade=>true,options=>'GATHER');

    Thanks
    Scott

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    I am not sure why that field is not getting updated.
    However, these are the parameters I use and the field gets updated:
    PHP Code:
    begin

    DBMS_STATS
    .GATHER_SCHEMA_STATS (
       
    ownname          => 'USERNAME',
       
    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 ...

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If you set granularity to GLOBAL it will force it to collect global stats on partitioned tables.

    Alan

Posting Permissions

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