2 Replies Latest reply: Oct 8, 2012 11:55 PM by 872581 RSS

    Gathering satistics with cascade option is slow

    872581
      Hi, all.

      The database is 11.2.0.3 on a linux machine.

      I issued the following command, but the session was a little slow.

      The table size is about 50 GB and has 3 indexes.

      I specified "degree=8" for parallel processing.

      When gathering statistics on the table , parallel slaves were invoked and gathering statistics on the table has finished fast enough.

      However, when it goes to gathering statistics on the indexes, only one active session was invocked, and thus "degree=8" option was ignored.

      My question is :

      Do I need to use dbms_stats.gahter_index_stats instead of "cascade" option in order to gather statistic on the indexes with parallelism?

      exec dbms_stats.gather_table_stats(ownname=>'SDPSTGOUT',tabname=>'OUT_SDP_CONTACT_HIS',estimate_percent=>10, degree=>8 , method_opt=>'FOR ALL COLUMNS SIZE 1',Granularity=>'ALL',cascade=>TRUE);
      Thanks in advance.

      Best Regards.
        • 1. Re: Gathering satistics with cascade option is slow
          Nikolay Savvinov
          Hi,

          this might be happening because of indexes being created as NOPARALLEL. Try to re-define them with DOP=8 and see if this helps (run a quick test to check this before doing any expensive DDLs).

          Best regards,
          Nikolay
          • 2. Re: Gathering satistics with cascade option is slow
            872581
            I solved this issue.

            The following is from oracle manual

            cascade
            Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default. The default value can be changed using theSET_PARAM Procedure.

            I issued the following sqls and it was successful.
            exec dbms_stats.set_param('DEGREE','8');
            
            exec dbms_stats.gather_table_stats(ownname=>'SDPSTGOUT',tabname=>'OUT_SDP_CONTACT_HIS',estimate_percent=>10, degree=>8 , method_opt=>'FOR ALL COLUMNS SIZE 1',Granularity=>'ALL',cascade=>TRUE);
            Thanks.
            Have a good day.

            Best Regards.