Index rebuilds in parallel change the parallel DEGREE

As many of the comments to this handy article echo, we got stung by this one today.  Definitely check out the article.  After a large data shuffle with some new partioning, involving index rebuilds, we saw a huge load on the server.  Didn’t take long to see that we had a query utilizing over 80 parallel threads.  Dig down further, turns out the tables are set to DEGREE of 1 but the indexes are set to DEGREE 8.  A quick alter index <name> noparallel; and the explain plan went back to normal, no parallelism. It is possible you might have to gather stats afterward to get the plan to change, but I didn’t have to.

Note that if you have an IOT (index organized table) then you will need to alter the table to noparallel, not the index, even though the index will show up in dba_indexes.

There is no parallel DML (since it was an UPDATE query) enabled on any session:

SQL> select distinct PDML_STATUS from v$session;

PDML_STA
--------
DISABLED

And the tables are not set to parallel degree > 1: 

1* select degree from dba_tables where 
lower(table_name)='mytable' and owner='myschema'

SQL> / 

DEGREE
------------------------------
1

So no problems there ... BUT the indexes are set to parallel 8 ... 

1* select degree from dba_indexes where 
index_name='PK_mytable' and OWNER='myschema'

SQL> /

DEGREE
----------------------------------------
8

1* select count(*) from dba_indexes where degree <> 'DEFAULT' 
and to_number(degree,99) > 1 and owner='myschema'

SQL> /

COUNT(*)
----------
703

Explain plan:

Operation Object

—————————————————————————————— ——————————
UPDATE STATEMENT ()
UPDATE () AGN_TOOL_VIEW_STATE
PX COORDINATOR ()
PX SEND (QC (RANDOM)) :TQ10004
NESTED LOOPS ()

ETC.

Query to generate noparallel statements for all the indexes that 
need fixing (with thanks to the article mentioned above for the 
original query showing which had a degree > 1):

SELECT
'alter index '||OWNER||'.'||INDEX_NAME||' noparallel;'
FROM
DBA_INDEXES
WHERE
degree <> 'DEFAULT'
AND to_number(DEGREE) > 1
-- put any exclusions here
--AND INDEX_NAME NOT IN ('BINARY_SEARCH_INDEX','TEXT_SEARCH_INDEX','SIMPLE_SEARCH_INDEX') 
ORDER BY
INDEX_NAME
/

Striking out from the bank …

In 2008, after a few years in support, eight years as a programmer, and four years as an IT manager, I wanted a change and moved into an Oracle DBA role. I hoped to chronicle the experience and tracks bits of flotsam that helped me along the way, but the first year was such a whirlwind that I didn’t have much time for blogging.  Now I am going to give it a shot with new things I learn as well some of my accumulated file of useful stuff.