0

It was taking hours to analyze the partitions. Are there any commands to parallelly analyze the partitions of a table?

Burhan Ali
  • 2,258
  • 1
  • 28
  • 38
user1403174
  • 27
  • 2
  • 12

1 Answers1

1

If you're using DBMS_STATS to gather your statistics you've got a couple options. If you're making explicit calls to DBMS_STATS.GATHER_TABLE_STATS and DBMS_STATS.GATHER_INDEX_STATS you can use the degree parameter to set the degree of parallelism. You can also set a default by using the various SET_*_PREFS routines in DBMS_STATS to set a default degree of parallelism, by object type or for the entire database (SET_DATABASE_PREFS). For example

DBMS_STATS.SET_DATABASE_PREFS('DEGREE', '10');

will set a default of 10 for degree in subsequent calls to DBMS_STATS.GATHER_TABLE_STATS. Or you can just use the degree parameter to GATHER_TABLE_STATS, as in

DBMS_STATS.GATHER_TABLE_STATS(ownname => 'A_SCHEMA',
                              tabname => 'A_TABLE',
                              degree  => 10);

If you're using ANALYZE to analyze your tables - well, you probably should change over to DBMS_STATS as ANALYZE has been obsolete since Oracle 9, if I recall correctly. The DBMS_STATS package has lots of routines with lots of parameters but most parameters have reasonable defaults and so don't need to be specified.

You might try re-posting this question on dba.stackexchange.com

Share and enjoy.

Korhan Ozturk
  • 11,148
  • 6
  • 36
  • 49
Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
  • hey @BobJarvis since you have over 3k rep, you can vote to close/migrate a question - juck click on close -> Offtopic -> dba.stackexchange.com - that'll migrate the questions & all answers to [dba.se] instead of OP having to repost. – Sathyajith Bhat Jun 15 '12 at 12:11
  • @Sathya - in this case I decided not cast a close & migrate vote because the OP is a relatively new user and I didn't want to discourage him/her from posting or confuse him/her by having the question suddenly vanish. YMMV. – Bob Jarvis - Слава Україні Jun 15 '12 at 12:52
  • alright, but once a question is migrated, there will be an auto-redirect to the migrated site. Just thought I'll let you know. Cheers – Sathyajith Bhat Jun 15 '12 at 13:58