先说一下结论：
1、parallel rebuild index的并行度并不取决于"alter index index_name rebuild parallel n"中的数字n，而是取决于一系列条件，但并行度不能大于PARALLEL_MAX_SERVERS。对于这一系列条件，oracle文档中也有描述：
PARALLEL
Specify PARALLEL if you want Oracle to select default degree of parallelism.
PARALLEL integer
Specification of integer indicates the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.
Default Degree of Parallelism：
The default DOP is used when you ask to parallelize an operation but you do not specify a DOP in a hint or within the definition of a table or index. The default DOP is appropriate for most applications.
The default DOP for a SQL statement is determined by the following factors:
The value of the parameter CPU_COUNT, which is, by default, the number of CPUs on the system, the number of RAC instances, and the value of the parameter PARALLEL_THREADS_PER_CPU.
For parallelizing by partition, the number of partitions that will be accessed, based on partition pruning.
For parallel DML operations with global index maintenance, the minimum number of transaction free lists among all the global indexes to be updated. The minimum number of transaction free lists for a partitioned global index is the minimum number across all index partitions. This is a requirement to prevent self-deadlock.
These factors determine the default number of parallel execution servers to use. However, the actual number of processes used is limited by their availability on the requested instances during run time. The initialization parameter PARALLEL_MAX_SERVERS sets an upper limit on the total number of parallel execution servers that an instance can have.
2、"alter index index_name rebuild parallel n"中的数字n决定了这个被rebuild的索引被访问的并行度，但这并不意味着oracle就会依据这个并行度去访问这个索引，这里仅仅意味着CBO会把并行访问作为一种备选项。
好了，我们来验证上述结论：
SQL> select count(*) from armshistemp.uplpdt;
COUNT(*)
----------
6962716
SQL> create index idx_uplpdt_updtkt on armshistemp.uplpdt(updtkt);
Index created
SQL> show parameter PARALLEL_THREADS_PER_CPU;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu integer 2
SQL> show parameter CPU_COUNT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 8
show parameter PARALLEL_MAX_SERVERS;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 5
现在PARALLEL_THREADS_PER_CPU和CPU_COUNT的乘积是16，PARALLEL_MAX_SERVERS的值是 5，所以当我rebuild idx_uplpdt_updtkt的时候并行度一定不可能大于5，来验证一下，开两个session:
Session 1:
13:35:37 SQL> alter index idx_uplpdt_updtkt rebuild;
Index altered
13:36:06 SQL> select sysdate from dual;
SYSDATE
-----------
13:37:08 SQL> alter index idx_uplpdt_updtkt rebuild parallel 2;
Index altered
13:37:51 SQL> select sysdate from dual;
SYSDATE
-----------
13:40:37 SQL> alter index idx_uplpdt_updtkt rebuild parallel 4;
Index altered
13:41:34 SQL> select sysdate from dual;
SYSDATE
-----------
13:42:40 SQL> alter index idx_uplpdt_updtkt rebuild parallel;
Index altered
在session 1 rebuild index的时候去session 2中观察v$session中的session的数量，这里因为uplpdt中有接近700万的数据，所以在rebuild index的时候我有充足的时间去观察v$session中的session的状况，下面是我在session 2中观察到的情况：
Session 2:
在执行alter index idx_uplpdt_updtkt rebuild的时候session 2中观察到的v$session的情况为：
SQL> select sid,status,osuser,module,action from v$session;
SID STATUS OSUSER MODULE ACTION
---------- -------- ------------- ----------------- ------------------------------------------------
1 ACTIVE oracle
2 ACTIVE oracle
3 ACTIVE oracle
4 ACTIVE oracle
5 ACTIVE oracle
6 ACTIVE oracle
7 ACTIVE oracle
9 INACTIVE cuihua PL/SQL Developer Main session
13 INACTIVE cuihua PL/SQLDeveloper Command Window - New
16 ACTIVE cuihua PL/SQL Developer Command Window - New
10 rows selected
在执行alter index idx_uplpdt_updtkt rebuild parallel 2，alter index idx_uplpdt_updtkt rebuild parallel 4和alter index idx_uplpdt_updtkt rebuild parallel的时候session 2中观察到的v$session中的情况均为：
SQL> select sid,status,osuser,module,action from v$session;
SID STATUS OSUSER MODULE ACTION
---------- -------- ------------------------------ ------------------------------ ------------------
1 ACTIVE oracle
2 ACTIVE oracle
3 ACTIVE oracle
4 ACTIVE oracle
5 ACTIVE oracle
6 ACTIVE oracle
7 ACTIVE oracle
9 INACTIVE cuihua PL/SQL Developer Main session
12 ACTIVE cuihua PL/SQL Developer Command Window - New
13 ACTIVE cuihua PL/SQL Developer Command Window - New
15 ACTIVE cuihua PL/SQL Developer Command Window - New
16 ACTIVE cuihua PL/SQL Developer Command Window - New
18 ACTIVE cuihua PL/SQL Developer Command Window - New
19 ACTIVE cuihua PL/SQL Developer Command Window - New
14 rows selected
可以看到多出了4个session。
现在我们把PARALLEL_MAX_SERVERS由5改为6，然后再验证一下在做parallel rebuild的时候的状况：
SQL> alter system set parallel_max_servers=6 scope=spfile;
System altered
重启上述数据库后如法炮制，然后再在session 2中观察v$session中的状况。
SQL> show parameter PARALLEL_MAX_SERVERS;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 6
这里在session 1中分别执行：
14:34:02 SQL> alter index idx_uplpdt_updtkt rebuild parallel 2;
Index altered
14:35:05 SQL> alter index idx_uplpdt_updtkt rebuild parallel 16;
Index altered
14:36:38 SQL> alter index idx_uplpdt_updtkt rebuild parallel 4;
Index altered
15:02:00 SQL> alter index idx_uplpdt_updtkt rebuild parallel;
Index altered
15:16:05 SQL> alter index idx_uplpdt_updtkt rebuild parallel 2;
Index altered
Session 2中观察到的情况为：
1、执行alter index idx_uplpdt_updtkt rebuild parallel 2的时候多了4个session。
2、执行alter index idx_uplpdt_updtkt rebuild parallel 16的时候多了6个session。
3、执行alter index idx_uplpdt_updtkt rebuild parallel 4的时候多了6个session。
4、执行alter index idx_uplpdt_updtkt rebuild parallel的时候多了6个session。
5、再次执行alter index idx_uplpdt_updtkt rebuild parallel 2的时候还是多了4个session。
至此，结论1得到验证。
现在我们来验证结论2：
15:16:05 SQL> alter index idx_uplpdt_updtkt rebuild parallel 2;
Index altered
15:20:29 SQL> set time off;
SQL> select degree from dba_indexes where index_name='IDX_UPLPDT_UPDTKT';
DEGREE
----------------------------------------
2
SQL> alter index idx_uplpdt_updtkt rebuild parallel;
Index altered
SQL> select degree from dba_indexes where index_name='IDX_UPLPDT_UPDTKT';
DEGREE
----------------------------------------
DEFAULT
SQL> alter index idx_uplpdt_updtkt rebuild parallel 16;
Index altered
SQL> select degree from dba_indexes where index_name='IDX_UPLPDT_UPDTKT';
DEGREE
----------------------------------------
16
SQL> alter index idx_uplpdt_updtkt noparallel;
Index altered
SQL> select degree from dba_indexes where index_name='IDX_UPLPDT_UPDTKT';
DEGREE
----------------------------------------
1
至此，结论2也得到验证。
这就是为什么在parallel rebuild index后一定要执行alter index index_name noparallel的原因。
最后试了一下传说中的可以加快rebuild index的方法，发现parallel的最短时间还不如直接rebuild快！
SQL> alter session set workarea_size_policy=MANUAL;
Session altered
SQL> alter session set sort_area_size=1073741824;
alter session set sort_area_size=1073741824
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00600: internal error code, arguments: [925], [hash_area_size], [-2147483648], [], [], [], [], []
SQL> alter session set sort_area_size=800000000;
Session altered
SQL> alter session set sort_area_retained_size=800000000;
Session altered
SQL> alter session set db_file_multiblock_read_count=128;
Session altered
SQL> set time on
15:42:29 SQL> alter index idx_uplpdt_updtkt rebuild online parallel compute statistics;
Index altered
15:43:04 SQL> select sysdate from dual;
SYSDATE
-----------
15:46:25 SQL> alter index idx_uplpdt_updtkt rebuild parallel;
Index altered
15:47:05 SQL> select sysdate from dual;
SYSDATE
-----------
15:50:52 SQL> alter index idx_uplpdt_updtkt rebuild;
Index altered
15:51:18 SQL>
注意，对于结论2，当你将某个index的degree改变之后oracle这里并不一定会去并行访问的，对这一点，196938.1有如下描述：
Note that the affect of setting a degree of parallelism on an index has changed. Prior to 8i this would have had no affect. Post 8i a parallel plan will be investigated. If degree is set ( to an integer value > 1 or to 'DEFAULT' ) on an object then this will mean a parallel plan is considered. Remember that the optimizer works on a cost basis so just because a parallel plan may be considered, does not mean that it will be chosen. Since 8i we have changed the syntax for the setting of degree of parallelism See Note 260845.1 Old and new Syntax for setting Degree of Parallelism.
this really assists, today i receive the problems and i donot know how to solve,
i search bing and found your blog,
thanks once againjust one thing, can i post this article on my site? i will add the source and credit to your site.regards!
OK.
Damn, cool website. I actually came across this on Ask Jeeves, and I am really happy I did. I will definately be coming back here more often. Wish I could add to the conversation and bring a bit more to the table, but am just absorbing as much info as I can at the moment.
Great job for creating such one of a kind collection of gems! Over here the blog writer whom certainly knowledgeable but also extremely inventive too. There usually aint a army of peeps who can come up with such excellent brain busters .My friend Carolyn told me to this url several weeks ago but this is the very first time I’m visting here. Guess what, this! Yea!