This discussion is archived
2 Replies Latest reply: Oct 8, 2012 9:55 PM by 872581 RSS

Gathering satistics with cascade option is slow

872581 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points