Observations on Parallel Index Rebuild jobs

Tonight I am working on index rebuild for three databases of one of our major applications. This post is intended to docomnet my observations about parallel executions.

First of all the parallel_max_server parameter of the database is 24. I run two scripts (call them script1 and script2) at the same time. Each script essentially starts with the following two statements:

alter session enable parallel ddl;
alter session force parallel ddl parallel 8;

Then, followed by bunch of alter index rebuild statements without specifying the parallel degree at the statement level, for example:

ALTER INDEX SCH1.INDEX_IX1 REBUILD NOLOGGING NOREVERSE TABLESPACE TBS_IDX_2 INITRANS 2 STORAGE (FREELISTS 2 BUFFER_POOL DEFAULT) ;

I run the script1.sql first, then 2 min later script2.sql.

I then check the v$px_session views from time to time by the following query:

SQL> select sid, qcsid, server#, degree 2* from v$px_session

At one time I saw:

  SID      QCSID    SERVER#     DEGREE
----- ---------- ---------- ----------
 1756       1756
 2797       1756          1          6
 2538       1756          2          6
  302       1756          3          6
 1649       1756          4          6
 3228       1756          5          6
 3469       1756          6          6
 2815       1756          5          6
 2087       1756          6          6
 3508       3654          1          6
 3002       1756          1          6
 3450       1756          2          6
 2827       3654          2          6
 2638       1756          3          6
  825       3654          3          6
 1305       1756          4          6
 3374       3654          4          6
 3654       3654
 1331       3654          5          6
 3516       3654          6          6
 3136       3654          5          6
 3388       3654          6          6
 3364       3654          1          6
 1521       3654          2          6
 3844       3654          3          6
  542       3654          4          6

I was able to assign QCSID=1756 to script1.sql and QCSID=3654 to script2.sql.

For the most of time, I observed:

  SID      QCSID    SERVER#     DEGREE
----- ---------- ---------- ----------
 1756       1756
 3844       3654          1          8
  141       3654          2          8
 1771       3654          3          8
  921       3654          4          8
 3374       3654          5          8
  542       3654          6          8
 3364       3654          7          8
 1331       3654          8          8
 2827       3654          1          8
 3136       3654          2          8
  665       3654          3          8
 3516       3654          4          8
 3388       3654          5          8
 1649       3654          6          8
 3508       3654          7          8
  825       3654          8          8
 3654       3654
 3228       1756          1          4
 2540       1756          2          4
 1305       1756          3          4
 2979       1756          4          4
 3469       1756          1          4
 2538       1756          2          4
  302       1756          3          4
 2797       1756          4          4

So, it looks like if we specify parallel degree N, Oracle will need to user parallel execution server 2xN. Since we have parallel_max_server=24, so if one job executed with degree 8, another will have to use degree 4 at most.

This is something I don't know before. I usually use parallel degree option in the create index statment- ie. at statement level. I have never enabled parallel option at session level. Could it be this reason? I will find it out. However by quick checking the example from Tom's Expert Oracle Database Architecture book, Chap 14, p622, it looks like if degree=8, server number=8 too.

Look at my v$px_session output again, I saw for QCSID=3654, there are two sets of server# from 1-8 .

I tested the case of using parallel degree at statement level. It also shows 2xN parallel execution server needed:

SQL_TEXT                                 ADDRESS          HASH_VALUE    SID USER_NAME
---------------------------------------- ---------------  ---------- ------ ------------
ALTER INDEX SCH_1.AXXXX_PXXXXXXXXX_PK   000000058B0A50B8  594046221    555 OPS$ORACLE
REBUILD NOLOGGING NOREVERSE TABLESPACE
SCH_IDX_4 INITRANS 10 STORAGE (FREELI
STS 2 BUFFER_POOL DEFAULT) parallel (
degree 5 )


SQL> @px_session

   SID      QCSID    SERVER#     DEGREE
------ ---------- ---------- ----------
   555        555
   103        555          1          5
   150        555          2          5
   666        555          3          5
   689        555          4          5
   339        555          5          5
   536        555          1          5
   646        555          2          5
   461        555          3          5
   442        555          4          5
   123        555          5          5

So,at this point, I believe that for index rebuild at least, Oracle needs 2xN parallel execution servers if N parallel degree is specified.

I tested the parallel query with the case similar to that is described in Tom Kyte's book, I found that Oracle used same number of parallel execution servers as the degree specified.