Oracle Index Parallel Degree Fix After Parallel Creation or Rebuild


Back in 2007, my third blog entry was about a parallel index building gotcha, a warning about building & rebuilding indexes in parallel. After completing the build, Oracle left the parallel degree in place for that index in data dictionary. Thus, after building a great new index over some weekend, many of your OLTP queries could have picked a parallel plan afterwards!

Whenever creating/rebuilding any indexes, I had a habit of writing up both the CREATE INDEX ... PARALLEL immediately followed by ALTER INDEX ... NOPARALLEL commands in my notes files and scripts, so I wouldn’t forget that important part.

I noticed a “new” bugfix on my Oracle 19.21 test instance that, when enabled, will not adjust the final index parallel degree simply because someone just ran the DDL for building or rebuilding it in parallel.

I like this direction, as I think the Degree of Parallelism should never be a single object-level setting anyway. Parallelism - desired througput - should be a workload, user type level setting, not attached to some individual schema object that can be accessed and joined by a variety of completely different workloads.

Judging from the bugfix number, it is present in all Oracle 19c instances, possibly also backported to earlier versions. However it is not enabled by default:

SQL> @fix 28606162

SESSION_ID      BUGNO      VALUE SQL_FEATURE          DESCRIPTION                                                      OPTIMIZER_FEATURE_ENABLE       EVENT IS_DEFAULT     CON_ID
---------- ---------- ---------- -------------------- ---------------------------------------------------------------- ------------------------- ---------- ---------- ----------
     10166   28606162          0 QKSFM_PQ_28606162    Parallellism of create and rebuild index not inherited by index                                     0          1          1

Let’s print the same query’s output out in a more readable fashion:

SQL> @pr
SESSION_ID                    : 10166
BUGNO                         : 28606162
VALUE                         : 0
SQL_FEATURE                   : QKSFM_PQ_28606162
DESCRIPTION                   : Parallellism of create and rebuild index not inherited by index
EVENT                         : 0
IS_DEFAULT                    : 1
CON_ID                        : 1

PL/SQL procedure successfully completed.

As the VALUE is 0 and this fix is not even tied to any OPTIMIZER_FEATURES_ENABLE setting, you’d have to manually enable it to get the new behavior.

A simple test shows taht indeed, now the index degree does not change after a parallel rebuild:

SQL> ALTER SESSION SET "_fix_control"='28606162:1';

Session altered.


Index altered.

Now scroll all the way to right to see the degree:

SQL> @ind sh.customers
Display indexes where table or index name matches %sh.customers%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
SH                   CUSTOMERS                      CUSTOMERS_GENDER_BIX              1 CUST_GENDER
                                                    CUSTOMERS_MARITAL_BIX             1 CUST_MARITAL_STATUS
                                                    CUSTOMERS_PK                      1 CUST_ID
                                                    CUSTOMERS_YOB_BIX                 1 CUST_YEAR_OF_BIRTH

Elapsed: 00:00:00.12

INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
SH                   CUSTOMERS                      CUSTOMERS_GENDER_BIX           BITMAP     NO   VALID    NO   N     3       9282             2      17729      17729 2024-02-11 19:42:58 1      VISIBLE
                     CUSTOMERS                      CUSTOMERS_MARITAL_BIX          BITMAP     NO   VALID    NO   N     3      12795             3      25222      25222 2024-02-11 19:42:33 1      VISIBLE
                     CUSTOMERS                      CUSTOMERS_PK                   NORMAL     YES  VALID    NO   N     3     371336     162606400  162606400  145529071 2024-02-13 00:06:06 1      VISIBLE
                     CUSTOMERS                      CUSTOMERS_YOB_BIX              BITMAP     NO   VALID    NO   N     3      44049            85      87235      87235 2024-02-11 19:42:46 1      VISIBLE

The degree is still 1!

I suspect that I’ll still keep using my old method (and additionally double-check from data dictionary after everything is done), but there is a valid use case for this: online index builds in 24x7 OLTP environments. You don’t want to have even a 1 millisecond window of opportunity for a bunch of queries to pick up a new parallel plan. But then again, Oracle allows you to build indexes with the INVISIBLE option, so you can alter its degree back to 1 and test it out with your app before “publishing” it to everyone else by making it visible.

Note that table creation also has a similar problem now, at least in 19c, I don’t remember this being an issue in the past. If you create a table with CREATE TABLE t PARALLEL 32 AS SELECT ..., you’ll get a table with DOP 32 in data dictionary. Even if you use ALTER SESSION FORCE PARALLEL DDL PARALLEL 32 and use CTAS syntax without any parallel clauses, you still get a table with DOP 32 in the dict. Go figure… but a long as you know about this, it’s easily manageable.

As usual, you can clone my latest TPT scripts repo from GitHub or just download as a .zip file.

  1. I am finally close to launching the completely rebuilt 2024 versions of my Linux & AOT classes in my Learning Platform! (Updates to SQL Tuning class in H2 2024):
    Advanced Oracle SQL Tuning training. Advanced Oracle Troubleshooting training, Linux Performance & Troubleshooting training. Check them out!
  2. Get randomly timed updates by email or follow Social/RSS