CODEWITHSUNDEEP
oracle-databaseindexingdatabase-partitioning
user1403174
user1403174

Reputation: 27

Can we use parallelism in analyzing partitions of a table?

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

Upvotes: 0

Views: 1151

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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.

Upvotes: 1

Related Questions