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
OPTIMIZER_FEATURE_ENABLE :
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.
SQL> ALTER INDEX sh.customers_pk REBUILD PARALLEL 64;
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.
