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
/
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s