A gotcha with parallel index builds, parallel degree and query plans

Reading the following article about PARALLEL hint by Jonathan Lewis made me remember a somewhat related gotcha with parallelism.

Often when creating (or rebuilding) an index on a large table, doing it with PARALLEL x option makes it go faster – usually in case when your IO subsystem is not the bottleneck and you have enough spare CPU capacity to throw in.

A small example below:


Tanel@Sol01> create table t1 as select * from all_objects;

Table created.

Tanel@Sol01> create index i1 on t1(object_id);

Index created.

Tanel@Sol01> exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true, no_invalidate=>false);

PL/SQL procedure successfully completed.

Ok, for whatever reason I need to rebuild my index, and for speed I do it in parallel:


Tanel@Sol01> alter index i1 rebuild parallel 4;

Index altered.

Tanel@Sol01>
Tanel@Sol01>
Tanel@Sol01> select
  2     sum(object_id)
  3  from
  4     t1
  5  where
  6     object_id > 60000
  7  /

SUM(OBJECT_ID)
--------------
      13233374

Tanel@Sol01>
Tanel@Sol01> @x

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Plan hash value: 3900446664

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |  2923 | 14615 |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID">60000)

14 rows selected.

The execution plan shows a nice serial range scan for above query. Let’s run the same query with a different value for object_id:


Tanel@Sol01> select
  2     sum(object_id)
  3  from
  4     t1
  5  where
  6     object_id > 10000
  7  /

SUM(OBJECT_ID)
--------------
    1294174783

Tanel@Sol01>
Tanel@Sol01> @x

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 2596547647

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |     1 |     5 |    19   (0)| 00:00:02 |        |      |            |
|   1 |  SORT AGGREGATE           |          |     1 |     5 |            |          |        |      |            |
|   2 |   PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000 |     1 |     5 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |          |     1 |     5 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR    |          | 42937 |   209K|    19   (0)| 00:00:02 |  Q1,00 | PCWC |            |
|*  6 |       INDEX FAST FULL SCAN| I1       | 42937 |   209K|    19   (0)| 00:00:02 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("OBJECT_ID">10000)

18 rows selected.

What? Suddenly my query turned parallel !

I haven’t enabled parallelism for my table! How can Oracle go parallel without my consent?

Tanel@Sol01> select table_name, degree from user_tables  where table_name = 'T1';

TABLE_NAME                     DEGREE
------------------------------ ----------------------------------------
T1                                      1

The answer lies in the result of next query:

Tanel@Sol01> select index_name, degree from user_indexes where table_name = 'T1';

INDEX_NAME                     DEGREE
------------------------------ ----------------------------------------
I1                             4

Parallel index (re)build will persistently set the index parallel degree in data dictionary to the value used during build!

This enables the CBO to be free to consider also parallel query plans and in our second select case a parallel plan seemed to be the best.

Whether this parallel plan actually is the most efficient way to go is a separate question, however this kind of unplanned parallelism may destabilize your system performance, especially as it can kick in only for certain instantiations of your SQL statement. Note that even one parallel-enabled object in your execution plan can parallelize the whole query joining many tables (just as even one table with statistics in a join turns on CBO for the whole cursor).

Combined with bind variable peeking side-effects and way too high parallel_max_servers value (hey it’s just a max value, let’s set it to 500), this can bring your OLTP system to knees at very unexpected times.

So, as my database does not have parallelism planned into it, I will eliminate the troublemaker:

Tanel@Sol01> alter index i1 noparallel;

Index altered.

Tanel@Sol01>
Tanel@Sol01> select
  2     sum(object_id)
  3  from
  4     t1
  5  where
  6     object_id > 10000
  7  /

SUM(OBJECT_ID)
--------------
    1294174783

Tanel@Sol01>
Tanel@Sol01> @x

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 129980005

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     5 |    19   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE       |      |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| I1   | 42937 |   209K|    19   (0)| 00:00:02 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID">10000)

14 rows selected.

So the key point is that unless your databases have planned and managed parallelism used in them, it’s worth to run the following query to identify potential troublemakers and disable their parallelism:

SELECT
	'INDEX' OBJECT_TYPE, OWNER, INDEX_NAME, TRIM(DEGREE)
FROM
	DBA_INDEXES
WHERE
	TRIM(DEGREE) > TO_CHAR(1)
UNION ALL
SELECT
	'TABLE', OWNER, TABLE_NAME, TRIM(DEGREE)
FROM
	DBA_TABLES
WHERE
	TRIM(DEGREE) > TO_CHAR(1)
/

On my test environment it returned the following rows:

OBJEC OWNER                          INDEX_NAME                     TRIM(DEGREE)
----- ------------------------------ ------------------------------ -------------
INDEX SYS                            UTL_RECOMP_SORT_IDX1           DEFAULT
TABLE TANEL                          T                              4

From here we see two addtional things:

  • Parallel operations also persist their degree to tables (using alter table move parallel x or CTAS for example)
  • There’s a parallel degree DEFAULT – which is used when you let the appropriate degree to be decided by optimizer

On the other hand, if you have planned for parallelism, then you probably want to keep the parallelism for tables and indexes consistent, e.g. enable it for all tables requiring parallelism and their indexes, not just for couple of indexes by accident.

Update:
Thanks to Adrian Billington for reminding me that also NOLOGGING flag will stick in data dictionary should you perform nologging operations on an object. You should review those too after rebuilds and reorgs.

This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

16 Responses to A gotcha with parallel index builds, parallel degree and query plans

  1. prodlife says:

    Oh, I’ve been bitten so hard by this issue.

    We had a table that was really not very well designed and several times a month the number of objects changed from 3 to 30,000,000. So every once in a while we rebuilt indexes. One day we decided to save time and rebuild in parallel. We forgot the little note in the admin guide that said that rebuilding in parallel will change the degree of the index. A day later we knew this, and we also knew that having queries run in parallel can cause very severe performance issues.

    Thanks for this article. It has good information, great examples and it also reassuring to know that I’m not the only DBA to make this mistake.

  2. paulk says:

    We had some related problems last night, your post was invaluable.

    From a message I sent to about 75 coworkers last night:

    “Allegedly some types of DDL when done in parallel will change the degree of the object. ( google oracle poder parallel. Poder is very credible, pretty much my
    rule of thumb is to read anything that he writes. )”

    I know, I shouldn’t give away my trade secrets…

  3. Paul Leland says:

    We seem to be hit by the same problem. Suddenly everything is ending with “Unable To allocate shared memory (“large pool”,”Unknown object”,”Large Pool”,”PX msg Pool”).

  4. Paul Leland says:

    We seem to be hit by the same problem. Suddenly everything is ending with ORA-04031:Unable To allocate shared memory (“large pool”,”Unknown object”,”Large Pool”,”PX msg Pool”).

  5. Avi says:

    Hi,
    How value for DEFAULT is being calculated, can you please tell me?

  6. kwanyoung oh says:

    Hi,

    I ran the scripts up there.
    But I found one strange thing in the execution plan.

    How come we have a lot different value in the Estimated rows and actual rows?

    SQL> set linesize 132
    SQL> select /*+ gather_plan_statistics */ sum(object_id) from t1 where object_id > 60000;

    SUM(OBJECT_ID)
    ————–
    65869754

    SQL> @x

    PLAN_TABLE_OUTPUT
    ————————————————————————————————————————————
    SQL_ID 6tk45guj3gkcg, child number 0
    ————————————-
    select /*+ gather_plan_statistics */ sum(object_id) from t1 where object_id > 60000

    Plan hash value: 2596547647

    ————————————————————————————————
    | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
    ————————————————————————————————
    | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 |
    | 2 | PX COORDINATOR | | 1 | | 4 |00:00:00.01 | 3 |

    PLAN_TABLE_OUTPUT
    ————————————————————————————————————————————
    | 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | 0 |00:00:00.01 | 0 |
    | 4 | SORT AGGREGATE | | 0 | 1 | 0 |00:00:00.01 | 0 |
    | 5 | PX BLOCK ITERATOR | | 0 | 19660 | 0 |00:00:00.01 | 0 |
    |* 6 | INDEX FAST FULL SCAN| I1 | 0 | 19660 | 0 |00:00:00.01 | 0 |
    ————————————————————————————————

    Predicate Information (identified by operation id):
    —————————————————

    6 – access(:Z>=:Z AND :Z60000)

    PLAN_TABLE_OUTPUT
    ————————————————————————————————————————————

    23 rows selected.

  7. Tanel Poder says:

    Did you gather optimizer stats on the table and index using dbms_stats? which command exactly did you use?

  8. oracle tom says:

    for my reorg / ilm job i just save all table and index parallel degrees to a table and reset them after the reorg job is done.

  9. Sue W - DBA in Hawaii says:

    Use of /*+ parallel */ will use parallel cpu engines without changing the object (parallel) degree value (as observed in dba_tables or dba_indexes).

  10. Sue W - DBA in Hawaii says:

    be sure it’s after the first command, e.g. CREATE /*+ parallel 3 */ TABLE/INDEX

  11. Sudheer says:

    I think if the index is not partitioned, index range scan goes serially , but Index FFS will go for parallel based on your parallelism.
    SQL> create index idx_P4 on TAB1_P4(OWNER) parallel 4;
    Index created.
    explain plan for
    Select /*+ index(TAB1_P4,idx_P4) */ count(object_name) from TAB1_P4 where OWNER=’SUDHEER;
    ————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost |
    ————————————————————
    | 0 | SELECT STATEMENT | | 1 | 10 | 51 |
    | 1 | SORT AGGREGATE | | 1 | 10 | |
    |* 2 | INDEX RANGE SCAN| IDX_P4 | 28760 | 280K| 51 |
    ————————————————————
    create index idx_P4 on TAB1_P4(OWNER) parallel 4 local;
    explain plan for
    Select /*+ index(TAB1_P4,idx_P4) */ count(object_name) from TAB1_P4 where OWNER=’SUDHEER';

    ——————————————————————————————————————-
    | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
    ——————————————————————————————————————-
    | 0 | SELECT STATEMENT | | 1 | 10 | 14 | | | | | |
    | 1 | SORT AGGREGATE | | 1 | 10 | | | | | | |
    | 2 | PX COORDINATOR | | | | | | | | | |
    | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 10 | | | | Q1,00 | P->S | QC (RAND) |
    | 4 | SORT AGGREGATE | | 1 | 10 | | | | Q1,00 | PCWP | |
    | 5 | PX PARTITION HASH ALL| | 28760 | 280K| 14 | 1 | 4 | Q1,00 | PCWC | |
    |* 6 | INDEX RANGE SCAN | IDX_P4 | 28760 | 280K| 14 | 1 | 4 | Q1,00 | PCWP | |
    ——————————————————————————————————————-

  12. ismail says:

    when I run @x command after see sum I get an below alert. How can I run @x command?

    SQL> @x
    SP2-0310: unable to open file “x.sql”

  13. Mandar Kundargi says:

    Thanks Mr.Tanel for sharing the valuable information.

  14. klep jax says:

    unsure if the parallel option with index rebuild will update the global index stats correctly. worth a test though, especially if the table itself has been stale.

  15. AV says:

    I would like to know the behavior of parallel clause in partition index rebuild , here is the DDL
    alter index IX_AGG_VALUE rebuild partition P09 parallel 4 nologging;

    After rebuilding all the index partition , i have checked dba_indexes for degree of parallelism and found its value as “1”
    select degree from dba_indexes
    –where degree=’4′
    where index_name =’IX_AGG_VALUE’
    Pl clarify if there is different concept for normal index and partition index .

    • Tanel Poder says:

      Looks like rebuilding just a partition doesn’t change the index PX degree. It makes sense as a partition is just a subobject, it shouldn’t change the parent object’s properties. And partitions don’t have PX degrees of their own in data dictionary.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>