4 Replies Latest reply on Apr 25, 2014 4:03 PM by Iordan Iotzov

    Stats Gathering in Dataware housing Environment

    user13549752

      DB:10.2.0.3
      OS:AIX

       

      We have a dataware housing database of size 6TB. The auto stats gathering job is disabled. We have very large size tables of size more than 300GB+. We are generally gathering the stats of some important tables with below option.

       

      execute dbms_stats.gather_table_stats(ownname=> 'OIDADMIN',tabname=>'XX_PORTDETAIL',estimate_percent=>20,method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1',block_sample=>FALSE,degree=>16,granularity=>'ALL',cascade=>TRUE);

       

       

      What i know the above option only gather stats of columns which are involved in index which is not always good in Dataware housing. If we go with default option In 10g and newer METHOD_OPT defaults to ‘FOR ALL COLUMNS SIZE AUTO’ is will take long time. Also it will take long time if we go with option "FOR ALL COLUMNS SIZE 1". Also i gone through the below blog post but there is no specific thing about dataware housing database with large size tables.


      Could you please provide some better option or the one which we are using is fine.

       

      DBMS_STATS, METHOD_OPT and FOR ALL INDEXED COLUMNS | Structured Data